Local variable in Posgresql

Question

I'm moving from SQL Server to Postgresql. In SQL Server I have a code like this:

Declare @usr int = (select id from users where name = 'root');

Select * from info1 where usr = @usr;
Select * from info2 where usr = @usr;
Select * from info3 where usr = @usr;

I want to write such a code in Postgres. I found no local variables in Postgresql. I try this way:

create temporary table _usr on commit drop as
select id from users where name = 'root';

select * from info1 where usr = (select id from _usr);
select * from info2 where usr = (select id from _usr);
select * from info3 where usr = (select id from _usr);

The question is: Is where an any other way to save a local variables other than a temporary tables? May be I missing some type of objects in Postgresql?

PS. Data structure for this example:

create table users
(
    id int not null,
    name varchar(100)
);

create table info1
(
    usr int not null,
    i1a text,
    i1b text
);

create table info2
(
    usr int not null,
    i2a text,
    i2b text,
    i2c text
);

create table info3
(
    usr int not null,
    i3a text,
    i2b decimal(10),
    i2c decimal(10),
    i2d decimal(10)
);

insert into users values (1, 'root'), (2, 'admin');
insert into info1 values (1, 'one', 'first'), (1, 'two', 'second');
insert into info2 values (1, '1', '11', 't111'), (1, '2', '22', '222');
insert into info3 values (1, '1', 1, 10, 100), (1, '2', 2, 20, 200);
insert into info3 values (2, '3', 3, 30, 300);

UPDATE. The way with anonymous code block seems to be much uglier:

do $$
declare _usr int;
begin
    _usr = (select id from users where name = 'root');

    create temporary table _info1 on commit drop as
    select * from info1 where usr = _usr;

    create temporary table _info2 on commit drop as
    select * from info2 where usr = _usr;

    create temporary table _info3 on commit drop as
    select * from info3 where usr = _usr;
end
$$;

select * from _info1;
select * from _info2;
select * from _info3;

https://stackoverflow.com/questions/1490942/how-to-declare-a-variable-in-a-postgresql-query — , Aug 30 '22 at 15:01
Use the [DO](https://www.postgresql.org/docs/current/sql-do.html)(anonymous function) example from the link @a_horse_with_no_name posted. — Adrian Klaver, Aug 30 '22 at 15:07

score 0 · Answer 1 · answered Aug 30 '22 at 16:42

There are no variables in SQL in PostgreSQL.

I think the best and simplest solution would be to repeat the (cheap) subquery:

SELECT info1.+
FROM info1
   JOIN users ON users.name = info1.usr
WHERE users.name = 'root';

SELECT info2.+
FROM info2
   JOIN users ON users.name = info2.usr
WHERE users.name = 'root';

SELECT info3.+
FROM info3
   JOIN users ON users.name = info3.usr
WHERE users.name = 'root';

If the three tables have the same structure, you could UNION the queries and use a CTE:

WITH u AS (
   SELECT id FROM users WHERE name = 'root'
)
SELECT info1.*
FROM info1
   JOIN u ON u.id = info1.usr
UNION ALL
    SELECT info2.*
FROM info2
   JOIN u ON u.id = info2.usr
UNION ALL
SELECT info3.*
FROM info3
   JOIN u ON u.id = info3.usr;

Local variable in Posgresql

1 Answers1