Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.3k views
in Technique[技术] by (71.8m points)

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

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...