Routine usage information schema tables

Several information schema views track dependencies between
functions/procedures and objects used by them.  These had not been
implemented so far because PostgreSQL doesn't track objects used in a
function body.  However, formally, these also show dependencies used
in parameter default expressions, which PostgreSQL does support and
track.  So for the sake of completeness, we might as well add these.
If dependency tracking for function bodies is ever implemented, these
views will automatically work correctly.

Reviewed-by: Erik Rijkers <er@xs4all.nl>
Discussion: https://www.postgresql.org/message-id/flat/ac80fc74-e387-8950-9a31-2560778fc1e3%40enterprisedb.com

5 files changed, 160 insertions, 6 deletions

diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index 4907855043d..513cb9a69cf 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -1325,7 +1325,33 @@ GRANT SELECT ON role_column_grants TO PUBLIC;
  * ROUTINE_COLUMN_USAGE view
  */
 
--- not tracked by PostgreSQL
+CREATE VIEW routine_column_usage AS
+    SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
+           CAST(np.nspname AS sql_identifier) AS specific_schema,
+           CAST(nameconcatoid(p.proname, p.oid) AS sql_identifier) AS specific_name,
+           CAST(current_database() AS sql_identifier) AS routine_catalog,
+           CAST(np.nspname AS sql_identifier) AS routine_schema,
+           CAST(p.proname AS sql_identifier) AS routine_name,
+           CAST(current_database() AS sql_identifier) AS table_catalog,
+           CAST(nt.nspname AS sql_identifier) AS table_schema,
+           CAST(t.relname AS sql_identifier) AS table_name,
+           CAST(a.attname AS sql_identifier) AS column_name
+
+    FROM pg_namespace np, pg_proc p, pg_depend d,
+         pg_class t, pg_namespace nt, pg_attribute a
+
+    WHERE np.oid = p.pronamespace
+          AND p.oid = d.objid
+          AND d.classid = 'pg_catalog.pg_proc'::regclass
+          AND d.refobjid = t.oid
+          AND d.refclassid = 'pg_catalog.pg_class'::regclass
+          AND t.relnamespace = nt.oid
+          AND t.relkind IN ('r', 'v', 'f', 'p')
+          AND t.oid = a.attrelid
+          AND d.refobjsubid = a.attnum
+          AND pg_has_role(t.relowner, 'USAGE');
+
+GRANT SELECT ON routine_column_usage TO PUBLIC;
 
 
 /*
@@ -1408,7 +1434,27 @@ GRANT SELECT ON role_routine_grants TO PUBLIC;
  * ROUTINE_ROUTINE_USAGE view
  */
 
--- not tracked by PostgreSQL
+CREATE VIEW routine_routine_usage AS
+    SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
+           CAST(np.nspname AS sql_identifier) AS specific_schema,
+           CAST(nameconcatoid(p.proname, p.oid) AS sql_identifier) AS specific_name,
+           CAST(current_database() AS sql_identifier) AS routine_catalog,
+           CAST(np1.nspname AS sql_identifier) AS routine_schema,
+           CAST(nameconcatoid(p1.proname, p1.oid) AS sql_identifier) AS routine_name
+
+    FROM pg_namespace np, pg_proc p, pg_depend d,
+         pg_proc p1, pg_namespace np1
+
+    WHERE np.oid = p.pronamespace
+          AND p.oid = d.objid
+          AND d.classid = 'pg_catalog.pg_proc'::regclass
+          AND d.refobjid = p1.oid
+          AND d.refclassid = 'pg_catalog.pg_proc'::regclass
+          AND p1.pronamespace = np1.oid
+          AND p.prokind IN ('f', 'p') AND p1.prokind IN ('f', 'p')
+          AND pg_has_role(p1.proowner, 'USAGE');
+
+GRANT SELECT ON routine_routine_usage TO PUBLIC;
 
 
 /*
@@ -1416,7 +1462,30 @@ GRANT SELECT ON role_routine_grants TO PUBLIC;
  * ROUTINE_SEQUENCE_USAGE view
  */
 
