Skip site navigation (1) Skip section navigation (2)

Getting number of matched records from query

From: Michael Owens <mike(at)mikesclutter(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Getting number of matched records from query
Date: 2003-11-02 04:54:05
Message-ID: 200311012254.06132.mike@mikesclutter.com (view raw or flat)
Thread:
Lists: pgsql-hackers
I should perhaps dig a little more on this one, but per the C API docs I don't 
think what I am asking for is available. This somewhat relates to count().

Is there a reasonable way that information regarding the number of matched 
records could be passed back to the client --- even in the case of a LIMIT? 
PQntuples() only returns the number of tuples in the result set (restricted by 
the limit clause). Is there a way to get all matched records in the query 
(before they were limited)? Perhaps this number could be sent back through 
PQcmdTuples(), in which case this would be the meaning of its return value 
for the SELECT queries.

This is useful in a limit/offset situation such as a web based search where 
you want to divide the results up over several pages. In order to display any 
kind of navigation, you need to know the total number of records up front so 
you can display to the client the number of pages it would take to step 
through all of the results, N records at a time. 

If this were available, then it would obviate the need to count(*) the query 
before executing it, which could save quite a bit of time.

Isn't this information (the number of matched records) available to the 
backend --- even in the case of LIMIT? All matched records still have to be 
materialized right? Therefore the record count should be available and could 
therefore be available to the client. Is this correct? 

I would be willing to work on implementing this, but I would just like to know 
if this is even possible first.



Responses

pgsql-hackers by date

Next:From: StephenDate: 2003-11-02 05:45:08
Subject: Re: Experimental patch for inter-page delay in VACUUM
Previous:From: Mark WongDate: 2003-11-02 04:43:46
Subject: Re: OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group