CREATE PACKAGE

CREATE PACKAGE
Prev	Up	SQL Commands	Home	Next

CREATE PACKAGE — define a new package

Synopsis

CREATE [ OR REPLACE ] PACKAGE package_name package_element [ ... ]

Description

CREATE PACKAGE enters a new package into the current database.

A package is essentially a schema that helps to organize the named objects with a related purpose so it can be also created using CREATE SCHEMA and is subject to the same actions as a schema. A package should contain only functions, procedures and composite types.

CREATE OR REPLACE PACKAGE will either create a new package, or replace an existing definition. You can replace the existing package if it contains only functions and types, other objects must be previously dropped. If you replace the package without changing the function signature, its body is replaced, while dependent objects still remain. If the function signature changes, you would actually be creating a new, distinct function. The latter would only work when there are no dependent objects, otherwise the CREATE OR REPLACE PACKAGE is forced to fail. The same restriction applies to replacing types. When replacing packages, make sure that no other sessions use them (which can be done by stopping the application before replacing the packages).

Parameters

package_name: The name of a package to be created. The package name must be distinct from the name of any existing package or schema in the current database. The name cannot begin with pg_, as such names are reserved for system schemas.
package_element: An SQL statement defining an object to be created within the package. Currently, only CREATE FUNCTION, CREATE TYPE, and CREATE PROCEDURE are accepted as clauses within CREATE PACKAGE. The subcommands are treated essentially the same as separate commands issued after creating the package. Note that all the variables declared in the package initialization function are global so they can be used by functions of other packages using dot notation. The #import modifier specified for the initialization function affects all package functions. For more information on package functions and modifiers, see Section 44.11.

Notes

To create a package, the invoking user must be a superuser or have the CREATE privilege for the current database.

Examples

Create a package named counter and create functions within it:

CREATE PACKAGE counter
    CREATE FUNCTION __init__() RETURNS void AS $$ -- package initialization
    DECLARE
        n int := 1;
        k int := 3;
    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;
    $$
;

Notice that the individual subcommands do not end with semicolons, just as in CREATE SCHEMA, and no language is specified when creating functions.

The example below shows how the above-described package can be used.

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

NOTICE:  1024
NOTICE:  1025

A variation of the previous example:

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;
  $$
;

The following is an equivalent way of achieving the same result using the CREATE SCHEMA command:

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;

Note that in this case you must use #package modifiers.