Хранимые процедуры в SQL: функции и процедуры PostgreSQL на примерах

Коротко

Хранимая процедура и хранимая функция — это запрос или несколько команд, которые сохранены в базе под именем и вызываются с параметрами.

• Функция возвращает значение и вызывается внутри запроса: select active_users('2026-07-15'). Создаётся командой CREATE FUNCTION.
• Процедура ничего не возвращает в запрос, она меняет данные: пересобирает витрину, чистит таблицу. Создаётся командой CREATE PROCEDURE, вызывается через CALL.
• В PostgreSQL только процедура может выполнить COMMIT внутри себя. Функция всегда работает в транзакции того запроса, который её вызвал.
• Аналитику функции нужны для единого определения метрики, процедуры — для повторяемой перезаливки таблиц.
• Если хватает VIEW или CTE, берите их: логика, спрятанная в функцию, хуже читается и сложнее проверяется.

Что такое хранимая процедура и функция и зачем они аналитику

Обычный запрос живёт в файле, в отчёте BI или в истории редактора. Хранимая процедура или функция живёт в самой базе, рядом с таблицами. У неё есть имя, список параметров и тело — тот SQL, который выполнится при вызове. Написали один раз, дальше вызывают по имени.

В разговоре «хранимыми процедурами» называют и то, и другое. В PostgreSQL это два разных объекта. Функция что-то вычисляет и возвращает: число, строку, таблицу. Процедура выполняет действие: удаляет старые строки, вставляет новые, фиксирует транзакцию. Процедуры появились в PostgreSQL 11, до этого их роль играли функции с типом результата void, и в старых базах таких функций до сих пор много.

Аналитику это пригождается в трёх случаях. Первый — единое определение: что такое «активный пользователь», написано в одном месте, и его нельзя случайно изменить в одном отчёте из десяти. Второй — расчёт с параметрами: выручка по каналам за любой период вызывается одной строкой, а не копируется с правкой дат. Третий — перезаливка витрины одной командой, которую можно поставить в расписание.

Примеры выполнены в PostgreSQL 14 на учебной базе «Маяк»: это SaaS-сервис с таблицами users, events и payments. Функции из статьи только читают эти таблицы, а процедура пишет в отдельную новую таблицу. Если пробуете на базе, где работают другие люди, заведите свою схему и скопируйте в неё данные (create table payments as select * from …), а не создавайте объекты рядом с рабочими таблицами. Песочница симулятора SQL-аналитика принимает только SELECT и WITH, поэтому в ней выполняются лишь «тела» будущих функций — обычные запросы с теми же числами.

Как создать функцию: CREATE FUNCTION на чистом SQL

Начнём с запроса, который станет телом функции. Активный пользователь за день — тот, у кого в этот день есть хотя бы одно событие. За 15 июля 2026 года таких 374.

Чтобы превратить запрос в функцию, нужно четыре вещи: имя и параметры, тип результата после RETURNS, язык и тело между $$. Дата в теле заменяется параметром p_day. Префикс p_ — привычка, которая спасает от путаницы: параметр с именем day легко перепутать со столбцом. Запись p_day + 1 в PostgreSQL означает следующий день.

Слово stable сообщает планировщику, что функция только читает данные и в пределах одного запроса возвращает одно и то же для одинаковых аргументов. Для функций-расчётов это правильная пометка; по умолчанию PostgreSQL считает функцию volatile, то есть способной менять данные.

Вызывается функция как любая встроенная: в SELECT, в WHERE, внутри другого выражения. Вторая часть блока считает DAU за неделю, вызывая функцию для каждой даты из generate_series.

select count(distinct user_id) as dau     -- 374
from events
where event_time >= date '2026-07-15'
  and event_time < date '2026-07-16';

create function active_users(p_day date)
returns bigint
language sql
stable
as $$
  select count(distinct user_id)
  from events
  where event_time >= p_day
    and event_time < p_day + 1;
$$;

select active_users('2026-07-15') as dau;  -- 374

select d::date as day,
       active_users(d::date) as dau
from generate_series(date '2026-07-13', date '2026-07-19', interval '1 day') as d
order by day;
-- 323, 335, 374, 418, 401, 354, 335

Функция, которая возвращает таблицу: RETURNS TABLE

Одно число — частный случай. Чаще нужен набор строк: выручка по каналам привлечения за период. Снова сначала обычный запрос. За июль 2026 года он возвращает четыре строки: organic — 207 плательщиков и 5250, referral — 124 и 3094, partner — 73 и 1766, paid_search — 32 и 758.

В функции после RETURNS TABLE перечисляются столбцы результата с типами. Типы должны совпасть с тем, что отдаёт запрос: count возвращает bigint, сумма по numeric — numeric. Границы периода становятся параметрами p_from и p_to; правая граница не включается, поэтому месяц задаётся как «с первого числа по первое число следующего».

Такая функция вызывается в FROM, как таблица: select * from revenue_by_channel(…). Результат можно фильтровать, сортировать и соединять с другими таблицами. По сути это представление с параметрами — то, чего обычный VIEW не умеет.

select u.channel,
       count(distinct p.user_id) as payers,
       sum(p.amount) as revenue
from payments as p
join users as u on u.user_id = p.user_id
where p.paid_at >= date '2026-07-01'
  and p.paid_at < date '2026-08-01'
group by u.channel
order by revenue desc;
-- organic 207 | 5250.00, referral 124 | 3094.00,
-- partner 73 | 1766.00, paid_search 32 | 758.00

create function revenue_by_channel(p_from date, p_to date)
returns table (channel text, payers bigint, revenue numeric)
language sql
stable
as $$
  select u.channel,
         count(distinct p.user_id) as payers,
         sum(p.amount) as revenue
  from payments as p
  join users as u on u.user_id = p.user_id
  where p.paid_at >= p_from
    and p.paid_at < p_to
  group by u.channel
  order by revenue desc;
$$;

select * from revenue_by_channel('2026-07-01', '2026-08-01');
-- те же четыре строки, что у запроса выше

select channel, revenue
from revenue_by_channel('2026-08-01', '2026-09-01')
where payers >= 100
order by revenue desc;
-- organic 7087.00, referral 4792.00

Параметры и значения по умолчанию

У параметра может быть значение по умолчанию: оно пишется после слова default, и такой параметр при вызове можно пропустить. Параметры со значением по умолчанию стоят в конце списка.

Функция top_channels вызывает уже готовую revenue_by_channel и оставляет первые p_limit каналов, по умолчанию три. За август без третьего аргумента она возвращает organic, referral и partner, с p_limit = 1 — только organic.

Аргументы можно передавать по именам через =>. Порядок тогда не важен, а вызов читается без подглядывания в определение: сразу видно, какая дата начало, а какая конец. Для функций с тремя и более параметрами это заметно снижает число ошибок.

create function top_channels(p_from date, p_to date, p_limit integer default 3)
returns table (channel text, revenue numeric)
language sql
stable
as $$
  select channel, revenue
  from revenue_by_channel(p_from, p_to)
  order by revenue desc
  limit p_limit;
$$;

select * from top_channels('2026-08-01', '2026-09-01');
-- organic 7087.00, referral 4792.00, partner 2200.00

select * from top_channels('2026-08-01', '2026-09-01', 1);
-- organic 7087.00

select * from top_channels(p_to => '2026-09-01', p_from => '2026-08-01', p_limit => 2);
-- organic 7087.00, referral 4792.00

Как создать процедуру: CREATE PROCEDURE и вызов CALL

Теперь действие, а не расчёт. Дашборд читает выручку по каналам из небольшой витрины mart_channel_revenue: по строке на месяц и канал. Когда приходят возвраты или исправления, месяц нужно пересчитать — удалить его строки и вставить заново. Это две команды, которые всегда идут вместе, и их удобно спрятать в процедуру.

