Passing arguments to psql

To add another functionality for -v... If you're trying to add the quotation, add it in the command line:

psql -v action="'drop'"

and this will run the code for:

select * where :action;

The same as

select * where 'drop';

Try -v:

$ psql -U postgres -v something=\'blah-blah\'
psql (9.1.3)
Type "help" for help.

postgres=# select :something;
(1 row)

If you want to use current_setting and SET or setval, you have to append a row to postgresql.conf to add the option.

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:


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:

    UPDATE tbl1 SET col1 = $1 WHERE id = $2;
    UPDATE tbl2 SET col1 = $1 WHERE id = $2;

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.