Postgres Pro
Downloads
Home   >   mailing lists

Thread: SQL_CALC_FOUND_ROWS equivalent in PostgreSQL

SQL_CALC_FOUND_ROWS equivalent in PostgreSQL

From
"Matt Arnilo S. Baluyos (Mailing Lists)"
Date:
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.

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

Thanks,
Matt

--
Stand before it and there is no beginning.
Follow it and there is no end.
Stay with the ancient Tao,
Move with the present.

Re: SQL_CALC_FOUND_ROWS equivalent in PostgreSQL

From
Oliver Elphick
Date:
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.

Re: SQL_CALC_FOUND_ROWS equivalent in PostgreSQL

From
Michael Fuhr
Date:
On Tue, Jul 31, 2007 at 07:24:34AM +0100, Oliver Elphick wrote:
>    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.)

To ensure consistent results the transaction should be SERIALIZABLE.
With the default of READ COMMITTED changes between the two selects
would be visible to the second select.

--
Michael Fuhr