Обсуждение: Paging Through Query Result Sets

Поиск
Список
Период
Сортировка

Paging Through Query Result Sets

От
Don Parris
Дата:
Hi all,

I would like to be able to page through a result set when there is more than one screenful of data.  Is there a good example of paging through a query result set in Bash - something like implementing the less command?  Even if I can't scroll backwards through the result set, at least being able to control the paging forward would be good.

I have seen the notes on fetchmany(), which almost seems to be what I want.  Except that fetchmany(25) will fetch 25 records and that's about it.  Maybe I just need to change the way I code it...

Active State used to have some neat snippets I was able to take advantage of in the past (for working with text files), and I was looking at this:
http://code.activestate.com/recipes/137270-use-generators-for-fetching-large-db-record-sets/

But I am not certain it really does what I am looking for.  Still, I am fairly certain there is an example of this somewhere I can look at to see how it is implemented.

Thoughts?

--
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
GPG Key ID: F5E179BE

Re: Paging Through Query Result Sets

От
Joe Abbate
Дата:
Hello Don,

On 19/04/13 18:38, Don Parris wrote:
> I would like to be able to page through a result set when there is more
> than one screenful of data.  Is there a good example of paging through a
> query result set in Bash - something like implementing the less
> command?  Even if I can't scroll backwards through the result set, at
> least being able to control the paging forward would be good.

In web applications, paging is usually done by using PG's LIMIT and
OFFSET.  So if you're displaying the third page of 200 rows 30 rows at a
time, your SELECT would end with LIMIT 30 OFFSET 90.

If I may toot my horn, I've implemented this logic in Pyrseas.  If
you're interested see the subset method in:

https://github.com/jmafc/Pyrseas/blob/master/pyrseas/relation/join.py

Regards,

Joe

Re: Paging Through Query Result Sets

От
Karsten Hilbert
Дата:
On Fri, Apr 19, 2013 at 06:38:32PM -0400, Don Parris wrote:

> I would like to be able to page through a result set when there is more
> than one screenful of data.  Is there a good example of paging through a
> query result set in Bash - something like implementing the less command?

If Bash really is your implementation language one of your
best bets may be to download the entire resultset into a
text file and *actually* less'ing through that.

Other than that, something akin to a local file may be
constructed by judicious use of pipes.

    psql ... -c 'select interesting rows;' | less

should do the trick, no ?

Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346