Процедура написана на plpgsql — процедурном языке PostgreSQL. В нём есть то, чего нет в чистом SQL: блок declare с переменными, if, циклы, сообщения через raise. Здесь после вставки команда get diagnostics кладёт число вставленных строк в переменную v_rows. Если строк нет, процедура останавливается с ошибкой, и удаление старых строк откатывается вместе с ней: пустой месяц не затрёт витрину.

Вызов — отдельной командой CALL. Первый вызов за июль печатает NOTICE: Месяц 2026-07-01: записано строк — 4. Повторный вызов за тот же месяц дублей не создаёт: после трёх вызовов в витрине восемь строк, по четыре на июль и август. Такое свойство называют идемпотентностью, и для всего, что запускается по расписанию, оно обязательно.

Платежей за сентябрь в учебной базе нет, и вызов за сентябрь заканчивается ошибкой ERROR: За 2026-09-01 нет платежей, витрина не обновлена. В витрине по-прежнему восемь строк.

create table mart_channel_revenue (
  month    date,
  channel  text,
  payers   bigint,
  revenue  numeric,
  built_at timestamp default now()
);

create procedure rebuild_channel_revenue(p_month date)
language plpgsql
as $$
declare
  v_rows integer;
begin
  delete from mart_channel_revenue
  where month = p_month;

  insert into mart_channel_revenue (month, channel, payers, revenue)
  select p_month, channel, payers, revenue
  from revenue_by_channel(p_month, (p_month + interval '1 month')::date);

  get diagnostics v_rows = row_count;

  if v_rows = 0 then
    raise exception 'За % нет платежей, витрина не обновлена', p_month;
  end if;

  raise notice 'Месяц %: записано строк — %', p_month, v_rows;
end;
$$;

call rebuild_channel_revenue('2026-07-01');  -- NOTICE: ... записано строк — 4
call rebuild_channel_revenue('2026-08-01');  -- NOTICE: ... записано строк — 4
call rebuild_channel_revenue('2026-07-01');  -- повтор: июль пересобран, дублей нет

select month, channel, payers, revenue
from mart_channel_revenue
order by month, revenue desc;                -- 8 строк

Переменные и IF в plpgsql

На plpgsql пишут не только процедуры, но и функции — когда внутри нужна развилка. Функция dau_status берёт DAU за день и возвращает короткий вердикт для мониторинга: «норма», «ниже порога» или «нет данных».

Переменная объявляется в блоке declare с типом, значение присваивается через :=. Ветки записываются как if … then … elsif … else … end if, результат отдаёт return. Обратите внимание: функция вызывает active_users, а не повторяет её условие. Определение активности остаётся в одном месте.

На учебных данных 15 июля возвращает «норма: 374», 13 июля — «ниже порога: 323», а 1 декабря, которого в базе нет, — «нет данных». С порогом 400 вторым аргументом 15 июля становится «ниже порога: 374».

Циклы, курсоры и обработка исключений в plpgsql тоже есть, но аналитику они нужны редко. Если в теле появился цикл по строкам таблицы, обычно ту же задачу решает один запрос с GROUP BY или оконной функцией, и решает быстрее.

create function dau_status(p_day date, p_threshold integer default 350)
returns text
language plpgsql
stable
as $$
declare
  v_dau bigint;
begin
  v_dau := active_users(p_day);

  if v_dau = 0 then
    return 'нет данных';
  elsif v_dau < p_threshold then
    return 'ниже порога: ' || v_dau;
  else
    return 'норма: ' || v_dau;
  end if;
end;
$$;

select dau_status('2026-07-15') as jul_15,   -- норма: 374
       dau_status('2026-07-13') as jul_13,   -- ниже порога: 323
       dau_status('2026-12-01') as dec_01;   -- нет данных

select dau_status('2026-07-15', 400);        -- ниже порога: 374

