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

From: Grant Finnemore <grantf(at)guruhut(dot)co(dot)za>
To: Masaru Sugawara <rk73(at)sea(dot)plala(dot)or(dot)jp>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Does setof record in plpgsql work well in 7.3?
Date: 2002-09-29 11:42:43
Message-ID: 3D96E733.3080803@guruhut.co.za
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


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 majordomo(at)postgresql(dot)org
>
>
>

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2002-09-29 12:26:44 Re: hacker help: PHP-4.2.3 patch to allow restriction of
Previous Message Masaru Sugawara 2002-09-29 10:51:01 Does setof record in plpgsql work well in 7.3?