On Sat, Sep 22, 2012 at 12:23 AM, Jasen Betts <jasen@xxxxxxxxxx> wrote:
On 2012-09-18, Rafal Pietrak <rafal@xxxxxxxxxxxxxxxxxx> wrote:Macros are confusing:
>
> 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.
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
