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

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