Возврат setof записи (виртуальной таблицы) из функции

Мне нужна функция Postgres для возврата виртуальной таблицы (как в Oracle) с пользовательским содержимым. Таблица будет иметь 3 столбца и неизвестное количество строк.

Я просто не мог найти правильный синтаксис в интернете.

Вообразите это:

CREATE OR REPLACE FUNCTION "public"."storeopeninghours_tostring" (numeric)
  RETURNS setof record AS
DECLARE
  open_id ALIAS FOR $1;
  returnrecords setof record;
BEGIN
  insert into returnrecords('1', '2', '3');
  insert into returnrecords('3', '4', '5');
  insert into returnrecords('3', '4', '5');
  RETURN returnrecords;
END;

Как это написано правильно?

Источник

6 ответов

Решение

(Все это протестировано с postgresql 8.3.7- у вас есть более ранняя версия? Просто посмотрите, как вы используете "ALIAS FOR $1")

CREATE OR REPLACE FUNCTION storeopeninghours_tostring(numeric)
 RETURNS SETOF RECORD AS $$
DECLARE
 open_id ALIAS FOR $1;
 result RECORD;
BEGIN
 RETURN QUERY SELECT '1', '2', '3';
 RETURN QUERY SELECT '3', '4', '5';
 RETURN QUERY SELECT '3', '4', '5';
END
$$;

Если у вас есть возвращаемая запись или переменная строки (вместо результата запроса), используйте "RETURN NEXT" вместо "RETURN QUERY".

Чтобы вызвать функцию, вам нужно сделать что-то вроде:

select * from storeopeninghours_tostring(1) f(a text, b text, c text);

Таким образом, вы должны определить, что вы ожидаете от схемы выходной строки функции в запросе. Чтобы избежать этого, вы можете указать выходные переменные в определении функции:

CREATE OR REPLACE FUNCTION storeopeninghours_tostring(open_id numeric, a OUT text, b OUT text, c OUT text)
 RETURNS SETOF RECORD LANGUAGE 'plpgsql' STABLE STRICT AS $$
BEGIN
 RETURN QUERY SELECT '1'::text, '2'::text, '3'::text;
 RETURN QUERY SELECT '3'::text, '4'::text, '5'::text;
 RETURN QUERY SELECT '3'::text, '4'::text, '5'::text;
END
$$;

(не совсем уверен, почему требуются дополнительные::text приведения... '1' по умолчанию может быть varchar?)

Источник

Все существующие в настоящее время ответы устарели или были неэффективны с самого начала.

Предполагая, что вы хотите вернуть три integer колонны.

Функция PL/pgSQL

Вот как вы делаете это с современным PL/pgSQL (PostgreSQL 8.4 или новее):

CREATE OR REPLACE FUNCTION f_foo(open_id numeric)
  RETURNS TABLE (a int, b int, c int) AS
$func$
BEGIN
   -- do something with open_id?
   RETURN QUERY VALUES
     (1,2,3)
   , (3,4,5)
   , (3,4,5);
END
$func$  LANGUAGE plpgsql IMMUTABLE ROWS 3;

Вызов:

SELECT * FROM f_foo(1);

Основные моменты

  • использование RETURNS TABLE определить специальный тип строки для возврата.
    Или же RETURNS SETOF mytbl использовать предопределенный тип строки.

  • использование RETURN QUERY вернуть несколько строк одной командой.

  • Использовать VALUES Выражение для ввода нескольких строк вручную. Это стандартный SQL, который существует всегда.

  • Используйте имя параметра (open_id numeric) вместо ALIAS , что не рекомендуется для стандартных имен параметров. В примере параметр не используется, а только шум...

  • Нет необходимости заключать в кавычки совершенно легальные идентификаторы. Двойные кавычки нужны только для принудительного использования незаконных имен (смешанный регистр, недопустимые символы или зарезервированные слова).

  • Функция волатильности может быть IMMUTABLE, так как результат никогда не меняется.

  • ROWS 3 не является обязательным, но поскольку мы знаем, сколько строк возвращено, мы могли бы также объявить это Postgres. Может помочь планировщику запросов выбрать лучший план.

