用于返回父活动和依赖活动的分层查询

Question

我必须创建一个以activity_id为参数的函数。

函数应返回活动直接前置任务和间接前置任务及其关系，以及与树层次结构中存在的所有子活动的关系。所有子活动都以分层方式与前置任务进行。

我的表结构如下：

前任

CREATE TABLE public.predecessor
(
predecessor_id integer NOT NULL ,
current_activity_id integer,
dependent_activity_id integer,
createdby integer,
created_at timestamp without time zone NOT NULL DEFAULT now()
)

活动

CREATE TABLE public.activities
(
activity_id integer NOT NULL,
parent_id integer,
name text,
type_id integer NOT NULL DEFAULT 11
)

我知道这是一个非常简短的表示形式，但我会感谢快速回答或制定查询的指南。

表中的示例数据如下

活动数据

activity_id|parent_id
   8000|    |            
   8100|8000|            
   8200|8000|            
   8120|8100|            
   8130|8100|            
   8140|8100|            
   8220|8200|            
   8230|8200|            
   8240|8200|            
   8241|8240|            
   8242|8240|            
   8243|8240|            
   7000|    |            
   7100|7000|            
   7200|7000|            
   7300|7000|            
   7210|7200|            
   7220|7200|            
   6100|6000|            
   6200|6000|            
   6210|6200|            
   6220|6200|            
   6230|6200|            
   6240|6200|            
   6241|6240|            
   6242|6240|            
   6243|6240|            
   6244|6240|       

前置任务数据

activity_id|parent_id
8100|8120
8100|8130
8100|8140
8200|8220
8200|8230
8200|8240
7000|7100
7000|7200
7000|7300
6200|6210
6200|6220
6200|6230
6200|6240
6240|6241
6240|6242
6240|6243
6240|6244

Answer 1

创建了两个函数，一个用于获取活动的祖先或前置任务，另一个用于获取活动的后代或后继者。

函数get_activity_anc（传递act_id，然后检索所有前置任务或上级活动）

CREATE OR REPLACE FUNCTION public.get_activity_anc(IN act_id integer)
  RETURNS TABLE(activity_id integer, parent_id integer) AS
$BODY$
BEGIN
    RETURN QUERY
    WITH RECURSIVE results(activity_id, parent_id) AS
    (
        SELECT  act.activity_id,
            act.parent_id
        FROM    activities act
        WHERE act.activity_id = act_id
        UNION ALL
        SELECT  t.activity_id,
            t.parent_id
        FROM    activities t
            INNER JOIN results r ON r.parent_id = t.activity_id
    )
    -- output the results
    SELECT  res.activity_id, res.parent_id
    FROM    results res
    ORDER BY activity_id;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE

函数get_activity_desc（传递act_id，然后检索所有后代或后续活动）

CREATE OR REPLACE FUNCTION public.get_activity_desc(IN act_id integer)
  RETURNS TABLE(activity_id integer, parent_id integer, level integer, path_info integer[]) AS
$BODY$
BEGIN
     RETURN QUERY
     WITH RECURSIVE rel_tree(activity_id, parent_id, level, path_info) AS (
               SELECT activities.activity_id,
                   activities.parent_id,
                   0 AS level,
                   sortorder || array[activities.activity_id]   as path_info
                  FROM activities
                 WHERE activities.parent_id =act_id
          UNION ALL
               SELECT c.activity_id,
                   c.parent_id,
                   p.level + 1,
                   p.path_info || c.sortorder ||c.activity_id
                  FROM activities c
                JOIN rel_tree p ON c.parent_id = p.activity_id
          )
          select * from rel_tree s order by path_info;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE

用于获取活动的祖先和后代的函数get_activity_ancdesc

CREATE OR REPLACE FUNCTION public.get_activity_ancdesc(IN act_id integer)
  RETURNS TABLE(activity_id integer, parent_id integer) AS
$BODY$
BEGIN
    RETURN QUERY
    WITH ANCESTOR_DESC AS 
    (
        select ga.activity_id, ga.parent_id from get_activity_anc(act_id) ga
        union
        select gad.activity_id, gad.parent_id from get_activity_desc(act_id) gad
    )
    select * from ANCESTOR_DESC AD
    ORDER BY AD.activity_id;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE