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

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

[cid:image001_d0a5cf51-5dd3-4ff5-b428-1016a6d95a0f.png]

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

fon +49 2202 2401-1197
dirk(dot)mika(at)mikatiming(dot)de
www.mikatiming.de

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

[cid:CCh2U_f27a5ce6-8556-4db1-8aa4-fd610d6e9efe.jpg]<https://youtu.be/qfOFXrpSKLQ>