pltcl.sgml 28.4 KB
Newer Older
1
<!-- $PostgreSQL: pgsql/doc/src/sgml/pltcl.sgml,v 2.45 2007/02/01 00:28:17 momjian Exp $ -->
2

3
 <chapter id="pltcl">
4
  <title>PL/Tcl - Tcl Procedural Language</title>
5

6 7 8 9 10 11 12 13
  <indexterm zone="pltcl">
   <primary>PL/Tcl</primary>
  </indexterm>

  <indexterm zone="pltcl">
   <primary>Tcl</primary>
  </indexterm>

14 15
  <para>
   PL/Tcl is a loadable procedural language for the
16
   <productname>PostgreSQL</productname> database system
17 18
   that enables the <ulink url="http://www.tcl.tk/">
   Tcl language</ulink> to be used to write functions and
19
   trigger procedures.
20 21 22 23
  </para>

  <!-- **** PL/Tcl overview **** -->

24
  <sect1 id="pltcl-overview">
25 26 27
   <title>Overview</title>

   <para>
Bruce Momjian's avatar
Bruce Momjian committed
28 29 30 31
    PL/Tcl offers most of the capabilities a function writer has in
    the C language, with a few restrictions, and with the addition of
    the powerful string processing libraries that are available for
    Tcl.
32 33
   </para>
   <para>
Bruce Momjian's avatar
Bruce Momjian committed
34 35 36 37 38 39 40 41
    One compelling <emphasis>good</emphasis> restriction is that
    everything is executed from within the safety of the context of a
    Tcl interpreter.  In addition to the limited command set of safe
    Tcl, only a few commands are available to access the database via
    SPI and to raise messages via <function>elog()</>.  PL/Tcl
    provides no way to access internals of the database server or to
    gain OS-level access under the permissions of the
    <productname>PostgreSQL</productname> server process, as a C
42
    function can do.  Thus, unprivileged database users can be trusted
Bruce Momjian's avatar
Bruce Momjian committed
43
    to use this language; it does not give them unlimited authority.
44 45
   </para>
   <para>
Bruce Momjian's avatar
Bruce Momjian committed
46
    The other notable implementation restriction is that Tcl functions
47
    cannot be used to create input/output functions for new data
Bruce Momjian's avatar
Bruce Momjian committed
48
    types.
49 50
   </para>
   <para>
51
    Sometimes it is desirable to write Tcl functions that are not restricted
Peter Eisentraut's avatar
Peter Eisentraut committed
52 53
    to safe Tcl.  For example, one might want a Tcl function that sends
    email.  To handle these cases, there is a variant of <application>PL/Tcl</> called <literal>PL/TclU</>
54
    (for untrusted Tcl).  This is the exact same language except that a full
55
    Tcl interpreter is used.  <emphasis>If <application>PL/TclU</> is used, it must be
56
    installed as an untrusted procedural language</emphasis> so that only
57
    database superusers can create functions in it.  The writer of a <application>PL/TclU</>
58 59 60 61 62
    function must take care that the function cannot be used to do anything
    unwanted, since it will be able to do anything that could be done by
    a user logged in as the database administrator.
   </para>
   <para>
Bruce Momjian's avatar
Bruce Momjian committed
63 64 65 66 67 68
    The shared object code for the <application>PL/Tcl</> and
    <application>PL/TclU</> call handlers is automatically built and
    installed in the <productname>PostgreSQL</productname> library
    directory if Tcl support is specified in the configuration step of
    the installation procedure.  To install <application>PL/Tcl</>
    and/or <application>PL/TclU</> in a particular database, use the
Peter Eisentraut's avatar
Peter Eisentraut committed
69
    <command>createlang</command> program, for example
Tom Lane's avatar
Tom Lane committed
70 71
    <literal>createlang pltcl <replaceable>dbname</></literal> or
    <literal>createlang pltclu <replaceable>dbname</></literal>.
72 73 74 75 76
   </para>
  </sect1>

  <!-- **** PL/Tcl description **** -->

Peter Eisentraut's avatar
Peter Eisentraut committed
77
   <sect1 id="pltcl-functions">
Tom Lane's avatar
Tom Lane committed
78
    <title>PL/Tcl Functions and Arguments</title>
79 80

    <para>
81 82 83
     To create a function in the <application>PL/Tcl</> language, use
     the standard <xref linkend="sql-createfunction"
     endterm="sql-createfunction-title"> syntax:
84

Peter Eisentraut's avatar
Peter Eisentraut committed
85
<programlisting>
86
CREATE FUNCTION <replaceable>funcname</replaceable> (<replaceable>argument-types</replaceable>) RETURNS <replaceable>return-type</replaceable> AS $$
87
    # PL/Tcl function body
