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

CREATE OR REPLACE FUNCTION myfunc(integer) RETURNS SETOF record AS '   DECLARE      rec record;   BEGIN      FOR rec
INSELECT * FROM test WHERE a = $1 LOOP          RAISE NOTICE ''a = %, b = %'',rec.a, rec.b;          RETURN NEXT rec;
  END LOOP;
 
      RETURN null;   END; ' LANGUAGE 'plpgsql';
 SELECT * FROM myfunc(1) AS t(a integer, b text);

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 ...

One can also create your own return type in the following
manner.

create type my_return_type as (  foo integer,  bar text
);

Now, the declare block has the following:-
DECLARE  rec my_return_type%ROWTYPE

The function definition then becomes:- CREATE OR REPLACE FUNCTION myfunc(integer) RETURNS SETOF my_return_type ...

Regards,
Grant Finnemore

Masaru Sugawara wrote:
> Hi, all
> 
> Does 7.3 support "SETOF RECORD" in plpgsql ?
> As far as I test it, a function using it in plpgsql always seems to return
> no row. On the other hand,  a sql function returns correct rows.  
> 
> If 7.3 doesn't support it in plpgsql, I would think plpgsql needs to raise
> an error rather than return "0 rows" message. Am I misunderstanding
> how to use? 
> 
> 
> ------------------------------------------------------
> CREATE TABLE test (a integer, b text);
> INSERT INTO test VALUES(1, 'function1');
> INSERT INTO test VALUES(2, 'function2');
> INSERT INTO test VALUES(1, 'function11');
> INSERT INTO test VALUES(2, 'function22');
> 
> 
> CREATE OR REPLACE FUNCTION myfunc(integer) RETURNS SETOF record AS '
>   DECLARE
>      rec record;
>   BEGIN
>      FOR rec IN SELECT * FROM test WHERE a = $1 LOOP
>          RAISE NOTICE ''a = %, b = %'',rec.a, rec.b;
>      END LOOP; 
>      RETURN rec;
>   END;
> ' LANGUAGE 'plpgsql';
> 
> SELECT * FROM myfunc(1) AS t(a integer, b text);
> 
> NOTICE:  a = 1, b = function1
> NOTICE:  a = 1, b = function11
>  a | b 
> ---+---
> (0 rows)
> 
> 
> 
> CREATE OR REPLACE FUNCTION myfunc(integer) RETURNS SETOF record AS '
>     SELECT * FROM test WHERE a = $1;
> ' LANGUAGE 'sql';
> 
> SELECT * FROM myfunc(1) AS t(a integer, b text);
> 
>  a |     b      
> ---+------------
>  1 | function1
>  1 | function11
> (2 rows)
> 
> 
> 
> Regards,
> Masaru Sugawara
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> 
> 
>