Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE class (
  id   bigserial PRIMARY KEY
, name varchar(500) NOT NULL
);

INSERT INTO class VALUES
  (1, 'c1')
, (2, 'c2')
, (3, 'c3')
, (4, 'c4')
, (5, 'c5')
, (6, 'c6')
, (7, 'c7')
, (8, 'c8')
, (9, 'c9')
;

CREATE TABLE inheritance (
  id        bigserial PRIMARY KEY
, source_id bigint NOT NULL
, target_id bigint NOT NULL
-- , CONSTRAINT inherited_source_id_fkey FOREIGN KEY (source_id) REFERENCES class (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION
-- , CONSTRAINT inherited_target_id_fkey FOREIGN KEY (target_id) REFERENCES class (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION
);

INSERT INTO inheritance(source_id, target_id) VALUES
  (2, 1)
, (3, 1)
, (4, 1)
, (5, 2)
, (5, 3)
, (6, 3)
, (7, 3)
, (7, 4)
, (8, 5)
, (9, 6)
, (9, 7)
;

-- CREATE UNIQUE INDEX inheritance_uni_idx ON inheritance (GREATEST(source_id, target_id), LEAST(source_id, target_id));  -- !
9 rows affected

11 rows affected

SELECT *
FROM   class c
LEFT   JOIN (
   SELECT target_id AS id, array_agg(source_id) AS sources
   FROM  (SELECT source_id, target_id FROM inheritance i ORDER BY 1,2) sub
   GROUP  BY 1
   ) i USING (id);


id
name
sources


1
c1
{2,3,4}

2
c2
{5}

3
c3
{5,6,7}

4
c4
{7}

5
c5
{8}

6
c6
{9}

7
c7
{9}

8
c8
null

9
c9
null


-- 1. build SQL string
SELECT format(
$q$SELECT *
FROM   class c
LEFT   JOIN crosstab(
   'SELECT target_id, source_id, 1 FROM inheritance ORDER BY 1,2'
 , 'VALUES (%s)'
   ) AS ct (id int, %s int)
     USING (id)
ORDER  BY id;
$q$
        , string_agg(c.id::text, '), (')
        , string_agg('"' || c.id || '"', ' int, ')
      )
FROM  (SELECT id FROM class ORDER BY 1) c;


format


SELECT *
FROM   class c
LEFT   JOIN crosstab(
   'SELECT target_id, source_id, 1 FROM inheritance ORDER BY 1,2'
 , 'VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9)'
   ) AS ct (id int, "1" int, "2" int, "3" int, "4" int, "5" int, "6" int, "7" int, "8" int, "9" int)
     USING (id)
ORDER  BY id;



-- 2. execute
SELECT *
FROM   class c
LEFT   JOIN crosstab(
   'SELECT target_id, source_id, 1 FROM inheritance ORDER BY 1,2'
 , 'VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9)'
   ) AS ct (id int, "1" int, "2" int, "3" int, "4" int, "5" int, "6" int, "7" int, "8" int, "9" int)
     USING (id)
ORDER  BY id;


id
name
1
2
3
4
5
6
7
8
9


1
c1
null
1
1
1
null
null
null
null
null

2
c2
null
null
null
null
1
null
null
null
null

3
c3
null
null
null
null
1
1
1
null
null

4
c4
null
null
null
null
null
null
1
null
null

5
c5
null
null
null
null
null
null
null
1
null

6
c6
null
null
null
null
null
null
null
null
1

7
c7
null
null
null
null
null
null
null
null
1

8
c8
null
null
null
null
null
null
null
null
null

9
c9
null
null
null
null
null
null
null
null
null

id	name	1	2	3	4	5	6	7	8	9
1	c1	null	1	1	1	null	null	null	null	null
2	c2	null	null	null	null	1	null	null	null	null
3	c3	null	null	null	null	1	1	1	null	null
4	c4	null	null	null	null	null	null	1	null	null
5	c5	null	null	null	null	null	null	null	1	null
6	c6	null	null	null	null	null	null	null	null	1
7	c7	null	null	null	null	null	null	null	null	1
8	c8	null	null	null	null	null	null	null	null	null
9	c9	null	null	null	null	null	null	null	null	null

id	name	sources
1	c1	{2,3,4}
2	c2	{5}
3	c3	{5,6,7}
4	c4	{7}
5	c5	{8}
6	c6	{9}
7	c7	{9}
8	c8	null
9	c9	null