postgresql PL/PgSQL：没有函数匹配给定的名称和参数类型。您可能需要添加显式类型转换

PL/PgSQL: No function matches the given name and argument types. You might need to add explicit type casts

提问by Homunculus Reticulli

I am trying to write a dateadd()function using PL/PgSQL. I want to be able to add anything from seconds, right up to years to a date/timestamp. have cobbled together a function (from snippets etc obtained online), and have come up with this "implementation":

我正在尝试dateadd()使用 PL/PgSQL编写一个函数。我希望能够添加从秒到年到日期/时间戳的任何内容。拼凑了一个函数（从网上获得的片段等），并提出了这个“实现”：

CREATE OR REPLACE FUNCTION dateadd(diffType VARCHAR(15), incrementValue int, inputDate timestamp) RETURNS timestamp AS $$
DECLARE
   YEAR_CONST Char(15) := 'year';
   MONTH_CONST Char(15) := 'month';
   DAY_CONST Char(15) := 'day';
   HOUR_CONST Char(15) := 'hour';
   MIN_CONST Char(15) := 'min';
   SEC_CONST Char(15) := 'sec';

   dateTemp Date;
   intervals interval;
BEGIN
   IF lower() = lower(YEAR_CONST) THEN
       select cast(cast(incrementvalue as character varying) || ' year' as interval) into intervals;
   ELSEIF lower() = lower(MONTH_CONST) THEN
       select cast(cast(incrementvalue as character varying) || ' months' as interval) into intervals;
   ELSEIF lower() = lower(DAY_CONST) THEN
       select cast(cast(incrementvalue as character varying) || ' day' as interval) into intervals;
   ELSEIF lower() = lower(HOUR_CONST) THEN
       select cast(cast(incrementvalue as character varying) || ' hour' as interval) into intervals;
   ELSEIF lower() = lower(MIN_CONST) THEN
       select cast(cast(incrementvalue as character varying) || ' minute' as interval) into intervals;
   ELSEIF lower() = lower(SEC_CONST) THEN
       select cast(cast(incrementvalue as character varying) || ' second' as interval) into intervals;
   END IF;

   dateTemp:= inputdate + intervals;

   RETURN dateTemp;
END;
$$ IMMUTABLE LANGUAGE plpgsql;

However, when I try to use the function, I get the following error:

但是，当我尝试使用该函数时，出现以下错误：

template1=# select dateadd('hour', 1, getdate());
ERROR:  function dateadd(unknown, integer, timestamp with time zone) does not exist
LINE 1: select dateadd('hour', 1, getdate());
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
template1=# 

Why is the function not being found by PG?

为什么PG找不到该功能？

I am running PG 9.3 on Ubuntu 12.0.4 LTS

我在 Ubuntu 12.0.4 LTS 上运行 PG 9.3

回答by IMSoP

You'll kick yourself: the difference between the line that's erroring (and the next few) and the one before it is that you've accidentally added an underscore in the wrong place:

你会踢自己：出错的行（和接下来的几行）和之前的行之间的区别在于你不小心在错误的地方添加了下划线：

You have:

你有：

HOUR_CONST_Char(15) := 'hour';

HOUR_CONST_Char(15) := '小时';

It should be:

它应该是：

HOUR_CONST Char(15) := 'hour';

HOUR_CONST 字符（15）：= '小时';

EDIT

编辑

The updated question is suffering from Postgres's slightly fussy type system: your getdate()function is returning timestamp with time zone, but your dateaddaccepts a timestamp(i.e. timestamp without time zone). Using the Postgres short-hand for cast of value::type, this should work (SQLFiddle demo, using now()in place of getdate())

更新后的问题受到 Postgres 稍微繁琐的类型系统的影响：您的getdate()函数正在返回timestamp with time zone，但您dateadd接受 a timestamp(即timestamp without time zone)。使用 Postgres 简写的 cast of value::type，这应该可以工作（SQLFiddle 演示，使用now()代替getdate()）

select dateadd('hour', 1, getdate()::timestamp);

However, you have a few other odd type selections:

但是，您还有其他一些奇怪的类型选择：

• Your "constants" are Char(15), but aren't 15 characters long, so will be padded with spaces; you should probably use VarChar(15), or even just text(unlike MS SQL, in Postgres, all strings are stored out-of-page dynamically, and a VarCharis essentially just textwith a maximum length constraint).
• Your intermediate variable (which can probably be refactored out) is of type Date, not Timestamp, so will truncate the input to just the date part, no time.

• 你的“常量”是Char(15)，但不是 15 个字符长，所以会用空格填充；您可能应该使用VarChar(15)，甚至只是text（与 MS SQL 不同，在 Postgres 中，所有字符串都是动态存储在页外的，而 aVarChar本质上只是text具有最大长度约束）。
• 您的中间变量（可能可以重构）的类型为Date，而不是Timestamp，因此会将输入截断为仅日期部分，没有时间。

Finally, I'll note one fundamental optimisation: you don't need to lower()your constants, because you already know they're lower case. :)

最后，我会注意到一个基本的优化：你不需要lower()你的常量，因为你已经知道它们是小写的。:)

回答by NL - Apologize to Monica

It's the underscore between HOUR_CONST and Char(15) You should enter "HOUR_CONST Char(15)" instead of "HOUR_CONST_Char(15)"

它是 HOUR_CONST 和 Char(15) 之间的下划线 你应该输入 "HOUR_CONST Char(15)" 而不是 "HOUR_CONST_Char(15)"