Thread: RFE: Column aliases in WHERE clauses

It would be nice if PostgreSQL supported column aliases in WHERE
clauses, eg:

SELECT left(value, 1) AS first_letter
FROM some_table
WHERE first_letter > 'a';

Is this the proper mailing list for such feature requests?

Thanks in advance,
Daniel Serodio

On Mon, Sep 17, 2012 at 06:18:53PM -0300, Daniel Serodio (lists) wrote:
> It would be nice if PostgreSQL supported column aliases in WHERE
> clauses, eg:
>
> SELECT left(value, 1) AS first_letter
> FROM some_table
> WHERE first_letter > 'a';
>
> Is this the proper mailing list for such feature requests?
I think this is explicitly disallowed by the spec.

And by Tom:
http://archives.postgresql.org/pgsql-general/2002-11/msg01411.php

>
> Thanks in advance,
> Daniel Serodio
>

-Ryan Kelly

On Sep 17, 2012, at 17:42, Ryan Kelly <rpkelly22@gmail.com> wrote:

> On Mon, Sep 17, 2012 at 06:18:53PM -0300, Daniel Serodio (lists) wrote:
>> It would be nice if PostgreSQL supported column aliases in WHERE
>> clauses, eg:
>>
>> SELECT left(value, 1) AS first_letter
>> FROM some_table
>> WHERE first_letter > 'a';
>>
>> Is this the proper mailing list for such feature requests?
> I think this is explicitly disallowed by the spec.
>

Just to be clear, the spec does not care where you post your feature requests...it is the feature that it disallows.

When in doubt the general list is a good choice and all the key people monitor it and will move the discussion
elsewhereif warranted. 

David J.

On Mon, Sep 17, 2012 at 06:18:53PM -0300, Daniel Serodio (lists) wrote:

It would be nice if PostgreSQL supported column aliases in WHERE
clauses, eg:

SELECT left(value, 1) AS first_letter
FROM some_table
WHERE first_letter > 'a';

Is this the proper mailing list for such feature requests?

I think this is explicitly disallowed by the spec.

And by Tom:
http://archives.postgresql.org/pgsql-general/2002-11/msg01411.php

On Mon, Sep 17, 2012 at 3:10 PM, Daniel Serodio (lists)
<daniel.lists@mandic.com.br> wrote:
> Ryan Kelly wrote:
>
> On Mon, Sep 17, 2012 at 06:18:53PM -0300, Daniel Serodio (lists) wrote:
>
> It would be nice if PostgreSQL supported column aliases in WHERE
> clauses, eg:
>
> SELECT left(value, 1) AS first_letter
> FROM some_table
> WHERE first_letter > 'a';
>
> Is this the proper mailing list for such feature requests?
>
> I think this is explicitly disallowed by the spec.
>
> And by Tom:
> http://archives.postgresql.org/pgsql-general/2002-11/msg01411.php
>
> Tom's explanation makes perfect sense, thanks for the pointer.

This definitely makes sense in the context of aggregation, but I'm
wondering if the same argument applies in the use case originally
posted:

SELECT left(value, 1) as first_letter
FROM some_table
WHERE first_letter > 'a';

Obviously, you can write this as:

SELECT left(value, 1) as first_letter
FROM some_table
WHERE left(value, 1) > 'a';

This would run fine, though you'd be doing a sequential scan on the
entire table, getting the left most character in each value, then
filtering those results.  This of course assumes you haven't built an
index on left(value, 1).

Thus, in theory the compiler *could* resolve the actual definition of
first_letter and substitute in that expression on the fly.  I'm
wondering if that concept is actually disallowed by the SQL spec.
Obviously, it would add complexity (and compile overhead) but would be
somewhat handy to avoid repeating really complicated expressions.
Perhaps Common Table Expressions are a better way of doing this thing
anyhow.

Mike

