Re: RFE: Column aliases in WHERE clauses - Mailing list pgsql-general

From Rafal Pietrak
Subject Re: RFE: Column aliases in WHERE clauses
Date
Msg-id 1348406324.22293.43.camel@localhost.localdomain
Whole thread Raw
In response to Re: RFE: Column aliases in WHERE clauses  (Chris Travers <chris.travers@gmail.com>)
List pgsql-general
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




pgsql-general by date:

Previous
From: Jasen Betts
Date:
Subject: Re: Using psql -f to load a UTF8 file
Next
From: "ac@hsk.hk"
Date:
Subject: Upgrade from 8.4.13 to 9.2.0.1 successful but it still displays 8.4.13 if SELECT version();