--- not tracked by PostgreSQL
+CREATE VIEW routine_sequence_usage AS
+    SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
+           CAST(np.nspname AS sql_identifier) AS specific_schema,
+           CAST(nameconcatoid(p.proname, p.oid) AS sql_identifier) AS specific_name,
+           CAST(current_database() AS sql_identifier) AS routine_catalog,
+           CAST(np.nspname AS sql_identifier) AS routine_schema,
+           CAST(p.proname AS sql_identifier) AS routine_name,
+           CAST(current_database() AS sql_identifier) AS sequence_catalog,
+           CAST(ns.nspname AS sql_identifier) AS sequence_schema,
+           CAST(s.relname AS sql_identifier) AS sequence_name
+
+    FROM pg_namespace np, pg_proc p, pg_depend d,
+         pg_class s, pg_namespace ns
+
+    WHERE np.oid = p.pronamespace
+          AND p.oid = d.objid
+          AND d.classid = 'pg_catalog.pg_proc'::regclass
+          AND d.refobjid = s.oid
+          AND d.refclassid = 'pg_catalog.pg_class'::regclass
+          AND s.relnamespace = ns.oid
+          AND s.relkind = 'S'
+          AND pg_has_role(s.relowner, 'USAGE');
+
+GRANT SELECT ON routine_sequence_usage TO PUBLIC;
 
 
 /*
@@ -1424,7 +1493,30 @@ GRANT SELECT ON role_routine_grants TO PUBLIC;
  * ROUTINE_TABLE_USAGE view
  */
 
