Re: Substitute a variable in PL/PGSQL.

Klint Gore wrote:

> Steve Martin wrote:
>
>> 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?
>>
>>
>> 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;
>>
>>
>> Or is there another way other than using another procedural language.
>>
>> Thanks - Steve M.
>>
>
>
> There's no direct way to reference a particular field in a record
> variable where the field name is held in a variable in pl/pgsql.
> I.E. if ted = 'col1' there's no way to reference bob.ted to give you
> the value of bob.col1.
>
> If you want it easy to code but have to create something for every
> table and modify it ever time the table changes
>
> create view test_vertical_table as
> select col1::text from test
> union all
> select col2::text from test
> union all
> select col3::text from test
> union all
> select col4::text from test
> union all
> select col5::text from test
> ...
>
>
> If you want to go the generic function route
>
> CREATE OR REPLACE FUNCTION testfunc(text) RETURNS SETOF text AS $$
> DECLARE        vertTableName alias for $1;
>    ted text;
>    bob RECORD;
>    bill record;
> BEGIN
>    for bill in        select table_name, column_name        from
> information_schema.columns        where table_schema = public
> and table_name = vertTableName
>    loop
>        FOR bob IN            execute 'SELECT '||bill.column_name||' as
> thiscol FROM '||bill.table_name        LOOP
>            ted := bob.thiscol;
>            RETURN NEXT ted;
>        END LOOP;
>    end loop;
>    RETURN;
> END
> $$ LANGUAGE plpgsql;
>
>
>
> klint.
>
Hi Klint,
Thanks for the advice, I found the sql to get the column names useful.
Steve M.