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.

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)

I'll present 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.

-- DROP SCHEMA x CASCADE;
CREATE SCHEMA x;

CREATE OR REPLACE FUNCTION x.f1(int)
  RETURNS int AS
$$SELECT $1 * 100;$$
  LANGUAGE sql IMMUTABLE;

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

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

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

CREATE OR REPLACE FUNCTION x.f_eval(text, _type anyelement, OUT _result anyelement)
  AS
$x$
BEGIN

EXECUTE
'SELECT ' || (SELECT def FROM x.my_expr WHERE expr = $1)
INTO _result;

END;
$x$
LANGUAGE plpgsql;

Call:

SELECT x.f_eval('x', (SELECT rettype FROM x.my_expr WHERE expr = 'x'));
 f_eval
--------
 300
(1 row)

SELECT x.f_eval('y', (SELECT rettype FROM x.my_expr WHERE expr = 'y'));
 f_eval
---------
 bar_foo
(1 Zeile)

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:

-- DROP SCHEMA x CASCADE;
CREATE SCHEMA x;
CREATE TABLE x.my_func (func text PRIMARY KEY, def text);

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

CREATE OR REPLACE FUNCTION x.f_create_func(text)
RETURNS void AS $x$
BEGIN

EXECUTE (SELECT def FROM x.my_func WHERE func = $1);

END;
$x$
LANGUAGE plpgsql;

Call:

select x.f_create_func('f');
SELECT f(3);

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.