Thread: plpgsql: How to modify a field in an array of records
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 |
|
fon +49 2202 2401-1197 |
AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884 Geschäftsführer: Harald Mika, Jörg Mika |
Attachment
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 ideawhat'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 likethis: > > 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 ideawhat'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 modifyindividual 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