sql - How do you use script variables in psql?

Question

In MS SQL Server, I create my scripts to use customizable variables:

DECLARE @somevariable int  
SELECT @somevariable = -1

INSERT INTO foo VALUES ( @somevariable )

I'll then change the value of @somevariable at runtime, depending on the value that I want in the particular situation. Since it's at the top of the script it's easy to see and remember.

How do I do the same with the PostgreSQL client psql?

Answer 1

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;