Craig Walker Craig Walker - 6 months ago 79
SQL Question

How do you use script variables in PostgreSQL?

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




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

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;
Comments