Subject: Re: set return function is returning a single record,
From: Ross Johnson <ross.johnson@homemail.com.au>
To: PostgreSQL SQL List <pgsql-sql@postgresql.org>
In-Reply-To: <20060428105621.GD8542@webserv.wug-glas.de>
References: <A70AE049E81A6A40879D5DC0AC8C38C905FA27A3@venus.infics.com>
	<20060428105621.GD8542@webserv.wug-glas.de>
Content-Type: text/plain
Date: Sat, 29 Apr 2006 01:24:45 +1000
Message-Id: <1146237886.13720.12.camel@desk.home>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

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