PostgreSQL 9.6调用get_color函数报UndefinedFunction错误求助

问题描述 #

学习PostgreSQL时，尝试在SELECT语句中调用get_color函数并传入pianokeys表的n列作为参数，但PostgreSQL 9.6及13.0版本均返回UndefinedFunction错误。

完整查询代码 #

SELECT n 
FROM pianokeys pn,
LATERAL get_color(pn.n) AS res;

CREATE OR REPLACE FUNCTION get_color(n integer) RETURNS text AS $$
  BEGIN
    IF((n % 88) % 2)
    THEN
       RETURN $b$black$b$;
    ELSE
       RETURN $w$white$w$;
    END IF;
  END;
$$ LANGUAGE plpgsql;

错误信息 #

There was an error with the SQL query:

PG::UndefinedFunction: ERROR:  function get_color(integer) does not exist
LINE 8: lateral get_color(pn.n) as res) AS "t1" LIMIT 1
                ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

问题原因及解决步骤 #

1. 函数定义顺序错误
   你先执行了SELECT查询，再定义函数，此时查询执行时get_color函数还未创建，必然报错。必须先运行CREATE OR REPLACE FUNCTION语句创建函数，再执行SELECT查询。

2. IF条件表达式不合法
   PostgreSQL的IF语句要求条件是布尔值，而(n % 88) % 2返回的是整数（0或1），不能直接作为条件判断。需要修改为布尔表达式：

   IF ((n % 88) % 2) = 1 THEN
   -- 或者
   IF ((n % 88) % 2) != 0 THEN
   

3. 参数类型匹配检查
   确认pianokeys表的n列类型确实是integer。如果是其他数值类型（比如numeric），需要显式转换参数类型：

   LATERAL get_color(pn.n::integer) AS res
   

4. 可选优化：简化函数写法
   这个逻辑可以用更简洁的SQL函数实现，不需要plpgsql：

   CREATE OR REPLACE FUNCTION get_color(n integer) RETURNS text AS $$
     SELECT CASE WHEN ((n % 88) % 2) = 1 THEN 'black' ELSE 'white' END;
   $$ LANGUAGE sql;
   

内容的提问来源于stack exchange，提问作者EDOARDO ROSSO