From: | Bernd von den Brincken <bvdb(at)asa(dot)de> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Rvsd. / Re: Named column in a Function fails at ORDER BY (PgSQL 7.1) |
Date: | 2002-04-04 00:28:18 |
Message-ID: | 5.1.0.14.2.20020404020655.01cd3ea8@pop.kundenserver.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2002-04-04 01:45:35 | Re: Cleaning up template 1 -> template0 cloning |
Previous Message | Josh Berkus | 2002-04-03 21:57:44 | Re: date manipulation |