Re: Oracle Associate Array conversion to PostgreSQL

Hi

čt 10. 9. 2020 v 7:12 odesílatel JAGMOHAN KAINTURA <
jagmohan(dot)kaintura(at)gmail(dot)com> 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
>