Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 5D33A6324A6; Fri, 26 Mar 2010 03:55:47 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 78254-03; Fri, 26 Mar 2010 06:55:36 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mx1.ibsplc.com (mx1.ibsplc.com [202.177.44.5]) by mail.postgresql.org (Postfix) with ESMTP id 1F9D06323CE; Fri, 26 Mar 2010 03:55:34 -0300 (ADT) X-IronPort-AV: E=Sophos;i="4.51,312,1267381800"; d="scan'208";a="13927559" Received: from unknown (HELO Green.ibsplc.com) ([192.168.0.4]) by mx2.ibsplc.com with ESMTP; 26 Mar 2010 12:08:30 +0530 In-Reply-To: <20100326062350.GA6817@a-kretschmer.de> To: "A. Kretschmer" Cc: pgsql-sql@postgresql.org,pgsql-sql-owner@postgresql.org Subject: Re: SQL syntax rowcount value as an extra column in the result set Message-ID: From: "Jayadevan M" Date: Fri, 26 Mar 2010 12:26:54 +0530 Content-Type: multipart/alternative; boundary="=_alternative 002608F2652576F2_=" References: <594D625E4784AD4EA9D423E292BE6725489FD1@mlbe2k10.cs.myharris.net> <20100326062350.GA6817@a-kretschmer.de> MIME-Version: 1.0 X-KeepSent: 3566EE01:958C8348-652576F2:002579A1; name=$KeepSent; type=4 X-Mailer: Lotus Notes Release 8.5 December 05, 2008 X-Disclaimed: 58411 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.002 tagged_above=-10 required=5 tests=BAYES_50=0.001, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201003/201 X-Sequence-Number: 33908 --=_alternative 002608F2652576F2_= Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable Hi, >It works, but you should use a recent version: >test=3D*# 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=3D*# select version(); > version >=20 ---------------------------------------------------------------------------= ----------------------------- > PostgreSQL 8.4.2 on x86=5F64-pc-linux-gnu, compiled by GCC gcc-4.3.real > (Debian 4.3.2-1.1) 4.3.2, 64-bit > (1 row) Thank you for setting that right. Apologies for not checking version. Is this approach better compared to=20 postgres=3D# select * from (select count(*) from people ) p, (select=20 firstname from people)p2; count | firstname -------+----------- 5 | Mary 5 | Mary 5 | John 5 | John 5 | Jacob (5 rows) This gives me postgres=3D# explain select * from (select count(*) from people )as p,=20 (select firstname from people)p2; QUERY PLAN --------------------------------------------------------------------- Nested Loop (cost=3D14.00..30.42 rows=3D320 width=3D226) -> Aggregate (cost=3D14.00..14.01 rows=3D1 width=3D0) -> Seq Scan on people (cost=3D0.00..13.20 rows=3D320 width=3D0) -> Seq Scan on people (cost=3D0.00..13.20 rows=3D320 width=3D218) Since I don't have 8.4, I am not in a position to do explain on that=20 version. My guess - over () will be better. My query does sequential=20 scans/nested loop...(if there are no indexes) Regards, Regards, Jayadevan DISCLAIMER: "The information in this e-mail and any attachment is intende= d only for the person to whom it is addressed and may contain confidential = and/or privileged material. If you have received this e-mail in error, kind= ly contact the sender and destroy all copies of the original communication.= IBS makes no warranty, express or implied, nor guarantees the accuracy, ad= equacy or completeness of the information contained in this email or any at= tachment and is not liable for any errors, defects, omissions, viruses or f= or resultant loss or damage, if any, direct or indirect." = --=_alternative 002608F2652576F2_= Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable Hi,
>It works, but = you should use a recent version:

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

> test=3D*# select version();
= >                                                version
> ---------------------------------------= -----------------------------------------------------------------
> &= nbsp;PostgreSQL 8.4.2 on x86=5F64-pc-linux-gnu, compiled by GCC gcc-4.3.rea= l
> (Debian 4.3.2-1.1) 4.3.2, 64-bit
> (1 row)
<= br>Thank you for setting that right. Apo= logies for not checking version.
Is t= his approach better compared to
postgres=3D# select * from (s= elect count(*) from people ) p, (select firstname from people)p2;
 count | firstname
-------+-----------
     5 | Mary
     5 | Mary
     5 | John
     5 | John
     5 | Jacob
(5 rows)
This gives me
postgres=3D# explain  select *= from (select count(*) from people )as p, (select firstname from people)p2;
          =                    QUERY PLAN
--------------------------------= -------------------------------------
 Nested Loop  (cost=3D14.00..30.42 rows=3D320 width=3D226)
 =  ->  Aggregate  (cost=3D14.00..14.01 rows=3D1 width=3D0)
  &nb= sp;      ->  Seq Scan on people  (cost=3D0.00..13.20 rows=3D320 width=3D0)
   ->  Seq Sca= n on people  (cost=3D0.00..13.20 rows=3D320 width=3D218)

Since I don't have 8.4, I am not in a position to do explain on that version. My guess - over () will be better. My query does sequential scans/nested loop...(if there are no indexes)

Regards,
Regards,
Jayadevan DISCLAIMER: "The information in this e-mail and any attachment is intended only for the= person to whom it is addressed and may contain confidential and/or privile= ged material. If you have received this e-mail in error, kindly contact the= sender and destroy all copies of the original communication. IBS makes no = warranty, express or implied, nor guarantees the accuracy, adequacy or comp= leteness of the information contained in this email or any attachment and i= s not liable for any errors, defects, omissions, viruses or for resultant l= oss or damage, if any, direct or indirect."
 --=_alternative 002608F2652576F2_=--