PL/SQL specificities #
Autonomous transactions #
We often find
PRAGMA associated with autonomous transactions in PL/SQL. This
last notion does not exist in PostgreSQL.
It is possible to emulate autonomous transactions through a dblink, but it is
a particularly counter-efficient solution that consumes resources. Indeed, the
use of a dblink will cause a new database connection. The cost of a new
connection is significant and a additional connection will require to properly
max_connections parameter and will consume additional memory and CPU
The following Oracle PL/SQL code shows the declaration of a procedure with
CREATE PROCEDURE LOG_ACTION (username VARCHAR2, msg VARCHAR2) IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO table_tracking VALUES (username, msg); COMMIT; END log_action;
A possible rewrite with a dblink involves filling in the information to connect to the local PostgreSQL instance:
CREATE EXTENSION dblink; CREATE OR REPLACE FUNCTION log_action(username TEXT, msg TEXT) RETURNS void AS $$ BEGIN perform dblink_connect('pragma', format( 'dbname=%s user=test password=test', current_database() )); perform dblink_exec('pragma', format( 'insert into table_tracking values (%s, %s);', username, msg )); perform dblink_exec('pragma','commit;'); perform dblink_disconnect('pragma'); END; $$ LANGUAGE plpgsql;
Another alternative, which appeared in version 9.5 with background workers, is
to rely on the extension
pg_background to deport the
execution of a procedure to a new transaction using of a wrapper procedure and
pg_background_launch method of the extension.
Another rewrite of the previous code would look like this with
with the creation of a calling procedure:
-- Create the function we will be "remotely" calling: CREATE OR REPLACE FUNCTION log_action_atx(username TEXT, msg TEXT) RETURNS void AS $$ BEGIN INSERT INTO table_tracking VALUES (username, msg); END; $$ LANGUAGE plpgsql; -- Now we can write the calling function CREATE OR REPLACE FUNCTION log_action(username TEXT, msg TEXT) RETURNS void AS $$ DECLARE v_query text; BEGIN v_query := format( 'SELECT true FROM log_action_atx (%s, %s)', quote_nullable(username), quote_nullable(msg) ); PERFORM pg_background_result( pg_background_launch(v_query) ); END; $body$ LANGUAGE plpgsql SECURITY DEFINER;
log_action method behaves exactly as if it were executing a
statement in a autonomous transaction.
VARRAY collections #
VARRAY collections in Oracle packages are migrated to PostgreSQL arrays.
Their definition is created by Ora2Pg, but they usually need a rewrite of the
code using them.
VARRAY is a simple array of a scalar datatype, there is less rewriting
work to be done than when dealing with a
%ROWTYPE VARRAY. In this case, there
is quite a lot of work.
The following code:
DECLARE TYPE Calendar IS VARRAY(366) OF DATE;
will be converted to:
CREATE TYPE calendar AS (date);
The next one, though converted by Ora2Pg, won’t work without modification:
TYPE t_tab_emp IS VARRAY (1000) OF emp%ROWTYPE; ... tab_emp t_tab_emp;
Associative arrays and nested tables #
TABLE OF collections are usually used to declare functions returning a set of
records. As a consequence, the
TABLE OF types can be replaced by a
RETURNS TABLE or a
RETURNS SETOF data_type for simple data types. Please refer to
PIPELINED attribute and PIPE ROW instruction for an example where a
collection is not necessary.
Anyway, Ora2Pg translates this data type to an array of the associated type, and will probably need some rework of the translated code.
Thus, the following declaration:
CREATE TYPE information IS TABLE OF VARCHAR2(255);
will be converted to an array of
varchar(255) by Ora2Pg:
CREATE TYPE information AS VARCHAR(255);
NUMBER INDEX clause has no equivalent, though. For instance, the following
TYPE t_list_qlf_id IS TABLE OF NUMBER INDEX BY VARCHAR2(5);
cannot be directly converted. One can use the hstore contrib
module, or the
JSONB datatype, to emulate this feature.