Mike Christensen <mike@kitchenpc.com> writes:
> This definitely makes sense in the context of aggregation, but I'm
> wondering if the same argument applies in the use case originally
> posted:

> SELECT left(value, 1) as first_letter
> FROM some_table
> WHERE first_letter > 'a';

> Obviously, you can write this as:

> SELECT left(value, 1) as first_letter
> FROM some_table
> WHERE left(value, 1) > 'a';

> This would run fine, though you'd be doing a sequential scan on the
> entire table, getting the left most character in each value, then
> filtering those results.  This of course assumes you haven't built an
> index on left(value, 1).

> Thus, in theory the compiler *could* resolve the actual definition of
> first_letter and substitute in that expression on the fly.  I'm
> wondering if that concept is actually disallowed by the SQL spec.

Yes, it is.  If you read the spec you'll find that the scope of
visibility of names defined in the SELECT list doesn't include WHERE.

It's easier to understand why this is if you realize that SQL has a very
clear model of a "pipeline" of query execution.  Conceptually, what
happens is:

1. Form the cartesian product of the tables listed in FROM (ie, all
combinations of rows).

2. Apply the WHERE condition to each row from 1, and drop rows that
don't pass it.

3. If there's a GROUP BY, merge the surviving rows into groups.

4. If there's aggregate functions, compute those over the rows in
each group.

5. If there's a HAVING, filter the grouped rows according to that.

6. Evaluate the SELECT expressions for each remaining row.

7. If there's an ORDER BY, evaluate those expressions and sort the
remaining rows accordingly.

(Obviously, implementations try to improve on this - you don't want
to actually form the cartesian product - but that's the conceptual
model.)

The traditional shortcut of doing "ORDER BY select-column-reference"
is okay according to this world view, because the SELECT expressions
are already available when ORDER BY needs them.  However, it's not
sensible to refer to SELECT outputs in WHERE, HAVING, or GROUP BY,
because those steps precede the evaluation of the SELECT expressions.

This isn't just academic nit-picking either, because the SELECT
expressions might not be valid for rows that don't pass WHERE etc.
Consider
    SELECT 1/x AS inverse FROM data WHERE x <> 0;
The implementation *must* apply WHERE before computing the SELECT
expressions, or it'll get zero-divide failures that should not happen.

Now, having said all that, if you try it you'll find that Postgres
does allow select column references in GROUP BY, using the model
you propose above of copying whatever expression is in SELECT into
GROUP BY.  This is, to put it politely, a mistake that we are now
stuck with for backwards-compatibility reasons.  It's not spec compliant
and it doesn't fit the language's conceptual model, but it's been that
way for long enough that we're not likely to take it out.  We are not,
however, gonna introduce the same mistake elsewhere.

> Obviously, it would add complexity (and compile overhead) but would be
> somewhat handy to avoid repeating really complicated expressions.
> Perhaps Common Table Expressions are a better way of doing this thing
> anyhow.

CTEs or sub-selects are a better answer for that.  Each sub-select has
its own instance of the conceptual pipeline.

            regards, tom lane

