How to Use Script Variables in Psql

How do you use script variables in psql?

Postgres variables are created through the \set command, for example ...

\set myvariable value

... and can then be substituted, for example, as ...

SELECT * FROM :myvariable.table1;

... or ...

SELECT * FROM table1 WHERE :myvariable IS NULL;

edit: As of psql 9.1, variables can be expanded in quotes as in:

\set myvariable value 

SELECT * FROM table1 WHERE column1 = :'myvariable';

In older versions of the psql client:

... If you want to use the variable as the value in a conditional string query, such as ...

SELECT * FROM table1 WHERE column1 = ':myvariable';

... then you need to include the quotes in the variable itself as the above will not work. Instead define your variable as such ...

\set myvariable 'value'

However, if, like me, you ran into a situation in which you wanted to make a string from an existing variable, I found the trick to be this ...

\set quoted_myvariable '\'' :myvariable '\''

Now you have both a quoted and unquoted variable of the same string! And you can do something like this ....

INSERT INTO :myvariable.table1 SELECT * FROM table2 WHERE column1 = :quoted_myvariable;

How to declare a variable in postgres script?

You are confused on several levels.

1. There is the query language SQL, and there is the procedural language PL/pgSQL. The only connection is that

   • you can run SQL statements from PL/pgSQL code

   • you can have PL/pgSQL code in the body of the SQL statements DO and CREATE FUNCTION/PROCEDURE.

2. There are variables in PL/pgSQL, which are defined in the DECLARE section, but there are no variables in SQL.

3. DO statements cannot return any values.

If you want to use PL/pgSQL variables, and you want to return values, you'll have to use a function. An example:

CREATE FUNCTION getpersons() RETURNS SETOF person
   LANGUAGE plpgsql AS
$$DECLARE
   overTheAgeOf int := 15;
BEGIN
    RETURN QUERY
       SELECT *
       FROM person
       WHERE age > overTheAgeOf;
END;$$;

SELECT getpersons();

There is the alternative of using variables on the client. With the psql client, you could use:

\set overTheAgeOf 15

SELECT *
FROM person
WHERE age > :overTheAgeOf;

How do I use a variable in Postgres scripts?

I can think of three approaches, since psql cannot do this directly.

Shell script

Use a bash script to perform the variable substitution and pipe the results into psql, like.

#!/bin/bash

$schemaName = $1
$contents = `cat script.sql | sed -e 's/@SCHEMA_NAME@/$schemaName'`

echo $contents | psql

This would probably be a lot of boiler plate if you have a lot of .sql scripts.

Staging Schema

Keep the approach you have now with a hard-coded schema of something like staging and then have a bash script go and rename staging to whatever you want the actual schema to be.

Customize the search path

Your entry point could be an inline script within bash that is piped into psql, does an up-front update of the default connection schema, then uses \ir to include all of your .sql files, which should not specify a schema.

#!/bin/bash

$schemaName = $1

psql <<SCRIPT
SET search_path TO $schemaName;
\ir sql/file1.pgsql
\ir sql/file2.pgsql
SCRIPT

Some details: How to select a schema in postgres when using psql?

Personally I am leaning towards the latter approach as it seems the simplest and most scalable.

how to use variables in a psql script

First of all, you are trying to concatenate two strings with + operator, but the SQL operator for concatenation is ||, with that information, you could think the expected result would be (won't work):

\set path '''c:\\server\\data\\'''
COPY paymentMethods (name,regexString) FROM :path || 'paymentMethods.csv' WITH (FORMAT csv, HEADER true);
COPY priceLevels (name) FROM :path || 'priceLevels.csv' WITH (FORMAT csv, HEADER false);

But! The COPY command expect a literal string for the path, not an expression, so you really should give the path. Notice it would work for commands like SELECT, INSERT, UPDATE, etc.

With that information, you can only use psql variables, as Pavel pointed, and concatenate the strings into a psql's variable. A good solution is to use the psql's :'var' syntax, that insert the variable as a string into the SQL expression:

\set path 'c:\\server\\data\\'
\set paymentMethodsPath :path 'paymentMethods.csv'
\set priceLevelsPath :path 'priceLevels.csv'
COPY paymentMethods (name,regexString) FROM :'paymentMethodsPath' WITH (FORMAT csv, HEADER true);
COPY priceLevels (name) FROM :'priceLevels' WITH (FORMAT csv, HEADER false);

Which will generate (will send to the PostgreSQL's server):

COPY paymentMethods (name,regexString) FROM E'c:\\server\\data\\paymentMethods.csv' WITH (FORMAT csv, HEADER true);
COPY priceLevels (name) FROM E'c:\\server\\data\\priceLevels.csv' WITH (FORMAT csv, HEADER false);

This :'var' syntax will not work on all psql versions (I don't recall now which one this was introduced), but for old versions you can easily use dollar-quoting:

\set path 'c:\\server\\data\\'
\set paymentMethodsPath :path 'paymentMethods.csv'
\set priceLevelsPath :path 'priceLevels.csv'
COPY paymentMethods (name,regexString) FROM $$:paymentMethodsPath$$ WITH (FORMAT csv, HEADER true);
COPY priceLevels (name) FROM $$:priceLevels$$ WITH (FORMAT csv, HEADER false);

Or escape into single-quotes:

\set path 'c:\\server\\data\\'
\set paymentMethodsPath 'E''':path'paymentMethods.csv'''
\set priceLevelsPath 'E''':path'priceLevels.csv'''
COPY paymentMethods (name,regexString) FROM :paymentMethodsPath WITH (FORMAT csv, HEADER true);
COPY priceLevels (name) FROM :priceLevels WITH (FORMAT csv, HEADER false);

That is it.

PSQL passing external variables

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

• What are '$$' used for in PL/pgSQL

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:

• How to pass variable to PL/pgSQL code from the command line?

---

Related Topics