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:


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?

3 Answers 3



Your formulas look like this:


Replace the variables with $n notation so they can be interpolated with values directly in PL/pgSQL EXECUTE (see below):


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(
      , '\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

       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
   EXECUTE 'SELECT ' || formula
   INTO   result
   USING  $1, $2, $3, $4, $5, $6;                                          


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



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.


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;

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

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.
  • 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 Feb 1, 2013 at 1:00
  • @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, 2013 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. Feb 1, 2013 at 6:40
  • @ChrisTravers i have created the function CREATE FUNCTION foo(IN formula text, IN a integer, IN b integer) RETURNS numeric AS $BODY$ BEGIN select eval(regexp_replace(regexp_replace($1, 'a', $2, 'formula'), 'b', '$3', 'formula')); END; $BODY$ LANGUAGE sql IMMUTABLE; but i get the error ERROR: function regexp_replace(text, unknown, integer, unknown) does not exist LINE 4: regexp_replace($1, 'a', $2, 'formula'), what could be wrong?
    – indago
    Feb 1, 2013 at 8:30
  • @indago: The code example has a couple of flaws. Most importantly, function parameters are not visible inside dynamic SQL. I posted a version that works with Postgres 9.1. Feb 1, 2013 at 21:43

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_id (FK, referenced by the agents table)
  operator (could also be an ID to a table of allowed operators, if you don't want to store operator symbols directly)

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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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