PostgreSQL 存储过程数据参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5996399/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL stored procedure data parameter
提问by user373201
I have the following stored procedure, which returns 0 results but if the run the query by itself it result lot of results. What am i missing.
我有以下存储过程，它返回 0 个结果，但如果单独运行查询，它会产生很多结果。我错过了什么。
CREATE OR REPLACE FUNCTION countStatistics(baselineDate Date) RETURNS int AS $$
DECLARE
qty int;
BEGIN
SELECT COUNT(*) INTO qty FROM statistics WHERE time_stamp = baselineDate;
RETURN qty;
END;
$$ LANGUAGE plpgsql;
--Execute the function
SELECT countStatistics('2015-01-01 01:00:00') as qty;
return 0 results
返回 0 个结果
SELECT COUNT(*) FROM statistics WHERE time_stamp = '2015-01-01 01:00:00';
return 100+ results
返回 100 多个结果
回答by mu is too short
You're declaring your baselineDateparameter as a date:
您将baselineDate参数声明为date：
CREATE OR REPLACE FUNCTION countStatistics(baselineDate Date)
but using it as a timestamp:
但将其用作timestamp：
SELECT COUNT(*) INTO qty FROM statistics WHERE time_stamp = baselineDate;
You're getting an implicit cast so countStatistics('2015-01-01 01:00:00')will actually execute this SQL:
您将获得一个隐式转换，因此countStatistics('2015-01-01 01:00:00')将实际执行此 SQL：
SELECT COUNT(*) INTO qty FROM statistics WHERE time_stamp = '2015-01-01';
and, after the dateis implicitly cast back to a timestamp, it will effectively be this:
并且，在date隐式转换回 a 之后timestamp，它实际上是这样的：
SELECT COUNT(*) INTO qty FROM statistics WHERE time_stamp = '2015-01-01 00:00:00';
Try changing your function declaration to use a timestamp:
尝试更改您的函数声明以使用timestamp：
CREATE OR REPLACE FUNCTION countStatistics(baselineDate timestamp)
