eval function - Mailing list pgsql-general

From Sim Zacks
Subject eval function
Date
Msg-id 4E314577.8020208@compulab.co.il
Whole thread Raw
Responses Re: eval function  (Merlin Moncure <mmoncure@gmail.com>)
Re: eval function  (Chris Travers <chris.travers@gmail.com>)
List pgsql-general
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;




pgsql-general by date:

Previous
From: Samba
Date:
Subject: Statistics about Streaming Replication deployments in production
Next
From: tommaso
Date:
Subject: postgres unable to start