PostgreSQL Returning id into array-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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.