Производственный календарь в 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/2024/calendar.xml'   WITH (FORMAT text); SELECT res FROM tmp_tmp ORDER BY ID;

<?xml version="1.0" encoding="UTF-8"?> <calendar year="2024" lang="ru" date="2023.09.30">     <holidays>         <holiday id="1" title="Новогодние каникулы"/>         <holiday id="2" title="Рождество Христово"/>         <holiday id="3" title="День защитника Отечества"/>         <holiday id="4" title="Международный женский день"/>         <holiday id="5" title="Праздник Весны и Труда"/>         <holiday id="6" title="День Победы"/>         <holiday id="7" title="День России"/>         <holiday id="8" title="День народного единства"/>     </holidays>     <days>         <day d="01.01" t="1" h="1"/>         <day d="01.02" t="1" h="1"/>         <day d="01.03" t="1" h="1"/>         <day d="01.04" t="1" h="1"/>         <day d="01.05" t="1" h="1"/>         <day d="01.06" t="1" h="1"/>         <day d="01.07" t="1" h="2"/>         <day d="01.08" t="1" h="1"/>         <day d="02.22" t="2"/>         <day d="02.23" t="1" h="3"/>         <day d="03.07" t="2"/>         <day d="03.08" t="1" h="4"/>         <day d="04.27" t="3" />         <day d="04.29" t="1" f="04.27"/>         <day d="04.30" t="1" f="11.02"/>         <day d="05.01" t="1" h="5"/>         <day d="05.08" t="2"/>         <day d="05.09" t="1" h="6"/>         <day d="05.10" t="1" f="01.06"/>         <day d="06.11" t="2"/>         <day d="06.12" t="1" h="7"/>         <day d="11.02" t="2"/>         <day d="11.04" t="1" h="8"/>         <day d="12.28" t="3"/>         <day d="12.30" t="1" f="12.28"/>         <day d="12.31" t="1" f="01.07"/>     </days> </calendar>

Теперь нам нужно из полученных текстовых строк получить XML

SELECT string_agg(res,'' ORDER BY ID)::xml AS res FROM tmp_tmp

Для проверки при помощи xmltable() распарсим заголовочную строку вида <calendar year=»2024″ lang=»ru» date=»2023.09.30″>, содержащую год, страну, для которой этот календарь, и дату его последнего изменения.

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 '@lang',       create_date date PATH '@date') Y   WHERE Y.for_year=2024::smallint ) SELECT * FROM CheckYear

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

Теперь распарсим таблицу праздников. Она содержит только внутренний числовой идентификатор праздника и его полное наименование в строках вида <holiday id=»6″ title=»День Победы»/>

WITH [...] 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 ) SELECT * FROM Holidays;

Затем распарсим основную часть, содержащую строки вида <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 ('2024-'       ||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,     ('2024-'       ||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(('2024-01-01')::timestamp,          ('2024-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 '@lang',       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)