88
$$ LANGUAGE pltcl;
Peter Eisentraut's avatar
Peter Eisentraut committed
89
</programlisting>
90

Peter Eisentraut's avatar
Peter Eisentraut committed
91
     <application>PL/TclU</> is the same, except that the language has to be specified as
92
     <literal>pltclu</>.
Tom Lane's avatar
Tom Lane committed
93 94 95 96 97
    </para>

    <para>
     The body of the function is simply a piece of Tcl script.
     When the function is called, the argument values are passed as
Peter Eisentraut's avatar
Peter Eisentraut committed
98
     variables <literal>$1</literal> ... <literal>$<replaceable>n</replaceable></literal> to the
Tom Lane's avatar
Tom Lane committed
99
     Tcl script.  The result is returned
100
     from the Tcl code in the usual way, with a <literal>return</literal>
Peter Eisentraut's avatar
Peter Eisentraut committed
101 102 103 104 105
     statement.
    </para>

    <para>
     For example, a function
Tom Lane's avatar
Tom Lane committed
106
     returning the greater of two integer values could be defined as:
107

Peter Eisentraut's avatar
Peter Eisentraut committed
108
<programlisting>
109
CREATE FUNCTION tcl_max(integer, integer) RETURNS integer AS $$
110
    if {$1 &gt; $2} {return $1}
Tom Lane's avatar
Tom Lane committed
111
    return $2
112
$$ LANGUAGE pltcl STRICT;
Peter Eisentraut's avatar
Peter Eisentraut committed
113
</programlisting>
Tom Lane's avatar
Tom Lane committed
114

Peter Eisentraut's avatar
Peter Eisentraut committed
115 116 117
     Note the clause <literal>STRICT</>, which saves us from
     having to think about null input values: if a null value is passed, the
     function will not be called at all, but will just return a null
Tom Lane's avatar
Tom Lane committed
118 119 120 121
     result automatically.
    </para>

    <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
122 123 124 125
     In a nonstrict function,
     if the actual value of an argument is null, the corresponding
     <literal>$<replaceable>n</replaceable></literal> variable will be set to an empty string.
     To detect whether a particular argument is null, use the function
126
     <literal>argisnull</>.  For example, suppose that we wanted <function>tcl_max</function>
Peter Eisentraut's avatar
Peter Eisentraut committed
127 128
     with one null and one nonnull argument to return the nonnull
     argument, rather than null:
Tom Lane's avatar
Tom Lane committed
129

Peter Eisentraut's avatar
Peter Eisentraut committed
130
<programlisting>
131
CREATE FUNCTION tcl_max(integer, integer) RETURNS integer AS $$
Tom Lane's avatar
Tom Lane committed
132 133 134 135 136
    if {[argisnull 1]} {
        if {[argisnull 2]} { return_null }
        return $2
    }
    if {[argisnull 2]} { return $1 }
137
    if {$1 &gt; $2} {return $1}
138
    return $2
139
$$ LANGUAGE pltcl;
Peter Eisentraut's avatar
Peter Eisentraut committed
140
</programlisting>
Tom Lane's avatar
Tom Lane committed
141
    </para>
142

Tom Lane's avatar
Tom Lane committed
143 144
    <para>
     As shown above,
Peter Eisentraut's avatar
Peter Eisentraut committed
145
     to return a null value from a PL/Tcl function, execute
Tom Lane's avatar
Tom Lane committed
146 147
     <literal>return_null</literal>.  This can be done whether the
     function is strict or not.
148 149 150
    </para>

    <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
151 152 153 154 155 156 157 158 159 160 161 162
     Composite-type arguments are passed to the function as Tcl
     arrays.  The element names of the array are the attribute names
     of the composite type. If an attribute in the passed row has the
     null value, it will not appear in the array. Here is an example:

<programlisting>
CREATE TABLE employee (
    name text,
    salary integer,
    age integer
);

163
CREATE FUNCTION overpaid(employee) RETURNS boolean AS $$
164
    if {200000.0 &lt; $1(salary)} {
165 166
        return "t"
    }
167
    if {$1(age) &lt; 30 && 100000.0 &lt; $1(salary)} {
168 169 170
        return "t"
    }
    return "f"
171
$$ LANGUAGE pltcl;
Peter Eisentraut's avatar
Peter Eisentraut committed
172
</programlisting>
173 174
    </para>

Tom Lane's avatar
Tom Lane committed
175
    <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
176
     There is currently no support for returning a composite-type
177 178 179 180 181 182 183 184 185 186
     result value, nor for returning sets.
    </para>

    <para>
     <application>PL/Tcl</> does not currently have full support for
     domain types: it treats a domain the same as the underlying scalar
     type.  This means that constraints associated with the domain will
     not be enforced.  This is not an issue for function arguments, but
     it is a hazard if you declare a <application>PL/Tcl</> function
     as returning a domain type.
