Received: from moutvdom00.kundenserver.de (moutvdom00.kundenserver.de [195.20.224.149]) by postgresql.org (Postfix) with ESMTP id 4FFB9474E6D for ; Wed, 3 Apr 2002 19:32:14 -0500 (EST) Received: from [195.20.224.219] (helo=mrvdom03.kundenserver.de) by moutvdom00.kundenserver.de with esmtp (Exim 2.12 #2) id 16svAa-0001g1-00; Thu, 4 Apr 2002 02:32:04 +0200 Received: from [213.7.63.119] (helo=BURNT.asa.de) by mrvdom03.kundenserver.de with esmtp (Exim 2.12 #2) id 16svAa-0004r0-00; Thu, 4 Apr 2002 02:32:04 +0200 Message-Id: <5.1.0.14.2.20020404020655.01cd3ea8@pop.kundenserver.de> X-Sender: m6261949-7@pop.kundenserver.de X-Mailer: QUALCOMM Windows Eudora Version 5.1 Date: Thu, 04 Apr 2002 02:28:18 +0200 To: Tom Lane From: Bernd von den Brincken Subject: Rvsd. / Re: Named column in a Function fails at ORDER BY (PgSQL 7.1) Cc: pgsql-sql@postgresql.org In-Reply-To: <12792.1017870411@sss.pgh.pa.us> References: <5.1.0.14.2.20020403211109.01cf5e40@pop.kundenserver.de> <5.1.0.14.2.20020403211109.01cf5e40@pop.kundenserver.de> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed X-Archive-Number: 200204/52 X-Sequence-Number: 7317 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