Thread: pl/pgsql array return

-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Jose Luis LG
Sent: Wednesday, February 13, 2002 9:25 AM
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] pl/pgsql array return

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

 

> 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.

You can return an array value from plpgsql, no problem.  The problem is
that plpgsql is pretty weak on functionality for constructing an array
value on the fly.  I think the only way that would work is to build up
a textual representation of an array literal ('{foo,bar,baz}') and then
rely on runtime type conversion to do the right thing when you try to
RETURN the text string from a function declared as returning int[] or
whatever.

You might have better luck doing this in pltcl or plperl, either of
which have string-mashing facilities far superior to plpgsql.

            regards, tom lane

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
>
>
>

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

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
>
>