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

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

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

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