How to create a table function in PostgreSQL?

Question

PostgreSQL

Wrote a function:

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;

I'm trying to display a table:

SELECT id FROM public.get_full_info_about_passenger(2);

I get:

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

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

I get:

ERROR: query has no destination for result data
Hint: If you want to discard the results of a SELECT, use PERFORM instead.
Where: PL/pgSQL function "get_full_info_about_passenger" line 8 at SQL statement

What is the problem? How to create a table function?

Answer 1

What perverts. Never use execute if you can do without it.
https://www.postgresql.org/docs/9.4/static/plpgsql...
You can describe in the store what exactly it returns:

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;

And just a bunch of numbers:

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;

You can return table rows: (handy to encapsulate some tricky filtering logic)

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: a hint that can save some time: return query does not interrupt code execution, there is life after it. And you can use several return queries in one store, they will behave like union all

Answer 2

pgcookbook.ru/article/return_from_function.html