3

I created a table named Loan that contains the following columns:

loanID SERIAL, 
annualInterestRate INT, 
numberOfYears INT, 
loanAmount NUMERIC, 
monthlyPayment NUMERIC

The calculation of the monthlyPayment depends on numberOfYears, loanAmount, and the annualInterestRate as per the following formula:

monthlyPayment = (loanAmount * monthlyInterestRate) /
                (1 - (1/Math.pow(1 + monthlyInterestRate , numberOfYears * 12) ));

I made a function named get_monthly_payment() that returns the monthlyPayment with no problem. For each row, I want to make the return of this function the default of the column monthlyPayment. How can I achieve this?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Hussein Eid
  • 209
  • 2
  • 9

3 Answers3

1

In Postgres 12+ you have generated columns:

A generated column is a special column that is always computed from other columns.

"A generated column is a special column that is always computed from other columns. ..."

So:

monthlyPayment numeric GENERATED ALWAYS AS(loanAmount * monthlyInterestRate) /
                (1 - (1/Math.pow(1 + monthlyInterestRate , numberOfYears * 12) ))  STORED

Before version 12 you have to use CREATE TRIGGER to add a trigger to the table that calls a function that contains the above equation and sets the column value.

Adrian Klaver
  • 15,886
  • 2
  • 17
  • 28
  • This is so good. But `monthlyInterestRate` is not a column in the table, it is a custom function. Can we use a custome function in the `GENERATED ALWAYS` clause?. – Hussein Eid Jul 17 '20 at 23:17
  • A generated column is different on principal from a column with a trigger-generated default. The latter is a plain column allowing manual changes. Not possible with generated columns. See: https://stackoverflow.com/a/8250729/939860 – Erwin Brandstetter Jul 17 '20 at 23:45
  • @Hussein Eid If ```monthlyInterestRate``` is a function I'm not seeing the function call. It looks like a variable/coulmn name to me. – Adrian Klaver Jul 18 '20 at 00:17
1

If monthlyPayment is fixed as per your definition, hence completely functionally dependent, then consider not persisting the value at all. Keep using your cheap (!) function instead. Much cleaner and cheaper overall. Like:

SELECT *, f_monthly_payment(l) AS monthly_payment
FROM loan l;

Assuming the function is defined as f_monthly_payment(loan) (taking the row type of the table as argument). Else adapt accordingly.

Postgres 12 or later has STORED generated columns, but those only pay for expensive calculations. Persisted columns occupy space and slow down all operations on the table.

See:

If you want to allow manual changes, a column default is the way to go (like you actually asked). But you cannot use your function because, quoting the manual:

The DEFAULT clause assigns a default data value for the column whose column definition it appears within. The value is any variable-free expression (in particular, cross-references to other columns in the current table are not allowed).

The remaining solution for this is a trigger BEFORE INSERT on the table like:

CREATE OR REPLACE FUNCTION trg_loan_default_rate()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   NEW.monthlyPayment := (NEW.loanAmount * monthlyInterestRate())
                    / (1 - (1/power(1 + monthlyInterestRate(), NEW.numberOfYears * 12)));

   RETURN NEW;
END
$func$;

CREATE TRIGGER loan_default_rate
BEFORE INSERT ON loan
FOR EACH ROW EXECUTE FUNCTION trg_loan_default_rate();

Assuming monthlyInterestRate() is a custom function.
And I replaced Math.pow with the built-in Postgres function power().

NEW is a special record variable in trigger functions, referring to the newly inserted row. See:

EXECUTE FUNCTION requires Postgres 11. See:

Related:

Aside: consider legal, lower-case, unquoted identifiers in Postgres. See:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • It seems from your code that we do not put the `NEW` operator before the custom function in the creation of the trigger. Just put it before the attributes of the column. Is that ok?. Another question is that: If the monthlyInterestRate() has parameters, shall we use the `NEW` operator before the parameters as well?. – Hussein Eid Jul 18 '20 at 00:27
  • @HusseinEid: `NEW` is a special record variable in trigger functions, referring to the newly inserted row. I added a link above. – Erwin Brandstetter Jul 18 '20 at 00:34
0

Monthly Interest rate may not be a column on your table, but the Annual rate is. But Annual to Monthly is a simple calculation. The following can be used for both v12 generated columns and prior versions using a trigger. Additionally it provides what could be a generally usefully Postgres function for monthly payment calculations. See fiddle for each.

create or replace 
function monthly_loan_payment 
       ( amount  numeric
       , apr     numeric
       , term    numeric
       )
  returns numeric
  language sql 
  immutable strict  
/*  Given a loan amount, the Annual Percent (Interest) Rate and term (in years) 
 *  compute the monthly payment to service the loan. 
 *  Note. Monthly payment calculates correctly, but due to the exact terms of loan
 *        and date of payment receipt adjustments for end of loan payment may be 
 *        required.  
 */ 
as $$   
  with monthly (mrate) as (values ( (apr/100.00) / 12.00 ) )
  select round((amount * mrate)  /(1.0 - (1.0/ ((1.0 +mrate)^( term * 12.00)) ))::numeric,2) 
     from monthly;
$$;
Belayer
  • 13,578
  • 2
  • 11
  • 22