PL/SQL specificities

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 size the max_connections parameter and will consume additional memory and CPU time.

The following Oracle PL/SQL code shows the declaration of a procedure with AUTONOMOUS_TRANSACTION pragma:

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 the pg_background_launch method of the extension.

Another rewrite of the previous code would look like this with pg_background, 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;

The log_action method behaves exactly as if it were executing a statement in a autonomous transaction.

References:

Autonomous transaction support in PostgreSQL

VARRAY collections #

VARRAY collections in Oracle packages are migrated to PostgreSQL arrays. They usually need a rewrite of the code using them.

When a 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[366]);

The next one, 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 TABLE OF collection is not necessary.

Thus, the following declaration:

CREATE TYPE information IS TABLE OF VARCHAR2(255);

will be converted to an array of varchar(255):

CREATE TYPE information AS VARCHAR(255)[];

The NUMBER INDEX clause has no equivalent, though. For instance, the following declaration:

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.

References:

hstore