pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNamed column in a Function fails at ORDER BY (PgSQL 7.1)
2+ messages / 2 participants
[nested] [flat]
* Named column in a Function fails at ORDER BY (PgSQL 7.1)
@ 2002-04-03 19:15 Bernd von den Brincken <bvdb@asa.de>
0 siblings, 1 reply; 2+ messages in thread
From: Bernd von den Brincken @ 2002-04-03 19:15 UTC (permalink / raw)
To: pgsql-sql
Hello,
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:
It seems that pgsql can not use a named result column in a function for
ORDER BY. The same named result column can be used in an ORDER BY clause a)
directly, _without_ the function or b) _with_ the same function but with
the _original_ column name.
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)
Now in this example the alternative works fine, but if you use a Sub-SELECT
there is no alternative to a named result column. I posted the simple
example here to focus on the issue.
- Thanks for any hints -
// Bernd vdB
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Named column in a Function fails at ORDER BY (PgSQL 7.1)
@ 2002-04-03 21:46 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bernd von den Brincken <bvdb@asa.de>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2002-04-03 21:46 UTC (permalink / raw)
To: Bernd von den Brincken <bvdb@asa.de>; +Cc: pgsql-sql
Bernd von den Brincken <bvdb@asa.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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-04-03 21:46 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-04-03 19:15 Named column in a Function fails at ORDER BY (PgSQL 7.1) Bernd von den Brincken <bvdb@asa.de>
2002-04-03 21:46 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox