有人能帮忙为以下链接修改光标吗

i modified my function to execute some json object

我修改了函数以执行一些json对象

cursor loop in PostgreSQL

在PostgreSQL游标循环

i have query which returns below result

我有一个查询，在结果下面返回

SELECT asn."Id",asn."UserId",asn."ActivityId",pd."DataSourceId",ad."Dump"
   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



Id                        UserId    ActId   DId  Dump
42594890910270849   42031336811660529   1   1   {"activities-steps":[{"dateTime":"2016-10-17","value":"4023"}]}
42595097786975653   42031336811660529   1   1   {"activities-steps":[{"dateTime":"2016-10-17","value":"4023"}]}
42597154682570183   42031336811660529   1   1   {"activities-steps":[{"dateTime":"2016-10-17","value":"5388"}]}
42602824351352325   42031336811660529   1   1   {"activities-steps":[{"dateTime":"2016-10-18","value":"0"}]}
42605659177354793   42031336811660529   1   1   {"activities-steps":[{"dateTime":"2016-10-18","value":"0"}]}
42611328851117671   42031336811660529   1   1   {"activities-steps":[{"dateTime":"2016-10-18","value":"0"}]}

i wrote one function below

我在下面写了一个函数

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"
   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;
--titles := titles || ',' || rec."Dump";
 EXECUTE 'SELECT rec."Dump"::json#>''{activities-steps,0}''->>''value''' INTO jsonrec;
titles := titles || ',' || jsonrec."steps";
end loop;
return titles;
END;
$$ LANGUAGE plpgsql;

but i am unable to execute

但我不能执行

EXECUTE 'SELECT rec."dump"::json#>''{activities-steps,0}''->>''value''' INTO jsonrec;

rec."dump" here is equal to {"activities-steps":[{"dateTime":"2016-10-17","value":"4023"}]}, so my query is like

矩形。这里的“dump”等于{“activity -steps”:[{“dateTime”:“2016-10-17”，“value”:“4023”}]}，所以我的查询是这样的

SELECT '{"activities-steps":[{"dateTime":"2016-10-17","value":"4023"}]}'::json#>'{activities-steps,0}'->>'value' as steps;

but i am unable to execute this inside function error is:

但我无法执行这个内部函数错误是:

Error in query: ERROR: missing FROM-clause entry for table "rec"
LINE 1: SELECT rec."Dump"::json#>'{activities-steps,0}'->>'value'
QUERY: SELECT rec."Dump"::json#>'{activities-steps,0}'->>'value'
CONTEXT: PL/pgSQL function thirdpartydataparse() line 19 at EXECUTE statement

1 个解决方案

#1

You cannot include a reference to a PL/pgSQL variable into the command string. The SQL parser does not know these variables, so it assumes that you are asking for an attribute "Dump" of a table rec and then complains if there is no FROM clause with that table.

不能将对PL/pgSQL变量的引用包含到命令字符串中。SQL解析器不知道这些变量，因此它假定您正在请求表rec的一个属性“Dump”，然后如果该表没有FROM子句，就会报错。

You need to use EXECUTE ... USING like this:

你需要使用EXECUTE…使用这样的:

EXECUTE 'SELECT $1::json#>''{activities-steps,0}''->>''value'''
   INTO jsonrec USING rec."Dump";