How do I declare variables in pgAdmin

postgresql variables psql pgadmin
dagda1 picture dagda1 · May 9, 2014 · Viewed 24.9k times · Source

If I am in the psql terminal then I can declare and use a variable like this:

\set message_id soifsdaofisd.gmail.com;
select * from emails where message_id = ':message_id';

How can I do this in pgAdmin?

I get an error when ever I try this in pgAdmin:

ERROR: syntax error at or near "CALAdkA4YC0" LINE 3: set message_id soifsdaofisd.gmail.com.

Answer

Erwin Brandstetter picture Erwin Brandstetter · May 21, 2014

\set is a feature of psql (the interactive command line terminal) and not available in pgAdmin.

PostgreSQL does not normally use variables in plain SQL. You would use PL/pgSQL code in an anonymous block (DO statement) or in a function.

However, you can (ab)use customized options, for server-side "variables", independent the client in use:

SET foo.test = 'SELECT bar FROM baz';
SELECT current_setting('foo.test');

Details in this related answer:

And there is also (was) pgScript, a local scripting extension of the pgAdmin3 query tool, where you can use local variables, comparable to what you can do in psql. The manual:

You can run pgScript scripts by selecting Execute pgScript from the Query menu instead of Execute, or you press the Execute pgScript toolbar button, or you press the F6 function key.

But pgAdmin3 is unmaintained now, and pgAdmin4 does not include pgScript. Wasn't all that useful.

Related questions

How do you use script variables in psql?

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 …

Read More
How do you use variables in a simple PostgreSQL script?

For example, in MS-SQL, you can open up a query window and run the following: DECLARE @List AS VARCHAR(8) SELECT @List = 'foobar' SELECT * FROM dbo.PubLists WHERE Name = @List How is this done in PostgreSQL? Can it be done?

Read More
Loop on tables with PL/pgSQL in Postgres 9.0+

I want to loop through all my tables to count rows in each of them. The following query gets me an error: DO $$ DECLARE tables CURSOR FOR SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg_%' ORDER BY …

Read More