Re: Multiple result set to be returned in procedure/function

Thomas Kellerer wrote:

> arthur=> \set AUTOCOMMIT off

Alternatively, start an explicit transaction block with BEGIN.
The point is that the lifespan of the cursor is the transaction block
in which it's instantiated.

> arthur=> select * from get_results();
> get_results
> --------------------
> <unnamed portal 1>
> <unnamed portal 2>

Friendlier names may be used by assigning them in the function,
i.e. plpgsql does support:

declare
c1 refcursor := 'mycursorname';

Then the caller might simply hardcode the cursor names in the FETCH
statements rather than building them dynamically at runtime.

Also it allows to put the server-side code into an anymous DO block
instead of creating a function, because it doesn't have to return
any refcursor when the caller already knows the cursor names.

BEGIN;
DO $$
declare c refcursor := 'c_pgclass';
begin
open c for select relname from pg_class;
end $$ LANGUAGE plpgsql;

FETCH ALL from c_pgclass;
...
COMMIT;

This form might be closer to how inline blocks of code are
written with some other db engines, when they produce resultsets
without an explicit cursor interface.

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: https://www.manitou-mail.org
Twitter: @DanielVerite