Postgres plpgsql - ARRAY record attribute asssignment

Question

Will appreciate if someone can please guide if Postgres allows assignment for a ARRAY record attribute. For example - dummy set up.

Trying to update an ttribute of a Record (part of array) . The direct assignment does not work. Have to create a roundabout way of creating extra record, assignment and swapping.

 create table dummy_array (id numeric, descr varchar(100));
 insert into dummy_array values(1,'TEST1'),(2,'TEST2');
 select * from dummy_array;
 do
  $function$
   declare
    v_array dummy_array[];
    v_dummy_rec dummy_array;
       begin
        v_array=array(select ROW(id,descr) from dummy_array);
         raise notice '%',v_array[1].descr; 
         --FOLLOWING DIRECT ASSIGNMENT DOES NOT WORK
         v_array[1].descr :='ZZZ';--This assignment errors out 
         end ;
   $function$

      Errors out with SQL ERROR[42601]:ERROR:syntax error at or near "."
      Tried even  (v_array[1]).descr :='ZZZ'; does not work

Following Works but a hack create temp, copy,assign ,modify reassing

         do
         $function$
          declare
           v_array dummy_array[];
           v_dummy_rec dummy_array;
         begin
           v_array=array(select ROW(id,descr) from dummy_array);
           raise notice '%',v_array[1].descr;   
           **--v_array[1].descr :='ZZZ';--This assignment errors out**
           v_dummy_rec :=v_array[1];
           v_dummy_rec.descr :='ZZZ';
           v_array[1]:=v_dummy_rec;
           raise notice '%',v_array[1].descr;   
          end ;
         $function$

Can somone please guide me if it is possible to do direct assignments like shown above

        **v_array[1].descr :='ZZZ';--This assignment errors out**

Dan · Accepted Answer · 2018-03-21T16:51:54.307

The problem is that your v_array has the type dummy_array[], so it will only accept a dummy_array[] value.

This works v_array[1] := ROW(1,'ZZZ');

 do
  $function$
   declare
    v_array dummy_array[];
    v_dummy_rec dummy_array;
       begin
        v_array=array(select ROW(id,descr) from dummy_array);
         raise notice '%',v_array[1].descr; 
         v_array[1] := ROW(1,'ZZZ'); 
        raise notice '%',v_array[1].descr; 
         end ;
   $function$

Answer to clarificate comment

In PostgreSQL it is possible to update individual attributes of a record (see here). In your case the problem is the array, there is where PostgreSQL fails; because it seems it doesn't compile the v_array[1] as a record variable when running the code (PL), it just see it as an array element (therefore the error message). This can be "fixed" using an extra variable of type dummy_array without the array; that is your v_dummy_rec:

DO $function$
DECLARE
    v_array dummy_array[];
    v_dummy_rec dummy_array;
BEGIN
    v_array=array(select ROW(id,descr) from dummy_array);
      raise notice '%',v_array[1].descr; 
    v_array[1] := ROW(1,'ZZZ'); 
      raise notice '%',v_array[1].descr;
-- Here is the use of v_dummy_rec:
    v_dummy_rec := v_array[1];
    v_dummy_rec.descr := 'AAA';
    v_array[1] := v_dummy_rec;
      raise notice '%',v_array[1].descr;
END; $function$

The RAISE NOTICE outputs:

NOTICE:  TEST1
NOTICE:  ZZZ
NOTICE:  AAA

Thanks for your reply. Sorry , my question was not clear. My question stems from Oracle to postgres migration. Oracle allows an individual attribure of a record(which is part of array to be updated). assignments like v_array[1].descr :='ZZZ'; are valid where as Postgres did not allow. Wanted to know if such attribute assignment are note allowed and the whole record need to be updated vs it is an issue with my syntax and some alternate syntax exists. In my case record struture is wide and only very small subset of attribute need update as we go over the array iteratively . — PostgresQuestion, Mar 21 '18 at 14:59

Postgres plpgsql - ARRAY record attribute asssignment

1 Answers1