Skip site navigation (1) Skip section navigation (2)

Re: Does setof record in plpgsql work well in 7.3?

From: Masaru Sugawara <rk73(at)sea(dot)plala(dot)or(dot)jp>
To: Grant Finnemore <grantf(at)guruhut(dot)co(dot)za>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Does setof record in plpgsql work well in 7.3?
Date: 2002-09-29 15:58:02
Message-ID: 20020930001013.8EFD.RK73@sea.plala.or.jp (view raw or flat)
Thread:
Lists: pgsql-hackers
On Sun, 29 Sep 2002 13:42:43 +0200
Grant Finnemore <grantf(at)guruhut(dot)co(dot)za> wrote:

> Note the use of the "RETURN NEXT rec" line in the body
> of the for loop, and also the "RETURN null" at the end.
> 
> It is also possible to create typed returns, so in this
> case, in the declare body, the following would be valid.
> DECLARE
>    rec test%ROWTYPE;
> 
> The function definition then becomes:-
>   CREATE OR REPLACE FUNCTION myfunc(integer) RETURNS SETOF test ...


Thank you for your useful info.  the previous function turned out to work
correctly by using "RETURN NEXT rec." And, I found out that plpgsql was
able to nest one.


-- for example
CREATE OR REPLACE FUNCTION myfunc(integer) RETURNS SETOF record AS '
  DECLARE
     rec1 record;
     rec2 record;
     rec3 record;
  BEGIN
     SELECT INTO rec1 max(a) AS max_a FROM test;
   
     FOR rec2 IN SELECT * FROM test WHERE a = $1 LOOP
         SELECT INTO rec3 * FROM
                 (SELECT 1::integer AS a, ''test''::text AS b) AS t;
             RETURN NEXT rec3;
         rec2.a = rec2.a + rec3.a + rec1.max_a;
         RETURN NEXT rec2;
     END LOOP;
     RETURN NEXT rec3;
 
     RETURN;
  END;
' LANGUAGE 'plpgsql';

SELECT * FROM myfunc(1) AS t(a integer, b text);


 a |     b      
---+------------
 1 | test
 5 | function1
 1 | test
 5 | function11
 1 | test
(5 rows)



Regards,
Masaru Sugawara



In response to

pgsql-hackers by date

Next:From: Justin CliftDate: 2002-09-29 16:15:18
Subject: Re: Do we want a CVS branch now?
Previous:From: Tom LaneDate: 2002-09-29 15:36:23
Subject: Re: making use of large TLB pages

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group