PostgreSQL - RETURNING INTO array
I want to store the RETURNING update into a data structure so that I can use it in the next request.
In this example, I am assigned the list "parent_ids", and I want to find all children whose parent is in this array. Then I want to update some values and do other things.
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
+3
source to share
2 answers
There are several ways to get around this. Let's say you want to name f()
for everyone id
.
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 everything found_id
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
$$
+5
source to share
Here's 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;
+1
source to share