Re: set return function is returning a single record,

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