pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Bernd von den Brincken <bvdb@asa.de>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Rvsd. / Re: Named column in a Function fails at ORDER BY (PgSQL 7.1)
Date: Thu, 04 Apr 2002 02:28:18 +0200
Message-ID: <5.1.0.14.2.20020404020655.01cd3ea8@pop.kundenserver.de> (raw)
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>

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




view thread (2+ messages)  latest in thread

Message-ID: <5.1.0.14.2.20020404020655.01cd3ea8@pop.kundenserver.de>
Permalink:  ../5.1.0.14.2.20020404020655.01cd3ea8@pop.kundenserver.de/
Also on:    postgresql.org/message-id/5.1.0.14.2.20020404020655.01cd3ea8@pop.kundenserver.de

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: bvdb@asa.de, tgl@sss.pgh.pa.us
  Subject: Re: Rvsd. / Re: Named column in a Function fails at ORDER BY (PgSQL 7.1)
  In-Reply-To: <5.1.0.14.2.20020404020655.01cd3ea8@pop.kundenserver.de>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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