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