Get all descendants from 3 hierarchical tables
Identifiers cannot be parameterized, so I can't think of a way to traverse multiple tables with an rCTE.

Assuming UNIQUE or PRIMARY KEY constraints on each table, so we don't have to worry about duplicates.

I wrapped the query in a SQL function to simplify passing the 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;


The SELECT on project2 goes into CTE p2, so we can reuse the result for the SELECT on project3. The same is not needed for project1 and project3.

Call:

SELECT * FROM f_all_descendants_of('hnc4');


Produces your desired result exactly.

SQL Fiddle.

If the tables are big, be sure to have the right indexes. Related:


Is a composite index also good for queries on the first field?