22

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
dagda1
  • 26,856
  • 59
  • 237
  • 450

3 Answers3

31

When trying to run scripts in pgAdmin I wanted the something similar where i wanted to store a value to use across multiple queries. This is what I found which is similar to SQL

set session vars.batch_id = '82';

select * from batches where batch_id = current_setting('vars.batch_id')::int;
FPVjeary
  • 423
  • 5
  • 8
16

\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 of the client in use:

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

Details in this related answer:

There was also pgScript, a local scripting extension of the pgAdmin3 query tool, where you could 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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 41
    Does anyone else think this is a ridiculous limitation in postgres? – Jeremy Holovacs Mar 13 '16 at 15:42
  • 1
    `declare @mytbl, @maxid; set @mytbl = 'sometable'; set @maxid = someid; select count(*) from @mytbl where id <= @maxid;` when I click on "execute PgScript" this just prints the SQL statement with the variables replaced with values in the Messages window, but does not return the results itself. – arun Nov 02 '16 at 22:02
  • 2
    Nvm, found the answer here: https://www.postgresql.org/message-id/201001231852.35717.j.kobal@gmx.com Need to set the output to a variable and print it, so like this: `declare @mytbl, @maxid; set @mytbl = 'sometable'; set @maxid = 2500; set @res = select count(*) from @mytbl where id <= @maxid; print @res;` – arun Nov 02 '16 at 22:08
2

An alternative is to create a view.

-- create view to store variable list
create view script_variables as select 'soifsdaofisd.gmail.com' as message_id;
-- run your script
select * from emails where message_id = (select message_id from script_variables);
-- clean up 
drop view script_variables;
Tim Kessler
  • 21
  • 1