From: "Steve - DND" <postgres@digitalnothing.com>
To: <pgsql-sql@postgresql.org>
Subject: Pagination with Output Variables?
Date: Tue, 22 Feb 2005 16:29:30 -0700
Message-ID: <LDEHKBBOEMIJKHKBOFNFOENBLKAA.postgres@digitalnothing.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Importance: Normal

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