以下是PostgreSQL 9.0中的游标代码。我想通过连接多个表来获取记录,并从该连接中获取JSON数据。
所以我想循环这些记录并使用类似查询的方式解析那个json
SELECT "Dump"->'activities-steps'->0->'value' as "steps"
FROM "ActivitySessionDump" where "Id"=42594321345021288
然后我需要从这个查询中获取数据并插入到其他表格中,例如:
insert to table name (key,value);
所以我准备了一个只读游标来完成这个任务。
begin work;
DECLARE
sessionids INSENSITIVE no scroll CURSOR FOR
SELECT asn."Id",asn."UserId",asn."ActivityId",ad."Dump"
FROM "ActivitySession" as asn inner join "ActivitySessionDump" as ad
on asn."Id"=ad."ActivitySessionId"
where asn."CreatedAt" between now() - interval '5 hours' and now() and asn."ActivityId"=1
for read only;
---- i want her loop should start and i will parse a json Dump by executing query--------
--------insert record to another table---------------
---end loop-----------
FETCH next FROM sessionids;
CLOSE sessionids;
COMMIT WORK;
任何帮助都非常感激。谢谢。