使用DO块内的psql元命令设置的变量 [英] Use variable set by psql meta-command inside of DO block

问题描述

这是我想做的事情：

\set values foo,bar,baz

DO $$
DECLARE
    value  TEXT;
    values TEXT[] := string_to_array(:'values', ',');
BEGIN
    FOREACH value IN ARRAY values LOOP
        raise notice 'v: %', value;
    END LOOP;
END $$ LANGUAGE plpgsql;

这会导致以下错误：

ERROR:  syntax error at or near ":"
SELECT string_to_array(:'values', ',') INTO values...
                       ^

这是我目前拥有的解决方案，但感觉hacky：

Here's the solution I have currently, but it feels hacky:

\set values foo,bar,baz

PREPARE get_values AS SELECT string_to_array(:'values', ',');

DO $$
DECLARE
    value  TEXT;
    values TEXT[];
BEGIN
    EXECUTE 'EXECUTE get_values' INTO values;

    FOREACH value IN ARRAY values LOOP
        raise notice 'v: %', value;
    END LOOP;
END $$ LANGUAGE plpgsql;

推荐答案

答案

DO 需要一个带有plpgsql代码的字符串文字。符号不能在psql的字符串内替换。

您可以将整个字符串连接到psql变量中，然后然后执行它。

Answer

DO expects a string literal with plpgsql code. Symbols are not substituted inside strings in psql.
You could concatenate the whole string into a psql variable and then execute it.

• 如何连接psql变量？

• How to concatenate psql variables?

不可能使用多行格式，因为（每个文档）：

Pretty multi-line format is not possible, because (per documentation):

在任何情况下，元命令的参数都不能在行尾继续
。

But in any case, the arguments of a meta-command cannot continue beyond the end of the line.

简单示例：

test=# \set value foo
test=# \set do 'BEGIN\n   RAISE NOTICE ''v: %'', ' :'value' ';\nEND'
test=# DO :'do';
NOTICE:  v: foo

用 \替换换行符n （如果您不喜欢漂亮的格式，请将其删除）。根据以下修改后的代码：

Replace line breaks with \n (or remove them if you don't care for pretty format). Based on this adapted code:

DO
'
DECLARE
   _val  text;
   _vals text[] := string_to_array(>>values<<, '','');
BEGIN
   FOREACH _val IN ARRAY _vals
   LOOP
     RAISE NOTICE ''v: %'', _val;
   END LOOP;
END
'

它看起来像这样：

test=# \set do 'DECLARE\n   _val  text;\n   _vals text[] := string_to_array(' :'values' ', '','');\nBEGIN\n   FOREACH _val IN ARRAY _vals\n   LOOP\n     RAISE NOTICE ''v: %'', _val;\n   END LOOP;\nEND'
test=# DO :'do';
NOTICE:  v: foo
NOTICE:  v: bar
NOTICE:  v: baz
DO

我在变量中添加了粗体强调，以便于查找。

I added bold emphasis to the variable to make it easier to spot.

@Pavel（ab ）使用服务器会话变量：

Related answer by @Pavel (ab)using a server session variable:

• 引用PL / PGSQL中的会话变量（\set var ='value'）

• Referring to session variables (\set var='value') from PL/PGSQL

您当前的解决方案看起来并不那么糟糕。我会简化一下：

Your current solution doesn't look that bad. I would simplify:

PREPARE get_values AS SELECT * FROM regexp_split_to_table(:'values', ',');

DO
$do$
DECLARE
   _val text;
BEGIN
   FOR _val IN EXECUTE
      'EXECUTE get_values'
   LOOP
      RAISE NOTICE 'v: %', _val;
   END LOOP;
END
$do$;

临时表

类似的解决方案临时表：

Temporary table

Similar solution with a temporary table:

CREATE TEMP TABLE tmp AS SELECT * FROM regexp_split_to_table(:'values', ',') v;

DO
$do$
DECLARE
   _val text;
BEGIN
   FOR _val IN
      TABLE tmp
   LOOP
      RAISE NOTICE 'v: %', _val;
   END LOOP;
END
$do$;

这篇关于使用DO块内的psql元命令设置的变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！