PostgreSQL Returning id into array

Question

I am trying to create a function in postgreSQL where I need to input multiple rows, and upon insert, I need to get the id of each row so that I can use it later in the function to be inserted into another table.

The function I have below is partially what I am working with. I have wrapped it with a WITH clause so that I can use the webpage_ids later.

create or replace function insert_webpages (query_domain_id int, query_page_titles text[], query_page_slugs text[])
returns table (new_webpage_id int[]) as
$$
DECLARE new_webpage_id int[];

BEGIN
  WITH webpage_rows AS (
  INSERT INTO webpages (domain_id, title, slug)
  VALUES (query_domain_id, unnest(query_page_titles), unnest(query_page_slugs))
  RETURNING id into new_webpage_id
  )
...

The problem that I am currently facing is that I know that the last line does not input the all the ids into the new_webpage_id array.

So how do I fix it such that it inputs each id of the newly inputed row into the new_webpage_id array?

Stefanov.sm · Accepted Answer · 2022-01-13 18:49:18Z

2

The values expression is invalid, set-returning functions (i.e. unnest) are not allowed there. The CTE does not need an into clause inside but a select statement that follows it. Try

WITH webpage_rows(wr_id) AS
(
  INSERT INTO webpages (domain_id, title, slug)
  select query_domain_id, unnest(query_page_titles), unnest(query_page_slugs)
  returning id
)
select array_agg(wr_id) into new_webpage_id from webpage_rows;

Unrelated but I do not think that having a variable and return table column by the same name is a good idea.

answered Jan 13, 2022 at 18:49

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

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

3 Comments

Prajwal Over a year ago

Thank you so much for this answer. What do you mean by the variable having the same name? I'm using different names for them in the example above

Stefanov.sm Over a year ago

I mean returns table (new_webpage_id int[]) and DECLARE new_webpage_id int[];. It may be confusing to have the same new_webpage_id two times for different things. Does the answer solve your problem?

Prajwal Over a year ago

Oh right. I have changed that. Yes, it solves my problem. Thank you!

Edouard · Accepted Answer · 2022-01-13 17:42:05Z

1

Try this :

  WITH webpage_rows AS (
  INSERT INTO webpages (domain_id, title, slug)
  VALUES (query_domain_id, unnest(query_page_titles), unnest(query_page_slugs))
  RETURNING id -- id is to be replaced by the column of the table webpages and which must be aggregated into the array new_webpage_id
  )
SELECT array_agg(new_webpage_id)
  INTO new_webpage_id
  FROM webpage_rows

answered Jan 13, 2022 at 17:42

Edouard

7,1291 gold badge11 silver badges24 bronze badges

1 Comment

Prajwal Over a year ago

Thank you so much for this. It did the trick.

Collectives™ on Stack Overflow

PostgreSQL Returning id into array

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related