How to declare and use a variable in PostgreSQL?

Question

I'm new to PostgreSQL, so maybe my question is unconvinced. But what I want is to put the result of one query into a variable and then used it again in another query. I run my query inside pgAdmin4, Below is my query:

Also I tried those solution1, solution2 without achieving my goal.

WITH vars AS (
    (select count(*) from employee) AS vars_id
)
select 
    *
from 
    employee
where
    id=vars.vars_id;

The error is:

ERROR:  syntax error at or near "AS"
LINE 2:  (select count(*) from employee) AS vars_id
                                         ^
SQL state: 42601
Character: 49

where id=vars.vars_id; => where id = (SELECT vars.vars_id FROM vars); — Lukasz Szozda
– Lukasz Szozda, Commented Dec 26, 2022 at 21:10
vars is a table, not a single record. There are no such variables in plain SQL. — Bergi
– Bergi, Commented Dec 26, 2022 at 21:15

Mureinik · Accepted Answer · 2022-12-26 21:19:07Z

2

The result of a CTE is a table expression. You can't just refer to it as a scalar, you need to query from it:

WITH vars AS (
    SELECT COUNT(*) AS vars_id FROM employee
)
SELECT *
FROM   employee e
JOIN   vars ON e.id = vars.vars_id

edited Dec 26, 2022 at 21:19

answered Dec 26, 2022 at 21:12

Mureinik

316k54 gold badges399 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

NZJL Over a year ago

I implemented your solution, it gives me the same error as mine.

Mureinik Over a year ago

@NZJL ooh, the as clause is misplaced. Didn't even notice that, mea culpe. See my edited answer.

Collectives™ on Stack Overflow

How to declare and use a variable in PostgreSQL?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related