Re: Substitute a variable in PL/PGSQL.

From: Steve Martin <steve(dot)martin(at)nec(dot)co(dot)nz>
To: Klint Gore <kgore4(at)une(dot)edu(dot)au>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Substitute a variable in PL/PGSQL.
Date: 2008-07-27 23:15:31
Message-ID: 488D0193.3050206@nec.co.nz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Steve Martin 2008-07-27 23:18:33 Re: Substitute a variable in PL/PGSQL.
Previous Message Steve Martin 2008-07-27 23:04:56 Re: Substitute a variable in PL/PGSQL.