Store a formula in a table and use the formula in a function

Question

I have a PostgreSQL 9.1 database where part of it handles agent commissions. Each agent has his/her own formula of calculation how much commission they get. I have a function to generate the amount of commission each agent should get, but it's becoming impossible to use as the number of agents grow. Am forced to do some extremely long case statements and repeating code, which has made my function very big.

All the formulas have constant variables:

d .. days worked that month
r .. new nodes accuired
l .. loyalty score
s .. subagent commission
b .. base rate
i .. revenue gained

The formula can be something like:

d*b+(l*4+r)+(i/d)+s

Each agent negotiates the payment formula with the HR dept. So can I store the formula in the agents table then have like a small function that just gets the formula from the table and translates it with values and computes the amount?

Answer 1

Prepare

Your formulas look like this:

d*b+(l*4+r)+(i/d)+s

I would replace the variables with $n notation so they can be replaced with values directly in plpgsql EXECUTE (see below):

$1*$5+($3*4+$2)+($6/$1)+$4

You can store your original formulas additionally (for the human eye) or generate this form dynamically with an expression like:

SELECT regexp_replace(regexp_replace(regexp_replace(
       regexp_replace(regexp_replace(regexp_replace(
      'd*b+(l*4+r)+(i/d)+s'
      , '\md\M', '$1', 'g')
      , '\mr\M', '$2', 'g')
      , '\ml\M', '$3', 'g')
      , '\ms\M', '$4', 'g')
      , '\mb\M', '$5', 'g')
      , '\mi\M', '$6', 'g');

Just make sure, you translation is sound. Some explanation for the regexp expressions:

\m .. matches only at the beginning of a word
\M .. matches only at the end of a word

4th parameter 'g' .. replace globally

Core function

CREATE OR REPLACE FUNCTION f_calc(
    d int         --  days worked that month
   ,r int         --  new nodes accuired
   ,l int         --  loyalty score
   ,s numeric     --  subagent commission
   ,b numeric     --  base rate
   ,i numeric     --  revenue gained
   ,formula text
   ,OUT result numeric
)  RETURNS numeric AS
$func$
BEGIN    
   EXECUTE 'SELECT '|| formula
   INTO   result
   USING  $1, $2, $3, $4, $5, $6;                                          
END
$func$ LANGUAGE plpgsql SECURITY DEFINER IMMUTABLE; 

Call:

SELECT f_calc(1, 2, 3, 4.1, 5.2, 6.3, '$1*$5+($3*4+$2)+($6/$1)+$4');

Returns:

29.6000000000000000

Major points

• The function takes 6 value parameter and formula text as 7th. I put the formula last, so we can use $1 .. $6 instead of $2 .. $7. Just for the sake of readability.
  I assigned data types for the values as I saw fit. Assign proper types (to implement basic sanity checks) or just make them all numeric:

• Pass in values for dynamic execution with the USING clause. This avoids casting back and forth and makes everything simpler, safer and faster.

• I use an OUT parameter because that's more elegant and makes for shorter clearer syntax. A final RETURN is not needed, the value of the OUT parameter(s) are returned automatically.

• Consider the lecture on security by @Chris and the chapter "Writing SECURITY DEFINER Functions Safely" in the manual. In my design, the single point of injection is the formula itself.

• You could use defaults for some parameters to further simplify the call.

Answer 2

Please read this carefully regarding security considerations. Essentially you are trying to inject arbitrary SQL in your functions. Consequently you need to have this run under a user with highly restricted permissions.

1. Create a user and revoke all permissions from it. Do not grant permissions to public in the same db as you do this.

2. Create a function to evaluate the expression, make it security definer and alter the owner to that restricted user.

3. Preprocess the expression and then pass it to the eval() function you created above. You can do this in another function if you need to,

Note again, this has serious security implications.

Edit: Brief sample code (untested but should get you there if you follow docs):

CREATE OR REPLACE FUNCTION eval_numeric(text) returns numeric language plpgsql security definer immutable as
$$
declare retval numeric;
begin

execute $e$ SELECT ($1)::numeric$e$ into retval;
return retval;
end;
$$;

ALTER FUNCTION eval_numeric OWNER TO jailed_user;

CREATE OR REPLACE FUNCTION foo(expression text, a numeric, b numeric) returns numeric language sql immutable as $$
select eval(regexp_replace(regexp_replace($1, 'a', $2, 'g'), 'b', '$3', 'g'));
$$; -- can be security invoker, but eval needs to be jailed.

Answer 3

An alternative to just storing the formula and then executing it (which as Chris mentioned, has security problems) Would be to have a separate table called formula_steps which basically would contain the variables and operators and the sequence in which they are executed. This would be a bit more work, but would be more secure. The table might look like this:

formula_steps
-------------
  formula_step_id
  formula_id (FK, referenced by the agents table)
  input_1
  input_2
  operator (could also be an ID to a table of allowed operators, if you don't want to store operator symbols directly)
  sequence

---

Another option would be to use some 3rd-party library/tool to evaluate mathematical expressions. This would make your database less vulnerable to SQL injection, but now you've just shifted the possible security problems to your external tool (which still might be pretty safe).

---

The final option would be to write (or download) a procedure that evaluates mathematical expressions. There are known algorithms for this problem, so it shouldn't be hard to find info online.