PostgreSQL: store function in column as value

Question

Can functions be stored as anonymous functions directly in column as its value?

Let's say I want this function be stored in column. Example (pseudocode):

Table my_table: pk (int), my_function (func)

func ( x ) { return x * 100 }

And later use it as:

select 
    t.my_function(some_input) AS output
from 
    my_table as t 
where t.pk = 1999

Function may vary for each pk.

Typical

Presumably you mean my_function() or some_actual_schema.my_function() instead of t.my_function(), since t is a table alias. As long as we're not trying that bit of insanity, then as long as the function outputs a single non-set, non-record value (ie it doesn't output a row or a table), then yes, it should be fine. – Isolationist

No, it sounds like t.my_function() is EXACTLY what's he's asking for. Basically, the DB equivalent of a function pointer or clousure. – Prakash

Yes, I meant something like closure. – Typical

Answer 1

Your title asks something else than your example.

1. A function has to be created before you can call it. (title)
2. An expression has to be evaluated. You would need a meta-function for that. (example)

Here are solutions for both:

1. Evaluate expressions dynamically

You have to take into account that the resulting type can vary. I use polymorphic types for that.

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$;

Related:

• Refactor a PL/pgSQL function to return the output of various SELECT queries

Call:

SQL is strictly typed, the same result column can only have one data type. For multiple rows with possibly heterogeneous data types, you might settle for type text, as every data type can be cast to and from text:

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

Or return multplce columns like:

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;

db<>fiddle here

2. Create and use functions dynamically

It is possible to create functions dynamically and then use them. You cannot do that with plain SQL, however. You will have to use another function to do that or at least an anonymous code block (DO statement), introduced in PostgreSQL 9.0.

It can work like this:

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$;

Call:

SELECT f_create_func('f');
SELECT f(3);

db<>fiddle here

You may want to drop the function afterwards.

In most cases you should just create the functions instead and be done with it. Use separate schemas if you have problems with multiple versions or privileges.

For more information on the features I used here, see my related answer on dba.stackexchange.com.

Secunda

Thank you Erwin for this example. I suspected that what I'm asking might not work, so this or something like this, was plan B. – Typical

How are you assigning the return datatype in x.f_eval? I see you pass _type anyelement as the second argument; however, I don't see how it is being used in the function definition. – Bleier

disregard the above comment... details found at #11740756 – Bleier

I stumbled across this old answer and felt it was incomplete. You might still be interested in the update. – Secunda