CODEWITHSUNDEEP
postgresqldynamic-sql
Peter
Peter

Reputation: 914

PostgreSQL: store function in column as value

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.

Upvotes: 7

Views: 3548

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657847

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:

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.

Upvotes: 9

Related Questions