Recompiling Feature Functions upon Failure

Description: Sets the status of the package and function in pg_object to false, and sets the status of objects that depend on the package and function to false. The user who calls this function must have the SYSADMIN permission.

Parameters: This function is an overloaded function. If there is no input parameter, the package and function objects in the current database are invalidated. When schema, objname, and objtype are specified, the status of the package and function objects in the current database in pg_object is set to false. schema indicates the name of the schema to which the object belongs. objname indicates the object name. objtype indicates the object type. If the object type is a package, the value is package. If the object type is a function or stored procedure, the value is function.

• This function can be used to invalidate packages, functions, and objects that depend on the packages and functions.
• Only the system administrator can call the functions.

Example:

This function does not return the invalidation result. You can query the invalidation result in the pg_object system catalog. If the object is valid, the corresponding valid status is true. If the object is invalid, the corresponding valid status is false.

gs_set_plsql_invalid belongs to the pg_catalog schema. It can be called even if no schema is specified.

-- Create packages pkg1 and pkg2 and function f3. Query the pg_object information. The status is valid.
SET enable_force_create_obj=on;
SET
SET ddl_invalid_mode=invalid;
SET
CREATE SCHEMA testInvalidate;
CREATE SCHEMA
SET CURRENT_SCHEMA TO testInvalidate;
SET
CREATE OR REPLACE PACKAGE pkg1 AS
var1 VARCHAR;
FUNCTION f1() RETURN VARCHAR;
END pkg1;
/
CREATE PACKAGE
CREATE OR REPLACE PACKAGE BODY pkg1 AS
FUNCTION f1() RETURN VARCHAR
IS
BEGIN
var1 := '2';
RETURN var1;
END;
END pkg1;
/
CREATE PACKAGE BODY
CREATE OR REPLACE PACKAGE pkg2 AS
var1 pkg1.var1%TYPE;
FUNCTION f2() RETURN VARCHAR;
END pkg2;
/
CREATE PACKAGE
CREATE OR REPLACE PACKAGE BODY pkg2 AS
FUNCTION f2() RETURN VARCHAR
IS
BEGIN
var1 := '2';
RETURN var1;
END;
END pkg2;
/
CREATE PACKAGE BODY
CREATE OR REPLACE FUNCTION f3() RETURN VARCHAR
IS
DECLARE
var2 VARCHAR := 'li2';
BEGIN
var2='22';
RETURN var2;
END
/
CREATE FUNCTION
SELECT pkg1.f1();
 f1 
----
 2
(1 row)
SELECT pkg2.f2();
 f2 
----
 2
(1 row)
SELECT f3();
 f3 
----
 22
(1 row)

-- Call the function and specify the schema, function name, and type to invalidate pkg1 and pkg2. Query pg_object again. The value of valid corresponding to pkg1 and pkg2 is false.
SELECT gs_set_object_invalid('testinvalidate','pkg1','package');
 set_object_invalid 
-------------------------
(1 row)
-- To invalidate the function, set the following parameters. Query pg_object. The value of valid corresponding to f3 is false.
SELECT gs_set_object_invalid('testinvalidate','f3','function');
 set_object_invalid 
-------------------------
(1 row)
-- If there is no input parameter, all cache objects will be invalidated.
SELECT gs_set_object_invalid();
 set_object_invalid 
-------------------------
(1 row)