PL/pgSQL can be used to define functions for working with packages. A package is essentially a schema that helps to organize the named objects with a related purpose so it can be created using CREATE SCHEMA or a special CREATE PACKAGE command. A package should contain only functions, procedures and composite types.

When you create a package, you must also create the initialization function. The initialization function is a PL/pgSQL function named __init__ that has no arguments and returns void. Note that the initialization function must be defined before any other package function, and all the variables declared in the initialization function are global so they can be used by functions of other packages using dot notation. For example, the bar variable declared in the __init__ function of the foo package can be referenced as foo.bar.

Postgres Pro provides function creation modifiers for working with packages. The #package modifier defines that it is a package function and it can use variables of this package directly. The #import modifier defines that the function can use variables of other packages using the dot notation as described above. The #package modifier is omitted when creating functions inside a package using the CREATE PACKAGE command. The #private modifier indicates that the function is private, meaning that it cannot be referenced from outside the package, but it is necessary for the internal processes of the package. The modifier #export indicates that the package variable is public, meaning it can be referenced from outside the package. When a function with the #package modifier is called, the containing package is initialized if it has not been initialized already in the current session. You can create a function with the #package modifier only in the schema containing the initialization function. If the #import modifier is specified, the function will have access to the variables of the packages specified in a comma-separated list. These packages are initialized automatically if they have not been initialized already in the current session. In packages created with CREATE PACKAGE, #import specified for the initialization function affects all package functions, while in CREATE SCHEMA it must be defined separately for __init__ and other package functions. The #import modifier can also be used in anonymous code blocks.

In the below example of using the #import modifier, the showValues procedure calls p of the htp package and set_action of the dbms_application_info package.

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;

PL/pgSQL also provides a built-in function plpgsql_reset_packages() that deinstantiates all the packages in this session to bring the session to the original state.

SELECT plpgsql_reset_packages();