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.