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

Tiemroth

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. – Loxodromic

Answer 1

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
$$

Ding

Answer 2

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;

Pasta