Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

How to declare and use a variable in PostgreSQL?

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:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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

>Solution :

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
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading