-- Render DIEU-28 v2.0 preorder output; self-scoped, no external pub_id parameter. WITH RECURSIVE pub AS ( SELECT id FROM tac_publication WHERE doc_code = 'DIEU-28' AND version = 'v2.0' ), tree AS ( SELECT lu.id, lu.parent_id, lu.canonical_address, lu.sort_order, lu.section_type, uv.title, uv.body, 0 AS depth, ARRAY[lu.sort_order, 0, 0, 0]::int[] AS path FROM tac_logical_unit lu JOIN tac_publication_member pm ON pm.logical_unit_id = lu.id JOIN pub ON pub.id = pm.publication_id JOIN tac_unit_version uv ON uv.id = pm.unit_version_id WHERE lu.parent_id IS NULL AND lu.doc_code = 'DIEU-28' UNION ALL SELECT child.id, child.parent_id, child.canonical_address, child.sort_order, child.section_type, uv.title, uv.body, tree.depth + 1, tree.path || child.sort_order FROM tree JOIN tac_logical_unit child ON child.parent_id = tree.id JOIN tac_publication_member pm ON pm.logical_unit_id = child.id JOIN pub ON pub.id = pm.publication_id JOIN tac_unit_version uv ON uv.id = pm.unit_version_id WHERE child.doc_code = 'DIEU-28' ) SELECT canonical_address, depth, section_type, title, body FROM tree ORDER BY path;