Re: [HACKERS] Does setof record in plpgsql work well in 7.3?

[Grant Finnemore]

  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;
           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 [EMAIL PROTECTED]
> 
> 
> 

smime.p7s
Description: S/MIME Cryptographic Signature