I am trying to update multiple fields from JSONB but getting error like cannot call_populate composite on an array.

I have written below code:-

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 $$;

CodePudding user response：

There are three things to touch.

• Your JSON syntax is invalid, "nw_schema":public must be quoted;
• jsonb_to_record shall become jsonb_to_recordset;
• Expressions like i->>schema_name shall become i.schema_name.

So here it is corrected:

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 $$;