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

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?

Prepare

Your formulas look like this:

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

Replace the variables with $n notation so they can be interpolated with values directly in PL/pgSQL 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, your 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 acquired
     , l int         --  loyalty score
     , s numeric     --  subagent commission
     , b numeric     --  base rate
     , i numeric     --  revenue gained
     , formula text
     , OUT result numeric)
  RETURNS numeric
  LANGUAGE plpgsql IMMUTABLE AS
$func$
BEGIN    
   EXECUTE 'SELECT ' || formula
   INTO   result
   USING  $1, $2, $3, $4, $5, $6;                                          
END
$func$;

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, OUT parameter(s) are returned automatically.

-
Consider the lecture on security by @Chris. In my design, the single point of possible SQL injection is the formula itself. Make sure nothing malicious can be passed there.

-
If you need to run calculations with the permissions of your role, consider SECURITY DEFINER. But read the chapter "Writing SECURITY DEFINER Functions Safely" in the manual first.

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

StackExchange Database Administrators Q#33894, answer score: 7