Convert recursive function to view

common-table-expression, postgresql, recursion, sql, tree

Solution

You can use something like this:

CREATE OR REPLACE VIEW v_tree AS
SELECT tr.id as start,
       (_tree(tr.id)).id, 
       (_tree(tr.id)).parent_id, 
       (_tree(tr.id)).depth
FROM tree tr;

It is a view of paths from all nodes the to root.

Then use something like:

SELECT * 
FROM v_tree
WHERE start = 15;

To get desired path.

It works for me on your example data, but i haven't tested it for performance.

Updated query to call `_tree` only once :

CREATE OR REPLACE VIEW v_tree AS
SELECT t_tree.start,
       (t_tree.node).id, 
       (t_tree.node).parent_id, 
       (t_tree.node).depth
FROM (SELECT tr.id as start,
            _tree(tr.id) as node
      FROM tree tr) t_tree;

Problem

I'm trying to convert a function in Postgres into a `select` query which I intend to use as a view. The reason is that I'd like to access it from a client via a `select` query with a `where` clause instead of using a parameter as with the function. The table represents a tree (and adjacency list) and is defined as follow: ``` CREATE TABLE tree ( id serial primary key, parent_id int references tree(id) ); INSERT INTO tree (id, parent_id) VALUES (1,null) , (2,1), (3,2), (4,3), (5,3) , (6,5), (7,6), (8,4), (9,8) , (10,9), (11,9), (12,7) , (13,12), (14,12), (15,11) , (16,15), (17,16), (18,14) , (19,13), (20,19), (21,20); SELECT setval ('tree_id_seq', 21); -- reset sequence -- This produces a tree like: -- +-- <10> -- / -- +-- <4> -- <8> --- <9> -+- <11> --- <15> --- <16> --- <17> -- / -- <1> --- <2> --- <3> -+ -- \ -- +-- <5> --- <6> --- <7> --- <12> -+- <14> --- <18> -- \ -- \ -- \ -- \ -- +-- <13> --- <19> --- <20> --- <21> -- ``` To get a path from any node in the tree to the root in order, I use this function: ``` create or replace function _tree(rev int) returns table(id int, parent_id int, depth int) as $$ declare sql text; begin sql = 'WITH RECURSIVE tree_list(id, parent_id, depth) AS ( SELECT id, parent_id, 1 FROM tree WHERE id = ' || rev || 'UNION SELECT p.id, p.parent_id, r.depth + 1 FROM tree p, tree_list r WHERE p.id = r.parent_id ) SELECT id, parent_id, depth FROM tree_list order by id;'; return query execute sql; end; $$ language plpgsql; ``` A query would look like `select * from _tree(15)`. The question is how would I go about converting this function into a view, so I could call `select * from tree where id <= 15`. Also, would a view be executed at the same speed as the function (i.e. would the where clause be considered while executing the query)?

Original source