Os identificadores não podem ser parametrizados, então não consigo pensar em uma maneira de percorrer várias tabelas com um rCTE.

Assumindo UNIQUEou PRIMARY KEYrestrições em cada tabela, para que não tenhamos que nos preocupar com duplicatas.

Envolvi a consulta em uma função SQL para simplificar a passagem do ID:

CREATE OR REPLACE FUNCTION f_all_descendants_of(_id text)
  RETURNS TABLE (descendants text) AS
$func$
   WITH p2 AS (
      SELECT project2_id
      FROM   project2
      WHERE  project1_id = $1
         OR  project2_id = $1
      )

   SELECT project3_id AS descendants
   FROM   project3
   WHERE  project2_id IN (TABLE p2)
      OR  project3_id = $1

   UNION ALL TABLE p2

   UNION ALL
   SELECT project1_id
   FROM   project1
   WHERE  project1_id = $1;
$func$  LANGUAGE sql;

O SELECTon project2vai para o CTE p2, para que possamos reutilizar o resultado para o SELECTon project3. O mesmo não é necessário para project1e project3.

Ligar:

SELECT * FROM f_all_descendants_of('hnc4');

Produz exatamente o resultado desejado.

violino SQL.

Se as tabelas forem grandes, certifique-se de ter os índices corretos . Relacionado:

• Um índice composto também é bom para consultas no primeiro campo?

Atualização do esquema

Você não precisa de três tabelas para ter uma única herança. Você pode fazer isso com uma única tabela. Isso não é apenas mais rápido e limpo e melhor para manter a integridade, mas permite mais de três níveis de profundidade (n-level)

CREATE TABLE foo (
  id         int  PRIMARY KEY,
  id_parent  int  REFERENCES foo,
  CHECK (id>id_parent)
);

INSERT INTO foo (id, id_parent)
VALUES
  (1,null),  -- null = no parent, ancestors
  (2,null),  -- all of these are separate trees.
  (3,null),
  ( 4, 1 ),
  ( 5, 1 ),
  ( 6, 3 ),
  ( 7, 4 ),
  ( 8, 4 ),
  ( 9, 6 );

Agora podemos consultá-lo,

WITH RECURSIVE t(id, id_parent, level, path) AS (
  SELECT id, id_parent, 0, ARRAY[id]
  FROM foo
  WHERE id_parent IS NULL
  UNION ALL
    SELECT foo.id, foo.id_parent, level+1, path + foo.id
    FROM t
    JOIN foo ON (foo.id_parent = t.id)
)
SELECT *
FROM t;

 id | id_parent | level |  path   
----+-----------+-------+---------
  1 |           |     0 | {1}
  2 |           |     0 | {2}
  3 |           |     0 | {3}
  4 |         1 |     1 | {1,4}
  5 |         1 |     1 | {1,5}
  6 |         3 |     1 | {3,6}
  7 |         4 |     2 | {1,4,7}
  8 |         4 |     2 | {1,4,8}
  9 |         6 |     2 | {3,6,9}
(9 rows)

Esse padrão é chamado de "hierarquia de tabela única" ou uma tabela de auto-referência.

Compatível com versões anteriores

Na verdade, se você quiser voltar ao design bobo de três mesas ..

Crie uma visualização que faça essa última consulta,

CREATE VIEW foo_level1 AS
WITH RECURSIVE t(id, id_parent, level, path) AS (
  SELECT id, id_parent, 0, ARRAY[id]
  FROM foo
  WHERE id_parent IS NULL
  UNION ALL
    SELECT foo.id, foo.id_parent, level+1, path + foo.id
    FROM t
    JOIN foo ON (foo.id_parent = t.id)
)
SELECT *
FROM t WHERE level = 0;

Apenas continue seguindo isso..

CREATE VIEW foo_level2 AS [...] WHERE level = 1;
CREATE VIEW foo_level3 AS [...] WHERE level = 2;

Suas consultas

Para obter todos os ancestrais de hcn4ou 4na minha tabela, altere o ponto terminal e construa-o na direção oposta.

WITH RECURSIVE t(id, id_parent, level, path) AS (
  SELECT id, id_parent, 0, ARRAY[id]
  FROM foo
  WHERE id = 4
  UNION ALL
    SELECT foo.id, foo.id_parent, level+1, path + foo.id
    FROM t
    JOIN foo ON (t.id = foo.id_parent)
)
 AS
  SELECT * FROM t;

Quer hcn1só precisa mudar para WHERE id = 1;

WITH RECURSIVE t(id, id_parent, level, path) AS (
  SELECT id, id_parent, 0, ARRAY[id]
  FROM foo
  WHERE id = 1
  UNION ALL
    SELECT foo.id, foo.id_parent, level+1, path + foo.id
    FROM t
    JOIN foo ON (t.id = foo.id_parent)
)
SELECT *
FROM t;

 id | id_parent | level |  path   
----+-----------+-------+---------
  1 |           |     0 | {1}
  4 |         1 |     1 | {1,4}
  5 |         1 |     1 | {1,5}
  7 |         4 |     2 | {1,4,7}
  8 |         4 |     2 | {1,4,8}
(5 rows)

O equivalente, f_all_descendants_of(int)torna-se,

CREATE FUNCTION f_all_descendants_of(_id int)
RETURNS TABLE (id int, id_parent int, level int, path int[]) AS $$
  WITH RECURSIVE t(id, id_parent, level, path) AS (
    SELECT id, id_parent, 0, ARRAY[id]
    FROM foo
    WHERE id = _id
    UNION ALL
      SELECT foo.id, foo.id_parent, level+1, path + foo.id
      FROM t
      JOIN foo ON (t.id = foo.id_parent)
  )
  SELECT * FROM t;
$$ LANGUAGE sql
IMMUTABLE;