Index: openacs-4/packages/acs-subsite/www/admin/applications/index-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/applications/index-postgresql.xql,v
diff -u -N -r1.4.2.1 -r1.4.2.2
--- openacs-4/packages/acs-subsite/www/admin/applications/index-postgresql.xql	5 Oct 2022 13:42:56 -0000	1.4.2.1
+++ openacs-4/packages/acs-subsite/www/admin/applications/index-postgresql.xql	5 Oct 2022 13:44:53 -0000	1.4.2.2
@@ -5,27 +5,30 @@
 
 <fullquery name="select_applications">
       <querytext>
-    select node_id from (
        WITH RECURSIVE site_node_tree AS (
-         select node_id, parent_id, name, object_id from site_nodes where node_id = :subsite_node_id
+         select node_id, parent_id, name, object_id, '/' as url from site_nodes where node_id = :subsite_node_id
        UNION ALL
-         select c.node_id, c.parent_id, c.name, c.object_id from site_node_tree tree, site_nodes as c
+         select c.node_id, c.parent_id, c.name, c.object_id, tree.url || c.name || '/' as url from site_node_tree tree, site_nodes as c
          where  c.parent_id = tree.node_id
        )
-       select n.node_id, n.parent_id, n.name, site_node__url(n.node_id) as url
-       from   site_node_tree n, apm_packages p
-	      left outer join lang_messages m
-	        on m.locale = 'en_US' and
-  	           '#' || m.package_key || '.' || m.message_key || '#' = p.instance_name
+       select n.node_id
+       from   site_node_tree n
+             inner join apm_packages p
+                on p.package_id = n.object_id
+             inner join apm_package_types pt
+                on pt.package_key = p.package_key
+              left outer join lang_messages m
+                on m.locale = 'en_US'
+               and m.package_key = p.package_key
+               and m.message_key = split_part(trim('#' from p.instance_name), '.', 2)
               left outer join lang_messages md
-	        on m.locale = 'en_US' and
-  	           '#' || md.package_key || '.' || md.message_key || '#' = p.instance_name,
-	      apm_package_types pt
-       where  p.package_id = n.object_id
-       and    pt.package_key = p.package_key
-       [template::list::filter_where_clauses -and -name applications]
-       order  by url) node_tree
-      </querytext>
+                on md.locale <> 'en_US'
+               and md.locale = :locale
+               and md.package_key = m.package_key
+               and md.message_key = m.message_key
+       where [template::list::filter_where_clauses -name applications]
+       order by n.url
+     </querytext>
 </fullquery>
 
 <fullquery name="select_applications_page">