В Postgres Pro реализованы пакеты, подобные пакетам в Oracle. Они могут быть полезны при портировании с PL/SQL на PL/pgSQL. В этом разделе описаны различия между пакетами в Postgres Pro и Oracle.

В Oracle пакет — это объект, представляющий собой схему, которая группирует логически связанные типы, глобальные переменные и подпрограммы (процедуры и функции). Пакет состоит из спецификации и тела (хотя, в общем случае тело пакета может отсутствовать). В спецификации пакета объявляются элементы, на которые можно ссылаться снаружи пакета и использовать в приложениях: типы, переменные, константы, исключения, курсоры и подпрограммы. Тело пакета включает в себя реализацию подпрограмм пакета, объявление внутренних переменных, а также секцию инициализации. Переменные, типы и подпрограммы, объявленные в теле пакета, недоступны снаружи пакета.

Ниже приведён пример спецификации пакета counter на PL/SQL в Oracle, содержащего глобальную переменную n и функцию inc:

CREATE PACKAGE counter IS
    n int;

    FUNCTION inc RETURN int;
END;

В теле пакета counter в Oracle объявлены функция inc и глобальная переменная k (доступна только в теле пакета).

CREATE PACKAGE BODY counter IS
    k int := 3; -- переменная доступна только в теле пакета

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

-- Инициализация пакета
BEGIN
    n := 1;
    FOR i IN 1..10 LOOP
        n := n + n;
    END LOOP;
END;

Глобальные переменные пакета существуют в течение сеанса. Обратите внимание, что в теле пакета была определена секция инициализации — блок кода, который выполняется один раз за сеанс при первом обращении к любому элементу пакета. В Oracle к элементам пакета можно обращаться, используя запись с точкой: имя_пакета.элемент_пакета.

SET SERVEROUTPUT ON

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

1024
1025

В Postgres Pro пакет по сути представляет собой схему, которая содержит функцию инициализации и может содержать только функции, процедуры и составные типы. Функция инициализации — это функция на PL/pgSQL с именем __init__, которая не имеет аргументов и возвращает значение типа void. Функция инициализации должна быть определена до любой другой функции пакета. По умолчанию все переменные, объявленные в функции инициализации пакета, функциях пакета и процедурах пакета, являются публичными, поэтому к ним можно обращаться снаружи пакета, используя запись с точкой в других функциях, процедурах и анонимных блоках, импортирующих пакет. Модификатор #private определяет функции и процедуры как внутренние, а модификатор #export определяет, какие переменные пакета являются публичными. Например, на переменную bar, объявленную в функции __init__ пакета foo, можно ссылаться как foo.bar.

Переменные пакета доступны только из кода на языке PL/pgSQL. Для получения значений глобальных переменных пакета из SQL-запроса (SELECT) или DML-оператора (INSERT, UPDATE, DELETE), необходимо создать функцию-геттер, возвращающую значение глобальной переменной. Для определения переменных пакета, объявленных как константы, используется стандартный для PL/pgSQL синтаксис. Функция инициализации пакета вызывается автоматически при первом обращении к любому из следующих элементов в текущем сеансе:

Функцию инициализации можно вызвать вручную, чтобы сбросить значения переменных пакета. Для этого также можно использовать встроенную функцию plpgsql_reset_packages(), но она сбрасывает значения глобальных переменных всех пакетов в текущем сеансе (аналог DBMS_SESSION.RESET_PACKAGES в Oracle).

Описанный выше пример пакета counter в Oracle после портирования в Postgres Pro примет следующий вид:

CREATE PACKAGE counter

    CREATE FUNCTION __init__() RETURNS void AS $$ -- инициализация пакета
    #export n
    DECLARE
        n int := 1; -- публичная переменная n, доступная и внутри, и снаружи пакета
        k int := 3; -- внутренняя переменная k, доступная только внутри пакета
    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;
    $$
;

Использовать этот пакет в Postgres Pro можно так:

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

NOTICE:  1024
NOTICE:  1025

Стоит отметить следующее.

Для поддержки пакетов Postgres Pro включает следующие усовершенствования:

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 имя_переменной_1, имя_переменной_2, ... имя_переменной_N

SELECT plpgsql_reset_packages();

CREATE [OR REPLACE] PACKAGE имя_пакета тело_пакета;

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] список_имён_пакетов [CASCADE];