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.
