Thread: set return function is returning a single record, multiple times,how can i get all the records in the table( description inside )

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Hi</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">      Please spare some time to provide a solution for the described problem :</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I am using set returning functions to return all the records from a table named  </span></font><font
face="ArialBlack" size="2"><span style="font-size:10.0pt;font-family:"Arial Black"">pss</span></font><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;font-family:Arial"> ,</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">But what I am getting is the first record is returned as many  times , the number of records present
inthe rank_master:</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">I am giving a detailed description below please check it out  </span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">1) The following  query creates </span></font><font face="Arial Black" size="2"><span
style="font-size:10.0pt;font-family:"ArialBlack"">pss</span></font><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">table:</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">create table pss( name varchar(20), num integer, phno integer );</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">2) insert three records in to </span></font><font face="Arial Black" size="2"><span
style="font-size:10.0pt;font-family:"ArialBlack"">pss</span></font><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">:</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">insert into pss values(‘penchal’,1,420);</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">insert into pss values(‘joe’,2,421);</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">insert into pss values(‘ali’,3,422);</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">3) create an user defines type of  variable named structrankmaster2 ( something like a structure to
holda record ) : </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">create type Structrankmaster2 as (name varchar(20), num integer, phno integer);</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">4) The following is the function that retrieves the records from pss :</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">CREATE or replace  FUNCTION ftoc9() RETURNS setof  structrankmaster2  LANGUAGE 'plpgsql'
</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> AS' DECLARE </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> rowdata pss%rowtype;</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">BEGIN for i in 1..3 loop</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">select * into rowdata from pss ;</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">return next rowdata ;</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">end loop;</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">return;</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">end';</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> 5) now call  the function  from command prompt:</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial">         Select  * from ftoc9();</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">6) the following is the output that I am getting ( i.e the first row repeated 3 times )
 :</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">  name   | num | phno</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">---------+-----+------</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> penchal |   1 |  420</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> penchal |   1 |  420</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> penchal |   1 |  420</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">(3 rows)</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">7) what exactly I should be getting is :</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">  name   | num | phno</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">---------+-----+------</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> penchal |   1 |  420</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> joe        |   2 |  421</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> penchal |   3 |  422</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">(3 rows)</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Please provide a solution for this so that I can get     </span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial">  name   | num | phno</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">---------+-----+------</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> penchal |   1 |  420</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> joe        |   2 |  421</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> penchal |   3 |  422</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">(3 rows)</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">                                                        Thanks & regards </span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">                                                                       </span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:5.0pt"><b><font
color="navy"face="Tahoma" size="1"><span style="font-size:8.0pt;font-family:Tahoma; 
color:navy;font-weight:bold">Thanks  &  Regards</span></font><font color="navy"><span
style="color:navy"></span></font></b><pclass="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:5.0pt"><b><font
color="navy"face="Tahoma" size="1"><span style="font-size:8.0pt;font-family:Tahoma; 
color:navy;font-weight:bold">Penchal reddy </span></font></b><b><font color="red" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;
color:red;font-weight:bold">|</span></font></b><b><font color="navy" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:navy;
font-weight:bold"> Software Engineer           </span></font><font color="navy"><span
style="color:navy"></span></font></b><pclass="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:5.0pt"><b><font
color="navy"face="Tahoma" size="1"><span style="font-size:8.0pt;font-family:Tahoma; 
color:navy;font-weight:bold">Infinite Computer Solutions </span></font></b><b><font color="red" face="Tahoma"
size="1"><spanstyle="font-size:8.0pt;font-family:Tahoma; 
color:red;font-weight:bold">|</span></font></b><b><font color="navy" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:navy;
font-weight:bold"> Exciting Times…Infinite Possibilities... </span></font><font color="navy"><span
style="color:navy"></span></font></b><pclass="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:5.0pt"><b><font
color="navy"face="Tahoma" size="1"><span style="font-size:8.0pt;font-family:Tahoma; 
color:navy;font-weight:bold">SEI-CMMI level 5 </span></font></b><b><font color="red" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;
color:red;font-weight:bold">| </span></font></b><b><font color="black" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:black;
font-weight:bold">ISO 9001:2000</span></font><font color="navy"><span style="color:navy"></span></font></b><p
class="MsoNormal"style="mso-margin-top-alt:auto;margin-bottom:5.0pt"><b><font color="navy" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;
color:navy;font-weight:bold">IT SERVICES </span></font></b><b><font color="red" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;
color:red;font-weight:bold">|</span></font></b><b><font color="navy" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:navy;
font-weight:bold">
BPO                                                                                                                                                                          
</span></font><fontcolor="navy"><span style="color:navy"></span></font></b><p class="MsoNormal"
style="mso-margin-top-alt:auto;margin-bottom:5.0pt"><b><fontcolor="navy" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;
color:navy;font-weight:bold">Telecom </span></font></b><b><font color="red" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;
color:red;font-weight:bold">|</span></font></b><b><font color="navy" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:navy;
font-weight:bold"> </span></font></b><b><font color="black" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:black;font-weight:bold">Finance</span></font></b><b><font color="red"
face="Tahoma"size="1"><span style="font-size: 
8.0pt;font-family:Tahoma;color:red;font-weight:bold">|</span></font></b><b><font color="navy" face="Tahoma"
size="1"><spanstyle="font-size:8.0pt;font-family:Tahoma; 
color:navy;font-weight:bold"> </span></font></b><b><font color="black" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:black;
font-weight:bold">Healthcare </span></font></b><b><font color="red" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:red;
font-weight:bold">| </span></font></b><b><font color="black" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:black;font-weight:bold">Manufacturing</span></font></b><b><font
color="red"face="Tahoma" size="1"><span style="font-size: 
8.0pt;font-family:Tahoma;color:red;font-weight:bold">|</span></font></b><b><font color="navy" face="Tahoma"
size="1"><spanstyle="font-size:8.0pt;font-family:Tahoma; 
color:navy;font-weight:bold"> </span></font></b><b><font color="black" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:black;
font-weight:bold">Energy & Utilities </span></font></b><b><font color="red" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;
color:red;font-weight:bold">|</span></font></b><b><font color="navy" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:navy;
font-weight:bold"> </span></font></b><b><font color="black" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:black;font-weight:bold">Retail& Distribution
</span></font></b><b><fontcolor="red" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:red;font-weight:bold">|</span></font></b><b><fontcolor="navy"
face="Tahoma"size="1"><span style="font-size:8.0pt;font-family:Tahoma; 
color:navy;font-weight:bold"> </span></font></b><b><font color="black" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:black;
font-weight:bold">Government                                                        </span></font><font
color="navy"><spanstyle="color:navy"></span></font></b><p class="MsoNormal"
style="mso-margin-top-alt:auto;margin-bottom:5.0pt"><b><fontcolor="navy" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;
color:navy;font-weight:bold">Tel +91-80-4133-0000(Ext:503)</span></font></b><b><font color="red" face="Tahoma"
size="1"><spanstyle="font-size:8.0pt;font-family:Tahoma; 
color:red;font-weight:bold">|</span></font></b><b><font color="navy" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:navy;
font-weight:bold"> Fax  +91-80-51930009 </span></font></b><b><font color="red" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;
color:red;font-weight:bold">|</span></font></b><b><font color="navy" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:navy;
font-weight:bold"> Cell No  +91-9886774209</span></font></b><b><font color="red" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;
color:red;font-weight:bold">|</span></font></b><b><font color="navy" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:navy;
font-weight:bold">www.infics.com</span></font></b><b><font color="black" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:black;
font-weight:bold">          </span></font><font color="navy"><span style="color:navy"></span></font></b><p
class="MsoNormal"style="mso-margin-top-alt:auto;margin-bottom:5.0pt"><b><font color="gray" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;
color:gray;font-weight:bold">Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/
orits Customers and is intended for use only by the individual or entity to which it is addressed, and may contain
informationthat is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended
recipientor it appears that this mail has been forwarded to you without proper authority, you are notified that any use
ordissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately
at</span></font></b><b><fontcolor="black" face="Tahoma" size="1"><span style="font-size:8.0pt;font-family:Tahoma; 
color:black;font-weight:bold"> </span></font></b><b><u><font color="gray" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;color:gray;
font-weight:bold">info.in@infics.com </span></font></u></b><b><font color="gray" face="Tahoma" size="1"><span
style="font-size:8.0pt;font-family:Tahoma;
color:gray;font-weight:bold">and delete this mail from your records.</span></font><font color="navy"><span
style="color:navy"></span></font></b><pclass="MsoNormal"><font face="Times New Roman" size="3"><span style="font-size: 
12.0pt"> </span></font></div>

