PostgreSQL函数 [英] Function for Postgresql

问题描述

在此网站上，我看到了一个我想与postgresql一起使用的功能：

On this website I saw a function, that I would like to use with 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

我重命名了没有 @ 的变量并进行了更改身体有点，但不知何故我无法使其正常运行。

I renamed the variables without @ and changed the body a little bit, but somehow I can't get it to run correctly.

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

Is it possible to rewrite this query for postgresql? Do you have ideas how to do it? Thanks

推荐答案

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;

这篇关于PostgreSQL函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！