Skip site navigation (1) Skip section navigation (2)

Can PostgreSQL 8.x Return Multiple "Result Sets" ?

From: "willfarnaby" <willfarnaby(at)hotmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Can PostgreSQL 8.x Return Multiple "Result Sets" ?
Date: 2006-06-14 06:18:04
Message-ID: 1150265884.310043.118310@y41g2000cwy.googlegroups.com (view raw or flat)
Thread:
Lists: pgsql-novice
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=(at)id

  SELECT ResourceId, LastName, FirstName, Assigned, Role,
               Assignments.LastChanged AS LastChanged
  FROM Resources,Assignments
    WHERE ProjectId=(at)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.


Responses

pgsql-novice by date

Next:From: David FetterDate: 2006-06-15 00:58:09
Subject: Re: Can PostgreSQL 8.x Return Multiple "Result Sets" ?
Previous:From: Michael GlaesemannDate: 2006-06-14 04:24:16
Subject: Re: order by desc - with zeros on top

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group