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
Date: Fri, 26 Mar 2010 07:23:50 +0100
Message-ID: <20100326062350.GA6817@a-kretschmer.de> (raw)
In-Reply-To: <OFA0B1E010.BE09B175-ON652576F2.00143421-652576F2.0014316E@LocalDomain>
References: <594D625E4784AD4EA9D423E292BE6725489FD1@mlbe2k10.cs.myharris.net>
<hogm4r$gh6$1@dough.gmane.org>
<OFA0B1E010.BE09B175-ON652576F2.00143421-652576F2.0014316E@LocalDomain>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andreas.kretschmer@schollglas.com
Subject: Re: SQL syntax rowcount value as an extra column in the result set
In-Reply-To: <20100326062350.GA6817@a-kretschmer.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox