2

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

script.sql

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$$;

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

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

diffeomorphism
  • 991
  • 2
  • 10
  • 27

1 Answers1

2

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

Little bit more complex example

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
Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94
  • I see your point (on my deleted answer) - specifically in the case of a function body, it's not parsed as an expression that you can concatenate strings in, so you can't `$$ ... '$$ || :var || $$' ... $$` it. – Craig Ringer Jun 06 '14 at 07:29
  • @CraigRinger I am thinking, so your solution is possible, but I don't prefer it - server side variables based design is more clean, and automatically safe against SQL injection. Probably there are not a problem with classic attacks, but can be problem with some specific data that contains apostrophes or some other special chars. – Pavel Stehule Jun 06 '14 at 07:36
  • 2
    To use *server side session variables* (aka *customized options*) in Postgres 9.1 or earlier one must set **custom_variable_classes** option in *postgresql.conf*. – klin Jun 06 '14 at 11:44