Q:

Aggregate parents recursively in PostgreSQL

In a child-parent table, I need to aggregate all parents for each child. I can readily get children per parent in a CTE query, but can't figure how to reverse it (sqfiddle here). Given this:
CREATE TABLE rel(
  child integer,
  parent integer
);

INSERT INTO rel(child, parent)
VALUES
(1,NULL),
(2,1),
(3,1),
(4,3),
(5,2),
(6,4),
(7,2),
(8,7),
(9,8);

a query that will return an array of parents (order is not important):
1, {NULL}
2, {1}
3, {1}
4, {3,1}
5, {2,1}
6, {4,3,1}
7, {2,1}
8, {7,2,1}
9, {8,7,2,1}

A:

Even if there is an accepted answer, I would like to show how the problem can be solved in pure SQL in a much simpler way, with a recursive CTE:
WITH RECURSIVE t(child, parentlist) AS (
  SELECT child , ARRAY[]::INTEGER[] FROM rel WHERE parent IS NULL
  UNION
  SELECT rel.child, rel.parent || t.parentlist 
    FROM rel 
    JOIN t ON rel.parent = t.child
) SELECT * FROM t;

 child | parentlist 
-------+------------
     1 | {}
     2 | {1}
     3 | {1}
     4 | {3,1}
     5 | {2,1}
     7 | {2,1}
     6 | {4,3,1}
     8 | {7,2,1}
     9 | {8,7,2,1}
(9 rows)

If you insist on having a singleton {NULL} for children with an empty list of parents, just say 
SELECT child,
       CASE WHEN CARDINALITY(parentlist) = 0 
            THEN ARRAY[NULL]::INTEGER[]
            ELSE parentlist
       END
  FROM t;

instead of SELECT * FROM t, but frankly, I don’t see why you should.
A final remark: I am not aware of any efficient way to do this with relational databases, either in pure SQL or with procedural languages. The point is that JOIN’s are inherently expensive, and if you have really large tables, your queries will take lots of time. You can mitigate the problem with indexes, but the best way to tackle this kind of problems is by using graphing software and not RDBMS.

