Postgresql – WITH RECURSIVE with union among the recursive term

ctepostgresqlrecursive

Is it possible to use WITH RECURSIVE queries in PostgreSQL where the recursive term is a union of different tables? My usecase is to extract some tuples from a table and some logical rules:

p_1(x_1,x_2,x_3,x_4,x_5,x_6,x_7) \wedge x_6\neq\texttt{NULL} \wedge x_2 \neq\texttt{NULL}\wedge  x_5 \neq\texttt{NULL} \Rightarrow p_2(x_2,x_6)

And I would like to exploit recursive queries having the following syntax:

WITH RECURSIVE cte_name(
    CTE_query_definition -- non-recursive term
    UNION
    CTE_query definion  -- recursive term
) SELECT * FROM cte_name;

where p1 and p3 can be tuples from different possible types. This means that my non-recursive term will be:

SELECT * from TUPLES

While the recursive term generates new tuples from our data but does not have to generate tuples that already exist (CTE with UNION without ALL).

SELECT 
       'ex' AS type_event,
       t1.arg2 AS arg1,
       t1.arg6 AS arg2,
       null AS arg3,
       null AS arg4,
       null AS arg5,
       null AS arg6
    FROM cte_name t1
    WHERE t1.type_event = 'Transfering'
      AND t1.arg2 = t2.arg1
      AND t1.arg6 = t2.arg2

UNION ALL 


       'ex' AS type_event,
       t1.arg2 AS arg1,
       t1.arg6 AS arg2,
       null AS arg3,
       null AS arg4,
       null AS arg5,
       null AS arg6
    FROM cte_name t1
    WHERE t1.type_event = 'Replacement'
      AND t1.arg2 = t2.arg1
      AND t1.arg6 = t2.arg2
UNION ALL

       'ex' AS type_event,
       t1.arg2 AS arg1,
       t1.arg1 AS arg2,
       null AS arg3,
       null AS arg4,
       null AS arg5,
       null AS arg6
    FROM cte_name t1
    WHERE t1.type_event = 'ex'

By the way, WITH RECURSIVE does not allow to express recursive statements where the recursive element is a union of different selects. Is there an efficient solution that does not involve to programmatically implement this in C++/Java/Python, so that all the data "stays" in the database and I do not need to serialize and desirialize the data multiple times?

Example of input data for TUPLES:

+-------------+------+------+------+------+------+------+
| type_event  | arg1 | arg2 | arg3 | arg4 | arg5 | arg6 |
+-------------+------+------+------+------+------+------+
| ex          | A    | B    | null | null | null | null |
+-------------+------+------+------+------+------+------+
| Transfering | A    | B    | C    | D    | E    | F    |
+-------------+------+------+------+------+------+------+
| Replacement | G    | H    | I    | J    | K    | L    |
+-------------+------+------+------+------+------+------+

Expected output after the third and final iteration:

+-------------+------+------+------+------+------+------+
| type_event  | arg1 | arg2 | arg3 | arg4 | arg5 | arg6 |
+-------------+------+------+------+------+------+------+
| ex          | A    | B    | null | null | null | null |
+-------------+------+------+------+------+------+------+
| Transfering | A    | B    | C    | D    | E    | F    |
+-------------+------+------+------+------+------+------+
| Replacement | G    | H    | I    | J    | K    | L    |
+-------------+------+------+------+------+------+------+
| ex          | B    | F    | null | null | null | null |
+-------------+------+------+------+------+------+------+
| ex          | H    | L    | null | null | null | null |
+-------------+------+------+------+------+------+------+
| ex          | B    | A    | null | null | null | null |
+-------------+------+------+------+------+------+------+
| ex          | F    | B    | null | null | null | null |
+-------------+------+------+------+------+------+------+
| ex          | L    | H    | null | null | null | null |
+-------------+------+------+------+------+------+------+

Best Answer

The three parts of the desired recursive term select rows from the same CTE, so you can transform them into a single SELECT:

SELECT ...
FROM cte_name t1
WHERE (t1.type_event = 'Transfering' AND t1.arg2 = t2.arg1 AND t1.arg6 = t2.arg2)
   OR (t1.type_event = 'Replacement' AND t1.arg2 = t2.arg1 AND t1.arg6 = t2.arg2)
   OR (t1.type_event = 'ex')

Reordering the columns can be done with a CASE expression:

SELECT 'ex' AS type_event,   -- same for all three
       t1.arg2 AS arg1,      -- same for all three
       CASE t1.type_event
         WHEN 'Transfering' THEN t1.arg6
         WHEN 'Replacement' THEN t1.arg6
         WHEN 'ex'          THEN t1.arg1
       END AS arg2,
       ...