Re: example of using java to fetch from a result set returned from a plpgsql function (in 7.2)

Jose,

I have included a sample program that creates a plpgsql function that
returns a cursor, then shows how the returned cursor can be used in java
to fetch a set of results.

While cursors are intended to return results from a regular query, this
example shows how you can use 'select' and 'union all' to return a set
of records that have nothing to do with any tables.  The example shows
the function returning three records with each record containing two values.

thanks,
--Barry




Jose Luis LG wrote:
> Thanks Barry,
>
> I have tried this with no luck.  I am very new pl/pgsql.  I get a error
> on the very first line of the function.  The function is:
>
>     CREATE FUNCTION testFunction() RETURNS CURSOR AS '
>             ....
>
> Is this the correct way?  Could you send me an example?
>
> Thanks in advance
>
>
> Jose Luis
>
>
>>-----Original Message-----
>>From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-
>>owner@postgresql.org] On Behalf Of Barry Lind
>>Sent: miércoles, 13 de febrero de 2002 18:40
>>To: Jose Luis LG
>>Cc: pgsql-jdbc@postgresql.org
>>Subject: Re: [JDBC] pl/pgsql array return
>>
>>Jose,
>>
>>pl/pgsql can't return an array.  However in 7.2 it can return a
>>
> cursor.
>
>>  So it is possible to do the following in jdbc:
>>
>>foo() is a pl/pgsql function that returns a cursor (see pl/pgsql doc
>>
> for
>
>>7.2 to see how this is done in pl/pgsql).
>>
>>
>>ResultSet l_cursorRSet = dbcon.executeQuery("select foo()");
>>
>>l_cursorRSet.next();
>>String l_cursor = l_cursorRSet.getString(1);
>>
>>ResultSet l_functionResults = dbcon.executeQuery("fetch all from " +
>>l_cursor);
>>
>>while (l_functionResults.next()) {
>>   //do something useful with that data
>>}
>>
>>//should close result sets and close the cursor when done
>>...
>>
>>
>>thanks,
>>--Barry
>>
>>
>>
>>Jose Luis LG wrote:
>>
>>>Hi,
>>>
>>>
>>>
>>>I hope someone can help me.  Is it possible to return an array of
>>>results from a query in a function in pl/pgsql and get this result
>>>
> via
>
>>>the jdbc.  Could someone send me an example of how this is done.
>>>
>>>
>>>
>>>
>>>
>>>Thanks
>>>
>>>
>>>
>>>Jose Luis
>>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>