Take the 2-minute tour ×
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.

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
add comment

3 Answers

up vote 0 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:

CREATE LANGUGAGE plpgsql;

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:

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

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
add comment

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
add comment

Try -v:

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

postgres=# select :something;
 ?column?
----------
 blah-blah
(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
add comment

Your Answer

 
discard

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.