3

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?

3

A recursive view is just syntactic sugar for a recursive CTE.

create or replace recursive view foo (name, groupid, parentid) as 
  select name, groupid,parentid from rl.groups where groupid=2
  union
  SELECT groups.name, groups.groupid, groups.parentid 
    FROM rl.groups AS groups, foo                       
    WHERE groups.groupid = foo.parentid ;

is the same as your CTE.

If you want to parameterize a CTE, wrap it in a function.

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.