pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Rvsd. / Re: Named column in a Function fails at ORDER BY (PgSQL 7.1)
2+ messages / 2 participants
[nested] [flat]

* Rvsd. / Re: Named column in a Function fails at ORDER BY (PgSQL 7.1)
@ 2002-04-04 00:28 Bernd von den Brincken <bvdb@asa.de>
  2002-04-04 17:03 ` Re: Rvsd. / Re: Named column in a Function fails at ORDER BY (PgSQL 7.1) Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Bernd von den Brincken @ 2002-04-04 00:28 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql

Hello Tom,
thanks for your detailed remarks.

At 03.04.2002 23:46, you wrote:
>...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.

Actually I had not understood the input/output column rules with ORDER BY, 
so my question was not meant as proposal to change the rules - in the first 
place.

But at this occasion, now rethinking the concept of SQL92 vs. SQL99 (as you 
describe them) I see some advantage of "expressions over output columns".
Once again, I just rewrote my query (simplified):

SELECT A, (SELECT x FROM y) AS B
         FROM z
         ORDER BY function ( A, B )

that did not work, into:

SELECT A, function ( A, (SELECT x FROM y) ) AS B
         FROM z
         ORDER BY B

That works fine, thanks so far.
But, if I wanted the subselect as an output column (as well as a function 
parameter), I would have to write:

SELECT A, (SELECT x FROM y) AS B, function (A, B) AS C
         FROM z
         ORDER BY C

This once again does not work, because not only ORDER BY but also a 
function() in the column list seems to fail with the output column name B. 
The only workaround I see is to use the subselect twice - not very elegant. 
(But I'm still willing to learn...)

>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.

Would it really break existing applications it this syntax would be possible?
Regards
// Bernd vdB




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Rvsd. / Re: Named column in a Function fails at ORDER BY (PgSQL 7.1)
  2002-04-04 00:28 Rvsd. / Re: Named column in a Function fails at ORDER BY (PgSQL 7.1) Bernd von den Brincken <bvdb@asa.de>
@ 2002-04-04 17:03 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2002-04-04 17:03 UTC (permalink / raw)
  To: Bernd von den Brincken <bvdb@asa.de>; +Cc: pgsql-sql

Bernd von den Brincken <bvdb@asa.de> writes:
>> 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.

> Would it really break existing applications it this syntax would be possible?

Yes, it would.  The main problem with defining ORDER BY in terms of
output columns not input columns is that there is *no way* to write
an ordering spec that involves data not explicitly presented to the
user.  This is such an obvious point that I can't believe the SQL99
committee missed it.  Defining ORDER BY as they did does not create
any functional benefit, it is only a way of avoiding writing the
same expression twice --- but defining ORDER BY as we've historically
done it offers a functional benefit that would be lost if we changed
to the SQL99 interpretation.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2002-04-04 17:03 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-04-04 00:28 Rvsd. / Re: Named column in a Function fails at ORDER BY (PgSQL 7.1) Bernd von den Brincken <bvdb@asa.de>
2002-04-04 17:03 ` 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