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

<p dir="LTR"><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us"><font
face="Calibri">Hello</font></span><pdir="LTR"><span lang="en-us"></span><span lang="en-us"></span><span
lang="en-us"></span><pdir="LTR"><span lang="en-us"><font face="Calibri">I</font></span><span lang="en-us"></span><span
lang="en-us"></span><spanlang="en-us"><font face="Calibri">’</font></span><span lang="en-us"></span><span
lang="en-us"></span><spanlang="en-us"><font face="Calibri">m using PostgreSQL (8.4.701) and Java
(jdbc</font></span><spanlang="en-us"></span><span lang="en-us"></span><span lang="en-us"><font
face="Calibri">,</font></span><spanlang="en-us"></span><span lang="en-us"></span><span lang="en-us"> <font
face="Calibri">postgresql-8.4-701.jdbc4.jar</font></span><spanlang="en-us"></span><span lang="en-us"></span><span
lang="en-us"><fontface="Calibri">) to connect to the database.</font></span><p dir="LTR"><span
lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"></span><p dir="LTR"><span lang="en-us"><font
face="Calibri">Myquestion is:</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us"><font
face="Calibri">what is the SQL syntax for PostgreSQL to achieve the following:</font></span><span
lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"></span><p dir="LTR"><span lang="en-us"></span><span
lang="en-us"></span><spanlang="en-us"></span><p dir="LTR"><span lang="en-us"><font face="Calibri">I want to receive the
rowcountalong with the rest of a result set. For example, let</font></span><span lang="en-us"></span><span
lang="en-us"></span><spanlang="en-us"><font face="Calibri">’</font></span><span lang="en-us"></span><span
lang="en-us"></span><spanlang="en-us"><font face="Calibri">s say the following query returns</font></span><p
dir="LTR"><spanlang="en-us"></span><span lang="en-us"></span><span lang="en-us"></span><p dir="LTR"><span
lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"><font face="Calibri">select first_name from
people</font></span><spanlang="en-us"></span><span lang="en-us"></span><span lang="en-us"><font
face="Calibri">;</font></span><spanlang="en-us"></span><span lang="en-us"></span><span lang="en-us"></span><p
dir="LTR"><spanlang="en-us"></span><span lang="en-us"></span><span lang="en-us"></span><p dir="LTR"><span
lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"><font face="Calibri">first_name</font></span><span
lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"></span><p dir="LTR"><span lang="en-us"><font
face="Calibri">=========</font></span><spanlang="en-us"></span><span lang="en-us"></span><span lang="en-us"></span><p
dir="LTR"><spanlang="en-us"><font face="Calibri">Mary</font></span><p dir="LTR"><span lang="en-us"><font
face="Calibri">Sue</font></span><pdir="LTR"><span lang="en-us"><font face="Calibri">Joe</font></span><br /><p
dir="LTR"><spanlang="en-us"><font face="Calibri">and the following query returns the value</font></span><span
lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"> </span><p dir="LTR"><span lang="en-us"></span><span
lang="en-us"></span><spanlang="en-us"></span><p dir="LTR"><span lang="en-us"></span><span lang="en-us"></span><span
lang="en-us"><fontface="Calibri">select count(*)</font></span><span lang="en-us"></span><span lang="en-us"></span><span
lang="en-us"><fontface="Calibri">as ROWCOUNT</font></span><span lang="en-us"></span><span lang="en-us"></span><span
lang="en-us"><fontface="Calibri"></font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us">
<fontface="Calibri">from people</font></span><span lang="en-us"></span><span lang="en-us"></span><span
lang="en-us"><fontface="Calibri">;</font></span><span lang="en-us"></span><span lang="en-us"></span><span
lang="en-us"></span><pdir="LTR"><span lang="en-us"><font face="Calibri">ROWCOUNT</font></span><p dir="LTR"><span
lang="en-us"><fontface="Calibri">==========</font></span><p dir="LTR"><span lang="en-us"></span><span
lang="en-us"></span><spanlang="en-us"><font face="Calibri">3</font></span><p dir="LTR"><span lang="en-us"></span><span
lang="en-us"></span><spanlang="en-us"><font face="Calibri">3</font></span><span lang="en-us"></span><span
lang="en-us"></span><spanlang="en-us"></span><p dir="LTR"><span lang="en-us"></span><span lang="en-us"></span><span
lang="en-us"></span><pdir="LTR"><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us"></span><p
dir="LTR"><spanlang="en-us"><font face="Calibri">What I</font></span><span lang="en-us"></span><span
lang="en-us"></span><spanlang="en-us"><font face="Calibri">’</font></span><span lang="en-us"></span><span
lang="en-us"></span><spanlang="en-us"><font face="Calibri">m looking for is the output as</font></span><p
dir="LTR"><spanlang="en-us"></span><span lang="en-us"></span><span lang="en-us"></span><p dir="LTR"><span
lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"><font face="Calibri">ROWCOUNT , 
first_name</font></span><pdir="LTR"><span lang="en-us"><font face="Calibri">=====================</font></span><span
lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"></span><p dir="LTR"><span lang="en-us"><font
face="Calibri">3, Mary</font></span><p dir="LTR"><span lang="en-us"><font face="Calibri">3 , Sue</font></span><p
dir="LTR"><spanlang="en-us"><font face="Calibri">3 , Joe</font></span><span lang="en-us"></span><span
lang="en-us"></span><spanlang="en-us"></span><p dir="LTR"><span lang="en-us"></span><span lang="en-us"></span><span
lang="en-us"></span><pdir="LTR"><span lang="en-us"><font face="Calibri">so I can use JDBC (snip-it) as
follows:</font></span><pdir="LTR"><span lang="en-us"><font face="Calibri">resultSet.getInt(</font></span><span
lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"><font face="Calibri">“</font></span><span
lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"><font face="Calibri">ROWCOUNT</font></span><span
lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"><font face="Calibri">”</font></span><span
lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"><font face="Calibri">)</font></span><p dir="LTR"><span
lang="en-us"><fontface="Calibri">resultSet.getString(</font></span><span lang="en-us"></span><span
lang="en-us"></span><spanlang="en-us"><font face="Calibri">“</font></span><span lang="en-us"></span><span
lang="en-us"></span><spanlang="en-us"><font face="Calibri">first_name</font></span><span lang="en-us"></span><span
lang="en-us"></span><spanlang="en-us"><font face="Calibri">”</font></span><span lang="en-us"></span><span
lang="en-us"></span><spanlang="en-us"><font face="Calibri">)</font></span><p dir="LTR"><span lang="en-us"></span><span
lang="en-us"></span><spanlang="en-us"></span><p dir="LTR"><span lang="en-us"></span><span lang="en-us"></span><span
lang="en-us"><fontface="Calibri">On a side note,</font></span><span lang="en-us"></span><span lang="en-us"></span><span
lang="en-us"><font face="Calibri">Oracle allows the following</font></span><span lang="en-us"></span><span
lang="en-us"></span><spanlang="en-us"> <font face="Calibri">syntax</font></span><span lang="en-us"></span><span
lang="en-us"></span><spanlang="en-us"><font face="Calibri"> to achieve the above</font></span><span
lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"><font face="Calibri">:</font></span><p dir="LTR"><span
lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"><font face="Calibri">select count(*)</font></span><span
lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"><font face="Calibri"> over ()</font></span><span
lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"> <font face="Calibri">as ROWCOUNT</font></span><span
lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"><font face="Calibri"></font></span><span
lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"> <font face="Calibri">, first_name</font></span><span
lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"> <font face="Calibri">from people</font></span><p
dir="LTR"><spanlang="en-us"></span><span lang="en-us"></span><span lang="en-us"></span><p dir="LTR"><span
lang="en-us"><fontface="Calibri">Thank</font></span><span lang="en-us"></span><span lang="en-us"></span><span
lang="en-us"><fontface="Calibri">s,</font></span><span lang="en-us"></span><span lang="en-us"></span><span
lang="en-us"><fontface="Calibri">Jim</font></span><p dir="LTR"><span lang="en-us"></span><span
lang="en-us"></span><spanlang="en-us"></span>