Простой SQL

Для простого случая, подобного этому, вы можете использовать вместо этого простой SQL-оператор:

VALUES (1,2,3), (3,4,5), (3,4,5)

Или, если вы хотите (или должны) определить конкретные имена и типы столбцов:

SELECT *
FROM  (
   VALUES (1::int, 2::int, 3::int)
        , (3, 4, 5)
        , (3, 4, 5)
   ) AS t(a, b, c);

Функция SQL

Вы можете обернуть его в простую функцию SQL. Пример без параметра функции, так как он не используется:

CREATE OR REPLACE FUNCTION f_foo()
   RETURNS TABLE (a int, b int, c int) AS
$func$
   VALUES (1, 2, 3)
        , (3, 4, 5)
        , (3, 4, 5);
$func$  LANGUAGE sql IMMUTABLE ROWS 3;
Источник

Я довольно часто использую временные таблицы в своих функциях. Вам необходимо создать тип возврата в базе данных, а затем создать переменную этого типа для возврата. Ниже приведен пример кода, который делает именно это.

CREATE TYPE storeopeninghours_tostring_rs AS
(colone text,
 coltwo text,
 colthree text
);

CREATE OR REPLACE FUNCTION "public"."storeopeninghours_tostring" () RETURNS setof storeopeninghours_tostring_rs AS
$BODY$
DECLARE
  returnrec storeopeninghours_tostring_rs;
BEGIN
    BEGIN 
        CREATE TEMPORARY TABLE tmpopeninghours (
            colone text,
            coltwo text,
            colthree text
        );
    EXCEPTION WHEN OTHERS THEN
        TRUNCATE TABLE tmpopeninghours; -- TRUNCATE if the table already exists within the session.
    END;
    insert into tmpopeninghours VALUES ('1', '2', '3');
    insert into tmpopeninghours VALUES ('3', '4', '5');
    insert into tmpopeninghours VALUES ('3', '4', '5');

    FOR returnrec IN SELECT * FROM tmpopeninghours LOOP
        RETURN NEXT returnrec;
    END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;


select * from storeopeninghours_tostring()
Источник 
CREATE OR REPLACE FUNCTION foo(open_id numeric, OUT p1 varchar, OUT p2 varchar, OUT p3 varchar) RETURNS SETOF RECORD AS $$
BEGIN
  p1 := '1'; p2 := '2'; p3 := '3';
  RETURN NEXT; 
  p1 := '3'; p2 := '4'; p3 := '5';
  RETURN NEXT; 
  p1 := '3'; p2 := '4'; p3 := '5';
  RETURN NEXT; 
  RETURN;
END;
$$ LANGUAGE plpgsql;
Источник

Для тех, кто приземлился здесь, ищет MSSQL-эквивалент создания временной таблицы и выгрузки ее записей в качестве вашего возврата... которого нет в PostgreSQL:( - вы должны определить тип возврата. Есть два способа сделать это во время создания функции или во время создания запроса.

Смотрите здесь: http://wiki.postgresql.org/wiki/Return_more_than_one_row_of_data_from_PL/pgSQL_functions

Источник

Начните с PostgreSQL 11, вы можете использовать процедуру.

      CREATE OR REPLACE PROCEDURE f_foo()  LANGUAGE plpgsql
   AS
$$
BEGIN
drop table if exists foo cascade;
create temp table  IF NOT EXISTS foo (a int, b int, c int);
insert into foo VALUES
  (1,2,3)
, (3,4,5)
, (3,4,5)
;
END
$$;

Когда вам нужна временная таблица, вы можете вызвать ее.

      call f_foo();

Он удалит таблицу foo , если она существует.

Источник
Другие вопросы по тегам