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:
monthlyInterestRateandMath.powdefined?Math.powI mean the power function, its just a java function.monthlyInterestRate` is a custom function. @ErwinBrandstetter