Thread: porting vb6 code to pgplsql, referencing fields

Hi everyone,

I'm trying to port some vb6 code to  pgplsql  (PostgreSQL 8.3 winxp)

that code is

    Const f2_MAX_TRAMS = 5
    Dim f2_rTarifaA as new ADODB.Recordset
    Dim Mpa(f2_MAX_TRAMS) As Double ' preu aigua
    Dim Ma(f2_MAX_TRAMS) As Long    ' m3 aigua   tarifa
    Dim i As Integer, j As Integer ' indexs matrius
        
    ...
    ( open connection, open recordset, etc )
    ...

    -- fill array with field values of M3TRAM1, ..., M3TRAM5
    --                                 PREU1, ..., PREU5
    for i = 1 to f2_MAX_TRAMS
        Ma(i)  = f2_rTarifaA.Fields("M3TRAM" + CStr(i)).Value
        Mpa(i) = f2_rTarifaA.Fields("PREU" + CStr(i)).Value
    next




in pgplsql, more or less
     
DECLARE

    c_tarifa_c CURSOR (dfac DATE, key INTEGER) IS SELECT * FROM F2_TARIFA_C WHERE TIPUS = key AND dfac BETWEEN DINICI AND DFINAL;
    f2_MAX_TRAMS CONSTANT INTEGER := 5;
    Ma       INTEGER[5];     
    Mpa      NUMERIC(10,2)[5];
    row_tfa  f2_tarifa_a%rowtype;

BEGIN

    OPEN c_tarifa_a (datafac, f2_Mtar);
    FETCH c_tarifa_a INTO row_tfa;
    CLOSE c_tarifa_a;

    For i IN 1..f2_MAX_TRAMS LOOP
      Ma[i]  := row_tfa. ?????  -- "M3TRAM" + CStr(i)).Value
      Mpa[i] := row_tfa. ?????  -- "PREU" + CStr(i)).Value
    END LOOP;


END

I would like to know some tips about:

How can I declare arrays especifying the size with a constant,
but the most important is how can I reference the fields inside de loop


Thanks in advance


Josep

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.

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

josep porres wrote:

> 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?

I don't know, but I would not be too surprised if that could be an
issue. I avoid array types myself except in "private" parts of the
database that're only exposed to apps indirectly via views or stored
procedures. In fact, I really only use them in stored procedures and
rarely then.

> So the simplest way could be the most suitable one.
> However, imagine I had more fields....

That's why I suggested using a secondary table. Adding fields won't be
fun the way you're doing things.

> 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;

It can probably be done using PL/PgSQL's EXECUTE statement. In
PostgreSQL 8.3 this supports the INTO clause (you had to use some
less-than-pretty workarounds in previous versions) so you can write
something like:

FOR IN 1..5 LOOP
     EXECUTE 'SELECT row'||rownum||'FROM blah'
     INTO STRICT result_variable[i];
END LOOP

However, as far as I know you cannot access the value of local variables
in EXECUTEd SQL. So if you've DECLARE'd a variable that you're storing a
row in, you won't be able to generate a query that can access arbitrary
columns of it. You can go and SELECT the original row again, but this
will of course get slow (10 queries per row the way you're doing it) and
it's ugly.

It's also important to understand that EXECUTEd queries are re-planned
every time they're run. That makes them expensive relative to normal
assignments, SELECT INTO, etc in PL/PgSQL.

I guess you could write an EXECUTE query that assembled an array
literal. Consider the following example:

BEGIN;

CREATE TABLE fiveints(
     a INTEGER,
     b INTEGER,
     c INTEGER,
     d INTEGER,
     e INTEGER
) WITH(OIDS=FALSE);

INSERT INTO fiveints (a,b,c,d,e) VALUES (1,2,3,4,5);

CREATE OR REPLACE FUNCTION testfn() RETURNS integer[5] AS $$
DECLARE
     arr INTEGER[5];
BEGIN
     EXECUTE 'SELECT ARRAY[a,b,c,d,e] FROM fiveints' INTO STRICT arr;
     RETURN arr;
END;
$$ LANGUAGE 'plpgsql';

SELECT testfn();



... which when executed outputs:

    testfn
-------------
  {1,2,3,4,5}
(1 row)


Note the use of array constructor syntax.




The version for handling multiple values would be:



BEGIN;

CREATE TABLE fiveints(
     a INTEGER,
     b INTEGER,
     c INTEGER,
     d INTEGER,
     e INTEGER
) WITH(OIDS=FALSE);

INSERT INTO fiveints (a,b,c,d,e) VALUES (1,2,3,4,5);
INSERT INTO fiveints (a,b,c,d,e) VALUES (11,21,31,41,51);

CREATE OR REPLACE FUNCTION testfn() RETURNS setof integer[5] AS $$
DECLARE
     arr INTEGER[5];
BEGIN
     FOR arr IN EXECUTE 'SELECT ARRAY[a,b,c,d,e] FROM fiveints' LOOP
         RETURN NEXT arr;
     END LOOP;
     RETURN;
END;
$$ LANGUAGE 'plpgsql';

SELECT * FROM testfn();




I think you're trying to swim upstream here, personally, and do
something very much the hard way, but it sounds like you're stuck with
existing apps with inflexible designs that you need to accommodate. Even
then, maybe you can use some stored procedures and updateable views to
provide the old interface for those apps, while internally changing the
database's structure to something a bit nicer to work with.

--
Craig Ringer