Migrating Oracle PL/SQL code to PostgreSQL PL/pgSQL requires a deeper understanding of the behavior in both Oracle and PostgreSQL databases. It is not only the syntax but also the behavior that is very important. Else, upon porting the PL/SQL code to PL/pgSQL, we may encounter some strange application behaviors that are difficult to trace. We would like to discuss one of such common mistakes while porting arrays indexed by integers from Oracle PL/SQL to PostgreSQL PL/pgSQL.
Let us consider a very simple use case in Oracle, as follows. In this example, we are declaring a collection or an associative array, that is indexed by an integer.
In Oracle
CREATE OR REPLACE FUNCTION test_func(a int, b int) RETURN INTEGER
IS
TYPE array_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
v_test_array array_type;
BEGIN
v_test_array(a) :=1001;
v_test_array(b) :=2001;
RETURN v_test_array.count;
END;
In the above function, we defined the test_func function with an array, which has 2 parameter variables as `a`, `b`. By using these 2 variable values as an index to the array, we are saving some random values and returning the array length to the function called in the environment.
Now, let us execute and see it's behavior in oracle.
SQL> SELECT test_func(1, 2) FROM dual;
TEST_FUNC
----------
2
As expected, we got the number of values of the array (or array length) as 2.
Now, let us convert this code into PostgreSQL, which is straightforward.
In PostgreSQL
CREATE OR REPLACE FUNCTION public.test_func(a int, b int) RETURNS INT
AS
$
DECLARE
v_test_array INT[];
BEGIN
v_test_array[a]:= 1001;
v_test_array[b]:= 2001;
RETURN array_length(v_test_array, 1);
END;
$
LANGUAGE PLPGSQL;
In the above code block, we see the converted PostgreSQL array object, which is indexed by an integer.
Now, let us call the function using same parameters as Oracle and see the results in Postgres.
postgres=# SELECT public.test_func(1, 2);
test_func
-----------
2
(1 row)
As expected, we got the array length as 2. Now, let us use some random parameter values and compare the behavior in Oracle and PostgreSQL. These random parameter values will be used as index of the array variable.
Oracle
SQL> SELECT test_func(10, 20) FROM dual;
TEST_FUNC
----------
2
PostgreSQL
postgres=# SELECT public.test_func(10, 20); test_func ----------- 11 (1 row)
Wait ! Array length is 11 in PostgreSQL but not 2 as seen in Oracle ?
Before going much further, let us pass some epoch values as parameters to these functions and then compare the behavior in Oracle and PostgreSQL.
Oracle
SQL> SELECT test_func(1619901545, 1619911545) FROM dual;
TEST_FUNC(1619901545,1619911545)
--------------------------------
2
PostgreSQL
postgres=# SELECT test_func(1619901545, 1619911545); test_func ----------- 10001 (1 row)
As expected, we see a huge difference between Oracle and PostgreSQL.
Now, if we see both the Oracle and the converted PostgreSQL code, they look similar as it is a direct conversion. As we have arrays indexed by integers in both Oracle and Postgres, one may easily assume the behavior as same. But the main difference is that an Oracle array is an associative array whereas a PostgreSQL array is not an associative array. At a glance, converting this simple Oracle code into PostgreSQL may seem easy, but when comparing their behaviors, it is not that direct.
PostgreSQL arrays are 1-Based, but it provides much flexibility in accessing the array values. Unlike the native programming language arrays like C (where an array index should start with 0), PostgreSQL arrays can begin from any value, and can end at any value. For example, consider the below use case, which prints the given array dimensions.
postgres=# SELECT array_dims('{1001,2001}'::int[]);
array_dims
------------
[1:2]
(1 row)
From the above result, we see that PostgreSQL array values start at 1, and end at 2.
Let us change the above query with different start and end positions, and then see the dimensions.
postgres=# SELECT array_dims('[-2:-1]={1001,2001}'::int[]);
array_dims
------------
[-2:-1]
(1 row)
As you see, PostgreSQL provides much flexibility in having its own array dimensions. And, we can also access the array with a required index value.
postgres=# SELECT ('[-2:-1]={1001,2001}'::int[])[-1];
int4
------
2001
(1 row)
Now, let us create an anonymous block, and assign two values in two different indexes, and then print the whole array.
postgres=#DO
postgres-#$
postgres$#DECLARE
postgres$# v_test_array INT[];
postgres$#BEGIN
postgres$# v_test_array[10]:=1001;
postgres$# v_test_array[20]:=2001;
postgres$# RAISE NOTICE '%', v_test_array;
postgres$#END;
postgres$#$;
NOTICE: [10:20]={1001,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,2001}
DO
From the above output, we have a total 11 elements of which 9 are null and 2 and not null values. This is because, PostgreSQL arrays give flexibility in assigning the array values using any index position which is a dynamic array object allocation.
Now, let us print the total number of elements in the array using array_length, and also print it's dimensions.
postgres=# DO postgres-# $ postgres$# DECLARE postgres$# v_test_array INT[]; postgres$# BEGIN postgres$# v_test_array[10]:=1001; postgres$# v_test_array[20]:=2001; postgres$# RAISE NOTICE 'length %', array_length(v_test_array, 1); postgres$# RAISE NOTICE 'dim %', array_dims(v_test_array); postgres$# END; postgres$# $; NOTICE: length 11 NOTICE: dim [10:20] DO
As expected, we got the total number of elements as 11 because PostgreSQL `array_length` calculates the item count based on the `array_dim` function.
So, what is the solution for this type of behavior ?
Before talking about the solution to the problem, we must understand that migrating from one database to another database requires a strong behavioral knowledge about the PL/SQL objects that are being converted. By looking at this simple Oracle PL/SQL code, we may convert the code as per the syntax, but there might be chances that we miss the actual PL/SQL behavior.
Assume that such an object has been directly converted and integrated into the application. One can imagine how hard it will be to troubleshoot the issue that does not throw any sort of application errors. This is why, it is also wise to closely with an application engineer while converting the Oracle PL/SQL code, and make sure that the correct solution is delivered and migration failures can be avoided.
Now, coming to the solution to this problem, If the purpose of an Oracle array is to be indexed by an integer, then, we must understand the kind of data that is being stored in the array. If it is like a normal array index (which starts at 1, and ends at definite value), then we can go with the PostgreSQL arrays.
If it is not like a normal array, where the index of an array can be like an epoch value or another random application ID, then we should not choose the PostgreSQL arrays. In such a case, we must rather choose PostgreSQL temporary tables as demonstrated below.
postgres=# DO postgres-# $ postgres$# DECLARE postgres$# v_length INT; postgres$# BEGIN postgres$# CREATE TEMP TABLE v_test_array(key int, value int); postgres$# INSERT INTO v_test_array(key, value) VALUES(10, 1001); postgres$# INSERT INTO v_test_array(key, value) VALUES(20, 2001); postgres$# SELECT COUNT(*) INTO v_length FROM v_test_array; postgres$# RAISE NOTICE 'length %', v_length; postgres$# END; postgres$# $; NOTICE: length 2 DO
Otherwise, we can leverage other PostgreSQL procedural languages like PL/TCL as discussed in this blog post about Handling Global Associative Arrays in PostgreSQL.
Please Contact Us to know more about the robust Open Source solutions available for PostgreSQL and how we can help you migrate from Oracle and other commercial databases to PostgreSQL. Ask us about our fastest data migration tool that has been developed in-house for the purpose of data migrations from Oracle to PostgreSQL.
