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. 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.