Глава 7. Отчеты, функции, расписание

Главы

Содержание

Главы
Проблема вложенности
Простые отчеты
Функция jsonb_table
Параметры в JSON Path
Отчеты с группировкой
Рекурсивные документы и запросы
Знакомство с функциями
Схемы и префиксы
Характеристики функций
Примеры функций
Рекомендации к функциям
Временные функции
Тестирование функций
Выгрузка отчетов
Отчеты и представления
Задачи по расписанию
Другие применения pg_cron

В этой главе мы затронем тему отчетности — предоставлении информации другим участникам бизнеса, например менеджерам и аналитикам. Мы узнаем, как приводить вложенные структуры в плоские, познакомимся с мощной функцией json_table, научимся писать свои функции, изучим расширение pg_cron и многое другое.

До сих пор мы смотрели на документы глазами программиста. Как правило, в его обязанности входит извлечь документы из базы, преобразовать их и отдать клиенту по HTTP. Наверняка читатель пишет подобный код часто, если не каждый день.

Чем крупнее компания, тем больше в ней участников, заинтересованных в офисном формате данных. Это руководоство, менеджмент, отделы анализа и другие. Им нужна выгрузка данных по разным критериям в разрезе тех или иных полей — другими словами, отчетность. Какие-то отчеты проверяют вручную: каждое утро начальник открывает файл Excel и оценивает показатели. Другие предназначены для импорта в программы, созданные для моделирования и сложных расчетов. В них аналитики строят проекции, кубы, проверяют гипотезы.

Если вы работаете с базой данных, от вас тоже потребуют всевозможных отчетов. Вот какие задачи вам могут поручить:

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

На практике отчетов могут быть десятки. Наша задача – удовлетворить эти требования с учетом того, что в базе хранятся документы, а не плоские таблицы. Прежде чем пойти в технические дебри, выскажем утверждение, от которого будем отталкиваться в работе.

Проблема вложенности

Модель заявки, с которой мы работаем в книге, является деревом. Некоторые значения корневого объекта – тоже объекты и массивы. Этот тезис относится к любому документу; он – дерево. Давайте вспомним, как в нашей заявке хранятся пользователи в разрезе отделов:

"departments": [
  {
    "id": "00000000-0000-0000-0000-000000000005",
    "code": "dep_5",
    "name": "Department 5",
    "users": [
      {
        "id": "00000000-0000-0000-0000-000000000055",
        "name": "User 55",
        "role": "support",
        "email": "user_55@test.com"
      },
      {
        "id": "00000000-0000-0000-0000-000000000065",
        "name": "User 65",
        "role": "decision-maker",
        "email": "user_65@test.com"
      }
    ]
  },
  {
    "id": "00000000-0000-0000-0000-000000000015",
    "code": "dep_15",
    "name": "Department 15",
    "users": [
      {
        "id": "00000000-0000-0000-0000-000000000075",
        "name": "User 75",
        "role": "support",
        "email": "user_75@test.com"
      },
      {
        "id": "00000000-0000-0000-0000-000000000035",
        "name": "User 35",
        "role": "decision-maker",
        "email": "user_35@test.com"
      }
    ]
  }
]

Такая структура удобна с точки зрения интерфейса. Скорее всего, клиент увидит список отделов, каждый из которых разворачивается и показывает список пользователей. Изобразим подобный интерфейс на рисунке:

┌───────────────────────────────┐
│┌───────────────────────────┐  │
││Departments                │  │
│└─┬──┬──────────────────────┴┐ │
│  ├─▶│Risk Management        │ │
│  │  └┬──┬───────────────────┴┐│
│  │   ├─▶│John Smith          ││
│  │   │  ├────────────────────┤│
│  │   └─▶│Ivan Petrov         ││
│  │  ┌───┴───────────────────┬┘│
│  └─▶│Analytics              │ │
│     └┬──┬───────────────────┴┐│
│      ├─▶│Eva Ivanova         ││
│      │  ├────────────────────┤│
│      └─▶│Marina Smirnova     ││
│         └────────────────────┘│
└───────────────────────────────┘

Пользователь раскрывает узлы и погружается в ту часть иерархии, что ему нужна. Однако отчетность, которую у нас запрашивают, плоская: файлы CSV и Excel не могут показывать деревья — только таблицы.

На этом противоречии держится вся задача: мы должны перестроить дерево в таблицу. Как только мы освоим технику перестроения, то без труда построим отчеты.

Простые отчеты

Для начала рассмотрим простой отчет: заявки со статусом “в работе” за последние три месяца. Удобство в том, что отчет не требует группировок и агрегаций. Все поля извлекаются стрелочными операторами ->> и #>>. Вот как выглядит запрос:

select
    doc->>'application_id' as app_id,
    doc->>'status' as status,
    doc->>'credit_type' as credit_type,
    (doc->>'created_at')::date as created_at,
    doc #>> '{created_by,name}' as created_by,
    doc #>> '{organization,short_name}' as org_short_name
from
    applications
where
    doc->>'status' in ('active', 'pending')
    and (doc->>'created_at')::timestamptz > now() - interval '3 months'
order by
    (doc->>'created_at')::timestamptz
limit
    1000;

Частичный результат:

┌────────┬────────┬─────────────┬────────────┬────────────┬──────────────────┐
│ app_id │ status │ credit_type │ created_at │ created_by │  org_short_name  │
├────────┼────────┼─────────────┼────────────┼────────────┼──────────────────┤
│ 651889 │ active │ org         │ 2026-03-19 │ User 0889  │ Organization 889 │
│ 399715 │ active │ org         │ 2026-03-19 │ User 0715  │ Organization 715 │
│ 565634 │ active │ org         │ 2026-03-19 │ User 0634  │ Organization 634 │
│ 280547 │ active │ org         │ 2026-03-19 │ User 0547  │ Organization 547 │
│ 590919 │ active │ org         │ 2026-03-19 │ User 0919  │ Organization 919 │
│ 225917 │ active │ org         │ 2026-03-19 │ User 0917  │ Organization 917 │
│ 66410  │ active │ country     │ 2026-03-19 │ User 0410  │ Organization 410 │
│ 255001 │ active │ country     │ 2026-03-19 │ User 0001  │ Organization 1   │
│ 24655  │ active │ country     │ 2026-03-19 │ User 0655  │ Organization 655 │
│ 689536 │ active │ org         │ 2026-03-19 │ User 0536  │ Organization 536 │

Запишем выборку в файл CSV или Excel, и отчет готов. Скрипт, который отвечает за его производство, добавим в расписание cron или аналогичную службу.

В запросе выше все поля обладают общим признаком: путь к ним содержит только объекты (слвари). Такие значения легко извлечь оператором #>>. Все усложняется, когда в отчете участвуют поля с массивами. В следующем примере мы рассмотрим как раз такой случай.

Предположим, для каждой заявки нужно показать пользователей, которые над ней работали. Пользователи – это массив, а путь к ним лежит через отделы – тоже массивы. Вместе они образуют дерево, которое мы не можем показать в файле CSV или Excel.

Что мы можем – так это привести дерево к плоскому виду, где каждый родительский узел повторяется столько раз, сколько у него дочерних элементов. Нам поможет функция jsonb_array_elements, которая принимает jsonb-массив и возвращает его элементы в виде строк. В сигнатуре функции подобный тип описан как set of jsonb. Строки, что вернула функция jsonb_array_elements, автоматически соединяются со строкой, из которых они были получены.

Следующий запрос разложит дерево “заявка → отдел → пользователь” на отдельные составляющие:

select
    doc->>'application_id' as app_id,
    doc->>'status' as status,
    (doc->>'created_at')::date as created_at,
    doc #>> '{created_by,name}' as created_by,
    doc #>> '{organization,short_name}' as org_short_name,
    dep->>'code' as dep_code,
    dep->>'name' as dep_name,
    usr->>'name' as user_name,
    usr->>'role' as user_role
from
    applications,
    jsonb_array_elements(doc->'departments') as dep,
    jsonb_array_elements(dep->'users') as usr
where
    doc->>'status' in ('active', 'pending')
    and (doc->>'created_at')::timestamptz > now() - interval '3 months'
order by
    (doc->>'created_at')::timestamptz
limit
    1000;

Частичный результат:

┌────────┬────────┬────────────┬────────────┬──────────────────┬──────────┬───────────────┬───────────┬────────────────┐
│ app_id │ status │ created_at │ created_by │  org_short_name  │ dep_code │   dep_name    │ user_name │   user_role    │
├────────┼────────┼────────────┼────────────┼──────────────────┼──────────┼───────────────┼───────────┼────────────────┤
│ 399715 │ active │ 2026-03-19 │ User 0715  │ Organization 715 │ dep_15   │ Department 15 │ User 715  │ support        │
│ 399715 │ active │ 2026-03-19 │ User 0715  │ Organization 715 │ dep_15   │ Department 15 │ User 725  │ analyst        │
│ 399715 │ active │ 2026-03-19 │ User 0715  │ Organization 715 │ dep_25   │ Department 25 │ User 735  │ reader         │
│ 399715 │ active │ 2026-03-19 │ User 0715  │ Organization 715 │ dep_25   │ Department 25 │ User 45   │ reader         │
│ 565634 │ active │ 2026-03-19 │ User 0634  │ Organization 634 │ dep_9    │ Department 9  │ User 634  │ analyst        │
│ 565634 │ active │ 2026-03-19 │ User 0634  │ Organization 634 │ dep_9    │ Department 9  │ User 644  │ decision-maker │
│ 565634 │ active │ 2026-03-19 │ User 0634  │ Organization 634 │ dep_19   │ Department 19 │ User 654  │ lead           │
│ 565634 │ active │ 2026-03-19 │ User 0634  │ Organization 634 │ dep_19   │ Department 19 │ User 39   │ support        │
│ 280547 │ active │ 2026-03-19 │ User 0547  │ Organization 547 │ dep_22   │ Department 22 │ User 547  │ lead           │
│ 280547 │ active │ 2026-03-19 │ User 0547  │ Organization 547 │ dep_22   │ Department 22 │ User 557  │ principal      │
│ 280547 │ active │ 2026-03-19 │ User 0547  │ Organization 547 │ dep_32   │ Department 32 │ User 567  │ manager        │
│ 280547 │ active │ 2026-03-19 │ User 0547  │ Organization 547 │ dep_32   │ Department 32 │ User 52   │ decision-maker │
│ 590919 │ active │ 2026-03-19 │ User 0919  │ Organization 919 │ dep_19   │ Department 19 │ User 919  │ decision-maker │
│ 590919 │ active │ 2026-03-19 │ User 0919  │ Organization 919 │ dep_19   │ Department 19 │ User 929  │ lead           │
│ 590919 │ active │ 2026-03-19 │ User 0919  │ Organization 919 │ dep_29   │ Department 29 │ User 939  │ decision-maker │
│ 590919 │ active │ 2026-03-19 │ User 0919  │ Organization 919 │ dep_29   │ Department 29 │ User 49   │ principal      │

Ключевая деталь кроется в этом фрагменте:

from
    applications,
    jsonb_array_elements(doc->'departments') as dep,
    jsonb_array_elements(dep->'users') as usr

Для каждой строки из таблицы applications мы получим набор значений dep. Тип соединения не указывают, потому что он очевиден: строки dep соединяются с теми строками applications, из которых были получены. То же самое происходит с пользователями: из каждой строки dep получаем набор записей usr (слово user не подойдет, потому что оно служебное). Каждый пользователь соединяется с тем отделом, из которого происходит.

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

from
    applications,
    ...
    jsonb_array_elements(usr->'roles')

Получив плоскую таблицу, ее легко привести к нужному виду: выбрать отдельные строки, сгруппировать и так далее. Тем самым снимается противоречие, о котором мы упоминали: вложенности больше нет, и мы вправе делать с данными что угодно – в том числе придать им другую вложенность.

Функция `jsonb_table`

Со временем стало очевидным: почти любая задача, связанная с JSON, начинается с того, что его приводят к плоской таблице. Только имея на руках таблицу, можно применить к ней всю мощь реляционной алгебры. Череда вызовов jsonb_array_elements в целом решает проблему, но хотелось бы универсального решения: такого, чтобы структура JSON была описана декларативно, а скрытый код строил по ней таблицу.

Функция json_table появилась в Postgres с версии 17 (в Postgres Pro – c 15). Она служит как раз для этого – приводит JSON-документ к таблице. Функция учитывает вложенность и выводит нативные типы Postgres. Приведем минимальный пример:

select
    jt.*
from
    applications,
    json_table(doc, '$' columns(
        application_id int         path '$.application_id',
        status         text        path '$.status',
        assigned_to    text        path '$.assigned_to',
        created_at     timestamptz path '$.created_at',
        credit_type    text        path '$.credit_type'
    )) as jt
limit
    1000;

┌────────────────┬──────────┬───────────────────┬───────────────────────────────┬─────────────┐
│ application_id │  status  │    assigned_to    │          created_at           │ credit_type │
├────────────────┼──────────┼───────────────────┼───────────────────────────────┼─────────────┤
│          10177 │ archived │ user_177@test.com │ 2026-03-20 16:25:15.295135+03 │ org         │
│          10178 │ active   │ user_178@test.com │ 2026-01-27 03:01:24.60134+03  │ org         │
│          10179 │ rejected │ user_179@test.com │ 2026-01-20 14:08:31.31944+03  │ org         │
│          10180 │ archived │ user_180@test.com │ 2026-03-16 16:25:14.90315+03  │ country     │
│          10181 │ archived │ user_181@test.com │ 2026-02-24 20:31:46.79655+03  │ country     │
│          10182 │ archived │ user_182@test.com │ 2025-11-27 04:53:55.326635+03 │ org         │
│          10183 │ archived │ user_183@test.com │ 2026-01-06 18:40:52.184655+03 │ org         │
│          10184 │ archived │ user_184@test.com │ 2025-08-22 10:44:59.469485+03 │ org         │

Функция принимает документ, путь JSON Path к элементу, от которого начинается обход, и выражение COLUMNS. В последнем указаны имена полей, тип и путь к значению относительно текущего уровня. Типы выводятся из текстового представления JSON, что крайне удобно: колонки сразу становятся числами, датой или временем.

Выше мы обратились только к первому уровню документа; истинная мощь json_table кроется во вложенных конструкциях. Внутри COLUMNS может быть выражение NESTED с описанием дочерних объектов. В итоговой выборке окажутся все поля: внешние и вложенные. Принцип их соединения такой же, как у jsonb_array_elements: родительский элемент повторяется столько раз, сколько у него потомков.

Вспомним запрос, который возвращает плоское представление заявок, отделов и пользователей. Перепишем его при помощи json_table:

select
    jt.*
from
    applications,
    json_table(doc, '$' columns(
        application_id int         path '$.application_id',
        status         text        path '$.status',
        credit_type    text        path '$.credit_type',
        nested path '$.departments[*]' columns(
            dep_code text path '$.code',
            dep_name text path '$.name',
            nested path '$.users[*]' columns(
                user_name text path '$.name',
                user_role text path '$.role'
            )
        )
    )) as jt
limit
    1000;

Результат:

┌────────────────┬──────────┬─────────────┬──────────┬───────────────┬───────────┬────────────────┐
│ application_id │  status  │ credit_type │ dep_code │   dep_name    │ user_name │   user_role    │
├────────────────┼──────────┼─────────────┼──────────┼───────────────┼───────────┼────────────────┤
│          11457 │ archived │ org         │ dep_7    │ Department 7  │ User 457  │ support        │
│          11457 │ archived │ org         │ dep_7    │ Department 7  │ User 467  │ manager        │
│          11457 │ archived │ org         │ dep_17   │ Department 17 │ User 477  │ principal      │
│          11457 │ archived │ org         │ dep_17   │ Department 17 │ User 37   │ lead           │
│          11458 │ archived │ org         │ dep_8    │ Department 8  │ User 458  │ support        │
│          11458 │ archived │ org         │ dep_8    │ Department 8  │ User 468  │ manager        │
│          11458 │ archived │ org         │ dep_18   │ Department 18 │ User 478  │ reader         │
│          11458 │ archived │ org         │ dep_18   │ Department 18 │ User 38   │ support        │
│          11459 │ archived │ org         │ dep_9    │ Department 9  │ User 459  │ analyst        │
│          11459 │ archived │ org         │ dep_9    │ Department 9  │ User 469  │ manager        │
│          11459 │ archived │ org         │ dep_19   │ Department 19 │ User 479  │ principal      │
│          11459 │ archived │ org         │ dep_19   │ Department 19 │ User 39   │ principal      │

Вызов json_table заменил каскад функций jsonb_array_elements. Новый запрос короче и понятней: в нем отражается структура документа.

Обратите внимание, что в теле COLUMNS пути к значениям начинаются со знака доллара, а не собаки: мы пишем $.code, а не @.code. Выражение COLUMNS ограничивает корень документа текущим уровнем: сослаться на вершину исходного документа невозможно.

Когда мы привели дерево к таблице, к нему применяют условия, например выбирают записи с конкретными отделами или ролями пользователей:

...
where
        dep_code in ('dep_15', 'dep_16', 'dep_17')
    and user_role in ('support', 'reader', 'lead')

Иногда условия помещают в путь JSON Path, используя подзапрос:

select
    jt.*
from
    applications,
    json_table(doc, '$' columns(
        application_id int         path '$.application_id',
        status         text        path '$.status',
        credit_type    text        path '$.credit_type',
        nested path '$.departments[*] ? (@.code == "dep_15" || @.code == "dep_16" || @.code == "dep_16")' columns(
            dep_code text path '$.code',
            dep_name text path '$.name',
            nested path '$.users[*] ? (@.role == "support" || @.role == "reader" || @.role == "lead")' columns(
                user_name text path '$.name',
                user_role text path '$.role'
            )
        )
    )) as jt
limit
    1000;

Важно понимать отличие WHERE от подзапросов JSON Path. Оператор WHERE накладывает условия на итоговое соединение строк: множество записей applications, к которым присоединены отделы, а к ним – пользователи. Чтобы узнать, какими были данные до фильтрации, достаточно закомментировать часть WHERE.

Подзапросы JSON Path устроены по-другому: они фильтруют не итоговую выборку, а записи, которые возвращаются из родительской строки. Возможна ситуация, когда у родителя нет потомков. В этом случае родитель останется в выборке, а поля потомков будут NULL. Так, для запроса выше получим следующий результат:

