Pagination with Output Variables?

From: "Steve - DND" <postgres(at)digitalnothing(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Pagination with Output Variables?
Date: 2005-02-22 23:29:30
Message-ID: LDEHKBBOEMIJKHKBOFNFOENBLKAA.postgres@digitalnothing.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Michael Fuhr 2005-02-23 00:26:57 Re: Pagination with Output Variables?
Previous Message Joel Fradkin 2005-02-22 22:19:55 Speeds using a transaction vrs not