Select and Update in Stored Functions Postgresql

Question

I created a stored function for fetching data:

CREATE OR REPLACE FUNCTION sold_quantity()  
RETURNS TABLE(  
 invoiceid BIGINT,  
 itemid BIGINT,  
 sum_sold_quantity NUMERIC)  
AS $$  
BEGIN  
 RETURN QUERY SELECT  
 invoice_id as invoiceid, item_id as itemid, sum(sold_quantity) as  
 sum_sold_quantity  
 FROM  
 invoice_item  
 WHERE  
 status='sold'  
 GROUP BY  
 invoice_id, item_id;  
END; $$  

LANGUAGE 'plpgsql';

How to store this data and using it to update the table below?

UPDATE invoice_item 
    SET sold_quantity = sum_sold_quantity 
where invoice_id=invoiceid 
  and item_id = itemid;

How can we club these two queries (Select and update) in one stored function and execute to fetch all 500k records with the above three columns and update batch wise (like 10 000 records)

Unrelated, but: using PL/pgSQL for a simply query is overkill. Using a language sql function would be more efficient — user330315
– user330315, Commented Nov 19, 2018 at 13:31
please, don't do it - don't write procedures just for wrapping SELECT command. It is significant performance antipattern - use views instread. — Pavel Stehule
– Pavel Stehule, Commented Nov 20, 2018 at 5:00

user330315 · Accepted Answer · 2018-11-19 13:35:25Z

1

You can use the function directly in the UPDATE statement

UPDATE invoice_item ii
    SET sold_quantity = sq.sum_sold_quantity 
from sold_quantity() as sq
where ii.invoice_id = sq.invoiceid 
  and ii.item_id = sq.itemid;

For just 500.000 rows, I wouldn't bother doing any batching at all. Just update all rows in a single statement.

In fact you don't really need a function to begin with:

UPDATE invoice_item ii
    SET sold_quantity = sq.sum_sold_quantity 
from (
  select invoice_id as invoiceid, item_id as itemid, sum(sold_quantity) as  sum_sold_quantity  
  FROM  invoice_item  
  WHERE status='sold'  
  GROUP BY  invoice_id, item_id;  
) as sq
where ii.invoice_id = sq.invoiceid 
  and ii.item_id = sq.itemid;

answered Nov 19, 2018 at 13:35

user330315

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

1 Comment

Gagan Mesala Over a year ago

can you please look into this query. stackoverflow.com/questions/53387137/…

Collectives™ on Stack Overflow

Select and Update in Stored Functions Postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related