Re: Substitute a variable in PL/PGSQL.

On Wed, Jul 23, 2008 at 4:08 AM, Klint Gore <kgore4@une.edu.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