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.
I developed this as part of the patch "SQL-standard function body",
where it would become more useful, but I'm sending it now separately to
not bloat the other patch further.
[0]:
https://www.postgresql.org/message-id/flat/1c11f1eb-f00c-43b7-799d-2d44132c0...@2ndquadrant.com
From f3a62c680760f57693dac2be2e7ba7c8cfa04fd4 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Tue, 9 Feb 2021 07:42:25 +0100
Subject: [PATCH] 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.
---
doc/src/sgml/information_schema.sgml | 443 ++++++++++++++++++
src/backend/catalog/information_schema.sql | 100 +++-
src/backend/catalog/sql_features.txt | 2 +-
.../regress/expected/create_function_3.out | 38 ++
src/test/regress/sql/create_function_3.sql | 24 +
5 files changed, 602 insertions(+), 5 deletions(-)
diff --git a/doc/src/sgml/information_schema.sgml
b/doc/src/sgml/information_schema.sgml
index 36ec17a4c6..9d55120024 100644
--- a/doc/src/sgml/information_schema.sgml
+++ b/doc/src/sgml/information_schema.sgml
@@ -4841,6 +4841,126 @@ <title><structname>role_usage_grants</structname>
Columns</title>
</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 @@ <title><structname>routine_privileges</structname>
Columns</title>
</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 ony 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 4907855043..513cb9a69c 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -1325,7 +1325,33 @@ CREATE VIEW role_column_grants AS
* 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 @@ CREATE VIEW role_routine_grants AS
* 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 @@ CREATE VIEW role_routine_grants AS
* 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 @@ CREATE VIEW role_routine_grants AS
* 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 86519ad297..a24387c1e7 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/test/regress/expected/create_function_3.out
b/src/test/regress/expected/create_function_3.out
index ce508ae1dc..f25a407fdd 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 bd108a918f..549b34b4b2 100644
--- a/src/test/regress/sql/create_function_3.sql
+++ b/src/test/regress/sql/create_function_3.sql
@@ -177,6 +177,30 @@ CREATE FUNCTION functest_IS_3(a int default 1, out b int)
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';
base-commit: 7cb3048f38e26b39dd5fd412ed8a4981b6809b35
--
2.30.0
