From: | Thomas Kellerer <spam_eater(at)gmx(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Recursive Arrays 101 |
Date: | 2015-10-26 14:54:29 |
Message-ID: | n0ler5$gml$1@ger.gmane.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2015-10-26 14:57:06 | Re: Service not starting on Ubuntu 15.04 |
Previous Message | Jim Nasby | 2015-10-26 14:43:42 | Re: Recursive Arrays 101 |