From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Sim Zacks <sim(at)compulab(dot)co(dot)il> |
Cc: | PostgreSQL general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: eval function |
Date: | 2011-07-28 14:03:26 |
Message-ID: | CAHyXU0wWFbjAE61AMUgDdynSu7urd1T8BO95F5kOc-JErP5Nng@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Jul 28, 2011 at 6:18 AM, Sim Zacks <sim(at)compulab(dot)co(dot)il> wrote:
> I need an eval function that will evaluate a valid SQL expression and return
> the value.
>
> I've seen variations of this asked before with no real answer.
>
> I wrote a function to handle it, but it looks like there should be a better
> way to do this (see below).
>
> My use case is a table with date range definitions that should be evaluated
> dynamically:
>
>
> For example:
>
> Period DateFrom
> DateTo
>
> Last Month $expr$current_date-interval '1 month'$expr$
> $expr$current_date$expr$
>
>
> ...
>
> select datefrom,dateto into v_datefrom, v_dateto from movingperiods where
> period='Last Month';
>
> select * from sales where orderdate between eval(v_datefrom)::date and
> eval(v_dateto)
>
> ...
>
>
>
> CREATE OR REPLACE FUNCTION eval(str text)
> RETURNS text AS
> $BODY$
> declare
> row record;
> res text;
> begin
> if lower(str) in ('true','false') then
> res:=str;
> else
> for row in execute 'select (' || str || ')::text as res1' loop
> res:=row.res1;
> end loop;
> end if;
> return res;
> end;
> $BODY$
> LANGUAGE plpgsql VOLATILE;
Couple points:
*) why a special case for boolean values?
*) this should be immutable
*) why have a loop? old version of postgres maybe? EXECUTE INTO...
merlin
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2011-07-28 14:11:04 | Re: eval function |
Previous Message | Pavel Stehule | 2011-07-28 13:50:01 | Re: List Functions and Code |