Ispirer Website
Ispirer Capabilities - MySQL Migration
Free Trial
PostgreSQL - BULK COLLECT conversion to PostgreSQL
A bulk collect is a method of fetching data when many rows are placed in a collection at once. This can be a problem when migrating procedures from databases that support this mechanism to others that do not. In this case, Oracle supports the bulk collection, but PostgreSQL does not. The Ispirer toolkit allows you to solve this problem and convert procedures that contain BULK COLLECT in several ways. Let's look at these methods using the examples of converting procedures from Oracle to PostgreSQL.
The Ispirer toolkit converts collection type variables either into a temporary tables or into an array. It depends on the option Table_type_conversion in the [POSTGRE] section of the ini file (General information on how to set options: https://wiki.ispirer.com/sqlways/faq/set-options-sqlways-wizard):
[Postgre]
TABLE_TYPE_CONVERSION
It can take the following values:
- TABLE_TYPE_CONVERSION=Tables
- TABLE_TYPE_CONVERSION=Arrays
The default value is Tables. It is used when option is missing. If it is set to Arrays, then variables of collection type are converted into arrays, otherwise into temporary tables. It seems that converting such variables into arrays is more convinient for further work with them. For more information please read the following article. https://wiki.ispirer.com/sqlways/postgresql/techniques/table-type-conv-using_array
In the case when a variable of the collection type becomes a temporary table, the bulk collect is converted into a simple insertion into the created table.
Oracle code:
CREATE TYPE type_bulk AS object (p1 integer, p2 varchar2(22)); CREATE TYPE type_bulk_tab AS TABLE OF type_bulk; CREATE FUNCTION func_for_bulk(par IN NUMBER) RETURN type_bulk_tab AS RESULTS type_bulk_tab; BEGIN SELECT type_bulk(p1,p2) BULK COLLECT INTO RESULTS FROM (SELECT c1 as p1, c2 as p2 from table_for_bulk); RETURN RESULTS; END;
After converting with our tool, we get the following PostgreSQL code:
CREATE FUNCTION FUNC_FOR_BULK (IN PAR DOUBLE PRECISION) RETURNS TABLE ( SWC_Index INTEGER, p1 INTEGER, p2 VARCHAR(22) ) LANGUAGE plpgsql AS $$ BEGIN create temporary table if not exists SWT_FUNC_FOR_BULK_RESULTS ( SWC_INDEX INTEGER NOT NULL, P1 INTEGER, P2 VARCHAR(22) ); DELETE FROM SWT_FUNC_FOR_BULK_RESULTS; INSERT INTO SWT_FUNC_FOR_BULK_RESULTS SELECT row_number() OVER(), TabAl.P1,TabAl.P2 from(SELECT C1 as P1,C2 as P2 from TABLE_FOR_BULK) AS TABAL; RETURN QUERY(SELECT SWT_FUNC_FOR_BULK_RESULTS.SWC_INDEX, SWT_FUNC_FOR_BULK_RESULTS.P1, SWT_FUNC_FOR_BULK_RESULTS.P2 FROM SWT_FUNC_FOR_BULK_RESULTS); END; $$;
If there are more than one variable for bulk collection, temporary tables are created for each of them.
Oracle code:
SELECT id, full_name BULK COLLECT INTO v_id, v_full_name FROM TABLE_FOR_BULK;
PostgreSQL code:
create temporary table if not exists SWT_PRC_BULK_COLLECT_V_FULL_NAME ( SWC_Index INTEGER NOT NULL, SWC_Value VARCHAR(30) ); DELETE FROM SWT_PRC_BULK_COLLECT_V_FULL_NAME; create temporary table if not exists SWT_PRC_BULK_COLLECT_V_ID ( SWC_Index INTEGER NOT NULL, SWC_Value DOUBLE PRECISION ); DELETE FROM SWT_PRC_BULK_COLLECT_V_ID; INSERT INTO SWT_PRC_BULK_COLLECT_V_ID SELECT row_number() OVER(), id FROM TABLE_FOR_BULK; INSERT INTO SWT_PRC_BULK_COLLECT_V_FULL_NAME SELECT row_number() OVER(), full_name FROM TABLE_FOR_BULK;
If a variable of varray_type type is converted into array due to the option Table_type_conversion=Arrays, instead of bulk collection, a new array is created and then assigned to the variable of an array type.
Oracle code:
CREATE FUNCTION func_with_varray() RETURN varray_type IS Arr varray_type := varray_type(); BEGIN SELECT name BULK COLLECT INTO Arr FROM TABLE_FOR_BULK; RETURN Arr; END;
PostgreSQL code:
CREATE OR REPLACE FUNCTION FUNC_WITH_VARRAY() RETURNS VARCHAR(60)[] LANGUAGE plpgsql AS $$ DECLARE ARR VARCHAR(60)[] default array[]::VARCHAR(60)[]; BEGIN ARR := ARRAY(SELECT NAME FROM TABLE_FOR_BULK); RETURN ARR; END; $$;
As you can see, the Ispirer toolkit in these cases can convert BULK COLLECT from Oracle into PostgreSQl correctly.
Related Topics
Official User's Guide: Ispirer Toolkit Official Guide
If you have any questions or face any difficulties, please contact our support team: support@ispirer.com