help - Tools

9 Oct 2022


      --------自定义函数f_change_days----------------------------------------------------------------
CREATE OR REPLACE FUNCTION f_change_days(i_day bigint, i_number bigint)
RETURNS bigint
LANGUAGE plpgsql
AS $$
DECLARE
BEGIN
return (to_char(to_date(i_day::varchar,'yyyymmdd')+i_number,'yyyymmdd'))::numeric;
end;
$$;
/
-------------------------------------------------------------------------------------------------------------
当调用 时报错：
select F_CHANGE_DAYS(20110101,-1);
报错信息：
[2022-10-09 16:50:54.164 CST] : [ERROR] 执行失败
错误代码：[0][SQLErrorCode : 9683][192.168.4.14:50311/192.168.2.23:15400] ERROR: operator does not exist: timestamp without time zone + bigint
  建议:No operator matches the given name and argument type(s). You might need to add explicit type casts.
  在位置:referenced column: to_char
PL/pgSQL function f_change_days(bigint,bigint) line 4 at RETURN
referenced column: f_change_daysLine Number: 1
当把函数拓传入参数及返回参数的数据类型都改为INTEGER时，就不会报错，如下：
--------修改后自定义函数f_change_days----------------------------------------------------------------
CREATE OR REPLACE FUNCTION f_change_days(i_day integer, i_number integer)
RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
BEGIN
return (to_char(to_date(i_day::varchar,'yyyymmdd')+i_number,'yyyymmdd'))::numeric;
end;
$$;
/
-------------------------------------------------------------------------------------------------------------
调用时正常：
select F_CHANGE_DAYS(20110101,-1);
正常输出：
20101231
我想问的时，函数的数据类型必须强一致，不太明白其中原理