How to substitute psql script variables into strings in the query so it doesn't fail regardless if the variable is set or not

Question

What I want is for the same query not to fail - regardless of the variable being set or not. Then depending on the variable being set - returning relevant output.

Here's more or less what I mean/want:

--variable not set, returns not substituted string:
SELECT 'solution=:abc' result;
    result     
---------------
 solution=:abc
--variable set:
\set abc 5+7
SELECT 'solution=:abc' result;
    result     
---------------
 solution=5+7

BUT INSTEAD in both cases variable doesn't get substituted:

SELECT 'solution=:abc' result;
    result     
---------------
 solution=:abc
--variable set:
\set abc 5+7
SELECT 'solution=:abc' result;
    result     
---------------
 solution=:abc

I found that I need to use :'variable' syntax, so

SELECT 'solution=':'abc' result;

but this gives an extra (') sign there:

    result     
---------------
 solution='5+7

and doesn't work when variable is not set

\unset abc
SELECT 'solution=':'abc' result;
ERROR:  syntax error at or near ":"
LINE 1: SELECT 'solution=':'abc' result;

What's going on?

Any way to make it work in both cases as described at the top? I am on PostgreSQL 9.6.11

@fphilipe not really - It gives good explanation how this works but in essence I was looking for something that would work without raising an ERROR irrespective if the variable is set or not… — msciwoj, Jun 24 '19 at 13:15

score 3 · Answer 1 · answered Jun 18 '19 at 06:05

psql's variable interpolation is syntax aware. If you want to use the variable

verbatim, use :abc;
as a string, use :'abc';
as an identifier (e.g. for a table name), use :"abc" (e.g. for a table name).

What you're trying to do is to combine two strings. Thus, syntactically the correct would be:

SELECT 'solution=' || :'abc' result;

The explanation as to why there's the extra quote when you do

SELECT 'solution=':'abc' result;

is that :'abc' gets replaced by '5+7', resulting in

SELECT 'solution=''5+7' result;

The double single quote is how you escape a single quote in a string, thus the single quote in your output.

As to how to make the query work when there is no variable set, I think there you're out of luck. The only workaround would be to initialize the variable to the variable name itself:

db=# \set abc :abc
db=# SELECT 'solution=' || :'abc' result;
    result
---------------
 solution=:abc

How to substitute psql script variables into strings in the query so it doesn't fail regardless if the variable is set or not

1 Answers1