postgresql - 如何将 psql 变量传递给 PL/pgSQL 代码的 DECLARE 部分？

Question

我想在 PostgreSQL 的 PL/pgSQL 代码的声明部分使用一个额外的变量。

DO $ANONYMOUS_BLOCK$ declare
   user_c cursor is
   select username
   from   users
   where upper(:v1) in ('ALL',upper(component_id)) order by username;
begin
   /* data */
end $ANONYMOUS_BLOCK$;

外壳命令:

psql -f test.psql -v v1='SR'

我尝试了多种情况来获取上述函数中的值'SR'，但无法实现。

预期结果:

DO $ANONYMOUS_BLOCK$ declare
   user_c cursor is
   select username
   from   users
   where upper('SR') in ('ALL',upper(component_id)) order by username;
begin
   /* data */
end $ANONYMOUS_BLOCK$;

需要在 v1 的位置获取 'SR' 值。

我收到这个错误:

psql:test.psql:13: ERROR:  syntax error at or near ":"
LINE 5:    where  :v1 in ('ALL',upper(component_id)) order by userna...

Answer 1

The manual for psql:

Variable interpolation will not be performed within quoted SQL literals and identifiers.

DO 语句(或函数)的主体是被引用的文字。在这种情况下以美元报价，但都是一样的。

我不得不在最近关于 dba.SE 的回答中给出同样的否定答复:

• How to pass variable to PL/pgSQL code from the command line?

但是有多种解决方法，我在那里提供了一些。

对于您的情况来说，一个简单的方法可能是创建一个临时函数而不是DO 语句，然后立即调用它。然后你可以很容易地传递变量。将此写入您的文件 test.psql:

CREATE FUNCTION pg_temp.foo(_v1 text)
  RETURNS void AS  -- can also return sth. (as opposed to DO)
$func$
DECLARE
   user_c cursor is
   SELECT username
   FROM   users
   WHERE  upper(_v1) IN ('ALL', upper(component_id))
   ORDER  BY username;
BEGIN
   RAISE NOTICE '%', _v1;  -- to demonstrate
   -- more
END 
$func$  LANGUAGE plpgsql;

SELECT pg_temp.foo(:'v1'); -- call with psql variable interpolation

然后从 psql 或直接从 shell 调用它:

Linux 外壳:

psql -f '\path\to\file\test.psql' -v v1='SR'

Windows 外壳:

"C:\Program Files\PostgreSQL\12\bin\psql" -p 5432 -U postgres -f "C:\path\to\file\test.psql" -v v1='SR'

根据您的设置调整文件路径和连接参数。

旁白:postgresql-9.2 ， 严重地？自 2017 年以来不受支持。Consider upgrading to a current version.