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

Re: SQL_CALC_FOUND_ROWS equivalent in PostgreSQL

From: Oliver Elphick <olly(at)lfix(dot)co(dot)uk>
To: "Matt Arnilo S(dot) Baluyos (Mailing Lists)" <matt(dot)baluyos(dot)lists(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: SQL_CALC_FOUND_ROWS equivalent in PostgreSQL
Date: 2007-07-31 06:24:34
Message-ID: 1185863074.10580.91.camel@linda.lfix.co.uk (view raw or flat)
Thread:
Lists: 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(at)lfix(dot)co(dot)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.


In response to

Responses

pgsql-novice by date

Next:From: Oliver ElphickDate: 2007-07-31 06:25:53
Subject: Re: alter table table add column
Previous:From: Michael GlaesemannDate: 2007-07-31 05:56:11
Subject: Re: [NOVICE] alter table table add column

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