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.
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.
