14

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

2
  • 1
    I think you are looking for the -v argument of psql. Commented Jun 14, 2012 at 20:54
  • I tried that - to retrieve it in the script, I'm calling "SELECT current_setting('pretend') INTO _result" - no success.
    – Jmoney38
    Commented Jun 14, 2012 at 21:28

6 Answers 6

7

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.

1
  • 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
    Commented Jun 18, 2012 at 11:52
7

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';
1
  • FYI you can do :'action' and define it as action="drop"
    – Rafs
    Commented May 2 at 15:27
6

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.

1
  • What I want to run a DO block inside psql and pass a -v variable inside the dynamic sql block? the variable is a database name, and the query is to alter that database and set timezone.
    – Rafs
    Commented May 2 at 15:20
3

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';
 ?column? 
----------
 drop
(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)
DO
jmindek=#

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

1
  • I was trying to do that; too bad there is no way to pass the var into DO blocks, dynamic SQL can't be used then
    – Rafs
    Commented May 2 at 15:21
1

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
       sql_script="$pgsql_scripts_dir/01.create-qto-app-user.pgsql"
       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
       ret=$?
       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
            DO
            $do$
            BEGIN
               IF NOT EXISTS (
                  SELECT
                  FROM   pg_catalog.pg_roles
                  WHERE  rolname = 'usrqtoapp') THEN
                     CREATE ROLE usrqtoapp
                       WITH PASSWORD :'postgres_db_user_pw' LOGIN ;
               END IF;
            END
            $do$;
            ALTER ROLE usrqtoapp
              WITH PASSWORD  :'postgres_db_user_pw' LOGIN ;

    -- eof run.sql
1
  • Shouldn't it be :'postgres_db_user_pw' etc.?
    – Rafs
    Commented May 2 at 15:23
0

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

cat <<EOF
   UPDATE tablename SET field=$arg1 WHERE field = $arg2;
EOF | psql database
1
  • A variant of one line here documents: psql database <<< "UPDATE tablename SET field=$arg1 WHERE field = $arg2;"
    – yurenchen
    Commented Mar 30, 2022 at 21:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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