Pages

Sunday, January 18, 2015

how to push parameters to DO statement from command line

PostgreSQL DO statement doesn't support parametrization. But with psql variables we are able to "inject" do statement safely and we can do it:
bash-4.1$ cat test.sh 
#!/bin/bash

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

Usage:
bash-4.1$ ./test.sh 3
SET
Time: 0.386 ms
NOTICE:  Hello
NOTICE:  Hello
NOTICE:  Hello
DO
Time: 1.849 ms

1 comment: