I am doing some paging in my application, returning 20 rows from the database at a time using PostgreSQL's standard OFFSET and LIMIT keywords. For instance, to get page 1 page:
SELECT stuff FROM table WHERE condition ORDER BY stuff OFFSET 0 LIMIT 20
It is a requirement of the application that we also show to the user the total number of records. So, obviously, I can get the total by issuing a separate query:
SELECT COUNT(*) FROM table WHERE condition
But if there are a large number of rows then this is not an optimal solution. I notice that MySQL has a very useful function called FOUND_ROWS() that does exactly what I am looking for:
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function%5Ffound-rows
Is there an equivalent in PostgreSQL?
PostgreSQL has had window functions for a while now which can be used to do many things including counting rows before LIMIT is applied.
Based on the example above:
SELECT stuff,
count(*) OVER() AS total_count
FROM table
WHERE condition
ORDER BY stuff OFFSET 40 LIMIT 20