I'm running a plpgsql script in Postgres 8.3 – I would like to pass arguments to this script via psql. I'm currently executing the script like:
psql -d database -u user -f update_file.sql
I came across This link which explains PGOPTIONS environment variable, but that doesn't work for "custom" arguments. i.e. I receive an error because the setting isn't listed in the postgres.conf file.
-bash-3.2$ export PGOPTIONS='--pretend=true'
-bash-3.2$ psql -d my_db -f update_database.sql
psql: FATAL: unrecognized configuration parameter "pretend"
Any other ideas? Ideally I'd like to avoid environment variables…
Best Answer
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 andEXECUTE
.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:
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:
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):
Or with any data type:
-c
executes one command string and then exits. More about command line arguments of psql in the manual.