| 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: | Whole Thread | Raw Message | 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
| 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 |