i too don't know i too don't know - 1 month ago 5
JSON Question

Can anybody help be for below link modified cursor

i modified my function to execute some json object

cursor loop in 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

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

Answer

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.

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

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