Postgres 13含循环的自定义函数报错，求助实现UPDATE逻辑

先逐个拆解函数中的语法与逻辑问题，再给出修正方案：

语法错误修正点 #

• 变量声明遗漏分号：DECLARE sid int = 0 末尾缺少分号，PostgreSQL要求每个声明语句必须以分号结尾。
• 嵌套查询语法错误：SET sname = SELECT split_part(SELECT ...) 中，嵌套的姓名查询需要用括号包裹，正确写法为 split_part((SELECT CONCAT(first_name, ' ', last_name) FROM resume WHERE id = rid), ' ', i)。
• IF条件语法错误：IF子句中不能直接写查询语句，需通过SELECT ... INTO语法获取匹配的ID，或先判断存在性再赋值。
• 重复查询性能浪费：函数中多次查询resume表获取姓名，应提前将完整姓名存入变量，避免重复IO操作。

逻辑错误修正点 #

• 循环范围错误：初始i是姓名中的空格数，循环时i = i -1会导致只检查前N-1个单词（N为单词总数），应改为从1到单词总数（空格数+1）循环，确保每个单词都被检查到。

修正后的函数代码 #

CREATE OR REPLACE FUNCTION check_language(rid integer) RETURNS integer AS
$BODY$
DECLARE
    full_name varchar;
    word_count int;
    sname varchar;
    sid int = 0;
BEGIN
    -- 一次性获取完整姓名，避免重复查询表
    SELECT CONCAT(first_name, ' ', last_name) INTO full_name FROM resume WHERE id = rid;
    -- 计算姓名中的单词总数（空格数+1）
    word_count = (LENGTH(full_name) - LENGTH(REPLACE(full_name, ' ', ''))) + 1;
    
    -- 遍历每个单词进行匹配
    FOR i IN 1..word_count LOOP
        sname = split_part(full_name, ' ', i);
        -- 查询匹配的语种ID，找到后立即退出循环提升效率
        SELECT id INTO sid FROM mother_tongues 
        WHERE noun ILIKE '%+' || sname || '+%'
        LIMIT 1;
        
        IF sid IS NOT NULL THEN
            EXIT;
        END IF;
    END LOOP;
    
    RETURN sid;
END;
$BODY$ 
LANGUAGE plpgsql;

修正后的UPDATE语句 #

原UPDATE语句嵌套查询逻辑冗余，简化后直接调用函数即可：

UPDATE resume
SET mother_tongues_id = check_language(id)
WHERE mother_tongues_id IS NULL;

更高效的无函数实现（推荐） #

用原生SQL直接拆分姓名并匹配，避免PL/pgSQL循环的性能开销：

UPDATE resume r
SET mother_tongues_id = mt.id
FROM (
    SELECT 
        r.id,
        mt.id
    FROM resume r
    CROSS JOIN UNNEST(string_to_array(CONCAT(r.first_name, ' ', r.last_name), ' ')) AS words(word)
    JOIN mother_tongues mt ON mt.noun ILIKE '%+' || words.word || '+%'
    WHERE r.mother_tongues_id IS NULL
    GROUP BY r.id, mt.id
    LIMIT 1 -- 若多个单词匹配不同语种，取第一个匹配结果
) sub
WHERE r.id = sub.id;

内容的提问来源于stack exchange，提问作者Lê Minh Thiện