将参数传递给 psql 过程脚本 [英] Passing argument to a psql procedural script

问题描述

我在 psql 上有一个循环命令脚本，如下所示:

I have a loop command script on psqlthat looks like this:

DO $$DECLARE
    data_rec RECORD;
    r RECORD;
    r2 RECORD;
BEGIN
select mytables.data_id into data_rec from mytables where id = :arg1;

    FOR r IN select * from
    (select * from ...)
    LOOP
        FOR r2 IN select * from
        (...)
              LOOP
            ......
          END LOOP;
    END LOOP;
END$$;

我想从命令行传递 arg1 作为参数:

And I want to pass arg1 as a argument from the command line:

psql -h db.server.com -f script.sql -v arg1=1234 > foo.out

但是我一直在 where id = :arg1 处收到语法错误，所以我不知道如何将一个简单的参数传递给这个脚本.欢迎提出建议

But I keep getting a syntax error at where id = :arg1, so I'm out of ideas how to pass a simple parameter to this script. Suggestions welcome

推荐答案

可能性很少，如何去做.匿名块与客户端环境相对隔离，因此参数化并不直观 - 但并不难.

There are few possibilities, how to do it. Anonymous block is relative strongly isolated from client environment, so parametrisation is not intuitive - but it is not hard work.

您可以使用服务器端会话变量.这些变量可以从两种环境中访问.

You can use a server side session variables. These variables are accessible from both environments.

postgres=# \set txt Ahoj
postgres=# set myvars.txt to :'txt'; -- fill server side variable
SET
postgres=# do $$ begin raise notice '%', current_setting('myvars.txt'); end;$$;
NOTICE:  Ahoj
DO

稍微复杂一点的例子

bash-4.1$ cat test.sh
echo "
set myvars.msgcount TO :'msgcount'; 
DO \$\$ 
BEGIN 
  FOR i IN 1..current_setting('myvars.msgcount')::int LOOP 
    RAISE NOTICE 'Hello';
  END LOOP; 
END \$\$" | psql postgres -v msgcount=$1


bash-4.1$ sh test.sh 10
SET
Time: 0.935 ms
NOTICE:  Hello
NOTICE:  Hello
NOTICE:  Hello
NOTICE:  Hello
NOTICE:  Hello
NOTICE:  Hello
NOTICE:  Hello
NOTICE:  Hello
NOTICE:  Hello
NOTICE:  Hello
DO
Time: 1.709 ms

这篇关于将参数传递给 psql 过程脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！