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@xxxxxxxxxxxxxxxxxxxxx>:
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.
