Re: Substitute a variable in PL/PGSQL.

Steve Martin wrote:

> 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.

Found that this function works if I process by column.
CREATE OR REPLACE FUNCTION testfunc() RETURNS SETOF text AS $$
DECLARE
bob RECORD;
ted TEXT;
may TEXT;
BEGIN
FOR i IN 1..10 LOOP
ted := 'col' || i;
may := ' SELECT ' || ted || ' as col FROM test';
FOR bob IN EXECUTE may LOOP
RETURN NEXT bob.col;
END LOOP;
END LOOP;
RETURN;
END
$$ LANGUAGE plpgsql;

test=> select testfunc as data from testfunc() ;
data
------
a
A
1
b
B
2
c
C
3
d
D
4
e
E
5
f
F
6
g
G
7 d
D
4
e
E
5
f
F
6
g
G
7
h
H
8
i
I
9
j
J
10
(30 rows)

test=>

Any ideas on how to process by row?

Steve Martin