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
