0

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$;
  • Try the RETURNING clause of INSERT. – dezso May 29 '18 at 11:36
  • can you please give me example ?? I tried with ('INSERT INTO %1$s(%2$s) SELECT %2$s FROM %1$s WHERE budget_id = $1 RETURNING id) SELECT array_agg(id) INTO abcd FROM ins' but didn't get any result. – somil May 29 '18 at 12:10
  • When you write I have to return a list, do you mean an array, a set or an actual (comma-separated) list? – Erwin Brandstetter May 29 '18 at 22:45
  • @ErwinBrandstetter yes i mean an array – somil May 30 '18 at 7:16
  • I added an array variant. – Erwin Brandstetter May 30 '18 at 12:52
2

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);

Related:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.