Friday, January 6, 2012

Postgresql - psql passing variables to a sql script

It could arise the necessity to parametrize the scripts that we pass to psql, maybe just for testing purposes. For instance, this is the script that pgbench uses for each transaction that simulated clients execute:

BEGIN;

    UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;

    SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

    UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;

    UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;

    INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);

    END;


I called this script : pgbench_transaction.sql
As you can see, each variable has a colon as prefix. You can execute manually by invoking psql like this:

postgres@linux-2561:~> psql -p 9999 -v tid=1 -v bid=2 -v aid=3 -v delta=4  pgbenchdb < pgbench_transaction.sql
so you prefix each variable=value pair with a -v flag And the output:
BEGIN
UPDATE 1
 abalance 
----------
        8
(1 row)

UPDATE 1
UPDATE 1
INSERT 0 1
COMMIT
an alternative way to do this "parameter passing" is by including set statements inside the script, obviously this approach is less flexible.
BEGIN;
        \set tid 1
        \set bid 2
        \set aid 3
        \set delta 4

    UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
    SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
    UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
    UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
    INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
    END;
but useful nonetheless. There's an issue with using quotes, they must be escaped. For instance:
\set myvariable '\'somestring\'' SELECT * FROM FOO WHERE BAR = :myvariable
http://stackoverflow.com/questions/36959/how-do-you-use-script-variables-in-postgresql

No comments:

Post a Comment