Обсуждение: Set of cursors from database
Hi, I'm want to retrieve a set of cursors but with only one call to database. I was wondering about how to write such functionin postgres and get the results in java. Have anyone some experience in doing such thing??
On Thu, 31 Aug 2006, student23 wrote: > I'm want to retrieve a set of cursors but with only one call to > database. I was wondering about how to write such function in postgres > and get the results in java. > There are two approaches to doing this. You can either return a SETOF refcursor or you can return multiple refcursors as OUT parameters. Building on the example from the documentation for how to call this from java the two following functions should be helpful: http://jdbc.postgresql.org/documentation/81/callproc.html#callproc-resultset-refcursor CREATE FUNCTION setofcursors() RETURNS SETOF refcursor AS ' DECLARE curs1 refcursor; curs2 refcursor; BEGIN OPEN curs1 FOR SELECT 1; RETURN NEXT curs1; OPEN curs2 FOR SELECT 2; RETURN NEXT curs2; RETURN; END; ' LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION outcursors(curs1 OUT refcursor, curs2 OUT refcursor) AS ' BEGIN OPEN curs1 FOR SELECT 1; OPEN curs2 FOR SELECT 2; RETURN; END; ' LANGUAGE plpgsql; Kris Jurka