Postgres Pro provides packages, which are similar to packages in Oracle, and can be useful when porting from PL/SQL to PL/pgSQL. This section explains differences between Postgres Pro's and Oracle's packages.

In Oracle, a package is a schema object that groups logically related types, global variables, and subprograms (procedures and functions). A package consists of a package specification and a package body (in general, the package body is optional). In the package specification, those items are declared that can be referenced from outside the package and used in applications: types, variables, constants, exceptions, cursors, and subprograms. The package body contains package subprogram implementation, private variable declarations, and the initialization section. The variables, types and subprograms declared in the package body cannot be used from outside the package.

The example below shows the package specification of the PL/SQL counter package in Oracle, which contains the global variable n and the function inc:

CREATE PACKAGE counter IS
    n int;

    FUNCTION inc RETURN int;
END;

The function inc and the global variable k (available only in the package body) are declared in the counter package body in Oracle.

CREATE PACKAGE BODY counter IS
    k int := 3; -- the variable is available only in the package body

    FUNCTION inc RETURN int IS
    BEGIN
        n := n + 1;
        RETURN n;
    END;

-- Package initialization
BEGIN
    n := 1;
    FOR i IN 1..10 LOOP
        n := n + n;
    END LOOP;
END;

Global package variables exist during the session lifetime. Note that the package body contains the initialization section — the code block executed once a session when any package element is accessed for the first time. In Oracle, package elements could be accessed using dot notation as follows: package_name.package_element.

SET SERVEROUTPUT ON

BEGIN
    dbms_output.put_line(counter.n);
    dbms_output.put_line(counter.inc());
END;
/

1024
1025

A package in Postgres Pro is essentially a schema that contains the initialization function and may contain only functions, procedures and composite types. The initialization function is a PL/pgSQL function named __init__ that has no arguments and returns void. The initialization function must be defined before any other package function. By default, all the variables declared in the package initialization function, package functions and package procedures are public, so they can be called using dot notation from outside the package by other functions, procedures and anonymous blocks that import the package. The #private modifier defines functions and procedures as private, and the #export modifier defines which package variables are public. For example, the bar variable declared in the __init__ function of the foo package can be referenced as foo.bar.

Package variables can be accessed only from PL/pgSQL code. To retrieve the value of the global variable in an SQL query (SELECT) or in a DML operation (INSERT, UPDATE, DELETE), add a getter function that returns the value to the caller. For package variables declared as constant, default definition syntax in PL/pgSQL is used. The package initialization function is invoked automatically when any of the following elements is accessed in the current session:

The initialization function can be invoked manually to reset values of package variables. To do that, you can also use a built-in function plpgsql_reset_packages() but it resets all the packages in the current session (similar to DBMS_SESSION.RESET_PACKAGES in Oracle).

The above example of the Oracle's counter package after porting to Postgres Pro will look like this:

CREATE PACKAGE counter

    CREATE FUNCTION __init__() RETURNS void AS $$ -- package initialization
    #export n
    DECLARE
        n int := 1; -- n public variable, available both inside and outside the package
        k int := 3; -- k private variable, only available inside the package
    BEGIN
        FOR i IN 1..10 LOOP
            n := n + n;
        END LOOP;
    END;
    $$

    CREATE FUNCTION inc() RETURNS int AS $$
    BEGIN
        n := n + 1;
        RETURN n;
    END;
    $$
;

You can use this package in Postgres Pro as follows:

DO $$
#import counter
BEGIN
    RAISE NOTICE '%', counter.n;
    RAISE NOTICE '%', counter.inc();
END;
$$;

NOTICE:  1024
NOTICE:  1025

There are some points worth noting.

To support packages in Postgres Pro, the following enhancements were introduced:

CREATE OR REPLACE PROCEDURE showValues(p_Str varchar) AS $$
#import htp, dbms_application_info
BEGIN
  CALL dbms_application_info.set_action('Show hello');

  CALL htp.p('<p>' || p_Str || '</p>');
END;
$$LANGUAGE plpgsql;

#export var_name_1, var_name_2, ... var_name_N

SELECT plpgsql_reset_packages();

CREATE [OR REPLACE] PACKAGE package_name package_body;

CREATE SCHEMA foo;
CREATE TYPE foo.footype AS (a int, b int);
CREATE FUNCTION foo.__init__() RETURNS void AS $$
DECLARE x int := 1;
BEGIN
  RAISE NOTICE 'foo initialized';
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION foo.get() RETURNS int AS $$
#package
BEGIN
  RETURN x;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION foo.inc() returns void AS $$
#package
BEGIN
  x := x + 1;
END;
$$ LANGUAGE plpgsql;

CREATE PACKAGE foo
  CREATE TYPE footype AS (a int, b int)

  CREATE FUNCTION __init__() RETURNS void AS $$
  DECLARE
    x int := 1;
  BEGIN
    RAISE NOTICE 'foo initialized';
  END;
  $$

  CREATE FUNCTION get() RETURNS int AS $$
  BEGIN
    RETURN x;
  END;
  $$

  CREATE FUNCTION inc() RETURNS void AS $$
  BEGIN
    x := x + 1;
  ENG;
  $$
;

DROP PACKAGE [IF EXISTS] package_name_list [CASCADE];