在PostgreSQL中的游标循环 [英] cursor loop in PostgreSQL

问题描述

下面的代码是PostgreSQL 9.0中的一个游标。我想通过加入多个表来获取我的记录，我从该连接获取JSON数据。

This below code is a cursor in PostgreSQL 9.0. I want to fetch my records by joining more than one table and i am getting JSON data from that join.

所以我想循环这些记录，并使用查询像

So I want to loop those records and parse that json using query something like

SELECT "Dump"->'activities-steps'->0->'value' as "steps"
FROM "ActivitySessionDump" where "Id"=42594321345021288   

来自此查询的数据，并插入到某些其他表，如

then i have to get data from this query and insert to some other table like

insert to table name (key,value);

所以我准备了一个readonly游标来实现这个任务

So i prepared one readonly cursor to achieve this task

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;

任何帮助真的很感激。感谢

Any help really appreciated.Thanks

推荐答案

这里是我的问题的代码，我无法
EXECUTE'SELECT rec。Dump:: json＃>''{activities-steps，0} - >>''value''as steps'INTO jsonrec; line;

here is the code for my question and i am unable to EXECUTE 'SELECT rec."Dump"::json#>''{activities-steps,0}''->>''value'' as steps ' INTO jsonrec; line;

SELECT'{activities-steps：[{dateTime：2016-10-17，value：4023}]} ':: json＃>'{activities-steps，0}' - >>'value'as steps;
其中我可以在控制台中执行此代码。

SELECT '{"activities-steps":[{"dateTime":"2016-10-17","value":"4023"}]}'::json#>'{activities-steps,0}'->>'value' as steps; where as i can execute this code in console.

但在函数内部不能。

CREATE OR REPLACE FUNCTION ThirdPartyDataParse()
RETURNS text AS $$
DECLARE 
sessionid NO SCROLL CURSOR FOR SELECT asn."Id",asn."UserId",asn."ActivityId",pd."DataSourceId",ad."Dump"::TEXT
   FROM "Development"."ActivitySession" as asn inner join "Development"."PersonDataSource" as pd on pd."UserId" = asn."UserId" inner join "Development"."ActivitySessionDump" as ad 
   on asn."Id"=ad."ActivitySessionId" where asn."CreatedAt" between now() - interval '5 days' and now() and asn."ActivityId"=1  and pd."DataSourceId"=1 for read only;
titles TEXT DEFAULT '';
rec record;
jsonrec record;
 BEGIN
 OPEN sessionid;
loop

FETCH sessionid INTO rec;
--raise notice '%d',rec."UserId";
   if not found then
        exit ;
   end if;
 EXECUTE 'SELECT rec."Dump"::json#>''{activities-steps,0}''->>''value'' as steps ' INTO jsonrec;
titles := titles || ',' || jsonrec."steps";
end loop;
return titles;
END;
$$ LANGUAGE plpgsql;

这篇关于在PostgreSQL中的游标循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！