How to get newly inserted row ids when inserting with execute? 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 &gt; 0        -- no system columns
      AND    attname &lt;&gt; '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 &gt; 0        -- no system columns
  AND    attname &lt;&gt; 'id'   -- exclude id column
  )
 USING _id 
 into ab;

 RETURN ARRAY[ab];
END;$BODY$;
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 &gt; 0        -- no system columns
   AND    attname &lt;&gt; '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 &gt; 0        -- no system columns
   AND    attname &lt;&gt; 'id'   -- exclude id from column list
   )
   USING _id
   INTO _ids;
END
$func$  LANGUAGE plpgsql;


Call:

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


Related:


RETURN value directly from INSERT with RETURNING clause
Find changed columns for near-identical rows across tables
Why is array_agg() slower than the non-aggregate ARRAY() constructor?