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 Comments:

At January 18, 2015 at 7:01 AM , Blogger Adrian Klaver said...

Well that is sneaky:) Thanks I am going to use this.

 

Post a Comment

Subscribe to Post Comments [Atom]

Links to this post:

Create a Link

<< Home