PL/PGSQL create array type table

Question

I am migrating Oracle schema into PostgreSQL 9.5. Currently, I am trying to migrate the next function (already in plpgsql) :

CREATE OR REPLACE FUNCTION Control_Reports_Pg.idd_in_crm_not_in_cc_fn (P_Dir_Name text, P_File_Name text, P_Table_Title text ) RETURNS bigint AS $body$

        DECLARE
        TYPE Area_Table IS TABLE OF VARCHAR2(3) INDEX BY BINARY_INTEGER;

         Area_Array Area_Table
         C_Report CURSOR(V_Area text) FOR
           SELECT NAP_AREA2, NAP_PHONE_NUM, NAP_HEKER
           FROM PS_RF_INST_PROD
           WHERE NAP_AREA2 = V_Area
         EXCEPT
           SELECT AREA, PHONE, btrim(HEKER_NO)
           FROM MANUIM
           WHERE AREA = V_Area;


          V_Counter          bigint:=0;
          V_Step             bigint;
          V_File_Type        UTL_FILE.FILE_TYPE;
          V_Dir_Name         varchar(50) :=P_Dir_Name;
          V_File_Name        varchar(60) := P_File_Name;
          V_Table_Title      varchar(250) := P_Table_Title;
          V_Count_Columns    bigint:=4; -- count of the fields in report
          C_Function_Name    varchar(200) := 'Idd_In_Crm_Not_IN_CC_Fn';
          Array_Count RECORD;
          Rec_Report RECORD;

    BEGIN

    V_Step := 1;

    Area_Array(1) := '02';
    Area_Array(2) := '03';
    Area_Array(3) := '04';
    Area_Array(4) := '08';
    Area_Array(5) := '09';
    Area_Array(6) := '50';
    Area_Array(7) := '52';
    Area_Array(8) := '54';
    Area_Array(9) := '57';
    Area_Array(10) := '74';
    Area_Array(11) := '77';

     FOR Array_Count IN 1..11 LOOP

      FOR Rec_Report IN C_Report(Area_Array(Array_Count)) LOOP
      .......

However, I know that in PostgreSQL I can't declare type table. I searched online and saw a solution that is creating temp table and inserting that table into my variable but I don't know pgplsql well and therefore I don't know how to do it.

Area_Array Area_Table -> Area_Array varchar(3)[];, Area_Array(1) := '02'; Area_Array(2) := '03'; ... -> Area_Array := '{02,03,...}'; and Area_Array(Array_Count) -> Area_Array[Array_Count]. Arrays — Abelisto
regarding this part Area_Array := '{02,03,...}' , the values inside arent suppose to be in quotes to ? Area_Array := '{'02','03'...} — JeyJ
1) Only if value contains some delimiters (spaces, commas etc): select select array['01a','02 03', '22"33']; -> {01a,"02 03","22\"33"} (you can use array[...] syntax to be sure what you doing) 2) No, there are a lot of other ways. — Abelisto

a_horse_with_no_name a_horse_with_no_name · Accepted Answer · 2020-09-26T11:08:27

You don't need to declare a separate type for that, just use an array:

Area_Array varchar[];

There is no real benefit in limiting the length, so varchar(3)[] has no benefit over varchar[] or text[].

Then you can initialize the array like this:

Area_Array := array['02','03','04','08','09','50','52', '54', '57','74','77';

to iterate over the array you can use foreach:

foreach v_area in areay_array 
loop
   for Rec_Report in SELECT NAP_AREA2, NAP_PHONE_NUM, NAP_HEKER
                     FROM PS_RF_INST_PROD
                     WHERE NAP_AREA2 = V_Area
                     except 
                     SELECT AREA, PHONE, btrim(HEKER_NO)
                     FROM MANUIM
                     WHERE AREA = V_Area
   loop
   ....
   end loop; -- for rec_report

end loop;

PL/PGSQL create array type table

1 Answers