从PL/PGSQL引用会话变量(\ set var ='value') [英] Referring to session variables (\set var='value') from PL/PGSQL

问题描述

我可以使用以下方式将变量传递到PostgreSQL中

I can pass variables into PostgreSQL using

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

...，在这种情况下，在传递给stdin上的psql的SQL查询中将它们称为:var.

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

但是，这不适用于使用PL/PGSQL的代码:

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

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

...产生错误:

ERROR:  syntax error at or near ":"

如何解决?

推荐答案

您不能直接在plpgsql代码中使用psql变量.符号替换在字符串内被阻止:

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)

但是您可以设置服务器会话变量，以后再在plpgsql代码中(在服务器端)使用这种变量:

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

您可以从命令行使用相同的技术，请参见: http://okbob.blogspot.cz/2015/01/how-to-push-parameters-to-do-statement.html

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

最后的笔记-代码

BEGIN
  SELECT some;
END;

在plpgsql中无效.任何SELECT的结果都应存储在某些变量中. Postgres不可能将免费的SELECT结果返回给客户端-DO语句不等同于MS SQL过程.

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.

这篇关于从PL/PGSQL引用会话变量(\ set var ='value')的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！