Обсуждение: Can PostgreSQL 8.x Return Multiple "Result Sets" ?

Can PostgreSQL 8.x Return Multiple "Result Sets" and, if so, can
someone in the know provide or at least sketch the equivalent of the
SQL Server stored proc listed below?

------------------------------------------------------------------------------
Consider the following explanation c/w SQL Server stored procedure:

The getProject procedure retrieves the information for a single
project. This is a relatively complex proposition, since a Project
object needs to retrieve not only the core project data, but also the
list of resources assigned to the project.

This could be done by making two stored procedures and calling both of
them to populate the
business objects, but this can be reduced to a single database call by
putting both SELECT statements in a single stored procedure. The stored
procedure will then return two result sets, which can be read within
the business object's code:

CREATE PROCEDURE getProject(@id uniqueidentifier)
AS
  SELECT Id, Name, Started, Ended, Description, LastChanged
  FROM Projects
    WHERE Id=@id

  SELECT ResourceId, LastName, FirstName, Assigned, Role,
               Assignments.LastChanged AS LastChanged
  FROM Resources,Assignments
    WHERE ProjectId=@id AND ResourceId=Id

RETURN

So, in M$-land, after processing the returned 1st result set (using
DataReader), dataReader.NextResult() is called and then the 2nd result
set is processed by the caller of the stored proc.

willfarnaby <willfarnaby@hotmail.com> wrote:
> Can PostgreSQL 8.x Return Multiple "Result Sets" and, if so, can
> someone in the know provide or at least sketch the equivalent of the
> SQL Server stored proc listed below?

Essentially, yes.  You can return a row containing some fixed number
of REFCURSORs, or if you don't know in advance how many you'll get,
you can make a set-returning function that returns them one at a time
until done.

Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Courage is the first of human qualities because it is the quality
which guarantees all others.
                                                Winston Churchill