Dynamic function invocation in trigger

Question

In my use case I need to be able to execute a dynamic (predefined) function with static arguments after insertion into a table.

Logically I'm thinking:

Define function calls (i.e. name and static args)in their own table.
Associate these static function call definitions with records in another table (insertion into which will trigger the dynamic function call).
Upon insertion use a trigger to query the static function definition table and execute the obtained function with the obtained static args.

Here is what I've arrived at so far:

Available pool of functions to be called dynamically

create function f1(num int) returns boolean as $$
  -- ...
$$ language plpgsql;

create function f2(name text, age int) returns boolean as $$
  -- ...
$$ language plpgsql;

create function f3(first_name text, last_name text) returns boolean as $$
  -- ...
$$ language plpgsql;

Function invocations

create table function_invocations(
  id integer not null,
  name text not null,
  args text not null, -- (not sure if this should be an array)
  primary key(id)
);

create function verify_function_exists() returns trigger as $$
  -- query information_schema to verify there is
  -- a function with specified name and that
  -- specified args satisfy function's
  -- signature.
$$ language plpgsql;

create trigger function_exists_trig
  before insert on function_invocations
  for each row
  execute procedure verify_function_exists();

Table whose inserts result in dynamic function call

create table my_data(
  id integer not null,
  function_invocation_id integer not null,
  -- etc.
  primary key(id),
  foreign key(function_invocation_id) references function_invocations(id)
);

create function exec_dynamic_function() returns trigger as $$
  -- retrieve the function name and args from
  -- function_definitions and execute the
  -- function specified by `name` with the
  -- provided `args`.  
$$ language plpgsql;

create trigger function_invocations_trig
  after update on my_data
  for each row
  execute procedure exec_dynamic_function();

Is this the right way about going about the task? Coming from a JS background I may well be thinking of it the wrong way, i.e.

var api = {
  my_func: function (age, name) {
    console.log('%d %s', age, name);
  }
};

var fn = 'my_func';
var args = [50, 'john'];

api[fn].apply(api, args);

My main concern is how to ensure that the functions referenced by rows in the function_invocations table actually exist and the args defined are valid (or can at least be coerced into being valid).

I'm using PostgreSQL 9.4.1.

As *always* please provide your version of Postgres. – Erwin Brandstetter Mar 11 '15 at 05:30 — Erwin Brandstetter, Mar 11 '15 at 05:30
Sorry, my mistake- have added. – jabclab Mar 11 '15 at 09:24 — jabclab, Mar 11 '15 at 09:24

score 1 · Accepted Answer · edited May 23 '17 at 12:12

Here is solution with a simple CHECK constraint instead of a trigger:

CREATE TABLE func (
  func_id serial PRIMARY KEY
, func text NOT NULL
, args text NOT NULL
, CHECK ((func || '(' || args || ')')::regprocedure IS NOT NULL)
);

The CHECK constraint is simpler, faster and more reliable than any possible trigger solution. This variant works in any modern Postgres version.

The cast to regprocedure fails for invalid functions signatues before the constraint can finish evaluating - which is just as reliable. This is reflected in the respective error message.

In Postgres 9.4+ rather use the new to_regprocedure() instead of the cast, which does not raise an exception. You get an exception from the CHECK constraint instead. More (last chapter):

Works:

INSERT INTO func(func, args) VALUES ('substring','text, int');

Fails with an exception:

INSERT INTO func(func, args) VALUES ('nonexistant','text, int');

SQL Fiddle.

I would also consider a UNIQUE constraint on (func, args). Be aware that there can be multiple valid text representations for the same args. Here is a quick check to discover hidden duplicates:

SELECT func, string_to_array(args, ', ')::regtype[], count(*)
FROM   func
GROUP  BY 1, 2
HAVING count(*) > 1;

You can't use this expression in a unique index because the cast to regtype is not IMMUTABLE. You would have to play tricks ...

Dynamic function invocation in trigger

Available pool of functions to be called dynamically

Function invocations

Table whose inserts result in dynamic function call

1 Answers1