Convert recursive function to view

Simpler function

First of all, you can simplify your function quite a bit. This simpler SQL function does the same:

CREATE OR REPLACE FUNCTION f_tree(_rev int) RETURNS TABLE(id int, parent_id int, depth int) AS$func$   WITH RECURSIVE tree_list AS (      SELECT t.id, t.parent_id, 1 -- AS depth      FROM   tree t      WHERE  t.id = $1      UNION ALL  -- no point using UNION      SELECT t.id, t.parent_id, r.depth + 1      FROM   tree_list r      JOIN   tree t ON t.id = r.parent_id      )   SELECT t.id, t.parent_id, t.depth   FROM   tree_list t   ORDER  BY t.id;$func$ LANGUAGE sql;

Call:

select * from f_tree(15);

• You could use plpgsql, might be slightly beneficial for cashing the query plan in versions before PostgreSQL 9.2. But you voided the only theoretical benefit by using dynamic SQL without need. This makes no sense at all. Simplify to plain SQL.

• Use UNION ALL instead of UNION, cheaper since there cannot be dupes by design.

Just SQL

Obviously, you can replace this with plain SQL:

WITH RECURSIVE tree_list AS (   SELECT t.id, t.parent_id, 1 AS depth   FROM   tree t   WHERE  t.id = 15  -- enter parameter here   UNION ALL   SELECT t.id, t.parent_id, r.depth + 1   FROM   tree_list r   JOIN   tree t ON t.id = r.parent_id   )SELECT t.id, t.parent_id, t.depthFROM   tree_list tORDER  BY t.id;

Does the same.

VIEW

Now, the VIEW is a trivial matter:

CREATE OR REPLACE VIEW v_tree15 ASWITH RECURSIVE tree_list AS (   SELECT t.id, t.parent_id, 1 AS depth   FROM   tree t   WHERE  t.id <= 15   -- only detail to change   UNION ALL   SELECT t.id, t.parent_id, r.depth + 1   FROM   tree_list r   JOIN   tree t ON t.id = r.parent_id   )SELECT t.id, t.parent_id, t.depthFROM   tree_list tORDER  BY t.id;

The result does not make a lot of sense to me, but the question does not define anything more sensible ..

You can use something like this:

CREATE OR REPLACE VIEW v_tree ASSELECT tr.id as start,       (_tree(tr.id)).id,        (_tree(tr.id)).parent_id,        (_tree(tr.id)).depthFROM tree tr;

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

Then use something like:

SELECT * FROM v_treeWHERE 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 ASSELECT t_tree.start,       (t_tree.node).id,        (t_tree.node).parent_id,        (t_tree.node).depthFROM (SELECT tr.id as start,            _tree(tr.id) as node      FROM tree tr) t_tree;