From: | Harald Fuchs <hari(dot)fuchs(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Ltree - how to sort nodes on parent node |
Date: | 2010-04-20 16:05:46 |
Message-ID: | pubpdecdnp.fsf@srv.protecting.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
In article <59670B22-30CB-4E6E-83C8-C1D1036C9B2A(at)solfertje(dot)student(dot)utwente(dot)nl>,
Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> writes:
> 2). Drop the ltree column and go with a truly recursive approach, something like this:
> CREATE TABLE node (
> category text NOT NULL PRIMARY KEY,
> sort_order int NOT NULL,
> parent text REFERENCES tree (category)
> ON UPDATE CASCADE
> ON DELETE CASCADE
> );
> WITH RECURSIVE tree AS (
> SELECT *
> FROM node
> WHERE parent IS NULL
> UNION ALL
> SELECT node.*
> FROM tree, node
> WHERE node.parent = tree.category
> ORDER BY sort_order
> )
> SELECT * FROM tree;
Here's a working version:
WITH RECURSIVE tree (path, category, sort_order, parent) AS (
SELECT category, category, sort_order::text, parent
FROM node
WHERE parent IS NULL
UNION ALL
SELECT t.path || '.' || n.category,
n.category,
t.sort_order || '.' || n.sort_order,
n.parent
FROM tree t
JOIN node n ON n.parent = t.category
)
SELECT path
FROM tree
ORDER BY sort_order
From | Date | Subject | |
---|---|---|---|
Next Message | Glus Xof | 2010-04-20 17:53:34 | Specific database vars, again... |
Previous Message | sunpeng | 2010-04-20 15:19:40 | how to debug the codes in the PostgresMain() from the begining of this function to the "for (;;)" loop that handles the connection? |