PostgreSQL调用返回多行的SELECT函数执行UPDATE报错如何解决？

• 返回值类型不匹配：emailAnonymisation()返回的是多行结果集（表结构），无法直接赋值给varchar[]类型的变量，这是触发「返回超过一行」报错的直接原因。
• 更新逻辑完全错位：你代码中把取出的邮箱值当成表名拼接进了UPDATE语句，实际你要更新的是固定表compte的mail字段，不是更新名称为邮箱值的表。
• 函数和语法误用：PostgreSQL没有LOCATE函数，对应的字符串位置查找函数为strpos；字符串内容需要用单引号包裹，双引号仅用于标识表名、字段名等标识符。
• 动态SQL滥用：你没有动态拼接表名/字段名的需求，不需要使用EXECUTE，反而会引入引号语法错误、SQL注入风险。

方案1：单函数实现（更简洁，无需额外定义查询函数） #

CREATE OR REPLACE FUNCTION updateMail()
RETURNS varchar[] AS
$$
DECLARE
    updated_mails varchar[];
BEGIN
    -- 直接更新compte表中前100个符合条件的邮箱，@后所有字符替换为X，同时返回更新结果
    UPDATE compte
    SET mail = overlay(mail placing repeat('X', length(mail) - strpos(mail, '@')) from strpos(mail, '@') + 1)
    WHERE strpos(mail, '@') > 0
    LIMIT 100
    RETURNING mail INTO updated_mails;
    
    RETURN updated_mails;
END;
$$ LANGUAGE plpgsql;

方案2：保留原有两个函数的写法修正 #

原emailAnonymisation函数无需修改 #

CREATE OR REPLACE FUNCTION emailAnonymisation() 
RETURNS table (mail varchar)
LANGUAGE plpgsql 
AS $$
BEGIN
    return query
SELECT compte.mail
  FROM compte
  limit 100;
END 
$$;

修正后的updateMail函数 #

CREATE OR REPLACE FUNCTION updateMail()
RETURNS varchar[] AS
$BODY$
DECLARE
    _mail_arr varchar[];
    t    text;
    updated_mails varchar[] := '{}'::varchar[];
BEGIN
    -- 将查询到的多行邮箱结果聚合为数组，再赋值给变量
    SELECT array_agg(mail) INTO _mail_arr FROM emailAnonymisation();

    FOREACH t IN ARRAY _mail_arr
    LOOP
        -- 定位对应邮箱完成更新，替换@后内容为X
        UPDATE compte
        SET mail = overlay(mail placing repeat('X', length(t) - strpos(t, '@')) from strpos(t, '@') + 1)
        WHERE mail = t
        RETURNING mail INTO updated_mails[array_length(updated_mails, 1) + 1];
    END LOOP;

    RETURN updated_mails;
END;
$BODY$ LANGUAGE plpgsql;

select updateMail();

内容的提问来源于stack exchange，提问作者Aloïs Coussout