Обсуждение: How do I detect End-of-table or End-of-limit ?
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
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.
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.
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.