PostgreSQL对row_to_json结果嵌套循环报42601语法错误如何解决

1. 语法错误根因 #

PL/pgSQL的块结构要求：所有变量声明（DECLARE段）必须对应一个BEGIN ... END代码段，你在第一层循环内部声明orgs、j变量之后，没有写BEGIN就直接写内层FOR循环，直接触发语法报错。

2. 额外逻辑问题修正 #

你当前写的row_to_json(t)仅能获取查询结果的第一行数据生成JSON对象，如果你需要遍历my_table中所有符合条件的parent_id，需要修改为用jsonb_agg聚合所有结果为JSON数组，再用jsonb_array_elements遍历数组元素，避免遗漏数据。

修正后可运行代码 #

如果需要处理查询返回的多行结果，使用以下版本：

DO
$BODY$
DECLARE
    js jsonb := '{"ddd525b9-e47c-46ac-ab55-e0db93338ff3": "ddd525b9-e47c-46ac-ab55-e0db93338ff4", "550e8400-e29b-41d4-a716-446655440000": "e15c6fe3-000c-4c69-9c41-9e241323cbe5", "0ba26163-bf75-40d2-8526-172229604d95": "7ed83dbc-3895-490c-8a16-9842cfe85e28"}';
    i record;
BEGIN
  FOR i IN SELECT * FROM jsonb_each_text(js)
  LOOP
    -- 嵌套块必须遵循DECLARE + BEGIN/END的结构
    DECLARE
      orgs jsonb := (SELECT jsonb_agg(row_to_json(t)) FROM (SELECT parent_id FROM my_schema.my_table o WHERE o.id != i.value) t);
      j jsonb;
    BEGIN
      FOR j IN SELECT * FROM jsonb_array_elements(orgs)
      LOOP
        RAISE NOTICE 'key parent_id';
        RAISE NOTICE 'value %', j->>'parent_id';
      END LOOP;
    END;
  END LOOP;
END;
$BODY$;

如果仅需要处理单行查询结果，只修复语法问题可以使用以下简化版本：

DO
$BODY$
DECLARE
    js jsonb := '{"ddd525b9-e47c-46ac-ab55-e0db93338ff3": "ddd525b9-e47c-46ac-ab55-e0db93338ff4", "550e8400-e29b-41d4-a716-446655440000": "e15c6fe3-000c-4c69-9c41-9e241323cbe5", "0ba26163-bf75-40d2-8526-172229604d95": "7ed83dbc-3895-490c-8a16-9842cfe85e28"}';
    i record;
BEGIN
  FOR i IN SELECT * FROM jsonb_each_text(js)
  LOOP
    DECLARE
      orgs jsonb := (SELECT row_to_json(t) FROM (SELECT parent_id FROM my_schema.my_table o WHERE o.id != i.value LIMIT 1) t);
      j record;
    BEGIN
      FOR j IN SELECT * FROM jsonb_each_text(orgs)
      LOOP
        RAISE NOTICE 'key %', j.key;
        RAISE NOTICE 'value %', j.value;
      END LOOP;
    END;
  END LOOP;
END;
$BODY$;

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