Re: Substitute a variable in PL/PGSQL.

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.


--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au