Re: RFE: Column aliases in WHERE clauses

On Mon, Sep 17, 2012 at 3:10 PM, Daniel Serodio (lists)
<daniel(dot)lists(at)mandic(dot)com(dot)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