SQL UDF: WITH FUNCTION и CREATE FUNCTION

Встроенных функций в Trino много, но бизнес-логика всегда специфична: «класс клиента по сумме покупок», «нормализованный телефон», «налог по региону». Копировать одно и то же выражение в десятки запросов — путь к расхождениям и ошибкам. Решение — пользовательская функция: один раз описать логику, дальше вызывать по имени.

Trino позволяет писать такие функции на самом SQL — это SQL UDF (user-defined functions). Урок завершает модуль и разбирает два способа их объявления: WITH FUNCTION для функции, живущей в пределах одного запроса, и CREATE FUNCTION для функции, сохранённой в каталоге и доступной всем.

Тело SQL UDF: язык внутри функции

Прежде чем разбирать два способа объявления, посмотрим на саму функцию — её тело одинаково в обоих случаях. SQL UDF описывается небольшим процедурным языком. Каркас объявления:

FUNCTION имя(параметр тип, ...)
RETURNS тип_результата
[характеристики]
BEGIN
  ... тело ...
END

Самый простой случай — функция-выражение через RETURN:

FUNCTION to_celsius(f DOUBLE)
RETURNS DOUBLE
RETURN (f - 32) * 5.0 / 9.0

Но тело может быть и полноценно процедурным. Доступны управляющие конструкции:

• DECLARE имя тип — объявить локальную переменную.
• SET имя = выражение — присвоить ей значение.
• IF ... THEN ... ELSEIF ... ELSE ... END IF — ветвление.
• CASE — выбор по значению.
• WHILE ... DO ... END WHILE, REPEAT ... UNTIL ... END REPEAT, LOOP ... END LOOP — циклы.
• RETURN выражение — вернуть результат.

FUNCTION customer_tier(total_spent DECIMAL(12,2))
RETURNS VARCHAR
BEGIN
  IF total_spent >= 100000 THEN
    RETURN 'platinum';
  ELSEIF total_spent >= 10000 THEN
    RETURN 'gold';
  ELSEIF total_spent >= 1000 THEN
    RETURN 'silver';
  ELSE
    RETURN 'bronze';
  END IF;
END

Важная характеристика — детерминированность. Функцию помечают DETERMINISTIC, если на одних и тех же аргументах она всегда возвращает один и тот же результат (как customer_tier выше), и NOT DETERMINISTIC — если нет (например, использует current_timestamp или случайность). Эта пометка — не формальность: оптимизатор вправе вычислить детерминированную функцию один раз и переиспользовать результат, а недетерминированную обязан звать каждый раз. Ещё одна полезная характеристика — RETURNS NULL ON NULL INPUT: функция автоматически вернёт NULL, если любой аргумент NULL, не выполняя тело.

WITH FUNCTION: inline-функция запроса

Первый способ — inline SQL UDF. Функция объявляется прямо перед запросом через WITH FUNCTION и существует только в пределах этого запроса. Закончился запрос — функции больше нет, она нигде не сохранена.

WITH FUNCTION customer_tier(total_spent DECIMAL(12,2))
RETURNS VARCHAR
BEGIN
  IF total_spent >= 100000 THEN RETURN 'platinum';
  ELSEIF total_spent >= 10000 THEN RETURN 'gold';
  ELSEIF total_spent >= 1000 THEN RETURN 'silver';
  ELSE RETURN 'bronze';
  END IF;
END
SELECT custkey,
       sum(totalprice) AS spent,
       customer_tier(sum(totalprice)) AS tier
FROM tpch.sf1.orders
GROUP BY custkey
LIMIT 10;

Синтаксически WITH FUNCTION устроен как WITH для CTE: объявление идёт перед SELECT. В одном запросе можно объявить несколько функций. Inline-функция нужна, когда логика повторяется внутри одного сложного запроса несколько раз, но в других запросах не понадобится: выносить её в каталог незачем, а дублировать выражение в трёх местах запроса — плохо. WITH FUNCTION даёт локальное переиспользование без следов в схеме.

CREATE FUNCTION: каталожная функция

Второй способ — catalog routine, постоянная функция, сохранённая в каталоге. Объявляется через CREATE FUNCTION и живёт, пока её не удалят через DROP FUNCTION. Её видят и вызывают все запросы и все пользователи кластера.

CREATE FUNCTION example.default.customer_tier(total_spent DECIMAL(12,2))
RETURNS VARCHAR
BEGIN
  IF total_spent >= 100000 THEN RETURN 'platinum';
  ELSEIF total_spent >= 10000 THEN RETURN 'gold';
  ELSEIF total_spent >= 1000 THEN RETURN 'silver';
  ELSE RETURN 'bronze';
  END IF;
END;

Ключевая деталь — функция получает полное имя из трёх частей: каталог.схема.имя, здесь example.default.customer_tier. Каталожная функция должна где-то храниться, и хранится она в каталоге — поэтому ей и нужно квалифицированное имя, как таблице. Кластер должен быть настроен так, чтобы какой-то каталог был назначен хранилищем функций; обычно для этого подходит каталог на коннекторе, поддерживающем хранение UDF.

После создания функция доступна отовсюду:

SELECT custkey, example.default.customer_tier(sum(totalprice)) AS tier
FROM tpch.sf1.orders
GROUP BY custkey;

Управляют каталожными функциями командами:

• CREATE FUNCTION — создать; CREATE OR REPLACE FUNCTION — создать или переопределить.
• DROP FUNCTION имя(типы_параметров) — удалить; типы параметров указывают, потому что функции можно перегружать.
• SHOW FUNCTIONS — список доступных функций, включая пользовательские.

Как выбрать между двумя способами

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

WITH FUNCTION берут, когда логика локальна для одного запроса: повторяется в нём, но больше нигде. Запрос самодостаточен, ничего не нужно создавать заранее и убирать потом. CREATE FUNCTION берут для общей бизнес-логики, к которой обращаются многие запросы и люди: единое определение «класса клиента» для всей команды, чтобы оно не разъезжалось по копиям. Цена каталожной функции — она отдельный объект схемы: её надо сопровождать, версионировать, и при изменении явно делать CREATE OR REPLACE, иначе старые запросы будут видеть старую логику.

Попробуй сам

На песочнице курса (Trino 481):

1. Напишите inline-функцию через WITH FUNCTION: order_size(price DECIMAL(12,2)), возвращающую 'large' при цене больше 200000, 'medium' больше 50000, иначе 'small'. Примените её к tpch.sf1.orders: SELECT orderkey, totalprice, order_size(totalprice) FROM orders LIMIT 20;. Убедитесь, что после выполнения запроса функция нигде не осталась.

2. Рассуждение в двух абзацах. Первый: в команде из пяти аналитиков всем нужна единая функция «класс клиента». Какой способ объявления выбрать и почему именно он защищает от того, что определение «класса» разъедется по копиям. Второй: что произойдёт со старыми запросами, если изменить логику каталожной функции обычным CREATE FUNCTION без OR REPLACE, и почему важно помнить про версионирование.

3. Объясните, почему пометка функции как DETERMINISTIC или NOT DETERMINISTIC влияет на исполнение. Что оптимизатор имеет право сделать с детерминированной функцией, чего не может с недетерминированной, и почему функцию, использующую current_timestamp, нельзя помечать DETERMINISTIC.