pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
how many rows? [was Re: fetching rows]
4+ messages / 4 participants
[nested] [flat]

* how many rows? [was Re: fetching rows]
@ 2000-11-17 09:01 Arnaud Vandyck <arnaud@ressource-toi.org>
  2000-11-18 00:03 ` Re: how many rows? [was Re: fetching rows] Robert B. Easter <reaster@comptechnews.com>
  2000-11-18 17:57 ` Re: how many rows? [was Re: fetching rows] Johann Spies <jhspies@adept.co.za>
  0 siblings, 2 replies; 4+ messages in thread

From: Arnaud Vandyck @ 2000-11-17 09:01 UTC (permalink / raw)
  To: pgsql-sql

"Robert B. Easter" wrote:
> 
> > you can't do that with a cursor, but you can use they mysql-ism called a
> > limit clause.  for example, to fetch rows 26-50 from that query, you'd
> > do:
> >
> > select * from films limit 25,26;
> >
> > or
> >
> > select * from files limit 25 offset 26;

I did know it was possible but did not know how to do, thanks (It was
not my question but I'm glad to see the solution:)

and how can I know how many rows are returned by the query?

--
Arnaud
( http://www.ressource-toi.org )



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

* Re: how many rows? [was Re: fetching rows]
  2000-11-17 09:01 how many rows? [was Re: fetching rows] Arnaud Vandyck <arnaud@ressource-toi.org>
@ 2000-11-18 00:03 ` Robert B. Easter <reaster@comptechnews.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Robert B. Easter @ 2000-11-18 00:03 UTC (permalink / raw)
  To: Arnaud Vandyck <arnaud@ressource-toi.org>; pgsql-sql

On Friday 17 November 2000 04:01, Arnaud Vandyck wrote:
> "Robert B. Easter" wrote:
> > > you can't do that with a cursor, but you can use they mysql-ism called
> > > a limit clause.  for example, to fetch rows 26-50 from that query,
> > > you'd do:
> > >
> > > select * from films limit 25,26;
> > >
> > > or
> > >
> > > select * from files limit 25 offset 26;
>
> I did know it was possible but did not know how to do, thanks (It was
> not my question but I'm glad to see the solution:)
>
> and how can I know how many rows are returned by the query?
>

I don't know exactly.  I don't know of any way to find the total number of 
rows in a cursor.  If you really need to know, you'll have to run a count(*) 
first, then make the cursor using the same select almost.  Once you get the 
count(*), you can then use MOVE and FETCH to get the page you want.

If someone has done it a better way, I'd like to hear how.  However, 
sometimes it is possible to cache a count(*) value somewhere in the database 
so it doesn't have to be found everytime - it depends on your database and 
what the select is if you can store the count in advance somehow.

> --
> Arnaud
> ( http://www.ressource-toi.org )

-- 
-------- Robert B. Easter  reaster@comptechnews.com ---------
- CompTechNews Message Board   http://www.comptechnews.com/ -
- CompTechServ Tech Services   http://www.comptechserv.com/ -
---------- http://www.comptechnews.com/~reaster/ ------------



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

* Re: how many rows? [was Re: fetching rows]
  2000-11-17 09:01 how many rows? [was Re: fetching rows] Arnaud Vandyck <arnaud@ressource-toi.org>
@ 2000-11-18 17:57 ` Johann Spies <jhspies@adept.co.za>
  1 sibling, 0 replies; 4+ messages in thread

From: Johann Spies @ 2000-11-18 17:57 UTC (permalink / raw)
  To: pgsql-sql

On Fri, Nov 17, 2000 at 10:01:28AM +0100, Arnaud Vandyck wrote:
> "Robert B. Easter" wrote:
> > 
> > > you can't do that with a cursor, but you can use they mysql-ism called a
> > > limit clause.  for example, to fetch rows 26-50 from that query, you'd
> > > do:
> > >
> > > select * from films limit 25,26;
> > >
> > > or
> > >
> > > select * from files limit 25 offset 26;
> 
> I did know it was possible but did not know how to do, thanks (It was
> not my question but I'm glad to see the solution:)
> 
> and how can I know how many rows are returned by the query?

If you use ruby's interface to postgresql you can use num_tuples to
determine how many rows are returned by the query.

Ruby is an exiting young language somewhat like python.

Johann.
-- 
J.H. Spies - Tel. 082 782 0336 / 023 55 11 568
     "A Song for the sabbath day. It is a good thing to 
      give thanks unto the LORD, and to sing praises unto 
      thy name, O most High."   Psalms 92:1 



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

* Re: how many rows? [was Re: fetching rows]]
@ 2000-11-18 00:11 Roberto Mello <rmello@cc.usu.edu>
  0 siblings, 0 replies; 4+ messages in thread

From: Roberto Mello @ 2000-11-18 00:11 UTC (permalink / raw)
  To: pgsql-sql

"Robert B. Easter" wrote:

> If someone has done it a better way, I'd like to hear how.  However,
> sometimes it is possible to cache a count(*) value somewhere in the database
> so it doesn't have to be found everytime - it depends on your database and
> what the select is if you can store the count in advance somehow.

	I don't know how it was implemented (source code is available), but the
PostgreSQL driver for AOLserver (a kick-butt web server for
database-backed websites) has a function that does that. All I do after
a SQL statement is:

	set rowcount [ns_pg ntuples]

	in my Tcl code and there it is. The driver is available at either
http://www.aolserver.com or http://www.openacs.org (the latter has a
more elaborate version of the driver).

	-Roberto
-- 
Computer Science			Utah State University
Space Dynamics Laboratory		Web Developer
USU Free Software & GNU/Linux Club 	http://fslc.usu.edu
My home page - http://www.brasileiro.net/roberto



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


end of thread, other threads:[~2000-11-18 17:57 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-11-17 09:01 how many rows? [was Re: fetching rows] Arnaud Vandyck <arnaud@ressource-toi.org>
2000-11-18 00:03 ` Robert B. Easter <reaster@comptechnews.com>
2000-11-18 17:57 ` Johann Spies <jhspies@adept.co.za>
2000-11-18 00:11 Re: how many rows? [was Re: fetching rows]] Roberto Mello <rmello@cc.usu.edu>

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