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

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
    #export on
    DECLARE
        n int := 1; -- public package variable n
        k int := 3; -- public package variable k
    BEGIN
        FOR i IN 1..10 LOOP
            n := n + n;
        END LOOP;
    END;
    $$

    CREATE FUNCTION inc() RETURNS int AS $$ -- public package function inc()
    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 #export on modifier inside the package initialization function renders all the declared variables public, meaning they are available outside the package. The same result is achieved if the package does not contain the #export modifier (default). The inc() function is public by default and is available outside the package, as any other package function or procedure.

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 $$
  #export y
  DECLARE
    x int := 1; -- private package variable x
    y int := 5; -- public package variable y
  BEGIN
    RAISE NOTICE 'foo initialized';
  END;
  $$

  CREATE FUNCTION get_x() RETURNS int AS $$ -- public package function get_x()
  BEGIN
    RETURN x;
  END;
  $$

  CREATE PROCEDURE set_x(val int) AS $$ -- public package procedure set_x()
  BEGIN
    x := val;
    CALL foo.check_x();
  END;
  $$

  CREATE PROCEDURE check_x() AS $$ -- private package procedure check_x()
  #private
  BEGIN
    IF x <= 0 THEN
      RAISE INFO 'now x is not natural number';
    ELSE
      RAISE INFO 'now x is natural number';
    END IF;
  END;
  $$
;

In this example, the y variable, the get_x() function and the set_x(int) procedure of the foo package are public.

A variation of the previous example:

DO $$
#import foo
BEGIN
  RAISE INFO 'y = %', foo.y;
  RAISE INFO 'x = %', foo.get_x();
  CALL foo.set_x(25);
  RAISE INFO 'x = %', foo.get_x();
END;
$$;

INFO:  y = 5
INFO:  x = 1
INFO:  x set to natural value
INFO:  x = 25

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 $$
#export y
DECLARE
  x int := 1; -- private package variable x
  y int := 5; -- public package variable y
BEGIN
  RAISE NOTICE 'foo initialized';
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION foo.get_x() RETURNS int AS $$ -- public package function get_x()
#package
BEGIN
  RETURN x;
END;
$$ LANGUAGE plpgsql;

CREATE PROCEDURE foo.set_x(val int) AS $$ -- public package procedure set_x()
#package
BEGIN
  x := val;
  CALL foo.check_x();
END;
$$ LANGUAGE plpgsql;

CREATE PROCEDURE foo.check_x() AS $$ -- private package procedure check_x()
#package
#private
BEGIN
  IF x <= 0 THEN
    RAISE INFO 'x set to not natural value';
  ELSE
    RAISE INFO 'x set to natural value';
  END IF;
END;
$$ LANGUAGE plpgsql;

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

See Also