PostgreSQL一句sql解析微信公众号基础消息(XML格式)

直接使用

其中XML字符串就是微信公众号推送至您的服务的消息

select * from xmltable('xml' passing '<xml><ToUserName><![CDATA[gh_xxxxxx]]></ToUserName>
<FromUserName><![CDATA[o_2xxxxxx-Eus]]></FromUserName>
<CreateTime>1629256215</CreateTime>
<MsgType><![CDATA[event]]></MsgType>
<Event><![CDATA[VIEW]]></Event>
<EventKey><![CDATA[xxxxxx]]></EventKey>
<MenuId>418339369</MenuId>
</xml>'  columns 
				"ToUserName" text,
				 "FromUserName" text,
				 "CreateTime" bigint,
				 "MsgType" text,							--消息类型,值范围[text,image,voice,video,shortvideo,location,link,event]
				 "Content" text,							--文本消息内容
				 "MsgId" bigint,							--消息id，64位整型,[只有MsgType为ext,image,voice,video,shortvide才有效]
				 "PicUrl" text,								--图片链接（由系统生成）,微信临时保存,需要自己下载保存
				 "MediaId" text,							--媒体id，可以调用获取临时素材接口拉取数据。
				 "Format" text,								--语音格式，如amr，speex等
				 "Recognition" text,						--语音识别结果，UTF8编码
				 "ThumbMediaId" text,				--视频消息缩略图的媒体id，可以调用多媒体文件下载接口拉取数据。
				 "Location_X" float8,					--地理位置纬度
				 "Location_Y" float8,					--地理位置经度
				 "Scale" integer,							--地图缩放大小
				 "Label" text,								--地理位置信息
				 "Title" text,									--消息标题
				 "Description" text,						--消息描述
				 "Url" text,									--消息链接
				 "Event" text,								--事件类型，subscribe(订阅)、unsubscribe(取消订阅),SCAN,LOCATION,CLICK,VIEW
				 "EventKey" text,							--事件KEY值
				 "Ticket" text,								--二维码的ticket，可用来换取二维码图片
				 "Latitude" float8,						--地理位置纬度
				 "Longitude" float8,						--地理位置经度
				 "Precision" float8,						--地理位置精度
				 "MenuId" bigint							--菜单编号
);

存储微信消息后使用

收到微信公众号推送的消息后存储在wxAutoMessage表中

create table wxAutoMessage(
	objectid bigint not null,															--唯一编号
	context xml,																			--描述
	gen timestamptz default(now()) not null,
	constraint pk_wxAutoMessage_objectid primary key (objectid)  with (fillfactor=100) using index tablespace idxxsbn
) with (fillfactor=100,
		autovacuum_enabled=true,toast.autovacuum_enabled=true,
		autovacuum_vacuum_threshold=100,autovacuum_analyze_threshold=200,
		toast.autovacuum_vacuum_threshold=100);

然后根据gen的时间段解析消息.

drop type if exists ctWxMsg;

create type ctWxMsg as(
	tousername text,fromusername text,createtime bigint,msgtype text,
	content text,msgid bigint,picurl text,mediaid text,
	format text,recognition text,thumbmediaid text,location_x float8,
	location_y float8,scale float8,label text,title text,
	description text,url text,event text,eventkey text,
	ticket text,latitude float8,longitude float8,precision float8,
	menuid bigint
);

/*
https://developers.weixin.qq.com/doc/offiaccount/Message_Management/Receiving_standard_messages.html
https://developers.weixin.qq.com/doc/offiaccount/Message_Management/Receiving_event_pushes.html
*/
with cte as(
	select 
		(
			select 
				("ToUserName","FromUserName","CreateTime","MsgType",
					"Content","MsgId","PicUrl","MediaId",
					"Format","Recognition","ThumbMediaId","Location_X",
					"Location_Y","Scale","Label","Title",
					"Description","Url","Event","EventKey",
					"Ticket","Latitude","Longitude","Precision",
					"MenuId"
				)::ctWxMsg as l
			from xmltable('xml' passing t1.context  columns 
						"ToUserName" text,
							"FromUserName" text,
							"CreateTime" bigint,
							"MsgType" text,							--消息类型,值范围[text,image,voice,video,shortvideo,location,link,event]
							"Content" text,							--文本消息内容
							"MsgId" bigint,							--消息id，64位整型,[只有MsgType为ext,image,voice,video,shortvide才有效]
							"PicUrl" text,								--图片链接（由系统生成）,微信临时保存,需要自己下载保存
							"MediaId" text,							--媒体id，可以调用获取临时素材接口拉取数据。
							"Format" text,								--语音格式，如amr，speex等
							"Recognition" text,						--语音识别结果，UTF8编码
							"ThumbMediaId" text,				--视频消息缩略图的媒体id，可以调用多媒体文件下载接口拉取数据。
							"Location_X" float8,					--地理位置纬度
							"Location_Y" float8,					--地理位置经度
							"Scale" integer,							--地图缩放大小
							"Label" text,								--地理位置信息
							"Title" text,									--消息标题
							"Description" text,						--消息描述
							"Url" text,									--消息链接
							"Event" text,								--事件类型，subscribe(订阅)、unsubscribe(取消订阅),SCAN,LOCATION,CLICK,VIEW
							"EventKey" text,							--事件KEY值
							"Ticket" text,								--二维码的ticket，可用来换取二维码图片
							"Latitude" float8,						--地理位置纬度
							"Longitude" float8,						--地理位置经度
							"Precision" float8,						--地理位置精度
							"MenuId" bigint							--菜单编号
			)
		)
	from wxAutoMessage as t1
)select 
	(l).tousername,(l).fromusername,(l).createtime,(l).msgtype,
	(l).content,(l).msgid,(l).picurl,(l).mediaid,
	(l).format,(l).recognition,(l).thumbmediaid,(l).location_x,
	(l).location_y,(l).scale,(l).label,(l).title,
	(l).description,(l).url,(l).event,(l).eventkey,
	(l).ticket,(l).latitude,(l).longitude,(l).precision,
	(l).menuid
from cte;