Re: RFE: Column aliases in WHERE clauses

On Sat, Sep 22, 2012 at 12:23 AM, Jasen Betts <jasen(at)xnet(dot)co(dot)nz> wrote:

> On 2012-09-18, Rafal Pietrak <rafal(at)zorro(dot)isa-geek(dot)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;
>
> 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;
$$;

Then it can be used as a macro:

SELECT d.inverse FROM data d WHERE d.x <> 0 AND d.inverse > 0.5;

Wondering if we want to support something like this, essentially anonymous
functions, if we shouldn't extend the WITH clause to support something like
WITH FUNCTION for cases where you don't want your macro to persist.

I don't know though. Are there cases where you don't want the macro to
persist?

Best Wishes,
Chris Travers