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, Dec 26 '22 at 21:10
`vars` is a table, not a single record. There are no such variables in plain SQL. — Bergi, Dec 26 '22 at 21:15

Mureinik · Accepted Answer · 2022-12-26T21:19:07.710

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 '22 at 21:19

answered Dec 26 '22 at 21:12

Mureinik

297,002
52
306
350

I implemented your solution, it gives me the same error as mine. – NZJL Dec 26 '22 at 21:17
@NZJL ooh, the `as` clause is misplaced. Didn't even notice that, mea culpe. See my edited answer. – Mureinik Dec 26 '22 at 21:19

How to declare and use a variable in PostgreSQL?

1 Answers1