Tom Lane wrote: > Michael Glaesemann <grzm@xxxxxxxxxxxxxxx> writes: > > On Dec 26, 2006, at 18:39 , Mike Benoit wrote: > >> ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of > >> the result columns > > > Even though state is a column in both tables, the order by is using > > an expression, rather than a column. > > ... > > I'm not sure of the underlying reasons why your query doesn't work, > > but give these a shot. > > There are some implementation reasons for not supporting expressions > computed on a UNION result without an intervening sub-SELECT. It's too > late at night for me to recall exactly what they are :-( --- one is that > an Append plan node doesn't do any expression evaluation, but I think > there are some more-subtle issues too. Suffice it to say that we could > support this if we wanted to throw enough effort at it, but so far other > problems have seemed more pressing. > > In the meantime, it seems like the above-quoted error message is not > clear enough, since Mike failed to get the point that "the ORDER BY > item has to be just a simple column name of the UNION output". Anyone > have a suggestion for better wording? I have updated the wording from "ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the result columns"))); to: "ORDER BY on a UNION/INTERSECT/EXCEPT result must match existing result columns"))); The 'match' wording might help, rather then 'use'. -- Bruce Momjian bruce@xxxxxxxxxx EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