Чем функция отличается от процедуры в PostgreSQL

Перепутать вызовы PostgreSQL не даст. select rebuild_channel_revenue('2026-07-01') отвечает ERROR: rebuild_channel_revenue(unknown) is a procedure с подсказкой HINT: To call a procedure, use CALL. Обратная попытка, call active_users('2026-07-15'), — ERROR: active_users(unknown) is not a procedure и HINT: To call a function, use SELECT.

Главное отличие по существу — транзакции. Функция выполняется внутри запроса, который её вызвал, и зафиксировать что-либо отдельно не может. Процедура может выполнить COMMIT посреди работы. Это проверяется маленьким опытом: процедура load_with_commit пишет в журнал «шаг 1», делает COMMIT, пишет «шаг 2» и падает с ошибкой. После вызова в таблице load_log осталась одна строка — «шаг 1». Всё, что было до COMMIT, сохранено, всё, что после, откатилось.

Та же процедура без строки commit после сбоя оставляет в журнале ноль строк: она целиком откатилась. Для длинной загрузки из нескольких этапов COMMIT между этапами полезен — сбой на пятом не заставит повторять первые четыре. Но у этого есть цена: частично выполненная процедура оставляет данные в промежуточном состоянии, и об этом должен знать тот, кто её перезапускает.

Есть два ограничения. Если вызвать процедуру с COMMIT внутри уже открытой транзакции (begin; call load_with_commit();), PostgreSQL ответит ERROR: invalid transaction termination. С той же ошибкой заканчивается попытка написать commit в теле функции.

create table load_log (step text, logged_at timestamp default now());

create procedure load_with_commit()
language plpgsql
as $$
begin
  insert into load_log (step) values ('шаг 1');
  commit;
  insert into load_log (step) values ('шаг 2');
  raise exception 'сбой на шаге 2';
end;
$$;

call load_with_commit();   -- ERROR:  сбой на шаге 2

select step from load_log; -- одна строка: шаг 1

Как посмотреть текст существующей функции

Обычная ситуация: в запросе коллеги вызывается active_users, и надо понять, что она считает. В psql короче всего \sf active_users — команда печатает определение целиком. \df показывает список функций и процедур схемы с аргументами и типом результата, \df+ active_users добавляет язык, владельца и исходный текст.

В DBeaver, DataGrip и других клиентах, где команд psql нет, работает обычный запрос с pg_get_functiondef. Он возвращает готовую команду CREATE OR REPLACE FUNCTION … — её можно скопировать, поправить и выполнить. Для функции с несколькими вариантами аргументов имя пишут с типами: 'active_users(date)'::regprocedure.

Список всего, что создано в схеме, даёт стандартное представление information_schema.routines: в столбце routine_type стоит FUNCTION или PROCEDURE. На этом шаге статьи там шесть строк: четыре функции и две процедуры.

select pg_get_functiondef('active_users'::regproc);
-- CREATE OR REPLACE FUNCTION ...active_users(p_day date)
--  RETURNS bigint
--  LANGUAGE sql
--  STABLE
-- AS $function$
--   select count(distinct user_id)
--   ...

select routine_name, routine_type
from information_schema.routines
where routine_schema = current_schema()
order by routine_name;
-- active_users FUNCTION, dau_status FUNCTION, load_with_commit PROCEDURE, ...

CREATE OR REPLACE и DROP FUNCTION: как изменить и удалить

Чтобы изменить тело, функцию не удаляют, а пересоздают командой CREATE OR REPLACE FUNCTION с тем же именем и теми же параметрами. Права и зависимости сохраняются. Допустим, команда договорилась, что открытие приложения — ещё не активность, и активным считается тот, кто сделал что-то содержательное. Меняем одно условие, и active_users('2026-07-15') возвращает уже 81 вместо 374. Функция dau_status, которую никто не трогал, теперь отвечает «ниже порога: 81».

