How do you use script variables in PostgreSQL?

Question

In MS SQL Server, 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 the particular situation. Since it's at the top of the script it's easy to see & remember.

How do I do the same with PostgreSQL?

Googling turned up PSQL variables, but it's implied that they can only be used within other slash commands, not in actual SQL.

EDIT: Found my own answers, and they're actually fairly complicated. Sort the posts older->newer to follow my discoveries.

Answer 1

Found my own answer further down that linked page:

An additional useful feature of psql variables is that you can substitute ("interpolate") them into regular SQL statements.

I tried this already and got a problem, but this suggests that my problem isn't related to the variable after all.

Answer 2

FWIW, the real problem was that I had included a semicolon at the end of my \set command:

\set owner_password 'thepassword';

The semicolon was interpreted as an actual character in the variable:

\echo :owner_password thepassword;

So when I tried to use it:

CREATE ROLE myrole LOGIN UNENCRYPTED PASSWORD :owner_password NOINHERIT CREATEDB CREATEROLE VALID UNTIL 'infinity';

...I got this:

CREATE ROLE myrole LOGIN UNENCRYPTED PASSWORD thepassword; NOINHERIT CREATEDB CREATEROLE VALID UNTIL 'infinity';

That not only failed to set the quotes around the literal, but split the command into 2 parts (the second of which was invalid as it started with "NOINHERIT").

The moral of this story: PostgreSQL "variables" are really macros used in text expansion, not true values. I'm sure that comes in handy, but it's tricky at first.

Answer 3

One final word on PSQL variables:

1. 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'

1. 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

Answer 4

You need to use one of the procedural languages such as PL/pgSQL not the SQL proc language. In PL/pgSQL you can use vars right in SQL statements. For single quotes you can use the quote literal function.

Answer 5

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;

... but, 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;