am  28.04.2006, um 16:14:10 +0530 mailte Penchalaiah P. folgendes:
> 4) The following is the function that retrieves the records from pss :
> 
> CREATE or replace  FUNCTION ftoc9() RETURNS setof  structrankmaster2
> LANGUAGE 'plpgsql' 
> 
>  AS' DECLARE 
>  rowdata pss%rowtype;
> BEGIN for i in 1..3 loop
> select * into rowdata from pss ;
> return next rowdata ;
> end loop;
> return;
> end';

Your loop is wrong, for i in 1..3 select... and then returns the first
record.


Change this to:

BEGIN .. for rowdata in select * from pss ;   return next rowdata ; end loop; ..
END;

*untestet*




HTH, Andreas
-- 
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47215,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net===    Schollglas Unternehmensgruppe    === 

> CREATE or replace  FUNCTION ftoc9() RETURNS setof  structrankmaster2  
> LANGUAGE 'plpgsql'
>
>  AS' DECLARE
>
>  rowdata pss%rowtype;
>
> BEGIN for i in 1..3 loop
>
> select * into rowdata from pss ;
>
> return next rowdata ;
>
> end loop;
>
> return;
>
> end';
>
The query should be outside the loop, otherwise you are re-running the 
query each time :-)

Alex

On Fri, 2006-04-28 at 12:56 +0200, A. Kretschmer wrote: 
> am  28.04.2006, um 16:14:10 +0530 mailte Penchalaiah P. folgendes:
> > 4) The following is the function that retrieves the records from pss :
> > 
> > CREATE or replace  FUNCTION ftoc9() RETURNS setof  structrankmaster2
> > LANGUAGE 'plpgsql' 
> > 
> >  AS' DECLARE 
> >  rowdata pss%rowtype;
> > BEGIN for i in 1..3 loop
> > select * into rowdata from pss ;
> > return next rowdata ;
> > end loop;
> > return;
> > end';
> 
> Your loop is wrong, for i in 1..3 select... and then returns the first
> record.
> 
> 
> Change this to:
> 
> BEGIN
>   ..
>   for rowdata in select * from pss ;
>     return next rowdata ;
>   end loop;
>   ..
> END;
> 
> *untestet*

If you meant to return the first 3 records, then:

...
begin
for rowdata in select * from pss limit 3 loop   return next rowdata ;end loop;return;
end';

You can also return a SETOF pss without creating the structrankmaster2
type.

If this is actually all you are after, and not just a simplified example
then you could also use this (also not tested):

CREATE FUNCTION ftoc9() RETURNS SETOF pss
AS $$   SELECT * FROM pss LIMIT 3;
$$ LANGUAGE SQL;


If you do use LIMIT, then ORDER BY might also be needed as well.

Ross