Hello, Don!
Ok.
Here is the difference between two selects called
from admin/site-map with Postgres 7.4.3
This is the original from CVS, using exists
in one subselect
----------------------------------------------
select package_id,
package_key,
pretty_name as package_pretty_name,
apm_package_type__num_parameters(package_key) as parameter_count,
node_id, url, parent_url, name, root_p, mylevel, object_id,
directory_p, parent_id, n_children,
p.instance_name as object_name,
acs_permission__permission_p(object_id, '3901770', 'admin') as object_admin_p
from apm_packages p join apm_package_types using (package_key) right outer join
(select n.node_id,
site_node__url(n.node_id) as url,
site_node__url(n.parent_id) as parent_url,
n.name,
case when exists (select 1 from site_nodes where parent_id = n.node_id) then 1 else 0 end as n_children,
case when n.node_id = (select site_node__node_id('/', null)) then 1 else 0 end as root_p,
(tree_level(n.tree_sortkey) - (select tree_level(n2.tree_sortkey) from site_nodes n2 where n2.node_id = (select coalesce('436', site_node__node_id('/', null))))) as mylevel,
n.object_id,
n.directory_p,
n.parent_id
from site_nodes n, site_nodes n2
where (n.object_id is null
or exists (
select 1 from acs_object_party_privilege_map ppm
where ppm.object_id = n.object_id
and ppm.party_id = '3901770'
and ppm.privilege = 'read'))
and n2.node_id = (select coalesce('436', site_node__node_id('/', null)))
and n.tree_sortkey between n2.tree_sortkey and tree_right(n2.tree_sortkey)
and (n.parent_id is null or n.parent_id in (436))) site_map
on site_map.object_id = p.package_id
order by url
;
This is modified by me, only replacing "exists"
by "1 in" inthe same subselect:
----------------------------------------------
select package_id,
package_key,
pretty_name as package_pretty_name,
apm_package_type__num_parameters(package_key) as parameter_count,
node_id, url, parent_url, name, root_p, mylevel, object_id,
directory_p, parent_id, n_children,
p.instance_name as object_name,
acs_permission__permission_p(object_id, '3901770', 'admin') as object_admin_p
from apm_packages p join apm_package_types using (package_key) right outer join
(select n.node_id,
site_node__url(n.node_id) as url,
site_node__url(n.parent_id) as parent_url,
n.name,
case when exists (select 1 from site_nodes where parent_id = n.node_id) then 1 else 0 end as n_children,
case when n.node_id = (select site_node__node_id('/', null)) then 1 else 0 end as root_p,
(tree_level(n.tree_sortkey) - (select tree_level(n2.tree_sortkey) from site_nodes n2 where n2.node_id = (select coalesce('436', site_node__node_id('/', null))))) as mylevel,
n.object_id,
n.directory_p,
n.parent_id
from site_nodes n, site_nodes n2
where (n.object_id is null
or 1 in (
select 1 from acs_object_party_privilege_map ppm
where ppm.object_id = n.object_id
and ppm.party_id = '3901770'
and ppm.privilege = 'read'))
and n2.node_id = (select coalesce('436', site_node__node_id('/', null)))
and n.tree_sortkey between n2.tree_sortkey and tree_right(n2.tree_sortkey)
and (n.parent_id is null or n.parent_id in (436))) site_map
on site_map.object_id = p.package_id
order by url
;
Regards,
Agustin
