"INSERT INTO ... FETCH ALL FROM ..." не может быть скомпилирован
У меня есть функция в 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;
Но есть ли способ лучше?
@a_horse_with_no_name Думаю, это лучшее решение.
Первая функция должна возвращать рефкурсор по спецификации задачи.
В сторону: используйте UNION ALL или выражение VALUES вместо UNION в первом запросе. Но весь подход ошибочен. Какая спецификация задачи потребовала бы неадекватной техники?
@ErwinBrandstetter мы переписываем базу данных с MS SQL на Postgre. В MS SQL есть процедуры, поэтому одна процедура может возвращать несколько таблиц. Postgre не может, поэтому мы решили вернуть setof refcursor, 1 refcursor для 1 таблицы. Вот почему первая функция должен возвращает refcursor (или setof refcursor), а не table (). Но я не знаю адекватного способа вставить данные из рефкурсора в временную таблицу.
Я вижу, откуда вы. Новые SQL-процедуры в Postgres 11 предназначены также для возврата нескольких результатов в конечном итоге, но это еще не реализовано в стр. 11.
Ответы 2
К сожалению, INSERT и SELECT не имеют доступа к курсорам в целом.
Чтобы избежать дорогостоящего однорядного INSERT, вы можете использовать промежуточные функции с RETURNS TABLE и возвращать курсор в виде таблицы с RETURN QUERY. Видеть:
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();
Видеть:
Все еще не очень элегантно, но много быстрее однорядного INSERT.
Примечание. Поскольку источником является cursor, успешно выполняется только первый вызов. Повторное выполнение функции вернет пустой набор. Вам понадобится курсор с SCROLL вариант и переход к началу для повторных вызовов.
Но как мне написать такую посредническую функцию? Он должен читать из курсора и возвращать таблицу, поэтому он будет иметь SELECT ... FROM FETCH ALL ..., но такая конструкция не разрешена. Я знаю только одно решение, но это ужасно.
Спасибо, я написал промежуточную функцию с «RETURN QUERY FETCH ALL FROM ...» для преобразования данных курсора в таблицу, и это сработало.
@ ЕвгенийНоздрев: Да. И упростите создание временной таблицы. Я добавил еще несколько выше.
Эта функция делает INSERT INTO из refcursor. Для всех таблиц это универсальный. Единственное требование - все столбцы таблицы должны соответствовать столбцам рефкурсора по типам и порядку (не обязательно по именам).
to_json() позволяет преобразовать любые примитивные типы данных в строки с двойными кавычками "", которые позже заменяются на ''.
CREATE OR REPLACE FUNCTION public.insert_into_from_refcursor(_table_name text, _ref refcursor)
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
_sql text;
_sql_val text = '';
_row record;
_hasvalues boolean = FALSE;
BEGIN
LOOP --for each row
FETCH _ref INTO _row;
EXIT WHEN NOT found; --there are no rows more
_hasvalues = TRUE;
SELECT _sql_val || '
(' ||
STRING_AGG(val.value :: text, ',') ||
'),'
INTO _sql_val
FROM JSON_EACH(TO_JSON(_row)) val;
END LOOP;
_sql_val = REPLACE(_sql_val, '"', '''');
_sql_val = TRIM(TRAILING ',' FROM _sql_val);
_sql = '
INSERT INTO ' || _table_name || '
VALUES ' || _sql_val;
--RAISE NOTICE 'insert_into_from_refcursor(): SQL is: %', _sql;
IF _hasvalues THEN --to avoid error when trying to insert 0 values
EXECUTE (_sql);
END IF;
END;
$$;
Применение:
CREATE TABLE public.table1 (...);
PERFORM my_func_opening_refcursor();
PERFORM public.insert_into_from_refcursor('public.table1', 'name_of_refcursor_portal'::refcursor);
где my_func_opening_refcursor() содержит
DECLARE
_ref refcursor = 'name_of_refcursor_portal';
OPEN _ref FOR
SELECT ...;
Было бы намного проще, если бы первая функция также была определена как
returns table (...).