将递归函数转换为视图 [英] Convert recursive function to view

问题描述

我正在尝试将Postgres中的函数转换为我打算用作视图的 select 查询。原因是我想通过 select 查询使用 where 子句从客户端访问它，而不是与函数一起使用参数。
该表表示一棵树（和邻接表），其定义如下：

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;

查询看起来像 select * from _tree（15）。问题是我将如何将该函数转换为视图，因此我可以从id< = 15 的树中调用 select *。另外，是否可以以与函数相同的速度执行视图（即，在执行查询时 时，where子句会被视为）？

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

推荐答案

您可以使用类似这样的东西：

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.

然后使用类似的东西：

SELECT * 
FROM v_tree
WHERE start = 15;

要获取所需的路径。

它在您的示例数据上对我有效，但我尚未对其性能进行测试。

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

更新后的查询可以调用 _tree 仅一次：

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;

这篇关于将递归函数转换为视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！