Strictly speaking, there is no such thing as a "plpgsql script" - PL/pgSQL is the default procedural language of PostgreSQL. It's either an SQL script or a plpgsql function / procedure. Your example seems to indicate an SQL script.
You could create a (server-side ) plpgsql (or sql) function instead, that takes any number of arguments. It's very simple as long as the arguments are values
. It gets a bit more complicated if the arguments include identifiers. Then you'll have to use PL/pgSQL with dynamic SQL and EXECUTE
.
PL/pgSQL is pre-installed by default in PostgreSQL 9.0 or later. You have to install it once per database in Postgres 8.3, though:
CREATE LANGUGAGE plpgsql;
Speaking of the version: you should consider upgrading to a current version of PostgreSQL. v8.3 is very old by now, end-of-life in early 2013.
Since you seem to have a ready SQL script I'll demonstrate an SQL function. Simple dummy function with two integer arguments:
CREATE OR REPLACE FUNCTION func(int, int)
LANGUAGE sql RETURNS void AS
$func$
UPDATE tbl1 SET col1 = $1 WHERE id = $2;
UPDATE tbl2 SET col1 = $1 WHERE id = $2;
$func$;
You can find many more sophisticated examples for plpgsql here on dba.SE or on SO.
You can call this function and hand in parameters in a shell script:
Basic example for a call in a shell script that uses input parameters for integer parameters (no single-quotes around the value needed):
psql mydb -c "SELECT func($1, $2)"
Or with any data type:
psql mydb -c "SELECT func2('$1'::text, '$2'::numeric)"
-c
executes one command string and then exits.
More about command line arguments of psql in the manual.
The -1
option to psql
causes it to wrap a file specified by -f
in a BEGIN
..COMMIT
block, making it a transaction.
Otherwise, add the BEGIN
and COMMIT
commands to your script so that it becomes a single transaction.
Best Answer
You can use the environment variable
PGOPTIONS
. Either set it permanently or just when you call psql, e.g.,This works for any libpq client, including psycopg.
Of course you could also just put the
SET
statement in the psqlscript.