Re: SQL syntax rowcount value as an extra column in the result set - Mailing list pgsql-sql

<tt><font size="2">Hi,</font></tt><br /><tt><font size="2">>It works, but you should use a recent version:<br /><br
/>>test=*#select count(1) over (), i from foo;<br />> count | i<br />>-------+----<br />>     8 |  1<br
/>>    8 |  2<br />>     8 |  3<br />>     8 |  6<br />>     8 |  7<br />>     8 |  9<br />>     8 |
13<br/>>     8 | 14<br />>(8 rows)<br /><br />> test=*# select version();<br />>                          
                    version<br />>
--------------------------------------------------------------------------------------------------------<br/>>
 PostgreSQL8.4.2 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.3.real<br />> (Debian 4.3.2-1.1) 4.3.2, 64-bit<br
/>>(1 row)<br /></font></tt><br /><font face="sans-serif" size="2">Thank you for setting that right. Apologies for
notchecking version.</font><br /><font face="sans-serif" size="2">Is this approach better compared to </font><br
/><fontface="sans-serif" size="2">postgres=# select * from (select count(*) from people ) p, (select firstname from
people)p2;</font><br/><font face="sans-serif" size="2"> count | firstname</font><br /><font face="sans-serif"
size="2">-------+-----------</font><br/><font face="sans-serif" size="2">     5 | Mary</font><br /><font
face="sans-serif"size="2">     5 | Mary</font><br /><font face="sans-serif" size="2">     5 | John</font><br /><font
face="sans-serif"size="2">     5 | John</font><br /><font face="sans-serif" size="2">     5 | Jacob</font><br /><font
face="sans-serif"size="2">(5 rows)</font><br /><font face="sans-serif" size="2">This gives me</font><br /><font
face="sans-serif"size="2">postgres=# explain  select * from (select count(*) from people )as p, (select firstname from
people)p2;</font><br/><font face="sans-serif" size="2">                             QUERY PLAN</font><br /><font
face="sans-serif"size="2">---------------------------------------------------------------------</font><br /><font
face="sans-serif"size="2"> Nested Loop  (cost=14.00..30.42 rows=320 width=226)</font><br /><font face="sans-serif"
size="2">  ->  Aggregate  (cost=14.00..14.01 rows=1 width=0)</font><br /><font face="sans-serif" size="2">       
 -> Seq Scan on people  (cost=0.00..13.20 rows=320 width=0)</font><br /><font face="sans-serif" size="2">   ->
 SeqScan on people  (cost=0.00..13.20 rows=320 width=218)</font><br /><br /><font face="sans-serif" size="2">Since I
don'thave 8.4, I am not in a position to do explain on that version. My guess - over () will be better. My query does
sequentialscans/nested loop...(if there are no indexes)</font><br /><br /><font face="sans-serif"
size="2">Regards,</font><br/><font face="sans-serif" size="2">Regards,</font><br /><font face="Trebuchet MS"
size="2">Jayadevan</font><fontface="sans-serif"></font> <img src="http://www.ibsplc.com/images/email_footer_final.gif"
/><font color="#999999" face="Tahoma" size="1"><u> DISCLAIMER:</u></font>  <font color="#999999" face="Tahoma"
size="1">"The information in this e-mail and any attachment is intended only for the person to whom it is addressed and
maycontain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the
senderand destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees
theaccuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable
forany errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."</font><br
/><fontsize="1"> </font>