How do I declare variables in pgAdmin

Question

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.

Homozygote

Answer 1

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;

Quintero

this should be the correct answer, works perfectly in pgadmin – Belly

Doesn't work for me I get a syntax error – Aparri

Answer 2

\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 can use PL/pgSQL code in an anonymous code block (DO statement) or in a function or procedure.

Or you can (ab)use "customized options", for text-only server-side "variables", independent of the client:

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

Details:

• User-defined variables in PostgreSQL

Other options:

• Is there a way to define a named constant in a PostgreSQL query?

Bigot

Does anyone else think this is a ridiculous limitation in postgres? – Reginareginald

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

Nvm, found the answer here: postgresql.org/message-id/[email protected] 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; – Rici

Answer 3

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; 

Crockett

This works really well and FAST! – Wiener

Answer 4

Try with bellow structure:

DO $$
DECLARE
    a INT;
BEGIN
    a := 1;
END;
$$;

Kerbstone