Skip site navigation (1) Skip section navigation (2)

Re: Set of cursors from database

From: Kris Jurka <books(at)ejurka(dot)com>
To: student23 <student23(at)o2(dot)pl>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Set of cursors from database
Date: 2006-09-11 17:42:54
Message-ID: Pine.BSO.4.63.0609111241020.8730@leary2.csoft.net (view raw or flat)
Thread:
Lists: pgsql-jdbc

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


In response to

pgsql-jdbc by date

Next:From: Marc HerbertDate: 2006-09-12 14:31:40
Subject: Re: about monitoring the input stream
Previous:From: Albert CardonaDate: 2006-09-11 16:59:50
Subject: about monitoring the input stream

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group