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

Dirk Mika <Dirk.Mika@mikatiming.de> writes:

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.

Yeah, that seems like it ought to work, and ideally it would. But plpgsql
doesn't currently have support for nested field-of-array-element or
element-of-record-field assignments. (I've looked briefly at this in the
past, and it doesn't seem impossible, but it's definitely not trivial
either.)

You'll have to do some kind of workaround like assigning the whole array
element to a temp variable, fixing the field in the temp variable, then
assigning back :-(

regards, tom lane

Dirk Mika wrote:

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

Hi Dirk,

I don't know the answer to this but I have encountered
a situation where the plpgsql syntax didn't support
something I would have liked (specifically, it
can't/couldn't select into array elements so I needed
to select into multiple scalar variables and then
assign their values to the array elements).

If the problem you are facing is similar in nature, you
might be able to work around it by having a variable of
the same type as the array elements, assign to it the
record that you want to modify, make the modification
in the single record variable and then assign that
single record variable back into the array element that
it came from.

i.e. something like:

declare
a rectype[];
r rectype;
i integer;
begin
...
r := a[i];
r.field := newvalue;
a[i] := r;
...

I didn't even realise that you could have an array of records.
I've only ever used arrays of scalar values.

cheers,
raf

--
Dirk Mika
Software Developer

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

fon +49 2202 2401-1197
dirk.mika@mikatiming.de
www.mikatiming.de

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

## How2Use
## the ChampionChip by mika:timing
## https://youtu.be/qfOFXrpSKLQ
Am 02.07.19, 15:53 schrieb "Tom Lane" <tgl@sss.pgh.pa.us>:

Yeah, that seems like it ought to work, and ideally it would. But plpgsql
doesn't currently have support for nested field-of-array-element or
element-of-record-field assignments. (I've looked briefly at this in the
past, and it doesn't seem impossible, but it's definitely not trivial
either.)

Ah, that explains the error message, of course. And I thought it was actually a matter of correct syntax. :-)

You'll have to do some kind of workaround like assigning the whole array
element to a temp variable, fixing the field in the temp variable, then
assigning back :-(

I tried that and it seemed to work. There is no error message and the function seems to do the right thing. ;-)

Dirk

--
Dirk Mika
Software Developer

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

fon +49 2202 2401-1197
dirk.mika@mikatiming.de
www.mikatiming.de

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

## How2Use
## the ChampionChip by mika:timing
## https://youtu.be/qfOFXrpSKLQ
Am 03.07.19, 01:39 schrieb "raf@raf.org" <raf@raf.org>:

If the problem you are facing is similar in nature, you
might be able to work around it by having a variable of
the same type as the array elements, assign to it the
record that you want to modify, make the modification
in the single record variable and then assign that
single record variable back into the array element that
it came from.

i.e. something like:

declare
a rectype[];
r rectype;
i integer;
begin
...
r := a[i];
r.field := newvalue;
a[i] := r;
...

It actually works the way you suggested. Not nice, but as long as it works, it's ok ;-)

I didn't even realise that you could have an array of records.
I've only ever used arrays of scalar values.

Yes, if that wasn't possible, I would have had to switch to a series of Array of scalar values.

BR
Dirk