Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
I think you are looking for the -v argument of psql. – dezso Jun 14 '12 at 20:54
I tried that - to retrieve it in the script, I'm calling "SELECT current_setting('pretend') INTO _result" - no success. – Jmoney38 Jun 14 '12 at 21:28
up vote 1 down vote accepted

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 - updates will be discontinued (end-of-life) early in 2013.

Since you seem to have a ready SQL script I'll demonstrate an SQL function. Simple dummy function with two integer arguments:

    UPDATE mytbl1 SET col1 = $1 WHERE id = $2;
    UPDATE mytbl2 SET col1 = $1 WHERE id = $2;

You can find many more sophisticated examples for plpgsql here on dba.SE or on stackoverflow.

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.

share|improve this answer
Thanks for the response - I'm actually quite aware of plpgsql - This script I'm referring to is a file that contains numerous functions. I have a "main" function in the sense of C-oriented programming. The last 2 lines within the script/file are 1) calling the "main" function and then 2) dropping the function. So, in this setup, I essentially have a self-contained script that can be run to do work (psql -f). I like your point about calling a function with the "application args" via psql -c. I'll probably go that route, because I can't go the route of adding values to the postgres.conf file. – Jmoney38 Jun 18 '12 at 11:52

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.

share|improve this answer

From my experience, dereffing a psql variable inside a plpgsql declaration such as in CREATE FUNCTION BEGIN or DO BEGIN results in a syntax error:

/tmp $ psql -U jmindek -v action=drop
psql (9.3.4)
Type "help" for help.

jmindek=# select :'action';
(1 row)

jmindek=# DO $$ BEGIN RAISE INFO 'The value in variable action is (%)',:x; END $$;     
ERROR:  syntax error at or near ":"
LINE 1: ... RAISE INFO 'The value in variable action is (%)',:x; END $$...

My solution is to create a temporary table with a single column and store the value in it. This temporary table is accessible via plpgsql and thus I can pass psql variables used with in DO blocks.

 ~ $ psql -v action=drop
psql (9.3.4)
Type "help" for help.

jmindek=# create temporary table actions (type text);                                                             CREATE TABLE
jmindek=# insert into actions values (:'action');                                                                 INSERT 0 1
jmindek=# do $$                                                                                                   declare                                                                                                            action_type text := null;                                                                                        begin                                                                                                               select type from actions into action_type;                                                                        raise info 'Hello, the action is (%)',action_type;                                                              end $$;
INFO:  Hello, the action is (drop)

To use additional psql variables in CREATE FUNCTION or DO declarations you may create a column per variable needed.

share|improve this answer

This is not very elegant but it works (pseudocode):

cat <<EOF
   UPDATE tablename SET field=$arg1 WHERE field = $arg2;
EOF | psql database
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.