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
Well that is sneaky:) Thanks I am going to use this.
ReplyDelete