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 quite a 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 example need 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-compliant way. > > 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, but in Oracle I can extend > this query to this for getting the total-number (not just the "page" 11-20) > of persons matching in 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 rows in the "result-set" without > actually retrieving them all? I understand that PG might have to visit them > all in order to 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-compliant SQL. > -- Sent via pgsql-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
