Geek Answers Handbook

Convert a recursive function to view

I am trying to convert a function in Postgres to a select query, which I intend to use as a view. The reason is that I would like to access it from the client using a select query with a where clause instead of using a parameter, as with a function. A table is a tree (and an adjacency list) and is defined as follows:

 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 the 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;

The query will look like select * from _tree(15) . The question is, how do I go about converting this function to a view, so I could call select * from tree where id <= 15 . In addition, will the view be executed at the same speed as the function (i.e., will the where clause be considered when executing the request)?

+6
source share
2 answers

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;

This is a representation of paths from all nodes from root to.

Then use something like:

 SELECT * FROM v_tree WHERE start = 15;

To get the desired path.

It works for me on your sample data, but I have not 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;
+2
source

Simplified function

First of all, you can simplify your function. This simple 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 can use plpgsql, which can be a little useful to cash out a query plan in versions prior to PostgreSQL 9.2. But you ruled out the only theoretical benefit using dynamic SQL unnecessarily. That doesn't make any sense. Simplify plain SQL.

  • Use UNION ALL instead of UNION , cheaper since design cannot be deceived.

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;

Same.

VIEW

Now VIEW is a trivial question:

 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 much sense to me, but the question does not determine anything more reasonable.

+4
source

More articles:


All Articles