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