postgresql - Referring to session variables (set var='value') from PL/PGSQL

Question

I can pass variables into PostgreSQL using

psql --variable="var='value'" <<<'SELECT :var'

...and refer to them as, in this case, :var in SQL queries passed to psql on stdin.

However, this doesn't work from code using PL/PGSQL:

psql --variable=var="'value'" <<'EOF'
  DO $$
  BEGIN
    SELECT :var;
  END;
  $$
EOF

...yielding the error:

ERROR:  syntax error at or near ":"

How can this be resolved?

Answer 1

You cannot to use a psql variables inside plpgsql code directly. The symbol substitution is blocked inside strings:

postgres=> select :'xx';
 ?column? 
----------
 AHOJ
(1 row)

postgres=> select ' :xx ';
?column? 
----------
 :xx 
(1 row)

But you can set a server session variables and later to use this kind of variables in plpgsql code (on server side):

postgres=> set myvars.xx = :'xx';
SET
postgres=> do $$ begin 
                   raise notice '>>%<<', current_setting('myvars.xx');
                 end $$;
NOTICE:  >>AHOJ<<
DO

You can use same technique from command line, see: http://okbob.blogspot.cz/2015/01/how-to-push-parameters-to-do-statement.html

last note - the code

BEGIN
  SELECT some;
END;

is invalid in plpgsql. Results of any SELECTs should be stored in some variables. Postgres has not possibility to returns result of free SELECT to client - DO statement is not equivalent of MS SQL procedure.