Implement the DO statement to support execution of PL code without having

to create a function for it.

Procedural languages now have an additional entry point, namely a function
to execute an inline code block.  This seemed a better design than trying
to hide the transient-ness of the code from the PL.  As of this patch, only
plpgsql has an inline handler, but probably people will soon write handlers
for the other standard PLs.

In passing, remove the long-dead LANCOMPILER option of CREATE LANGUAGE.

Petr Jelinek

8 files changed, 225 insertions, 25 deletions

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 3f7fdf16b45..dd103573a5d 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.206 2009/08/10 22:13:50 alvherre Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.207 2009/09/22 23:43:37 tgl Exp $ -->
 <!--
  Documentation of the system catalogs, directed toward PostgreSQL developers
  -->
@@ -2942,6 +2942,18 @@
      </row>
 
      <row>
+      <entry><structfield>laninline</structfield></entry>
+      <entry><type>oid</type></entry>
+      <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
+      <entry>
+       This references a function that is responsible for executing
+       <quote>inline</> anonymous code blocks
+       (<xref linkend="sql-do" endterm="sql-do-title"> blocks).
+       Zero if inline blocks are not supported
+      </entry>
+     </row>
+
+     <row>
       <entry><structfield>lanvalidator</structfield></entry>
       <entry><type>oid</type></entry>
       <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
@@ -3548,6 +3560,12 @@
      </row>
 
      <row>
+      <entry><structfield>tmplinline</structfield></entry>
+      <entry><type>text</type></entry>
+      <entry>Name of anonymous-block handler function, or NULL if none</entry>
+     </row>
+
+     <row>
       <entry><structfield>tmplvalidator</structfield></entry>
       <entry><type>text</type></entry>
       <entry>Name of validator function, or NULL if none</entry>
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index fa2a12feab1..ee28bbb1079 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.228 2009/09/13 19:52:29 petere Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.229 2009/09/22 23:43:37 tgl Exp $ -->
 
 <chapter Id="runtime-config">
   <title>Server Configuration</title>
@@ -3964,6 +3964,21 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-default-do-language" xreflabel="default_do_language">
+      <term><varname>default_do_language</varname> (<type>string</type>)</term>
+      <indexterm>
+       <primary><varname>default_do_language</> configuration parameter</primary>
+      </indexterm>
+      <listitem>
+       <para>
+        This parameter specifies the language to use when the
+        <literal>LANGUAGE</> option is omitted in a
+        <xref linkend="sql-do" endterm="sql-do-title"> statement.
+        The default is <literal>plpgsql</literal>.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry id="guc-default-transaction-isolation" xreflabel="default_transaction_isolation">
       <indexterm>
        <primary>transaction isolation level</primary>
diff --git a/doc/src/sgml/keywords.sgml b/doc/src/sgml/keywords.sgml
index cb167ce10b4..8bfede77d6f 100644
--- a/doc/src/sgml/keywords.sgml
+++ b/doc/src/sgml/keywords.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/keywords.sgml,v 2.25 2009/04/06 15:01:36 tgl Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/keywords.sgml,v 2.26 2009/09/22 23:43:37 tgl Exp $ -->
 
 <appendix id="sql-keywords-appendix">
  <title><acronym>SQL</acronym> Key Words</title>
@@ -2376,6 +2376,14 @@
     <entry>reserved</entry>
    </row>
    <row>
+    <entry><token>INLINE</token></entry>
+    <entry>non-reserved</entry>
+    <entry></entry>
+    <entry></entry>
+    <entry></entry>
+    <entry></entry>
+   </row>
+   <row>
     <entry><token>INNER</token></entry>
     <entry>reserved (can be function or type)</entry>
     <entry>reserved</entry>
@@ -2576,14 +2584,6 @@
     <entry></entry>
    </row>
    <row>
-    <entry><token>LANCOMPILER</token></entry>
-    <entry>non-reserved</entry>
-    <entry></entry>
-    <entry></entry>
-    <entry></entry>
-    <entry></entry>
-   </row>
-   <row>
     <entry><token>LANGUAGE</token></entry>
     <entry>non-reserved</entry>
     <entry>reserved</entry>
diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml
index 6c20b623c49..845033b6b66 100644
--- a/doc/src/sgml/ref/allfiles.sgml
+++ b/doc/src/sgml/ref/allfiles.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/allfiles.sgml,v 1.74 2008/12/19 16:25:16 petere Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/allfiles.sgml,v 1.75 2009/09/22 23:43:37 tgl Exp $
 PostgreSQL documentation
 Complete list of usable sgml source files in this directory.
 -->
@@ -77,6 +77,7 @@ Complete list of usable sgml source files in this directory.
 <!entity declare            system "declare.sgml">
 <!entity delete             system "delete.sgml">
 <!entity discard            system "discard.sgml">
+<!entity do                 system "do.sgml">
 <!entity dropAggregate      system "drop_aggregate.sgml">
 <!entity dropCast           system "drop_cast.sgml">
 <!entity dropConversion     system "drop_conversion.sgml">
diff --git a/doc/src/sgml/ref/create_language.sgml b/doc/src/sgml/ref/create_language.sgml
index ae02995e37f..4c0463ddec1 100644
--- a/doc/src/sgml/ref/create_language.sgml
+++ b/doc/src/sgml/ref/create_language.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/create_language.sgml,v 1.45 2008/11/14 10:22:46 petere Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/create_language.sgml,v 1.46 2009/09/22 23:43:37 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -23,7 +23,7 @@ PostgreSQL documentation
 <synopsis>
 CREATE [ PROCEDURAL ] LANGUAGE <replaceable class="parameter">name</replaceable>
 CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE <replaceable class="parameter">name</replaceable>
