PostgreSQL查询结果迭代报错：record无id字段求助

我是PostgreSQL新手，想要执行SELECT查询后迭代结果集，逐条处理记录并插入到accounts表，但执行自定义函数时出现错误。错误信息如下：

ERROR:  record "setting_rows" has no field "id"
CONTEXT:  SQL statement "SELECT 
            "Id", "AppValue", "UcValue", "AppRefId", "UcRefId",
            "integrationId", "DefaultUc", "SettingId", "DefaultApp",
            "Exclude", "AppFieldType", "UcFieldType"
                                                                                                                                                         FROM mappings 
        WHERE "SettingId" = setting_rows.Id
        LIMIT 1"
PL/pgSQL function "setWorkorderStatus"() line 25 at SQL statement
SQL state: 42703

函数代码如下：

DROP FUNCTION IF EXISTS public."setWorkorderStatus"();
CREATE OR REPLACE FUNCTION public."setWorkorderStatus"()
    RETURNS VOID 
    LANGUAGE 'plpgsql'
AS $BODY$
DECLARE 
    r_count INTEGER;
    setting_rows RECORD;
    s_id BIGINT;
    AppValue TEXT;
    UcValue TEXT ;
    AppRefId TEXT;
    UcRefId TEXT;
    integrationId BIGINT;
    DefaultUc BOOLEAN;
    SettingId BIGINT;
    DefaultApp BOOLEAN;
    Exclude BOOLEAN;
    AppFieldType TEXT;
    UcFieldType TEXT;
BEGIN
    FOR setting_rows IN SELECT "Id","integrationId", "Key", "Value", "VendorClientId", "Appkey", "UCkey" FROM settings WHERE "Key" = 'wo_order_statuses' LOOP
        SELECT 
            "Id", "AppValue", "UcValue", "AppRefId", "UcRefId",
            "integrationId", "DefaultUc", "SettingId", "DefaultApp",
            "Exclude", "AppFieldType", "UcFieldType"
        INTO
            s_id, AppValue, UcValue, AppRefId, UcRefId,
            integrationId, DefaultUc, SettingId, DefaultApp,
            Exclude, AppFieldType, UcFieldType
        FROM mappings 
        WHERE "SettingId" = setting_rows.Id
        LIMIT 1;
        INSERT INTO accounts(name,balance)
        VALUES(s_id,700);
    END LOOP;
END
$BODY$;

ALTER FUNCTION public."setWorkorderStatus"()
    OWNER TO postgres;

错误原因及解决方法 #

错误核心是PostgreSQL对标识符大小写的处理规则：

• 查询settings表时用双引号包裹了"Id"，PostgreSQL会保留字段名的大小写，返回的setting_rows记录中该字段名称为大写首字母的Id
• 后续引用setting_rows.Id时未加双引号，PostgreSQL会自动将其转换为小写id，导致找不到对应字段

方案1：修正字段引用的大小写

将WHERE子句中的setting_rows.Id改为带双引号的setting_rows."Id"：

WHERE "SettingId" = setting_rows."Id"

方案2：使用全小写标识符（推荐）

PostgreSQL默认会把未加双引号的标识符转为小写，建议将表中字段名统一改为全小写（比如id代替Id），后续查询和引用都不用双引号，彻底避免大小写问题：

-- 查询时不用双引号
FOR setting_rows IN SELECT id,integration_id, key, value, vendor_client_id, appkey, uckey FROM settings WHERE key = 'wo_order_statuses' LOOP
-- 引用时直接写setting_rows.id
WHERE setting_id = setting_rows.id

额外优化建议 #

逐行循环处理在数据量较大时性能较差，推荐直接用INSERT...SELECT语句一次性完成插入，代码更简洁高效：

INSERT INTO accounts(name, balance)
SELECT m."Id", 700
FROM settings s
JOIN mappings m ON m."SettingId" = s."Id"
WHERE s."Key" = 'wo_order_statuses'
LIMIT 1; -- 如果只需要每条setting对应一条mapping记录

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