Tom Lane's avatar
Tom Lane committed
187 188
    </para>

Peter Eisentraut's avatar
Peter Eisentraut committed
189
   </sect1>
Tom Lane's avatar
Tom Lane committed
190

Peter Eisentraut's avatar
Peter Eisentraut committed
191
   <sect1 id="pltcl-data">
Tom Lane's avatar
Tom Lane committed
192 193 194
    <title>Data Values in PL/Tcl</title>

    <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
195
     The argument values supplied to a PL/Tcl function's code are simply
Tom Lane's avatar
Tom Lane committed
196
     the input arguments converted to text form (just as if they had been
197 198
     displayed by a <command>SELECT</> statement).  Conversely, the
     <literal>return</>
Tom Lane's avatar
Tom Lane committed
199
     command will accept any string that is acceptable input format for
200 201
     the function's declared return type.  So, within the PL/Tcl function,
     all values are just text strings.
Tom Lane's avatar
Tom Lane committed
202 203
    </para>

Peter Eisentraut's avatar
Peter Eisentraut committed
204
   </sect1>
205

Peter Eisentraut's avatar
Peter Eisentraut committed
206
   <sect1 id="pltcl-global">
207 208
    <title>Global Data in PL/Tcl</title>

Peter Eisentraut's avatar
Peter Eisentraut committed
209 210 211 212 213
    <indexterm zone="pltcl-global">
     <primary>global data</primary>
     <secondary>in PL/Tcl</secondary>
    </indexterm>

214
    <para>
Tom Lane's avatar
Tom Lane committed
215
     Sometimes it
Peter Eisentraut's avatar
Peter Eisentraut committed
216 217
     is useful to have some global data that is held between two
     calls to a function or is shared between different functions.
Tom Lane's avatar
Tom Lane committed
218
     This is easily done since
Peter Eisentraut's avatar
Peter Eisentraut committed
219
     all PL/Tcl functions executed in one session share the same
Tom Lane's avatar
Tom Lane committed
220
     safe Tcl interpreter.  So, any global Tcl variable is accessible to
Peter Eisentraut's avatar
Peter Eisentraut committed
221 222
     all PL/Tcl function calls and will persist for the duration of the
     SQL session.  (Note that <application>PL/TclU</> functions likewise share
Tom Lane's avatar
Tom Lane committed
223 224
     global data, but they are in a different Tcl interpreter and cannot
     communicate with PL/Tcl functions.)
225 226
    </para>
    <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
227
     To help protect PL/Tcl functions from unintentionally interfering
Tom Lane's avatar
Tom Lane committed
228
     with each other, a global
Peter Eisentraut's avatar
Peter Eisentraut committed
229 230 231
     array is made available to each function via the <function>upvar</>
     command. The global name of this variable is the function's internal
     name, and the local name is <literal>GD</>.  It is recommended that
Tom Lane's avatar
Tom Lane committed
232
     <literal>GD</> be used
233 234 235
     for persistent private data of a function.  Use regular Tcl global
     variables only for values that you specifically intend to be shared among
     multiple functions.
236
    </para>
Tom Lane's avatar
Tom Lane committed
237 238 239 240 241

    <para>
     An example of using <literal>GD</> appears in the
     <function>spi_execp</function> example below.
    </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
242
   </sect1>
Tom Lane's avatar
Tom Lane committed
243

Peter Eisentraut's avatar
Peter Eisentraut committed
244
   <sect1 id="pltcl-dbaccess">
Tom Lane's avatar
Tom Lane committed
245 246 247 248
    <title>Database Access from PL/Tcl</title>

    <para>
     The following commands are available to access the database from
Peter Eisentraut's avatar
Peter Eisentraut committed
249
     the body of a PL/Tcl function:
Tom Lane's avatar
Tom Lane committed
250 251 252 253

    <variablelist>

     <varlistentry>
Peter Eisentraut's avatar
Peter Eisentraut committed
254
      <term><literal><function>spi_exec</function> <optional role="tcl">-count <replaceable>n</replaceable></optional> <optional role="tcl">-array <replaceable>name</replaceable></optional> <replaceable>command</replaceable> <optional role="tcl"><replaceable>loop-body</replaceable></optional></literal></term>
Tom Lane's avatar
Tom Lane committed
255 256
      <listitem>
       <para>
257 258 259 260 261 262 263
        Executes an SQL command given as a string.  An error in the command
        causes an error to be raised.  Otherwise, the return value of <function>spi_exec</function>
        is the number of rows processed (selected, inserted, updated, or
        deleted) by the command, or zero if the command is a utility
        statement.  In addition, if the command is a <command>SELECT</> statement, the
        values of the selected columns are placed in Tcl variables as
        described below.
