Supabase中PGSQL函数ON CONFLICT语法验证失败求助

你的核心问题是**ON CONFLICT子句的位置不符合PostgreSQL语法规范**。在INSERT ... SELECT语句中，ON CONFLICT必须放在SELECT查询块的后面，而不是列定义列表之后。

修正后的完整函数代码 #

CREATE OR REPLACE FUNCTION fetch_and_insert_donations()
RETURNS void AS $$
BEGIN
    INSERT INTO donations(
        donation_id, amount, avatar_image_url, created_date_utc, 
        display_name, donor_id, event_id, incentive_id, 
        message, participant_id, team_id
    )
    SELECT 
        elms->>'donationID' AS donation_id,
        (elms->>'amount')::float8 AS amount,
        elms->>'avatarImageURL' AS avatar_image_url,
        (elms->>'createdDateUTC')::timestamptz AS created_date_utc,
        elms->>'displayName' AS display_name,
        elms->>'donorID' AS donor_id,
        (elms->>'eventID')::int AS event_id,
        elms->>'incentiveID' AS incentive_id,
        elms->>'message' AS message,
        (elms->>'participantID')::int AS participant_id,
        (elms->>'teamID')::int AS team_id
    FROM (
        SELECT jsonb_array_elements(content::jsonb) AS elms
        FROM http_get('https://extralife.donordrive.com/api/teams/59881/donations/')
    ) AS alias
    ON CONFLICT (donation_id) DO NOTHING;
END;
$$ LANGUAGE plpgsql;

关键说明 #

1. 语法位置修正：把ON CONFLICT (donation_id) DO NOTHING移到了SELECT查询的末尾，这是PostgreSQL要求的正确顺序。
2. 依赖唯一约束：确保你的donations表中，donation_id字段已经创建了唯一约束或主键，否则ON CONFLICT子句无法生效。如果还没创建，可以执行：

   ALTER TABLE donations ADD CONSTRAINT unique_donation_id UNIQUE (donation_id);
   

3. 简化JSON解析：原代码里的elms::jsonb->>可以简化为elms->>，因为jsonb_array_elements返回的已经是jsonb类型，不需要再次强制转换。

可选优化（更简洁的JSON解析） #

可以用jsonb_to_recordset直接把JSON数组映射为行记录，减少嵌套查询：

CREATE OR REPLACE FUNCTION fetch_and_insert_donations()
RETURNS void AS $$
BEGIN
    INSERT INTO donations(
        donation_id, amount, avatar_image_url, created_date_utc, 
        display_name, donor_id, event_id, incentive_id, 
        message, participant_id, team_id
    )
    SELECT 
        donation_id, amount, avatar_image_url, created_date_utc,
        display_name, donor_id, event_id, incentive_id,
        message, participant_id, team_id
    FROM jsonb_to_recordset(
        (SELECT content::jsonb FROM http_get('https://extralife.donordrive.com/api/teams/59881/donations/'))
    ) AS x(
        donation_id text,
        amount float8,
        avatar_image_url text,
        created_date_utc timestamptz,
        display_name text,
        donor_id text,
        event_id int,
        incentive_id text,
        message text,
        participant_id int,
        team_id int
    )
    ON CONFLICT (donation_id) DO NOTHING;
END;
$$ LANGUAGE plpgsql;

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