m i r a l l

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

Официальная документация на этот счёт даёт более чем исчерпывающие пояснения, поэтому подробно расписывать смысла нет. Но пример оттуда приведу, ибо выглядит весьма странно и с непривычки выносит мозг.

WITH moved_rows AS (
    DELETE FROM products
    WHERE
        "date" >= '2010-10-01' AND
        "date" < '2010-11-01'
    RETURNING *
)
INSERT INTO products_log
SELECT * FROM moved_rows;

Работает для insert, update и delete в блоке with и то же самое плюс select во внешнем блоке. Ограничение, как обычно, есть: изменять одну и ту же строку технически возможно, но, по факту, приводит к непредсказуемым последствиям.

Оказывается, в SQL есть оператор OVERLAPS. И это прекрасно.

Правда, это всего лишь частично прекрасно. Ибо в Oracle эта возможность является недокументированной. Тем не менее, она есть.

SQL> select 1 from dual
SQL>  where (sysdate - 1, sysdate + 1)
SQL>        overlaps
SQL>        (sysdate - 2, sysdate + 2);

         1
----------
         1

Зато в PostgreSQL оно существует вполне себе официально. И это ещё прекраснее.

# select ('-infinity', current_timestamp + interval '10 days')
         overlaps
         (current_timestamp - interval '1 days', current_timestamp + interval '1 days');
 overlaps
----------
 t
(1 row)

Кстати, PostgreSQL ещё знает, что такое бесконечность.

Дано.
Лес (т.е. набор деревьев) простого алгоритма представлен в виде двух таблиц.

       Table "algorithm_node"
    Column    |       Type        | Modifiers
--------------+-------------------+-----------
 node_id      | ext_id_udt        | not null
 algorithm_id | ext_id_udt        | not null
 node_type    | character varying | not null
Indexes:
    "algorithm_node_pk" PRIMARY KEY, btree (node_id, algorithm_id)

    Table "algorithm_node_link"
       Column        |    Type    | Modifiers
---------------------+------------+-----------
 algorithm_id        | ext_id_udt | not null
 source_node_id      | ext_id_udt | not null
 destination_node_id | ext_id_udt | not null
Indexes:
    "algorithm_node_link_pk" PRIMARY KEY, btree (algorithm_id, source_node_id, destination_node_id)
Foreign-key constraints:
    "alg_node_link_dst__alg_node_fk" FOREIGN KEY (destination_node_id, algorithm_id)
          REFERENCES algorithm_node(node_id, algorithm_id)
    "alg_node_link_src__alg_node_fk" FOREIGN KEY (source_node_id, algorithm_id)
          REFERENCES algorithm_node(node_id, algorithm_id)

По алгоритму задаётся некая функциональность. При этом любые ноды алгоритма могут быть в функциональности пропущены.

                   Table "program_node"
         Column         |           Type           | Modifiers
------------------------+--------------------------+-----------
 program_node_id        | id_udt                   | not null
 program_id             | id_udt                   | not null
 algorithm_node_id      | ext_id_udt               | not null
 algorithm_id           | ext_id_udt               | not null
 state                  | ref_id_udt               | not null
Indexes:
    "program_node_pk" PRIMARY KEY, btree (program_node_id)
Foreign-key constraints:
    "prg_node__alg_node_fk" FOREIGN KEY (algorithm_node_id, algorithm_id)
          REFERENCES algorithm_node(node_id, algorithm_id)

Задача состояла в том, чтобы вернуть корни функциональности. Получилось такое безобразие.

with recursive rec (node_id, parent_id, program_node_id, nx, program_id) as (
  select lb.node_id, lb.parent_id, lb.program_node_id, lb.n nx, lb.program_id program_id
    from link lb
    where lb.parent_id is null
  union
  select l.node_id, l.parent_id, l.program_node_id, rec.nx + l.n, l.program_id
    from link l join rec on (rec.node_id = l.parent_id) 
),
link as (
  select an.node_id node_id, aln.source_node_id parent_id, pnd.program_node_id
       , case when pnd.program_node_id is null then 0 else 1 end n, pnd.program_id
    from algorithm.algorithm_node an
    left join algorithm.algorithm_node_link aln on aln.destination_node_id = an.node_id
    left join task.program_node pnd on an.node_id = pnd.algorithm_node_id
                                and pnd.program_id = 'ALG1' and pnd.state = 0
    where an.algorithm_id = rt_prg.algorithm_id
)
select program_node_id from rec r
  where nx = 1
    and program_node_id is not null

В целом, ничего выдающегося. За исключением того, что я в равной степени плохо разбираюсь в рекурсивных запросах и в PostgreSQL. Список личных мелких открытий:
1) если хочется в одном запросе использовать обычный и рекурсивный WITH, то сначала пишется рекурсивный, а потом только обычный;
2) PostgreSQL поддерживает аналитические функции (здесь не используется, но как-то мимоходом выяснилось);
3) придумывать рекурсивные запросы - это не просто долго, это очень долго, по крайней мере, для мозга, не привыкшего мыслить рекурсивно.