Tom Lane's avatar
Tom Lane committed
264 265
       </para>
       <para>
266 267 268 269
        The optional <literal>-count</> value tells
        <function>spi_exec</function> the maximum number of rows
        to process in the command.  The effect of this is comparable to
        setting up a query as a cursor and then saying <literal>FETCH <replaceable>n</></>.
Tom Lane's avatar
Tom Lane committed
270 271
       </para>
       <para>
272 273
        If the command is a <command>SELECT</> statement, the values of the
        result columns are placed into Tcl variables named after the columns.
Tom Lane's avatar
Tom Lane committed
274
        If the <literal>-array</> option is given, the column values are
275 276
        instead stored into the named associative array, with the
        column names used as array indexes.
Tom Lane's avatar
Tom Lane committed
277 278
       </para>
       <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
279
        If the command is a <command>SELECT</> statement and no <replaceable>loop-body</>
280 281 282 283
        script is given, then only the first row of results are stored into
        Tcl variables; remaining rows, if any, are ignored.  No storing occurs
        if the 
        query returns no rows.  (This case can be detected by checking the
284
        result of <function>spi_exec</function>.)  For example:
Peter Eisentraut's avatar
Peter Eisentraut committed
285
<programlisting>
Tom Lane's avatar
Tom Lane committed
286
spi_exec "SELECT count(*) AS cnt FROM pg_proc"
Peter Eisentraut's avatar
Peter Eisentraut committed
287
</programlisting>
Tom Lane's avatar
Tom Lane committed
288

289 290
        will set the Tcl variable <literal>$cnt</> to the number of rows in
        the <structname>pg_proc</> system catalog.
Tom Lane's avatar
Tom Lane committed
291 292 293
       </para>
       <para>
        If the optional <replaceable>loop-body</> argument is given, it is
294 295 296
        a piece of Tcl script that is executed once for each row in the
        query result.  (<replaceable>loop-body</> is ignored if the given
        command is not a <command>SELECT</>.)  The values of the current row's columns
297
        are stored into Tcl variables before each iteration.  For example:
Tom Lane's avatar
Tom Lane committed
298

Peter Eisentraut's avatar
Peter Eisentraut committed
299
<programlisting>
Tom Lane's avatar
Tom Lane committed
300 301 302
spi_exec -array C "SELECT * FROM pg_class" {
    elog DEBUG "have table $C(relname)"
}
Peter Eisentraut's avatar
Peter Eisentraut committed
303
</programlisting>
Tom Lane's avatar
Tom Lane committed
304

305 306 307 308
        will print a log message for every row of <literal>pg_class</>.  This
        feature works similarly to other Tcl looping constructs; in
        particular <literal>continue</> and <literal>break</> work in the
        usual way inside the loop body.
Tom Lane's avatar
Tom Lane committed
309 310
       </para>
       <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
311
        If a column of a query result is null, the target
312
        variable for it is <quote>unset</> rather than being set.
Tom Lane's avatar
Tom Lane committed
313 314 315 316 317 318 319 320
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><function>spi_prepare</function> <replaceable>query</replaceable> <replaceable>typelist</replaceable></term>
      <listitem>
       <para>
321 322 323 324
        Prepares and saves a query plan for later execution.  The
        saved plan will be retained for the life of the current
        session.<indexterm><primary>preparing a query</><secondary>in
        PL/Tcl</></>
Tom Lane's avatar
Tom Lane committed
325 326
       </para>
       <para>
327
        The query can use parameters, that is, placeholders for
328 329 330 331 332 333 334 335 336
        values to be supplied whenever the plan is actually executed.
        In the query string, refer to parameters
        by the symbols <literal>$1</literal> ... <literal>$<replaceable>n</replaceable></literal>.
        If the query uses parameters, the names of the parameter types
        must be given as a Tcl list.  (Write an empty list for
        <replaceable>typelist</replaceable> if no parameters are used.)
        Presently, the parameter types must be identified by the internal
        type names shown in the system table <literal>pg_type</>; for example <literal>int4</> not
        <literal>integer</>.
Tom Lane's avatar
Tom Lane committed
337 338 339
       </para>
       <para>
        The return value from <function>spi_prepare</function> is a query ID
340 341
        to be used in subsequent calls to <function>spi_execp</function>. See
        <function>spi_execp</function> for an example.
