Postgres 15

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

CREATE TABLE family (
  node text
, parent text
, color text
);

INSERT INTO family (parent, node, color)
VALUES
  (null, 'A', null)
, ('A', 'AB', null)
, ('A', 'AC', null)
, ('AB', 'ABD', null)
, ('ABD', 'ABDE', 'green')
, ('AC', 'ACF', null)
, ('AC', 'ACG', null)
, ('ACF', 'ACFH', 'red')
, ('ACF', 'ACFI', 'green')
, ('ACG', 'ACGJ', 'red')
, ('ACG', 'ACGK', 'red')
;

CREATE INDEX family_level_idx ON family (length(node));

CREATE TABLE



INSERT 0 11



CREATE INDEX


DO
$do$
DECLARE
   _max_depth int := (SELECT max(length(node)) - 1 FROM family);
   _depth int;
BEGIN
   FOR _depth IN REVERSE _max_depth .. 1 LOOP
      UPDATE family p
      SET    color = c.color
      FROM  (
         SELECT parent, CASE WHEN min(color) = max(color) THEN min(color) ELSE 'gray' END AS color
         FROM   family
         WHERE  length(node) = _depth + 1
         GROUP  BY 1
         ) c
      WHERE  p.node = c.parent
      AND    p.color IS DISTINCT FROM c.color;
   END LOOP;
END
$do$;

DO


TABLE family;


node
parent
color


ABDE
ABD
green

ACFH
ACF
red

ACFI
ACF
green

ACGJ
ACG
red

ACGK
ACG
red

ABD
AB
green

ACF
AC
gray

ACG
AC
red

AB
A
green

AC
A
gray

A
null
gray



SELECT 11

node	parent	color
ABDE	ABD	green
ACFH	ACF	red
ACFI	ACF	green
ACGJ	ACG	red
ACGK	ACG	red
ABD	AB	green
ACF	AC	gray
ACG	AC	red
AB	A	green
AC	A	gray
A	null	gray