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