Tom Lane's avatar
Tom Lane committed
342 343 344 345 346
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
Peter Eisentraut's avatar
Peter Eisentraut committed
347
      <term><literal><function>spi_execp</> <optional role="tcl">-count <replaceable>n</replaceable></optional> <optional role="tcl">-array <replaceable>name</replaceable></optional> <optional role="tcl">-nulls <replaceable>string</replaceable></optional> <replaceable>queryid</replaceable> <optional role="tcl"><replaceable>value-list</replaceable></optional> <optional role="tcl"><replaceable>loop-body</replaceable></optional></literal></term>
Tom Lane's avatar
Tom Lane committed
348 349
      <listitem>
       <para>
350 351 352 353 354 355 356 357
        Executes a query previously prepared with <function>spi_prepare</>.
        <replaceable>queryid</replaceable> is the ID returned by
        <function>spi_prepare</>.  If the query references parameters,
        a <replaceable>value-list</replaceable> must be supplied.  This
        is a Tcl list of actual values for the parameters.  The list must be
        the same length as the parameter type list previously given to
        <function>spi_prepare</>.  Omit <replaceable>value-list</replaceable>
        if the query has no parameters.
Tom Lane's avatar
Tom Lane committed
358 359
       </para>
       <para>
360 361 362 363 364
        The optional value for <literal>-nulls</> is a string of spaces and
        <literal>'n'</> characters telling <function>spi_execp</function>
        which of the parameters are null values. If given, it must have exactly the
        same length as the <replaceable>value-list</replaceable>.  If it
        is not given, all the parameter values are nonnull.
Tom Lane's avatar
Tom Lane committed
365 366
       </para>
       <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
367
        Except for the way in which the query and its parameters are specified,
368
        <function>spi_execp</> works just like <function>spi_exec</>.
Tom Lane's avatar
Tom Lane committed
369
        The <literal>-count</>, <literal>-array</>, and
370 371
        <replaceable>loop-body</replaceable> options are the same,
        and so is the result value.
Tom Lane's avatar
Tom Lane committed
372 373
       </para>
       <para>
374
        Here's an example of a PL/Tcl function using a prepared plan:
Tom Lane's avatar
Tom Lane committed
375

Peter Eisentraut's avatar
Peter Eisentraut committed
376
<programlisting>
377
CREATE FUNCTION t1_count(integer, integer) RETURNS integer AS $$
Tom Lane's avatar
Tom Lane committed
378 379
    if {![ info exists GD(plan) ]} {
        # prepare the saved plan on the first call
380 381
        set GD(plan) [ spi_prepare \
                "SELECT count(*) AS cnt FROM t1 WHERE num &gt;= \$1 AND num &lt;= \$2" \
Tom Lane's avatar
Tom Lane committed
382 383 384 385
                [ list int4 int4 ] ]
    }
    spi_execp -count 1 $GD(plan) [ list $1 $2 ]
    return $cnt
386
$$ LANGUAGE pltcl;
Peter Eisentraut's avatar
Peter Eisentraut committed
387
</programlisting>
Tom Lane's avatar
Tom Lane committed
388

389 390 391 392 393
        We need backslashes inside the query string given to
        <function>spi_prepare</> to ensure that the
        <literal>$<replaceable>n</replaceable></> markers will be passed
        through to <function>spi_prepare</> as-is, and not replaced by Tcl
        variable substitution.
394

Tom Lane's avatar
Tom Lane committed
395 396 397 398 399 400 401 402 403 404 405
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <indexterm>
       <primary>spi_lastoid</primary>
      </indexterm>
      <term><function>spi_lastoid</></term>
      <listitem>
       <para>
406
        Returns the OID of the row inserted by the last
407 408 409
        <function>spi_exec</> or <function>spi_execp</>, if the
        command was a single-row <command>INSERT</> and the modified
        table contained OIDs.  (If not, you get zero.)
Tom Lane's avatar
Tom Lane committed
410 411 412 413 414 415 416 417
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><function>quote</> <replaceable>string</replaceable></term>
      <listitem>
       <para>
418
        Doubles all occurrences of single quote and backslash characters
419
        in the given string.  This can be used to safely quote strings
420 421 422
        that are to be inserted into SQL commands given
        to <function>spi_exec</function> or
        <function>spi_prepare</function>.
423
        For example, think about an SQL command string like:
Tom Lane's avatar
Tom Lane committed
424 425 426 427 428

<programlisting>
"SELECT '$val' AS ret"
</programlisting>

429 430
        where the Tcl variable <literal>val</> actually contains
        <literal>doesn't</literal>. This would result
431
        in the final command string:
Tom Lane's avatar
Tom Lane committed
432 433 434 435 436

<programlisting>
SELECT 'doesn't' AS ret
</programlisting>

437 438 439
        which would cause a parse error during
        <function>spi_exec</function> or
        <function>spi_prepare</function>.
440
        To work properly, the submitted command should contain:
