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 working table in an
EXISTS subquery in the
JOIN 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 only meant to demonstrate my problem in code:
WITH RECURSIVE t(n) AS (
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 is the problem and specifically what gives an error.
Is there a way to determine existence in the working table?