Thread: cursor empty

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

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