Help Center/ GaussDB/ Centralized_8.x/ SQL Reference/ SQL Syntax/ A/ ALTER PACKAGE

Updated on 2024-06-03 GMT+08:00

View PDF

ALTER PACKAGE

Description

Modifies the attributes of a package or recompiles a package.

Precautions

Currently, only users with the ALTER PACKAGE OWNER permission can run this command. By default, system administrators have the permission. 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.

The plpgsql_dependency parameter must be set for package recompilation.
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.

Example

Change the owner of a package.

-- Create a package.
gaussdb=# CREATE OR REPLACE PACKAGE test_pkg AS
    pkg_var int := 1;
    PROCEDURE test_pkg_proc(var int);
END test_pkg;
/
gaussdb=# 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.
gaussdb=# CREATE ROLE test PASSWORD '********';

-- Change the owner of a package.
gaussdb=# ALTER PACKAGE test_pkg OWNER TO test;

-- Query the owner of test_pkg.
gaussdb=# SELECT t1.pkgname,t2.rolname FROM gs_package t1, pg_roles t2 WHERE t1.pkgname = 'test_pkg' AND t1.pkgowner = t2.oid;
 pkgname  | rolname 
----------+---------
 test_pkg | test
(1 row)

Recompile the package.

-- Enable the dependency function.
gaussdb=# SET behavior_compat_options ='plpgsql_dependency';
-- Recompile the package.
gaussdb=# ALTER PACKAGE test_pkg COMPILE;

-- Delete.
gaussdb=# DROP PACKAGE test_pkg;
gaussdb=# DROP ROLE test;
-- Disable the dependent function.
gaussdb=# RESET behavior_compat_options;