Working with pages of data (LIMIT/OFFSET keyword)

From: Mike Christensen <mike(at)kitchenpc(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Working with pages of data (LIMIT/OFFSET keyword)
Date: 2010-06-16 12:32:56
Message-ID: AANLkTilM-HhQJMmXOAltHcjJb2xOw7Vxpr0wMABJkOVs@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm generating a query on the fly to return a set of data, however I
only want to display 30 rows at a time to the user. For this reason,
I use the LIMIT 30 OFFSET x clause on the select statement. However,
I also want to know the total rows that match this query had there
been no limit, that way I can display to the user the total count and
the number of pages, and have Next/Prev buttons in my UI. I can think
of the following ways to do this:

1) When the page loads, execute two totally separate queries. One
that does the COUNT, and then another query immediately after to get
the desired page of data. I don't like this as much because the two
queries will execute in separate transactions and it'd be nice if I
could just perform a single SQL query and get all this information at
once. However, I will use this if there's no other way.

2) Execute two select statements in a single SQL query. The first
table will contain a single row and column with just the count, then
the second table will contain the results for the page. This should
work in practice, but I don't believe Npgsql supports the idea of a
single query returning multiple tables. Or this might be a Postgres
limitation, I'm not sure. MSSQL supports it, however.

3) Do something weird with query parameters or return values. I've
messed around with this, but I don't think parameters, variables, or
return values are supported outside a stored function call. Since I'm
generating my SQL statement on the fly, I'm not calling a function. I
could write a function that takes parameters for all the values,
however I'm JOIN'ing different tables depending on what the user is
searching for. For example, I don't join in the users table unless
they're filtering some sort of property of the user.

Anyone ran into this situation before? What's the best approach here. Thanks!

Mike

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Yeb Havinga 2010-06-16 12:58:48 Re: consequent PQsendQueryPrepared() failed: another command is already in progress
Previous Message Rob Richardson 2010-06-16 12:29:39 Dynamic triggers