SQL-standard function body

This adds support for writing CREATE FUNCTION and CREATE PROCEDURE
statements for language SQL with a function body that conforms to the
SQL standard and is portable to other implementations.

Instead of the PostgreSQL-specific AS $$ string literal $$ syntax,
this allows writing out the SQL statements making up the body
unquoted, either as a single statement:

    CREATE FUNCTION add(a integer, b integer) RETURNS integer
        LANGUAGE SQL
        RETURN a + b;

or as a block

    CREATE PROCEDURE insert_data(a integer, b integer)
    LANGUAGE SQL
    BEGIN ATOMIC
      INSERT INTO tbl VALUES (a);
      INSERT INTO tbl VALUES (b);
    END;

The function body is parsed at function definition time and stored as
expression nodes in a new pg_proc column prosqlbody.  So at run time,
no further parsing is required.

However, this form does not support polymorphic arguments, because
there is no more parse analysis done at call time.

Dependencies between the function and the objects it uses are fully
tracked.

A new RETURN statement is introduced.  This can only be used inside
function bodies.  Internally, it is treated much like a SELECT
statement.

psql needs some new intelligence to keep track of function body
boundaries so that it doesn't send off statements when it sees
semicolons that are inside a function body.

Tested-by: Jaime Casanova <jcasanov@systemguards.com.ec>
Reviewed-by: Julien Rouhaud <rjuju123@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/1c11f1eb-f00c-43b7-799d-2d44132c02d7@2ndquadrant.com

3 files changed, 179 insertions, 17 deletions

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index f103d914a62..2656786d1e6 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -6004,6 +6004,16 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
 
      <row>
       <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>prosqlbody</structfield> <type>pg_node_tree</type>
+      </para>
+      <para>
+       Pre-parsed SQL function body.  This will be used for language SQL
+       functions if the body is not specified as a string constant.
+       </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
        <structfield>proconfig</structfield> <type>text[]</type>
       </para>
       <para>
diff --git a/doc/src/sgml/ref/create_function.sgml b/doc/src/sgml/ref/create_function.sgml
index f1001615f4a..e43705d069c 100644
--- a/doc/src/sgml/ref/create_function.sgml
+++ b/doc/src/sgml/ref/create_function.sgml
@@ -38,6 +38,7 @@ CREATE [ OR REPLACE ] FUNCTION
     | SET <replaceable class="parameter">configuration_parameter</replaceable> { TO <replaceable class="parameter">value</replaceable> | = <replaceable class="parameter">value</replaceable> | FROM CURRENT }
     | AS '<replaceable class="parameter">definition</replaceable>'
     | AS '<replaceable class="parameter">obj_file</replaceable>', '<replaceable class="parameter">link_symbol</replaceable>'
+    | <replaceable class="parameter">sql_body</replaceable>
   } ...
 </synopsis>
  </refsynopsisdiv>
@@ -262,7 +263,9 @@ CREATE [ OR REPLACE ] FUNCTION
        The name of the language that the function is implemented in.
        It can be <literal>sql</literal>, <literal>c</literal>,
        <literal>internal</literal>, or the name of a user-defined
-       procedural language, e.g., <literal>plpgsql</literal>.  Enclosing the
+       procedural language, e.g., <literal>plpgsql</literal>.  The default is
+       <literal>sql</literal> if <replaceable
+       class="parameter">sql_body</replaceable> is specified.  Enclosing the
        name in single quotes is deprecated and requires matching case.
       </para>
      </listitem>
@@ -582,6 +585,44 @@ CREATE [ OR REPLACE ] FUNCTION
      </listitem>
     </varlistentry>
 
+    <varlistentry>
+     <term><replaceable class="parameter">sql_body</replaceable></term>
+
+     <listitem>
+      <para>
+       The body of a <literal>LANGUAGE SQL</literal> function.  This can
+       either be a single statement
+<programlisting>
+RETURN <replaceable>expression</replaceable>
+</programlisting>
+       or a block
+<programlisting>
+BEGIN ATOMIC
+  <replaceable>statement</replaceable>;
+  <replaceable>statement</replaceable>;
+  ...
+  <replaceable>statement</replaceable>;
+END
+</programlisting>
+      </para>
+
+      <para>
+       This is similar to writing the text of the function body as a string
+       constant (see <replaceable>definition</replaceable> above), but there
+       are some differences: This form only works for <literal>LANGUAGE
+       SQL</literal>, the string constant form works for all languages.  This
+       form is parsed at function definition time, the string constant form is
+       parsed at execution time; therefore this form cannot support
+       polymorphic argument types and other constructs that are not resolvable
+       at function definition time.  This form tracks dependencies between the
+       function and objects used in the function body, so <literal>DROP
+       ... CASCADE</literal> will work correctly, whereas the form using
+       string literals may leave dangling functions.  Finally, this form is
+       more compatible with the SQL standard and other SQL implementations.
+      </para>
+     </listitem>
+    </varlistentry>
+
    </variablelist>
  </refsect1>
 
