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

Question

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?

user330315 · Accepted Answer · 2019-04-23 06:55:18Z

3

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;

answered Apr 23, 2019 at 6:55

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related