I come from the Oracle world and 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 in a variable so that I am able to modify individual fields of individual array elements.