postgresql 7.1
select news_items_approved.package_id, acs_object__name(apm_package__parent_id(news_items_approved.package_id)) as parent_name, (select site_node__url(site_nodes.node_id) from site_nodes where site_nodes.object_id = news_items_approved.package_id) as url, news_items_approved.item_id, news_items_approved.publish_title, to_char(news_items_approved.publish_date, 'YYYY-MM-DD HH24:MI:SS') as publish_date_ansi, item_creator, creation_user $content_column from news_items_approved where news_items_approved.publish_date < current_timestamp and (news_items_approved.archive_date >= current_timestamp or news_items_approved.archive_date is null) and news_items_approved.package_id in ([join $list_of_package_ids ", "]) order by package_id, parent_name, news_items_approved.publish_date desc, news_items_approved.publish_title

select package_id from apm_packages p right outer join (select n.object_id, n.parent_id, tree_level(n.tree_sortkey) as mylevel from site_nodes n, site_nodes root where (n.object_id is null or acs_permission__permission_p(n.object_id, :user_id, 'read') = 't') and root.node_id = :root_id and n.tree_sortkey between root.tree_sortkey and tree_right(root.tree_sortkey)) site_map on site_map.object_id = p.package_id where package_key = 'news'