Re: ACS Web Server & PostgreSQL

leif@danmos.dk writes:

> select ug.group_id as subgroup_id, group_name as subgroup_name, ug.registration_date,
>                         ug.approved_p, count(user_id) as n_members,
>                         upper(group_name)
>                    from user_groups ug, user_group_map ugm
>                   where parent_group_id=1
>                     and ug.group_id=ugm.group_id
>                group by ug.group_id, group_name, ug.registration_date, ug.approved_p
>           union
[snip]
>            group by ug.group_id, group_name, ug.registration_date, ug.approved_p
>            order by upper(group_name)
>
> This statement must have worked fine in 7.0.2, but now a get an error:
> Error: Ns_PgExec: result status: 7 message: ERROR:  Attribute 'group_name' not found
> After several tries, I ended up just modifying the order by clause to:
> order by upper

This works because the expression 'upper(group_name)' happens to be
labelled 'upper' due to the lack of an AS clause, but this assumption is
not portable.  It would be better to either use an AS clause an order by
that alias, or simply use the column number, as in 'ORDER BY 6'.

--
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/