PostgreSQL自定义PL/pgSQL函数触发42601错误求解决方案

错误原因 #

• 直接在PL/pgSQL代码块中执行SELECT语句但没有指定结果存储的目标变量，PostgreSQL不知道你要拿这个查询结果做什么，所以触发报错。
• 函数返回值声明为VARCHAR单字符串类型，但你实际需要返回emp_id、role_id、from_date、to_date4个字段，返回类型不匹配，且RETURN后直接跟未定义的列名，语法不合法。
• 查询逻辑没有使用传入的project_id参数，调用时传的'P01'完全没有生效，会返回全表的分配记录。
• 声明的变量名和函数名重名，存在命名冲突风险。
• 后续DROP FUNCTION语句没有匹配函数的入参定义，无法正确删除你创建的函数。

修正后可运行代码 #

一般同个项目会对应多个员工分配记录，这里采用返回表结构的方案，可直接查询返回多行多列结果：

-- 创建返回多列多行结果的函数
CREATE OR REPLACE FUNCTION show_emp_details(p_project_id varchar(10))
RETURNS TABLE (
    emp_id varchar,
    role_id varchar,
    from_date date,
    to_date date
)
LANGUAGE plpgsql
AS $BODY$
BEGIN
    -- 用RETURN QUERY直接将查询结果作为返回值，解决SELECT无目标的问题
    RETURN QUERY
    SELECT al.emp_id, al.role_id, al.from_date, al.to_date
    FROM allocation al
    JOIN t_project pj ON pj.project_id = al.project_id
    WHERE al.project_id = p_project_id; -- 匹配入参过滤指定项目的记录
END
$BODY$;

-- 正确删除函数的写法，需要匹配入参类型
-- DROP FUNCTION IF EXISTS show_emp_details(varchar(10));

-- 调用函数，直接查询返回的表结构即可
SELECT * FROM show_emp_details('P01');

内容的提问来源于stack exchange，提问作者hari kishore