On Mon, Sep 17, 2012 at 4:32 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Mike Christensen <mike@kitchenpc.com> writes:
>> This definitely makes sense in the context of aggregation, but I'm
>> wondering if the same argument applies in the use case originally
>> posted:
>
>> SELECT left(value, 1) as first_letter
>> FROM some_table
>> WHERE first_letter > 'a';
>
>> Obviously, you can write this as:
>
>> SELECT left(value, 1) as first_letter
>> FROM some_table
>> WHERE left(value, 1) > 'a';
>
>> This would run fine, though you'd be doing a sequential scan on the
>> entire table, getting the left most character in each value, then
>> filtering those results.  This of course assumes you haven't built an
>> index on left(value, 1).
>
>> Thus, in theory the compiler *could* resolve the actual definition of
>> first_letter and substitute in that expression on the fly.  I'm
>> wondering if that concept is actually disallowed by the SQL spec.
>
> Yes, it is.  If you read the spec you'll find that the scope of
> visibility of names defined in the SELECT list doesn't include WHERE.
>
> It's easier to understand why this is if you realize that SQL has a very
> clear model of a "pipeline" of query execution.  Conceptually, what
> happens is:
>
> 1. Form the cartesian product of the tables listed in FROM (ie, all
> combinations of rows).
>
> 2. Apply the WHERE condition to each row from 1, and drop rows that
> don't pass it.
>
> 3. If there's a GROUP BY, merge the surviving rows into groups.
>
> 4. If there's aggregate functions, compute those over the rows in
> each group.
>
> 5. If there's a HAVING, filter the grouped rows according to that.
>
> 6. Evaluate the SELECT expressions for each remaining row.
>
> 7. If there's an ORDER BY, evaluate those expressions and sort the
> remaining rows accordingly.
>
> (Obviously, implementations try to improve on this - you don't want
> to actually form the cartesian product - but that's the conceptual
> model.)
>
> The traditional shortcut of doing "ORDER BY select-column-reference"
> is okay according to this world view, because the SELECT expressions
> are already available when ORDER BY needs them.  However, it's not
> sensible to refer to SELECT outputs in WHERE, HAVING, or GROUP BY,
> because those steps precede the evaluation of the SELECT expressions.
>
> This isn't just academic nit-picking either, because the SELECT
> expressions might not be valid for rows that don't pass WHERE etc.
> Consider
>         SELECT 1/x AS inverse FROM data WHERE x <> 0;
> The implementation *must* apply WHERE before computing the SELECT
> expressions, or it'll get zero-divide failures that should not happen.
>
> Now, having said all that, if you try it you'll find that Postgres
> does allow select column references in GROUP BY, using the model
> you propose above of copying whatever expression is in SELECT into
> GROUP BY.  This is, to put it politely, a mistake that we are now
> stuck with for backwards-compatibility reasons.  It's not spec compliant
> and it doesn't fit the language's conceptual model, but it's been that
> way for long enough that we're not likely to take it out.  We are not,
> however, gonna introduce the same mistake elsewhere.
>
>> Obviously, it would add complexity (and compile overhead) but would be
>> somewhat handy to avoid repeating really complicated expressions.
>> Perhaps Common Table Expressions are a better way of doing this thing
>> anyhow.
>
> CTEs or sub-selects are a better answer for that.  Each sub-select has
> its own instance of the conceptual pipeline.

Excellent information, Tom!  I've been somewhat curious on this
behavior for some time now, and it's great to get a detailed answer..

Mike

On 09/18/2012 07:32 AM, Tom Lane wrote:
> It's easier to understand why this is if you realize that SQL has a very
> clear model of a "pipeline" of query execution.

I just wish they hadn't written it backwards!

It'd be much less confusing were it formulated as something like:

SELECT
FROM thetable
WHERE first_letter > 'a'
RESULTS left(value,1) AS first_letter

or something, where the order is more obvious. I really dislike the way
SQL is written not-quite-backwards.

--
Craig Ringer

On Mon, 2012-09-17 at 16:44 -0700, Mike Christensen wrote:
> On Mon, Sep 17, 2012 at 4:32 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Mike Christensen <mike@kitchenpc.com> writes:

[-----------]

> > It's easier to understand why this is if you realize that SQL has a very
> > clear model of a "pipeline" of query execution.  Conceptually, what
> > happens is:
> >
> > 1. Form the cartesian product of the tables listed in FROM (ie, all
> > combinations of rows).
> >
> > 2. Apply the WHERE condition to each row from 1, and drop rows that
> > don't pass it.
> >
> > 3. If there's a GROUP BY, merge the surviving rows into groups.
> >
> > 4. If there's aggregate functions, compute those over the rows in
> > each group.
> >
> > 5. If there's a HAVING, filter the grouped rows according to that.
> >
> > 6. Evaluate the SELECT expressions for each remaining row.
> >
> > 7. If there's an ORDER BY, evaluate those expressions and sort the
> > remaining rows accordingly.
> >
> > (Obviously, implementations try to improve on this - you don't want
> > to actually form the cartesian product - but that's the conceptual
> > model.)
> >
> > The traditional shortcut of doing "ORDER BY select-column-reference"
> > is okay according to this world view, because the SELECT expressions

