Анонимные процедуры

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

В данной статье подробно разбираются анонимные процедуры — принцип, устройство и причины использования, лучшие практики.

В прошлой статье разбираются понятие процедуры, отличие их от функции и возвращение значения процедуры.

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

Анонимная процедура

Анонимный блок DO в PL/pgSQL или одномоментная процедура:

DO $$
DECLARE
     r record;
BEGIN
     SELECT id, code INTO r FROM t WHERE id = 1;
     RAISE NOTICE '%', r;
END;
$$;

Это такая же процедура, только без имени и входящих/исходящих параметров. Предназначена для оперирования переменными, вызова других процедур/функций.

Вызов процедуры

Функция, другая процедура или анонимный блок DO в PL/pgSQL может вызвать процедуру, используя оператор CALL. Каждому выходному параметру INOUT для процедуры должна соответствовать переменная в операторе CALL, и этой переменной по завершении процедуры будет присвоено возвращаемое процедурой значение.

DO $$
DECLARE
    r record;
BEGIN
    CALL test(r);
    RAISE NOTICE '%', r;
END;
$$;

Идеально подходят для:

• Администрирования БД
• Разовых миграций данных
• Прототипирования и тестирования
• Автоматизации рутинных задач

Итоги

Преимущества:

• Не требуют создания объектов в БД
• Полная поддержка транзакций
• Автоматическая очистка
• Гибкость и быстрота разработки

Ограничения:

• Невозможно повторное использование
• Нет прямого возврата значений
• Сложнее отлаживать

Пример вызова функции из анонимного кода:

-- есть именованная функция
CREATE OR REPLACE FUNCTION fmt_out_2 (IN phone text, OUT code text, OUT num text)
AS $$
BEGIN
    IF phone ~ '^[0-9]*$' AND length(phone) = 10 THEN
    --  ^[0-9]{10}$ - сразу проверить и количество символов
        code := substr(phone,1,3);
        num  := substr(phone,4);
    ELSE
        code := NULL;
        num  := NULL;
    END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

— Условный оператор CASE (анонимный блок)

DO $$
DECLARE
    code text := (fmt_out_2('9992128506')).code; -- сразу обращаемся к возвращаемой переменной из функции через .
BEGIN
    CASE code
    WHEN '495', '499' THEN
        RAISE NOTICE '% - Москва', code;
    WHEN '812' THEN
        RAISE NOTICE '% - Санкт-Петербург', code;
    WHEN '384' THEN
        RAISE NOTICE '% - Кемеровская область', code;
    END CASE;
END;
$$;

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

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