Re: eval function

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