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


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.