[--------------]

> > are already available when ORDER BY needs them.  However, it's not
> > sensible to refer to SELECT outputs in WHERE, HAVING, or GROUP BY,
> > because those steps precede the evaluation of the SELECT expressions.
> >
> > This isn't just academic nit-picking either, because the SELECT
> > expressions might not be valid for rows that don't pass WHERE etc.
> > Consider
> >         SELECT 1/x AS inverse FROM data WHERE x <> 0;
> > The implementation *must* apply WHERE before computing the SELECT
> > expressions, or it'll get zero-divide failures that should not happen.

[-----------------]

>
> Excellent information, Tom!  I've been somewhat curious on this
> behavior for some time now, and it's great to get a detailed answer..
>

Yes.

But it puzzles me, if it *conceptually* would be a signifficant
misstake, when what Tom calls "select-column-reference" (I understand
as: the colunm name introduced on the select-list), would actually be
regarded by the SQL parser as "macro-definition". Just to place the
*string*, defined at select-list-level by "AS <name>", to wherever it's
used in the WHERE/ORDER/GROUP or HAVING clauses at earlier then
select-list-evaluation processing stage.

Actual Tom's example(1):
    SELECT 1/x AS inverse FROM data WHERE x <> 0;
extended to (2):
    SELECT 1/x AS inverse FROM data WHERE x <> 0 AND 1/x > 20;
could be written by user as (3):
    SELECT 1/x AS inverse FROM data WHERE x <> 0 AND inverse > 20;
but token/replaced to its form (2) before WHERE evaluation.


-R

On Tue, Sep 18, 2012 at 4:44 PM, Craig Ringer <ringerc@ringerc.id.au> wrote:
> On 09/18/2012 07:32 AM, Tom Lane wrote:
>>
>> It's easier to understand why this is if you realize that SQL has a very
>> clear model of a "pipeline" of query execution.
>
> I just wish they hadn't written it backwards!
>
> It'd be much less confusing were it formulated as something like:
>
> SELECT
> FROM thetable
> WHERE first_letter > 'a'
> RESULTS left(value,1) AS first_letter
>
> or something, where the order is more obvious. I really dislike the way SQL
> is written not-quite-backwards.

I agree, SQL has its quirks. There are general principles, but they're
all violated somewhere... "UNION ALL" annoys me some by breaking the
rule that more words = more work, which is almost universally adhered
to elsewhere. But such is the nature of specifications.

ChrisA

>>>>> "Craig" == Craig Ringer <ringerc@ringerc.id.au> writes:

    Craig> I just wish they hadn't written it backwards!

    Craig> It'd be much less confusing were it formulated as something
    Craig> like:

    Craig> SELECT FROM thetable WHERE first_letter > 'a' RESULTS
    Craig> left(value,1) AS first_letter

    Craig> or something, where the order is more obvious. I really
    Craig> dislike the way SQL is written not-quite-backwards.

It's not "written backwards", it's plain natural language semantics:
"give me the first letter of all records where the first letter is
greater than a". Refining a set is better done walking from the more
general set to a subset, not the other way around, IMO: "give me all
persons that are females and over the age of 20". Mathematical set
builder notation does this in a similar fashion, for the same reason.

--
Eden Cardim
http://insoli.de

