Thread: adressing column

i have the following function:

declare
 prim_col_name Alias for $1;
 prim_col_val Alias for $2;
 my_column Alias for $3;
 link_table Alias for $4;
 link_col Alias for $5;
 data_table alias for $6;
 my_record Record;
 result_string varchar;
 i integer;
begin
 result_string := '';
 i := 0;
 For my_record in execute 'select ' || my_column || ' from ' ||
data_table || ' where ' || link_col || ' in (select ' || link_col || '
from ' || link_table || ' where ' || prim_col_name || ' = ' ||
prim_col_val || ') order by lower(' || my_column || ')' loop
     if i = 0 then
        result_string := my_record.col;
     else
        result_string := result_string || ', ' || my_record.col;
     end if;
     i := i + 1;
 end loop;
 return result_string;
end


now i am searching for a way, to access my_column in my_record (now it
just works if  my_column = 'col')
can anyone help please!
thanx
steffn

On Wednesday 01 October 2003 11:20, Stefan Leitich wrote:
> i have the following function:
[snip code]
>
> now i am searching for a way, to access my_column in my_record (now it
> just works if  my_column = 'col')
> can anyone help please!

That's not going to work in plpgsql at the moment. Because it's "compiled", it
has problems with things like this.

The usual answer is to use pltcl or some other more dynamic language.

In you particular example you could just build your execute statement as:
 ... || my_column || ' AS foo FROM ' || ...

Then you can access the column as "foo"
--
  Richard Huxton
  Archonet Ltd

thanx a lot.
i thought much to complicated!
this will work in my case perfectly!

Richard Huxton wrote:

On Wednesday 01 October 2003 11:20, Stefan Leitich wrote: 

i have the following function:   

[snip code] 

now i am searching for a way, to access my_column in my_record (now it
just works if  my_column = 'col')
can anyone help please!   

That's not going to work in plpgsql at the moment. Because it's "compiled", it 
has problems with things like this.

The usual answer is to use pltcl or some other more dynamic language.

In you particular example you could just build your execute statement as:... || my_column || ' AS foo FROM ' || ...

Then you can access the column as "foo"