mardi 27 janvier 2015

Determine existence in recursive


I have a query that needs to check for existence of each input row type in another table, but it is unnecessary and inefficient to check for existence of subsequent rows of the same row type if one is already found.


I tried to reference the recursive query in an EXISTS subquery to prevent the unnecessary checks, but this gave an error that the recursive query cannot be referenced in a subquery.


For a simplified example modified from the docs:



WITH RECURSIVE t(n) AS (
VALUES (1)
UNION ALL
SELECT n+1 FROM t WHERE n < 100 AND NOT EXISTS (SELECT 1 FROM t u WHERE u.n = t.n+1)
)
SELECT sum(n) FROM t;


This is of course logically ridiculous, but the NOT EXISTS in this case is what gives an error.


Is there an alternative?





Aucun commentaire:

Enregistrer un commentaire