Thread: Pagination with Output Variables?

I have a MSSQL Stored Procedure that returns a particular section of a
resultset depending on the desired "page". This procedure also has a
parameter marked as OUTPUT which indicates the total number of records in
the actual resultset. Is there a way to do something similar in PG or does
TotalRecords need to be part of the type returned by the function.

MSSQL Procedure

CREATE PROCEDURE PageCustomers (   @Page int,   @PerPage int,   @TotalRecords int OUTPUT
) AS

--Do stuff to determine number of records
SELECT @TotalRecords = COUNT(*) FROM tmpCustomers

--Get the actual data
SELECT CustomerID, CustomerName, EmailAddress FROM tmpCustomers WHERE ...


Would the only way to do this in PG to have my function return a type like
this?
CREATE TYPE "PageCustomers_type"  AS ("TotalRecords" int4, "CustomerID" int4, "CustomerName" varchar(100),
"EmailAddress" varchar(100));

Can a function return two type results? So that the first type would be just
the total number of records, and the second type would be the resultset of
customers?

Thanks,
Steve

On Tue, Feb 22, 2005 at 04:29:30PM -0700, Steve - DND wrote:

> Can a function return two type results? So that the first type would be just
> the total number of records, and the second type would be the resultset of
> customers?

The function could return a set of cursors, one for the query that
returns the total number of records and one for the resultset.  The
8.0 documentation has an example that should work in earlier versions
as well (except for the dollar quoting):

http://www.postgresql.org/docs/8.0/static/plpgsql-cursors.html

See the example at the bottom of the page.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/