Re: Need help to dynamically access to colomns in function!

On Tue, Dec 16, 2008 at 11:37:17PM +0300, IIIIIIII wrote:
> Hello. I have table classif with columns:
> ... , group1, group2, group3, ... , group48, ...
>
> In function i do query and want run on every row and dynamically operate on
> columns from group1 to group20. I do something like this:

It sounds as though you should be using an ARRAY instead of having lots
of columns.

The best I could come up with would be doing something like:

> OPEN curs FOR select * from classif;

OPEN curs FOR SELECT ARRAY[group1,group2,group3,group4] AS group FROM classif;

You'd obviously need to all the way up to "group20" here. If the syntax
gets a bit baroque you could create a view to do the same.

> loop
> fetch curs into tmprec;
> exit when not found;
>
> for I in 1..20 loop
> ...
> -- problem code is:
> value := tmprec.group{I};

value := tmprec.group[I];

> -- i cannot dynamically access to group1, group2, ... colomns according
> to "I" variable.
> ...
>
> end loop;
> end loop;
>
> I have to manually identify and handle each entry without a cycle do
> something like this:

You're using the wrong data type; RECORDs are for where you statically
know and care about the structure of the data, ARRAYs are when you care
at runtime.

Sam