Error while converting a procedure referring to a table of collections from Oracle to Postgresql

Question

I am on Oracle 11.2.0.4 and need to convert the database procedures to Postgresql 9.6. I have Amazon SCT tool but it gives error on a few specific cases. The one tha t I am working on is about a pl/sql procedure that has input type as a collection of object type. The code is as follows and it gives error while converting. Any suggestion on how I can go about it, I will be very thankful. The error while conversion is due to this line

select count(*) INTO v_cnt from TABLE(t_cust_tab_type_i); <- it seems that postgresql does not have a nested type or a collection of collections perhaps so this error may come - not sure I am just starting on postgresql and i dont have a lot of knowledge.

:

    create or replace type temp_n_cust_header_type
is 
object(ssn    number,
       fname  varchar2(20),
       lname  varchar2(20),
       items  varchar2(100));
/

pause ;


create or REPLACE type temp_n_customer_tab_type is table of temp_n_cust_header_type;
/
pause;

CREATE OR REPLACE PROCEDURE temp_n_ins_cust_proc (
   p_cust_tab_type_i   IN temp_n_customer_tab_type)
IS

t_cust_tab_type_i   temp_n_customer_tab_type;
v_cnt number;

BEGIN

t_cust_tab_type_i := temp_n_customer_tab_type();

select count(*) INTO v_cnt from TABLE(t_cust_tab_type_i);

   DBMS_OUTPUT.put_line (
         'there are '
     ||v_cnt    
      /* || p_cust_tab_type_i.COUNT */
      || ' elements in the collection');

   FOR i IN 1 .. p_cust_tab_type_i.COUNT
   LOOP
      DBMS_OUTPUT.put_line (
         'ssn(' || i || ') = ' || p_cust_tab_type_i (i).ssn);
   END LOOP;
END;
/

Thanks, Nirav

here is what I have in postgresql:

CREATE TYPE temp_n_cust_header_type AS (
ssn DOUBLE PRECISION,
fname CHARACTER VARYING(20),
lname CHARACTER VARYING(20),
items CHARACTER VARYING(100)
);


CREATE TYPE temp_n_customer_tab_type AS (
col1 temp_n_cust_header_type[]
);

  CREATE OR REPLACE FUNCTION temp_n_ins_cust_proc(IN p_cust_tab_type_i temp_n_customer_tab_type)
RETURNS void
AS
$BODY$
DECLARE
    t_cust_tab_type_i temp_n_customer_tab_type;
    v_cnt DOUBLE PRECISION;
BEGIN
    t_cust_tab_type_i := ARRAY[]
    /*
    [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
    select count(*) INTO v_cnt from TABLE(t_cust_tab_type_i)
    */;
    RAISE DEBUG USING MESSAGE := CONCAT_WS('', 'there are ', v_cnt
    /* || p_cust_tab_type_i.COUNT */, ' elements in the collection');

    FOR i IN 1..array_length(p_cust_tab_type_i, 1) LOOP
        RAISE DEBUG USING MESSAGE := CONCAT_WS('', 'ssn(', i, ') = ', p_cust_tab_type_i[i].ssn);
    END LOOP;
END;
$BODY$
LANGUAGE  plpgsql;

Answer 1

You can't use an array like a table. To get the length of the passed array, use cardinality or array_length().

Additionally: temp_n_customer_tab_type is a type with a single attribute which is an array. The attribute col1 is an array, not the whole "thing". You would need to use cardinality(t_cust_tab_type_i.col1), not cardinality(t_cust_tab_type_i).

The intermediate type temp_n_customer_tab_type is not needed, you can pass an array of a type directly to a function.

You are also checking the length of the local variable t_cust_tab_type_i which is initialized to an empty array - so the result (if that was valid) would always be zero to begin with. I am not sure what you are trying to do there.

It's also better to use the format() function to put variables into strings (instead of concat() or concat_ws(). It makes the code more readable.

So putting that all together you wind up with something like his.

CREATE TYPE temp_n_cust_header_type AS (
  ssn integer,
  fname CHARACTER VARYING(20),
  lname CHARACTER VARYING(20),
  items CHARACTER VARYING(100)
);

CREATE OR REPLACE FUNCTION temp_n_ins_cust_proc(IN p_cust_tab_type_i temp_n_cust_header_type[])
RETURNS void
AS
$BODY$
DECLARE
    t_cust_tab_type_i temp_n_cust_header_type[];
    v_cnt integer; -- no need for a "double" to hold a count
BEGIN
  t_cust_tab_type_i := ARRAY[];
  -- this will always be zero
  v_cnt := cardinality(t_cust_tab_type_i);

  RAISE DEBUG USING MESSAGE := format('there are %s elements in the collection', v_cnt);

  FOR i IN 1..cardinality(p_cust_tab_type_i) LOOP
    RAISE DEBUG USING MESSAGE := format('ssn(%s)=%s', i, p_cust_tab_type_i[i].ssn);
  END LOOP;
END;
$BODY$
LANGUAGE  plpgsql;

You should also avoid the double precision data type - especially if you don't need decimals anyway. Use integer or bigint for numbers without decimals. and numeric for numbers that should have fractional digits.

---

I am not recommending the following, but a complicated and slow way to simulate the select .. from table() thing would be this:

 select count(*)
    into v_cnt
 from unnest(t_cust_tab_type_i);

But that is needlessly complicated and slow. Using cardinality() is much better.

---

Your original initialization t_cust_tab_type_i := ARRAY[]; was also incorrect as the declared type of the variable is not an array, but a custom type with a single attribute. That should be something like: t_cust_tab_type_i := row('{}'::temp_n_cust_header_type[]);

The correct way to initialize a record type with a single attribute that is an array