Passing arguments to psql

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...

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 PL/pgSQL code block - inside a function / procedure or a DO command. 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 or syntax elements. Then you have to use PL/pgSQL with dynamic SQL and EXECUTE.

PL/pgSQL is pre-installed by default since Postgres 9.0. Install it once per database in Postgres 8.3, though:

CREATE LANGUAGE plpgsql;

Consider upgrading to a current version. Postgres 8.3 reached end-of-life in 2013.

Demonstrating an SQL function, a simple dummy function with two integer arguments:

CREATE OR REPLACE FUNCTION func(int, int)
  RETURNS void 
  LANGUAGE sql AS 
$func$
    UPDATE tbl1 SET col1 = $1 WHERE id = $2;
    UPDATE tbl2 SET col1 = $1 WHERE id = $2;
$func$;

Find many more sophisticated examples for PL/pgSQL here on dba.SE or on SO.

You can call this function and pass parameters in a shell script:
Basic example call with 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.

StackExchange Database Administrators Q#19291, answer score: 7