On Mon, Sep 17, 2012 at 3:10 PM, Daniel Serodio (lists) <daniel.lists@xxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
