I want to store an update's RETURNING values into a data structure so that I can use it in a subsequent query.
In this example, I'm given a list of "parent_ids", and I want to find all children whose parent is in that array. Then, I wish to update some value on them, and do other stuff.
CREATE OR REPLACE FUNCTION plpgsql_is_really_great(parent_ids bigint[])
RETURNS void AS
$$
DECLARE
found_ids bigint[];
BEGIN
UPDATE child SET
foo = bar
FROM
(SELECT id
FROM child
WHERE parent_id=ANY(parent_ids)
) as children_ids
WHERE
child.id = children_ids.id
RETURNING children_ids.id INTO found_ids; -- ???
-- do more stuff with found_ids
$$ LANGUAGE plpgsql
There are a few ways to go about this.
Say you want to call some
f(id)
for eachid
affected by theUPDATE
.In PL/pgSQL:
In pure SQL:
If you want to collect all the
found_id
s in an array, you can simply: