30.12.2023 17:48
ptr128 20 7800 Источник

Новый Год уже совсем на носу, а значит нужен свежий производственный календарь в базе данных 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)

Комментарии (20)


  1. BugM
    30.12.2023 18:16
    #26325222
    +5

    Извините

    Там 26 строк. Перепечатать руками в табличку быстрее.


    1. ptr128 Автор
      30.12.2023 18:16
      #26325240
      +2

      Для пяти стран за десять лет и не допустив ни одной ошибки? Ну-ну )

      И Вы действительно думаете, что я потратил на эту функцию больше 5 минут?

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


      1. BugM
        30.12.2023 18:16
        #26325258
        +7

        5 стран это 100 строчек. Раз в год. Вбивается силами любой девочки.

        Экран кода не пишется за 5 минут. Вероятно даже за час не пишется. Опять же тесты, обработка ошибок, развернуть куда-то, подумать как запускать и все такое. День работы точно. За 10 лет не окупится.


        1. ptr128 Автор
          30.12.2023 18:16
          #26325272
          -2

          Вбивается силами любой девочки.

          Особенно когда такая таблица переносимая и заполняется только через GIT.

          День работы точно.

          Мои соболезнования. Я столь простые функции из одного(!) SQL запроса действительно пишу за 5 минут.


    1. dyadyaSerezha
      30.12.2023 18:16
      #26325360
      +3

      Дело не в этом, а в примере, как читать из файла и парсить, и прочее.


  1. rezdm
    30.12.2023 18:16
    #26325464
    +1

    Вот этот календарь, что по ссылке -- это первый шаг.

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

    Грамотная поддержка календаря -- нетривиальная, но вполне решаемая задача задача.


    1. PrinceKorwin
      30.12.2023 18:16
      #26326194

      А ещё бывает так, что правительство выделяет N дней в году на праздники, но какие из них M (M < N) дней будет выходными для сотрудников решает каждая компания сама. N и M регулируется государство на каждый год отдельно.


  1. Akina
    30.12.2023 18:16
    #26325530
    +1

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

    Работает исключительно в случаях, когда он нужен в справочных целях. Впрочем, подбор подтверждающих документов можно спихнуть на ОТиЗ и юристов. А сам календарь после первого же парсинга сохранить в служебной таблице - ну право слово, глупо же каждый раз лезть к чужому дяде и потом парсить его ответ.


    1. ptr128 Автор
      30.12.2023 18:16
      #26325552
      +1

      Работает исключительно в случаях, когда он нужен в справочных целях.

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

      А сам календарь после первого же парсинга сохранить в служебной таблице

      А я написал иначе?

      "Ну а уж из этой функции можно его сохранить в табличку и спокойно использовать до следующего Нового Года."

      Ну и Вы, хоть и не первый, но тоже пропустили цель статьи: "Цель статьи - показать возможности COPY ... FROM PROGRAM и простейшие приемы парсинга XML в PostgreSQL."


    1. Xokare228
      30.12.2023 18:16
      #26325892

      Вообще нерабочие дни это константа, которая записана в ТК РФ. Но загвоздка в том, что раз в год принимается Постановление Правительства, которое некоторые праздничные дни (обычно, выпадающие на выходные или "висящие" рядом с ними) переносят на другие дни. Так что простой ответ на вопрос а можно ли это автоматизировать - нет. Проще захадркодить нерабочие дни и раз в год отправлять человека сохранять в бд несколько переносов


      1. ptr128 Автор
        30.12.2023 18:16
        #26326138

        Именно ради этих переносов и загружается производственный календарь. Что и позволяет процесс автоматизировать.

        Вот раз в год один человек и фиксирует изменения производственного календаря. После чего миллионы могут этим уже автоматизировано воспользоваться.


  1. Sdolgov
    30.12.2023 18:16
    #26326192

    В принципе неплохой пример как вытянуть данные и распарсить xml. Мне, как вообще не работающему с постгрессом было интересно. Правда резануло глаз - holYday это там в таком виде приходит? Еще попутно вопрос - надо по работе переписать кучу серверного кода с оракла на постгрес. Попробовал - это ужас. Никакой отладки или пошагового выполнения. Ошибку пишет только после запуска если дошло до неправильной строки - да и тут какую-то странную ссылку дает - типа ошибка в строке 3452 а такой строки нет. Собственно вопрос - есть какая-то среда разработки для постгресса с нормальной отладкой чтобы функцию написать не из одного селекта а со сложной логикой на несколько сотен строк? Ну и по календарю - почти в каждой области РФ он свой со своими праздниками. Всероссийские конечно тоже входят, но несколько региональных добавляется.


    1. ptr128 Автор
      30.12.2023 18:16
      #26326286
      +1

      резануло глаз - holYday это там в таком виде приходит?

      Нет. Это я в одном месте описку допустил.

      Ошибку пишет только после запуска если дошло до неправильной строки

      А в Oracle что-ли кто-то телепатически ошибки находит до запуска? Типа "вангую Вам дублирование ключей с такими-то значениями в строке такой-то"? Что-то не верится.

      есть какая-то среда разработки для постгресса с нормальной отладкой

      А тут надо понимать, что есть SQL диалект PostgreSQL, на котором, кстати можно писать и фукнции (LANGUAGE SQL), а есть еще целый ворох процедурных языков (plperlu, plpythonu, plrust, plr и т.п.). И если для SQL мне вполне хватает средств DBEaver, то уже как только начинаются DO блоки даже на plpgsql - у него конкретно сносит крышу. Собственно говоря, отладчик я знаю только для plpgsql в составе pgAdmin. Но сам пользуюсь им редко, так код процедур и так всегда в BEGIN ... EXCEPTION ... END, и в журнал пишется вообще все, что только можно получить через GET STACKED DIAGNOSTICS.


      1. Sdolgov
        30.12.2023 18:16
        #26326730

        А в Oracle что-ли кто-то телепатически ошибки находит до запуска? Типа "вангую Вам дублирование ключей с такими-то значениями в строке такой-то"? Что-то не верится.

        Это уже ошибка в данных, само собой такое предсказать невозможно. Я про другое. Попробовал перевести одну функцию с большим запросом внутри с оракла на пострес. Сконвертировал утилитой, само собой ничего сразу не скомпилировалось, в итоге почти все пришлось подправить руками. Суть в том, что в запросе были поля из таблицы, к которым не добавлен алиас таблицы. В самой функции тоже были поля с такими же наименованиями в блоке declare. Все нормально скомпилировалось. Но при запуске начало выдавать что поле дублируется, нужно добавить алиас. Номер строки при этом какой-то странный - ни внутри функции ни внутри селекта такой нет. Приходилось каждый раз просматривать, пытаться догадаться где же это произошло, менять, компилировать, заново запускать и смотреть пропало или нет. Оракл сругался бы на этапе компиляции указав на конкретную строку. В оракле, допустим, вы поменяли состав входных параметров функции которая вызывается в других функциях/процедурах/триггерах. Сразу после перекомпиляции все эти объекты станут инвалидными и будет видно в какой строке код теперь инвалидный. В постгрессе, как я понял, пока не вызовешь функцию и не поймешь что она не может выполниться впринципе.


        1. RekGRpth
          30.12.2023 18:16
          #26326796

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

          для этого тоже есть расширение plpgsql_check


        1. ptr128 Автор
          30.12.2023 18:16
          #26326822
          +1

          Все нормально скомпилировалось.

          Оракл сругался бы на этапе компиляции указав на конкретную строку.

          С точки зрения PostgreSQL, процедуры и функции на любом языке, кроме SQL (LANGUAGE SQL) - это лишь строки, которые следует передать при их вызове соответствующему интерпретатору. Остальное - уже его проблемы.

          Подозревая, что речь идет все же о plpgsql (PL/pgSQL), остановимся на его поведении. Он транслирует процедуру или функцию во внутреннее бинарное представление при первом ее запуске. При этом транслируются только конструкции языка PL/pgSQL, но не SQL предложения используемые в нем. SQL предложения транслируются SPI_prepare только при первом выполнении и результат кешируется для дальнейшего использования. Поэтому полная "компиляция" всего текста процедуры может вообще не произойти никогда, если какие-то SQL предложения еще ни разу не вызывались по логике ветвлений.

          Отсюда, если Вы хотите произвести полную проверку синтаксиса процедуры или функции на PL/pgSQL, следует воспользоваться соответствующим расширением plpgsql_check. Сам интерпретатор PL/pgSQL для этой цели мало пригоден.

          В оракле, допустим, вы поменяли состав входных параметров функции которая вызывается в других функциях/процедурах/триггерах.

          А тут поведение PostgreSQL тоже радикально отличается. Он вообще не позволяет изменять состав входных параметров функции. Если у Вас была функция you_function(integer) и Вы создадите (CREATE OR REPLACE) функцию you_function(smallint), то это будет новая функция с тем же именем, но применяемая для типа параметра smallint. А старая останется для типа параметра integer.

          Наглядно:

          CREATE OR REPLACE FUNCTION you_function(x integer)
RETURNS integer AS $function$
<<func>>
BEGIN
RETURN x*2;
END; $function$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION you_function(x smallint)
RETURNS integer AS $function$
<<func>>
BEGIN
RETURN x/2;
END; $function$ LANGUAGE plpgsql;

SELECT you_function(10) AS I, you_function(10::smallint) AS S;

i,s
20,5

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

          Идеология поддержки неограниченного множества процедурных языков приводит именно к этому. Для самой СУБД интерпретаторы процедурных языков - черный ящик. И каждый из них может поступать по своему. Например, PL/pgSQL - интерпретатор, а вот PL/Rust - компилирует процедуры и функции в машинный код при первом запуске.


          1. Sdolgov
            30.12.2023 18:16
            #26327174
            +1

            Большое спасибо за ответы и подсказки, я действительно имел ввиду pl/pgsql. Просто в оракле особого выбора нет, думал здесь так же принято - в основном на нем писать, остальное только прям если прижмет. Пока пробовал работать только в dbeaver. Буду пробовать/экспериментировать.


    1. RekGRpth
      30.12.2023 18:16
      #26326302

      pldebugger и pgadmin вроде как раз подходят для отладки


      1. ptr128 Автор
        30.12.2023 18:16
        #26326582

        К сожалению, только для plpgsql. С теми же plperlu, plr, plpythonu - кувыркайся как знаешь (


  1. bzq
    30.12.2023 18:16
    #26327418
    +3

    Человек написал отличный туториал по работе с xml на постгресе, а ему в ответ — не так сидишь, почерк кривой... Тот самый случай, когда за читателей стыдно.