postgresql jsonb 解析 example
do $$ declare initData jsonb; initDataSize integer; item record; psetRoleMap jsonb; prmItem record; roleRecord record; pSetName varchar(100); pSetNameArray varchar[]; pId integer; psId integer; permset record; perm record; sysmodule record; mName varchar(100); modelId integer; begin initData:= '[ {"PermissionName":"总览","PermissionCode":"*****","ActionUrl":"*****","PermissionSetName":"总览","SysModuleName":"内部","SysModuleId":4}, {"PermissionName":"列表","PermissionCode":"*****","ActionUrl":"*****","PermissionSetName":"总览","SysModuleName":"内部","SysModuleId":4} ]'::jsonb; ; for item in select a.* from jsonb_to_recordset(initData) as a("PermissionName" varchar(100), "PermissionCode" varchar(100), "ActionUrl" varchar(500), "PermissionSetName" varchar(100), "SysModuleName" varchar(100), "SysModuleId" integer) loop RAISE NOTICE 'Begin Process Permission %', item."PermissionName"; RAISE NOTICE 'Finish Process Permission %', item."PermissionName"; end loop; psetRoleMap:= '[ { "RoleName": "AM", "PermissionSetName": "总览", "SysModuleId": "4" }]'::jsonb; for prmItem in select a.* from jsonb_to_recordset(psetRoleMap) as a("RoleName" varchar(100), "PermissionSetName" text, "SysModuleId" varchar(300)) loop RAISE NOTICE 'Process Role % PermissionSet', prmItem."RoleName"; foreach pSetName in array pSetNameArray loop end loop; end if; end loop; end $$;