0

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.

1 Answer 1

1

The following Q/A has the reason and the solution to your problem:

PL/pgSQL: accessing fields of an element of an array of custom type

Sign up to request clarification or add additional context in comments.

1 Comment

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.