Cursor seems to return an empty set?

by

When executing the following Postgres script in pgAdmin I see an empty result set.

create OR replace function xtest(inout rc refcursor) 
language plpgsql 
as $$
begin 
   open rc for select unnest('{1,2,3}'::int2[]) id;
end;
$$;

begin;
select * from xtest('a');
fetch all from a;
close a;
commit;

Shouldn’t this return rows “1,2,3”?
What sort of adjustments does it need?

  • Works for me: dbfiddle.uk/_dkZGut1

    – 

  • @ErwinBrandstetter you mean the script works on your machine ?

    – 

  • Yes. As well as in the given fiddle.

    – 

  • in #pgadmin an empty result set is returned

    – 

  • I tested with pgAdmin and it works as expected. You need to stay within the same transaction, and after FETCH ALL, the next FETCH is empty, of course. I rarely use explicit cursors. Cases where those are the best solution are far and few between.

    – 




Works for me. Locally as well as in this fiddle.
Works using pgAdmin, too, of course.

I am pretty sure the confusion arises from the fact that pgAdmin only displays the result of the last executed command. So if you execute the whole block, you get an empty result from the last command, which is commit in your example.
pgAdmin suffers from a minor bug in this regard: since COMMIT does not return data, the most recently used column name(s) (“id” in the example) is displayed with empty result.

Solution:

Execute one command at a time. The explicit BEGIN starts a transaction, which persists until closed.

Leave a Comment