postgres: query results into variables to be used later

Question

I have what will end up being a complex query where certain parts will often be repeated. I would therefore like to store the results of some sub queries into variables which can then be used in the main query.

For example I would like to set the variable 'variable_id' to be equal to a SELECT query and variable_school_id to be equal to another SELECT query:

variable_id integer := (SELECT id FROM account WHERE email = '[email protected]');
variable_school_id integer := (SELECT school FROM account WHERE email = '[email protected]');

Then I would like to make use of those variables in a query that would look like:

select * from doctor where account_id = variable_id AND school = variable_school_id ;

How do I go about doing this?

Gordon Linoff · Accepted Answer · 2018-11-07 20:23:37Z

1

Can't you just use CTEs?

with params as (
      SELECT id, school
      FROM account
      WHERE email = '[email protected]'
     )
select d.*
from params cross join
     doctor d
     on d.account_id = params.id and d.school = params.school;

answered Nov 7, 2018 at 20:23

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

4 Comments

Charlie Over a year ago

That works for this basic example but I'm going to end up with a much more complex query with many more variables and CASE instances. So would like to do it with variables if possible?

Gordon Linoff Over a year ago

@Charlie . . . I don't understand what you need. You can make the params CTE as complex as you like.

Charlie Over a year ago

I didn't think that you could reference the params within a subquery. I get the error, ERROR: invalid reference to FROM-clause entry for table "params" HINT: There is an entry for table "ref_user", but it cannot be referenced from this part of the query.

Gordon Linoff Over a year ago

@Charlie . . . You have to include the parameters in any subquery where you reference them.

Collectives™ on Stack Overflow

postgres: query results into variables to be used later

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related