Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



	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






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux