summaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/backend/catalog/information_schema.sql100
-rw-r--r--src/backend/catalog/sql_features.txt2
-rw-r--r--src/include/catalog/catversion.h2
-rw-r--r--src/test/regress/expected/create_function_3.out38
-rw-r--r--src/test/regress/sql/create_function_3.sql24
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';
generated by cgit v1.2.3 (git 2.46.0) at 2025-08-09 20:43:37 +0000