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

From: Dirk Mika <Dirk(dot)Mika(at)mikatiming(dot)de>
To: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: plpgsql: How to modify a field in an array of records
Date: 2019-07-02 07:49:28
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general


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

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 Mika
Software Developer


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

fon +49 2202 2401-1197

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



Browse pgsql-general by date

  From Date Subject
Next Message Prakash Ramakrishnan 2019-07-02 08:02:27 Re:
Previous Message Laurenz Albe 2019-07-02 07:45:03 Re: