Skip site navigation (1) Skip section navigation (2)

Does setof record in plpgsql work well in 7.3?

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 (view raw or flat)
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



Responses

pgsql-hackers by date

Next:From: Grant FinnemoreDate: 2002-09-29 11:42:43
Subject: Re: Does setof record in plpgsql work well in 7.3?
Previous:From: Mario WeilguniDate: 2002-09-29 08:12:56
Subject: Re: How to REINDEX in high volume environments?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group