Tom Lane's avatar
Tom Lane committed
441 442

<programlisting>
443
SELECT 'doesn''t' AS ret
Tom Lane's avatar
Tom Lane committed
444 445
</programlisting>

446
        which can be formed in PL/Tcl using:
Tom Lane's avatar
Tom Lane committed
447 448 449 450 451 452

<programlisting>
"SELECT '[ quote $val ]' AS ret"
</programlisting>

        One advantage of <function>spi_execp</function> is that you don't
453 454
        have to quote parameter values like this, since the parameters are never
        parsed as part of an SQL command string.
Tom Lane's avatar
Tom Lane committed
455 456 457 458 459 460 461
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <indexterm>
       <primary>elog</primary>
Peter Eisentraut's avatar
Peter Eisentraut committed
462
       <secondary>in PL/Tcl</secondary>
Tom Lane's avatar
Tom Lane committed
463 464 465 466
      </indexterm>
      <term><function>elog</> <replaceable>level</replaceable> <replaceable>msg</replaceable></term>
      <listitem>
       <para>
467 468 469
        Emits a log or error message. Possible levels are
        <literal>DEBUG</>, <literal>LOG</>, <literal>INFO</>,
        <literal>NOTICE</>, <literal>WARNING</>, <literal>ERROR</>, and
470
        <literal>FATAL</>. <literal>ERROR</>
471 472 473 474 475 476 477
        raises an error condition; if this is not trapped by the surrounding
        Tcl code, the error propagates out to the calling query, causing
        the current transaction or subtransaction to be aborted.  This
        is effectively the same as the Tcl <literal>error</> command.
        <literal>FATAL</> aborts the transaction and causes the current
        session to shut down.  (There is probably no good reason to use
        this error level in PL/Tcl functions, but it's provided for
478 479 480 481 482 483 484 485
        completeness.)  The other levels only generate messages of different
        priority levels.
        Whether messages of a particular priority are reported to the client,
        written to the server log, or both is controlled by the
        <xref linkend="guc-log-min-messages"> and
        <xref linkend="guc-client-min-messages"> configuration
        variables. See <xref linkend="runtime-config"> for more
        information.
Tom Lane's avatar
Tom Lane committed
486 487 488 489 490
       </para>
      </listitem>
     </varlistentry>

    </variablelist>
Peter Eisentraut's avatar
Peter Eisentraut committed
491
    </para>
Tom Lane's avatar
Tom Lane committed
492

Peter Eisentraut's avatar
Peter Eisentraut committed
493
   </sect1>
494

Peter Eisentraut's avatar
Peter Eisentraut committed
495
   <sect1 id="pltcl-trigger">
496 497
    <title>Trigger Procedures in PL/Tcl</title>

498
    <indexterm>
Peter Eisentraut's avatar
Peter Eisentraut committed
499
     <primary>trigger</primary>
500 501 502
     <secondary>in PL/Tcl</secondary>
    </indexterm>

503
    <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
504 505
     Trigger procedures can be written in PL/Tcl.
     <productname>PostgreSQL</productname> requires that a procedure that is to be called
Tom Lane's avatar
Tom Lane committed
506
     as a trigger must be declared as a function with no arguments
507
     and a return type of <literal>trigger</>.
508 509
    </para>
    <para>
Tom Lane's avatar
Tom Lane committed
510
     The information from the trigger manager is passed to the procedure body
511 512 513 514 515
     in the following variables:

     <variablelist>

      <varlistentry>
Peter Eisentraut's avatar
Peter Eisentraut committed
516
       <term><varname>$TG_name</varname></term>
517
       <listitem>
518 519 520
        <para>
         The name of the trigger from the <command>CREATE TRIGGER</command> statement.
        </para>
521 522 523 524
       </listitem>
      </varlistentry>

      <varlistentry>
Peter Eisentraut's avatar
Peter Eisentraut committed
525
       <term><varname>$TG_relid</varname></term>
526
       <listitem>
527 528 529 530
        <para>
         The object ID of the table that caused the trigger procedure
         to be invoked.
        </para>
531 532 533
       </listitem>
      </varlistentry>

534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553
      <varlistentry>
       <term><varname>$TG_table_name</varname></term>
       <listitem>
        <para>
         The name of the table that caused the trigger procedure
         to be invoked.
        </para>
       </listitem>
      </varlistentry>

      <varlistentry>
       <term><varname>$TG_table_schema</varname></term>
       <listitem>
        <para>
         The schema of the table that caused the trigger procedure
         to be invoked.
        </para>
       </listitem>
      </varlistentry>

554
      <varlistentry>
Peter Eisentraut's avatar
Peter Eisentraut committed
555
       <term><varname>$TG_relatts</varname></term>
