I don’t know if this is still a topic for anyone. But here is a query that I came up with to do the sorting. It will currently probably not make use of the ltree indexing, so it might be worth to further adapt the query.
/* create a intermediate table with an id column */
WITH ltreeTable AS (
SELECT
-- select the last part of the path as id
subpath(path, -1) as "id",
"path",
"ordinal"
FROM example_table
),
/* split the ltree path into separate parts */
treeParts AS (
SELECT
"id",
-- split the path into separate parts
unnest(regexp_split_to_array(path::text, '\.'))::ltree as "part",
-- generate an ordinal for each array to preserve the order of the path
generate_subscripts(regexp_split_to_array(path::text, '\.'), 1) as "idx"
FROM ltreeTable
),
/* prefix each part with its respective zero-padded ordinal for sorting */
treePartsSorted AS (
SELECT
a.*,
-- prefix each part with the ordinal
lpad(b.ordinal::text, 4, '0') || '.' || a.part::text as "prefixed"
FROM treeParts as a
LEFT JOIN ltreeTable as b
ON a.part =
b.id ),
/* combine the paths back again */
treeSorting AS (
SELECT
"id",
-- aggregate all parts and combine it back to an ltree path
array_to_string(array_agg(prefixed ORDER BY idx),'.') AS "sorting"
FROM treePartsSorted
GROUP BY "id"
),
/* add the sorting column to the tree */
tree AS (
SELECT
a.*, text2ltree(b.sorting) as "sorting"
FROM ltreeTable as a
LEFT JOIN treeSorting as b
ON
a.id =
b.id )
SELECT * FROM tree
ORDER BY sorting asc;
