Re: Oracle Associate Array conversion to PostgreSQL

Hi All,

Since we don't have the support of Associative arrays in Postgres, we need to convert Oracle associative arrays with a different approach in PostgreSQL.  

Following is a sample piece of code to demonstrate the same.

--Convert Oracle RECORD type to PostgreSQL TYPE.
--=============================================
create type SLOC_TB as ( IN_EFFECT VARCHAR(1),
SUB_LOC_C VARCHAR(6),
START_DT timestamp(0),
END_DT timestamp(0),
SLOC_PHRASE_N VARCHAR(5)
);
---================================================

--Below is simple anonymous block, where TYPE is used as an ARRAY ([]).  TYPE is used as an array object to allow storing multiple rows/records supported by Index (like index by binary integer in Oracle).

do
$$
declare
  lv_list SLOC_TB[];
  idx record;
begin
     lv_list[1] = ROW('X','XYZ',sysdate(),sysdate()+1, 'abc');
RAISE INFO '%', lv_list[1].IN_EFFECT;
RAISE INFO '%', lv_list[1].SUB_LOC_C;
RAISE INFO '%', lv_list[1].START_DT;
RAISE INFO '%', lv_list[1].END_DT;
RAISE INFO '%', lv_list[1].SLOC_PHRASE_N;
     lv_list[2] = ROW('D','Tecore',sysdate(),sysdate()+1, 'MIG');
RAISE INFO '%', lv_list[2].IN_EFFECT;
RAISE INFO '%', lv_list[2].SUB_LOC_C;
RAISE INFO '%', lv_list[2].START_DT;
RAISE INFO '%', lv_list[2].END_DT;
RAISE INFO '%', lv_list[2].SLOC_PHRASE_N;
end$$;

==========================================================
--tested the anonymous block and returns values correctly.

zdcqpoc=> do
zdcqpoc-> $$
zdcqpoc$> declare
zdcqpoc$>   lv_list SLOC_TB[];
zdcqpoc$>   idx record;
zdcqpoc$> begin
zdcqpoc$>      lv_list[1] = ROW('X','XYZ',sysdate(),sysdate()+1, 'abc');
zdcqpoc$>  RAISE INFO '%', lv_list[1].IN_EFFECT;
zdcqpoc$>  RAISE INFO '%', lv_list[1].SUB_LOC_C;
zdcqpoc$>  RAISE INFO '%', lv_list[1].START_DT;
zdcqpoc$>  RAISE INFO '%', lv_list[1].END_DT;
zdcqpoc$>  RAISE INFO '%', lv_list[1].SLOC_PHRASE_N;
zdcqpoc$>      lv_list[2] = ROW('D','Tecore',sysdate(),sysdate()+1, 'MIG');
zdcqpoc$>  RAISE INFO '%', lv_list[2].IN_EFFECT;
zdcqpoc$>  RAISE INFO '%', lv_list[2].SUB_LOC_C;
zdcqpoc$>  RAISE INFO '%', lv_list[2].START_DT;
zdcqpoc$>  RAISE INFO '%', lv_list[2].END_DT;
zdcqpoc$>  RAISE INFO '%', lv_list[2].SLOC_PHRASE_N;
zdcqpoc$> end$$;
INFO:  X
INFO:  XYZ
INFO:  2020-09-08 03:29:52
INFO:  2020-09-09 03:29:52
INFO:  abc
INFO:  D
INFO:  Tecore
INFO:  2020-09-08 03:29:52
INFO:  2020-09-09 03:29:52
INFO:  MIG
DO

But a problem arises when we want to assign any value to a specific column to array type.

In Oracle we mostly do this way :

   lv_list[2].START_DT  := sysdate +1;

But above does not work in PostgreSQL. It says syntax error at ".".

What would be the method for this type of single element assignment in an array created from composite type.

Best Regards,

Jagmohan