Re: Oracle Associate Array conversion to PostgreSQL

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

Hi

čt 10. 9. 2020 v 7:12 odesílatel JAGMOHAN KAINTURA <jagmohan.kaintura@xxxxxxxxx> napsal:

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.

 Unfortunately, the direct update is not possible. You need use helper variable

create type tt as (a int, b int);

do $$                            
declare

  x tt[] = ARRAY[(10,20),(30,40)];

  _x tt;
begin

  _x := x[1];
  _x.a := 100;

  x[1] := _x;
  raise notice '%', x;
end;
$$;

you can read a field of an item of an array of some composite type with parenthesis - (x[1]).a, but this syntax is not supported on the left part of the assign statement.

Regards

Pavel

Best Regards,

Jagmohan