Convert recursive function to view

Question

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)?

Answer 1

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.depth
FROM   tree_list t
ORDER  BY t.id;

Does the same.

VIEW

Now, the VIEW is a trivial matter:

CREATE OR REPLACE VIEW v_tree15 AS
WITH 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.depth
FROM   tree_list t
ORDER  BY t.id;

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