pgsql-sql-ow...@postgresql.org wrote: > In article > <e6a0649f1fbfa3408a37f505400e7ac215c...@email.attiksystem.ch>, > "Philippe Lang" <philippe.l...@attiksystem.ch> writes: > >> Thanks for your answer. Si there a built-in function that would allow >> generating the sort path based on the value of the lookup column, >> instead of the id, which has no meaning at all? > >> If yes, we would get instead: > >> depth | id | lookup | parent_id >> -------+-----+--------+----------- >> 0 | 1 | a1 | >> 1 | 2 | b11 | 1 >> 2 | 645 | c111 | 2 >> 2 | 823 | c112 | 2 >> 2 | 243 | c113 | 2 >> 1 | 6 | b12 | 1 >> 2 | 845 | c121 | 6 >> 2 | 583 | c122 | 6 >> 1 | 9 | b13 | 1 >> 2 | 10 | c131 | 9 > > Try this: > > WITH RECURSIVE parse_tree (depth, id, lookup, parent_id, path) AS ( > SELECT 0, parent.id, parent.lookup, parent.parent_id, > parent.lookup::text FROM recursion AS parent > WHERE parent_id IS NULL > UNION ALL > SELECT parent.depth + 1, child.id, child.lookup, child.parent_id, > parent.path || '.' || child.lookup > FROM parse_tree parent > JOIN recursion AS child ON child.parent_id = parent.id > ) > SELECT depth, id, lookup, parent_id > FROM parse_tree > ORDER BY path
Works great, thanks! Of course, concatenating lookups... Best regards, Philippe -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
