Returning an array of inserted sequence values-CodePudding

I have an insert statement that may insert single or multiple records into a table with an INSERT statement. I need to know what the primary id value is for each new row.

Tried adding the following to the insert statement:

RETURNING array_agg(new_seq_id) INTO new_ids

Get the following error:

[42803] ERROR: aggregate functions are not allowed in RETURNING

Is there a way to get an array into the returning statements of all the new sequence values?

CodePudding user response：

After returning newly generated (and actually inserted!) IDs in a data-modifying CTE (like others provided), to insert into a table, you need an INSERT statement:

WITH cte AS (
   INSERT ...
   RETURNING new_seq_id
   )
INSERT INTO tbl(new_ids)
SELECT ARRAY(TABLE cte)

TABLE cte is the short form of SELECT * FROM cte.

An ARRAY constructor is typically a bit faster than the (more versatile) array_agg() aggregate function. See:

Why is array_agg() slower than the non-aggregate ARRAY() constructor?

Only makes sense if you, indeed, need to return a single array. You might just return a set of rows with the RETURNING clause.

CodePudding user response：

Yes, you can use a data modifying CTE.

with t as
(
  -- your insert statement
  RETURNING new_seq_id
) 
select array_agg(new_seq_id) from t into new_ids;

CodePudding user response：

You need to wrap it into a common table expression:

with new_rows as (
  insert into (...)
  values (...)
  returning new_seq_id
)
select array_agg(new_seq_id)
  into new_ids
from new_rows;