pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / 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> 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 17:03 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-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