pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedINFORMATION_SCHEMA.routine_column_usage
3+ messages / 2 participants
[nested] [flat]
* INFORMATION_SCHEMA.routine_column_usage
@ 2023-11-24 11:04 Erki Eessaar <erki.eessaar@taltech.ee>
0 siblings, 1 reply; 3+ messages in thread
From: Erki Eessaar @ 2023-11-24 11:04 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
Hello
Here
https://www.postgresql.org/docs/current/infoschema-routine-column-usage.html
it is stated that "The view routine_column_usage is meant to identify all columns that are used by a function or procedure. This information is currently not tracked by PostgreSQL."
However, this claim is incorrect because the view provides information about the column usage of SQL functions that have SQL-standard function body.
CREATE TABLE Person (person_id SERIAL,
given_name VARCHAR(50) NOT NULL,
reg_time TIMESTAMP(0) NOT NULL DEFAULT LOCALTIMESTAMP(0),
CONSTRAINT pk_person PRIMARY KEY (person_id));
CREATE FUNCTION f_reg_person(p_given_name Person.given_name%TYPE)
RETURNS Person.person_id%TYPE
LANGUAGE SQL SECURITY DEFINER
BEGIN ATOMIC
INSERT INTO Person (given_name) VALUES (p_given_name)
RETURNING person_id;
END;
SELECT * FROM Information_schema.routine_column_usage;
Returns information about two columns - person_id and given_name.
Perhaps the better wording is:
The view routine_column_usage is meant to identify all columns that are used by a function or procedure. This information is tracked by PostgreSQL in case of routines that have SQL-standard function body.
Best regards
Erki Eessaar
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: INFORMATION_SCHEMA.routine_column_usage
@ 2023-11-29 14:01 Peter Eisentraut <peter@eisentraut.org>
parent: Erki Eessaar <erki.eessaar@taltech.ee>
0 siblings, 1 reply; 3+ messages in thread
From: Peter Eisentraut @ 2023-11-29 14:01 UTC (permalink / raw)
To: Erki Eessaar <erki.eessaar@taltech.ee>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
> Here
> https://www.postgresql.org/docs/current/infoschema-routine-column-usage.html <https://www.postgresql.org/docs/current/infoschema-routine-column-usage.html;
>
> it is stated that "The view |routine_column_usage| is meant to identify
> all columns that are used by a function or procedure. This information
> is currently not tracked by PostgreSQL."
>
> However, this claim is incorrect because the view provides information
> about the column usage of SQL functions that have SQL-standard function
> body.
Correct, the documentation was not updated when this was added. A few
other views are similarly affected. How about this attached patch.
From dfe65ace27a5b2e11511e5d929eea356c8ca89e5 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Wed, 29 Nov 2023 14:56:41 +0100
Subject: [PATCH] doc: Update info on information schema usage tables
Commit f40c6969d0 added the information schema usage tables but added
documentation that they did not fully work yet. Commit e717a9a18b
then added SQL-standard function bodies, which made the information
schema views fully functional, but it neglected to update the
documentation. This is now done here.
Reported-by: Erki Eessaar <erki.eessaar@taltech.ee>
Discussion: https://www.postgresql.org/message-id/flat/AM9PR01MB8268EC7B696F9FE346CA5B93FEB8A%40AM9PR01MB8268.eu...
TODO: backpatch 14+
---
doc/src/sgml/information_schema.sgml | 30 +++++++++++++++-------------
1 file changed, 16 insertions(+), 14 deletions(-)
diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml
index 9c39e9aab5..0ca7d5a9e0 100644
--- a/doc/src/sgml/information_schema.sgml
+++ b/doc/src/sgml/information_schema.sgml
@@ -4846,9 +4846,11 @@ <title><structname>role_usage_grants</structname> Columns</title>
<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>.
+ The view <literal>routine_column_usage</literal> identifies all columns
+ that are used by a function or procedure, either in the SQL body or in
+ parameter default expressions. (This only works for unquoted SQL bodies,
+ not quoted bodies or functions in other languages.) A column is only
+ included if its table is owned by a currently enabled role.
</para>
<table>
@@ -5085,12 +5087,12 @@ <title><structname>routine_privileges</structname> Columns</title>
<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
+ The view <literal>routine_routine_usage</literal> identifies all functions
+ or procedures that are used by another (or the same) function or procedure,
+ either in the SQL body or in parameter default expressions. (This only
+ works for unquoted SQL bodies, not quoted bodies or functions in other
+ languages.) 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>
@@ -5184,11 +5186,11 @@ <title><literal>routine_routine_usage</literal> Columns</title>
<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.
+ The view <literal>routine_sequence_usage</literal> identifies all sequences
+ that are used by a function or procedure, either in the SQL body or in
+ parameter default expressions. (This only works for unquoted SQL bodies,
+ not quoted bodies or functions in other languages.) A sequence is only
+ included if that sequence is owned by a currently enabled role.
</para>
<table>
--
2.43.0
Attachments:
[text/plain] 0001-doc-Update-info-on-information-schema-usage-tables.patch (3.8K, ../../28163ed6-e3aa-49b3-8053-02f6c145f02f@eisentraut.org/2-0001-doc-Update-info-on-information-schema-usage-tables.patch)
download | inline diff:
From dfe65ace27a5b2e11511e5d929eea356c8ca89e5 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Wed, 29 Nov 2023 14:56:41 +0100
Subject: [PATCH] doc: Update info on information schema usage tables
Commit f40c6969d0 added the information schema usage tables but added
documentation that they did not fully work yet. Commit e717a9a18b
then added SQL-standard function bodies, which made the information
schema views fully functional, but it neglected to update the
documentation. This is now done here.
Reported-by: Erki Eessaar <erki.eessaar@taltech.ee>
Discussion: https://www.postgresql.org/message-id/flat/AM9PR01MB8268EC7B696F9FE346CA5B93FEB8A%40AM9PR01MB8268.eurprd01.prod.exchangelabs.com
TODO: backpatch 14+
---
doc/src/sgml/information_schema.sgml | 30 +++++++++++++++-------------
1 file changed, 16 insertions(+), 14 deletions(-)
diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml
index 9c39e9aab5..0ca7d5a9e0 100644
--- a/doc/src/sgml/information_schema.sgml
+++ b/doc/src/sgml/information_schema.sgml
@@ -4846,9 +4846,11 @@ <title><structname>role_usage_grants</structname> Columns</title>
<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>.
+ The view <literal>routine_column_usage</literal> identifies all columns
+ that are used by a function or procedure, either in the SQL body or in
+ parameter default expressions. (This only works for unquoted SQL bodies,
+ not quoted bodies or functions in other languages.) A column is only
+ included if its table is owned by a currently enabled role.
</para>
<table>
@@ -5085,12 +5087,12 @@ <title><structname>routine_privileges</structname> Columns</title>
<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
+ The view <literal>routine_routine_usage</literal> identifies all functions
+ or procedures that are used by another (or the same) function or procedure,
+ either in the SQL body or in parameter default expressions. (This only
+ works for unquoted SQL bodies, not quoted bodies or functions in other
+ languages.) 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>
@@ -5184,11 +5186,11 @@ <title><literal>routine_routine_usage</literal> Columns</title>
<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.
+ The view <literal>routine_sequence_usage</literal> identifies all sequences
+ that are used by a function or procedure, either in the SQL body or in
+ parameter default expressions. (This only works for unquoted SQL bodies,
+ not quoted bodies or functions in other languages.) A sequence is only
+ included if that sequence is owned by a currently enabled role.
</para>
<table>
--
2.43.0
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: INFORMATION_SCHEMA.routine_column_usage
@ 2023-12-01 07:53 Peter Eisentraut <peter@eisentraut.org>
parent: Peter Eisentraut <peter@eisentraut.org>
0 siblings, 0 replies; 3+ messages in thread
From: Peter Eisentraut @ 2023-12-01 07:53 UTC (permalink / raw)
To: Erki Eessaar <erki.eessaar@taltech.ee>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
On 29.11.23 15:01, Peter Eisentraut wrote:
>> Here
>> https://www.postgresql.org/docs/current/infoschema-routine-column-usage.html <https://www.postgresql.org/docs/current/infoschema-routine-column-usage.html;
>>
>> it is stated that "The view |routine_column_usage| is meant to
>> identify all columns that are used by a function or procedure. This
>> information is currently not tracked by PostgreSQL."
>>
>> However, this claim is incorrect because the view provides information
>> about the column usage of SQL functions that have SQL-standard
>> function body.
>
> Correct, the documentation was not updated when this was added. A few
> other views are similarly affected. How about this attached patch.
Fix committed.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-12-01 07:53 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-11-24 11:04 INFORMATION_SCHEMA.routine_column_usage Erki Eessaar <erki.eessaar@taltech.ee>
2023-11-29 14:01 ` Peter Eisentraut <peter@eisentraut.org>
2023-12-01 07:53 ` Peter Eisentraut <peter@eisentraut.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox