Чарльз Дарвин. Вступление
Уверенность чаще порождается невежеством, нежели знанием
Небольшой тренинг по XML в PostgreSQL на примере производственного календаря
Новый Год уже скоро, а значит нужен свежий производственный календарь в базе данных PostgreSQL. Но как совершенно обленившийся IT-шник, заводить его руками не хочется. Хочется, чтобы вызовом одной функции он сразу появился. Ну а уж из этой функции можно его сохранить в табличку и спокойно использовать до следующего Нового Года. А тогда опять просто вызвать функцию и с чистой совестью отрапортовать о выполненной работе. Моя цель - показать возможности COPY ... FROM PROGRAM и простейшие приемы парсинга XML в PostgreSQL.
Для начала пришлось поискать, где же его взять в наиболее удобном для обработки виде. Нашел!
Ставить какие-либо расширения на PostgreSQL не хотелось, поэтому ограничился прямым вызовом wget через COPY. Для этого сначала создаем временную таблицу:
DROP TABLE IF EXISTS tmp_tmp;
CREATE TEMP TABLE tmp_tmp (
ID serial PRIMARY KEY,
res text NOT NULL);
ID нам нужен исключительно для сохранения порядка строк, полученных от wget. Теперь мы вполне можем заполнить эту табличку и даже посмотреть на результат:
COPY tmp_tmp (res) FROM PROGRAM
'/usr/bin/wget -qO - https://xmlcalendar.ru/data/ru/2026/calendar.xml'
WITH (FORMAT text);
SELECT res FROM tmp_tmp ORDER BY ID;
Теперь нам нужно из полученных текстовых строк получить XML
SELECT string_agg(res,'' ORDER BY ID)::xml AS res
FROM tmp_tmp
Для проверки при помощи xmltable() распарсим заголовочную строку вида <calendar year="2026" lang="ru" date="2025.09.30" country="ru">, содержащую год, страну, для которой этот календарь, и дату его последнего изменения.
WITH CTE AS (
SELECT string_agg(res,'' ORDER BY ID)::xml AS res
FROM tmp_tmp ),
CheckYear AS (
SELECT Y.for_year, Y.for_country, Y.create_date
FROM CTE C
CROSS JOIN xmltable('//calendar' PASSING C.res COLUMNS
for_year smallint PATH '@year',
for_country varchar PATH '@Country',
create_date date PATH '@DaTe') Y
WHERE Y.for_year=2026::smallint )
SELECT * FROM CheckYear
В дальнейшем я буду просто использовать имена общих табличных выражений, которые уже показал, не повторяя их код.
Теперь распарсим таблицу праздников. Она содержит только внутренний числовой идентификатор праздника и его полное наименование в строках вида <holiday id="6" title="День Победы"/>
Затем распарсим основную часть, содержащую строки вида <day d="03.08" t="1" h="4"/> или <day d="04.29" t="1" f="04.27"/>. Тут требуются пояснения. Под тегом d скрывается дата в формате ММ.ДД. Тег t определяет тип записи: 1 - выходной день, 2 - рабочий и сокращенный (может быть использован для любого дня недели), 3 - рабочий день (суббота/воскресенье). Тег h является ссылкой на идентификатор праздника из предыдущего запроса. А тег f - дата с которой был перенесен выходной день тоже в формате ММ.ДД. При этом суббота и воскресенье считаются выходными, если нет тегов day с атрибутом t=2 и t=3 за этот день.
WITH [...]
SpecialDays AS (
SELECT ('2026-'
||left(D.d,2)||'-'
||right(D.d,2))::date AS special_date,
CASE WHEN D.t=1 THEN 'Holiday'
WHEN D.t=2 THEN 'Shortened'
WHEN D.t=3 THEN 'Working'
ELSE NULL END AS day_type,
('2026-'
||left(D.f,2)||'-'
||right(D.f,2))::date AS from_date,
H.title AS holiday_name
FROM CheckYear Y
CROSS JOIN CTE C
CROSS JOIN xmltable('//calendar/days/day' PASSING C.res COLUMNS
d varchar(5) PATH '@d',
t smallint PATH '@t',
h smallint PATH '@h',
f varchar(5) PATH '@f') D
LEFT JOIN Holidays H ON H.id=D.h )
SELECT * FROM SpecialDays;
Для наглядности я заменил числовые типы на краткое их строковое описание. Даты же из формата MM.ДД преобразовал в формат ISO ГГГГ-ММ-ДД, а затем уже в тип date.
Теперь осталось только сгенерировать календарь за год и для каждого дня указать количество рабочих часов для 40-часовой рабочей недели.
SELECT C.d::date AS pk_date,
CASE WHEN S.day_type='Shortened' THEN 7
WHEN S.day_type='Working' THEN 8
WHEN S.day_type='Holiday' THEN 0
WHEN extract(DOW FROM C.d) IN (0,6) THEN 0
ELSE 8 END::smallint AS working_hours,
S.from_date, S.holiday_name
FROM generate_series(('2026-01-01')::timestamp,
('2026-12-31')::timestamp,
'1 day'::interval) C(d)
LEFT JOIN SpecialDays S ON S.special_date=C.d;
Сокращенные предпраздничные дни получили по 7 часов. Рабочие дни в выходные - 8. Праздники - 0. Воскресенье и суббота (в PostgreSQL нулевой и шестой дни недели соответственно) - 0. Ну а остальные дни считаются рабочими по 8 часов.
После всего этого осталось только создать функцию
CREATE OR REPLACE FUNCTION get_working_calendar(
calendar_year smallint,
calendar_lang varchar(2)='ru')
RETURNS TABLE (
pk_date date,
working_house smallint,
from_date date,
holiday_name varchar
) AS $function$
<<func>>
DECLARE
sql_cmd varchar='COPY tmp_tmp (res) FROM PROGRAM $$'
||$$/usr/bin/wget -qO - https://xmlcalendar.ru/data/$$
||calendar_lang||$$/$$
||calendar_year::text
||$$/calendar.xml --no-check-certificate$$
||'$$ WITH (FORMAT text);';
BEGIN
DROP TABLE IF EXISTS tmp_tmp;
CREATE TEMP TABLE tmp_tmp (
ID serial PRIMARY KEY,
res text NOT NULL);
EXECUTE func.sql_cmd;
RETURN QUERY WITH CTE AS (
SELECT string_agg(res,'' ORDER BY ID)::xml AS res
FROM tmp_tmp ),
CheckYear AS (
SELECT Y.for_year, Y.for_country, Y.create_date
FROM CTE C
CROSS JOIN xmltable('//calendar' PASSING C.res COLUMNS
for_year smallint PATH '@year',
for_country varchar PATH '@country,
create_date date PATH '@date') Y
WHERE Y.for_year=calendar_year ),
Holidays AS (
SELECT H.id, H.title
FROM CheckYear Y
CROSS JOIN CTE C
CROSS JOIN xmltable('//calendar/holidays/holiday'
PASSING C.res COLUMNS
id smallint PATH '@id',
title varchar PATH '@title') H ),
SpecialDays AS (
SELECT (calendar_year::text||'-'
||left(D.d,2)||'-'
||right(D.d,2))::date AS special_date,
CASE WHEN D.t=1 THEN 'Holiday'
WHEN D.t=2 THEN 'Shortened'
WHEN D.t=3 THEN 'Working'
ELSE NULL END AS day_type,
(calendar_year::text||'-'
||left(D.f,2)||'-'
||right(D.f,2))::date AS from_date,
H.title AS holiday_name
FROM CheckYear Y
CROSS JOIN CTE C
CROSS JOIN xmltable('//calendar/days/day' PASSING C.res COLUMNS
d varchar(5) PATH '@d',
t smallint PATH '@t',
h smallint PATH '@h',
f varchar(5) PATH '@f') D
LEFT JOIN Holidays H ON H.id=D.h )
SELECT C.d::date AS pk_date,
CASE WHEN S.day_type='Shortened' THEN 7
WHEN S.day_type='Working' THEN 8
WHEN S.day_type='Holiday' THEN 0
WHEN extract(DOW FROM C.d) IN (0,6) THEN 0
ELSE 8 END::smallint AS working_hours,
S.from_date, S.holiday_name
FROM CheckYear Y
CROSS JOIN generate_series((calendar_year::text||'-01-01')::timestamp,
(calendar_year::text||'-12-31')::timestamp,
'1 day'::interval) C(d)
LEFT JOIN SpecialDays S ON S.special_date=C.d;
END; $function$ LANGUAGE plpgsql;
И убедиться, что производственный календарь успешно загружается и парсится:
SELECT * FROM get_working_calendar(2024::smallint);
P.S. Забыл указать, что доступны производственные календари с 2015 года для России (ru) Белоруссии (by), Украины (ua), Казахстана (kz) и Узбекистана (uz)
P.P.S. Простите, если что, но после совершенно убогих постов про SQL, захотелось написать пост имеющий хоть ничтожную практическую ценность.
Показать полностью