556
       <listitem>
557 558
        <para>
         A Tcl list of the table column names, prefixed with an empty list
Peter Eisentraut's avatar
Peter Eisentraut committed
559
         element. So looking up a column name in the list with <application>Tcl</>'s
Tom Lane's avatar
Tom Lane committed
560
         <function>lsearch</> command returns the element's number starting
561 562 563 564 565 566
         with 1 for the first column, the same way the columns are customarily
         numbered in <productname>PostgreSQL</productname>.  (Empty list
         elements also appear in the positions of columns that have been
         dropped, so that the attribute numbering is correct for columns
         to their right.)
        </para>
567 568 569 570
       </listitem>
      </varlistentry>

      <varlistentry>
Peter Eisentraut's avatar
Peter Eisentraut committed
571
       <term><varname>$TG_when</varname></term>
572
       <listitem>
573 574 575 576
        <para>
         The string <literal>BEFORE</> or <literal>AFTER</> depending on the
         type of trigger call.
        </para>
577 578 579 580
       </listitem>
      </varlistentry>

      <varlistentry>
Peter Eisentraut's avatar
Peter Eisentraut committed
581
       <term><varname>$TG_level</varname></term>
582
       <listitem>
583 584 585 586
        <para>
         The string <literal>ROW</> or <literal>STATEMENT</> depending on the
         type of trigger call.
        </para>
587 588 589 590
       </listitem>
      </varlistentry>

      <varlistentry>
Peter Eisentraut's avatar
Peter Eisentraut committed
591
       <term><varname>$TG_op</varname></term>
592
       <listitem>
593 594 595 596
        <para>
         The string <literal>INSERT</>, <literal>UPDATE</>, or
         <literal>DELETE</> depending on the type of trigger call.
        </para>
597 598 599 600
       </listitem>
      </varlistentry>

      <varlistentry>
Peter Eisentraut's avatar
Peter Eisentraut committed
601
       <term><varname>$NEW</varname></term>
602
       <listitem>
603 604 605 606 607 608
        <para>
         An associative array containing the values of the new table
         row for <command>INSERT</> or <command>UPDATE</> actions, or
         empty for <command>DELETE</>.  The array is indexed by column
         name.  Columns that are null will not appear in the array.
        </para>
609 610 611 612
       </listitem>
      </varlistentry>

      <varlistentry>
Peter Eisentraut's avatar
Peter Eisentraut committed
613
       <term><varname>$OLD</varname></term>
614
       <listitem>
615 616 617 618 619 620
        <para>
         An associative array containing the values of the old table
         row for <command>UPDATE</> or <command>DELETE</> actions, or
         empty for <command>INSERT</>.  The array is indexed by column
         name.  Columns that are null will not appear in the array.
        </para>
621 622 623 624
       </listitem>
      </varlistentry>

      <varlistentry>
Peter Eisentraut's avatar
Peter Eisentraut committed
625
       <term><varname>$args</varname></term>
626
       <listitem>
627 628 629 630 631
        <para>
         A Tcl list of the arguments to the procedure as given in the
         <command>CREATE TRIGGER</command> statement. These arguments are also accessible as
         <literal>$1</literal> ... <literal>$<replaceable>n</replaceable></literal> in the procedure body.
        </para>
632 633 634 635 636 637 638
       </listitem>
      </varlistentry>

     </variablelist>
    </para>

    <para>
Tom Lane's avatar
Tom Lane committed
639 640 641
     The return value from a trigger procedure can be one of the strings
     <literal>OK</> or <literal>SKIP</>, or a list as returned by the
     <literal>array get</> Tcl command. If the return value is <literal>OK</>,
Peter Eisentraut's avatar
Peter Eisentraut committed
642
     the operation (<command>INSERT</>/<command>UPDATE</>/<command>DELETE</>) that fired the trigger will proceed
Tom Lane's avatar
Tom Lane committed
643 644 645
     normally. <literal>SKIP</> tells the trigger manager to silently suppress
     the operation for this row. If a list is returned, it tells PL/Tcl to
     return a modified row to the trigger manager that will be inserted
Peter Eisentraut's avatar
Peter Eisentraut committed
646 647 648
     instead of the one given in <varname>$NEW</>.  (This works for <command>INSERT</> and <command>UPDATE</>
     only.) Needless to say that all this is only meaningful when the trigger
     is <literal>BEFORE</> and <command>FOR EACH ROW</>; otherwise the return value is ignored.
649 650 651
    </para>
    <para>
     Here's a little example trigger procedure that forces an integer value
652 653
     in a table to keep track of the number of updates that are performed on the
     row. For new rows inserted, the value is initialized to 0 and then
