Обсуждение: 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
"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
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 >
"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