Re: SQL_CALC_FOUND_ROWS equivalent in PostgreSQL

On Tue, 2007-07-31 at 09:22 +0800, Matt Arnilo S. Baluyos (Mailing
Lists) wrote:
> Hello everyone,
>
> I would like to use PostgreSQL with the SmartyPaginate plugin of the
> Smarty template engine.
>
> In the examples on the documentation, the following two queries are used:
>
> SELECT SQL_CALC_FOUND_ROWS * FROM mytable LIMIT X,Y
> SELECT FOUND_ROWS() as total
>
> What the SQL_CALC_FOUND_ROWS does is that it allows the FOUND_ROWS()
> function to return the total rows if the first query didn't have the
> LIMIT.
>
SQL_CALC_FOUND_ROWS and FOUND_ROWS() are MySQL features.

> Is there an equivalent function in PostgreSQL for this or perhaps a
> workaround?

There is no equivalent.  Use

   BEGIN;
   SELECT * FROM mytable OFFSET X LIMIT Y;
   SELECT COUNT(*) AS total FROM mytable;
   END;

(To ensure consistent results, both queries should be done in a single
transaction.)

If you are repeating the query multiple times for separate pages, it
would be more efficient to do the COUNT() selection first and not repeat
it for each page.  You could use a cursor to go back and forth through
the results while doing the query only once.

--
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
   Do you want to know God?   http://www.lfix.co.uk/knowing_god.html


--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.