Функции PostgreSQL, возвращающие набор данных(draft).

В PostgreSQL, в отличие от MS SQL Server, нельзя просто в коде передать параметры в какой-то скрипт и выполнить его, вернув данные, как, например, в MS Sql Server Management Studio. Приходится писать хранимую функцию.

Далее используются данные из демонстрационной базы https://postgrespro.ru/education/demodb

На примере запроса к одной таблице (на самом деле это не таблица, а представление, не суть),

SELECT aircraft_code, model, "range" FROM bookings.aircrafts where "range" > 3000

обернем запрос в функцию.

CREATE function bookings.uf_get_aircrafts (p_range int default 10000)

returns table (aircraft_code text, model text, "range" int) as $

select aircraft_code::text, model, "range" 
FROM bookings.aircrafts where "range" > p_range;

$ LANGUAGE sql stable;

--вызов функции с параметром

select * from bookings.uf_get_aircrafts (3000)

--вернулось 6 строк

--вызов функции с параметром по дефолту

select * from bookings.uf_get_aircrafts()

--вернулась 1 строка

Указание категории изменчивости stable (по дефолту функция считается volatile) позволяет развернуть определение функции при выполнении в запрос. Посмотрите на ожидаемые планы исполнения без этого:

--без указания stable
explain
select * from bookings.uf_get_aircrafts (3000)
--Function Scan on uf_get_aircrafts  (cost=0.25..10.25 rows=1000 width=68)

--с указанием stable
explain
select * from bookings.uf_get_aircrafts (3000)
--Seq Scan on aircrafts_data ml  (cost=0.00..1.88 rows=3 width=68)
  --Filter: (range > 3000)

Проверено на версии PostgreSQL 12.6

Это был аналог так называемой Inline табличной функции ms sql server.

P.S. Перечень ограничений для табличных функций, при которых тело функции может подставляться в вызывающий запрос(https://edu.postgrespro.ru/16/dev1-16/dev1_08_sql_func.html):

функция написана на языке SQL;
функция сама не должна быть изменчивой (VOLATILE) и не должна содержать вызовов таких функций;
функция не должна быть строгой (STRICT);
тело должно содержать единственный оператор SELECT (но он может быть сложным);
и ряд других ограничений.

P.P.S. Дополнение насчёт изменчивости функций (никак не связанное с темой заметки). Если в функции на языке SQL есть DML, то при вызове этой функции сама функция будет видеть произведенные изменения только при категории изменчивости VOLATILE. STABLE и IMMUTABLE используют снимок, полученный в начале вызывающего запроса, тогда как функции VOLATILE получают свежий снимок в начале каждого запроса, который они выполняют.