Recompile

Examples

CREATE OR REPLACE FUNCTION func1 RETURN INT AS
BEGIN
RETURN 1;
END;
/
CREATE FUNCTION

CREATE OR REPLACE PACKAGE pkg IS
PROCEDURE proc1();
END pkg;
/
CREATE PACKAGE

CREATE OR REPLACE PACKAGE BODY pkg IS
PROCEDURE proc1() AS
BEGIN
RAISE INFO 'proc1';
END;
END pkg;
/
CREATE PACKAGE BODY

-- Run the advanced package of recompilation upon expiration.
CALL pkg_util.gs_compile_schema('public', false, 1);

-- Execution result.
INFO:  successful
gs_compile_schema
-------------------

(1 row)

-- Query status.
SELECT proname,valid FROM pg_object obj JOIN pg_proc proc ON obj.object_oid = proc.oid AND proname = 'func1' AND propackageid = 0;
proname | valid
---------+-------
func1   | t
(1 row)
SELECT pkgname,object_type,valid FROM pg_object obj JOIN gs_package pkg ON obj.object_oid = pkg.oid AND pkgname = 'pkg';
pkgname | object_type | valid
---------+-------------+-------
pkg     | S           | t
pkg     | B           | t
(2 rows)

-- Execute the function.
SELECT func1();
 func1 
-------
     1
(1 row)
CALL pkg.proc1();
INFO:  proc1
 proc1 
-------

(1 row)

-- Drop a created object.
DROP FUNCTION func1;
DROP FUNCTION

DROP PACKAGE pkg;
NOTICE:  drop cascades to function public.proc1()
DROP PACKAGE