函数的参数传入值为json格式的字符串，通过遍历，获取某个字段值。之后进行处理。
下面的示例中，
p_Data进行了赋值，数组长度是2.
{"data": [{"_id": "fbc0sdfsdfdgdf","Name": "手环4492","PWD": "123456","SIM": "","MID": "68946470303dsg4492","FAC": "XT","Model": "T6 Pro","ImgID": "e476005866sdfa2a6407502b3","RT": "2020-04-18 16:57:31","Time": "2020-07-17 15:10:59","Tag": "happy","B": 100,"WID": "d7947802","PEDO": 0,"Step": 0,"Profile": 2,"SL": "","Admin": "sdfgreytr","Guarder": "1890120526309","Itvl": 1,"LowBat": 1,"AR": 1,"Wear": 0,"PID": "JX","DCT": 5,"FL": 4403675,"Auth": 1,"T": 0,"Keybrd": 1,"UT": "2020-07-27 14:22:11","FUT": "2020-06-22 14:54:12","Host": "10-9-116-82","PMT": "2020072711","SocTS": 1595830678,"GuarderFail": null,"SOS": "18901205263","SOSFail": null,"PSL": "18901205263","PNL": "神兽","PHLFail": null,"SR": 1,"SNum": 7,"SDay": "20200727","PUT": "2020-07-27 14:22:05","GSM": 100,"W": 36,"WTime": "13:47","WT": {"T": 1595828764797,"Lon": 116.368944,"Lat": 40.060245,"B": "WT,20-07-27,13:46:04,d2f5,1,24,-1,40,53174EAC5E02"},"HWf": 1,"FQCY": 60,"HGps": 1,"ETWD": 230720,"UID": "fbc09a5ea974f60d4713ff9f","Pro": "北京市","City": "北京市","Dist": "海淀区","Str": "建材城东路","Lon": 116.3690191,"Lat": 40.0602989,"Radius": 50,"Desc": ""}],"Ret": "Succ"
}
CREATE OR REPLACE FUNCTION "esys"."fntest123"()RETURNS "pg_catalog"."void" AS $BODY$ BEGIN-- Routine body goes here...DECLAREp_Data VARCHAR (4000);ppp varchar(1000);lll varchar(1000);tempdata varchar;rs record;BEGINp_Data := '[{"_id":"fbc09a5ea974f60d4713ff9f","Name":"手环4492","PWD":"123456","SIM":"","MID":"689464703034492","FAC":"XT","Model":"T6 Pro","ImgID":"e476005866e8a2a6407502b3","RT":"2020-04-18 16:57:31","Time":"2020-07-17 15:10:59","Tag":"happy","B":100,"WID":"d7947802","PEDO":0,"Step":0,"Profile":2,"SL":"","Admin":"4d17115f6d3a18b7152aec53","Guarder":"18901205263","Itvl":1,"LowBat":1,"AR":1,"Wear":0,"PID":"JX","DCT":5,"FL":4403675,"Auth":1,"T":0,"Keybrd":1,"UT":"2020-07-27 14:48:10","FUT":"2020-06-22 14:54:12","Host":"10-9-116-251","PMT":"2020072711","SocTS":1595832003,"SOS":"18901205263","PSL":"18901205263","PNL":"神兽","SR":1,"SNum":23,"SDay":"20200727","PUT":"2020-07-27 14:48:05","GSM":100,"W":36,"WTime":"13:47","WT":{"T":1595828764797,"Lon":116.368944,"Lat":40.060245,"B":"WT,20-07-27,13:46:04,d2f5,1,24,-1,40,53174EAC5E02"},"HWf":1,"FQCY":60,"HGps":1,"ETWD":230720,"UID":"fbc09a5ea974f60d4713ff9f","Pro":"北京市","City":"北京市","Dist":"海淀区","Str":"西三旗街道建材城东路二十一世纪大厦(建材城东路)","Lon":116.3685121,"Lat":40.061043,"Radius":75,"Desc":""},{"_id":"fbc09a5ea974f60d4713ff9f","Name":"手环4492","PWD":"123456","SIM":"","MID":"689464703034492","FAC":"XT","Model":"T6 Pro","ImgID":"e476005866e8a2a6407502b3","RT":"2020-04-18 16:57:31","Time":"2020-07-17 15:10:59","Tag":"happy","B":100,"WID":"d7947802","PEDO":0,"Step":0,"Profile":2,"SL":"","Admin":"4d17115f6d3a18b7152aec53","Guarder":"18901205263","Itvl":1,"LowBat":1,"AR":1,"Wear":0,"PID":"JX","DCT":5,"FL":4403675,"Auth":1,"T":0,"Keybrd":1,"UT":"2020-07-27 14:48:10","FUT":"2020-06-22 14:54:12","Host":"10-9-116-251","PMT":"2020072711","SocTS":1595832003,"SOS":"18901205263","PSL":"18901205263","PNL":"神兽","SR":1,"SNum":23,"SDay":"20200727","PUT":"2020-07-27 14:48:05","GSM":100,"W":36,"WTime":"13:47","WT":{"T":1595828764797,"Lon":116.368944,"Lat":40.060245,"B":"WT,20-07-27,13:46:04,d2f5,1,24,-1,40,53174EAC5E02"},"HWf":1,"FQCY":60,"HGps":1,"ETWD":230720,"UID":"fbc09a5ea974f60d4713ff9f","Pro":"北京市","City":"北京市","Dist":"海淀区","Str":"西三旗街道建材城东路二十一世纪大厦(建材城东路)","Lon":116.3685121,"Lat":40.061043,"Radius":75,"Desc":""}]';for rs in (select "value" as single_value from json_array_elements_text(p_Data::JSON)) loopselect t1.pwd::text into ppp from (select (rs.single_value) ::JSON #>> '{PWD}' as pwd) as t1;raise notice 'pwd is %',ppp;select t1.pwd::text into ppp from (select (rs.single_value) ::JSON #>> '{WT}' as pwd) as t1;raise notice 'wt is %',ppp;select t1.pwd::text into lll from (select (rs.single_value) ::JSON #>> '{Lon}' as pwd) as t1;raise notice 'wt_lon %',lll;end loop;END;RETURN;END $BODY$LANGUAGE plpgsql VOLATILECOST 100
输出结果如下
注意: pwd is 123456
注意: wt is {"T":1595828764797,"Lon":116.368944,"Lat":40.060245,"B":"WT,20-07-27,13:46:04,d2f5,1,24,-1,40,53174EAC5E02"}
注意: wt_lon 116.3685121
注意: pwd is 123456
注意: wt is {"T":1595828764797,"Lon":116.368944,"Lat":40.060245,"B":"WT,20-07-27,13:46:04,d2f5,1,24,-1,40,53174EAC5E02"}
注意: wt_lon 116.3685121Procedure executed successfully时间: 0.021s