@@ -668,6 +709,15 @@ CREATE FUNCTION add(integer, integer) RETURNS integer
     IMMUTABLE
     RETURNS NULL ON NULL INPUT;
 </programlisting>
+   The same function written in a more SQL-conforming style, using argument
+   names and an unquoted body:
+<programlisting>
+CREATE FUNCTION add(a integer, b integer) RETURNS integer
+    LANGUAGE SQL
+    IMMUTABLE
+    RETURNS NULL ON NULL INPUT
+    RETURN a + b;
+</programlisting>
   </para>
 
   <para>
@@ -797,23 +847,74 @@ COMMIT;
   <title>Compatibility</title>
 
   <para>
-   A <command>CREATE FUNCTION</command> command is defined in the SQL standard.
-   The <productname>PostgreSQL</productname> version is similar but
-   not fully compatible.  The attributes are not portable, neither are the
-   different available languages.
+   A <command>CREATE FUNCTION</command> command is defined in the SQL
+   standard.  The <productname>PostgreSQL</productname> implementation can be
+   used in a compatible way but has many extensions.  Conversely, the SQL
+   standard specifies a number of optional features that are not implemented
+   in <productname>PostgreSQL</productname>.
   </para>
 
   <para>
-   For compatibility with some other database systems,
-   <replaceable class="parameter">argmode</replaceable> can be written
-   either before or after <replaceable class="parameter">argname</replaceable>.
-   But only the first way is standard-compliant.
+   The following are important compatibility issues:
+
+   <itemizedlist>
+    <listitem>
+     <para>
+      <literal>OR REPLACE</literal> is a PostgreSQL extension.
+     </para>
+    </listitem>
+
+    <listitem>
+     <para>
+      For compatibility with some other database systems, <replaceable
+      class="parameter">argmode</replaceable> can be written either before or
+      after <replaceable class="parameter">argname</replaceable>.  But only
+      the first way is standard-compliant.
+     </para>
+    </listitem>
+
+    <listitem>
+     <para>
+      For parameter defaults, the SQL standard specifies only the syntax with
+      the <literal>DEFAULT</literal> key word.  The syntax with
+      <literal>=</literal> is used in T-SQL and Firebird.
+     </para>
+    </listitem>
+
+    <listitem>
+     <para>
+      The <literal>SETOF</literal> modifier is a PostgreSQL extension.
+     </para>
+    </listitem>
+
+    <listitem>
+     <para>
+      Only <literal>SQL</literal> is standardized as a language.
+     </para>
+    </listitem>
+
+    <listitem>
+     <para>
+      All other attributes except <literal>CALLED ON NULL INPUT</literal> and
+      <literal>RETURNS NULL ON NULL INPUT</literal> are not standardized.
+     </para>
+    </listitem>
+
+    <listitem>
+     <para>
+      For the body of <literal>LANGUAGE SQL</literal> functions, the SQL
+      standard only specifies the <replaceable>sql_body</replaceable> form.
+     </para>
+    </listitem>
+   </itemizedlist>
   </para>
 
   <para>
-   For parameter defaults, the SQL standard specifies only the syntax with
-   the <literal>DEFAULT</literal> key word.  The syntax
-   with <literal>=</literal> is used in T-SQL and Firebird.
+   Simple <literal>LANGUAGE SQL</literal> functions can be written in a way
+   that is both standard-conforming and portable to other implementations.
+   More complex functions using advanced features, optimization attributes, or
+   other languages will necessarily be specific to PostgreSQL in a significant
+   way.
   </para>
  </refsect1>
 
