PL/PgSQL:没有函数与给定的名称和参数类型匹配.您可能需要添加显式类型转换 [英] PL/PgSQL: No function matches the given name and argument types. You might need to add explicit type casts

问题描述

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

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":

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($1) = lower(YEAR_CONST) THEN
       select cast(cast(incrementvalue as character varying) || ' year' as interval) into intervals;
   ELSEIF lower($1) = lower(MONTH_CONST) THEN
       select cast(cast(incrementvalue as character varying) || ' months' as interval) into intervals;
   ELSEIF lower($1) = lower(DAY_CONST) THEN
       select cast(cast(incrementvalue as character varying) || ' day' as interval) into intervals;
   ELSEIF lower($1) = lower(HOUR_CONST) THEN
       select cast(cast(incrementvalue as character varying) || ' hour' as interval) into intervals;
   ELSEIF lower($1) = lower(MIN_CONST) THEN
       select cast(cast(incrementvalue as character varying) || ' minute' as interval) into intervals;
   ELSEIF lower($1) = 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=# 

为什么PG无法找到该功能?

Why is the function not being found by PG?

我正在Ubuntu 12.0.4 LTS上运行PG 9.3

I am running PG 9.3 on Ubuntu 12.0.4 LTS

推荐答案

您会踢自己:出错的行(及其后几行)与之前的那行之间的区别是，您不小心添加了下划线在错误的位置:

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:

您有:

HOUR_CONST_Char(15):='小时';

HOUR_CONST_Char(15) := 'hour';

应该是:

HOUR_CONST字符(15):='小时';

HOUR_CONST Char(15) := 'hour';

编辑

EDIT

更新后的问题受Postgres有点挑剔的类型系统的困扰:您的getdate()函数返回了timestamp with time zone，但是您的dateadd接受了timestamp(即timestamp without time zone).使用Postgres简写形式的value::type，这应该可以工作( SQLFiddle演示，使用now()代替getdate() )

The updated question is suffering from Postgres's slightly fussy type system: your getdate() function is returning timestamp with time zone, but your dateadd accepts 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())

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

但是，您还有其他一些奇数类型选择:

However, you have a few other odd type selections:

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

• 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 VarChar is essentially just text with 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.

最后，我要指出一个基本的优化方法:您不需要lower()常量，因为您已经知道它们是小写字母. :)

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

这篇关于PL/PgSQL:没有函数与给定的名称和参数类型匹配.您可能需要添加显式类型转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！