Help Center/ GaussDB/ Developer Guide(Centralized_V2.0-8.x)/ Stored Procedure/ Recompilation upon Expiration/ Re-creating the Package Header Without Deleting the Package Body
Updated on 2025-05-29 GMT+08:00
View PDF
Share

Re-creating the Package Header Without Deleting the Package Body

The package body definition is not deleted when the package header is re-created.

Examples

-- Create a package header and body.
CREATE OR REPLACE PACKAGE pkg_1 AS
var1 int;
var2 int;
PROCEDURE proc1;
END pkg_1;
/
CREATE PACKAGE

CREATE OR REPLACE PACKAGE BODY pkg_1 AS
PROCEDURE proc1 AS
BEGIN
var1 := var1 + var2;
END;
END pkg_1;
/
CREATE PACKAGE BODY

-- Re-create the package header.
CREATE OR REPLACE PACKAGE pkg_1 AS
var1 int;
var2 int;
var3 int;
PROCEDURE proc1;
END pkg_1;
/
CREATE PACKAGE

-- Query the status and verify that the package body definition is not deleted.
SELECT * FROM gs_package WHERE pkgname='pkg_1';
 pkgnamespace | pkgowner | pkgname |     pkgspecsrc     |    pkgbodydeclsrc    | pkgbodyinitsrc | pkgacl | pkgsecdef
--------------+----------+---------+--------------------+----------------------+----------------+--------+-----------
         2200 |       10 | pkg_1   |  PACKAGE  DECLARE +|  PACKAGE  DECLARE   +|                |        | f
              |          |         | var1 int;         +| procedure proc1 as  +|                |        |
              |          |         | var2 int;         +| begin               +|                |        |
              |          |         | var3 int;         +| var1 := var1 + var2;+|                |        |
              |          |         | procedure proc1;  +| end;                +|                |        |
              |          |         | end                | end                  |                |        |
(1 row)

-- The package body object is set to invalid.
SELECT * FROM pg_object WHERE object_oid = (SELECT oid FROM gs_package WHERE pkgname='pkg_1');
 object_oid | object_type | creator |             ctime             |             mtime             | createcsn | changecsn | valid
------------+-------------+---------+-------------------------------+-------------------------------+-----------+-----------+-------
     171138 | B           |      10 | 2024-12-31 16:50:29.923577+08 | 2025-01-17 15:56:42.271533+08 |           |    183936 | f
     171138 | S           |      10 | 2024-12-24 16:09:29.527485+08 | 2025-01-17 15:56:42.271533+08 |           |    183936 | t
(2 rows)

-- Drop a created object.
DROP PACKAGE pkg_1;
NOTICE:  drop cascades to function public.proc1()
DROP PACKAGE

-- The definitions of the package header and body are deleted.
SELECT * FROM gs_package WHERE pkgname='pkg_1';
 pkgnamespace | pkgowner | pkgname | pkgspecsrc | pkgbodydeclsrc | pkgbodyinitsrc | pkgacl | pkgsecdef
--------------+----------+---------+------------+----------------+----------------+--------+-----------
(0 rows)

SELECT * FROM pg_object WHERE object_oid = (SELECT oid FROM gs_package WHERE pkgname='pkg_1');
 object_oid | object_type | creator | ctime | mtime | createcsn | changecsn | valid
------------+-------------+---------+-------+-------+-----------+-----------+-------
(0 rows)