Doc: explain about dependency tracking for new-style SQL functions.

5.14 Dependency Tracking was not updated when we added new-style
SQL functions.  Improve that.

Noted by Sami Imseih.  Back-patch to v14 where
new-style SQL functions came in.

Discussion: https://postgr.es/m/2C1933AB-C2F8-499B-9D18-4AC1882256A0@amazon.com

1 files changed, 20 insertions, 2 deletions

diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 9717d1596de..cfe3dcbd392 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -5193,8 +5193,9 @@ DROP TABLE products CASCADE;
   </para>
 
   <para>
-   For user-defined functions, <productname>PostgreSQL</productname> tracks
-   dependencies associated with a function's externally-visible properties,
+   For a user-defined function or procedure whose body is defined as a string
+   literal, <productname>PostgreSQL</productname> tracks
+   dependencies associated with the function's externally-visible properties,
    such as its argument and result types, but <emphasis>not</emphasis> dependencies
    that could only be known by examining the function body.  As an example,
    consider this situation:
@@ -5222,6 +5223,23 @@ CREATE FUNCTION get_color_note (rainbow) RETURNS text AS
    table is missing, though executing it would cause an error; creating a new
    table of the same name would allow the function to work again.
   </para>
+
+  <para>
+   On the other hand, for a SQL-language function or procedure whose body
+   is written in SQL-standard style, the body is parsed at function
+   definition time and all dependencies recognized by the parser are
+   stored.  Thus, if we write the function above as
+
+<programlisting>
+CREATE FUNCTION get_color_note (rainbow) RETURNS text
+BEGIN ATOMIC
+  SELECT note FROM my_colors WHERE color = $1;
+END;
+</programlisting>
+
+   then the function's dependency on the <structname>my_colors</structname>
+   table will be known and enforced by <command>DROP</command>.
+  </para>
  </sect1>
 
 </chapter>