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@postgresql.org

Search Discussions

Discussion Posts

Previous

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 2 of 3 | next ›
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedSep 29, '02 at 10:50a
activeSep 29, '02 at 3:57p
posts3
users2
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase