如何在PostgreSQL函数中将日期用作变量？

Question

我正在尝试在PostgreSQL 9.5中编写一个函数，该函数以日期作为参数。我的表有一个名为inception_date的列，我希望该函数返回表中所有inception_date大于变量提供的日期的行。以下是我的函数：

    CREATE OR REPLACE FUNCTION somefunc(date) RETURNS setof table AS 
    $BODY$
    DECLARE variable ALIAS FOR $1;
    BEGIN
    RETURN QUERY SELECT * FROM table WHERE inception_date > variable;
    END;
    $BODY$
    LANGUAGE 'plpgsql'; 

    SELECT somefunc('2014-07-02');

我找不到任何信息表明，在posgreSQL函数中，日期与其他数据类型的处理方式有所不同，但这个函数没有显示任何输出，而查询语句却有。

   SELECT * FROM table WHERE inception_date > '2014-07-02';

返回15行。有人知道这里可能出了什么问题吗？

- Andi

---

实际上，SELECT somefunc('2014-07-02'); 和 SELECT * FROM table WHERE inception_date > '2014-07-02'; 并不相同，因为函数返回 record 类型的单个列，而查询返回单独的列。你如何检查结果？ - Abelisto

嗯，使用“ALIAS FOR”而不是在函数定义中直接放置参数名称有点过时了，而且您不想引用“plpgsql”语言规范，但这看起来应该可以工作。事实并非如此。首先，通过“SELECT * FROM somefunc...”正确调用它。然后，简化操作：（1）仅返回整个表，然后（2）明确检查常量日期。告诉我们这样做会发生什么。 - Richard Huxton

列 inception_date 的类型是什么？ - Ezequiel Tolnay

Answer 1

这里发生了什么，您解释代码与PostgreSQL解析器不同。对于您来说，“2014-07-02”是一个日期。对于解析器来说，这是一个字符串。但当它看到您将其用于筛选日期列时，它会尝试将其解释为日期。

但是，给定“2014-07-02”，您是指二月的第七天还是七月的第二天？他不知道（我也不知道）。因此，基本上这是您的错误，您不应该这样做。

我的建议是创建一个带有命名参数的日期类型函数，并将其用作日期。然后，在调用此函数并将字符串用作参数时，我会将此字符串转换为日期，并告诉PostgreSQL我想要的日期。

下面是示例和手册参考。

    CREATE OR REPLACE FUNCTION somefunc(p_date date)
    RETURNS setof table 
    AS 
    $BODY$
        BEGIN
            RETURN QUERY SELECT * FROM table WHERE inception_date > p_date;
        END;
    $BODY$
    LANGUAGE 'plpgsql'; 

    SELECT somefunc(to_date('2014-07-02','YYYY-MM-DD'));

在这里，您可以找到有关日期格式化和函数创建的更多信息。

- T.Z.

Answer 2

我有一个使用包含日期的伪变量的“模式”。它不依赖于函数。 我喜欢使用WITH() CTE来实现这个目的：

WITH myvar (enddate) AS (VALUES(to_date('2020-06-26', 'YYYY-MM-DD')))
SELECT 'Total downloaded records', sum(d.total_records) FROM download_table d 
JOIN myvar m ON date(d.created) BETWEEN '2020-01-01' AND m.enddate

请注意，WITH公共表达式中包含将日期转换为日期格式的内容。这是因为您需要在日期类型之间进行比较，而不是日期<=文本。

- Kristoffer Legind