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

  

posted @ 2022-04-12 14:44  PanPan003  阅读(114)  评论(0编辑  收藏  举报