plpgsql: How to modify a field in an array of records

[Dirk Mika <Dirk.Mika@xxxxxxxxxxxxx>]

Hi,

 

Another Oracle -> PostgreSQL Question. ☺

 

I try to migrate a package procedure. Local types were declared in the Oracle package:

 

  TYPE t_class_record IS RECORD

  (

     id_class       classes.id_class%TYPE,

     field1         number,

     field2         number

  );

 

  TYPE t_classes_table IS TABLE OF t_class_record

     INDEX BY BINARY_INTEGER;

 

  l_classes_table                     t_classes_table;

 

l_classes_table is initialized by a SELECT statement where later single fields of single array elements are modified like this:

 

  l_classes_table(i).field1 := l_value;

 

So far I have done the following in PostgreSQL:

 

• Defined a composite type that corresponds to the structure listed above:
  CREATE TYPE t_class_record AS (id_class CHARACTER VARYING,
                                 field1   INTEGER,
                                 field2   INTEGER);
  
  
• Defined a procedure with a local variable of type Array of t_class_record:
  l_classes_array                   t_class_record [];
  
  

But when I try to modify a field of a record in the array I get a syntax error.

 

l_classes_array[i].field1 := l_value;

 

The error is ERROR: syntax error at or near "." Position: 12414 where position points to the . after the [i]. I've no idea what's causing this syntax error.

 

My goal is to store an array of records, fetched via SELECT Statement, in a variable in a way, that I am able to modify individual fields of individual array elements later in the function.

 

Am I on the right track or should I approach the problem completely differently?

 

Dirk

 

--
Dirk Mika
Software Developer

mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
dirk.mika@xxxxxxxxxxxxx
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika