The following example shows a method for preventing infinite processing cyclic data.
CREATE RECURSIVE VIEW reachable_from (destination, cost, depth) AS ( SELECT root.destination, root.cost, 0 AS depth FROM flights AS root WHERE root.source = 'Paris' UNION ALL SELECT out.destination, in.cost + out.cost, in.depth + 1 AS depth FROM reachable_from AS in, flights AS out WHERE in.destination = out.source AND in.depth <= 20);
This recursive view is written to be queried by the following SELECT request.
SELECT * FROM reachable_from;
In this example, the variable depth is used as a counter, initialized to 0 within the seed query for the recursive view definition and incremented by 1 within the recursive query for the definition.
The AND condition of the WHERE clause tests to make sure the counter never exceeds a value of 20. Because the depth counter was initialized to 0, this condition limits the recursion to 21 cycles.