On Thu, Jul 28, 2011 at 6:18 AM, Sim Zacks <sim@compulab.co.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
