Using column aliasses in the same query

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 calculated columns 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