From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Chris <dmagick(at)gmail(dot)com> |
Cc: | Fabio Victora Hecht <fabiovh(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: count and limit |
Date: | 2006-08-18 00:47:10 |
Message-ID: | 1112.1155862030@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Chris <dmagick(at)gmail(dot)com> writes:
> Fabio Victora Hecht wrote:
>> I was wondering if there's a way to count the results of a query and
>> return part of the result set it in one query (LIMIT).
> I was going to suggest a cursor but I don't think you can get the number
> of results a cursor has :(
In general it's not possible to determine the number of rows a query
will return without actually executing it to completion.
You could use a cursor like this:
begin;
declare c cursor for select ... ;
fetch 20 from c;
-- display the first 20 results
move forward all in c;
-- note the count returned by MOVE, add 20 to get the total
but if you're expecting the MOVE to be instantaneous, prepare to be
disappointed.
If you're willing to settle for a (very) approximate count, there are
things you could do. Some people just feed the query to EXPLAIN and
grab the rowcount estimate out of the first line of output.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Fuhr | 2006-08-18 00:49:33 | Re: count and limit |
Previous Message | Tom Lane | 2006-08-18 00:40:38 | Re: select * from users where user_id NOT in (select |