PostgreSQL编写存储过程校验字段遇length函数不存在报错如何解决

错误原因 #

• 游标返回的是行记录类型，你直接对行变量调用length()函数，PostgreSQL找不到匹配记录类型入参的length()实现，就会报函数不存在的错误。你需要显式指定行变量里的字段名才能取到字符串值。
• 存储过程定义为无参数，但调用时传入了'testtest'参数，参数数量不匹配，运行时也会报错。
• 逻辑设置有误：你字段定义是VARCHAR(40)，最大允许40个字符，判断超过阈值应该是length(xxx) > 40，你写的41会漏判长度为41的非法值。

修复后的代码 #

版本1：遍历所有已录入餐厅名称校验 #

DROP PROCEDURE IF EXISTS test_name();
CREATE PROCEDURE test_name()
LANGUAGE plpgsql
AS $$
DECLARE
    v_restaurant RECORD;
    c1 CURSOR FOR SELECT RESTAURANT_NAME FROM RESTAURANT;
BEGIN
    FOR v_restaurant IN c1
    LOOP
        IF length(v_restaurant.RESTAURANT_NAME) > 40 THEN
            RAISE NOTICE '餐厅名称 % 长度超过40字符限制', v_restaurant.RESTAURANT_NAME;
        END IF;
    END LOOP;
END;
$$;
-- 调用方式（无参数）
CALL test_name();

版本2：按需求实现输入值校验（推荐） #

如果你需要校验新增的餐厅名是否符合要求，可以直接传入待校验的名称作为参数，符合你说的「输入值和表字段约束比对」的需求：

DROP PROCEDURE IF EXISTS test_name(IN p_restaurant_name VARCHAR);
CREATE PROCEDURE test_name(IN p_restaurant_name VARCHAR)
LANGUAGE plpgsql
AS $$
BEGIN
    IF length(p_restaurant_name) > 40 THEN
        RAISE EXCEPTION '餐厅名称长度不能超过40字符，当前输入长度为%', length(p_restaurant_name);
    END IF;
    RAISE NOTICE '餐厅名称校验通过';
END;
$$;
-- 调用方式，传入待校验的名称
CALL test_name('testtest');

额外优化建议 #

如果只是要限制餐厅名称长度，直接在表上加CHECK约束更简单，不需要额外写存储过程：

ALTER TABLE RESTAURANT ADD CONSTRAINT chk_restaurant_name_length CHECK (length(RESTAURANT_NAME) <=40);

加了这个约束之后，任何插入或者更新导致名称超过40字符的操作都会直接报错，不需要手动调用存储过程校验。

内容的提问来源于stack exchange，提问作者Smith K