Re: Ltree - how to sort nodes on parent node - Mailing list pgsql-general

In article <59670B22-30CB-4E6E-83C8-C1D1036C9B2A@solfertje.student.utwente.nl>,
Alban Hertroys <dalroi@solfertje.student.utwente.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