Обсуждение: set return function is returning a single record, multiple times,how can i get all the records in the table( description inside )
Hi
Please spare some time to provide a solution for the described problem :
I am using set returning functions to return all the records from a table named pss ,
But what I am getting is the first record is returned as many times , the number of records present in the rank_master:
I am giving a detailed description below please check it out
1) The following query creates pss table:
create table pss( name varchar(20), num integer, phno integer );
2) insert three records in to pss :
insert into pss values(‘penchal’,1,420);
insert into pss values(‘joe’,2,421);
insert into pss values(‘ali’,3,422);
3) create an user defines type of variable named structrankmaster2 ( something like a structure to hold a record ) :
create type Structrankmaster2 as (name varchar(20), num integer, phno integer);
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';
5) now call the function from command prompt:
Select * from ftoc9();
6) the following is the output that I am getting ( i.e the first row repeated 3 times ) :
name | num | phno
---------+-----+------
penchal | 1 | 420
penchal | 1 | 420
penchal | 1 | 420
(3 rows)
7) what exactly I should be getting is :
name | num | phno
---------+-----+------
penchal | 1 | 420
joe | 2 | 421
penchal | 3 | 422
(3 rows)
Please provide a solution for this so that I can get
name | num | phno
---------+-----+------
penchal | 1 | 420
joe | 2 | 421
penchal | 3 | 422
(3 rows)
Thanks & regards
Thanks & Regards
Penchal reddy | Software Engineer
Infinite Computer Solutions | Exciting Times…Infinite Possibilities...
SEI-CMMI level 5 | ISO 9001:2000
IT SERVICES | BPO
Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government
Tel +91-80-4133-0000(Ext:503)| Fax +91-80-51930009 | Cell No +91-9886774209|www.infics.com
Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at info.in@infics.com and delete this mail from your records.
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