How to get newly inserted row ids when inserting with execute?

Question

I have a function which returns newly inserted row count:

   BEGIN
   EXECUTE (

      SELECT format('INSERT INTO %1$s(%2$s) SELECT %2$s FROM %1$s WHERE budget_id = $1',
                    _tbl, string_agg(quote_ident(attname), ', '))
      FROM   pg_attribute
      WHERE  attrelid = _tbl
      AND    NOT attisdropped  -- no dropped (dead) columns
      AND    attnum > 0        -- no system columns
      AND    attname <> 'id'   -- exclude id column

      )
   USING _id;

   GET DIAGNOSTICS row_ct = ROW_COUNT;  -- directly assign OUT parameter
  END;

Instead, I have to return a list which contains newly inserted row ids. How can I do that?

I tried with result_oid,@@identity but I got no result.
This function is returning only one value. How can I get all the values?

CREATE OR REPLACE FUNCTION public.test2(_tbl regclass,_id integer)
RETURNS integer[]
LANGUAGE 'plpgsql'
VOLATILE
PARALLEL UNSAFE
COST 100
AS $BODY$DECLARE 
ab integer;

BEGIN
  EXECUTE (

  SELECT format('INSERT INTO %1$s(%2$s) SELECT %2$s FROM %1$s WHERE budget_id = $1 RETURNING id',
                _tbl, string_agg(quote_ident(attname), ', '))
  FROM   pg_attribute
  WHERE  attrelid = _tbl
  AND    NOT attisdropped  -- no dropped (dead) columns
  AND    attnum > 0        -- no system columns
  AND    attname <> 'id'   -- exclude id column
  )
 USING _id 
 into ab;

 RETURN ARRAY[ab];
END;$BODY$;

Erwin Brandstetter · Accepted Answer · 2018-05-30T12:52:32.407

To return a set, Use RETURN QUERY EXECUTE in a function that RETURNS SETOF integer:

CREATE OR REPLACE FUNCTION pubilc.test_set(_tbl regclass, _id int)
  RETURNS SETOF integer AS
$func$
BEGIN
   RETURN QUERY EXECUTE (
   WITH (
   SELECT format('INSERT INTO %1$s(%2$s)
                  SELECT %2$s FROM %1$s WHERE budget_id = $1
                  RETURNING id'
                , _tbl, string_agg(quote_ident(attname), ', '))
   FROM   pg_attribute
   WHERE  attrelid = _tbl
   AND    NOT attisdropped  -- no dropped (dead) columns
   AND    attnum > 0        -- no system columns
   AND    attname <> 'id'   -- exclude id from column list
   )
   USING _id;
END
$func$  LANGUAGE plpgsql;  -- don't quote the language name

Call:

SELECT * FROM pubilc.test_set('mytbl', 123);

To return an array, move the INSERT to a CTE and feed the result to an array constructor.

Then we don't need the function to RETURNS SETOF int[], because it always returns a single value. The simplest way is to define an OUT parameter and assign the result of the query:

CREATE OR REPLACE FUNCTION public.test_arr(_tbl regclass, _id int, OUT _ids int[]) AS
$func$
BEGIN
   EXECUTE (
   SELECT format('WITH ids AS (
                     INSERT INTO %1$s(%2$s)
                     SELECT %2$s FROM %1$s WHERE budget_id = $1
                     RETURNING id
                     )
                  SELECT ARRAY (TABLE ids)'
               , _tbl, string_agg(quote_ident(attname), ', '))
   FROM   pg_attribute
   WHERE  attrelid = _tbl
   AND    NOT attisdropped  -- no dropped (dead) columns
   AND    attnum > 0        -- no system columns
   AND    attname <> 'id'   -- exclude id from column list
   )
   USING _id
   INTO _ids;
END
$func$  LANGUAGE plpgsql;

Call:

SELECT public.test_arr('mytbl', 123);

How to get newly inserted row ids when inserting with execute?

1 Answers1