PostgreSQL recursive parent/child query

Question

I'm having some trouble working out the PostgreSQL documentation for recursive queries, and wonder if anyone might be able to offer a suggestion for the following.

Here's the data:

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

Here, a "subject" may have many parents and many children. Of course, at the top level a subject has no parents and at the bottom no children. For example:

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

What I'm looking for is starting with a child_id to get all the ancestors, and with a parent_id, all the descendants. Therefore:

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

Though there seem to be a lot of examples of similar things about I'm having trouble making sense of them, so any suggestions I can try out would be welcome.

You can start with any subject. I guess I don't understand your question fully. — Laurenz Albe
– Laurenz Albe, Commented Feb 27, 2019 at 14:59
Indeed. So if I started with subject 1, what SQL query would give me all the children, and if with subject 7 which query would give all the parents? Etc. etc. — knirirr
– knirirr, Commented Feb 27, 2019 at 15:26

Laurenz Albe · Accepted Answer · 2019-02-27 15:57:33Z

37

To get all children for subject 1, you can use

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;

answered Feb 27, 2019 at 15:57

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

knirirr Over a year ago

That seems to work very nicely - also in reverse. A bit simpler than I was thinking (I kept trying to join on subjects).

Laurenz Albe Over a year ago

You'd join with subjects in the outer query.

J Spratt · Accepted Answer · 2019-12-19 16:09:18Z

7

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;

edited Dec 19, 2019 at 16:09

answered Feb 27, 2019 at 15:59

J Spratt

2,0521 gold badge16 silver badges24 bronze badges

3 Comments

knirirr Over a year ago

Thanks for this reply. I found the other a little easier to understand - I'm not quite sure about how next/prev are working.

J Spratt Over a year ago

You're welcome. prev is what is currently in your recursive table, t in this case. next is what you'll be adding to the recursive table after a successful join. It's very similar to Laurenz's answer, I just gave the recursive table an alias in my second select statement. In my answer t is just like c in Laurenz's answer. He just does the join in the opposite order. I selected from the recursive table and then joined the subject_associations table.

knirirr Over a year ago

Thanks again. For the moment I think I prefer using the other answer, which seems to work nicely in my application, so I hope you don't mind me marking that as accepted.

Umanda · Accepted Answer · 2020-03-31 06:21:00Z

Try this

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

Collectives™ on Stack Overflow

PostgreSQL recursive parent/child query

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related