Postgres: How to populate an array after insert

Question

Imagine we have a simple table

create table a (id serial, data text);

and the following function:

create or replace function f()
returns SETOF int As
$BODY$
DECLARE
  l_arr int[];
BEGIN

  insert into a(data) 
  values ('a') 
  returning array_agg(data) into l_arr; --THIS DOES NOT WORK

  RETURN l_arr;

END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER
  COST 100

How one may gather inserted values into an array in the same SQL statement?

Timofey · Accepted Answer · 2012-10-09 14:01:32Z

1

It appears, that it is possible to use RETURN QUERY without intermediate array:

create or replace function f()
returns SETOF int As
$BODY$
BEGIN
  RETURN QUERY
  insert into a(data) 
  values ('a') 
  returning id;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER
  COST 100

edited Oct 9, 2012 at 14:01

answered Oct 9, 2012 at 13:05

Timofey

2,5183 gold badges38 silver badges54 bronze badges

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

2 Comments

András Váczi Over a year ago

This will return a set, not an array, so this is not a good answer for your question :)

Timofey Over a year ago

@dezso it is the answer because the function in the question aims to return set ;)

Collectives™ on Stack Overflow

Postgres: How to populate an array after insert

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related