8000 Implement the DO statement to support execution of PL code without ha… · postgrespro/postgres_cluster@9048b73 · GitHub
[go: up one dir, main page]
Skip to content

Commit 9048b73

Browse files
tglsfdctglsfdc
committed
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
1 parent d5a43ff commit 9048b73

File tree

34 files changed

+970
-140
lines changed
  • nodes
  • parser
  • tcop
  • utils/misc
  • bin
  • include
  • interfaces/ecpg/preproc
  • pl/plpgsql/src
  • test/regress

    • 34 files changed

    +970
    -140
    lines changed

    ‎doc/src/sgml/catalogs.sgml

    Lines changed: 19 additions & 1 deletion
    Original file line numberDiff line numberDiff line change
    @@ -1,4 +1,4 @@
    1-
    <!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.206 2009/08/10 22:13:50 alvherre Exp $ -->
    1+
    <!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.207 2009/09/22 23:43:37 tgl Exp $ -->
    22
    <!--
    33
    Documentation of the system catalogs, directed toward PostgreSQL developers
    44
    -->
    @@ -2941,6 +2941,18 @@
    29412941
    </entry>
    29422942
    </row>
    29432943

    2944+
    <row>
    2945+
    <entry><structfield>laninline</structfield></entry>
    2946+
    <entry><type>oid</type></entry>
    2947+
    <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
    2948+
    <entry>
    2949+
    This references a function that is responsible for executing
    2950+
    <quote>inline</> anonymous code blocks
    2951+
    (<xref linkend="sql-do" endterm="sql-do-title"> blocks).
    2952+
    Zero if inline blocks are not supported
    2953+
    </entry>
    2954+
    </row>
    2955+
    29442956
    <row>
    29452957
    <entry><structfield>lanvalidator</structfield></entry>
    29462958
    <entry><type>oid</type></entry>
    @@ -3547,6 +3559,12 @@
    35473559
    <entry>Name of call handler function</entry>
    35483560
    </row>
    35493561

    3562+
    <row>
    3563+
    <entry><structfield>tmplinline</structfield></entry>
    3564+
    <entry><type>text</type></entry>
    3565+
    <entry>Name of anonymous-block handler function, or NULL if none</entry>
    3566+
    </row>
    3567+
    35503568
    <row>
    35513569
    <entry><structfield>tmplvalidator</structfield></entry>
    35523570
    <entry><type>text</type></entry>

    ‎doc/src/sgml/config.sgml

    Lines changed: 16 additions & 1 deletion
    Original file line numberDiff line numberDiff line change
    @@ -1,4 +1,4 @@
    1-
    <!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.228 2009/09/13 19:52:29 petere Exp $ -->
    1+
    <!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.229 2009/09/22 23:43:37 tgl Exp $ -->
    22

    33
    <chapter Id="runtime-config">
    44
    <title>Server Configuration</title>
    @@ -3964,6 +3964,21 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
    39643964
    </listitem>
    39653965
    </varlistentry>
    39663966

    3967+
    <varlistentry id="guc-default-do-language" xreflabel="default_do_language">
    3968+
    <term><varname>default_do_language</varname> (<type>string</type>)</term>
    3969+
    <indexterm>
    3970+
    <primary><varname>default_do_language</> configuration parameter</primary>
    3971+
    </indexterm>
    3972+
    <listitem>
    3973+
    <para>
    3974+
    This parameter specifies the language to use when the
    3975+
    <literal>LANGUAGE</> option is omitted in a
    3976+
    <xref linkend="sql-do" endterm="sql-do-title"> statement.
    3977+
    The default is <literal>plpgsql</literal>.
    3978+
    </para>
    3979+
    </listitem>
    3980+
    </varlistentry>
    3981+
    39673982
    <varlistentry id="guc-default-transaction-isolation" xreflabel="default_transaction_isolation">
    39683983
    <indexterm>
    39693984
    <primary>transaction isolation level</primary>

    ‎doc/src/sgml/keywords.sgml

    Lines changed: 9 additions & 9 deletions
    Original file line numberDiff line numberDiff line change
    @@ -1,4 +1,4 @@
    1-
    <!-- $PostgreSQL: pgsql/doc/src/sgml/keywords.sgml,v 2.25 2009/04/06 15:01:36 tgl Exp $ -->
    1+
    <!-- $PostgreSQL: pgsql/doc/src/sgml/keywords.sgml,v 2.26 2009/09/22 23:43:37 tgl Exp $ -->
    22

    33
    <appendix id="sql-keywords-appendix">
    44
    <title><acronym>SQL</acronym> Key Words</title>
    @@ -2375,6 +2375,14 @@
    23752375
    <entry>reserved</entry>
    23762376
    <entry>reserved</entry>
    23772377
    </row>
    2378+
    <row>
    2379+
    <entry><token>INLINE</token></entry>
    2380+
    <entry>non-reserved</entry>
    2381+
    <entry></entry>
    2382+
    <entry></entry>
    2383+
    <entry></entry>
    2384+
    <entry></entry>
    2385+
    </row>
    23782386
    <row>
    23792387
    <entry><token>INNER</token></entry>
    23802388
    <entry>reserved (can be function or type)</entry>
    @@ -2575,14 +2583,6 @@
    25752583
    <entry></entry>
    25762584
    <entry></entry>
    25772585
    </row>
    2578-
    <row>
    2579-
    <entry><token>LANCOMPILER</token></entry>
    2580-
    <entry>non-reserved</entry>
    2581-
    <entry></entry>
    2582-
    <entry></entry>
    2583-
    <entry></entry>
    2584-
    <entry></entry>
    2585-
    </row>
    25862586
    <row>
    25872587
    <entry><token>LANGUAGE</token></entry>
    25882588
    <entry>non-reserved</entry>

    ‎doc/src/sgml/ref/allfiles.sgml

    Lines changed: 2 additions & 1 deletion
    Original file line numberDiff line numberDiff line change
    @@ -1,5 +1,5 @@
    11
    <!--
    2-
    $PostgreSQL: pgsql/doc/src/sgml/ref/allfiles.sgml,v 1.74 2008/12/19 16:25:16 petere Exp $
    2+
    $PostgreSQL: pgsql/doc/src/sgml/ref/allfiles.sgml,v 1.75 2009/09/22 23:43:37 tgl Exp $
    33
    PostgreSQL documentation
    44
    Complete list of usable sgml source files in this directory.
    55
    -->
    @@ -77,6 +77,7 @@ Complete list of usable sgml source files in this directory.
    7777
    <!entity declare system "declare.sgml">
    7878
    <!entity delete system "delete.sgml">
    7979
    <!entity discard system "discard.sgml">
    80+
    <!entity do system "do.sgml">
    8081
    <!entity dropAggregate system "drop_aggregate.sgml">
    8182
    <!entity dropCast system "drop_cast.sgml">
    8283
    <!entity dropConversion system "drop_conversion.sgml">

    ‎doc/src/sgml/ref/create_language.sgml

    Lines changed: 27 additions & 5 deletions
    Original file line numberDiff line numberDiff line change
    @@ -1,5 +1,5 @@
    11
    <!--
    2-
    $PostgreSQL: pgsql/doc/src/sgml/ref/create_language.sgml,v 1.45 2008/11/14 10:22:46 petere Exp $
    2+
    $PostgreSQL: pgsql/doc/src/sgml/ref/create_language.sgml,v 1.46 2009/09/22 23:43:37 tgl Exp $
    33
    PostgreSQL documentation
    44
    -->
    55

    @@ -23,7 +23,7 @@ PostgreSQL documentation
    2323
    <synopsis>
    2424
    CREATE [ PROCEDURAL ] LANGUAGE <replaceable class="parameter">name</replaceable>
    2525
    CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE <replaceable class="parameter">name</replaceable>
    26-
    HANDLER <replaceable class="parameter">call_handler</replaceable> [ VALIDATOR <replaceable>valfunction</replaceable> ]
    26+
    HANDLER <replaceable class="parameter">call_handler</replaceable> [ INLINE <replaceable class="parameter">inline_handler</replaceable> ] [ VALIDATOR <replaceable>valfunction</replaceable> ]
    2727
    </synopsis>
    2828
    </refsynopsisdiv>
    2929

    @@ -133,7 +133,7 @@ CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE <replaceable class="parameter">name</
    133133
    <para>
    134134
    <replaceable class="parameter">call_handler</replaceable> is
    135135
    the name of a previously registered function that will be
    136-
    called to execute the procedural language functions. The call
    136+
    called to execute the procedural language's functions. The call
    137137
    handler for a procedural language must be written in a compiled
    138138
    language such as C with version 1 call convention and
    139139
    registered with <productname>PostgreSQL</productname> as a
    @@ -144,6 +144,27 @@ CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE <replaceable class="parameter">name</
    144144
    </listitem>
    145145
    </varlistentry>
    146146

    147+
    <varlistentry>
    148+
    <term><literal>INLINE</literal> <replaceable class="parameter">inline_handler</replaceable></term>
    149+
    150+
    <listitem>
    151+
    <para>
    152+
    <replaceable class="parameter">inline_handler</replaceable> is the
    153+
    name of a previously registered function that will be called
    154+
    to execute an anonymous code block
    155+
    (<xref linkend="sql-do" endterm="sql-do-title"> command)
    156+
    in this language.
    157+
    If no <replaceable class="parameter">inline_handler</replaceable>
    158+
    function is specified, the language does not support anonymous code
    159+
    blocks.
    160+
    The handler function must take one argument of
    161+
    type <type>internal</type>, which will be the <command>DO</> command's
    162+
    internal representation, and it will typically return
    163+
    <type>void</>. The return value of the handler is ignored.
    164+
    </para>
    165+
    </listitem>
    166+
    </varlistentry>
    167+
    147168
    <varlistentry>
    148169
    <term><literal>VALIDATOR</literal> <replaceable class="parameter">valfunction</replaceable></term>
    149170

    @@ -216,7 +237,8 @@ CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE <replaceable class="parameter">name</
    216237
    </para>
    217238

    218239
    <para>
    219-
    The call handler function and the validator function (if any)
    240+
    The call handler function, the inline handler function (if any),
    241+
    and the validator function (if any)
    220242
    must already exist if the server does not have an entry for the language
    221243
    in <structname>pg_pltemplate</>. But when there is an entry,
    222244
    the functions need not already exist;
    @@ -230,7 +252,7 @@ CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE <replaceable class="parameter">name</
    230252
    In <productname>PostgreSQL</productname> versions before 7.3, it was
    231253
    necessary to declare handler functions as returning the placeholder
    232254
    type <type>opaque</>, rather than <type>language_handler</>.
    233-
    To support loading
    255+
    To support loading
    234256
    of old dump files, <command>CREATE LANGUAGE</> will accept a function
    235257
    declared as returning <type>opaque</>, but it will issue a notice and
    236258
    change the function's declared return type to <type>language_handler</>.

    ‎doc/src/sgml/ref/do.sgml

    Lines changed: 122 additions & 0 deletions
    Original file line numberDiff line numberDiff line change
    @@ -0,0 +1,122 @@
    1+
    <!--
    2+
    $PostgreSQL: pgsql/doc/src/sgml/ref/do.sgml,v 1.1 2009/09/22 23:43:37 tgl Exp $
    3+
    PostgreSQL documentation
    4+
    -->
    5+
    6+
    <refentry id="SQL-DO">
    7+
    <refmeta>
    8+
    <refentrytitle id="sql-do-title">DO</refentrytitle>
    9+
    <manvolnum>7</manvolnum>
    10+
    <refmiscinfo>SQL - Language Statements</refmiscinfo>
    11+
    </refmeta>
    12+
    13+
    <refnamediv>
    14+
    <refname>DO</refname>
    15+
    <refpurpose>execute an anonymous code block</refpurpose>
    16+
    </refnamediv>
    17+
    18+
    <indexterm zone="sql-do">
    19+
    <primary>DO</primary>
    20+
    </indexterm>
    21+
    22+
    <indexterm zone="sql-do">
    23+
    <primary>anonymous code blocks</primary>
    24+
    </indexterm>
    25+
    26+
    <refsynopsisdiv>
    27+
    <synopsis>
    28+
    DO <replaceable class="PARAMETER">code</replaceable> [ LANGUAGE <replaceable class="PARAMETER">lang_name</replaceable> ]
    29+
    </synopsis>
    30+
    </refsynopsisdiv>
    31+
    32+
    <refsect1>
    33+
    <title>Description</title>
    34+
    35+
    <para>
    36+
    <command>DO</command> executes an anonymous code block, or in other
    37+
    words a transient anonymous function in a procedural language.
    38+
    </para>
    39+
    40+
    <para>
    41+
    The code block is treated as though it were the body of a function
    42+
    with no parameters, returning <type>void</>. It is parsed and
    43+
    executed a single time.
    44+
    </para>
    45+
    </refsect1>
    46+
    47+
    <refsect1>
    48+
    <title>Parameters</title>
    49+
    50+
    <variablelist>
    51+
    <varlistentry>
    52+
    <term><replaceable class="PARAMETER">code</replaceable></term>
    53+
    <listitem>
    54+
    <para>
    55+
    The procedural language code to be executed. This must be specified
    56+
    as a string literal, just as in <command>CREATE FUNCTION</>.
    57+
    Use of a dollar-quoted literal is recommended.
    58+
    </para>
    59+
    </listitem>
    60+
    </varlistentry>
    61+
    62+
    <varlistentry>
    63+
    <term><replaceable class="PARAMETER">lang_name</replaceable></term>
    64+
    <listitem>
    65+
    <para>
    66+
    The name of the procedural language the code is written in.
    67+
    If omitted, the default is determined by the runtime parameter
    68+
    <xref linkend="guc-default-do-language">.
    69+
    </para>
    70+
    </listitem>
    71+
    </varlistentry>
    72+
    </variablelist>
    73+
    </refsect1>
    74+
    75+
    <refsect1>
    76+
    <title>Notes</title>
    77+
    78+
    <para>
    79+
    The procedural language to be used must already have been installed
    80+
    into the current database by means of <command>CREATE LANGUAGE</>.
    81+
    </para>
    82+
    83+
    <para>
    84+
    The user must have <literal>USAGE</> privilege for the procedural
    85+
    language, or must be a superuser if the language is untrusted.
    86+
    This is the same privilege requirement as for creating a function
    87+
    in the language.
    88+
    </para>
    89+
    </refsect1>
    90+
    91+
    <refsect1 id="sql-do-examples">
    92+
    <title id="sql-do-examples-title">Examples</title>
    93+
    <para>
    94+
    Execute a simple PL/pgsql loop without needing to create a function:
    95+
    <programlisting>
    96+
    DO $$
    97+
    DECLARE r record;
    98+
    BEGIN
    99+
    FOR r IN SELECT rtrim(roomno) AS roomno, comment FROM Room ORDER BY roomno
    100+
    LOOP
    101+
    RAISE NOTICE '%, %', r.roomno, r.comment;
    102+
    END LOOP;
    103+
    END$$;
    104+
    </programlisting>
    105+
    </para>
    106+
    </refsect1>
    107+
    <refsect1>
    108+
    <title>Compatibility</title>
    109+
    110+
    <para>
    111+
    There is no <command>DO</command> statement in the SQL standard.
    112+
    </para>
    113+
    </refsect1>
    114+
    115+
    <refsect1>
    116+
    <title>See Also</title>
    117+
    118+
    <simplelist type="inline">
    119+
    <member><xref linkend="sql-createlanguage" endterm="sql-createlanguage-title"></member>
    120+
    </simplelist>
    121+
    </refsect1>
    122+
    </refentry>

    ‎doc/src/sgml/reference.sgml

    Lines changed: 2 additions & 1 deletion
    Original file line numberDiff line numberDiff line change
    @@ -1,4 +1,4 @@
    1-
    <!-- $PostgreSQL: pgsql/doc/src/sgml/reference.sgml,v 1.67 2008/12/19 16:25:16 petere Exp $ -->
    1+
    <!-- $PostgreSQL: pgsql/doc/src/sgml/reference.sgml,v 1.68 2009/09/22 23:43:37 tgl Exp $ -->
    22

    33
    <part id="reference">
    44
    <title>Reference</title>
    @@ -105,6 +105,7 @@
    105105
    &declare;
    106106
    &delete;
    107107
    &discard;
    108+
    &do;
    108109
    &dropAggregate;
    109110
    &dropCast;
    110111
    &dropConversion;

    0 commit comments

    Comments
     (0)
    0