2

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

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,
       ...
  • Why I didn't come with this solution in the first place. Furthermore, if I have more complicated conditions, I may just use a Searched CASE instead of a Simple CASE. If I may have multiple join conditions, I may just adopt the maximum number of joins in the FROM caluse (in the most general case, I'm always using the same table cte_name but multiple more times). Thanks! – jackb Nov 8 '18 at 18:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.