pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedHow do I detect End-of-table or End-of-limit ?
4+ messages / 3 participants
[nested] [flat]
* How do I detect End-of-table or End-of-limit ?
@ 2007-01-11 10:30 Jan van der Weijde <Jan.van.der.Weijde@attachmate.com>
2007-01-12 00:36 ` Re: How do I detect End-of-table or End-of-limit ? Alvaro Herrera <alvherre@commandprompt.com>
2007-01-12 00:41 ` Re: How do I detect End-of-table or End-of-limit ? L Bayuk <lbayuk@pobox.com>
0 siblings, 2 replies; 4+ messages in thread
From: Jan van der Weijde @ 2007-01-11 10:30 UTC (permalink / raw)
To: pgsql-interfaces
Hello all,
I am using the libpq interface method PQexec() to execute SELECT
statements with a LIMIT clause. I declare a cursor with hold and use
PQecec() to execute FETCH NEXT on that cursor.
At a certain moment PQntuples() returns 0 after a fetch, but I cannot
check for the actual reason: is the physical end of the table (or
selection) reached or is the end of my limit reached.
In the former case I'm done. But in the latter case I can continue
selecting records by using SELECT with a LIMIT and OFFSET clause.
Of course there are workarounds:
count the number of records (but if the size of the table or select set
is a multiple of my LIMIT value that does not work)
or
just try another SELECT and if the FECTH NEXT will fail again I'm done
However I am looking for something more elegant like a status flag in
libpq that indicate EOF or End-Of-limit.
Thank you,
Jan van der Weijde
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How do I detect End-of-table or End-of-limit ?
2007-01-11 10:30 How do I detect End-of-table or End-of-limit ? Jan van der Weijde <Jan.van.der.Weijde@attachmate.com>
@ 2007-01-12 00:36 ` Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 0 replies; 4+ messages in thread
From: Alvaro Herrera @ 2007-01-12 00:36 UTC (permalink / raw)
To: Jan van der Weijde <Jan.van.der.Weijde@attachmate.com>; +Cc: pgsql-interfaces
Jan van der Weijde wrote:
> However I am looking for something more elegant like a status flag in
> libpq that indicate EOF or End-Of-limit.
I don't think there's an actual "EOF". What you see is an "end of
tuples in the result set". Whether it is because you ran into the
LIMIT, or because you fell off the end of the table, you can't tell (nor
can the server actually).
Maybe it's more appropriate for you to open a cursor for the whole
table, and then do FETCH from it until it runs out of tuples; you will
know then that it's the end of the table.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How do I detect End-of-table or End-of-limit ?
2007-01-11 10:30 How do I detect End-of-table or End-of-limit ? Jan van der Weijde <Jan.van.der.Weijde@attachmate.com>
@ 2007-01-12 00:41 ` L Bayuk <lbayuk@pobox.com>
1 sibling, 0 replies; 4+ messages in thread
From: L Bayuk @ 2007-01-12 00:41 UTC (permalink / raw)
To: Jan van der Weijde <Jan.van.der.Weijde@attachmate.com>; +Cc: pgsql-interfaces
On Thu, Jan 11, 2007 at 11:30:48AM +0100, Jan van der Weijde wrote:
> Hello all,
>
> I am using the libpq interface method PQexec() to execute SELECT
> statements with a LIMIT clause. I declare a cursor with hold and use
> PQecec() to execute FETCH NEXT on that cursor.
> At a certain moment PQntuples() returns 0 after a fetch, but I cannot
> check for the actual reason: is the physical end of the table (or
> selection) reached or is the end of my limit reached.
> In the former case I'm done. But in the latter case I can continue
> selecting records by using SELECT with a LIMIT and OFFSET clause.
> ...
I'm not sure if this applies to you, but what I do is if I want N records,
I select with limit N+1. If I get N or fewer records back, it reached end
of table. If I get N+1 back, I process the first N of them and discard the
last, and I know there are more records. Next query starts at the discarded
one. So there is 1 record overlap on the queries. And unless the data
changes between queries, you will never get 0 records back.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How do I detect End-of-table or End-of-limit ?
@ 2007-01-12 13:35 Jan van der Weijde <Jan.van.der.Weijde@attachmate.com>
0 siblings, 0 replies; 4+ messages in thread
From: Jan van der Weijde @ 2007-01-12 13:35 UTC (permalink / raw)
To: pgsql-interfaces
Thank you, however I need to use LIMIT. The table that is being accessed
has millions of records and a simple SELECT without LIMIT takes very
long.
Unless you have a solution to that performance problem I cannot leave
out LIMIT.
Regards,
Jan van der Weijde
-----Original Message-----
From: Alvaro Herrera [mailto:alvherre@commandprompt.com]
Sent: Friday, January 12, 2007 01:37
To: Jan van der Weijde
Cc: pgsql-interfaces@postgresql.org
Subject: Re: [INTERFACES] How do I detect End-of-table or End-of-limit ?
Jan van der Weijde wrote:
> However I am looking for something more elegant like a status flag in
> libpq that indicate EOF or End-Of-limit.
I don't think there's an actual "EOF". What you see is an "end of
tuples in the result set". Whether it is because you ran into the
LIMIT, or because you fell off the end of the table, you can't tell (nor
can the server actually).
Maybe it's more appropriate for you to open a cursor for the whole
table, and then do FETCH from it until it runs out of tuples; you will
know then that it's the end of the table.
--
Alvaro Herrera
http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-01-12 13:35 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-01-11 10:30 How do I detect End-of-table or End-of-limit ? Jan van der Weijde <Jan.van.der.Weijde@attachmate.com>
2007-01-12 00:36 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-01-12 00:41 ` L Bayuk <lbayuk@pobox.com>
2007-01-12 13:35 Re: How do I detect End-of-table or End-of-limit ? Jan van der Weijde <Jan.van.der.Weijde@attachmate.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