pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Pagination with Output Variables?
2+ messages / 2 participants
[nested] [flat]

* Pagination with Output Variables?
@ 2005-02-22 23:29 Steve - DND <postgres@digitalnothing.com>
  2005-02-23 00:26 ` Re: Pagination with Output Variables? Michael Fuhr <mike@fuhr.org>
  0 siblings, 1 reply; 2+ messages in thread

From: Steve - DND @ 2005-02-22 23:29 UTC (permalink / raw)
  To: 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





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Pagination with Output Variables?
  2005-02-22 23:29 Pagination with Output Variables? Steve - DND <postgres@digitalnothing.com>
@ 2005-02-23 00:26 ` Michael Fuhr <mike@fuhr.org>
  0 siblings, 0 replies; 2+ messages in thread

From: Michael Fuhr @ 2005-02-23 00:26 UTC (permalink / raw)
  To: Steve - DND <postgres@digitalnothing.com>; +Cc: pgsql-sql

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/



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2005-02-23 00:26 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-22 23:29 Pagination with Output Variables? Steve - DND <postgres@digitalnothing.com>
2005-02-23 00:26 ` Michael Fuhr <mike@fuhr.org>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox