Substitute a variable in PL/PGSQL.

[Steve Martin <steve.martin@xxxxxxxxx>]

Hi,

I am trying to create a PL/PGSQL function to return the values of the 
fields in a record, e.g. 1 value per row in the output of the function.

How do you substitute a variable?

Test case:

CREATE TABLE test(col1 text, col2 text, col3 text, col4 text, col5 text, 
col6 text, col7 text, col8 text, col9 text, col10 text);
INSERT INTO test VALUES ('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j');
INSERT INTO test VALUES ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J');
INSERT INTO test VALUES ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10');

CREATE OR REPLACE FUNCTION testfunc() RETURNS SETOF text AS $$
DECLARE     
   ted varchar;
   bob RECORD;
BEGIN
   FOR bob IN SELECT * FROM test LOOP
       FOR i IN 1..10 LOOP
           ted := 'bob.col' || i;
           RETURN NEXT ted;
       END LOOP;
   END LOOP;
   RETURN;
END
$$ LANGUAGE plpgsql;

test=> select * from testfunc();
testfunc  
-----------
bob.col1
bob.col2
bob.col3
bob.col4
bob.col5
bob.col6
bob.col7
bob.col8
bob.col9
bob.col10
bob.col1
bob.col2
bob.col3
bob.col4
bob.col5
bob.col6
bob.col7
bob.col8
bob.col9
bob.col10
bob.col1
bob.col2
bob.col3
bob.col4
bob.col5
bob.col6
bob.col7
bob.col8
bob.col9
bob.col10
(30 rows)

test=> 


Or:
CREATE OR REPLACE FUNCTION testfunc() RETURNS SETOF text AS $$
DECLARE      
   bob RECORD;
   ted TEXT;
BEGIN
   FOR i IN 1..10 LOOP
       ted := 'col' || i;
       FOR bob IN SELECT ted FROM test LOOP
           RETURN NEXT bob;
       END LOOP;
   END LOOP;
   RETURN;
END
$$ LANGUAGE plpgsql;
test=> select * from testfunc();
testfunc 
----------
(col1)
(col1)
(col1)
(col2)
(col2)
(col2)
(col3)
(col3)
(col3)
(col4)
(col4)
(col4)
(col5)
(col5)
(col5)
(col6)
(col6)
(col6)
(col7)
(col7)
(col7)
(col8)
(col8)
(col8)
(col9)
(col9)
(col9)
(col10)
(col10)
(col10)
(30 rows)

test=> 

Or is there another way other than using another procedural language.

Thanks - Steve M.