Date: Fri, 26 Mar 2010 07:23:50 +0100
From: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
To: pgsql-sql@postgresql.org
Subject: Re: SQL syntax rowcount value as an extra column in the result set
Message-ID: <20100326062350.GA6817@a-kretschmer.de>
References: <594D625E4784AD4EA9D423E292BE6725489FD1@mlbe2k10.cs.myharris.net>
	<hogm4r$gh6$1@dough.gmane.org>
	<OFA0B1E010.BE09B175-ON652576F2.00143421-652576F2.0014316E@LocalDomain>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <OFA0B1E010.BE09B175-ON652576F2.00143421-652576F2.0014316E@LocalDomain>
User-Agent: Mutt/1.5.9i

In response to Jayadevan M :
> Hi,
> I don't think so.
> Oracle -
> SQL> select count(*) over () as ROWCOUNT , first_name from people;
> 
>   ROWCOUNT FIRST_NAME
> ----------
> ---------------------------------------------------------------------
> -------------------------------
>          6 Mary
>          6 Mary
>          6 John
>          6 John
>          6 John
>          6 Jacob
> 
> 6 rows selected.
> 
> PostgreSQL
> postgres=# select count(*) over () as ROWCOUNT , first_name from people;
> ERROR:  syntax error at or near "over"
> LINE 1: select count(*) over () as ROWCOUNT , first_name from people...

It works, but you should use a recent version:

test=*# select count(1) over (), i from foo;
 count | i
-------+----
     8 |  1
     8 |  2
     8 |  3
     8 |  6
     8 |  7
     8 |  9
     8 | 13
     8 | 14
(8 rows)

test=*# select version();
                                                version
--------------------------------------------------------------------------------------------------------
 PostgreSQL 8.4.2 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.3.real
(Debian 4.3.2-1.1) 4.3.2, 64-bit
(1 row)

test=*#

Regards, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99