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.
