How do you use script variables in psql?

One final word on PSQL variables:

They don't expand if you enclose them in single quotes in the SQL statement. Thus this doesn't work:
```
SELECT * FROM foo WHERE bar = ':myvariable'
```
To expand to a string literal in a SQL statement, you have to include the quotes in the variable set. However, the variable value already has to be enclosed in quotes, which means that you need a second set of quotes, and the inner set has to be escaped. Thus you need:
```
\set myvariable '\'somestring\''  
SELECT * FROM foo WHERE bar = :myvariable
```
EDIT: starting with PostgreSQL 9.1, you may write instead:
```
\set myvariable somestring
SELECT * FROM foo WHERE bar = :'myvariable'
```

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;

You can try to use a WITH clause.

WITH vars AS (SELECT 42 AS answer, 3.14 AS appr_pi)
SELECT t.*, vars.answer, t.radius*vars.appr_pi
FROM table AS t, vars;

Related