In the docs for recursive queries with
WITH, you'll find this
Recursive queries are typically used to deal with hierarchical or tree-structured data.
This seems to be true, but how does this function with
RECURSIVE VIEWs? In a recursive view you can't define the edge case from the caller of the view. There is also 0-opportunity for the planner to push that edge case down..
For instance, take this query
WITH RECURSIVE t(name, groupid, parentid) AS( SELECT name,groupid,parentid FROM rl.groups WHERE groupid = 2 -- this defines the edge case UNION SELECT groups.name, groups.groupid, groups.parentid FROM rl.groups AS groups, t WHERE groups.groupid = t.parentid ) SELECT * FROM t;
This will show you group 2, and all parents of that group and their parents. However, how do I make that into a useful
RECURSIVE VIEW? So far as I can tell, you can't. And that eliminates the hierarchical and tree-structured data use-case. What are they good for then? Just building recursive mathematical sequences?