On Tue, Sep 18, 2012 at 2:53 AM, Eden Cardim <eden@insoli.de> wrote:
>>>>>> "Craig" == Craig Ringer <ringerc@ringerc.id.au> writes:
>
>     Craig> I just wish they hadn't written it backwards!
>
>     Craig> It'd be much less confusing were it formulated as something
>     Craig> like:
>
>     Craig> SELECT FROM thetable WHERE first_letter > 'a' RESULTS
>     Craig> left(value,1) AS first_letter
>
>     Craig> or something, where the order is more obvious. I really
>     Craig> dislike the way SQL is written not-quite-backwards.
>
> It's not "written backwards", it's plain natural language semantics:
> "give me the first letter of all records where the first letter is
> greater than a". Refining a set is better done walking from the more
> general set to a subset, not the other way around, IMO: "give me all
> persons that are females and over the age of 20". Mathematical set
> builder notation does this in a similar fashion, for the same reason.

Oh no, this debate again..

I do admit LINQ kind of threw me for a loop as they took the other
approach (from f in foo where f.id > 5 select f), which makes you
think about the collection you're working with first.  I usually think
about the table first when I'm writing a query.

I can also say if the table came before the columns, we'd probably
have a lot more SQL editors with auto-complete that worked :)

Mike

On Tue, Sep 18, 2012 at 11:20 AM, Eden Cardim <eden@insoli.de> wrote:
>>>>>> "Mike" == Mike Christensen <mike@kitchenpc.com> writes:
>
>     Mike> I can also say if the table came before the columns, we'd
>     Mike> probably have a lot more SQL editors with auto-complete that
>     Mike> worked :)
>
> There's nothing stopping an editor from making you type the table
> first though, it's easier to implement that in an editor than it is to
> change the way people have been thinking about math for the last few
> of centuries.

No, I meant editors that auto-complete SQL statements for you as
you're typing them.  Like Intellisense in Visual Studio.

Obviously you wouldn't want to type "select " and then see a list of
every column in your database, since it doesn't know what table you
want yet.

Mike

>>>>> "Mike" == Mike Christensen <mike@kitchenpc.com> writes:

    Mike> I can also say if the table came before the columns, we'd
    Mike> probably have a lot more SQL editors with auto-complete that
    Mike> worked :)

There's nothing stopping an editor from making you type the table
first though, it's easier to implement that in an editor than it is to
change the way people have been thinking about math for the last few
of centuries.

>>>>> "Mike" == Mike Christensen <mike@kitchenpc.com> writes:
    Mike> No, I meant editors that auto-complete SQL statements for
    Mike> you as you're typing them.  Like Intellisense in Visual
    Mike> Studio.

    Mike> Obviously you wouldn't want to type "select " and then see a
    Mike> list of every column in your database, since it doesn't know
    Mike> what table you want yet.

Again, you can type "select" and the have the editor expand the whole
thing into "select _ from [cursor is now here]" and present you with a
list of tables for the autocomplete, then after you've selected the
table, it goes back to the field part of the query. I have emacs
rigged to do this with yasnippet, except that it relies on a
handwritten function, not intellisense. Point stands, easier done in a
single editor as opposed to changing the semantics of math.

Hi There,

I've snipped a piece for the daily digest because I take issue with what's asserted here as a reason for not allowing
aliasesin where clauses. 

<< snip    This isn't just academic nit-picking either, because the SELECT
expressions might not be valid for rows that don't pass WHERE etc.
Consider
     SELECT 1/x AS inverse FROM data WHERE x <> 0;
The implementation *must* apply WHERE before computing the SELECT
expressions, or it'll get zero-divide failures that should not happen.     end snip>>

Irrespective of whether the standard prohibits aliases in where clauses, the reasoning here is irrelevant to the
discussionat hand. 

If I say:
     SELECT 1/x AS inverse FROM data WHERE x <> 0 or inverse > 0.5 (for arguments sake) (in a SQL dialect that supports
it)
then I must expect inverse to be evaluated for every row, exactly as if I said:
     SELECT 1/x AS inverse FROM data WHERE x <> 0 or 1/x > 0.5

It's surely not the role of the standard to protect us from the consequences of our own folly.

Since some dialects support the idiom and others don't there can't be any compelling reason to withhold support. It's
reallya matter of style. 
For my money the DRY style is better.

--
Steve Haresnape
Creative Integrity Ltd

On 19/09/2012 04:57, Chris Travers wrote:
>
> Natural language semantics will get you into trouble though.  After all,
> I think Lisp follows natural language semantics remarkably closely if
> your natural language is Irish Gaelic....

Really? I haven't used Irish seriously since I left school - maybe I
should learn Lisp.... :-)

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On 09/19/2012 02:36 PM, Steve Haresnape wrote:

> Since some dialects support the idiom and others don't there can't be
> any compelling reason to withhold support. It's really a matter of style.
> For my money the DRY style is better.

So you're saying that you want the implementation to effectively clone
the aliased SELECT term into the WHERE clause?

If so, what about functions with side-effects?

--
Craig Ringer

On Wed, 2012-09-19 at 20:36 +0800, Craig Ringer wrote:
> On 09/19/2012 02:36 PM, Steve Haresnape wrote:
>
> > Since some dialects support the idiom and others don't there can't be
> > any compelling reason to withhold support. It's really a matter of style.
> > For my money the DRY style is better.
>
> So you're saying that you want the implementation to effectively clone
> the aliased SELECT term into the WHERE clause?
>
> If so, what about functions with side-effects?

What about them: if they are put in the where clause by the user - e.g.
not from unaliasing?

Just bug-traceing will be more difficult from alias obfuscating effects.
That's all.

-R

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Steve Haresnape
> Sent: Wednesday, September 19, 2012 2:37 AM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] RFE: Column aliases in WHERE clauses
>
> Hi There,
>
> I've snipped a piece for the daily digest because I take issue with what's
> asserted here as a reason for not allowing aliases in where clauses.
>
> << snip    This isn't just academic nit-picking either, because the SELECT
> expressions might not be valid for rows that don't pass WHERE etc.
> Consider
>      SELECT 1/x AS inverse FROM data WHERE x <> 0; The implementation
> *must* apply WHERE before computing the SELECT
> expressions, or it'll get zero-divide failures that should not happen.
end
> snip>>
>
> Irrespective of whether the standard prohibits aliases in where clauses,
the
> reasoning here is irrelevant to the discussion at hand.
>
> If I say:
>      SELECT 1/x AS inverse FROM data WHERE x <> 0 or inverse > 0.5 (for
> arguments sake) (in a SQL dialect that supports it) then I must expect
inverse
> to be evaluated for every row, exactly as if I said:
>      SELECT 1/x AS inverse FROM data WHERE x <> 0 or 1/x > 0.5
>
> It's surely not the role of the standard to protect us from the
consequences
> of our own folly.
>
> Since some dialects support the idiom and others don't there can't be any
> compelling reason to withhold support. It's really a matter of style.
> For my money the DRY style is better.
>

There are any number of idioms that other dialects (and even PostgreSQL)
support for compatibility or standards reasons that, if decided upon now,
would not be included.

The SQL execution model is, from what I can infer, single-pass and linear
and while necessitating sometimes verbose syntax it makes execution
considerably less-problematic and more deterministic by the simple fact that
column names are more narrowly scoped.  While I can and have seen situations
where such a feature would be handy working around it is not that difficult.
For really complex expressions coding the formula into a (ideally immutable)
function is a better solution anyway.

As to Tom's example its main implication is that the WHERE-clause has to be
evaluated before the SELECT-list in the single-pass linear model.  That is
quite relevant if not the MAIN point of the example.

I could maybe see something like the following having some value:

SELECT inverse
FROM data
WHERE x<>0 AND inverse > .5
MACRO inverse (1/x)

