CODEWITHSUNDEEP
postgresqlpgadmincursors
user1889017
user1889017

Reputation:

Cursor seems to return an empty set?

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?

Upvotes: 0

Views: 293

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658502

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.

Upvotes: 0

Related Questions