CODEWITHSUNDEEP
sqlpostgresqlfunction
itaied
itaied

Reputation: 7107

How to insert multiple rows in postgresql using CTE (WITH)

I am trying to write a function that receive an array of ids (posts),
and insert all of them to another table (offer).
I need the ids from the newly created rows (offers) for more processing later.

I'm getting an error using WITH

create function foo (post_ids text[])
returns text[]
as $$
  with
  offer_ids as (insert into app.offer (user_email, post_id)
                  values ('[email protected]', unnest(foo.post_ids))
                  returning id),
  ...
  select array_agg(id) from offer_ids;
$$ language sql security definer;

Error

ERROR:  more than one row returned by a subquery used as an expression

How can I return multiple rows from a WITH and use it later?

Upvotes: 0

Views: 366

Answers (1)

user330315
user330315

Reputation:

I don't think the error comes from the CTE part, but from the INSERT part. Set returning functions like unnest have to be used as part of a SELECT statement:

with offer_ids as (
     insert into app.offer (user_email, post_id)
     select '[email protected]', f.pid
     from unnest(foo.post_ids) as f(pid)
     returning id
),
...
select array_agg(id) 
from offer_ids;

Upvotes: 3

Related Questions