梧桐数据库操作：使用函数时显示不存在的解决办法

概述：

我们在日常开发和维护时，调用函数时，有时会出现报错：函数不存在的情况。 但究竟是函数真的不存在，还是调用方式有问题，用户并不能做出正确的判断，这样对用户使用函数时会产生误导。

出现问题：

当参数类型或者数量不正确时显示函数不存在或者其他错误提示，让用户误以为此函数不存在！

举例：

一、聚合函数中的部分常用函数：

使用聚合函数时，当输入的参数的数量不正确时，当参数数量过多时，一般会显示函数不存在；当不输入参数时，根据函数的不同，有的函数（比如sum）会显示函数不存在，有点函数会（比如count）显示参数错误；

1、使用聚合函数count时：

1)、如果是一个参数或者是一个字段名count可以正常使用：

select call_type_id,count(1) from  tb_cis_billing_opp_day_a_xh_1 group by call_type_id;

2)、当参数大于一个时：会显示：

pq: function count(integer, integer) does not exist

select call_type_id,count(1,1) from  tb_cis_billing_opp_day_a_xh_1 group by call_type_id;

3)、当没有参数时：

select call_type_id,count() from  tb_cis_billing_opp_day_a_xh_1 group by call_type_id;

2、在梧桐中使用聚合函数sum时：

1)、当输入一个参数时，可以正常使用：

select call_type_id,sum(statis_month) from  tb_cis_billing_opp_day_a_xh_1 group by call_type_id;

2)、当输入多个参数时会显示函数不存在：

select call_type_id,sum(statis_month,statis_month) from  tb_cis_billing_opp_day_a_xh_1 group by call_type_id;

3)、当不输入参数时：也会显示函数不存在：

select call_type_id,sum() from  tb_cis_billing_opp_day_a_xh_1 group by call_type_id;

二、部分常见算数函数：

对算数函数Floor、mod、abs进行测试；当输入的参数数量过多或者无参数时一般显示函数不存在，当函数类型不正确时一般会显示参数错误；

例：

select floor(1.88,12);
pq: function floor(numeric, integer) does not exist

select floor();
pq: function floor() does not exist

select floor('45.25')；
pq: function floor(unknown) is not unique

select abs('-1,2')
pq: invalid input syntax for type double precision: "-1,2"

三、字符串函数：

对 trim 和 substr 进行测试，trim 和 substr 对参数类型不太敏感，当输入字符串或者数值型（整型和浮点型）都可以正常使用；当不输入参数时，有的函数（substr）会显示函数不存在，有的函数（substr）会显示参数错误；当参数数量不正确时，有的函数（substr）会显示函数不存在，有的函数（trim）正常使用；

例：

select substr(12345.34,'1','2')

select trim(1234.2  )

select substr();
pq: function substr() does not exist

select trim();
pq: syntax error at or near ")"

select trim('  one two ',' three ');

select substr('abcde',1,2,2)
pq: function substr(unknown, integer, integer, integer) does not exist

四、时间函数：

对date和to_date进行测试，当date参数不正确时，会显示非法参数，当无参数时会显示函数不存在；当测试to_date时，当参数不正确或者没有参数时都会显示函数不存在：

select date(-20240606);
pq: time zone displacement out of range: "-20240606"

select date();
pq: function date() does not exist

select to_date(20240606)
pq: function to_date(integer) does not exist

select to_date();
pq: function to_date() does not exist

解决办法：

用户在使用函数时，为了在系统返回结果不给自己造成误导，必需要对调用的函数输入正确的参数。如果不知道使用此函数的使用参数详细方法时，可通过查看系统函数说明来正确调用函数。以下将如何查看系统函数的方法列出来，以供用户参考：

一、 查看系统所有函数：

#梧桐6.0版本

SELECT 
    n.nspname AS schema_name,
    p.proname AS function_name,
    pg_catalog.pg_get_function_result(p.oid) AS return_type,
    pg_catalog.pg_get_function_arguments(p.oid) AS arguments,
    pg_catalog.pg_get_userbyid(p.proowner) AS owner,
    l.lanname AS language,
    p.prosrc AS source_code
FROM 
    pg_catalog.pg_proc p
LEFT JOIN 
    pg_catalog.pg_namespace n ON n.oid = p.pronamespace
