Re: Substitute a variable in PL/PGSQL.

On Wed, Jul 23, 2008 at 4:08 AM, Klint Gore <kgore4(at)une(dot)edu(dot)au> 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;

here is a way to do it with record variables...no inner loop but
doesn't the column names. with a little work you could add those with
some queries to information_schema (i don't think it's worth it
though).

create or replace function ff(tablename text) returns setof text as
$$
declare
r record;
begin
for r in
execute 'select record_out(' || tablename || ') as f' ||
' from ' || tablename loop
return next r.f;
end loop;
end;
$$ language plpgsql;

merlin