cursor empty

From: Philipp Kraus <philipp(dot)kraus(at)tu-clausthal(dot)de>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: cursor empty
Date: 2018-05-08 12:39:26
Message-ID: C65B4D42-269A-4881-9F37-22B81FE8EE28@tu-clausthal.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I have got a function with this definition:

CREATE OR REPLACE FUNCTION vectorize(refcursor)
RETURNS SETOF refcursor
LANGUAGE 'plpgsql'
COST 100
STABLE
ROWS 1000
AS $BODY$

begin
perform pivottable(
'_vector',
'select * from viewdata',
array['ideate', 'name', 'description', 'latitude', 'longitude'],
array['parametername'],
array['parametervalue::text', 'parametervaluetext']
);
open $1 scroll for select * from _vector;
return next $1;
end

$BODY$;

The perform call creates a dynamic column pivot table, if I run manually

select pivottable(
'_vector',
'select * from viewdata',
array['ideate', 'name', 'description', 'latitude', 'longitude'],
array['parametername'],
array['parametervalue::text', 'parametervaluetext']
);
select * from _vector;

I get all the data in the output, so everything is fine.

My goal is now to encapsulate the two lines into a function, so I define
a stable function and based on the dynamic column set a cursor. I get in
pgadmin the column names back, but the rows are empty if I run:

select * from vectorize('myvec');
fetch all from myvec;

Can you explain me, which part is wrong?
Thanks

Phil

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2018-05-08 13:36:08 Re: cursor empty
Previous Message Ayappan P2 2018-05-08 06:50:51 Re: Renice on Postgresql process