Как создать табличную функцию в PostgreSQL?

Question

[Антон]

Написал функцию:

CREATE OR REPLACE FUNCTION public.get_full_info_about_passenger(INTEGER)
    RETURNS SETOF RECORD
AS
$BODY$
BEGIN
    SELECT id FROM public.passengers;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

Пытаюсь вывести таблицу:

SELECT id FROM public.get_full_info_about_passenger(2);

Получаю:

a column definition list is required for functions returning "record"

SELECT id FROM public.get_full_info_about_passenger(2) f(id INTEGER);

Получаю:

ERROR: query has no destination for result data
Подсказка: If you want to discard the results of a SELECT, use PERFORM instead.
Где: PL/pgSQL function "get_full_info_about_passenger" line 8 at SQL statement

В чем проблема? Как создать табличную функцию?

Answer 1

[Melkij]

Извращенцы какие. Не используйте execute никогда, если без этого можно обойтись.

https://www.postgresql.org/docs/9.4/static/plpgsql...
return query select ....

Можно описать в хранимке, что именно она возвращает:

CREATE OR REPLACE FUNCTION public.get_full_info_about_passenger(INTEGER)
    RETURNS TABLE(
    id bigint
)
AS
$BODY$
BEGIN
    RETURN QUERY SELECT p.id FROM public.passengers p;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

И просто набор чисел:

CREATE OR REPLACE FUNCTION public.get_full_info_about_passenger(INTEGER)
    RETURNS setof bigint
AS
$BODY$
BEGIN
    RETURN QUERY SELECT id FROM public.passengers;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

Можно возвращать строки таблицы: (удобно, чтобы инкапсулировать какую-то заковыристую логику фильтрации)

CREATE OR REPLACE FUNCTION public.get_full_info_about_passenger(INTEGER)
    RETURNS setof public.passengers
AS
$BODY$
BEGIN
    RETURN QUERY SELECT * FROM public.passengers;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

PS: хинт, который может сэкономить некоторое время: return query не прерывает выполнение кода, после него жизнь есть. И можно использовать в одной хранимке несколько return query, они будут вести себя как union all

Comments

[Антон]

А как потом использовать функцию? Я переписал код, учитывая ваши замечания, но в итоге все равно получаю ошибку при выполнении:

SELECT * FROM public.get_full_info_about_passenger(2);

ERROR: column reference "id" is ambiguous
Подробности: It could refer to either a PL/pgSQL variable or a table column.
Где: PL/pgSQL function "get_full_info_about_passenger" line 4 at RETURN QUERY

[Антон]

CREATE OR REPLACE FUNCTION public.get_full_info_about_passenger(INTEGER)
	RETURNS TABLE(id INTEGER)
AS
$BODY$
BEGIN
	RETURN QUERY SELECT id FROM public.passengers;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

[Melkij]

- RETURN QUERY SELECT id FROM public.passengers;
+ RETURN QUERY SELECT p.id FROM public.passengers p;

Прошу прощения, запамятовал поправить этот момент. Используете правильно.

[Антон]

Melkij: воооо :)

Answer 2

[Андрей Буров]

pgcookbook.ru/article/return_from_function.html

Comments

[Антон]

Да это бред какой-то. не выходит у меня ничего. Снова:

ERROR: column reference "id" is ambiguous
Подробности: It could refer to either a PL/pgSQL variable or a table column.
Где: PL/pgSQL function "get_full_info_about_passenger" line 4 at SQL statement

[Андрей Буров]

Антон:

burik=# create table passengers(id integer, value text);
CREATE TABLE
burik=# insert into passengers (id, value) VALUES(1, 'hi');
INSERT 0 1
burik=# insert into passengers (id, value) VALUES(2, 'hi2');
INSERT 0 1
burik=# insert into passengers (id, value) VALUES(3, 'hi3');
INSERT 0 1
burik=# create or replace function public.get_full_info_about_passenger(INTEGER) returns setof record as
burik-# '
burik'# declare
burik'# r record;
burik'# begin
burik'# for r in EXECUTE ''select id from passengers'' loop
burik'# return next r;
burik'# end loop;
burik'# return;
burik'# end
burik'# '
burik-# language 'plpgsql';
CREATE FUNCTION
burik=# select id from get_full_info_about_passenger(1) as f(id INTEGER);
 id 
----
  1
  2
  3
(3 rows)