Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers

up vote 3 down vote accepted

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.

share|improve this answer
    
thanks alot,your answer worked perfectly! –  indago Feb 4 '13 at 6:54
add comment

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.
share|improve this answer
    
"make it security definer" is really confusing, can you explain? –  jcolebrand Jan 31 '13 at 15:21
1  
PostgreSQL has two security modes that a function can run as. SECURITY INVOKER is the default. SECURITY DEFINER means "run with the security context of the owner of the function" sort of like the SETUID bit on *nix. To make a function security definer you can specify this in the function declaration (CREATE FUNCTION foo(text) returns text IMMUTABLE LANGUAGE SQL SECURITY DEFINER AS $$...) or you can ALTER FUNCTION foo(text) SECURITY DEFINER –  Chris Travers Feb 1 '13 at 1:00
    
Oh, so that's specific PG lino. Gotcha. Shoulda used backticks in the Answer ;-) –  jcolebrand Feb 1 '13 at 2:03
    
@ChrisTravers i was expecting some sample code to evaluate a formula i.e a+b is stored in a text type column in a table then i have a function foo(a int, b int,formula text) if it gets the formula is a+b how can i make the function actually do a+b instead of me having to have a very long case statement for all the possible formulas and repeating the code in all the segments? –  indago Feb 1 '13 at 6:20
1  
@indago, I think you want to break this off into two layers because of security concerns. The first is an interpolation layer. You can use regexes in PostgreSQL to do this. In the lower level, you basically are running this in a jailed SQL function. You really need to pay very close attention to security if you are going to do this though, and you have to also pay close attention to return values. Without knowing a lot more, it is hard to do much with samople code but will amend the answer. –  Chris Travers Feb 1 '13 at 6:40
show 2 more comments

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.

share|improve this answer
1  
+1 for the third option. If all of the potential inputs are known, hard code a select of each of the inputs and substitute them (if and as necessary) into the formula stored as text, then use a library routine to evaluate the arithmetic. SQL injection risk eliminated. –  Joel Brown Feb 3 '13 at 12:54
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.