Postgresql – Limited utility in RECURSIVE VIEWS

ctepostgresqlpostgresql-9.4recursiveview

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?

Best Answer

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.

Related Question