postgres=# \set v_embed %:v_ssn% postgres=# \echo :v_embed %345% postgres=# SELECT * FROM employee WHERE ssn LIKE :'v_embed'; ssn | name -----------+---------- 123456789 | John Doe (1 row)
I need to pass a \set variable (in this example named v_ssn) into a LIKE string. A two-stage process (building v_like from v_ssn, and then using v_like in the LIKE string) works, and is fine when executing an sql script, but not so good is there any way to directly embed v_ssn in another string?
test=# SELECT * FROM employee WHERE ssn LIKE :'v_like'; ssn | name | ssn_int -----------+----------+----------- 123456789 | John Doe | 123456789 (1 row)
As expected, this fails:
postgres=# SELECT * FROM employee WHERE ssn LIKE :'%v_ssn%'; ERROR: syntax error at or near ":" LINE 1: SELECT * FROM employee WHERE ssn LIKE :'%v_ssn%';