postgresql 将递归函数转换为视图

myss37ts 于 2023-06-22 发布在 PostgreSQL

关注(0)|答案(2)|浏览(124)

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

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

要按顺序获取从树中的任何节点到根节点的路径，我使用以下函数：

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)。问题是如何将这个函数转换成视图，以便调用select * from tree where id <= 15。此外，视图是否会以与函数相同的速度执行（即while * 执行查询时会考虑where子句吗）？

postgresql

来源：https://stackoverflow.com/questions/13391457/convert-recursive-function-to-view

2条答案

按热度按时间

wr98u20j1#

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

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;

它是从所有节点到根的路径视图。
然后使用类似于：

SELECT * 
FROM v_tree
WHERE start = 15;

以获得想要的路径。
它对我来说适用于您的示例数据，但我还没有测试它的性能。
更新查询，仅调用_tree一次：

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;

赞(0）回复(0）举报 2023-06-22

waxmsbnn2#

功能更简单

首先，你可以简化你的函数。这个更简单的SQL函数做同样的事情：

CREATE OR REPLACE FUNCTION f_tree(_rev int)
 RETURNS TABLE(id int, parent_id int, depth int)
 LANGUAGE sql 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$;

电话：

select * from f_tree(15);

你可以使用PL/pgSQL，这可能对PostgreSQL 9.2之前的版本中的查询计划有一点点好处。但是您通过不必要地使用动态SQL而使唯一的理论上的好处无效。这完全说不通。简化为简单的SQL。
使用UNION ALL而不是UNION，更便宜，因为设计上不能被复制。

SQL即可

显然，你可以用普通的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;

也是一样。

查看

现在，VIEW是一个微不足道的问题：

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;

结果对我来说没有太大意义，但这个问题并没有定义任何更明智的东西...

赞(0）回复(0）举报 2023-06-22

我来回答

postgresql 将递归函数转换为视图

2条答案

功能更简单

SQL即可

查看

相关问题

热门标签

最新问答