cursor empty

On 05/08/2018 05:39 AM, Philipp Kraus wrote:

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?

I am going to say:

perform pivottable( ...

https://www.postgresql.org/docs/10/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-NORESULT

"Sometimes it is useful to evaluate an expression or SELECT query but
discard the result, for example when calling a function that has
side-effects but no useful result value. To do this in PL/pgSQL, use the
PERFORM statement:

PERFORM query;

This executes query and discards the result. ..."
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Thanks

Phil

--
Adrian Klaver
adrian.klaver@aklaver.com

On Tue, May 8, 2018 at 6:36 AM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

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

Can you explain me, which part is wrong?

I am going to say:

perform pivottable( ...

https://www.postgresql.org/docs/10/static/plpgsql-statements
.html#PLPGSQL-STATEMENTS-SQL-NORESULT

"Sometimes it is useful to evaluate an expression or SELECT query but
discard the result, for example when calling a function that has
side-effects but no useful result value. To do this in PL/pgSQL, use the
PERFORM statement:

PERFORM query;

This executes query and discards the result. ..."
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

​It discards the results but not the side-effect, which in this case is
creating a named portal in the session. While the name of the portal, as
returned by the function, is indeed lost the portal still exists, with the
same name, and since the name is known by the caller anyway the attempt to
make use of the portal succeeds (no failure and the column structure is
known). Maybe you are right about the contents of the portal being lost
due to the perform but that behavior isn't evident from the the section you
quote.

David J.

Am 08.05.2018 um 16:18 schrieb David G. Johnston <david.g.johnston@gmail.com<mailto:david.g.johnston@gmail.com>>:

On Tue, May 8, 2018 at 6:36 AM, Adrian Klaver <adrian.klaver@aklaver.com<mailto:adrian.klaver@aklaver.com>> wrote:

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

Can you explain me, which part is wrong?

I am going to say:

perform pivottable( ...

https://www.postgresql.org/docs/10/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-NORESULT

"Sometimes it is useful to evaluate an expression or SELECT query but discard the result, for example when calling a function that has side-effects but no useful result value. To do this in PL/pgSQL, use the PERFORM statement:

PERFORM query;

This executes query and discards the result. ..."
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

​It discards the results but not the side-effect, which in this case is creating a named portal in the session. While the name of the portal, as returned by the function, is indeed lost the portal still exists, with the same name, and since the name is known by the caller anyway the attempt to make use of the portal succeeds (no failure and the column structure is known). Maybe you are right about the contents of the portal being lost due to the perform but that behavior isn't evident from the the section you quote.

Thanks you both for this hint with the „discard“ :-)
I have modified my pivot-table function so that it returns the cursor directly, see my Gist: https://gist.github.com/flashpixx/66fb3dcd557cc9e93eb22ba692085622
After the pivot-function is run I can fetch all the cursor data.

Phil