Re: Named column in a Function fails at ORDER BY (PgSQL 7.1)

Bernd von den Brincken <bvdb(at)asa(dot)de> writes:
> on the occasion of a subselect I found a behavior in PostgreSQL 7.1 that I
> consider faulty, but would like to hear comments before complaining to the
> hackers:

Too late ;-)

> Here is the example - this fails with "ERROR: Attribute 'max_date' not
> found":

> SELECT content, datetime AS max_date
> FROM cftext
> ORDER by date_part('hour', max_date)

> Whereas this works both:

> a) SELECT content, datetime AS max_date
> FROM cftext
> ORDER by max_date

> b) SELECT content, datetime AS max_date
> FROM cftext
> ORDER by date_part('hour', datetime)

Right. The SQL92 spec disallows expressions in ORDER BY, and states that
the ORDER BY items are either names or numbers of *output columns* of
the SELECT. Thus, only your alternative (a) is legal per spec.
Postgres chooses to also allow sorting on expressions that are not in
the output list --- but such expressions are considered to be additional
expressions over the input columns. Essentially it's a way to write

SELECT content,
datetime AS max_date,
date_part('hour', datetime) AS order_val
FROM cftext
ORDER BY order_val

(which would be a spec-legal construct) and then tell the system you
didn't really want to see the order_val column in your output.

SQL99 seems to have (incompatibly) redefined ORDER BY to allow
expressions over the output column names, but I don't have a lot of
appetite for breaking existing applications in order to conform to the
SQL99 definition.

I do not have any sympathy for the notion that we should accept either
input or output column names in such expressions, if that's what you
were hoping to suggest. It's ambiguous.

> Now in this example the alternative works fine, but if you use a Sub-SELECT
> there is no alternative to a named result column.

Why not? You can always do something like

SELECT * FROM (SELECT ... ) AS T
ORDER BY x;

which is legal under both SQL specs.

regards, tom lane