Alban Hertroys schrieb am 25.10.2015 um 22:07: > WITH RECURSIVE taxons AS ( > -- Hierarchical root nodes > SELECT N AS id, Taxon, Rank, 1 AS level, '' || N AS Path -- A useful > addition explained further down > FROM t > WHERE ParentID IS NULL > > -- Child nodes > UNION ALL > SELECT N AS id, Taxon, Rank, taxons.level +1 AS level, taxons.Path || > ':' || N AS Path > FROM taxons > JOIN t ON taxons.id = t.ParentID > ) > SELECT id, Taxon, Rank, level > FROM taxons > ORDER BY Path > ; > > The Path-bit looks complicated, but basically that just appends ID's within > the same hierarchy such that, > when sorted on that field, you get the hierarchy in their hierarchical order.
I always wonder whether it's more efficient to aggregate this path using an array rather than a varchar. Mainly because representing the numbers as varchars will require more memory than as integer, but then I don't know the overhead of an array structure and whether appending to an array doesn't actually copy it. So "array[n] as path" in the root query and "taxons.path||n" in the recursive part. Any ideas? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