┌────────────────┬──────────┬─────────────┬──────────┬───────────────┬───────────┬───────────┐
│ application_id │  status  │ credit_type │ dep_code │   dep_name    │ user_name │ user_role │
├────────────────┼──────────┼─────────────┼──────────┼───────────────┼───────────┼───────────┤
│          24001 │ archived │ country     │ <null>   │ <null>        │ <null>    │ <null>    │
│          24002 │ archived │ org         │ <null>   │ <null>        │ <null>    │ <null>    │
│          24003 │ archived │ org         │ <null>   │ <null>        │ <null>    │ <null>    │
│          24004 │ archived │ org         │ <null>   │ <null>        │ <null>    │ <null>    │
│          24005 │ archived │ org         │ dep_15   │ Department 15 │ User 35   │ lead      │
│          24006 │ archived │ org         │ dep_16   │ Department 16 │ <null>    │ <null>    │
│          24007 │ approved │ org         │ <null>   │ <null>        │ <null>    │ <null>    │
│          24008 │ archived │ org         │ <null>   │ <null>        │ <null>    │ <null>    │
│          24009 │ approved │ country     │ <null>   │ <null>        │ <null>    │ <null>    │
│          24010 │ approved │ org         │ <null>   │ <null>        │ <null>    │ <null>    │
│          24011 │ archived │ org         │ <null>   │ <null>        │ <null>    │ <null>    │
│          24012 │ archived │ org         │ <null>   │ <null>        │ <null>    │ <null>    │
│          24013 │ archived │ org         │ <null>   │ <null>        │ <null>    │ <null>    │
│          24014 │ archived │ org         │ <null>   │ <null>        │ <null>    │ <null>    │

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

Функция json_table гибко настраивается. Среди прочего можно указать, что делать, если не удалось вывести тип колонки: вернуть null или ошибку. Ряд настроек определяет, как работать с кавычками. Для нас эти и другие опции не особо важны, но, возможно, пригодятся читателю. Предлагаем ознакомиться с ними в разделе документации “Функции и операторы JSON”.

Параметры в JSON Path

В подзапросах выше мы указали значения явно:

$.departments[*] ? (@.code == "dep_15")

Иногда значения приходят со стороны, и нужно передать их параметрами. Но как передать параметры в JSON Path, ведь путь – это сплошная строка? К счастью, для этого есть средства. Внутри пути на параметр ссылаются как на переменную со знаком доллара:

@.code == $dep

Передача параметра зависит от того, как именно используется путь. Если мы передаем его в функции семейства jsonb_path_query, то вторым параметром указывают json-объект вида переменная -> значение. Покажем это на примере: подготовим выражение get_id_by_role, замкнутое на определенном документе. Выражение ищет код пользователя по его роли; объект параметров формируется при помощи функции jsonb_build_object.

prepare get_id_by_role as
select jsonb_path_query($$
{
  "users": [
    {
      "id": 101,
      "role": "admin",
      "name": "Ivan Petrov"
    },
    {
      "id": 135,
      "role": "manager",
      "name": "Andrey Ivanov"
    },
    {
      "id": 399,
      "role": "programmer",
      "name": "Anna Smirnova"
    }
  ]
}
$$::jsonb,
    '$.users ? (@.role == $role) .id',
    jsonb_build_object('role', $1::text)
) as id;

Вызывая выражение с обычной строкой, мы получим нужный код:

execute get_id_by_role('programmer');

┌─────┐
│ id  │
├─────┤
│ 399 │
└─────┘

execute get_id_by_role('janitor');
-- null

Когда путь с переменной используется в json_table, за ним следует слово passing и пары <значение> as <имя>. При этом на месте значения можно указать литерал, выражение, колонку или параметр SQL:

select
    jt.*
from
    applications,
    json_table(doc, '$' passing 'dep_15' as dep_code, $1::text as user_role
    columns(...)) as jt
limit
    1000;

Обе техники помогут вам избежать инъекций и ошибок синтаксиса.

Отчеты с группировкой

Теперь когда мы знакомы с json_table, работа с отчетностью упрощается. Рассмотрим несколько примеров.

Предположим, требуется показать, сколько отделов задействовано в каждой заявке. Сделать это легко: функция jsonb_array_length принимает JSON-массив и возвращает его длину. Следовательно, число отделов мы получим, передав в функцию поле doc['departments']:

