Multiple assignments in a single select

Question

I have written a function in PostgreSQL where I wish to assign multiple values to variables using a single select statement. I have declared a few variables and using a select I wish to assign values to those variables in the body of the function. Please find below the body of the function:

BEGIN        
select SUM(quantity) into v_quantity, MAX(price) into v_price from trade where id=4;
END

When I compile the function I am getting the following error

ERROR:  "max" is not a known variable
LINE 20:     select SUM(quantity) into v_quantity, max(price) into v_...

What could be the reason? Does PostgreSQL not allow multiple assignments through a single select?

Answer 1

Yes it does. To make it work you should have only a single select list and a single INTO clause like so:

BEGIN        
  select SUM(quantity), MAX(price) into v_quantity, v_price from trade where id=4;
END

Answer 2

You can use a variable of type record instead:

...
declare
    rec record;
begin      
    select sum(quantity) as v_quantity, max(price) as v_price into rec from trade where id=4;
    if rec.v_quantity > 100 then
    ...