From: | Masaru Sugawara <rk73(at)sea(dot)plala(dot)or(dot)jp> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Does setof record in plpgsql work well in 7.3? |
Date: | 2002-09-29 10:51:01 |
Message-ID: | 20020929194834.EC2F.RK73@sea.plala.or.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Grant Finnemore | 2002-09-29 11:42:43 | Re: Does setof record in plpgsql work well in 7.3? |
Previous Message | Mario Weilguni | 2002-09-29 08:12:56 | Re: How to REINDEX in high volume environments? |