LEFT JOIN 
    pg_catalog.pg_language l ON l.oid = p.prolang
ORDER BY 
    n.nspname, p.proname;

# 梧桐5.4

SELECT 
    n.nspname AS schema_name,
    p.proname AS function_name,
    pg_catalog.format_type(p.prorettype, NULL) AS return_type,
    pg_catalog.pg_get_userbyid(p.proowner) AS owner,
    l.lanname AS language,
    p.prosrc AS source_code
FROM 
    pg_catalog.pg_proc p
LEFT JOIN 
    pg_catalog.pg_namespace n ON n.oid = p.pronamespace
LEFT JOIN 
    pg_catalog.pg_language l ON l.oid = p.prolang
ORDER BY 
    1, 2;

二、 查看指定函数的详细信息

1、使用 pg_get_functiondef 函数：

# 梧桐6.0

SELECT 
    p.proname AS function_name,
    pg_catalog.pg_get_functiondef(p.oid) AS definition
FROM 
    pg_catalog.pg_proc p
LEFT JOIN 
    pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE 
    p.proname = 'your_function_name' -- 调用的函数名
    AND n.nspname = 'your_schema_name';  -- 确保使用正确的模式名称

# 梧桐5.4
# 在5.4版本中没有 pg_get_functiondef 函数，你可以使用如下查询来获取函数的基本信息：
SELECT
    n.nspname AS schema_name,
    p.proname AS function_name,
    pg_catalog.format_type(p.prorettype, NULL) AS return_type,
    p.proargtypes AS argument_types,
    pg_catalog.pg_get_userbyid(p.proowner) AS owner,
    l.lanname AS language,
    p.prosrc AS source_code
FROM
    pg_catalog.pg_proc p
LEFT JOIN
    pg_catalog.pg_namespace n ON n.oid = p.pronamespace
LEFT JOIN
    pg_catalog.pg_language l ON l.oid = p.prolang
WHERE
    p.proname = 'your_function_name'
    AND n.nspname = 'your_schema_name';

以上将 your_function_name 替换为你要查看的函数名，将 your_schema_name 替换为该函数所在的模式名称。

示例：

假设你有一个函数名为 my_function，并且它在 public 模式中，你可以运行以下查询：

# 梧桐6.0

SELECT 
    p.proname AS function_name,
    pg_catalog.pg_get_functiondef(p.oid) AS definition
FROM 
    pg_catalog.pg_proc p
LEFT JOIN 
    pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE 
    p.proname = 'my_function'
    AND n.nspname = 'public';

# 梧桐5.4

SELECT
    n.nspname AS schema_name,
    p.proname AS function_name,
    pg_catalog.format_type(p.prorettype, NULL) AS return_type,
    p.proargtypes AS argument_types,
    pg_catalog.pg_get_userbyid(p.proowner) AS owner,
    l.lanname AS language,
    p.prosrc AS source_code
FROM
    pg_catalog.pg_proc p
LEFT JOIN
    pg_catalog.pg_namespace n ON n.oid = p.pronamespace
LEFT JOIN
    pg_catalog.pg_language l ON l.oid = p.prolang
WHERE
    p.proname = 'my_function'
    AND n.nspname = 'public';

该查询可能会返回如下结果：

function_name | definition
--------------+--------------------------------------------------------
my_function   | CREATE OR REPLACE FUNCTION public.my_function(integer)
              |  RETURNS integer
              |  LANGUAGE plpgsql
              | AS $function$
              | BEGIN
              |  RETURN $1 + 1;
              | END;
              | $function$

2、使用 \sf 命令：

在 命令行工具中，你还可以使用 \sf 命令来查看函数的定义。例如，要查看 my_function 的定义，可以使用：

\sf public.my_function

3、使用 information_schema 视图

你也可以结合 information_schema 视图来获取函数的详细信息。例如：

# 梧桐6.0

SELECT
    routines.routine_name,
    routines.data_type AS return_type,
    parameters.parameter_name,
    parameters.data_type AS parameter_type
FROM
    information_schema.routines
LEFT JOIN
    information_schema.parameters ON routines.specific_name = parameters.specific_name
WHERE
    routines.routine_name = 'my_function'
    AND routines.specific_schema = 'public'
ORDER BY
    routines.routine_name, parameters.ordinal_position;

这个查询将返回函数 my_function 的返回类型及其参数的详细信息。