--- not tracked by PostgreSQL
+CREATE VIEW routine_table_usage AS
+    SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
+           CAST(np.nspname AS sql_identifier) AS specific_schema,
+           CAST(nameconcatoid(p.proname, p.oid) AS sql_identifier) AS specific_name,
+           CAST(current_database() AS sql_identifier) AS routine_catalog,
+           CAST(np.nspname AS sql_identifier) AS routine_schema,
+           CAST(p.proname AS sql_identifier) AS routine_name,
+           CAST(current_database() AS sql_identifier) AS table_catalog,
+           CAST(nt.nspname AS sql_identifier) AS table_schema,
+           CAST(t.relname AS sql_identifier) AS table_name
+
+    FROM pg_namespace np, pg_proc p, pg_depend d,
+         pg_class t, pg_namespace nt
+
+    WHERE np.oid = p.pronamespace
+          AND p.oid = d.objid
+          AND d.classid = 'pg_catalog.pg_proc'::regclass
+          AND d.refobjid = t.oid
+          AND d.refclassid = 'pg_catalog.pg_class'::regclass
+          AND t.relnamespace = nt.oid
+          AND t.relkind IN ('r', 'v', 'f', 'p')
+          AND pg_has_role(t.relowner, 'USAGE');
+
+GRANT SELECT ON routine_table_usage TO PUBLIC;
 
 
 /*
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index 86519ad2974..a24387c1e76 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -243,7 +243,7 @@ F312	MERGE statement			NO	consider INSERT ... ON CONFLICT DO UPDATE
 F313	Enhanced MERGE statement			NO	
 F314	MERGE statement with DELETE branch			NO	
 F321	User authorization			YES	
-F341	Usage tables			NO	no ROUTINE_*_USAGE tables
+F341	Usage tables			YES	
 F361	Subprogram support			YES	
 F381	Extended schema manipulation			YES	
 F381	Extended schema manipulation	01	ALTER TABLE statement: ALTER COLUMN clause	YES	
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index ac8b8e7ee8a..bdf120fea94 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -53,6 +53,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	202102151
+#define CATALOG_VERSION_NO	202102171
 
 #endif
diff --git a/src/test/regress/expected/create_function_3.out b/src/test/regress/expected/create_function_3.out
index ce508ae1dcc..f25a407fddc 100644
--- a/src/test/regress/expected/create_function_3.out
+++ b/src/test/regress/expected/create_function_3.out
@@ -284,6 +284,44 @@ SELECT routine_name, ordinal_position, parameter_name, parameter_default
 (7 rows)
 
 DROP FUNCTION functest_IS_1(int, int, text), functest_IS_2(int), functest_IS_3(int);
+-- routine usage views
+CREATE FUNCTION functest_IS_4a() RETURNS int LANGUAGE SQL AS 'SELECT 1';
+CREATE FUNCTION functest_IS_4b(x int DEFAULT functest_IS_4a()) RETURNS int LANGUAGE SQL AS 'SELECT x';
+CREATE SEQUENCE functest1;
+CREATE FUNCTION functest_IS_5(x int DEFAULT nextval('functest1'))
+    RETURNS int
+    LANGUAGE SQL
+    AS 'SELECT x';
+SELECT r0.routine_name, r1.routine_name
+  FROM information_schema.routine_routine_usage rru
+       JOIN information_schema.routines r0 ON r0.specific_name = rru.specific_name
+       JOIN information_schema.routines r1 ON r1.specific_name = rru.routine_name;
+  routine_name  |  routine_name  
+----------------+----------------
+ functest_is_4b | functest_is_4a
+(1 row)
+
+SELECT routine_name, sequence_name FROM information_schema.routine_sequence_usage;
+ routine_name  | sequence_name 
+---------------+---------------
+ functest_is_5 | functest1
+(1 row)
+
+-- currently empty
+SELECT routine_name, table_name, column_name FROM information_schema.routine_column_usage;
+ routine_name | table_name | column_name 
+--------------+------------+-------------
+(0 rows)
+
+SELECT routine_name, table_name FROM information_schema.routine_table_usage;
+ routine_name | table_name 
+--------------+------------
+(0 rows)
+
+DROP FUNCTION functest_IS_4a CASCADE;
+NOTICE:  drop cascades to function functest_is_4b(integer)
+DROP SEQUENCE functest1 CASCADE;
+NOTICE:  drop cascades to function functest_is_5(integer)
 -- overload
 CREATE FUNCTION functest_B_2(bigint) RETURNS bool LANGUAGE 'sql'
        IMMUTABLE AS 'SELECT $1 > 0';
diff --git a/src/test/regress/sql/create_function_3.sql b/src/test/regress/sql/create_function_3.sql
index bd108a918fb..549b34b4b2a 100644
--- a/src/test/regress/sql/create_function_3.sql
+++ b/src/test/regress/sql/create_function_3.sql
@@ -177,6 +177,30 @@ SELECT routine_name, ordinal_position, parameter_name, parameter_default
 
 DROP FUNCTION functest_IS_1(int, int, text), functest_IS_2(int), functest_IS_3(int);
 
+-- routine usage views
+
+CREATE FUNCTION functest_IS_4a() RETURNS int LANGUAGE SQL AS 'SELECT 1';
+CREATE FUNCTION functest_IS_4b(x int DEFAULT functest_IS_4a()) RETURNS int LANGUAGE SQL AS 'SELECT x';
+
+CREATE SEQUENCE functest1;
+CREATE FUNCTION functest_IS_5(x int DEFAULT nextval('functest1'))
+    RETURNS int
+    LANGUAGE SQL
+    AS 'SELECT x';
+
+SELECT r0.routine_name, r1.routine_name
+  FROM information_schema.routine_routine_usage rru
+       JOIN information_schema.routines r0 ON r0.specific_name = rru.specific_name
+       JOIN information_schema.routines r1 ON r1.specific_name = rru.routine_name;
+SELECT routine_name, sequence_name FROM information_schema.routine_sequence_usage;
+-- currently empty
+SELECT routine_name, table_name, column_name FROM information_schema.routine_column_usage;
+SELECT routine_name, table_name FROM information_schema.routine_table_usage;
+
+DROP FUNCTION functest_IS_4a CASCADE;
+DROP SEQUENCE functest1 CASCADE;
+
+
 -- overload
 CREATE FUNCTION functest_B_2(bigint) RETURNS bool LANGUAGE 'sql'
        IMMUTABLE AS 'SELECT $1 > 0';