Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

-- 1. Evaluate expressions dynamically
CREATE OR REPLACE FUNCTION f1(int)
  RETURNS int
  LANGUAGE sql IMMUTABLE AS
'SELECT $1 * 100;';

CREATE OR REPLACE FUNCTION f2(text)
  RETURNS text
  LANGUAGE sql IMMUTABLE AS
$$SELECT $1 || '_foo';$$;

CREATE TABLE my_expr (
  expr    text PRIMARY KEY
, def     text
, rettype regtype
);

INSERT INTO my_expr VALUES
  ('x', 'f1(3)'      , 'int')
, ('y', $$f2('bar')$$, 'text')
, ('z', 'now()'      , 'timestamptz')
;

CREATE OR REPLACE FUNCTION f_eval(text, _type anyelement = 'NULL'::text, OUT _result anyelement)
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE
   'SELECT ' || (SELECT def FROM my_expr WHERE expr = $1)
   INTO _result;
END
$func$;
3 rows affected

-- Get one generic result column
SELECT *, f_eval(expr) AS result  -- default to type text
FROM   my_expr;


expr
def
rettype
result


x
f1(3)
integer
300

y
f2('bar')
text
bar_foo

z
now()
timestamp with time zone
2022-06-13 15:36:19.766944+01


-- Get typed results in separate columns
SELECT *
     , CASE WHEN rettype = 'text'::regtype        THEN f_eval(expr) END                    AS text_result  -- default to type text
     , CASE WHEN rettype = 'int'::regtype         THEN f_eval(expr, NULL::int) END         AS int_result
     , CASE WHEN rettype = 'timestamptz'::regtype THEN f_eval(expr, NULL::timestamptz) END AS tstz_result
  -- , more?
FROM   my_expr;


expr
def
rettype
text_result
int_result
tstz_result


x
f1(3)
integer
null
300
null

y
f2('bar')
text
bar_foo
null
null

z
now()
timestamp with time zone
null
null
2022-06-13 15:36:19.769761+01


-- 2. Create and use functions dynamically
CREATE TABLE my_func (func text PRIMARY KEY, def text);

INSERT INTO my_func VALUES
  ('f'
 , $$CREATE OR REPLACE FUNCTION f(int)
  RETURNS int
  LANGUAGE sql IMMUTABLE AS
'SELECT $1 * 100;'$$);

CREATE OR REPLACE FUNCTION f_create_func(text)
  RETURNS void
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE (SELECT def FROM my_func WHERE func = $1);
END
$func$;
1 rows affected

SELECT f_create_func('f');


f_create_func





SELECT f(3);


f


300

expr	def	rettype	result
x	f1(3)	integer	300
y	f2('bar')	text	bar_foo
z	now()	timestamp with time zone	2022-06-13 15:36:19.766944+01

expr	def	rettype	text_result	int_result	tstz_result
x	f1(3)	integer	null	300	null
y	f2('bar')	text	bar_foo	null	null
z	now()	timestamp with time zone	null	null	2022-06-13 15:36:19.769761+01