INSERT INTO with SELECT FROM into variable-CodePudding

I want to insert data into a table and get the newly inserted data into a variable, so I can call a function with that data.

I have a table called foo and a function that inserts data to it called insert_data_to_foo.

After inserting the data, I would like to call another function with that data.

I thought, I could just DECLARE an array variable and fill it using RETURNING.

But I get ERROR: more than one row returned

Source

CREATE OR REPLACE FUNCTION "insert_data_to_foo"(entries my_input_type[])
  RETURNS void as
  $BODY$
  DECLARE
    _added foo[];
  BEGIN
    -- insert data
    INSERT INTO foo(message)
    SELECT "message" FROM unnest(entries)
    RETURNING * INTO _added; -- << ERROR: more that one rows returned

    -- call function with _added data
    perform ....
  END;
  $BODY$
  LANGUAGE plpgsql;

CodePudding user response：

This can be achieved by putting the INSERT into a CTE which can return the rows. Then you can SELECT from that CTE and aggregate the rows into an array:

CREATE OR REPLACE FUNCTION "insert_data_to_foo"(entries my_input_type[])
  RETURNS foo[]
as
  $BODY$
  DECLARE
    _added foo[];
  BEGIN
    -- insert data
    with new_rows as (
      INSERT INTO foo(message)
      SELECT u.message FROM unnest(entries) as u(message)
      RETURNING *
    )
    select array_agg(new_rows)
      into _added
    from new_rows;
    ...
END;
$BODY$
LANGUAGE plpgsql;