Re: eval function

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

In response to

Responses

Browse pgsql-general by date

  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