The following bug has been logged online:

Bug reference: 1265
Logged by: Ansis

Email address: ataols (AT) latnet (DOT) lv

PostgreSQL version: 7.4.2

Operating system: Gentoo Linux

Description: sorting by (ORDER BY) result of an operation for names
assigned by AS does not work

Details:

The query:
SELECT id, lang as name, (SELECT lang FROM participants WHERE id =
event.participant) as pname FROM event ORDER BY name || pname;
returns an error:
ERROR: column "name" does not exist

However, "ORDER BY name" works, "ORDER BY name, pname" works etc. Also, if
I use original column names, not assigned ones, it works with cncatenation
operator too. So, the error occurs only then assigned names are used inside
operator - the Postgre does not find this name.
It looks like a bug - and an easy fixable one.


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

"PostgreSQL Bugs List" <pgsql-bugs (AT) postgresql (DOT) org> writes:
It's not a bug. Per
http://www.postgresql.org/docs/7.4/s...ql-select.html
under "ORDER BY Clause"

: expression can be the name or ordinal number of an output column (SELECT
: list item), or it can be an arbitrary expression formed from
: input-column values.

The ability to reference an output column in ORDER BY is actually a legacy
feature that was removed in the SQL99 spec.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend