pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedset return function is returning a single record, multiple times, how can i get all the records in the table( description inside )
4+ messages / 4 participants
[nested] [flat]
* set return function is returning a single record, multiple times, how can i get all the records in the table( description inside )
@ 2006-04-28 10:44 Penchalaiah P. <penchalaiahp@infics.com>
2006-04-28 10:56 ` Re: set return function is returning a single record, multiple times, how can i get all the records in the table( description inside ) A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-04-28 10:58 ` Re: set return function is returning a single record, multiple Alexis Paul Bertolini <bertolini@computer.org>
0 siblings, 2 replies; 4+ messages in thread
From: Penchalaiah P. @ 2006-04-28 10:44 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: set return function is returning a single record, multiple times, how can i get all the records in the table( description inside )
2006-04-28 10:44 set return function is returning a single record, multiple times, how can i get all the records in the table( description inside ) Penchalaiah P. <penchalaiahp@infics.com>
@ 2006-04-28 10:56 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-04-28 15:24 ` Re: set return function is returning a single record, Ross Johnson <ross.johnson@homemail.com.au>
1 sibling, 1 reply; 4+ messages in thread
From: A. Kretschmer @ 2006-04-28 10:56 UTC (permalink / raw)
To: pgsql-sql
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 ===
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: set return function is returning a single record,
2006-04-28 10:44 set return function is returning a single record, multiple times, how can i get all the records in the table( description inside ) Penchalaiah P. <penchalaiahp@infics.com>
2006-04-28 10:56 ` Re: set return function is returning a single record, multiple times, how can i get all the records in the table( description inside ) A. Kretschmer <andreas.kretschmer@schollglas.com>
@ 2006-04-28 15:24 ` Ross Johnson <ross.johnson@homemail.com.au>
0 siblings, 0 replies; 4+ messages in thread
From: Ross Johnson @ 2006-04-28 15:24 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: set return function is returning a single record, multiple
2006-04-28 10:44 set return function is returning a single record, multiple times, how can i get all the records in the table( description inside ) Penchalaiah P. <penchalaiahp@infics.com>
@ 2006-04-28 10:58 ` Alexis Paul Bertolini <bertolini@computer.org>
1 sibling, 0 replies; 4+ messages in thread
From: Alexis Paul Bertolini @ 2006-04-28 10:58 UTC (permalink / raw)
To: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-04-28 15:24 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-28 10:44 set return function is returning a single record, multiple times, how can i get all the records in the table( description inside ) Penchalaiah P. <penchalaiahp@infics.com>
2006-04-28 10:56 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-04-28 15:24 ` Re: set return function is returning a single record, Ross Johnson <ross.johnson@homemail.com.au>
2006-04-28 10:58 ` Re: set return function is returning a single record, multiple Alexis Paul Bertolini <bertolini@computer.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox