Thread: Using column aliasses in the same query
Hi, I am not sure if there ever was a feature request for using defined column aliases in the rest of a query. This would make queries with a lot of logic in those aliased columns a lot smaller and this easier to write/debug. I already know you can use the following syntax: SELECT col1, col2, col3, (col2-col3) as col4 FROM (SELECT col1, (long and lots of logic here) as col2, col3 FROM table) sWHERE col2 < aValue But when you need to use (calculated) values from the actual record and or have sub-selects in your main select that alsoneed to use these values things get really hairy. I don't know if the SQL specification allows it but I know that RDBMS's like Sybase already support this. Any thoughts? Cheers, Robert
On Sunday 17 April 2011 13:01:45 Robert J.C. Ivens wrote: > Hi, > > I am not sure if there ever was a feature request for using defined column > aliases in the rest of a query. This would make queries with a lot of > logic in those aliased columns a lot smaller and this easier to > write/debug. > > I already know you can use the following syntax: > > SELECT col1, col2, col3, (col2-col3) as col4 FROM (SELECT col1, (long and > lots of logic here) as col2, col3 FROM table) s WHERE col2 < aValue > > But when you need to use (calculated) values from the actual record and or > have sub-selects in your main select that also need to use these values > things get really hairy. I don't know if the SQL specification allows it > but I know that RDBMS's like Sybase already support this. > > Any thoughts? It's easy to define a view or an SQL function and stash the hairy logic there. regards, Leif
On 17 apr 2011, at 13:21, Leif Biberg Kristensen wrote: > On Sunday 17 April 2011 13:01:45 Robert J.C. Ivens wrote: >> Hi, >> >> I am not sure if there ever was a feature request for using defined column >> aliases in the rest of a query. This would make queries with a lot of >> logic in those aliased columns a lot smaller and this easier to >> write/debug. >> >> I already know you can use the following syntax: >> >> SELECT col1, col2, col3, (col2-col3) as col4 FROM (SELECT col1, (long and >> lots of logic here) as col2, col3 FROM table) s WHERE col2 < aValue >> >> But when you need to use (calculated) values from the actual record and or >> have sub-selects in your main select that also need to use these values >> things get really hairy. I don't know if the SQL specification allows it >> but I know that RDBMS's like Sybase already support this. >> >> Any thoughts? > > It's easy to define a view or an SQL function and stash the hairy logic there. > > regards, Leif True, but that is essentially the same thing as the example query I gave. There are plenty of cases where this approach is not workable. Cheers, Robert
2011/4/17 Robert J.C. Ivens <robert@roclasi.com>:
>
> On 17 apr 2011, at 13:21, Leif Biberg Kristensen wrote:
>
>> On Sunday 17 April 2011 13:01:45 Robert J.C. Ivens wrote:
>>> Hi,
>>>
>>> I am not sure if there ever was a feature request for using defined column
>>> aliases in the rest of a query. This would make queries with a lot of
>>> logic in those aliased columns a lot smaller and this easier to
>>> write/debug.
>>>
>>> I already know you can use the following syntax:
>>>
>>> SELECT col1, col2, col3, (col2-col3) as col4 FROM (SELECT col1, (long and
>>> lots of logic here) as col2, col3 FROM table) s WHERE col2 < aValue
>>>
>>> But when you need to use (calculated) values from the actual record and or
>>> have sub-selects in your main select that also need to use these values
>>> things get really hairy. I don't know if the SQL specification allows it
>>> but I know that RDBMS's like Sybase already support this.
>>>
>>> Any thoughts?
>>
>> It's easy to define a view or an SQL function and stash the hairy logic there.
>>
>> regards, Leif
>
> True, but that is essentially the same thing as the example query I gave.
> There are plenty of cases where this approach is not workable.
select bar.*, b-c
from (select i,i,i from foo )
as bar(a,b,c)
where c!=1;
you can also look at:
http://www.postgresql.org/docs/9.0/static/queries-with.html
--
Cédric Villemain 2ndQuadrant
http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support
Maybe you think about WITH queries? 2011/4/17, Robert J.C. Ivens <robert@roclasi.com>: > > On 17 apr 2011, at 13:21, Leif Biberg Kristensen wrote: > >> On Sunday 17 April 2011 13:01:45 Robert J.C. Ivens wrote: >>> Hi, >>> >>> I am not sure if there ever was a feature request for using defined >>> column >>> aliases in the rest of a query. This would make queries with a lot of >>> logic in those aliased columns a lot smaller and this easier to >>> write/debug. >>> >>> I already know you can use the following syntax: >>> >>> SELECT col1, col2, col3, (col2-col3) as col4 FROM (SELECT col1, (long and >>> lots of logic here) as col2, col3 FROM table) s WHERE col2 < aValue >>> >>> But when you need to use (calculated) values from the actual record and >>> or >>> have sub-selects in your main select that also need to use these values >>> things get really hairy. I don't know if the SQL specification allows it >>> but I know that RDBMS's like Sybase already support this. >>> >>> Any thoughts? >> >> It's easy to define a view or an SQL function and stash the hairy logic >> there. >> >> regards, Leif > > True, but that is essentially the same thing as the example query I gave. > There are plenty of cases where this approach is not workable. > > Cheers, > Robert > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- ------------ pasman
On 17 apr 2011, at 13:43, pasman pasmański wrote: > Maybe you think about WITH queries? > > 2011/4/17, Robert J.C. Ivens <robert@roclasi.com>: >> >> On 17 apr 2011, at 13:21, Leif Biberg Kristensen wrote: >> >>> On Sunday 17 April 2011 13:01:45 Robert J.C. Ivens wrote: >>>> Hi, >>>> >>>> I am not sure if there ever was a feature request for using defined >>>> column >>>> aliases in the rest of a query. This would make queries with a lot of >>>> logic in those aliased columns a lot smaller and this easier to >>>> write/debug. >>>> >>>> I already know you can use the following syntax: >>>> >>>> SELECT col1, col2, col3, (col2-col3) as col4 FROM (SELECT col1, (long and >>>> lots of logic here) as col2, col3 FROM table) s WHERE col2 < aValue >>>> >>>> But when you need to use (calculated) values from the actual record and >>>> or >>>> have sub-selects in your main select that also need to use these values >>>> things get really hairy. I don't know if the SQL specification allows it >>>> but I know that RDBMS's like Sybase already support this. >>>> >>>> Any thoughts? >>> >>> It's easy to define a view or an SQL function and stash the hairy logic >>> there. >>> >>> regards, Leif >> >> True, but that is essentially the same thing as the example query I gave. >> There are plenty of cases where this approach is not workable. >> >> Cheers, >> Robert CTE's are another option yes. But again it becomes really hairy (if not impossible) when you have a query where the calculatedcolumns are used all over the place as input values for other subqueries. Being able to use the aliases in the same scope would simplify things tremendously. Cheers, Robert
"Robert J.C. Ivens" <robert@roclasi.com> writes:
> I am not sure if there ever was a feature request for using defined
> column aliases in the rest of a query.
Yes, we've heard that before. Many times. It's not going to happen,
and here's why: it's flat out contrary to the SQL specification, as well
as to the basic intuitive semantics of SQL. The SELECT list is supposed
to be evaluated as the last step of a query (well, last except for ORDER
BY, which is why there's an exception for that). It's nonsensical for
WHERE etc to depend on the results of the SELECT list.
As an example of why this is important, consider
SELECT x/y AS z FROM tab WHERE y <> 0
If the WHERE clause doesn't act before the SELECT list is computed,
the query is going to fail with divisions-by-zero, exactly what the
WHERE clause is trying to prevent. So it'd be nonsensical to refer
to z in the WHERE clause.
regards, tom lane
On Sun, Apr 17, 2011 at 6:00 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Yes, we've heard that before. Many times. It's not going to happen,
> and here's why: it's flat out contrary to the SQL specification, as well
> as to the basic intuitive semantics of SQL. The SELECT list is supposed
> to be evaluated as the last step of a query (well, last except for ORDER
> BY, which is why there's an exception for that). It's nonsensical for
> WHERE etc to depend on the results of the SELECT list.
>
> As an example of why this is important, consider
>
> SELECT x/y AS z FROM tab WHERE y <> 0
>
> If the WHERE clause doesn't act before the SELECT list is computed,
> the query is going to fail with divisions-by-zero, exactly what the
> WHERE clause is trying to prevent. So it'd be nonsensical to refer
> to z in the WHERE clause.
Well, refering to the computed value may be nonsensical, but
couldn't it be some sort of query rewrite? So that...
SELECT x/y AS z FROM tab WHERE y <> 0 AND z > 2
... is a shorthand for
SELECT x/y AS z FROM tab WHERE y <> 0 AND x/y > 2
No big deal, since there are lots of other ways to do this.
--
Eld på åren og sol på eng gjer mannen fegen og fjåg. [Jøtul]
<demo> 2011 Tore Halvorsen || +052 0553034554
On 18 April 2011 22:06, Tore Halvorsen <tore.halvorsen@gmail.com> wrote: > Well, refering to the computed value may be nonsensical, but > couldn't it be some sort of query rewrite? So that... > > SELECT x/y AS z FROM tab WHERE y <> 0 AND z > 2 > > ... is a shorthand for > > SELECT x/y AS z FROM tab WHERE y <> 0 AND x/y > 2 > > No big deal, since there are lots of other ways to do this. That's an accurate observation, but has nothing to do w/ what the original poster was looking for, nor does it refute Toms argument against the OPs suggestion. Cheers, Andrej
On Wed, Apr 20, 2011 at 12:13 AM, Andrej <andrej.groups@gmail.com> wrote: > That's an accurate observation, but has nothing to do w/ what > the original poster was looking for, nor does it refute Toms > argument against the OPs suggestion. You're right, I jumped in without thinking enough.Sorry. I had just written some queries where a shortcut like the above would have made it slighly easier on the eyes and misinterpreted the discussion. -- Eld på åren og sol på eng gjer mannen fegen og fjåg. [Jøtul] <demo> 2011 Tore Halvorsen || +052 0553034554