Re: SQL_CALC_FOUND_ROWS equivalent in PostgreSQL - Mailing list pgsql-novice

From Oliver Elphick
Subject Re: SQL_CALC_FOUND_ROWS equivalent in PostgreSQL
Date
Msg-id 1185863074.10580.91.camel@linda.lfix.co.uk
Whole thread Raw
In response to SQL_CALC_FOUND_ROWS equivalent in PostgreSQL  ("Matt Arnilo S. Baluyos (Mailing Lists)" <matt.baluyos.lists@gmail.com>)
Responses Re: SQL_CALC_FOUND_ROWS equivalent in PostgreSQL  (Michael Fuhr <mike@fuhr.org>)
List pgsql-novice
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.


pgsql-novice by date:

Previous
From: Tom Lane
Date:
Subject: Re: doubt about datum
Next
From: Oliver Elphick
Date:
Subject: Re: alter table table add column