使用執行插入時如何獲取新插入的行ID？

我有一個返回新插入的行數的函式：

  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;

相反，我必須返回一個包含新插入的行 ID 的列表。我怎樣才能做到這一點？

我試過了，result_oid,@@identity但沒有結果。

這個函式只返回一個值。我怎樣才能得到所有的值？

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

要返回一個集合，請RETURN QUERY EXECUTE在一個函式中使用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

稱呼：

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

要返回一個數組，請將 移動INSERT到 CTE 並將結果提供給數組建構子。

然後我們不需要函式 to RETURNS SETOF int[]，因為它總是返回一個值。最簡單的方法是定義一個OUT參數並分配查詢的結果：

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;

稱呼：

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

有關的：

• RETURN 值直接來自帶有 RETURNING 子句的 INSERT
• 跨表查找幾乎相同行的更改列
• 為什麼 array_agg() 比非聚合 ARRAY() 建構子慢？

引用自：https://dba.stackexchange.com/questions/208095