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
representingthe numbers as varchars will require more memory than as integer, but then I don't know the overhead of an
arraystructure 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?