diff options
| -rw-r--r-- | doc/src/sgml/information_schema.sgml | 443 | ||||
| -rw-r--r-- | src/backend/catalog/information_schema.sql | 100 | ||||
| -rw-r--r-- | src/backend/catalog/sql_features.txt | 2 | ||||
| -rw-r--r-- | src/include/catalog/catversion.h | 2 | ||||
| -rw-r--r-- | src/test/regress/expected/create_function_3.out | 38 | ||||
| -rw-r--r-- | src/test/regress/sql/create_function_3.sql | 24 |
6 files changed, 603 insertions, 6 deletions
diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml index 36ec17a4c60..41001982528 100644 --- a/doc/src/sgml/information_schema.sgml +++ b/doc/src/sgml/information_schema.sgml @@ -4841,6 +4841,126 @@ ORDER BY c.ordinal_position; </table> </sect1> + <sect1 id="infoschema-routine-column-usage"> + <title><literal>routine_column_usage</literal></title> + + <para> + The view <literal>routine_column_usage</literal> is meant to identify all + columns that are used by a function or procedure. This information is + currently not tracked by <productname>PostgreSQL</productname>. + </para> + + <table> + <title><literal>routine_column_usage</literal> Columns</title> + + <tgroup cols="1"> + <thead> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + Column Type + </para> + <para> + Description + </para></entry> + </row> + </thead> + + <tbody> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>specific_catalog</structfield> <type>sql_identifier</type> + </para> + <para> + Name of the database containing the function (always the current database) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>specific_schema</structfield> <type>sql_identifier</type> + </para> + <para> + Name of the schema containing the function + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>specific_name</structfield> <type>sql_identifier</type> + </para> + <para> + The <quote>specific name</quote> of the function. See <xref linkend="infoschema-routines"/> for more information. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>routine_catalog</structfield> <type>sql_identifier</type> + </para> + <para> + Name of the database containing the function (always the current database) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>routine_schema</structfield> <type>sql_identifier</type> + </para> + <para> + Name of the schema containing the function + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>routine_name</structfield> <type>sql_identifier</type> + </para> + <para> + Name of the function (might be duplicated in case of overloading) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>table_catalog</structfield> <type>sql_identifier</type> + </para> + <para> + Name of the database that contains the table that is used by the + function (always the current database) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>table_schema</structfield> <type>sql_identifier</type> + </para> + <para> + Name of the schema that contains the table that is used by the function + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>table_name</structfield> <type>sql_identifier</type> + </para> + <para> + Name of the table that is used by the function + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>column_name</structfield> <type>sql_identifier</type> + </para> + <para> + Name of the column that is used by the function + </para></entry> + </row> + </tbody> + </tgroup> + </table> + </sect1> + <sect1 id="infoschema-routine-privileges"> <title><literal>routine_privileges</literal></title> @@ -4960,6 +5080,329 @@ ORDER BY c.ordinal_position; </table> </sect1> + <sect1 id="infoschema-routine-routine-usage"> + <title><literal>routine_routine_usage</literal></title> + + <para> + The view <literal>routine_routine_usage</literal> is meant to identify all + functions or procedures that are used by another (or the same) function or + procedure, either in the body or in parameter default expressions. + Currently, only functions used in parameter default expressions are + tracked. An entry is included here only if the used function is owned by a + currently enabled role. (There is no such restriction on the using + function.) + </para> + + <para> + Note that the entries for both functions in the view refer to the + <quote>specific</quote> name of the routine, even though the column names + are used in a way that is inconsistent with other information schema views + about routines. This is per SQL standard, although it is arguably a + misdesign. See <xref linkend="infoschema-routines"/> for more information + about specific names. + </para> + + <table> + <title><literal>routine_routine_usage</literal> Columns</title> + + <tgroup cols="1"> + <thead> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + Column Type + </para> + <para> + Description + </para></entry> + </row> + </thead> + + <tbody> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>specific_catalog</structfield> <type>sql_identifier</type> + </para> + <para> + Name of the database containing the using function (always the current database) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>specific_schema</structfield> <type>sql_identifier</type> + </para> + <para> + Name of the schema containing the using function + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>specific_name</structfield> <type>sql_identifier</type> + </para> + <para> + The <quote>specific name</quote> of the using function. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>routine_catalog</structfield> <type>sql_identifier</type> + </para> + <para> + Name of the database that contains the function that is used by the + first function (always the current database) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>routine_schema</structfield> <type>sql_identifier</type> + </para> + <para> + Name of the schema that contains the function that is used by the first + function + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>routine_name</structfield> <type>sql_identifier</type> + </para> + <para> + The <quote>specific name</quote> of the function that is used by the + first function. + </para></entry> + </row> + </tbody> + </tgroup> + </table> + </sect1> + + <sect1 id="infoschema-routine-sequence-usage"> + <title><literal>routine_sequence_usage</literal></title> + + <para> + The view <literal>routine_sequence_usage</literal> is meant to identify all + sequences that are used by a function or procedure, either in the body or + in parameter default expressions. Currently, only sequences used in + parameter default expressions are tracked. A sequence is only included if + that sequence is owned by a currently enabled role. + </para> + + <table> + <title><literal>routine_sequence_usage</literal> Columns</title> + + <tgroup cols="1"> + <thead> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + Column Type + </para> + <para> + Description + </para></entry> + </row> + </thead> + + <tbody> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>specific_catalog</structfield> <type>sql_identifier</type> + </para> + <para> + Name of the database containing the function (always the current database) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>specific_schema</structfield> <type>sql_identifier</type> + </para> + <para> + Name of the schema containing the function + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>specific_name</structfield> <type>sql_identifier</type> + </para> + <para> + The <quote>specific name</quote> of the function. See <xref linkend="infoschema-routines"/> for more information. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>routine_catalog</structfield> <type>sql_identifier</type> + </para> + <para> + Name of the database containing the function (always the current database) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>routine_schema</structfield> <type>sql_identifier</type> + </para> + <para> + Name of the schema containing the function + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>routine_name</structfield> <type>sql_identifier</type> + </para> + <para> + Name of the function (might be duplicated in case of overloading) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>schema_catalog</structfield> <type>sql_identifier</type> + </para> + <para> + Name of the database that contains the sequence that is used by the + function (always the current database) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>sequence_schema</structfield> <type>sql_identifier</type> + </para> + <para> + Name of the schema that contains the sequence that is used by the function + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>sequence_name</structfield> <type>sql_identifier</type> + </para> + <para> + Name of the sequence that is used by the function + </para></entry> + </row> + </tbody> + </tgroup> + </table> + </sect1> + + <sect1 id="infoschema-routine-table-usage"> + <title><literal>routine_table_usage</literal></title> + + <para> + The view <literal>routine_table_usage</literal> is meant to identify all + tables that are used by a function or procedure. This information is + currently not tracked by <productname>PostgreSQL</productname>. + </para> + + <table> + <title><literal>routine_table_usage</literal> Columns</title> + + <tgroup cols="1"> + <thead> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + Column Type + </para> + <para> + Description + </para></entry> + </row> + </thead> + + <tbody> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>specific_catalog</structfield> <type>sql_identifier</type> + </para> + <para> + Name of the database containing the function (always the current database) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>specific_schema</structfield> <type>sql_identifier</type> + </para> + <para> + Name of the schema containing the function + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>specific_name</structfield> <type>sql_identifier</type> + </para> + <para> + The <quote>specific name</quote> of the function. See <xref linkend="infoschema-routines"/> for more information. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>routine_catalog</structfield> <type>sql_identifier</type> + </para> + <para> + Name of the database containing the function (always the current database) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>routine_schema</structfield> <type>sql_identifier</type> + </para> + <para> + Name of the schema containing the function + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>routine_name</structfield> <type>sql_identifier</type> + </para> + <para> + Name of the function (might be duplicated in case of overloading) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>table_catalog</structfield> <type>sql_identifier</type> + </para> + <para> + Name of the database that contains the table that is used by the + function (always the current database) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>table_schema</structfield> <type>sql_identifier</type> + </para> + <para> + Name of the schema that contains the table that is used by the function + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>table_name</structfield> <type>sql_identifier</type> + </para> + <para> + Name of the table that is used by the function + </para></entry> + </row> + </tbody> + </tgroup> + </table> + </sect1> + <sect1 id="infoschema-routines"> <title><literal>routines</literal></title> 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'; |