PostgreSQL递归父/子查询

Question

我正在阅读PostgreSQL递归查询文档时遇到一些问题，不知道是否有人能够提供以下内容的建议。

这是数据:

                                            Table "public.subjects"
      Column       |            Type             | Collation | Nullable |               Default                
-------------------+-----------------------------+-----------+----------+--------------------------------------
 id                | bigint                      |           | not null | nextval('subjects_id_seq'::regclass)
 name              | character varying           |           |          | 



                                        Table "public.subject_associations"
   Column   |            Type             | Collation | Nullable |                     Default                      
------------+-----------------------------+-----------+----------+--------------------------------------------------
 id         | bigint                      |           | not null | nextval('subject_associations_id_seq'::regclass)
 parent_id  | integer                     |           |          | 
 child_id   | integer                     |           |          | 

这里，“主题”可以有很多父级和很多子级。当然，在最高层级上，一个主题没有父级，在最低层级上没有子级。例如：

 parent_id  |  child_id  
------------+------------
     2      |     3
     1      |     4
     1      |     3
     4      |     8
     4      |     5
     5      |     6
     6      |     7

我要找的是从一个子代ID开始获取所有祖先，以及从一个父代ID开始获取所有后代。 因此：

parent_id 1 -> children 3, 4, 5, 6, 7, 8
parent_id 2 -> children 3

child_id 3 -> parents 1, 2
child_id 4 -> parents 1
child_id 7 -> parents 6, 5, 4, 1

虽然似乎有很多类似的例子，但我很难理解它们，因此欢迎任何我可以尝试的建议。

- knirirr

---

你可以从任何主题开始。我猜我没有完全理解你的问题。 - Laurenz Albe

确实。如果我从主题1开始，哪个SQL查询会给我所有的子项，如果从主题7开始，哪个查询会给出所有的父项？等等。 - knirirr

Answer 1

要获取主题1的所有子项，您可以使用

WITH RECURSIVE c AS (
   SELECT 1 AS id
   UNION ALL
   SELECT sa.child_id
   FROM subject_associations AS sa
      JOIN c ON c.id = sa. parent_id
)
SELECT id FROM c;

- Laurenz Albe

---

这似乎非常好的运作 - 反向也是如此。比我想象的要简单一些（我一直试图在主题上进行连接）。 - knirirr

你需要在外部查询中与 subjects 进行连接。 - Laurenz Albe

Answer 2

CREATE OR REPLACE FUNCTION func_finddescendants(start_id integer)
RETURNS SETOF subject_associations
AS $$
DECLARE
BEGIN
    RETURN QUERY
    WITH RECURSIVE t
    AS
    (
        SELECT * 
          FROM subject_associations sa
         WHERE sa.id = start_id
         UNION ALL
        SELECT next.*
          FROM t prev
          JOIN subject_associations next ON (next.parentid = prev.id)
    )
    SELECT * FROM t;
END;
$$  LANGUAGE PLPGSQL;

- J Spratt

---

谢谢您的回复。我发现另一个更容易理解 - 我不太确定next/prev是如何工作的。 - knirirr

不用客气。prev是你递归表中当前的内容，本例中是t。next是在成功连接后将添加到递归表中的内容。这与Laurenz的答案非常相似，只是我在第二个选择语句中给递归表取了一个别名。在我的答案中，t就像Laurenz答案中的c一样。他只是以相反的顺序进行连接。我从递归表中选择，然后加入了subject_associations表。 - J Spratt

再次感谢。目前我认为我更喜欢使用另一个答案，在我的应用程序中它似乎很好用，所以我希望你不介意我将其标记为已接受的答案。 - knirirr

Answer 3

试试这个

--- Table

-- DROP SEQUENCE public.data_id_seq;

CREATE SEQUENCE "data_id_seq"
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE public.data_id_seq
  OWNER TO postgres;



CREATE TABLE public.data
(
  id integer NOT NULL DEFAULT nextval('data_id_seq'::regclass),
  name character varying(50) NOT NULL,
  label character varying(50) NOT NULL,
  parent_id integer NOT NULL,
  CONSTRAINT data_pkey PRIMARY KEY (id),
  CONSTRAINT data_name_parent_id_unique UNIQUE (name, parent_id)
)
WITH (
  OIDS=FALSE
);

INSERT INTO public.data(id, name, label, parent_id) VALUES (1,'animal','Animal',0);
INSERT INTO public.data(id, name, label, parent_id) VALUES (5,'birds','Birds',1);   
INSERT INTO public.data(id, name, label, parent_id) VALUES (6,'fish','Fish',1); 
INSERT INTO public.data(id, name, label, parent_id) VALUES (7,'parrot','Parrot',5); 
INSERT INTO public.data(id, name, label, parent_id) VALUES (8,'barb','Barb',6);

--- Function 

CREATE OR REPLACE FUNCTION public.get_all_children_of_parent(use_parent integer) RETURNS integer[] AS
        $BODY$
        DECLARE
            process_parents INT4[] := ARRAY[ use_parent ];
            children INT4[] := '{}';
            new_children INT4[];
        BEGIN
            WHILE ( array_upper( process_parents, 1 ) IS NOT NULL ) LOOP
                new_children := ARRAY( SELECT id FROM data WHERE parent_id = ANY( process_parents ) AND id <> ALL( children ) );
                children := children || new_children;
                process_parents := new_children;
            END LOOP;
            RETURN children;
        END;
        $BODY$
LANGUAGE plpgsql VOLATILE COST 100;
ALTER FUNCTION public.get_all_children_of_parent(integer) OWNER TO postgres


--- Test 

SELECT *  FROM data WHERE id = any(get_all_children_of_parent(1))
SELECT *  FROM data WHERE id = any(get_all_children_of_parent(5))
SELECT *  FROM data WHERE id = any(get_all_children_of_parent(6))

- Umanda