Re: SQL syntax rowcount value as an extra column in the result set

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