Re: porting vb6 code to pgplsql, referencing fields

thanks Craig

your assumption is right.

I have a given table structure, so redesign it now is not possible due to
having change a lot of things
Furthermore, using M3TRAM INTEGER[5], PREU NUMERIC(10,2)[5]
seems to me a very good way but I think it may appear problems when
accessing to that table
from third party apps such as excel, odbc, ... isn't it?
So the simplest way could be the most suitable one.
However, imagine I had more fields....

Is not really possible to 'calculate' a string, that is the field name, yeah
like it was an array,
and reference a field in a row using that string?
something like this
s:='PREU1';
row_tfa.s := x;

anyway, I a completely newbie in pgplsql, and I see my way of thinking is
not pgplsql

thanks

Josep Porres

2008/3/13, Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>:
>
> josep porres wrote:
>
> > but the most important is how can I reference the fields inside de loop
>
>
> By "the fields" I assume you mean the fields with names that end in a
> number from 1 to 5, and you want to access those fields in a loop as if
> you were indexing an array?
>
> I think you might want to explain what you're actually trying to do, as
> the right answer might not really be how to load/store your array but
> might involve looking at how and why you're using arrays this way too.
>
> In particular, maybe it's better to store an array in the record.
>
>
>
>
> Looking at your VB6 code it appears that your f2_tarifa_a table has some
> sequentially numbered fields, and might be defined like (assuming a
> SERIAL pkey):
>
> CREATE TABLE f2_tarifa_a (
> id SERIAL PRIMARY KEY,
> -- other values
> M3TRAM1 INTEGER,
> PREU1 NUMERIC(10,2)
> M3TRAM2 INTEGER,
> PREU2 NUMERIC(10,2)
> M3TRAM3 INTEGER,
> PREU3 NUMERIC(10,2)
> M3TRAM4 INTEGER,
> PREU4 NUMERIC(10,2)
> M3TRAM5 INTEGER,
> PREU5 NUMERIC(10,2)
> );
>
> ... and you're essentially using it to store 5-element arrays. You have
> a few options here. The simplest is probably just to explicitly fetch
> each element of the array, eg:
>
> Ma[1] := row_tfa.M3TRAM1;
> Mpa[1] := row_tfa.PREU1;
> Ma[2] := row_tfa.M3TRAM2;
> Mpa[2] := row_tfa.PREU2;
>
> etc.
>
> Alternately you could adjust your schema to store arrays:
>
>
> CREATE TABLE f2_tarifa_a (
> id SERIAL PRIMARY KEY,
> -- other values
> M3TRAM INTEGER[5],
> PREU NUMERIC(10,2)[5]
> );
>
> ... and fetch/store those directly.
>
> Another option is to switch from using an array to a secondary table. If
> your arrays are in any way variable in length that's probably a good
> ideea. For example:
>
>
> CREATE TABLE f2_tarifa_a (
> id SERIAL PRIMARY KEY,
> -- other values
> );
>
> CREATE TABLE f2_tarifa_a_trampreu (
> f2_tarifa_a_id INTEGER REFERENCES f2_tarifa_a(id) ON DELETE CASCADE,
> M3TRAM INTEGER,
> PREU NUMERIC(10,2)
> );
> CREATE INDEX f2_tarifa_a_trampreu_fkey_id
> ON f2_tarifa_a_trampreu(f2_tarifa_a_id);
>
> ... then you can FOR loop though the secondary table.
>