我正在嘗試從 JSONB 更新多個欄位，但出現類似cannot call_populate composition on a array 的錯誤。
我寫了下面的代碼：-
do $$
<<myjsonb>>
declare
spec jsonb:=('[
{"schema_name":"public",
"table_name":"temp",
"nw_schema":public,
"nw_table": "temp",
"nw_col":"id"},
{"schema_name":"public",
"table_name":"temp",
"nw_schema":public,
"nw_table": "temp",
"nw_col":"name"}
]');
i record;
BEGIN
for i in SELECT * from jsonb_to_record(spec) as (schema_name text, table_name text, nw_schema text, nw_table text, nw_col text)
LOOP
update my_table set schema_name=i->>schema_name, table_name=i->>table_name where nw_schema=i->>nw_schema and nw_table=i->>nw_table and nw_col=i->>nw_col;
end loop;
end myjsonb $$;
uj5u.com熱心網友回復：
有三樣東西可以觸摸。
- 您的 JSON 語法無效，
"nw_schema":public必須加引號； jsonb_to_record將成為jsonb_to_recordset；- 類似的運算式
i->>schema_name將變為i.schema_name。
所以這里更正了：
do $$
declare
spec jsonb:='[
{
"schema_name":"public",
"table_name":"temp",
"nw_schema":"public",
"nw_table": "temp",
"nw_col":"id"
},
{
"schema_name":"public",
"table_name":"temp",
"nw_schema":"public",
"nw_table": "temp",
"nw_col":"name"
}
]';
i record;
begin
for i in select * from jsonb_to_recordset(spec) as (schema_name text, table_name text, nw_schema text, nw_table text, nw_col text)
loop
update my_table
set schema_name = i.schema_name, table_name = i.table_name
where nw_schema = i.nw_schema and nw_table = i.nw_table and nw_col = i.nw_col;
end loop;
end $$;
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/383703.html