# 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:

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 |
+-------------+------+------+------+------+------+------+


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,
...