Don,
I've had good results by using an alias term to order by in a union clause. I recreated the error above and then changed it as follows:
acspg=# select ug.group_id as subgroup_id,
acspg-# group_name as subgroup_name,
acspg-# ug.registration_date,
acspg-# ug.approved_p, count(user_id) as n_members,
acspg-# upper(group_name) as u_gname
acspg-# from user_groups ug, user_group_map ugm
acspg-# where parent_group_id=1
acspg-# and ug.group_id=ugm.group_id
acspg-# group by ug.group_id, group_name, ug.registration_date, ug.approved_p
acspg-# union
acspg-# select ug.group_id as subgroup_id,
acspg-# group_name as subgroup_name, ug.registration_date,
acspg-# ug.approved_p, 0 as n_members,
acspg-# upper(group_name) as u_gname
acspg-# from user_groups ug
acspg-# where parent_group_id=1
acspg-# and not exists (select 1 from user_group_map ugm
acspg(# where ug.group_id=ugm.group_id)
acspg-# group by ug.group_id, group_name, ug.registration_date, ug.approved_p
acspg-# order by u_gname;
subgroup_id | subgroup_name | registration_date | approved_p | n_members | u_gname
-------------+---------------+-------------------+------------+-----------+---------
(0 rows)
acspg=# select version();
version
---------------------------------------------------------------------
PostgreSQL 7.1beta3 on sparc-sun-solaris2.7, compiled by GCC 2.95.2
(1 row)
acspg=#
Here it doesn't crash, but since I don't have anything that matches the query, you can't tell if the ordering is correct. However, I have used this trick on other work that I do outside of openacs, and it has worked well in the past.
