PostgreSQL - RETURNING INTO array

Question

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

It really depends on what you want to do with the collected IDs. Typically, a single query with data-modifying CTEs is the best solution. — Erwin Brandstetter
– Erwin Brandstetter, Commented Nov 19, 2014 at 11:55

Nick Barnes · Accepted Answer · 2021-03-17 11:13:29Z

There are a few ways to go about this.

Say you want to call some f(id) for each id affected by the UPDATE.

In PL/pgSQL:

$$ DECLARE found_id BIGINT; BEGIN FOR found_id IN (UPDATE child SET foo=bar RETURNING id) LOOP PERFORM f(found_id); END LOOP; END $$

In pure SQL:

WITH updated(found_id) AS ( UPDATE child SET foo=bar RETURNING id ) SELECT f(found_id) FROM updated;

If you want to collect all the found_ids in an array, you can simply:

$$ DECLARE array_var BIGINT[]; BEGIN WITH updated(found_id) AS ( UPDATE child SET foo=bar RETURNING id ) SELECT array_agg(found_id) FROM updated INTO array_var; END $$

Tomasz Siorek · Accepted Answer · 2014-11-18 22:56:10Z

Here is an example:

CREATE OR REPLACE FUNCTION exemplary ( parent_ids bigint[] ) RETURNS VOID AS $$ DECLARE _found_ids bigint[]; BEGIN WITH matching_children AS ( UPDATE child SET foo = 1 WHERE parent_id = ANY ( parent_ids ) RETURNING id ) SELECT array_agg ( id ) FROM matching_children INTO _found_ids; RAISE NOTICE '%', _found_ids; RETURN; END $$ LANGUAGE plpgsql;

Collectives™ on Stack Overflow

PostgreSQL - RETURNING INTO array

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related