Thread: Dynamic expression evaluation

Hello,

Imagine we have the following kind of table, with two values (a and b), and a varchar (f) representing an expression.

----------------------------------
CREATE TABLE public.test
(
  id serial NOT NULL,
  a int4,
  b int4,
  f varchar(50),
  CONSTRAINT id PRIMARY KEY (id)
) WITHOUT OIDS;

INSERT INTO public.test(a,b,f) VALUES(2,3,'a+b');
INSERT INTO public.test(a,b,f) VALUES(12,3,'a*b');
INSERT INTO public.test(a,b,f) VALUES(5,6,'a+2*b');
----------------------------------

Is there a simple way of doing "kind of" a

SELECT *, EVAL(f) FROM public.test;

... and having f evaluated as an expression, so that we get back:

------------------------------
id   a     b    f        eval
------------------------------
1    2     3    a+b      5
2    12    3    a*b      36
3    5     6    a+2*b    17
------------------------------


Has anyone done anything like that already?

Thanks!

Philippe

"Philippe Lang" <philippe.lang@attiksystem.ch> writes:
> Is there a simple way of doing "kind of" a
> SELECT *, EVAL(f) FROM public.test;

> ... and having f evaluated as an expression, so that we get back:

> ------------------------------
> id   a     b    f        eval
> ------------------------------
> 1    2     3    a+b      5
> 2    12    3    a*b      36
> 3    5     6    a+2*b    17
> ------------------------------

Not really.  You can sort of approximate eval() with plpgsql's EXECUTE:

regression=# create or replace function eval(text) returns int as '
regression'# declare res record;
regression'# begin
regression'# for res in execute ''select '' || $1 || '' as result'' loop
regression'#   return res.result;
regression'# end loop;
regression'# end' language plpgsql;
CREATE FUNCTION
regression=#  select eval ('23+34');
 eval
------
   57
(1 row)

regression=#

but this has a problem with supporting more than one result type (hmm,
maybe you could fake that with 7.4's polymorphism?).  And I don't see
any way at all for the function to have access to the other values in
the row, as your example presumes it would do.

            regards, tom lane