postgresql - "INSERT INTO ... FETCH ALL FROM ..."无法编译

Question

我在 PostgreSQL 9.6 上有一些返回游​​标的函数(refcursor):

CREATE OR REPLACE FUNCTION public.test_returning_cursor()
  RETURNS refcursor
IMMUTABLE
LANGUAGE plpgsql
AS $$
DECLARE
  _ref refcursor = 'test_returning_cursor_ref1';
BEGIN
  OPEN _ref FOR
  SELECT 'a' :: text AS col1
  UNION
  SELECT 'b'
  UNION
  SELECT 'c';

  RETURN _ref;
END
$$;

我需要编写另一个函数，在其中创建一个临时表并将来自此 refcursor 的所有数据插入其中。但是 INSERT INTO ... FETCH ALL FROM ... 似乎是不可能的。这样的函数无法编译:

CREATE OR REPLACE FUNCTION public.test_insert_from_cursor()
  RETURNS table(col1 text)
IMMUTABLE
LANGUAGE plpgsql
AS $$
BEGIN
  CREATE TEMP TABLE _temptable (
    col1 text
  ) ON COMMIT DROP;

  INSERT INTO _temptable (col1)
  FETCH ALL FROM "test_returning_cursor_ref1";

  RETURN QUERY
  SELECT col1
  FROM _temptable;
END
$$;

我知道我可以使用:

FOR _rec IN
  FETCH ALL FROM "test_returning_cursor_ref1"
LOOP
  INSERT INTO ...
END LOOP;

但是有更好的方法吗？

Answer 1

不幸的是，INSERT 和 SELECT 不能访问整个游标。

为避免昂贵的单行 INSERT，您可以使用 RETURNS TABLE 的中间函数，并使用 RETURN QUERY 将游标作为表返回。见:

• Return a query from a function?

CREATE OR REPLACE FUNCTION f_cursor1_to_tbl()
  RETURNS TABLE (col1 text) AS
$func$
BEGIN
   -- MOVE BACKWARD ALL FROM test_returning_cursor_ref1;  -- optional, see below

   RETURN QUERY
   FETCH ALL FROM test_returning_cursor_ref1;
END
$func$  LANGUAGE plpgsql;  -- not IMMUTABLE

然后直接创建临时表，如:

CREATE TEMP TABLE t1 ON COMMIT DROP
AS SELECT * FROM f_cursor1_to_tbl();

参见:

• Creating temporary tables in SQL

仍然不是很优雅，但是比单行INSERT快很多。

注意:由于源是一个游标，所以只有第一次调用会成功。第二次执行该函数将返回一个空集。您需要一个带有 SCROLL option 的光标并移动到重复调用的开头。