Search code examples
postgresqlparameter-passingplpgsqlpsql

PSQL passing external variables

I'm trying to pass a value/variable from a batch script to a SQL script and I am having trouble.

The code flows as such:

Starting in my batch script, I make a psql command call referencing myFirstSQLFile.sql, and passing env as a variable with a value of test

%PSQLCOMMAND% -h {server} -U {user} -d {dbname} -E -q -f myFirstSQLFile.sql -v env="test"

In my myFirstSQLFile.sql, it makes references to a number of other sql files, but I'll just show one for simplicity. It is in this file that I know I can access my env variable by using :env

\i myLastSQLFile.sql;

Within myLastSQLFile.sql, I have sql code that is meant to use that external env var for conditional statements as below

DO
$do$
BEGIN
   IF :env = 'test' THEN
       # Do Something
   END IF;
END
$do$

I know outside of those $do$ tags, I can access my variable by using the notation :env, However within them I cannot. I don't really understand what the $do$ tags or the BEGIN/END block are for and if/how I can access my env variable within them.

Solution

  • A DO statement executes an anonymous code block. The "$do$ tags" are dollar-quotes. Read this first to understand:

    BEGINand END are decorators for a PL/pgSQL code block - which is the default PL (programming language) of a DO statement.

    psql does not interpolate variables inside quoted SQL literals and identifiers.

    One way to fix your problem is using Postgres string processing and then execute the result with \gexec. From psql:

    SELECT format($$
DO
$do$
BEGIN
   IF %L = 'test' THEN
      -- do something
   END IF;
END
$do$;
$$, :'env')\gexec

    There are various other ways. Maybe a (temporary) function can serve you better: you can pass parameters to it. Depends on the complete picture.
    See this answers to a very similar question on dba.SE: