In article <59670B22-30CB-4E6E-83C8-C1D1036C9B2A@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>,
Alban Hertroys <dalroi@xxxxxxxxxxxxxxxxxxxxxxxxxxxx> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
