Возвращение множеств (setof) и таблиц (table)

Статья основана на десятом видео из 31 темы курса SQL 2.0 — PL/pgSQL в PostgreSQL от Аристова Евгения, который является логическим продолжением курса SQL c 0. Ссылки на видео на платформах RUTUBE и VK видео.

В данной статье подробно разбираются возвращение множеств(setof) из функции и таблиц(table).

В прошлой статье мы разобрали использование команды SELECT INTO, варианты использования и его ограничения.

Презентация и исходники доступны по ссылке.

Возвращение множеств

Для функций на PL/pgSQL, возвращающих не одну, а набор записей — можно использовать SETOF (множество) некий_тип (можем указать тип данных по имени существующей таблицы).

Отдельные элементы возвращаемого значения формируются командами RETURN NEXT — наполнить одной записью или RETURN QUERY — дообогатить множество результатом запроса, а финальная команда RETURN без аргументов завершает выполнение функции и возвращает сформированное множество записей.

RETURN NEXT используется как со скалярными, так и с составными типами данных. Для составного типа результат функции возвращается в виде таблицы. RETURN QUERY добавляет результат выполнения запроса к результату функции. RETURN NEXT и RETURN QUERY можно свободно смешивать в теле функции, в этом случае их результаты будут объединены.

RETURN NEXT выражение;

RETURN QUERY запрос;

RETURN QUERY EXECUTE строка-команды [USING выражение [, …]]; — динамический SQL, разберём в 23 теме

Наполнять можем сколько угодно (если не упадём по OOM).

Обратите внимание, что порядок записей в результирующем множестве НЕ гарантирован!

Пример использования RETURN NEXT:

— указываем тип данных по имени существующей таблицы

CREATE OR REPLACE FUNCTION get_all_foo() RETURNS SETOF foo AS
$BODY$
DECLARE
    r foo%rowtype; — тип данных кортеж — более подробно на 15 лекции
BEGIN
    FOR r IN — циклы в 24 теме более подробно
        SELECT * FROM foo WHERE fooid > 0
    LOOP
        — здесь возможна обработка данных
        r.fooid := r.fooid + 10;
        RETURN NEXT r; — возвращается текущая строка запроса
    END LOOP;
    RETURN;
END
$BODY$
LANGUAGE plpgsql;

SELECT * FROM get_all_foo();

Результат:

fooid | foosubid | fooname
——-+———-+———
11 | 2 | three
14 | 5 | six
(2 rows)

SELECT * FROM foo;

Результат:

fooid | foosubid | fooname
——-+———-+———
1 | 2 | three
4 | 5 | six
(2 rows)

Возвращение таблиц

Возвращать таблицы мы можем двумя путями:

• указав имя таблицы и набор полей
• указав имя и шаблон возвращаемых значений table (like шаблон)

Далее по аналогии с множеством необходимо наполнять набор строк — совпадающих с заданным шаблоном — RETURN NEXT/QUERY

Пример:

drop function if exists extended_sales;
CREATE or replace FUNCTION extended_sales(p_itemno int)
RETURNS TABLE(quantity int, total int) AS $$
BEGIN
    RETURN QUERY SELECT s.quantity as a, s.quantity * s.price as b FROM sales s
                 WHERE s.itemno = p_itemno;
END;
$$ LANGUAGE plpgsql;

SELECT extended_sales(1);

Вывод:

(100, 1000)

Больше примеров доступно на гитхабе и в видео.

В следующей статье мы разберём категории изменчивости функций.