plpgsql: How to modify a field in an array of records - Mailing list pgsql-general

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