Dependency Record

Examples

-- Configure the following parameters:
SET ddl_invalid_mode='invalid';
SET
SET enable_force_create_obj=on;
SET

-- pkg2 depends on pkg1 by type.
CREATE OR REPLACE PACKAGE pkg1 IS
TYPE rec1 IS RECORD(id INT, name VARCHAR(2));
END pkg1;
/
CREATE PACKAGE

CREATE OR REPLACE PACKAGE pkg2 IS
TYPE rec2 IS RECORD(rec1 pkg1.rec1);
END pkg2;
/
CREATE PACKAGE
SELECT DISTINCT p.pkgname AS objname, q.pkgname AS refobjname FROM pg_depend AS dp
INNER JOIN gs_package AS p ON dp.objid=p.oid
INNER JOIN gs_package AS q ON dp.refobjid=q.oid
WHERE p.pkgname='pkg2';
 objname | refobjname 
---------+------------
 pkg2    | pkg1

-- pkg2 depends on pkg1 by variable.
CREATE OR REPLACE PACKAGE pkg1 IS
var1 INT;
END pkg1;
/
CREATE PACKAGE

CREATE OR REPLACE PACKAGE pkg2 IS
var2 INT := pkg1.var1;
END pkg2;
/
CREATE PACKAGE

SELECT DISTINCT p.pkgname AS objname, q.pkgname AS refobjname FROM pg_depend AS dp
INNER JOIN gs_package AS p ON dp.objid=p.oid
INNER JOIN gs_package AS q ON dp.refobjid=q.oid
WHERE p.pkgname='pkg2';
 objname | refobjname 
---------+------------
 pkg2    | pkg1
(1 row)

-- pkg2 depends on pkg1 by direct calling.
CREATE OR REPLACE PACKAGE pkg1 IS
PROCEDURE proc1();
END pkg1;
/
CREATE PACKAGE

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

CREATE OR REPLACE PACKAGE pkg2 IS
PROCEDURE proc2();
END pkg2;
/
CREATE PACKAGE

CREATE OR REPLACE PACKAGE BODY pkg2 IS
PROCEDURE proc2() AS
BEGIN
RAISE INFO 'proc2';
pkg1.proc1();
END;
END pkg2;
/
CREATE PACKAGE BODY
SELECT DISTINCT p.pkgname AS objname, q.pkgname AS refobjname FROM pg_depend AS dp
INNER JOIN gs_package AS p ON dp.objid=p.oid
INNER JOIN gs_package AS q ON dp.refobjid=q.oid
WHERE p.pkgname='pkg2';
 objname | refobjname 
---------+------------
 pkg2    | pkg1
(1 row)

-- func2 depends on func1 by direct calling.
CREATE OR REPLACE FUNCTION func1 RETURN INT AS
BEGIN
RETURN 1;
END;
/
CREATE FUNCTION

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

SELECT DISTINCT p.proname AS objname, q.proname AS refobjname FROM pg_depend AS dp
INNER JOIN pg_proc AS p ON dp.objid=p.oid
INNER JOIN pg_proc AS q ON dp.refobjid=q.oid
WHERE p.proname='func2' AND q.proname='func1';
 objname | refobjname 
---------+------------
 func2   | func1
(1 row)

-- func2 calls func1 through perform. The dependency is not recorded.
CREATE OR REPLACE FUNCTION func1 RETURN INT AS
BEGIN
RETURN 1;
END;
/
CREATE FUNCTION

CREATE OR REPLACE FUNCTION func2 RETURN INT AS
BEGIN
PERFORM func1();
RETURN 1;
END;
/
CREATE FUNCTION

SELECT DISTINCT p.proname AS objname, q.proname AS refobjname FROM pg_depend AS dp
INNER JOIN pg_proc AS p ON dp.objid=p.oid
INNER JOIN pg_proc AS q ON dp.refobjid=q.oid
WHERE p.proname='func2' AND q.proname='func1';
 objname | refobjname 
---------+------------
(0 rows)