Re: Window Functions - Mailing list pgsql-hackers

On Tue, 2008-10-14 at 19:04 +0200, Andreas Joseph Krogh wrote:
> On Tuesday 14 October 2008 18:19:07 Hannu Krosing wrote:
> > On Tue, 2008-10-14 at 11:05 +0200, Andreas Joseph Krogh wrote:
> > > Hi all.
> > > This is not very "hackers"-related, but related to the topic of window-funcitons, which seems to be discussed
quitea bit on "hackers" these days.
 
> > > 
> > > Can window-functions in PG be used to return "total number of rows" in a "paged result"?
> > > Say you have:
> > > SELECT p.id, p.firstname
> > >   FROM person p
> > >  ORDER BY p.firstname ASC
> > >  LIMIT 10 OFFSET 10
> > > 
> > > Is it possible to use some window-function to return the "total-number of columns" in a separate column?
> > > 
> > > In Oracle one can do 
> > > SELECT q.*, max(rownum) over() as total_rows FROM (subquery)
> > > which returns the total number or columns in a separate column. This is very handy for web-pages which for
exampleneed to display the rist 20 results of several million, without having to do a separate count(*) query.
 
> > 
> > no need to use window functions here, just ask for max inline:
> > 
> > 
> > hannu=# select rownum, word, (select max(rownum) from words) as maxrow
> > from words limit 10;
> >  rownum |   word    | maxrow 
> > --------+-----------+--------
> >       1 |           |  98569
> >       2 | A         |  98569
> >       3 | A's       |  98569
> >       4 | AOL       |  98569
> >       5 | AOL's     |  98569
> >       6 | Aachen    |  98569
> >       7 | Aachen's  |  98569
> >       8 | Aaliyah   |  98569
> >       9 | Aaliyah's |  98569
> >      10 | Aaron     |  98569
> > (10 rows)
> 
> Where do you get your "rownum"-column from here? It's a pseudo-column in Oracle 
> which is computed for each row in the "result-set", it's not a column in a table 
> somewhere, which is why I figured I must use window-funciton, or "analytical function" 
> as Oracle calls them, to operate on the *result-set* to retrieve the maximum number of 
> rows which satisfies the query.

ok, I misunderstood your intent

I guess you can use the non-recursive variant WITH syntax (aka CTE aka
Recursive queries) to get what you want.

> As far as I understand the ROW_NUMBER() window-funciton can be used to construct "limit with offset"-queries in a
SQL-spec-compliantway.
 
> 
> Say I want to retrieve an ordered list of persons (by name):
> 
> SELECT * FROM (
> SELECT ROW_NUMBER() OVER (order by p.name) as rnum, q.*
>   FROM (
> SELECT p.id, p.name FROM person p where p.birth_date > '2000-01-01'
> ) q
> ) r
>  WHERE r.rnum between 11 AND 20
> ;
> 
> This is good and works in Oracle, PG >= 8.4 and others that implements spec-compliant window-functions. This is fine,
butin Oracle I can extend this query to this for getting the total-number (not just the "page" 11-20) of persons
matchingin a separate column:
 
> 
> SELECT * FROM (
> SELECT ROW_NUMBER() OVER (order by p.name) as rnum, q.*, max(rownum) over() as total_rows
>   FROM (
> SELECT p.id, p.name FROM person p where p.birth_date > '2000-01-01'
> ) q
> ) r
>  WHERE r.rnum between 11 AND 20
> ;
> 
> So my question is: Will PG, with window functions, provide a similar mechanism for retrieving the total number of
rowsin the "result-set" without actually retrieving them all? I understand that PG might have to visit them all in
orderto retrieve that count, but that's OK.
 
> 
> What I'm looking for is an elegant solution to what's becomming a more common requirement in web-applications these
days:To display pageable lists with a "total-count", and to do that with *one* query, preferrably using
standard-compliantSQL.
 
>