database - 如何使用自定义函数作为 PostgreSQL 表中的列默认值

Question

我创建了一个名为 Loan 的表，其中包含以下列:

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

monthlyPayment 的计算取决于 numberOfYears、loanAmount 和 annualInterestRate，按照以下公式:

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

我创建了一个名为 get_monthly_payment() 的函数，它可以毫无问题地返回 monthlyPayment。对于每一行，我想让此函数的返回值成为 monthlyPayment 列的默认值。我怎样才能做到这一点？

Answer 1

如果 monthlyPayment 按照您的定义是固定的，因此在功能上完全依赖，那么请考虑完全 不保留该值。继续使用廉价的 (!) 函数。整体更清洁，更便宜。喜欢:

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

假设函数定义为 f_monthly_payment(loan)(以表的行类型为参数)。否则相应调整。

Postgres 12 或更高版本具有STORED 生成的列，但那些只支付昂贵的计算费用。持久化列占用空间并减慢表上的所有操作。

参见:

• Computed / calculated / virtual / derived columns in PostgreSQL

如果您想要允许手动更改，列默认值 是可行的方法(正如您实际要求)。但是你不能使用你的函数，因为，quoting the manual :

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

剩下的解决方案是在表上添加一个触发器 BEFORE INSERT，如下所示:

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();

假设 monthlyInterestRate() 是一个自定义函数。
我用内置的 Postgres function power() 替换了 Math.pow .

NEW 是触发器函数中的一个特殊记录变量，指的是新插入的行。见:

• FOR EACH STATEMENT trigger example

EXECUTE FUNCTION 需要 Postgres 11。参见:

• Trigger function does not exist, but I am pretty sure it does

相关:

• PostgreSQL - set a default cell value according to another cell value

旁白:考虑 Postgres 中合法的、小写的、不带引号的标识符。见:

• Are PostgreSQL column names case-sensitive?