调用含uuid类型复合数组参数的PostgreSQL存储过程报错求助

问题根因 #

报错核心原因是存储过程内的FOR循环遍历逻辑错误：使用SELECT UNNEST(customer_items)时，返回的结果集每一行是包含单个item类型字段的行记录，直接将整行赋值给b item变量时，PostgreSQL会尝试把行输出字符串直接匹配第一个uuid字段，最终触发格式错误。

解决方案 #

1. 优先修改存储过程循环逻辑（推荐两种写法） #

写法A：使用FOREACH直接遍历数组（性能最优，无需调用unnest）

PostgreSQL原生支持FOR循环直接遍历数组，替换原有循环块即可：

FOREACH b IN ARRAY customer_items
LOOP
    RAISE NOTICE 'b.sku_reference_id: %', b.sku_reference_id; 
    RAISE NOTICE 'b.quantity: %', b.quantity;
    INSERT INTO item_table(SKU_REFERENCE_ID, QUANTITY) VALUES(b.sku_reference_id, b.quantity);
END LOOP;

写法B：修正unnest查询逻辑

如果要保留unnest用法，修改查询语句取子字段：

FOR b IN SELECT * FROM UNNEST(customer_items)
LOOP
    RAISE NOTICE 'b.sku_reference_id: %', b.sku_reference_id; 
    RAISE NOTICE 'b.quantity: %', b.quantity;
    INSERT INTO item_table(SKU_REFERENCE_ID, QUANTITY) VALUES(b.sku_reference_id, b.quantity);
END LOOP;

2. 可选优化调用写法 #

你之前的3种调用写法本身语法没有问题，修改存储过程后都可正常运行，也可以使用更简洁的调用方式：

CALL dbo.sp_test_script(
    'username',
    ARRAY[
        ('e3903331-ce0b-4bd5-a853-ec7aa725a812', 20)::item,
        ('ccbecc39-11bd-4bc4-97d6-7e2f981d30dd', 40)::item
    ],
    0
);

兼容性说明 #

修改后的逻辑完全兼容PostgreSQL 13.3版本，可直接在AWS RDS环境运行，调用后可正确打印字段值并插入临时表。

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