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

Ответ 1

(Все это проверено с помощью 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
$$;

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

Ответ 2

^{All previously existing answers are outdated or were inefficient to begin with.}

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

Функция PL/pgSQL

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

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

В Postgres 9.6 или более поздней версии вы также можете добавить PARALLEL SAFE.

Звоните:

SELECT * FROM f_foo();

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

• Используйте 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;

Ответ 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()

Ответ 4

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

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

Ответ 5

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;