pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
fetching rows
6+ messages / 6 participants
[nested] [flat]

* fetching rows
@ 2000-10-30 16:27  Nikolay Mijaylov <nmmm@nmmm.nu>
  0 siblings, 2 replies; 6+ messages in thread

From: Nikolay Mijaylov @ 2000-10-30 16:27 UTC (permalink / raw)
  To: pgsql-sql

Let say we have a select that returns 100 rows.

I can fetch first 25 with simple sql:

BEGIN WORK;
DECLARE liahona CURSOR FOR SELECT * FROM films;
FETCH [FORWARD] 25 IN liahona;
CLOSE liahona;
COMMIT WORK;

but how I can fetch rows from 26 to 50? I mean withou fetching first 25. Or
can I skip first 25?

When i ask this you need to know that Im using PHP in web environment and
try do this to get more  performens from the script (now the script fetch
all rows, then jump to needed row)





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

* Re: fetching rows
@ 2000-10-30 18:58  K Parker <kparker@eudoramail.com>
  0 siblings, 0 replies; 6+ messages in thread

From: K Parker @ 2000-10-30 18:58 UTC (permalink / raw)
  To: pgsql-sql

I hate to be the bearer of bad news, but if you're using PHP and you wanted to fetch just 25 rows at a time for a single page, and then fetch more when the user clicks on a NEXT button or link, you're completely out of luck.  Each http transaction is completely separate and so you can't maintain a cursor between pages.  Perhaps LIMIT would help you here; but I've found it more useful to make sure there is a unique key for the order I'm displaying, and then say "WHERE key > highest_key_value_on_current_page"




Join 18 million Eudora users by signing up for a free Eudora Web-Mail account at http://www.eudoramail.com



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

* Re: fetching rows
@ 2000-10-30 19:02  Jeff Hoffmann <jeff@propertykey.com>
  parent: Nikolay Mijaylov <nmmm@nmmm.nu>
  1 sibling, 2 replies; 6+ messages in thread

From: Jeff Hoffmann @ 2000-10-30 19:02 UTC (permalink / raw)
  To: Nikolay Mijaylov <nmmm@nmmm.nu>; +Cc: pgsql-sql

Nikolay Mijaylov wrote:
> 
> Let say we have a select that returns 100 rows.
> 
> I can fetch first 25 with simple sql:
> 
> BEGIN WORK;
> DECLARE liahona CURSOR FOR SELECT * FROM films;
> FETCH [FORWARD] 25 IN liahona;
> CLOSE liahona;
> COMMIT WORK;
> 
> but how I can fetch rows from 26 to 50? I mean withou fetching first 25. Or
> can I skip first 25?

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;

-- 

Jeff Hoffmann
PropertyKey.com



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

* Re: fetching rows
@ 2000-10-30 22:07  Martin Christensen <factotum@mail1.stofanet.dk>
  parent: Jeff Hoffmann <jeff@propertykey.com>
  1 sibling, 0 replies; 6+ messages in thread

From: Martin Christensen @ 2000-10-30 22:07 UTC (permalink / raw)
  To: Jeff Hoffmann <jeff@propertykey.com>; +Cc: Nikolay Mijaylov <nmmm@nmmm.nu>; pgsql-sql

>>>>> "Jeff" == Jeff Hoffmann <jeff@propertykey.com> writes:
Jeff> you can't do that with a cursor, but you can use they mysql-ism
Jeff> called a limit clause.  for example, to fetch rows 26-50 from
Jeff> that query, you'd do:
Jeff> select * from films limit 25,26;
Jeff> or
Jeff> select * from files limit 25 offset 26;

Since there's no particular ordering of the output of such a query it
is necessary to explicitly state an ordering key. Otherwise you'll
quickly find that the phase of the moon has very significant influence
on the produced results. :-) Especially in 7.*, as I understand it.

Martin

-- 
GPG public key: http://home1.stofanet.dk/factotum/gpgkey.txt



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

* Re: fetching rows
@ 2000-10-31 12:54  Decio Fonini <fonini@candidomendes.br>
  parent: Nikolay Mijaylov <nmmm@nmmm.nu>
  1 sibling, 0 replies; 6+ messages in thread

From: Decio Fonini @ 2000-10-31 12:54 UTC (permalink / raw)
  To: Nikolay Mijaylov <nmmm@nmmm.nu>; +Cc: pgsql-sql


Hello, Nikolay,

Don't use cursors; instead, use:
" select * from films limit 25 offset 0 ; "

and on the next query:
" select * from films limit 50 offset 25 ; " 

and so on. You have to encode the current offset into the NEXT link,
either making it into a button inside a form, with a hidden field
containing the offset, or making it an HREF with URL-encoded data
(HREF="myform.php?mycounter=50").

I don't remember in which version the LIMIT option appeared; if your
version doesn't support it, move on to 7.0.2!

Have fun!

On Mon, 30 Oct 2000, Nikolay Mijaylov wrote:

> Let say we have a select that returns 100 rows.
> 
> I can fetch first 25 with simple sql:
> 
> BEGIN WORK;
> DECLARE liahona CURSOR FOR SELECT * FROM films;
> FETCH [FORWARD] 25 IN liahona;
> CLOSE liahona;
> COMMIT WORK;
> 
> but how I can fetch rows from 26 to 50? I mean withou fetching first 25. Or
> can I skip first 25?
> 
> When i ask this you need to know that Im using PHP in web environment and
> try do this to get more  performens from the script (now the script fetch
> all rows, then jump to needed row)
> 
> 

-------------------------------------------------------
Fight for a free world: no walls, no windows, no gates.




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

* Re: fetching rows
@ 2000-11-17 03:09  Robert B. Easter <reaster@comptechnews.com>
  parent: Jeff Hoffmann <jeff@propertykey.com>
  1 sibling, 0 replies; 6+ messages in thread

From: Robert B. Easter @ 2000-11-17 03:09 UTC (permalink / raw)
  To: Jeff Hoffmann <jeff@propertykey.com>; Nikolay Mijaylov <nmmm@nmmm.nu>; +Cc: pgsql-sql

On Monday 30 October 2000 14:02, Jeff Hoffmann wrote:
> Nikolay Mijaylov wrote:
> > Let say we have a select that returns 100 rows.
> >
> > I can fetch first 25 with simple sql:
> >
> > BEGIN WORK;
> > DECLARE liahona CURSOR FOR SELECT * FROM films;
> > FETCH [FORWARD] 25 IN liahona;
> > CLOSE liahona;
> > COMMIT WORK;
> >
> > but how I can fetch rows from 26 to 50? I mean withou fetching first 25.
> > Or can I skip first 25?
>

I've done some web pages that have paging.  It did it with DECLARE to make a 
cursor then I used PostgreSQL's non-standard MOVE SQL command to start 
FETCHing from some offset depending on the page number.


> 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;

-- 
-------- 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] 6+ messages in thread


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

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-10-30 16:27 fetching rows Nikolay Mijaylov <nmmm@nmmm.nu>
2000-10-30 19:02 ` Jeff Hoffmann <jeff@propertykey.com>
2000-10-30 22:07   ` Martin Christensen <factotum@mail1.stofanet.dk>
2000-11-17 03:09   ` Robert B. Easter <reaster@comptechnews.com>
2000-10-31 12:54 ` Decio Fonini <fonini@candidomendes.br>
2000-10-30 18:58 Re: fetching rows K Parker <kparker@eudoramail.com>

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