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