select
    (doc #>> '{application_id}')::int as app_id,
    jsonb_array_length(doc['departments']) as dep_count
from
    applications
limit
    10;

Во всех заявках колонка dep_count равна двум, потому что так устроен скрипт генерации: в каждом поле departments было по два объекта.

┌────────┬───────────┐
│ app_id │ dep_count │
├────────┼───────────┤
│      1 │         2 │
│      2 │         2 │
│      3 │         2 │
│      4 │         2 │
│      5 │         2 │
│      6 │         2 │
│      8 │         2 │
│      9 │         2 │
└────────┴───────────┘

От нас могут потребовать обратную группировку: не отделы по заявкам, а заявки по отделам. Если точнее, каждому отделу мы должны сопоставить число заявок, которые к нему относятся. Кроме того, число заявок нужно вывести в разрезе ролей пользователей, которые над ними работали.

Требования могут показаться сложными, но на самом деле это не так. Задача решается в два шага, при этом первый — получить плоскую таблицу — нам уже знаком. Как только таблица готова, сгруппируем ее по нужным полям: коду отдела и роли. Итак, пишем запрос:

select
    dep_code,
    user_role,
    count(application_id) as app_count

from
    applications,
    json_table(doc, '$' columns(
        application_id int         path '$.application_id',
        status         text        path '$.status',
        nested path '$.departments[*]' columns(
            dep_code text path '$.code',
            dep_name text path '$.name',
            nested path '$.users[*]' columns(
                user_role text path '$.role'
            )
        )
    )) as jt

where
        dep_code in ('dep_15', 'dep_16', 'dep_17')
    and status = 'active'

group by
    dep_code, user_role

order by
    dep_code, user_role

limit
    1000;

Из результата видно, что в условном отделе dep_15 задействованы пользователи с ролями analyst, decision-maker, lead и другими. Суммарно каждой ролью обработано около тысячи заявок.

┌──────────┬────────────────┬───────────┐
│ dep_code │   user_role    │ app_count │
├──────────┼────────────────┼───────────┤
│ dep_15   │ analyst        │      1165 │
│ dep_15   │ decision-maker │      1162 │
│ dep_15   │ lead           │      1098 │
│ dep_15   │ manager        │      1147 │
│ dep_15   │ principal      │      1138 │
│ dep_15   │ reader         │      1218 │
│ dep_15   │ support        │      1168 │
│ dep_16   │ analyst        │      1163 │
│ dep_16   │ decision-maker │      1151 │
│ dep_16   │ lead           │      1157 │
│ dep_16   │ manager        │      1177 │
│ dep_16   │ principal      │      1203 │
│ dep_16   │ reader         │      1134 │
│ dep_16   │ support        │      1177 │
│ dep_17   │ analyst        │      1162 │
│ dep_17   │ decision-maker │      1134 │
│ dep_17   │ lead           │      1137 │
│ dep_17   │ manager        │      1136 │
│ dep_17   │ principal      │      1125 │
│ dep_17   │ reader         │      1132 │
│ dep_17   │ support        │      1148 │
└──────────┴────────────────┴───────────┘

Приведем другой пример группировки. Кроме отделов, заявка хранит еще одну вложенную структуру: суммы и сроки, на которые ее подают. Каждый элемент массива amounts — это объект с суммой денег в центах (копейках), валюта и срок действия относительно даты подачи.

"amounts": [
  {
    "amount": 42278084,
    "period": {
      "d": 6,
      "m": 1,
      "w": 10,
      "y": 8
    },
    "currency": "RUB"
  },
  {
    "amount": 92662590,
    "period": {
      "d": 3,
      "m": 2,
      "w": 10,
      "y": 8
    },
    "currency": "EUR"
  }
]

Следующий запрос вернет эти данные в плоской проекции:

select
    jt.*
from
    applications,
    json_table(doc, '$' columns(
        application_id int path '$.application_id',
        nested path '$.amounts[*]' columns(
            i for ordinality,
            amount   int8 path '$.amount',
            currency text path '$.currency'
        )
    )) as jt
limit
    1000;

┌────────────────┬───┬──────────┬──────────┐
│ application_id │ i │  amount  │ currency │
├────────────────┼───┼──────────┼──────────┤
│         152129 │ 1 │ 35343725 │ USD      │
│         152129 │ 2 │ 67772662 │ RUB      │
│         152130 │ 1 │ 31377772 │ RUB      │
│         152130 │ 2 │ 39103626 │ EUR      │
│         152131 │ 1 │  2026082 │ RUB      │
│         152131 │ 2 │ 91184516 │ USD      │

Руководство хочет знать, на какую сумму всего активных заявок в разрезе указанных валют. Для этого добавим отбор по этим валютам и статусу, группировку по полю currency и агрегатные функции к полям amoun и application_id. Все вместе дает запрос:

select
    currency,
    sum(amount) as amount_total,
    count(application_id) as app_count
from
    applications,
    json_table(doc, '$' columns(
        application_id int  path '$.application_id',
        status         text path '$.status',
        nested path '$.amounts[*]' columns(
            amount   int8 path '$.amount',
            currency text path '$.currency'
        )
    )) as jt
where
        status = 'active'
    and currency in ('USD', 'RUB', 'EUR')

group by currency;

Результат показывает, что в среднем на каждую валюту приходится по 33 тысячи активных заявок. Денежные суммы тоже примерно одинаковы. Подобная равномерность вызвана тем, что при генерации мы не использовали веса для этих полей.

┌──────────┬───────────────┬───────────┐
│ currency │ amount_total  │ app_count │
├──────────┼───────────────┼───────────┤
│ EUR      │ 1673618268900 │     33391 │
│ RUB      │ 1649511396849 │     33006 │
│ USD      │ 1678496810973 │     33427 │
└──────────┴───────────────┴───────────┘

Рекурсивные документы и запросы

Когда мы работаем с json_table, предполагается, что структура документа известна заранее. Но иногда вложенность носит произвольный характер. Например, один из документов, с которым работал автор, хранил иерархию продуктов фирмы. Выглядел он примерно так:

{
   "id":"101",
   "title":"Product A",
   "children":[
      {
         "id":"104",
         "title":"Product B"
      },
      {
         "id":"206",
         "title":"Product C",
         "children":[
            {
               "id":304,
               "title":"Product D"
            },
            {
               "id":323,
               "title":"Product E"
            }
         ]
      }
   ]
}

Это лишь краткая версия документа. Общий принцип был таков: каждый узел содержит данные об услуге и поле children с такими же узлами. Ограничения на вложенность нет: иной продукт достигал тридцати уровней, и все это – внутри одного документа. Руководство желает увидеть иерархию в плоском виде:

┌─────┬───────┬───────────┐
│ id  │ level │   title   │
├─────┼───────┼───────────┤
│ 101 │     0 │ Product A │
│ 104 │     1 │ Product B │
│ 206 │     1 │ Product C │
│ 304 │     2 │ Product D │
│ 323 │     2 │ Product E │
└─────┴───────┴───────────┘

Очевидно, json_table уже не выручит нас, потому что неизвестно заранее, сколько понадобится выражений NESTED.

Обычно подобные документы обходят силами Python или другого языка. Можно, однако, добиться желаемого силами Postgres – использовать рекурсивные запросы. Подобные запросы состоят из двух частей, разделенных оператором UNION (ALL). Первая часть вызывается один раз и возвращает начальные данные. Вторая часть вызывается многократно, при этом в первый раз она отталкивается от начальных данных, а в последующие – от предыдущего результата. Полученные записи оседают в общей таблице до тех пор, пока очередной результат не пуст. Формально процесс является циклом или сверткой, но в терминах Postgres называется рекурсивным.

Приведем первую часть запроса: она возвращает ключ (id) верхнего узла и некоторые его свойства. Потомки элемента помещаются в отдельное поле children:

select
    doc->>'id' as id,
    doc->>'title' as title,
    0 as level,
    doc->'children' as children
from
    ...

В рекурсивной части прошлая выборка доступна под псевдонимом rec (см. полный запрос ниже). В этом месте многие разработчики заблуждаются: они считают, что rec ссылается на все накопленные данные. Это не так: внутри рекурсивной части rec означает предыдущий результат. Но когда мы ссылаемся на rec вне рекурсии, он содержит итоговую таблицу.

В повторяющейся части мы делаем почти то же самое, но выбираем данные из потомков прошлого результата. Выражение jsonb_array_elements(children) вернет множество записей; из каждой получаем поля и новых потомков. Когда потомков не окажется ни у кого, процесс завершится. Подготовим выражение, которое зависит от одного параметра — документа:

prepare get_product_table as
with
recursive rec as (
    select
        doc->>'id' as id,
        doc->>'title' as title,
        0 as level,
        doc->'children' as children
    from
        (values ($1::jsonb)) as _(doc)
    union all
    select
        doc->>'id' as id,
        doc->>'title' as title,
        level + 1 as level,
        doc->'children' as children
    from
        rec,
        jsonb_array_elements(children) as _(doc)
)
select id, level, title
from rec;

Вызовем его с многоуровневым документом:

execute get_product_table($$
{
   "id":"101",
   "title":"Product A",
   "children":[
      {
         "id":"104",
         "title":"Product B"
      },
      {
         "id":"206",
         "title":"Product C",
         "children":[
            {
               "id":304,
               "title":"Product D"
            },
            {
               "id":323,
               "title":"Product E"
            }
         ]
      }
   ]
}
$$::jsonb);

Результат:

┌─────┬───────┬───────────┐
│ id  │ level │   title   │
├─────┼───────┼───────────┤
│ 101 │     0 │ Product A │
│ 104 │     1 │ Product B │
│ 206 │     1 │ Product C │
│ 304 │     2 │ Product D │
│ 323 │     2 │ Product E │
└─────┴───────┴───────────┘

Рекурсивные запросы полезны для обхода деревьев, графов, иерархий. Конечно, можно сделать это в и приложении: выгрузить документ и обработать силами императивного языка. Однако часто возможностей Postgres хватает, чтобы уладить все на месте.

Обратите внимание: уже в который раз мы объявляем подготовленное выражение (PREPARE) и вызываем его с параметрами (EXECUTE). Первое удобство в том, что код и данные не перемешиваются, и легко изучить то и другое по отдельности. Во-вторых, в подготовленное выражение можно передать разные значения, избежав тем самым повторов кода. Эти и другие возможности предлагают функции, о которых читатель узнает в следующем параграфе.

Знакомство с функциями

Чем дольше вы работаете с JSON, тем очевидней факт: каждый документ можно считать маленькой базой данных. В простых случаях мы извлекаем значение по пути:

doc #>> '{organization,short_name}'

Однако иной раз этого недостаточно: пути бывают сложными, зависят от параметров или других полей документа. В особых случаях нас интересует не одно поле, а их композиция, например сумма, количество, соединение строк через запятую. Набирая подобный код снова и снова, вы придете к выводу, что его нужно централизовать: объявить однажды и затем ссылаться.

В Postgres для этого служат функции. Они выполняют ту же работу, что и в обычных языках программирования: хранят логику в именованных блоках. Функции сокращают повторы в коде, делают его понятней; они полезны в построении абстракций.

Функция принимает параметры и возвращает результат. Некоторые функции ничего не принимают и ничего не возвращают. Результат таких функций обозначен типом void.

Не путайте функции с процедурами: в Postgres это хоть и близкие вещи, но не одно и то же. Одно из отличий в том, что функции не могут управлять транзакциями: нельзя написать функцию, которая выполняет BEGIN...COMMIT. Процедуры, напротив, имеют такое право, однако они ничего не возвращают. Далее по тексту мы будем говорить только о функциях.

Давайте напишем простую функцию. Она принимает заявку и возвращает ее номер, приведенный к целому числу. Вот ее определение:

create or replace function get_application_id(doc jsonb)
returns int8
language sql immutable strict parallel safe
return (doc->>'application_id')::int8;

Выполним запрос с этой функцией. Операция вызова не отличается от других языков: за именем следуют круглые скобки с аргументами.

select get_application_id(doc) as app_id
from applications limit 10;

┌────────┐
│ app_id │
├────────┤
│ 152641 │
│ 152642 │
│ 152643 │
│ 152644 │
│ 152645 │
│ 152646 │
│ 152647 │
│ 152648 │
│ 152649 │
│ 152650 │
└────────┘

Рассмотрим, что входит в описание функции. Выражение create or replace function создает функцию или обновляет ее, если таковая уже имеется. При замене функции важно, чтобы ее сигнатура (типы аргументов и результата) оставались прежними. В противном случае функцию удаляют и создают новую.

Выражение get_application_id задает имя функции. Общее правило таково: используйте только символы a-zA-Z0-9_, чтобы не приходилось брать имя в двойные кавычки. И раз уж мы заговорили об именах, давайте обсудим префиксы и пространства имен.

Схемы и префиксы

Современные языки предлагают модули или пакеты, чтобы сущности с одинаковыми именами не вступали в конфликт. Когда вы начнете использовать функции в Postgres, перед вами встанет та же проблема: как избежать конфликтов имен.

Первое и наивное решение в том, чтобы предварять имена особым префиксом. Поскольку наши функции работают с заявками, добавим к ним частичку app_ (от слова application), например app_get_number, app_get_org_name и так далее.

Второй и более грамотный способ — создать схему app и поместить в нее все, что относится к заявкам:

create schema app;

create or replace function app.application_id(doc jsonb)
returns int8
language sql immutable strict parallel safe
return (doc->>'application_id')::int8;

Обратимся к функции, явно указав схему:

select app.application_id(doc) as app_id
from applications limit 10;

Если сослаться на любой объект без схемы (функцию, таблицу и так далее), Postgres будет искать ее в схемах, заданных в переменной search_path. Это список через запятую, и Postgres обходит его слева направо. Давайте проверим, что в этой переменной:

show search_path;
-- "$user", public

Схема "$user" — особенная по двум причинам. Во-первых, из-за знака доллара ее берут в двойные кавычки, чтобы не получить ошибку синтаксиса. Во-вторых, она означает схему с именем текущего пользователя. Если мы работаем под пользователем john и вызывали функцию application_id, Postgres проверит ее наличие в схеме john. Отсутствие функции или даже схемы не вызовет ошибки: Postgres перейдет к следующему элементу search_path и так далее. Если же функция не найдена нигде, запрос окончится аварийно.

Добавьте в search_path схему app, и функция будет найдена даже в краткой записи:

set search_path to app,public,"$user";

select application_id(doc) as app_id
from applications limit 10;
-- no error

Какой способ выбрать – с префиксом или отдельной схемой – решать читателю. В крупных проектах сущности разносят по схемам, и в целом это вопрос времени: рано или поздно станет тяжело ориентироваться в глобальном пространстве имен. На страницах книги ради краткости мы ограничимся префиксом.

Характеристики функций

Выражение language sql означает язык, на котором написана функция. В Postgres встроены два диалекта для описания процедур и функций: sql и pgplsql. Первый из них, SQL, не отличается от классического языка запросов. Диалект pgplsql предлагает больше возможностей, привычных программистам: переменные, циклы, ветвления, возбуждение исключений и их перехват. Если диалект не указан (нет фразы language), по умолчанию он считается sql.

Отличий между sql и pgplsql довольно много. Их перечисление уведет нас от основной темы, поэтому общий совет такой: по возможности используйте SQL. Переходите к pgplsql, только если возможностей SQL не хватает.

Интересны характеристики функции: immutable, strict и другие. Коротко разберем, что есть что.

Слово immutable означает категорию изменчивости функции. Их бывает три (по нарастанию строгости): изменчивая (volatile), стабильная (stable) и неизменяемая (immutable). Категория отвечает на вопрос, как поведет себя функция при вызове с одинаковыми параметрами. Изменчивая функция (volatile) подразумевает, что либо она изменяет содержимое базы, либо результат зависит не только от входных аргументов, но и внешних факторов вроде текущей даты или случайных чисел. По умолчанию функция считается изменчивой.

Признак стабильности (stable) говорит о том, что, во-первых, функция не меняет данные в базе. Во-вторых, в рамках одного запроса для одинаковых аргументов функция вернет один и тот же результат.

Неизменяемость (immutable) означает то же самое, что и stable, но строже: для одинаковых аргументов функция вернет одинаковый результат в разных запросах, неважно какой между ними интервал — час или год.

Категории изменчивости нужны для внутренних оптимизаций. Если функция отмечена стабильной или неизменяемой, Postgres может сократить часть ее вызовов за счет кэширования. При первом вызове строится кэш вида аргументы -> результат, а перед вторым вызовом Postgres пытается найти значение в кэше.

Если функция встречается в выражении индекса, она обязана быть стабильной или неизменяемой. Postgres никак не проверяет это свойство, и оно остается на совести разработчика. Если функция объявлена immutable, но не является таковой, вас ждет странное поведение.

В этой главе мы будем работать с неизменяемыми, чистыми функциями. Их признаки следующие: функция делает только SELECT-запросы к документам, переданным в параметрах — но не к базе данных! Также не используются случайные величины, глобальные переменные, параметры текущей сессии. Если все это выполняется, функцию можно пометить неизменяемой.

Свойство функции STRICT (строгая) недостаточно ясно выражает его природу. Логика такова: если хотя бы один аргумент является NULL, тело функции даже не получит управления. Вместо этого функция сразу возвращает NULL.

Польза STRICT на самом деле огромна. Postgres устроен так, что почти любое действие с NULL возвращает NULL. Без STRICT разработчик вынужден писать долгие проверки: если первый аргумент IS NULL, то вернуть NULL; если второй аргумент IS NULL, тоже вернуть NULL и так далее. Флаг STRICT выполняет эту работу за вас.

В редких случаях параметр может быть NULL, и тогда STRICT не указывают. В наших примерах параметры обязательны и не допускают NULL, поэтому будем использовать STRICT.

Свойство PARALLEL SAFE означает, что функция безопасна для параллельного выполнения. Иногда Postgres выполняет сложные запросы по частям в отдельных процессах, а позже объединяет результаты. Для этого необходим ряд условий; одно из них в том, чтобы функции, которые принимают участие в запросе, допускали параллельность. Как и в случае с изменчивостью, Postgres не может определить свойство PARALLEL самостоятельно – за него отвечает программист.

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

Итак, мы рассмотрели основные положения, связанные с функциями. Кое-где мы срезали углы: опустили подробное описание характеристик с примерами их работы. Чтобы разобраться с функциями, обратитесь к книге Евгения Моргунова “PostgreSQL. Профессиональный SQL”. Вас ждут подробное изложение материала, примеры, планы, метрики и так далее.

Примеры функций

С багажом новых знаний вернемся к функции, которую только что написали:

create or replace function get_application_id(doc jsonb)
returns int8
language sql immutable strict parallel safe
return (doc->>'application_id')::int8;

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

create or replace function app_user_names(doc jsonb)
returns text
language sql immutable strict parallel safe as $$
select
    string_agg(distinct usr->>'name', ', ')
from
    jsonb_array_elements(doc['departments']) as deps(dep),
    jsonb_array_elements(dep['users']) as users(usr)
$$;

Вызов:

select app_user_names(doc) as users
from applications limit 10;

Результат:

┌───────────────────────────────────────┐
│                 users                 │
├───────────────────────────────────────┤
│ User 46, User 641, User 651, User 661 │
│ User 47, User 642, User 652, User 662 │
│ User 48, User 643, User 653, User 663 │
│ User 49, User 644, User 654, User 664 │
│ User 50, User 645, User 655, User 665 │
│ User 51, User 646, User 656, User 666 │
│ User 52, User 647, User 657, User 667 │
│ User 53, User 648, User 658, User 668 │
│ User 54, User 649, User 659, User 669 │
│ User 30, User 650, User 660, User 670 │
└───────────────────────────────────────┘

Функция принимает документ и формирует из него плоскую таблицу, затем поле usr->>'name' агрегируется функцией string_agg. Обратите внимание на слово DISTINCT – оно гарантирует, что в результате не будет повторов. Они возможны, если один пользователь работал над заявкой под разными ролями.

В агрегатную функцию можно добавить сортировку. В этом случае элементы следуют по алфавиту, что очень удобно:

string_agg(distinct usr->>'name', ', ' order by usr->>'name')

Подобное представление — в строку через разделитель — часто требуется в отчетах, которые, как мы помним, плохо справляются с иерархией. Объединение потомков в строку применяется ко многим вложенным полям, например валютам, отделам и так далее.

Следующий пример. Заявка хранит историю статусов в виде массива. Его элементы – объекты с меткой времени, статусом и ссылкой на пользователя:

"journal": [
  {
    "event": "active",
    "user_id": "6d4fd d3a-0cea-4927-80e4-39e06fcdc2ae",
    "datetime": "2026-01-17T00:35:25.83857+03:00"
  },
  {
    "event": "active",
    "user_id": "dad06c41-2286-47f0-8cea-62ab70608c12",
    "datetime": "202 5-09-06T12:10:26.6973+03:00"
  },
  {
    "event": "pending",
    "user_id": "9f6c794e-13ae-4dcf-b946-ba984823b515",
    "datetime": "2025-08-12T02:46:28.821215+03:00"
  }
]

Требуется узнать, кто установил последний статус.

Если предположить, что элементы следуют по нарастанию времени, достаточно взять пользователя из последнего элемента. Но иногда порядок массива нарушен, и мы получим неверные данные. Так что сначала приведем массив объектов к таблице и упорядочим по убыванию времени, а дальше возьмем первую запись. Все вместе дает функцию:

create or replace function app_last_event_user_id(doc jsonb)
returns uuid
language sql immutable strict parallel safe as $$
select
    jt.user_id
from
    json_table(doc, '$.journal[*]' columns(
        user_id  uuid        path '$.user_id',
        datetime timestamptz path '$.datetime'
    )) as jt
order by datetime desc
limit 1
$$;

Усложним задачу: путь нас интересует не просто последнее событие, а с определенным типом (поле event). Расширим функцию так, чтобы она принимала параметр event и фильтровала по нему таблицу:

create or replace function app_last_event_user_id(doc jsonb, event text)
returns uuid
language sql immutable strict parallel safe as $$
select
    jt.user_id
from
    json_table(doc, '$.journal[*]' columns(
        user_id  uuid        path '$.user_id',
        event    text        path '$.event',
        datetime timestamptz path '$.datetime'
    )) as jt
where jt.event = event
order by datetime desc
limit 1
$$;

До сих пор мы писали функции, чей результат – одно значение. В сигнатуре таких функций после слова returns указан тип, например int, text или uuid. Если функция написана на диалекте sql, то ее тело – либо выражение return, либо запрос select. В последнем случае запрос должен иметь одну колонку. Результат берется из первой строки запроса, а для пустой выборки он будет NULL.

Иногда от функции ожидают нескольких значений одного типа — множества в терминах Postgres. Множество ведет себя как набор строк, в каждой из которых одно значение. Также функция может вернуть полноценную таблицу с колонками разных типов.

Напишем функцию, которая возвращает сотрудников заявки, однако не через точку с запятой, а как множество текстовых строк. Для этого мы вызываем функцию jsonb_path_query, которая возвращает множество jsonb. Из ее результата мы, как из подзапроса, выбираем текстовые поля при помощи оператора ->> и нулевого индекса. Напомним, что если применить ->>0 к JSON-строке, то получим нативную строку Postgres:

create or replace function app_users(doc jsonb)
returns setof text
language sql immutable strict parallel safe as $$
select
    sub->>0 as name
from
    jsonb_path_query(doc, '$.departments.users.name') as sub
$$;

Опробуем функцию на заявках – каждая из повторяется столько раз, сколько в ней пользователей:

select id, app_users(doc) from applications limit 8;

┌──────────────────────────────────────┬───────────┐
│                  id                  │ app_users │
├──────────────────────────────────────┼───────────┤
│ 00000000-0000-0000-0000-000000635650 │ User 650  │
│ 00000000-0000-0000-0000-000000635650 │ User 660  │
│ 00000000-0000-0000-0000-000000635650 │ User 670  │
│ 00000000-0000-0000-0000-000000635650 │ User 30   │
│ 00000000-0000-0000-0000-000000635652 │ User 652  │
│ 00000000-0000-0000-0000-000000635652 │ User 662  │
│ 00000000-0000-0000-0000-000000635652 │ User 672  │
│ 00000000-0000-0000-0000-000000635652 │ User 32   │
└──────────────────────────────────────┴───────────┘

Также функция можно вернуть полноценную таблицу. Ее структуру описывают в сигнатуре после слова returns, например:

returns table (
    app_id int8,
    dep_code text,
    dep_name text,
    user_email text,
    user_role text
)

Напишем функцию, которая вернет данные о департаментах и пользователях в виде плоской таблицы:

create or replace function app_dep_user_table(doc jsonb)
returns table (
    app_id int8,
    dep_code text,
    dep_name text,
    user_email text,
    user_role text
)
language sql immutable strict parallel safe as $$
select
    jt.*
from
    json_table(doc, '$' columns(
        app_id int8 path '$.application_id',
        nested path '$.departments[*]' columns(
            dep_code text path '$.code',
            dep_name text path '$.name',
            nested path '$.users[*]' columns(
                user_email text path '$.email',
                user_role  text path '$.role'
            )
        )
    )) as jt
$$;

Пример ее вызова:

select
    tab.*
from
    applications,
    app_dep_user_table(doc) as tab
where
    id = '00000000-0000-0000-0000-000000000001';

┌────────┬──────────┬───────────────┬──────────────────┬───────────┐
│ app_id │ dep_code │   dep_name    │    user_email    │ user_role │
├────────┼──────────┼───────────────┼──────────────────┼───────────┤
│      1 │ dep_1    │ Department 1  │ user_1@test.com  │ analyst   │
│      1 │ dep_1    │ Department 1  │ user_11@test.com │ manager   │
│      1 │ dep_11   │ Department 11 │ user_21@test.com │ principal │
│      1 │ dep_11   │ Department 11 │ user_31@test.com │ reader    │
└────────┴──────────┴───────────────┴──────────────────┴───────────┘

Функции, возвращающие таблицы, очень удобны. В фирме может быть соглашение о том, как выглядит документ в плоском виде для обмена между отделами или сервисами. В этом случае поможет функция, которая приводит заявку к нативной таблице Postgres. Строки из этой таблицы передают клиенту, вставляют в другую таблицу, формируют их них CSV и так далее.

Выше мы рассмотрели, как хранится срок заявки (путь amounts[*].period). Это объект с полями y, m, w и d, которые означают число лет, месяцев, недель и дней. Зная дату начала, прибавим к ней все эти компоненты и получим дату конца. Подобная задача — идеальный кандидат для функции.

Договоримся, что функция принимает время начала с типом ts_from и объект JSON под названием period. Его мы раскладываем на поля y m, w, d при помощи json_table. Вызов json_table можно заменить оператором ->> и ручным приведением типов, но код получится слишком шумным.

Когда период разобран на составляющие, к дате прибавляются интервалы 1 year, 1 month и другие, каждый умноженный на свою компоненту.

create or replace function period_to_ts(ts_from timestamptz, period jsonb)
returns timestamptz
language sql immutable strict parallel safe as $$
select
    ts_from + interval '1 year'  * y
            + interval '1 month' * m
            + interval '7 days'  * w
            + interval '1 day'   * d
from
    json_table(period, '$' columns(
        y integer path '$.y',
        m integer path '$.m',
        w integer path '$.w',
        d integer path '$.d'
    ))
$$;

Быстрая проверка:

select period_to_ts(date('2025-01-01'), $$
{
    "y": 1, "m": 2, "w": 3, "d": 4
}
$$::jsonb) as ts;

┌────────────────────────┐
│           ts           │
├────────────────────────┤
│ 2026-03-26 00:00:00+03 │
└────────────────────────┘

Действительно: если к дате 2025-01-01 прибавить один год, два месяца, три недели и четыре дня, получится 26 марта 2026 года.

При подобных вычислениях учитывайте, что какой-то составляющих может не быть, потому что ее посчитали равной нулю. Однако с точки зрения Postgres она будет NULL, и весь каскад вычислений тоже вернет NULL. Чтобы этого не случилось, оберните каждую компоненту формой coalesce, которая возвращает первый отличный от NULL аргумент. С ней вычисление даты выглядит так:

ts_from + interval '1 year'  * coalesce(y, 0)
        + interval '1 month' * coalesce(m, 0)
        + interval '7 days'  * coalesce(w, 0)
        + interval '1 day'   * coalesce(d, 0)

Надеемся, эти примеры раскрыли тезис, который мы высказали в начале главы. Каждый JSON-документ представляет собой базу данных в миниатюре. Порой из нее сложно извлечь те или иные данные, и вам помогут функции. Если вынести код в именованный блок, пользоваться им гораздо легче.

До сих пор мы писали функции, которые выбирают что-то из переданного документа. В числе прочего функции могут изменить документ, точнее вернуть его новую копию. В качестве демонстрации напишем функцию app_add_event. Она принимает документ, код пользователя и событие и добавляет в журнал новый объект. Функция императивна и требует переменных, поэтому укажем диалект plpgsql. В нем мы укажем секцию declare для переменных и begin/end с телом функции:

create or replace function app_add_event(doc jsonb, user_id uuid, event text)
returns jsonb
language plpgsql immutable strict parallel safe as $$
declare
    journal jsonb;
begin
    journal := coalesce(doc['journal'], '[]'::jsonb);
    journal := journal || jsonb_build_object(
        'event', event,
        'user_id', user_id::text,
        'datetime', now()::text
    );
    return doc || jsonb_build_object('journal', journal);
end;
$$;

Сперва мы получаем массив событий с учетом того, что поля может не быть (форма coalesce вернет первый отличный от NULL аргумент). Вторая операция – добавить к журналу объект, который мы строим функцией jsonb_build_object. Последний шаг – заменить в документе поле journal на новый массив. Важно помнить, что тип jsonb является неизменяемым: каждая функция или оператор возвращает копию документа. Мы лишь перезаписываем переменную (ссылку) на документ.

Убедимся, что функция работает:

select
    jsonb_pretty(
        app_add_event(
            '{"journal": []}'::jsonb,
            '6d4fdd3a-0cea-4927-80e4-39e06fcdc2ae'::uuid,
            'created'
        )
    )
as doc_new;

┌────────────────────────────────────────────────────────────────┐
│                            doc_new                             │
├────────────────────────────────────────────────────────────────┤
│ {                                                             ↵│
│     "journal": [                                              ↵│
│         {                                                     ↵│
│             "event": "created",                               ↵│
│             "user_id": "6d4fdd3a-0cea-4927-80e4-39e06fcdc2ae",↵│
│             "datetime": "2026-07-19 14:08:59.977098+03"       ↵│
│         }                                                     ↵│
│     ]                                                         ↵│
│ }                                                              │
└────────────────────────────────────────────────────────────────┘

Временные функции

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

Вот в каком случае это полезно. Предположим, вы запустили в производство отчет, и начался период его стабилизации: вам присылают правки, вы их исправляете. Если делать на каждое изменение миграцию, со временем их станет слишком много. Поэтому один из вариантов – не хранить функции в базе, а перед запуском отчета объявлять их временные версии.

В Postgres нельзя создать временную функцию по аналогии с таблицей: синтаксис create function не допусткает слова temp. Есть, однако, уловка: воспользоваться схемой pg_temp. Эта схема привязана к конкретному соединению; две схемы pg_temp из разных соединений не пересекаются, даже если обслуживают одного пользователя. Когда соединение закрывается, исчезает и содержимое pg_temp.

На уровне движка Postgres эти схемы имеют уникальные имена: pg_temp_13, pg_temp_32 и так далее. Однако каждое подключение видит только свою схему без числового суффикса.

Идея в том, чтобы объявить функции в схеме pg_temp, а потом ссылаться на них, используя полное имя:

create or replace function pg_temp.some_calc(x int, y int)
returns int
language sql immutable strict parallel safe
return x + y;

select pg_temp.some_calc(3, 4) as num;
-- 7

В одном из отчетов автора сделано именно так. Он состоит из двух .sql-файлов: первый – с функциями, объявленными в pg_temp:

do $functions$ begin

  create or replace function pg_temp.func_a()
  returns int
  language sql as $$
  select 1;
  $$;

  create or replace function pg_temp.func_b()
  returns int
  language sql as $$
  select 2;
  $$;

end;

$functions$;

Обратите внимание, что функции находятся в анонимном блоке DO. Дело в том, что выражение CREATE FUNCTION не позволяет задать несколько функций разом. Чтобы не выполнять серию запросов, мы объединяем их в один блок: внутри DO может быть сколько угодно операторов SQL. С точки зрения Postgres это одно выражение.

Также обратите внимание, на верхнем уровне DO мы использовали именованный тег $functions$ , чтобы он не конфликтовал с тегами $$ в функциях.

Второй файл содержит запрос, который переиспользует функции из pg_temp. Когда запускается задача на построение отчета, она выполняет запрос из первого файла, а затем из второго; результат записываются в Excel. Далее приложение отключается от базы, и временные функции исчезают.

За счет этой техники функции хранят в коде приложения, а не базе. Создавать миграции на каждое изменение не требуются.

У схемы pg_temp еще одно преимущество: она доступна для записи даже когда база открыта в режиме чтения (read only). Если у вас ограниченный доступ, вы по-прежнему можете создать функции и таблицы в pg_temp и экспериментировать с ними.

Тестирование функций

Как и любой код, функции нуждаются в проверках: что случится, если передать NULL вместо аргумента? Что если в документе нет нужно поля? Чтобы не проверять все варианты вручную, пишут тест: автоматическую проверку, которая запускает код и убеждается, что результат совпадает с ожидаемым.

Для Postgres написаны несколько тестовых фреймворков: pgtap, pgunit, testgres. У каждого из них свои требования к установке и запуску. Ниже мы рассмотрим альтернативный подход: проверим функции в приложении.

Когда вы пишете программу, наверняка у вас есть тесты. Часть из них называются юнит-тестами: они не обращаются в сеть и проверяют только вычисления. Интеграционные тесты, напротив, запускают в окружении, максимально похожем на боевое. Им доступны настоящие сервера Postgres, Redis и других сервисов; последние запускаются в Docker или других службах виртуализации.

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

Приведем интеграционный тест на языке Python. Он использует фикстуру – функцию, которая дополняет тест какой-то логикой. В данном случае фикстура подключается к базе и производит объект Connection:

import pytest

TEST_DB_URL = "postgresql://localhost..."

@pytest.fixture(scope="session")
def db_conn():
    """
    Bootstrap a database connection for the entire test suite.
    """
    conn = create_connection(TEST_DB_URL)
    yield conn
    close_connection(conn)

Тест ниже зависит от этой фикстуры и запустится, когда соединение уже установлено. Тест вызывает функцию jsonb_string_agg, которую мы написали ранее. Параметры передаются явно из Python. Драйвер устроен так, что список [1, 2, 3] будет передан в виде json-строки. Далее мы проверяем поле val из первой строки результата. Если оно не равно ожидаемому значению, фиксируется ошибка.

def test_jsonb_string_agg(db_conn):
    query = "select jsonb_string_agg(?::text, ?::jsonb) as val"
    result = db_conn.execute(query, (', ', [1, 2, 3]))
    assert result[0].val = "1, 2, 3"

Код выше легко расширить: передать null (в Python – None), некорректный документ, вызвать функцию без аргументов и многое другое. Фреймворк pytest предлагает декоратор parametrize, чтобы прогнать тест со множеством аргументов. С ним мы бы записали тест так:

@pytest.mark.parametrize("sep, array, expected", [
    (", ",  [1, 2, 3],             "1, 2, 3"),
    (" | ", ["test", false, 42.9], "test | false | 42.9"),
    (None,  None,                  None),
])
def test_jsonb_string_agg_args(db_conn, sep, array, expected):
    query = "select jsonb_string_agg(?::text, ?::jsonb) as val"
    result = db_conn.execute(query, (sep, array))
    assert result[0].val = expected

Если понадобится еще один случай, мы расширим список parametrize, не меняя при этом сам тест.

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

Выгрузка отчетов

Предположим, мы написали запрос, который возвращает то, что нужно. Как перенести результат в другое место, например на диск или сетевой ресурс? Обычно это делают в лоб: выполняют запрос, читают строки, формируют CSV-файл и куда-то его записывают. Здесь возможны следующие улучшения.

Чтобы забрать из базы большой набор данных, используйте оператор COPY. Он принимает либо таблицу и список столбцов, либо произвольный запрос. В запросе не может быть параметров, то есть подстановок вида $1 или ?. Все параметры должны быть указаны явно (“захардкожены”):

copy applications (id, doc, created_at) to ‘/Users/ivan/work/pg-json-book-code/applications.csv’ with (format csv, header on);

Кроме запроса или таблицы, в COPY указывают назначение (после TO) – куда записывать данные. Назначением может быть файл, стандартный поток или процесс. Чаще всего данные отправляют в поток (STDOUT) и читают в приложении.

COPY поддерживает три формата данных. Первые два называются TEXT и CSV и в целом мало отличаются друг от друга. Поля представлены текстом, а между ними – разделители, например точка с запятой или знак табуляции. Если в строках встречаются служебные символы, они экранируются – предваряются обратной косой чертой.

Третий формат называется BINARY, двоичный. В нем данные передаются в сыром виде, то есть именно так, как они хранятся на диске. Прочитать формат BINARY, не зная его устройства, невозможно; для этого служат специальные библиотеки, например pg-bin автора. К достоинствам двоичного формата относятся его скорость и компактность: он на 20-30% быстрее текстовых аналогов и на столько же меньше.

Команда COPY работает и в обратную сторону – для загрузки данных из источника в базу. Поддерживаются те же виды источников (файл, поток, процесс) и форматы (CSV, TEXT, BINARY). Поскольку наша глава об отчетности, будем говорить только о выгрузке; разобравшись с ней, читатель без труда освоит загрузку данных.

Как мы упоминали, приёмником данных может быть файл (путь к нему должен быть абсолютным). Что именно делать с файлом после выгрузки – остается на ваше усмотрение. На сервере может быть скрипт, который запускается по расписанию и куда-то его копирует. Утилита csv2xlsx конвертирует CSV в формат Excel, чтобы предоставить клиентам офисный документ (открывать CSV в Excel затруднительно).

Иногда Postgres запущен в Docker или иной программе виртуализации. В этом случае к нему подключают виртуальный том, который служит посредником для обмена файлами. Например, внутри контейнера директория /exchange связана как раз с таким томом. Оператор COPY записывает файл в эту директорию, и отчет становится виден снаружи контейнера. Оттуда его забирает другая программа.

Данные COPY можно читать при помощи клиента к Postgres. Так, драйвер JDBC предлагает класс CopyManager для передачи данных в обоих направлениях. Класс опирается на потоки – экземпляры InputStream и OutputStream. Приведем минимальный код, чтобы записать разультат COPY в CSV-файл диск:

BaseConnection pgConn = (BaseConnection) conn.unwrap(BaseConnection.class);

CopyManager copyManager = new CopyManager(pgConn);

String sql = "COPY (select ...) TO STDOUT WITH (FORMAT CSV, HEADER, DELIMITER ',')";

try (FileOutputStream out = new FileOutputStream("/path/to/file.csv")) {
    long rows = copyManager.copyOut(sql, out);
    System.out.println("Rows exported: ", rows);
}

Метод copyOut принимает SQL-выражение COPY и выходной поток, экземпляр OutputStream. Он читает данные порциями, пока не придет особое сообщение, и направляет в поток. В свою очередь поток связан с файлом, сокетом, байтовым массивом или другим назначением. В конце Postgres уведомит нас о том, сколько строк было передано; метод copyOut вернет это число.

Работая с CSV, помните, что он отлично сжимается. Для небольших выборок сжатие некритично, однако иные отчеты занимают гигабайт и больше. Хранить их и передавать по сети затруднительно: если сеть “моргнет”, загрузка оборвется. Алгоритм gzip сжимает подобный CSV до 30-40 мегабайт, и его передача ускорится на порядки.

COPY не поддерживает сжатие напрямую, однако это легко исправить. Приёмником данных может выступить программа (выражение to program), которая запускается на сервере и принимает данные через стандартный поток ввода. В нашем случае это утилита gzip, которая направляет сжатый результат в файл:

copy (
  /* your select query */
) to program 'gzip > /Users/ivan/work/pg-json-book-code/report.csv.gzip' with (format csv, header on);

Если вы пользуетесь CopyManager в приложении, оберните выходной поток классом GZIPOutputStream. В выходном файле окажутся сжатые данные.

Алгоритм lz4 предлагает более эффективное и быстрое сжатие. Установите в систему одноименную утилиту и замените в запросе gzip на lz4. Чтобы прочитать сжатый файл в Java, используйте библиотеку lz4-java и классы LZ4FrameInputStream и LZ4FrameOutputStream.

Отчеты и представления

Иногда отчет делают представлением: объявляют сущность view при помощи запроса. Ниже мы создали представление с активными заявками за последние три месяца:

create or replace view v_active_apps_3_months as
select
    doc->>'application_id' as app_id,
    doc->>'status' as status,
    doc->>'credit_type' as credit_type,
    (doc->>'created_at')::date as created_at,
    doc #>> '{created_by,name}' as created_by,
    doc #>> '{organization,short_name}' as org_short_name
from
    applications
where
    doc->>'status' in ('active', 'pending')
    and (doc->>'created_at')::timestamptz > now() - interval '3 months';

Чтобы получить отчет, прочитаем представление как простую таблицу:

select * from v_active_apps_3_months limit 100;

Если в представлении много вычислений и агрегаций, запрос будет долгим. Подключившись в разгар рабочего дня, потребитель повесит базу сложным запросом. Чтобы этого не случилось, представления материализуют – сбрасывают данные в физическую таблицу. При ее чтении исходный запрос не выполняется: берется результат, полученный ранее. Вот как объявить материализованное представление для отчета:

create materialized view if not exists mv_active_apps_3_months as
select
    (doc->>'application_id')::int8 as app_id,
    doc->>'status' as status,
    doc->>'credit_type' as credit_type,
    (doc->>'created_at')::date as created_at,
    doc #>> '{created_by,name}' as created_by,
    doc #>> '{organization,short_name}' as org_short_name
from
    applications
where
    doc->>'status' in ('active', 'pending')
    and (doc->>'created_at')::timestamptz > now() - interval '3 months';

Возникает вопрос: как его обновить? Для этого служит команда refresh, которая в простом случае принимает имя представления:

refresh materialized view mv_active_apps_3_months;

Когда команда отработает, прежние записи представления пропадут, а вместо них появятся новые. Материализацию вызывают по расписанию: пишут скрипт, который выполняет команду refresh. Время запуска подбирают так, чтобы не повлиять на рабочие процессы, например ночью или рано утром. В идеале отчеты формируются не на боевой базе, а ее копии (реплике).

Материализованные представления имеют ряд особенностей. Первая – для них можно создать индексы: btree, gin и остальные, что мы проходили в главе про индексирование. Смысл индексов в следующем: иногда клиентам нужны отдельные отчеты в разрезе какого-то критерия, например статуса заявки. Предположим, всего статусов семь. Делать семь представлений, которые отличаются только статусом, неэффективно. Проще сделать одно представление со полем status и добавить на него индекс btree. Потребители сами “нарежут” данные серией запросов:

copy (select * from mv_... where status = 'active')
to program 'gzip > report_active.csv.gzip' with (format CSV);

Вторая особенность – материализация может протекать в фоне. При этом она длится дольше, но все это время представление доступно для чтения. В некоторых случаях это важно, особенно если потребителей отчета много. Для обновления в фоне в команде refresh указывают слово CONCURRENTLY:

refresh materialized view concurrently mv_active_apps_3_months;

Параллельная материализация возможна лишь в случае, если у представления есть хотя бы один уникальный индекс. Напомним, он гарантирует, что запись с таким значением встречается в таблице однажды. По уникальному индексу Postgres отслеживает, какая часть представления уже обновилась, а какую предстоит обработать.

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

create unique index if not exists idx_mv_active_apps_3_months_app_id
on mv_active_apps_3_months (app_id);

После создания индекса выражение REFRESH ... CONCURRENTLY не вызовет ошибки, а само представление не будет заблокировано.

Задачи по расписанию

При работе с отчетами особенно важна регулярность: верный отчет, доставленный с опозданием, уже никого не интересует.

Выше мы несколько раз упоминали, что отчеты формируются по расписанию. Для этого в системе работает служба, которая запускает в указанное время задачи. Как правило это скрипты, которые подключаются к базе и производят файлы.

В Unix-системах служба задач по расписанию называется crontab. Мы не будем ее рассматривать, потому что за много лет о ней написали сотни статей. Средства виртуализации вроде Kubernetes тоже имеют службу задач по расписанию, похожую на crontab. Аналогичный сервис предлагают облачные провайдеры: Amazon, Azure и другие.

Предлагаем читателю необычный ход: поручить задачи по расписанию базе данных! В Postgres для этого служит расширение pg_cron. Это стороннее решение, и его нет в поставке по умолчанию. Установить pg_cron можно одним из способов:

скомпилировать самому, для чего понадобится исходный код и различные утилиты для сборки кода на Си.
Установить из пакетного менеджера: apt, yam и других. Пакет называется postgresql-XX-cron, где XX — мажорная версия Postgres, например postgresql-16-cron.

Также pg_cron предустановлен у облачных провайдеров Amazon, Google Cloud, Neon и других. По умолчанию он отключен. Вам нужно включить его и перезапустить сервер Postgres. Когда расширение установлено, подключите его командой:

create extension pg_cron;

Поскольку pg_cron не входит в список доверенных расширений, команду должен выполнить владелец (owner) базы. Если не указано иное, pg_cron помещает свои сущности в схему cron.

Самая важная функция pg_cron называется schedule (запланировать). Она принимает уникальное имя задачи, расписание в формате crontab и SQL-выражение, которое будет выполняться. Предположим, мы бы хотели выгружать отчет на диск каждый рабочий день в 6:30 утра. Вот как запланировать задачу:

SELECT cron.schedule(
  'my-daily-report', '30 6 * * 1-5', $$

copy (

  select
      doc->>'application_id' as app_id,
      doc->>'status' as status,
      doc->>'credit_type' as credit_type,
      (doc->>'created_at')::date as created_at,
      doc #>> '{created_by,name}' as created_by,
      doc #>> '{organization,short_name}' as org_short_name
  from
      applications
  where
      doc->>'status' in ('active', 'pending')
      and (doc->>'created_at')::timestamptz > now() - interval '3 months'
  order by
      (doc->>'created_at')::timestamptz

) to program 'gzip > /path/to/report.csv.gzip' with (format csv, header on);

$$);

Расписание crontab состоит из нескольких величин, разделенных пробелом. Это минуты, часы, день месяца, месяц, день недели. Вот их подробное описание:

минуты: означает минуту текущего часа, число от 0 до 59;
часы: означает час текущих суток, число от 0 до 23;
день месяца: номер дня в текущем месяце, число от 1 до 31;
месяц: номер месяца текущего года, число от 1 до 12;
день недели: номер дня текущей недели, число от 0 до 7 (см. ниже).

Обратите внимание: минуты и часы считаются от нуля, а день месяца и месяц – от единицы. Сложнее всего с последней величиной: днем недели. В разных странах неделя начинается с понедельника или воскресенья. Чтобы сгладить эту неразбериху, понедельник определяется единицей, а воскресенье – нулем и семеркой одновременно.

Если на месте величины звездочка, она означает любое значение. Можно указать числа через запятую, что значит “любой из вариантов”. Дефис означает значения от и до указанных. Комбинация из звездочки, косой черты и числа читается как “каждый N-ый интервал”, например */3 – каждые три часа.

Синтаксис crontab не настолько гибок, чтобы покрыть все сценарии. Разные системы расширяют его служебными символами. В случае pg_cron вместо дня месяца (третий параметр) можно указать доллар – он означает последний день месяца. Порой это важно, потому что число дней в месяце отличается. Если явно задать день 31, задача не сработает в сентябре.

Чтобы лучше узнать crontab, посетите сайт crontab.guru. Он подробно описывает синтаксис, содержит готовые шаблоны, строит по ним текстовое описание.

Когда задача создана, она появится в таблице cron.job. Прочитав ее, мы получим список всех доступных задач. Другая таблица cron.job_run_details отслеживает историю их выполнения. Среди прочих полей она содержит признак успешности. После того как задача несколько раз сработала в фоне, запросите данные из cron.job_run_details. Результат будет примерно таким:

select * from cron.job_run_details order by start_time desc limit 5;

┌───────┬───────┬─────────┬──────────┬──────────┬───────────────────┬───────────┬──────────────────┬───────────────────────────────┬───────────────────────────────┐
│ jobid │ runid │ job_pid │ database │ username │      command      │  status   │  return_message  │          start_time           │           end_time            │
├───────┼───────┼─────────┼──────────┼──────────┼───────────────────┼───────────┼──────────────────┼───────────────────────────────┼───────────────────────────────┤
│    11 │  4328 │    2610 │ postgres │ marco    │ select pg_sleep(3)│ running   │ NULL             │ 2023-02-07 09:30:00.098164+01 │ NULL                          │
│    10 │  4327 │    2609 │ postgres │ marco    │ select process()  │ succeeded │ SELECT 1         │ 2023-02-07 09:29:00.015168+01 │ 2023-02-07 09:29:00.832308+01 │
│    10 │  4321 │    2603 │ postgres │ marco    │ select process()  │ succeeded │ SELECT 1         │ 2023-02-07 09:28:00.011965+01 │ 2023-02-07 09:28:01.420901+01 │
│    10 │  4320 │    2602 │ postgres │ marco    │ select process()  │ failed    │ server restarted │ 2023-02-07 09:27:00.011833+01 │ 2023-02-07 09:27:00.72121+01  │
│     9 │  4320 │    2602 │ postgres │ marco    │ select do_stuff() │ failed    │ job canceled     │ 2023-02-07 09:26:00.011833+01 │ 2023-02-07 09:26:00.22121+01  │
└───────┴───────┴─────────┴──────────┴──────────┴───────────────────┴───────────┴──────────────────┴───────────────────────────────┴───────────────────────────────┘

Чтобы получить историю конкретной задачи, отфильтруйте запрос по полю jobid – ее уникальному номеру. Получить номер по имени можно из таблицы cron.job.

Postgres никак не уведомит вас, если задача завершилась с ошибкой. Необходимо время от времени проверять, как идут дела. Напишите скрипт, который читает таблицу cron.job_run_details за последний день с условием where status = 'failed'. Если результат непустой, скрипт отправляет письмо администратору.

Чтобы снять задачу с выполнения, вызовите функцию cron.unschedule с ее кодом или именем:

cron.unschedule('my-daily-report')
cron.unschedule(123)

Расширение pg_cron гибко настраивается; дополнительные параметры и функции читатель найдет в документации на Github. В следующем параграфе мы отметим некоторые полезные приемы.

Другие применения pg_cron

Выше мы создали задачу my-daily-report. Обратите внимание, что ее выражение содержит запрос целиком. Это неудобно: если запрос изменится, придется исправлять задачу. Правильно сделать так, чтобы задача только запускала какую-то логику, не зная о ее содержимом.

Один из способов это сделать – перенести код в процедуру:

create or replace procedure dump_daily_report()
language sql as $$
    copy (select * from /* your report */)
    to program 'gzip > /path/to/report.csv.gzip'
    with (format csv, header on)
$$;

Тогда задача сводится к вызову процедуры и ничего больше:

call dump_daily_report();

Материализованное представление удобно обновлять при помощи pg_cron. Для этого заводят задачу с вызовом команды refresh. Тем самым вы гарантируете, что представление будет обновляться в фоне, и через указанный интервал мы получим новые данные.

Еще одно регулярное действие – очистка записей по какому-то принципу, например архивному статусу или сроку действия. Вот как выглядит подобная задача:

SELECT cron.schedule(
  'truncate-historical-records',
  '15 2 * * 1-5',
  $$
    delete from history_table where created_at < now() - interval '12 months';
  $$
);

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

Иногда pg_cron совмещают с расширением pg_prewarm для прогрева индексов. Одноименная функция принимает имя таблицы или индекса и насильно помещает их страницы в буферный кэш. Когда индекс прогрет (находится в оперативной памяти), доступ к нему значительно ускоряется. Задача ниже загружает конкретный индекс в память каждые три часа:

SELECT cron.schedule(
  'prewarm-idx-app-doc',
  '0 */3 * * 1-5',
  $$
    select pg_prewarm('idx_applications_doc_gin_jsonb_path_ops')
  $$
);

Узнать, какие индексы содержит таблица, можно при помощи команды \d+ <table> в psql. Статистику обращений к ним покажет представление pg_stat_user_indexes. Возможно, прогрев наиболее востребованных индексов улучшит производительность базы. В общем случае прогрев индексов не является универсальным решением: это локальное средство. К нему прибегают в особых случаях, которые мы не будем рассматривать на страницах книги.

Глава об отчетности подошла к концу. Мы научились писать отчеты и записывать их на диск. Мы узнали, как упростить код при помощи функций и какие у них характеристики. С помощью pg_cron мы сделали базу автономной: теперь она не зависит от сторонней службы crontab.

В следующей главе мы узнаем, как подружить Postgres с другой популярной технологией: языком программирования Python.

← Is AI Profitable Yet?

Главы

Содержание

Проблема вложенности

Простые отчеты

Функция jsonb_table

Параметры в JSON Path

Отчеты с группировкой

Рекурсивные документы и запросы

Знакомство с функциями

Схемы и префиксы

Характеристики функций

Примеры функций

Рекомендации к функциям

Временные функции

Тестирование функций

Выгрузка отчетов

Отчеты и представления

Задачи по расписанию

Другие применения pg_cron

Функция `jsonb_table`