sql - Postgresql 函数

Question

在这个网站上我看到了一个函数，我想将它与 postgresql 一起使用:

https://raresql.com/2013/05/16/sql-server-excel-financial-functions-pmt/

这里是查询:

CREATE FUNCTION UDF_PMT
(@InterestRate  NUMERIC(18,8), --Rate is the interest rate per period.
 @Nper          INT,           --Nper is the total number of payment
                               --periods in an annuity.
 @Pv            NUMERIC(18,4), --Pv is the present value, or the
                               --lump-sum amount that a series of
                               --future payments is worth right now.
                               --If pv is omitted, it is assumed to be
                               --0 (zero). PV must be entered as a
                               --negative number.
 @Fv            NUMERIC(18,4), --Fv is the future value, or the
                               --lump-sum amount that a series of
                               --future payments is worth right now.
                               --If pv is omitted, it is assumed to
                               --be 0 (zero). PV must be entered as a
                               --negative number.
 @Type           BIT            --Type is the number 0 or 1 and
                               --indicates when payments are due.
                               --If type is omitted, it is assumed
                               --to be 0 which represents at the end
                               --of the period.
                               --If payments are due at the beginning
                               --of the period, type should be 1.
)
RETURNS NUMERIC(18,2) --float
AS
  BEGIN
    DECLARE  @Value NUMERIC(18,2)
    SELECT @Value = Case
    WHEN @Type=0
    THEN Convert(float,@InterestRate / 100)
    /(Power(Convert(float,(1 + @InterestRate / 100)),@Nper)-1)
    * -(@Pv*Power(Convert(float,(1 + @InterestRate / 100)),@Nper)
    +@Fv)

    WHEN @Type=1
    THEN Convert(float,@InterestRate / 100) /
    (Power(Convert(float,(1 + @InterestRate / 100)),@Nper)-1)
    * -(@Pv*Power(Convert(float,(1 + @InterestRate / 100)),@Nper)
    +@Fv)
    /(1 + Convert(float,(@InterestRate / 100)))

  END
    RETURN @Value
  END

我在没有 @ 的情况下重命名了变量并稍微更改了主体，但不知何故我无法让它正确运行。

是否可以为 postgresql 重写此查询？你有想法怎么做吗？谢谢

Answer 1

CREATE OR REPLACE FUNCTION UDF_PMT (
 InterestRate  NUMERIC(18,8),
 Nper          INTEGER,
 Pv            NUMERIC(18,4),
 Fv            NUMERIC(18,4),
 Typ           INTEGER
)
RETURNS NUMERIC(18,2)
AS $$
    SELECT round(
        CASE
        WHEN Typ = 0 THEN 
            (InterestRate / 100) /
            (Power(1 + InterestRate / 100, Nper) - 1) *
            (Pv * Power(1 + InterestRate / 100, Nper) + Fv)
        WHEN Typ = 1 THEN
            (InterestRate / 100) /
            (Power(1 + InterestRate / 100, Nper) - 1) *
            (Pv * Power(1 + InterestRate / 100, Nper) + Fv) /
            (1 + InterestRate / 100)
        END, 2)
$$ LANGUAGE SQL;