CAn you use a vsql variable in a where statement which expects a varchar

[VaughanR]

Hi,

I have defined a vsql variable:
\set MPcode 'LIPI3';

now I would like to use it like so:
select MasterProductID from sandbox.DimProductMaster where MasterProductCode = :MPcode;
where MasterProductCode is a varchar but I get the error
ERROR 2624: Column "LIPI3" does not exist

How do I specify the variable to get this match to work?

[VaughanR]

Well a little more playing around and I got this to work:
sandbox=> \set MPcode to_char('LIPI3');
sandbox=> \echo :MPcode
to_char('LIPI3');
sandbox=> select MasterProductID from sandbox.DimProductMaster where MasterProductCode = :MPcode;
MasterProductID
-----------------
4867
(1 row)

[NorbertKrupa]

You can also do it like this:

Code: Select all

dbadmin=> select * from t1;
 c1 |  c2
----+------
  1 | Jim
  2 | Jane
(2 rows)

dbadmin=> \set v '''Jane'''

dbadmin=> select * from t1 where c2 = :v;
 c1 |  c2
----+------
  2 | Jane
(1 row)