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

  • 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

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.

| 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

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';
| improve this answer | |

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.

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

| 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
| improve this answer | |

This approach will provide you will full run-time resolution of the env vars ... so as soon as your script sets before-hand all the shell variables bellow it will work ( has been ran thousands of times against different dbs and hosts ) :

    -- start run.sh

       # 01 create / modify the app user
       PGPASSWORD="${postgres_db_useradmin_pw:-}" psql -q -t -X -w -U "${postgres_db_useradmin:-}" \
          -h $postgres_db_host -p $postgres_db_port \
          -v ON_ERROR_STOP=1 \
          -v postgres_db_user_pw="${postgres_db_user_pw:-}" \
          -v postgres_db_name="${postgres_db_name:-}" \
          -f "$sql_script" "${postgres_db_name:-}" > "$tmp_log_file" 2>&1
       cat "$tmp_log_file" ; cat "$tmp_log_file" >> $log_file # show it and save it
       test $ret -ne 0 && sleep 3
       test $ret -ne 0 && doExit 1 "pid: $$ psql ret $ret - failed to run sql_script: $sql_script !!!"
    -- stop run.sh

    -- start fun.sql
               IF NOT EXISTS (
                  FROM   pg_catalog.pg_roles
                  WHERE  rolname = 'usrqtoapp') THEN
                     CREATE ROLE usrqtoapp WITH PASSWORD ':postgres_db_user_pw' LOGIN ;
               END IF;
            ALTER ROLE usrqtoapp WITH PASSWORD  :'postgres_db_user_pw' LOGIN ;

    -- eof run.sql
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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