PostgreSQL | Как вернуть таблицу из функции, если количество столбцов неизвестно заранее?

RETURNS или RETURNS TABLE без определённого количества полей и типов данных

В чём проблема? Когда мы пишем функцию в PostgreSQL, тогда выражения RETURNS или RETURNS TABLE ждут от нас явного количества передаваемых параметров и их типов.

Иными словами. Если мы работаем с таблицей, то функция сломается, когда у таблицы появятся новые столбцы или исчезнут старые.

Это очень неудобно и не гибко. В развивающейся базе данных просто невозможно приковать таблицы фиксированными размерами (количествами столбцов). Что делать?

Как вернуть таблицу из функции, написанную на языке ‘sql‘ в PostgreSQL?

В PostgreSQL сильно намудрили с типами данных и любой классический язык программирования гораздо проще понять, чем SQL.

Что нужно знать?

Когда в PostgreSQL создаётся новая таблица, тогда в СУБД под эту таблицу создаётся новый ТИП ДАННЫХ. То есть каждая таблица — это какой-то тип данных.

Так вот для того чтобы не гадать с размерностью таблицы, можно просто указать путь до неё (путь до типа данных). Выглядит это примерно так.

-- SELECT a.get_old_datecheck_from_t_d();
-- SELECT * FROM a.get_old_datecheck_from_t_d();
-- DROP FUNCTION a.get_old_datecheck_from_t_d();

CREATE FUNCTION a.get_old_datecheck_from_t_d()
RETURNS a.t_d AS
$$
  SELECT * FROM a.t_d
  WHERE datecheck = (SELECT min(datecheck) FROM a.t_d)
  LIMIT 1;
$$
LANGUAGE sql;

Смотреть нужно на выражение «RETURNS a.t_d AS»

Получили таблицу из вызова функции не зная количества столбцов - PostgreSQL — Получили таблицу из вызова функции не зная количества столбцов — PostgreSQL

В нём a.t_d представляет собой тип данных таблицы. То есть если таблица поменяется, то и тип автоматически поменяется. Это значит, что нам не нужно париться и вручную прописывать все названия столбцов и их типы данных в выводной таблице.

Предлагаю посмотреть обучающее видео на тему «Составные типы«, чтобы лучше ориентироваться в проблеме.

Как вернуть таблицу из функции, написанную на языке ‘plpgsql‘ в PostgreSQL?

В случае с языком ‘sql‘ мы вообще не писали RETURN. У нас сразу был написан SELECT.

Но ситуация немного меняется, когда мы хотим использовать циклы через FOR и ветвление через IF. В этом случае мы по-любому переходим на язык ‘plpgsql‘.

Чтобы повторить удобный возврат таблицы из функции на языке ‘plpgsql‘, нам нужно в блоке объявления функции написать RETURNS SETOF a.t_d, а в теле функции в нужном месте написать RETURN QUERY SELECT …… .

Обратите внимание, что если функция с выходными параметрами должна возвращать множество значений, то при объявлении нужно указывать RETURNS SETOF. При этом если выходных параметров несколько, то используется RETURNS SETOF record, а если только один с типом некий_тип, то RETURNS SETOF некий_тип.

В нашем случае некий_тип — это тип a.t_d. (напоминаю, каждая таблица, это отдельный тип).

Информационные ссылки

Официальный сайт WEB-оболочки pgAdmin — https://www.pgadmin.org

Официальный сайт СУБД PostgreSQL — https://www.postgresql.org

Стандартные типы данных в PostgreSQL — https://postgrespro.ru/docs/postgresql/14/datatype

Идентификаторы объектов — https://postgrespro.ru/docs/postgresql/14/datatype-oid#DATATYPE-OID-TABLE