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 a 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 can be created by using the \set command, like this :

\set myvariable value

 Then, it can be substituted, this way:

SELECT * FROM :myvariable.table1;

Or 

SELECT * FROM table1 WHERE :myvariable IS NULL;

edit: In 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, like this :

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

Then you must include the quotes in the variable itself as the above query will not work. 

Instead, you can define your variable like this:

\set myvariable 'value'

However, if you want to make the string from an existing variable, Then use this:

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

Now you are having both the quoted and unquoted variable of the same string! And you can do in the similar manner  :

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