diff --git a/doc/src/sgml/ref/create_procedure.sgml b/doc/src/sgml/ref/create_procedure.sgml
index 6dbc0127194..2cd47d097f3 100644
--- a/doc/src/sgml/ref/create_procedure.sgml
+++ b/doc/src/sgml/ref/create_procedure.sgml
@@ -29,6 +29,7 @@ CREATE [ OR REPLACE ] PROCEDURE
     | SET <replaceable class="parameter">configuration_parameter</replaceable> { TO <replaceable class="parameter">value</replaceable> | = <replaceable class="parameter">value</replaceable> | FROM CURRENT }
     | AS '<replaceable class="parameter">definition</replaceable>'
     | AS '<replaceable class="parameter">obj_file</replaceable>', '<replaceable class="parameter">link_symbol</replaceable>'
+    | <replaceable class="parameter">sql_body</replaceable>
   } ...
 </synopsis>
  </refsynopsisdiv>
@@ -167,7 +168,9 @@ CREATE [ OR REPLACE ] PROCEDURE
        The name of the language that the procedure is implemented in.
        It can be <literal>sql</literal>, <literal>c</literal>,
        <literal>internal</literal>, or the name of a user-defined
-       procedural language, e.g., <literal>plpgsql</literal>.  Enclosing the
+       procedural language, e.g., <literal>plpgsql</literal>.  The default is
+       <literal>sql</literal> if <replaceable
+       class="parameter">sql_body</replaceable> is specified.  Enclosing the
        name in single quotes is deprecated and requires matching case.
       </para>
      </listitem>
@@ -304,6 +307,41 @@ CREATE [ OR REPLACE ] PROCEDURE
 
      </listitem>
     </varlistentry>
+
+    <varlistentry>
+     <term><replaceable class="parameter">sql_body</replaceable></term>
+
+     <listitem>
+      <para>
+       The body of a <literal>LANGUAGE SQL</literal> procedure.  This should
+       be a block
+<programlisting>
+BEGIN ATOMIC
+  <replaceable>statement</replaceable>;
+  <replaceable>statement</replaceable>;
+  ...
+  <replaceable>statement</replaceable>;
+END
+</programlisting>
+      </para>
+
+      <para>
+       This is similar to writing the text of the procedure body as a string
+       constant (see <replaceable>definition</replaceable> above), but there
+       are some differences: This form only works for <literal>LANGUAGE
+       SQL</literal>, the string constant form works for all languages.  This
+       form is parsed at procedure definition time, the string constant form is
+       parsed at execution time; therefore this form cannot support
+       polymorphic argument types and other constructs that are not resolvable
+       at procedure definition time.  This form tracks dependencies between the
+       procedure and objects used in the procedure body, so <literal>DROP
+       ... CASCADE</literal> will work correctly, whereas the form using
+       string literals may leave dangling procedures.  Finally, this form is
+       more compatible with the SQL standard and other SQL implementations.
+      </para>
+     </listitem>
+    </varlistentry>
+
    </variablelist>
  </refsect1>
 
@@ -323,6 +361,7 @@ CREATE [ OR REPLACE ] PROCEDURE
  <refsect1 id="sql-createprocedure-examples">
   <title>Examples</title>
 
+  <para>
 <programlisting>
 CREATE PROCEDURE insert_data(a integer, b integer)
 LANGUAGE SQL
@@ -330,9 +369,21 @@ AS $$
 INSERT INTO tbl VALUES (a);
 INSERT INTO tbl VALUES (b);
 $$;
-
+</programlisting>
+   or
+<programlisting>
+CREATE PROCEDURE insert_data(a integer, b integer)
+LANGUAGE SQL
+BEGIN ATOMIC
+  INSERT INTO tbl VALUES (a);
+  INSERT INTO tbl VALUES (b);
+END;
+</programlisting>
+   and call like this:
+<programlisting>
 CALL insert_data(1, 2);
 </programlisting>
+  </para>
  </refsect1>
 
  <refsect1 id="sql-createprocedure-compat">
@@ -340,9 +391,9 @@ CALL insert_data(1, 2);
 
   <para>
    A <command>CREATE PROCEDURE</command> command is defined in the SQL
-   standard.  The <productname>PostgreSQL</productname> version is similar but
-   not fully compatible.  For details see
-   also <xref linkend="sql-createfunction"/>.
+   standard.  The <productname>PostgreSQL</productname> implementation can be
+   used in a compatible way but has many extensions.  For details see also
+   <xref linkend="sql-createfunction"/>.
   </para>
  </refsect1>