Apart from all this I'll simply say that because the feature itself has
value it is the means of implementation that needs to be discussed and not
the merits of the feature itself.  Since no new capabilities are being
added, just verbosity reduction, the hurdle to spend development time on
this is pretty darn high.  To phrase it differently I do not believe that
the core team would outright reject the idea of making aliases work IF they
were presented with a suitable implementation; it just sounds like they are
"withholding support" to the extent that they have not been convinced to do
the work themselves.  As an outsider I can understand, and in this case
agree with, that position.

David J.

On Wed, Sep 19, 2012 at 11:15 PM, David Johnston <polobo@yahoo.com> wrote:
> I could maybe see something like the following having some value:
>
> SELECT inverse
> FROM data
> WHERE x<>0 AND inverse > .5
> MACRO inverse (1/x)
>

WITH macros AS (SELECT *,1/x AS inverse FROM data) SELECT inverse FROM
macros WHERE x<>0 AND inverse > .5

ChrisA

>
> On Wed, Sep 19, 2012 at 11:15 PM, David Johnston <polobo@yahoo.com>
> wrote:
> > I could maybe see something like the following having some value:
> >
> > SELECT inverse
> > FROM data
> > WHERE x<>0 AND inverse > .5
> > MACRO inverse (1/x)
> >
>
> WITH macros AS (SELECT *,1/x AS inverse FROM data) SELECT inverse FROM
> macros WHERE x<>0 AND inverse > .5
>

In your example the "macro" has to either be attached directly to the FROM
or be used as part of a sub-select; it is not a text substitution macro at
all.  The pre-processor upon encountering a macro, would simply replace all
identifiers (at the same level in the query) with "(expression)".

David J.

On 2012-09-18, Rafal Pietrak <rafal@zorro.isa-geek.com> wrote:
>
> Actual Tom's example(1):
>     SELECT 1/x AS inverse FROM data WHERE x <> 0;
> extended to (2):
>     SELECT 1/x AS inverse FROM data WHERE x <> 0 AND 1/x > 20;
> could be written by user as (3):
>     SELECT 1/x AS inverse FROM data WHERE x <> 0 AND inverse > 20;
> but token/replaced to its form (2) before WHERE evaluation.

Macros are confusing:

 select random()*10 as confusion from generate_series(1,10)
 where confusion > 5;


--
⚂⚃ 100% natural

On Sat, 2012-09-22 at 20:00 -0700, Chris Travers wrote:
> On Sat, Sep 22, 2012 at 12:23 AM, Jasen Betts <jasen@xnet.co.nz>
> wrote:
>         On 2012-09-18, Rafal Pietrak <rafal@zorro.isa-geek.com> wrote:

[-------------]

>         > could be written by user as (3):
>         >       SELECT 1/x AS inverse FROM data WHERE x <> 0 AND
>         inverse > 20;
>         > but token/replaced to its form (2) before WHERE evaluation.
>
>
>         Macros are confusing:
>
>          select random()*10 as confusion from generate_series(1,10)
>          where confusion > 5;

No dought about that.

And as I really cannot tell you if such processing-alias-as-macro (if
available) would make me more error prone or not; I deffinitly know,
that I often "upsss.." and rewrite an item from SELECT list into the
WHERE clause - because as a common sql-user I do forget such nuances.
Learning (for good :), that the ".... as <name>" is *not* a "definition
of a logical/local short-name for an expression" (e.g. it is, but only
within the context of SQL statement evaluation sequence) is really
counterintuitive for an sql-user like myself.
>
> Also you can already do this:
>
>
> CREATE FUNCTION inverse(data) RETURNS NUMERIC LANGUAGE SQL IMMUTABLE
> AS $$
>     select case when $1.x = 0 then null else 1/$1.x end;
> $$;
>
Hmmm, well. No. This is an overkill to a problem. I'd rather stay with
SELECT list item copyed by hand into the WHERE clauses.

-R