Это и сила, и опасность единого определения: правка в одном месте меняет все отчёты разом. Удобно, когда определение исправляют, и неприятно, когда его поменяли молча.

CREATE OR REPLACE меняет только тело. Попытки сменить тип результата или имя параметра PostgreSQL отклоняет — ответы в таблице ниже. В обоих случаях функцию сначала удаляют через DROP FUNCTION, а потом создают заново.

При удалении PostgreSQL не проверяет, вызывает ли эту функцию другая функция: тело хранится как текст. drop function active_users(date) проходит без предупреждений, а следующий вызов dau_status падает с ERROR: function active_users(date) does not exist. Перед удалением поищите имя функции в текстах остальных — и в репозитории с отчётами.

create or replace function active_users(p_day date)
returns bigint
language sql
stable
as $$
  select count(distinct user_id)
  from events
  where event_time >= p_day
    and event_time < p_day + 1
    and event_name <> 'app_open';
$$;

select active_users('2026-07-15');   -- 81 вместо 374
select dau_status('2026-07-15');     -- ниже порога: 81

-- уборка: функции и процедуры удаляются с типами аргументов
drop function dau_status(date, integer);
drop function top_channels(date, date, integer);
drop procedure rebuild_channel_revenue(date);
drop procedure load_with_commit();
drop function revenue_by_channel(date, date);
drop function active_users(date);
drop table mart_channel_revenue;
drop table load_log;

Минусы хранимых процедур и когда лучше VIEW, CTE или dbt-модель

Первый минус — логика уходит из запроса. В отчёте написано active_users(d), и по этой строке не видно, считаются ли открытия приложения. Чтобы проверить число, нужно идти в базу и читать определение. С CTE всё условие перед глазами.

Второй — версии и ревью. Запрос в репозитории проходит проверку коллегой и хранит историю. Функцию можно заменить одной командой прямо в базе, и следов не останется. Если функции нужны, держите их тексты в репозитории и накатывайте миграциями, а не правьте руками.

Третий — ошибки всплывают поздно. Мы проверили: функция на language sql с опечаткой в имени таблицы не создаётся, PostgreSQL сразу отвечает ERROR: relation "eventz" does not exist. Та же опечатка в теле на plpgsql создаётся без замечаний и падает только при первом вызове.

Четвёртый — скорость построчных вызовов. DAU за все 91 день учебной базы через active_users(d) считался 144 мс: функция 91 раз заново читает таблицу событий. Один запрос с GROUP BY по дате вернул те же числа за 7 мс. Это замер на ноутбуке и на 35 тысячах событий, но соотношение показательное: на реальной таблице разница превращается в минуты. Запрос ниже — тот самый вариант без функции, за неделю 13–19 июля.

Пятый — переносимость. plpgsql существует только в PostgreSQL. В MySQL и SQL Server хранимые процедуры тоже есть, но синтаксис и процедурный язык там свои, и при переезде на другую СУБД или в аналитическое хранилище тела придётся переписывать.

Отсюда простое правило выбора. Нужен именованный запрос без параметров — это VIEW. Нужно разбить длинный расчёт на шаги внутри одного отчёта — CTE. Нужна таблица-витрина, которая пересобирается по расписанию, с тестами и историей изменений, — это модель в dbt или похожем инструменте, если он есть в компании. Функция оправдана, когда нужен параметр и единое определение, процедура — когда инструмента для витрин нет, а команды записи всё равно надо запускать вместе и в одном порядке.

select cast(event_time as date) as day,
       count(distinct user_id) as dau
from events
where event_time >= date '2026-07-13'
  and event_time < date '2026-07-20'
group by cast(event_time as date)
order by day;
-- 323, 335, 374, 418, 401, 354, 335

Частые вопросы

Что почитать дальше

Запустите в песочнице три запроса для чтения из статьи — DAU за день, выручку по каналам и DAU за неделю — и поменяйте в них даты: это и есть работа параметров. Функции и процедуру создавайте в своём PostgreSQL.