From: Ross Johnson <ross.johnson@homemail.com.au>
To: PostgreSQL SQL List <pgsql-sql@postgresql.org>
Subject: Re: set return function is returning a single record,
Date: Sat, 29 Apr 2006 01:24:45 +1000
Message-ID: <1146237886.13720.12.camel@desk.home> (raw)
In-Reply-To: <20060428105621.GD8542@webserv.wug-glas.de>
References: <A70AE049E81A6A40879D5DC0AC8C38C905FA27A3@venus.infics.com>
<20060428105621.GD8542@webserv.wug-glas.de>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ross.johnson@homemail.com.au
Subject: Re: set return function is returning a single record,
In-Reply-To: <1146237886.13720.12.camel@desk.home>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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