Peter Eisentraut's avatar
Peter Eisentraut committed
654
     incremented on every update operation.
655

Peter Eisentraut's avatar
Peter Eisentraut committed
656
<programlisting>
657
CREATE FUNCTION trigfunc_modcount() RETURNS trigger AS $$
658 659 660 661 662 663 664 665 666 667 668 669 670
    switch $TG_op {
        INSERT {
            set NEW($1) 0
        }
        UPDATE {
            set NEW($1) $OLD($1)
            incr NEW($1)
        }
        default {
            return OK
        }
    }
    return [array get NEW]
671
$$ LANGUAGE pltcl;
672

Tom Lane's avatar
Tom Lane committed
673
CREATE TABLE mytab (num integer, description text, modcnt integer);
674 675 676

CREATE TRIGGER trig_mytab_modcount BEFORE INSERT OR UPDATE ON mytab
    FOR EACH ROW EXECUTE PROCEDURE trigfunc_modcount('modcnt');
Peter Eisentraut's avatar
Peter Eisentraut committed
677
</programlisting>
678

Tom Lane's avatar
Tom Lane committed
679 680
     Notice that the trigger procedure itself does not know the column
     name; that's supplied from the trigger arguments.  This lets the
Peter Eisentraut's avatar
Peter Eisentraut committed
681
     trigger procedure be reused with different tables.
682
    </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
683
   </sect1>
684

Peter Eisentraut's avatar
Peter Eisentraut committed
685 686
   <sect1 id="pltcl-unknown">
       <title>Modules and the <function>unknown</> command</title>
687
       <para>
688 689 690 691 692 693
        PL/Tcl has support for autoloading Tcl code when used.
        It recognizes a special table, <literal>pltcl_modules</>, which
        is presumed to contain modules of Tcl code.  If this table
        exists, the module <literal>unknown</> is fetched from the table
        and loaded into the Tcl interpreter immediately after creating
        the interpreter.
694 695
       </para>
       <para>
Tom Lane's avatar
Tom Lane committed
696
        While the <literal>unknown</> module could actually contain any
697 698 699 700 701 702 703 704 705 706
        initialization script you need, it normally defines a Tcl
        <function>unknown</> procedure that is invoked whenever Tcl does
        not recognize an invoked procedure name.  <application>PL/Tcl</>'s standard version
        of this procedure tries to find a module in <literal>pltcl_modules</>
        that will define the required procedure.  If one is found, it is
        loaded into the interpreter, and then execution is allowed to
        proceed with the originally attempted procedure call.  A
        secondary table <literal>pltcl_modfuncs</> provides an index of
        which functions are defined by which modules, so that the lookup
        is reasonably quick.
707 708
       </para>
       <para>
Tom Lane's avatar
Tom Lane committed
709
        The <productname>PostgreSQL</productname> distribution includes
710 711 712 713 714 715
        support scripts to maintain these tables:
        <command>pltcl_loadmod</>, <command>pltcl_listmod</>,
        <command>pltcl_delmod</>, as well as source for the standard
        <literal>unknown</> module in <filename>share/unknown.pltcl</>.  This module
        must be loaded
        into each database initially to support the autoloading mechanism.
716 717
       </para>
       <para>
Tom Lane's avatar
Tom Lane committed
718
        The tables <literal>pltcl_modules</> and <literal>pltcl_modfuncs</>
719 720
        must be readable by all, but it is wise to make them owned and
        writable only by the database administrator.
721
       </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
722
   </sect1>
723

Peter Eisentraut's avatar
Peter Eisentraut committed
724
   <sect1 id="pltcl-procnames">
Tom Lane's avatar
Tom Lane committed
725 726 727
    <title>Tcl Procedure Names</title>

    <para>
728 729
     In <productname>PostgreSQL</productname>, the same function name can be used for
     different function definitions as long as the number of arguments or their types
Tom Lane's avatar
Tom Lane committed
730 731 732
     differ. Tcl, however, requires all procedure names to be distinct.
     PL/Tcl deals with this by making the internal Tcl procedure names contain
     the object 
Peter Eisentraut's avatar
Peter Eisentraut committed
733
     ID of the function from the system table <structname>pg_proc</> as part of their name. Thus,
Tom Lane's avatar
Tom Lane committed
734
     <productname>PostgreSQL</productname> functions with the same name
Peter Eisentraut's avatar
Peter Eisentraut committed
735
     and different argument types will be different Tcl procedures, too.  This
Tom Lane's avatar
Tom Lane committed
736 737 738 739
     is not normally a concern for a PL/Tcl programmer, but it might be visible
     when debugging.
    </para>

Peter Eisentraut's avatar
Peter Eisentraut committed
740
   </sect1>
741
 </chapter>