Horton
Menu

Thread

  1. Stored procedures/functions that can return recordsets...

    Peter Adamek Jr. <peter.adamek@utoronto.ca> — 2002-12-22T19:14:39Z

    Hello all,

This email is in regards to the following statement that I found just 
last week:

"PostgreSQL 7.3 is full of new, often requested features such as SQL '92 
schemas, prepared statements, and stored procedures that can return 
record sets."

The statement is part of a larger article and can be found about half 
way down the following article:

http://advocacy.postgresql.org/news/2002112801/

I am specifically interested in the portion of the statement that reads 
"stored procedures that can return record sets."  If this is indeed 
true, then I believe that PostgreSQL will become a much more used DB. 
This is one aspect that MS SQL Server and Oracle have had for some time 
that has been lacking from PostgreSQL (not to mention the Callable 
Statement JDBC driver support that is now included in version 7.3).

I am writing this to see if anyone has been able to successfully run a 
stored procedure (although it is probably still called a function) via 
Java (using a Callable Statement) and have it return a recordset (that 
is multiple columns and multiple rows).  If so, I would appreciate it if 
you could post a short example, both of the stored procedure and the 
Java code used to access it.

I have looked through the 7.3 documentation at the following link, but I 
have not found any references to this functionality.

http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/

I suspect that even in PostgreSQL version 7.3 no such thing as stored 
procedures exist - I suspect it was a misquote and the article is 
referring to functions.

In any event, I'd like to see this run for my own interest, it's not for 
a project.  I know what needs to be done in theory, so I'll tinker with 
it to try and get it to work on my own.  If anyone has any suggestions 
or has got this to work and would like to shed light on this, I would 
appreciate it.

Thank you kindly,
Pete

  2. Re: Stored procedures/functions that can return recordsets...

    Nic Ferrier <nferrier@tapsellferrier.co.uk> — 2002-12-22T23:00:48Z

    "Peter Adamek Jr." <peter.adamek@utoronto.ca> writes:

> I am writing this to see if anyone has been able to successfully run a
> stored procedure (although it is probably still called a function) via
> Java (using a Callable Statement) and have it return a recordset (that
> is multiple columns and multiple rows).  If so, I would appreciate it if
> you could post a short example, both of the stored procedure and the
> Java code used to access it.

If you had checked the archives you could have established that this
has not been done yet: it is possible to return record sets to java
code, but not via a CallableStatement.

I have written a patch that allows PostgreSQL to do that.

I'll send it to you if you want.


> I have looked through the 7.3 documentation at the following link, but I
> have not found any references to this functionality.
> 
> http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/
> 
> I suspect that even in PostgreSQL version 7.3 no such thing as stored
> procedures exist - I suspect it was a misquote and the article is
> referring to functions.

Stored procedures, stored functions... all the same. The generic term
for imperative code executed within the database server is "stored
proc". PostgreSQL's implementation of stored procs can return only one
value.



Nic

  3. Re: Stored procedures/functions that can return recordsets...

    Remigius Stalder <remigius.stalder@descom-consulting.ch> — 2002-12-23T16:50:12Z

    Peter,

The description of how to return record sets can be found here (sorry, but
although I am not directly interested in returning record sets, I looked
into the manual and found this passage in five minutes):

http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/plpgsql-cursors.
html

(near the bottom, section 19.7.3.3. Returning Cursors)
However, if that does not help, I can't provide further assistance.

By the way - what is the difference between "functions" and "procedures" ?
In my understanding, the PL/pgSQL functions ARE what is in other DBMSes
called stored procedures, although they are not called so. If this is not
the case, I would be interested in the difference of PL/pgSQL functions and
"real" stored procedures. Of course I don't mean differences in the
procedural language, because these languages are not standardized anywhay.

Best regards, Remigius.
----- Original Message -----
From: "Peter Adamek Jr." <peter.adamek@utoronto.ca>
To: <pgsql-jdbc@postgresql.org>; <pgsql-general@postgresql.org>
Sent: Sunday, December 22, 2002 8:14 PM
Subject: [JDBC] Stored procedures/functions that can return recordsets...


> Hello all,
>
> This email is in regards to the following statement that I found just
> last week:
>
> "PostgreSQL 7.3 is full of new, often requested features such as SQL '92
> schemas, prepared statements, and stored procedures that can return
> record sets."
>
> The statement is part of a larger article and can be found about half
> way down the following article:
>
> http://advocacy.postgresql.org/news/2002112801/
>
> I am specifically interested in the portion of the statement that reads
> "stored procedures that can return record sets."  If this is indeed
> true, then I believe that PostgreSQL will become a much more used DB.
> This is one aspect that MS SQL Server and Oracle have had for some time
> that has been lacking from PostgreSQL (not to mention the Callable
> Statement JDBC driver support that is now included in version 7.3).
>
> I am writing this to see if anyone has been able to successfully run a
> stored procedure (although it is probably still called a function) via
> Java (using a Callable Statement) and have it return a recordset (that
> is multiple columns and multiple rows).  If so, I would appreciate it if
> you could post a short example, both of the stored procedure and the
> Java code used to access it.
>
> I have looked through the 7.3 documentation at the following link, but I
> have not found any references to this functionality.
>
> http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/
>
> I suspect that even in PostgreSQL version 7.3 no such thing as stored
> procedures exist - I suspect it was a misquote and the article is
> referring to functions.
>
> In any event, I'd like to see this run for my own interest, it's not for
> a project.  I know what needs to be done in theory, so I'll tinker with
> it to try and get it to work on my own.  If anyone has any suggestions
> or has got this to work and would like to shed light on this, I would
> appreciate it.
>
> Thank you kindly,
> Pete
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

  4. Re: [JDBC] Stored procedures/functions that can return recordsets...

    Nic Ferrier <nferrier@tapsellferrier.co.uk> — 2002-12-23T18:50:27Z

    "Remigius Stalder" <remigius.stalder@descom-consulting.ch> writes:

> Peter,
> 
> The description of how to return record sets can be found here (sorry, but
> although I am not directly interested in returning record sets, I looked
> into the manual and found this passage in five minutes):
> 
> http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/plpgsql-cursors.
> html
> 
> (near the bottom, section 19.7.3.3. Returning Cursors)
> However, if that does not help, I can't provide further assistance.
> 
> By the way - what is the difference between "functions" and "procedures" ?
> In my understanding, the PL/pgSQL functions ARE what is in other DBMSes
> called stored procedures, although they are not called so. If this is not
> the case, I would be interested in the difference of PL/pgSQL functions and
> "real" stored procedures. Of course I don't mean differences in the
> procedural language, because these languages are not standardized anywhay.

The only difference I'm aware of is that many other dbms stored proc
implementations pass values by reference (OUT parameters in PL/SQL
parlance) thus allowing values to be changed.

This is possible (I seem to recall) with C based stored procs using
pgsql but none of the stored proc languages, including pg/plsql,
supports it.


Nic