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