-    HANDLER <replaceable class="parameter">call_handler</replaceable> [ VALIDATOR <replaceable>valfunction</replaceable> ]
+    HANDLER <replaceable class="parameter">call_handler</replaceable> [ INLINE <replaceable class="parameter">inline_handler</replaceable> ] [ VALIDATOR <replaceable>valfunction</replaceable> ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -133,7 +133,7 @@ CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE <replaceable class="parameter">name</
       <para>
        <replaceable class="parameter">call_handler</replaceable> is
        the name of a previously registered function that will be
-       called to execute the procedural language functions.  The call
+       called to execute the procedural language's functions.  The call
        handler for a procedural language must be written in a compiled
        language such as C with version 1 call convention and
        registered with <productname>PostgreSQL</productname> as a
@@ -145,6 +145,27 @@ CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE <replaceable class="parameter">name</
     </varlistentry>
 
     <varlistentry>
+     <term><literal>INLINE</literal> <replaceable class="parameter">inline_handler</replaceable></term>
+
+     <listitem>
+      <para>
+       <replaceable class="parameter">inline_handler</replaceable> is the
+       name of a previously registered function that will be called
+       to execute an anonymous code block
+       (<xref linkend="sql-do" endterm="sql-do-title"> command)
+       in this language.
+       If no <replaceable class="parameter">inline_handler</replaceable>
+       function is specified, the language does not support anonymous code
+       blocks.
+       The handler function must take one argument of
+       type <type>internal</type>, which will be the <command>DO</> command's
+       internal representation, and it will typically return
+       <type>void</>.  The return value of the handler is ignored.
+      </para>
+     </listitem>
+    </varlistentry>
+
+    <varlistentry>
      <term><literal>VALIDATOR</literal> <replaceable class="parameter">valfunction</replaceable></term>
 
      <listitem>
@@ -216,7 +237,8 @@ CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE <replaceable class="parameter">name</
   </para>
 
   <para>
-   The call handler function and the validator function (if any)
+   The call handler function, the inline handler function (if any),
+   and the validator function (if any)
    must already exist if the server does not have an entry for the language
    in <structname>pg_pltemplate</>.  But when there is an entry,
    the functions need not already exist;
@@ -230,7 +252,7 @@ CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE <replaceable class="parameter">name</
    In <productname>PostgreSQL</productname> versions before 7.3, it was
    necessary to declare handler functions as returning the placeholder
    type <type>opaque</>, rather than <type>language_handler</>.
-   To support loading 
+   To support loading
    of old dump files, <command>CREATE LANGUAGE</> will accept a function
    declared as returning <type>opaque</>, but it will issue a notice and
    change the function's declared return type to <type>language_handler</>.
diff --git a/doc/src/sgml/ref/do.sgml b/doc/src/sgml/ref/do.sgml
new file mode 100644
index 00000000000..2fb53806630
--- /dev/null
+++ b/doc/src/sgml/ref/do.sgml
@@ -0,0 +1,122 @@
+<!--
+$PostgreSQL: pgsql/doc/src/sgml/ref/do.sgml,v 1.1 2009/09/22 23:43:37 tgl Exp $
+PostgreSQL documentation
+-->
+
+<refentry id="SQL-DO">
+ <refmeta>
+  <refentrytitle id="sql-do-title">DO</refentrytitle>
+  <manvolnum>7</manvolnum>
+  <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+  <refname>DO</refname>
+  <refpurpose>execute an anonymous code block</refpurpose>
+ </refnamediv>
+
+ <indexterm zone="sql-do">
+  <primary>DO</primary>
+ </indexterm>
+
+ <indexterm zone="sql-do">
+  <primary>anonymous code blocks</primary>
+ </indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+DO <replaceable class="PARAMETER">code</replaceable> [ LANGUAGE <replaceable class="PARAMETER">lang_name</replaceable> ]
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+  <title>Description</title>
+
+  <para>
+   <command>DO</command> executes an anonymous code block, or in other
+   words a transient anonymous function in a procedural language.
+  </para>
+
+  <para>
+   The code block is treated as though it were the body of a function
+   with no parameters, returning <type>void</>.  It is parsed and
+   executed a single time.
+  </para>
+ </refsect1>
+
+ <refsect1>
+  <title>Parameters</title>
+
+  <variablelist>
+   <varlistentry>
+    <term><replaceable class="PARAMETER">code</replaceable></term>
+    <listitem>
+     <para>
+      The procedural language code to be executed.  This must be specified
+      as a string literal, just as in <command>CREATE FUNCTION</>.
+      Use of a dollar-quoted literal is recommended.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><replaceable class="PARAMETER">lang_name</replaceable></term>
+    <listitem>
+     <para>
+      The name of the procedural language the code is written in.
+      If omitted, the default is determined by the runtime parameter
+      <xref linkend="guc-default-do-language">.
+     </para>
+    </listitem>
+   </varlistentry>
+  </variablelist>
+ </refsect1>
+
+ <refsect1>
+  <title>Notes</title>
+
+  <para>
+   The procedural language to be used must already have been installed
+   into the current database by means of <command>CREATE LANGUAGE</>.
+  </para>
+
+  <para>
+   The user must have <literal>USAGE</> privilege for the procedural
+   language, or must be a superuser if the language is untrusted.
+   This is the same privilege requirement as for creating a function
+   in the language.
+  </para>
+ </refsect1>
+
+ <refsect1 id="sql-do-examples">
+  <title id="sql-do-examples-title">Examples</title>
+  <para>
+   Execute a simple PL/pgsql loop without needing to create a function:
+<programlisting>
+DO $$
+DECLARE r record;
+BEGIN
+    FOR r IN SELECT rtrim(roomno) AS roomno, comment FROM Room ORDER BY roomno
+    LOOP
+        RAISE NOTICE '%, %', r.roomno, r.comment;
+    END LOOP;
+END$$;
+</programlisting>
+  </para>
+ </refsect1>
+ <refsect1>
+  <title>Compatibility</title>
+
+  <para>
+   There is no <command>DO</command> statement in the SQL standard.
+  </para>
+ </refsect1>
+
+ <refsect1>
+  <title>See Also</title>
+
+  <simplelist type="inline">
+   <member><xref linkend="sql-createlanguage" endterm="sql-createlanguage-title"></member>
+  </simplelist>
+ </refsect1>
+</refentry>
diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml
index d3a862959d9..48f8040541d 100644
--- a/doc/src/sgml/reference.sgml
+++ b/doc/src/sgml/reference.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/reference.sgml,v 1.67 2008/12/19 16:25:16 petere Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/reference.sgml,v 1.68 2009/09/22 23:43:37 tgl Exp $ -->
 
 <part id="reference">
  <title>Reference</title>
@@ -105,6 +105,7 @@
    &declare;
    &delete;
    &discard;
+   &do;
    &dropAggregate;
    &dropCast;
    &dropConversion;
diff --git a/doc/src/sgml/xplang.sgml b/doc/src/sgml/xplang.sgml
index 9882d835e35..b48b78f95ba 100644
--- a/doc/src/sgml/xplang.sgml
+++ b/doc/src/sgml/xplang.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/xplang.sgml,v 1.34 2007/02/01 00:28:18 momjian Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/xplang.sgml,v 1.35 2009/09/22 23:43:37 tgl Exp $ -->
 
  <chapter id="xplang">
   <title id="xplang-title">Procedural Languages</title>
@@ -75,9 +75,9 @@ createlang plpgsql template1
     </title>
 
     <para>
-     A procedural language is installed in a database in four steps,
+     A procedural language is installed in a database in five steps,
      which must be carried out by a database superuser.  (For languages
-     known to <command>CREATE LANGUAGE</>, the second and third steps
+     known to <command>CREATE LANGUAGE</>, the second through fourth steps
      can be omitted, because they will be carried out automatically
      if needed.)
     </para>
@@ -111,11 +111,27 @@ CREATE FUNCTION <replaceable>handler_function_name</replaceable>()
 
     <step performance="optional" id="xplang-install-cr3">
      <para>
+      Optionally, the language handler can provide an <quote>inline</>
+      handler function that executes anonymous code blocks
+      (<xref linkend="sql-do" endterm="sql-do-title"> commands)
+      written in this language.  If an inline handler function
+      is provided by the language, declare it with a command like
+<synopsis>
+CREATE FUNCTION <replaceable>inline_function_name</replaceable>(internal)
+    RETURNS void
+    AS '<replaceable>path-to-shared-object</replaceable>'
+    LANGUAGE C;
+</synopsis>
+     </para>
+    </step>
+
+    <step performance="optional" id="xplang-install-cr4">
+     <para>
       Optionally, the language handler can provide a <quote>validator</>
       function that checks a function definition for correctness without
       actually executing it.  The validator function is called by
       <command>CREATE FUNCTION</> if it exists.  If a validator function
-      is provided by the handler, declare it with a command like
+      is provided by the language, declare it with a command like
 <synopsis>
 CREATE FUNCTION <replaceable>validator_function_name</replaceable>(oid)
     RETURNS void
@@ -125,12 +141,13 @@ CREATE FUNCTION <replaceable>validator_function_name</replaceable>(oid)
      </para>
     </step>
 
-    <step performance="required" id="xplang-install-cr4">
+    <step performance="required" id="xplang-install-cr5">
      <para>
       The PL must be declared with the command
 <synopsis>
 CREATE <optional>TRUSTED</optional> <optional>PROCEDURAL</optional> LANGUAGE <replaceable>language-name</replaceable>
     HANDLER <replaceable>handler_function_name</replaceable>
+    <optional>INLINE <replaceable>inline_function_name</replaceable></optional>
     <optional>VALIDATOR <replaceable>validator_function_name</replaceable></optional> ;
 </synopsis>
       The optional key word <literal>TRUSTED</literal> specifies that
@@ -173,10 +190,13 @@ CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler AS
      </para>
 
      <para>
-      <application>PL/pgSQL</application> has a validator function,
-      so we declare that too:
+      <application>PL/pgSQL</application> has an inline handler function
+      and a validator function, so we declare those too:
 
 <programlisting>
+CREATE FUNCTION plpgsql_inline_handler(internal) RETURNS void AS
+    '$libdir/plpgsql' LANGUAGE C;
+
 CREATE FUNCTION plpgsql_validator(oid) RETURNS void AS
     '$libdir/plpgsql' LANGUAGE C;
 </programlisting>
@@ -187,6 +207,7 @@ CREATE FUNCTION plpgsql_validator(oid) RETURNS void AS
 <programlisting>
 CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql
     HANDLER plpgsql_call_handler
+    INLINE plpgsql_inline_handler
     VALIDATOR plpgsql_validator;
 </programlisting>
       then defines that the previously declared functions