Re: porting vb6 code to pgplsql, referencing fields - Mailing list pgsql-general

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.