Issue Details (XML | Word | Printable)

Key: CORE-2312
Type: New Feature New Feature
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: Adriano dos Santos Fernandes
Votes: 1
Watchers: 1

If you were logged in you would be able to see more operations.
Firebird Core

PSQL packages

Created: 08/Feb/09 12:48 PM   Updated: 24/Aug/16 06:36 AM
Component/s: Engine
Affects Version/s: None
Fix Version/s: 3.0 Alpha 1

Issue Links:

QA Status: Covered by another test(s)

 Description  « Hide
    A package is a group of procedures and functions managed as one entity.

    <package_header> ::=
            [ <package_item> ... ]

    <package_item> ::=
        <function_decl> ; |
        <procedure_decl> ;

    <function_decl> ::=
        FUNCTION <name> [( <parameters> )] RETURNS <type>

    <procedure_decl> ::=
        PROCEDURE <name> [( <parameters> ) [RETURNS ( <parameters> )]]

    <package_body> ::=
            [ <package_item> ... ]
            [ <package_body_item> ... ]

    <package_body_item> ::=
        <function_impl> ; |
        <procedure_impl> ;

    <function_impl> ::=
        FUNCTION <name> [( <parameters> )] RETURNS <type>
            EXTERNAL NAME '<name>' ENGINE <engine>

    <procedure_impl> ::=
        PROCEDURE <name> [( <parameters> ) [RETURNS ( <parameters> )]]
        PROCEDURE <name> [( <parameters> ) [RETURNS ( <parameters> )]]
            EXTERNAL NAME '<name>' ENGINE <engine>

    <drop_package_header> ::=
        DROP PACKAGE <name>

    <drop_package_body> ::=
        DROP PACKAGE BODY <name>

    - Make functional dependent code separated in logical modules like programming languages does.

      It's well know in programming world that having code grouped in some way (for example in
      namespaces, units or classes) is a good thing. With standard procedures and functions in the
      database this is not possible. It's possible to group them in different scripts files, but
      two problems remains:
      1) The grouping is not represented in the database metadata.
      2) They all participate in a flat namespace and all routines are callable by everyone (not
         talking about security permissions here).
    - Facilitate dependency tracking between its internal routines and between others packaged and
      unpackaged routines.

      Firebird packages are divided in two pieces: a header (aka PACKAGE) and a body (aka
      PACKAGE BODY). This division is very similar to a Delphi unit. The header correspond to the
      interface part, and the body correspond to the implementation part.

      The user needs first to create the header (CREATE PACKAGE) and after it the body (CREATE

      When a packaged routine uses a determined database object, it's registered on Firebird system
      tables that the package body depends on that object. If you want to, for example, drop that
      object, you first need to remove who depends on it. As who depends on it is a package body,
      you can just drop it even if some other database object depends on this package. When the body
      is dropped, the header remains, allowing you to create its body again after change it based on
      the object removal.

    - Facilitate permission management.

      It's generally a good practice to create routines with a privileged database user and grant
      usage to them for users or roles. As Firebird runs the routines with the caller privileges,
      it's also necessary to grant resources usage to each routine, when these resources would not
      be directly accessible to the callers, and grant usage of each routine to users and/or roles.

      Packaged routines do not have individual privileges. The privileges act on the package.
      Privileges granted to packages are valid for all (including private) package body routines,
      but are stored for the package header. Example usage:
        GRANT SELECT ON TABLE secret TO PACKAGE pk_secret;
        GRANT EXECUTE ON PACKAGE pk_secret TO ROLE role_secret;

    - Introduce private scope to routines making them available only for internal usage in the
      defining package.

      All programming languages have the notion of routine scope. But without some form of grouping,
      this is not possible. Firebird packages also works as Delphi units in this regard. If a
      routine is not declared on the package header (interface) and is implemented in the body
      (implementation), it becomes a private routine. A private routine can only be called from
      inside its package.

Syntax rules:
    - A package body should implement all routines declared in the header and in the body start,
      with the same signature.

    - DROP PACKAGE drops the package body before drop its header.
    - UDFs (DECLARE EXTERNAL FUNCTION) are currently not supported inside packages.

    - To come.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
eXandr added a comment - 02/Nov/09 03:58 PM
Is it possible to declare package variables/constants(global, per connection, per transaction) ?
If yes, then it is also required:
1. initialization section/procedure in package on connection.
2. initialization section/procedure in package on transaction.
3. initialization section/procedure in package on start server(for consts);