在Psycopg2存储过程中无法遍历嵌套字典的问题排查

我尝试通过psycopg2中的存储过程，将列表内嵌套字典中的值插入Postgres表。以下是从JSON应用获取的数据结构：

[
    {
        "team_id": 236,
        "lineup": [
            {
                "player_id": 3043,
                "country": {
                    "id": 61,
                    "name": "Denmark"
                }
            }
        ]
    },
    {
        "team_id": 237,
        "lineup": [
            {
                "player_id": 3045,
                "country": {
                    "id": 62,
                    "name": "Italy"
                }
            }
        ]
    }
]

我需要将球员的country信息插入Postgres的country表，其中id为integer类型，name为VARCHAR类型。我的存储过程代码如下：

CREATE PROCEDURE insert_country_by_lineups(data JSON)
AS $$
BEGIN
    FOR team IN SELECT * FROM json_array_elements(data) LOOP
        FOR player in SELECT * FROM json_array_elements(team->'lineup') LOOP
            INSERT INTO country(id,name) 
            VALUES (CAST(player->'country'->>'id' AS integer), player->'country'->>'name')
            ON CONFLICT DO NOTHING RETURNING id;
        END LOOP;
    END LOOP; 
END;
$$ LANGUAGE plpgsql;

但执行该存储过程时，我遇到了如下错误：

loop variable of loop over rows must be a record variable or list of scalar variables
LINE 5:         FOR team IN SELECT * FROM json_array_elements(data...

错误根源是PL/pgSQL的循环变量未声明类型：FOR team IN ...中的team以及内层的player没有被显式声明为record类型，PL/pgSQL无法自动推断变量类型，因此抛出报错。

以下两种方案可以解决问题：

方案一：显式声明循环变量为record类型 #

在BEGIN块前添加变量声明，指定team和player为record类型，同时可以移除无意义的RETURNING id（循环中未接收返回值）：

CREATE PROCEDURE insert_country_by_lineups(data JSON)
AS $$
DECLARE
    team record;
    player record;
BEGIN
    FOR team IN SELECT * FROM json_array_elements(data) LOOP
        FOR player in SELECT * FROM json_array_elements(team->'lineup') LOOP
            INSERT INTO country(id,name) 
            VALUES (CAST(player->'country'->>'id' AS integer), player->'country'->>'name')
            ON CONFLICT DO NOTHING;
        END LOOP;
    END LOOP; 
END;
$$ LANGUAGE plpgsql;

方案二：改用集合查询批量插入（更高效） #

嵌套循环的执行效率较低，推荐直接利用Postgres的JSON展开能力，通过集合查询一次性完成批量插入，完全避免循环变量的问题：

CREATE PROCEDURE insert_country_by_lineups(data JSON)
AS $$
BEGIN
    INSERT INTO country(id, name)
    SELECT 
        (player->'country'->>'id')::integer,
        player->'country'->>'name'
    FROM 
        json_array_elements(data) AS teams,
        json_array_elements(teams->'lineup') AS player
    ON CONFLICT DO NOTHING;
END;
$$ LANGUAGE plpgsql;

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