Производственный календарь в 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
SELECT string_agg(res,'' ORDER BY ID)::xml AS res
FROM tmp_tmpДля проверки при помощи xmltable () распарсим заголовочную строку вида
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 AND Y.for_country='ru' )
SELECT * FROM CheckYearВ дальнейшем я буду просто использовать имена общих табличных выражений, которые уже показал, не повторяя их код.
Теперь распарсим таблицу праздников. Она содержит только внутренний числовой идентификатор праздника и его полное наименование в строках вида
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;Затем распарсим основную часть, содержащую строки вида
WITH [...]
SpecialDays AS (
SELECT ('2024-'
||left(D.d,2)||'-'
||right(D.d,2))::date AS special_date,
CASE WHEN D.t=1 THEN 'Holyday'
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='Holyday' 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$
<>
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 AND Y.for_country=calendar_lang ),
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 'Holyday'
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='Holyday' 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((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);Спасибо за внимание. С наступающим Новым Годом вас!
