Updated on 2025-05-29 GMT+08:00

ALTER PACKAGE

Description

Changes the attributes of a package or recompiles a package.

Precautions

  • Currently, only users with the ALTER PACKAGE OWNER permission can run this command. The system administrator has this permission by default. The restrictions are as follows:
    • The current user must be the owner of the package or the system administrator and a member of the new owner role.
  • When separation of duties is enabled, to change the owner of a package, users must have the user group permission, even a system administrator. The owner of a package of the DEFINER type cannot be changed.
  • Only the initial user can change the owner of a package to the initial user.
  • When separation of duties is disabled, only a system administrator or a higher-level user can change the package owner, but the owner cannot be changed to an O&M administrator.
  • The system administrator is not allowed to change the owner of a package of the DEFINER type to the initial user or O&M administrator.

Syntax

  • Change the owner of a package.
    ALTER PACKAGE package_name OWNER TO new_owner;

  • Recompile the package.
    ALTER PACKAGE package_name COMPILE [PACKAGE | BODY | SPECIFICATION];

Parameters

  • package_name

    Specifies the name of the package to be modified.

    Value range: an existing package name. Only one package can be modified at a time.

  • new_owner

    Specifies the new owner of a package. To change the owner of a package, the new owner must have the CREATE permission on the schema to which the package belongs.

    Value range: an existing user role.

Examples

  • Change the owner of a package.
    -- Create a database.
    gaussdb=# CREATE DATABASE test DBCOMPATIBILITY 'ORA';
    gaussdb=# \c test
    
    -- Create a package.
    test=# CREATE OR REPLACE PACKAGE test_pkg AS
        pkg_var int := 1;
        PROCEDURE test_pkg_proc(var int);
    END test_pkg;
    /
    
    test=# CREATE OR REPLACE PACKAGE BODY test_pkg AS
        PROCEDURE test_pkg_proc(var int) AS
        BEGIN
            pkg_var := 1;
        END;
    END test_pkg;
    /
    
    -- Create a user.
    test=# CREATE ROLE test PASSWORD '********';
    
    -- Change the owner of a package.
    test=# ALTER PACKAGE test_pkg OWNER TO test;
    
    -- Query the owner of test_pkg.
    test=# SELECT t1.pkgname,t2.rolname FROM gs_package t1, gs_roles t2 WHERE t1.pkgname = 'test_pkg' AND t1.pkgowner = t2.oid;
     pkgname  | rolname 
    ----------+---------
     test_pkg | test
    (1 row)
    
    -- Delete.
    test=# DROP PACKAGE test_pkg;
    test=# DROP ROLE test;
    -- Switch to the default database. Change the database name based on the actual situation.
    test=# \c postgres
    gaussdb=# DROP DATABASE test;

Helpful Links

CREATE PACKAGE and DROP PACKAGE