doc/src/sgml/pltcl.sgml

   1 <!-- $PostgreSQL$ -->
   2
   3  <chapter id="pltcl">
   4   <title>PL/Tcl - Tcl Procedural Language</title>
   5
   6   <indexterm zone="pltcl">
   7    <primary>PL/Tcl</primary>
   8   </indexterm>
   9
  10   <indexterm zone="pltcl">
  11    <primary>Tcl</primary>
  12   </indexterm>
  13
  14   <para>
  15    PL/Tcl is a loadable procedural language for the
  16    <productname>PostgreSQL</productname> database system
  17    that enables the <ulink url="http://www.tcl.tk/">
  18    Tcl language</ulink> to be used to write functions and
  19    trigger procedures.
  20   </para>
  21
  22   <!-- **** PL/Tcl overview **** -->
  23
  24   <sect1 id="pltcl-overview">
  25    <title>Overview</title>
  26
  27    <para>
  28     PL/Tcl offers most of the capabilities a function writer has in
  29     the C language, with a few restrictions, and with the addition of
  30     the powerful string processing libraries that are available for
  31     Tcl.
  32    </para>
  33    <para>
  34     One compelling <emphasis>good</emphasis> restriction is that
  35     everything is executed from within the safety of the context of a
  36     Tcl interpreter.  In addition to the limited command set of safe
  37     Tcl, only a few commands are available to access the database via
  38     SPI and to raise messages via <function>elog()</>.  PL/Tcl
  39     provides no way to access internals of the database server or to
  40     gain OS-level access under the permissions of the
  41     <productname>PostgreSQL</productname> server process, as a C
  42     function can do.  Thus, unprivileged database users can be trusted
  43     to use this language; it does not give them unlimited authority.
  44    </para>
  45    <para>
  46     The other notable implementation restriction is that Tcl functions
  47     cannot be used to create input/output functions for new data
  48     types.
  49    </para>
  50    <para>
  51     Sometimes it is desirable to write Tcl functions that are not restricted
  52     to safe Tcl.  For example, one might want a Tcl function that sends
  53     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     function must take care that the function cannot be used to do anything
  59     unwanted, since it will be able to do anything that could be done by
  60     a user logged in as the database administrator.
  61    </para>
  62    <para>
  63     The shared object code for the <application>PL/Tcl</> and
  64     <application>PL/TclU</> call handlers is automatically built and
  65     installed in the <productname>PostgreSQL</productname> library
  66     directory if Tcl support is specified in the configuration step of
  67     the installation procedure.  To install <application>PL/Tcl</>
  68     and/or <application>PL/TclU</> in a particular database, use the
  69     <command>createlang</command> program, for example
  70     <literal>createlang pltcl <replaceable>dbname</></literal> or
  71     <literal>createlang pltclu <replaceable>dbname</></literal>.
  72    </para>
  73   </sect1>
  74
  75   <!-- **** PL/Tcl description **** -->
  76
  77    <sect1 id="pltcl-functions">
  78     <title>PL/Tcl Functions and Arguments</title>
  79
  80     <para>
  81      To create a function in the <application>PL/Tcl</> language, use
  82      the standard <xref linkend="sql-createfunction"
  83      endterm="sql-createfunction-title"> syntax:
  84
  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;
  89 </programlisting>
  90
  91      <application>PL/TclU</> is the same, except that the language has to be specified as
  92      <literal>pltclu</>.
  93     </para>
  94
  95     <para>
  96      The body of the function is simply a piece of Tcl script.
  97      When the function is called, the argument values are passed as
  98      variables <literal>$1</literal> ... <literal>$<replaceable>n</replaceable></literal> to the
  99      Tcl script.  The result is returned
 100      from the Tcl code in the usual way, with a <literal>return</literal>
 101      statement.
 102     </para>
 103
 104     <para>
 105      For example, a function
 106      returning the greater of two integer values could be defined as:
 107
 108 <programlisting>
 109 CREATE FUNCTION tcl_max(integer, integer) RETURNS integer AS $$
 110     if {$1 &gt; $2} {return $1}
 111     return $2
 112 $$ LANGUAGE pltcl STRICT;
 113 </programlisting>
 114
 115      Note the clause <literal>STRICT</>, which saves us from
 116      having to think about null input values: if a null value is passed, the
 117      function will not be called at all, but will just return a null
 118      result automatically.
 119     </para>
 120
 121     <para>
 122      In a nonstrict function,
 123      if the actual value of an argument is null, the corresponding
 124      <literal>$<replaceable>n</replaceable></literal> variable will be set to an empty string.
 125      To detect whether a particular argument is null, use the function
 126      <literal>argisnull</>.  For example, suppose that we wanted <function>tcl_max</function>
 127      with one null and one nonnull argument to return the nonnull
 128      argument, rather than null:
 129
 130 <programlisting>
 131 CREATE FUNCTION tcl_max(integer, integer) RETURNS integer AS $$
 132     if {[argisnull 1]} {
 133         if {[argisnull 2]} { return_null }
 134         return $2
 135     }
 136     if {[argisnull 2]} { return $1 }
 137     if {$1 &gt; $2} {return $1}
 138     return $2
 139 $$ LANGUAGE pltcl;
 140 </programlisting>
 141     </para>
 142
 143     <para>
 144      As shown above,
 145      to return a null value from a PL/Tcl function, execute
 146      <literal>return_null</literal>.  This can be done whether the
 147      function is strict or not.
 148     </para>
 149
 150     <para>
 151      Composite-type arguments are passed to the function as Tcl
 152      arrays.  The element names of the array are the attribute names
 153      of the composite type. If an attribute in the passed row has the
 154      null value, it will not appear in the array. Here is an example:
 155
 156 <programlisting>
 157 CREATE TABLE employee (
 158     name text,
 159     salary integer,
 160     age integer
 161 );
 162
 163 CREATE FUNCTION overpaid(employee) RETURNS boolean AS $$
 164     if {200000.0 &lt; $1(salary)} {
 165         return "t"
 166     }
 167     if {$1(age) &lt; 30 &amp;&amp; 100000.0 &lt; $1(salary)} {
 168         return "t"
 169     }
 170     return "f"
 171 $$ LANGUAGE pltcl;
 172 </programlisting>
 173     </para>
 174
 175     <para>
 176      There is currently no support for returning a composite-type
 177      result value, nor for returning sets.
 178     </para>
 179
 180     <para>
 181      <application>PL/Tcl</> does not currently have full support for
 182      domain types: it treats a domain the same as the underlying scalar
 183      type.  This means that constraints associated with the domain will
 184      not be enforced.  This is not an issue for function arguments, but
 185      it is a hazard if you declare a <application>PL/Tcl</> function
 186      as returning a domain type.
 187     </para>
 188
 189    </sect1>
 190
 191    <sect1 id="pltcl-data">
 192     <title>Data Values in PL/Tcl</title>
 193
 194     <para>
 195      The argument values supplied to a PL/Tcl function's code are simply
 196      the input arguments converted to text form (just as if they had been
 197      displayed by a <command>SELECT</> statement).  Conversely, the
 198      <literal>return</>
 199      command will accept any string that is acceptable input format for
 200      the function's declared return type.  So, within the PL/Tcl function,
 201      all values are just text strings.
 202     </para>
 203
 204    </sect1>
 205
 206    <sect1 id="pltcl-global">
 207     <title>Global Data in PL/Tcl</title>
 208
 209     <indexterm zone="pltcl-global">
 210      <primary>global data</primary>
 211      <secondary>in PL/Tcl</secondary>
 212     </indexterm>
 213
 214     <para>
 215      Sometimes it
 216      is useful to have some global data that is held between two
 217      calls to a function or is shared between different functions.
 218      This is easily done since
 219      all PL/Tcl functions executed in one session share the same
 220      safe Tcl interpreter.  So, any global Tcl variable is accessible to
 221      all PL/Tcl function calls and will persist for the duration of the
 222      SQL session.  (Note that <application>PL/TclU</> functions likewise share
 223      global data, but they are in a different Tcl interpreter and cannot
 224      communicate with PL/Tcl functions.)
 225     </para>
 226     <para>
 227      To help protect PL/Tcl functions from unintentionally interfering
 228      with each other, a global
 229      array is made available to each function via the <function>upvar</>
 230      command. The global name of this variable is the function's internal
 231      name, and the local name is <literal>GD</>.  It is recommended that
 232      <literal>GD</> be used
 233      for persistent private data of a function.  Use regular Tcl global
 234      variables only for values that you specifically intend to be shared among
 235      multiple functions.
 236     </para>
 237
 238     <para>
 239      An example of using <literal>GD</> appears in the
 240      <function>spi_execp</function> example below.
 241     </para>
 242    </sect1>
 243
 244    <sect1 id="pltcl-dbaccess">
 245     <title>Database Access from PL/Tcl</title>
 246
 247     <para>
 248      The following commands are available to access the database from
 249      the body of a PL/Tcl function:
 250
 251     <variablelist>
 252
 253      <varlistentry>
 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>
 255       <listitem>
 256        <para>
 257         Executes an SQL command given as a string.  An error in the command
 258         causes an error to be raised.  Otherwise, the return value of <function>spi_exec</function>
 259         is the number of rows processed (selected, inserted, updated, or
 260         deleted) by the command, or zero if the command is a utility
 261         statement.  In addition, if the command is a <command>SELECT</> statement, the
 262         values of the selected columns are placed in Tcl variables as
 263         described below.
 264        </para>
 265        <para>
 266         The optional <literal>-count</> value tells
 267         <function>spi_exec</function> the maximum number of rows
 268         to process in the command.  The effect of this is comparable to
 269         setting up a query as a cursor and then saying <literal>FETCH <replaceable>n</></>.
 270        </para>
 271        <para>
 272         If the command is a <command>SELECT</> statement, the values of the
 273         result columns are placed into Tcl variables named after the columns.
 274         If the <literal>-array</> option is given, the column values are
 275         instead stored into the named associative array, with the
 276         column names used as array indexes.
 277        </para>
 278        <para>
 279         If the command is a <command>SELECT</> statement and no <replaceable>loop-body</>
 280         script is given, then only the first row of results are stored into
 281         Tcl variables; remaining rows, if any, are ignored.  No storing occurs
 282         if the
 283         query returns no rows.  (This case can be detected by checking the
 284         result of <function>spi_exec</function>.)  For example:
 285 <programlisting>
 286 spi_exec "SELECT count(*) AS cnt FROM pg_proc"
 287 </programlisting>
 288
 289         will set the Tcl variable <literal>$cnt</> to the number of rows in
 290         the <structname>pg_proc</> system catalog.
 291        </para>
 292        <para>
 293         If the optional <replaceable>loop-body</> argument is given, it is
 294         a piece of Tcl script that is executed once for each row in the
 295         query result.  (<replaceable>loop-body</> is ignored if the given
 296         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:
 298
 299 <programlisting>
 300 spi_exec -array C "SELECT * FROM pg_class" {
 301     elog DEBUG "have table $C(relname)"
 302 }
 303 </programlisting>
 304
 305         will print a log message for every row of <literal>pg_class</>.  This
 306         feature works similarly to other Tcl looping constructs; in
 307         particular <literal>continue</> and <literal>break</> work in the
 308         usual way inside the loop body.
 309        </para>
 310        <para>
 311         If a column of a query result is null, the target
 312         variable for it is <quote>unset</> rather than being set.
 313        </para>
 314       </listitem>
 315      </varlistentry>
 316
 317      <varlistentry>
 318       <term><function>spi_prepare</function> <replaceable>query</replaceable> <replaceable>typelist</replaceable></term>
 319       <listitem>
 320        <para>
 321         Prepares and saves a query plan for later execution.  The
 322         saved plan will be retained for the life of the current
 323         session.<indexterm><primary>preparing a query</><secondary>in
 324         PL/Tcl</></>
 325        </para>
 326        <para>
 327         The query can use parameters, that is, placeholders for
 328         values to be supplied whenever the plan is actually executed.
 329         In the query string, refer to parameters
 330         by the symbols <literal>$1</literal> ... <literal>$<replaceable>n</replaceable></literal>.
 331         If the query uses parameters, the names of the parameter types
 332         must be given as a Tcl list.  (Write an empty list for
 333         <replaceable>typelist</replaceable> if no parameters are used.)
 334        </para>
 335        <para>
 336         The return value from <function>spi_prepare</function> is a query ID
 337         to be used in subsequent calls to <function>spi_execp</function>. See
 338         <function>spi_execp</function> for an example.
 339        </para>
 340       </listitem>
 341      </varlistentry>
 342
 343      <varlistentry>
 344       <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>
 345       <listitem>
 346        <para>
 347         Executes a query previously prepared with <function>spi_prepare</>.
 348         <replaceable>queryid</replaceable> is the ID returned by
 349         <function>spi_prepare</>.  If the query references parameters,
 350         a <replaceable>value-list</replaceable> must be supplied.  This
 351         is a Tcl list of actual values for the parameters.  The list must be
 352         the same length as the parameter type list previously given to
 353         <function>spi_prepare</>.  Omit <replaceable>value-list</replaceable>
 354         if the query has no parameters.
 355        </para>
 356        <para>
 357         The optional value for <literal>-nulls</> is a string of spaces and
 358         <literal>'n'</> characters telling <function>spi_execp</function>
 359         which of the parameters are null values. If given, it must have exactly the
 360         same length as the <replaceable>value-list</replaceable>.  If it
 361         is not given, all the parameter values are nonnull.
 362        </para>
 363        <para>
 364         Except for the way in which the query and its parameters are specified,
 365         <function>spi_execp</> works just like <function>spi_exec</>.
 366         The <literal>-count</>, <literal>-array</>, and
 367         <replaceable>loop-body</replaceable> options are the same,
 368         and so is the result value.
 369        </para>
 370        <para>
 371         Here's an example of a PL/Tcl function using a prepared plan:
 372
 373 <programlisting>
 374 CREATE FUNCTION t1_count(integer, integer) RETURNS integer AS $$
 375     if {![ info exists GD(plan) ]} {
 376         # prepare the saved plan on the first call
 377         set GD(plan) [ spi_prepare \
 378                 "SELECT count(*) AS cnt FROM t1 WHERE num &gt;= \$1 AND num &lt;= \$2" \
 379                 [ list int4 int4 ] ]
 380     }
 381     spi_execp -count 1 $GD(plan) [ list $1 $2 ]
 382     return $cnt
 383 $$ LANGUAGE pltcl;
 384 </programlisting>
 385
 386         We need backslashes inside the query string given to
 387         <function>spi_prepare</> to ensure that the
 388         <literal>$<replaceable>n</replaceable></> markers will be passed
 389         through to <function>spi_prepare</> as-is, and not replaced by Tcl
 390         variable substitution.
 391
 392        </para>
 393       </listitem>
 394      </varlistentry>
 395
 396      <varlistentry>
 397       <indexterm>
 398        <primary>spi_lastoid</primary>
 399       </indexterm>
 400       <term><function>spi_lastoid</></term>
 401       <listitem>
 402        <para>
 403         Returns the OID of the row inserted by the last
 404         <function>spi_exec</> or <function>spi_execp</>, if the
 405         command was a single-row <command>INSERT</> and the modified
 406         table contained OIDs.  (If not, you get zero.)
 407        </para>
 408       </listitem>
 409      </varlistentry>
 410
 411      <varlistentry>
 412       <term><function>quote</> <replaceable>string</replaceable></term>
 413       <listitem>
 414        <para>
 415         Doubles all occurrences of single quote and backslash characters
 416         in the given string.  This can be used to safely quote strings
 417         that are to be inserted into SQL commands given
 418         to <function>spi_exec</function> or
 419         <function>spi_prepare</function>.
 420         For example, think about an SQL command string like:
 421
 422 <programlisting>
 423 "SELECT '$val' AS ret"
 424 </programlisting>
 425
 426         where the Tcl variable <literal>val</> actually contains
 427         <literal>doesn't</literal>. This would result
 428         in the final command string:
 429
 430 <programlisting>
 431 SELECT 'doesn't' AS ret
 432 </programlisting>
 433
 434         which would cause a parse error during
 435         <function>spi_exec</function> or
 436         <function>spi_prepare</function>.
 437         To work properly, the submitted command should contain:
 438
 439 <programlisting>
 440 SELECT 'doesn''t' AS ret
 441 </programlisting>
 442
 443         which can be formed in PL/Tcl using:
 444
 445 <programlisting>
 446 "SELECT '[ quote $val ]' AS ret"
 447 </programlisting>
 448
 449         One advantage of <function>spi_execp</function> is that you don't
 450         have to quote parameter values like this, since the parameters are never
 451         parsed as part of an SQL command string.
 452        </para>
 453       </listitem>
 454      </varlistentry>
 455
 456      <varlistentry>
 457       <indexterm>
 458        <primary>elog</primary>
 459        <secondary>in PL/Tcl</secondary>
 460       </indexterm>
 461       <term><function>elog</> <replaceable>level</replaceable> <replaceable>msg</replaceable></term>
 462       <listitem>
 463        <para>
 464         Emits a log or error message. Possible levels are
 465         <literal>DEBUG</>, <literal>LOG</>, <literal>INFO</>,
 466         <literal>NOTICE</>, <literal>WARNING</>, <literal>ERROR</>, and
 467         <literal>FATAL</>. <literal>ERROR</>
 468         raises an error condition; if this is not trapped by the surrounding
 469         Tcl code, the error propagates out to the calling query, causing
 470         the current transaction or subtransaction to be aborted.  This
 471         is effectively the same as the Tcl <literal>error</> command.
 472         <literal>FATAL</> aborts the transaction and causes the current
 473         session to shut down.  (There is probably no good reason to use
 474         this error level in PL/Tcl functions, but it's provided for
 475         completeness.)  The other levels only generate messages of different
 476         priority levels.
 477         Whether messages of a particular priority are reported to the client,
 478         written to the server log, or both is controlled by the
 479         <xref linkend="guc-log-min-messages"> and
 480         <xref linkend="guc-client-min-messages"> configuration
 481         variables. See <xref linkend="runtime-config"> for more
 482         information.
 483        </para>
 484       </listitem>
 485      </varlistentry>
 486
 487     </variablelist>
 488     </para>
 489
 490    </sect1>
 491
 492    <sect1 id="pltcl-trigger">
 493     <title>Trigger Procedures in PL/Tcl</title>
 494
 495     <indexterm>
 496      <primary>trigger</primary>
 497      <secondary>in PL/Tcl</secondary>
 498     </indexterm>
 499
 500     <para>
 501      Trigger procedures can be written in PL/Tcl.
 502      <productname>PostgreSQL</productname> requires that a procedure that is to be called
 503      as a trigger must be declared as a function with no arguments
 504      and a return type of <literal>trigger</>.
 505     </para>
 506     <para>
 507      The information from the trigger manager is passed to the procedure body
 508      in the following variables:
 509
 510      <variablelist>
 511
 512       <varlistentry>
 513        <term><varname>$TG_name</varname></term>
 514        <listitem>
 515         <para>
 516          The name of the trigger from the <command>CREATE TRIGGER</command> statement.
 517         </para>
 518        </listitem>
 519       </varlistentry>
 520
 521       <varlistentry>
 522        <term><varname>$TG_relid</varname></term>
 523        <listitem>
 524         <para>
 525          The object ID of the table that caused the trigger procedure
 526          to be invoked.
 527         </para>
 528        </listitem>
 529       </varlistentry>
 530
 531       <varlistentry>
 532        <term><varname>$TG_table_name</varname></term>
 533        <listitem>
 534         <para>
 535          The name of the table that caused the trigger procedure
 536          to be invoked.
 537         </para>
 538        </listitem>
 539       </varlistentry>
 540
 541       <varlistentry>
 542        <term><varname>$TG_table_schema</varname></term>
 543        <listitem>
 544         <para>
 545          The schema of the table that caused the trigger procedure
 546          to be invoked.
 547         </para>
 548        </listitem>
 549       </varlistentry>
 550
 551       <varlistentry>
 552        <term><varname>$TG_relatts</varname></term>
 553        <listitem>
 554         <para>
 555          A Tcl list of the table column names, prefixed with an empty list
 556          element. So looking up a column name in the list with <application>Tcl</>'s
 557          <function>lsearch</> command returns the element's number starting
 558          with 1 for the first column, the same way the columns are customarily
 559          numbered in <productname>PostgreSQL</productname>.  (Empty list
 560          elements also appear in the positions of columns that have been
 561          dropped, so that the attribute numbering is correct for columns
 562          to their right.)
 563         </para>
 564        </listitem>
 565       </varlistentry>
 566
 567       <varlistentry>
 568        <term><varname>$TG_when</varname></term>
 569        <listitem>
 570         <para>
 571          The string <literal>BEFORE</> or <literal>AFTER</> depending on the
 572          type of trigger event.
 573         </para>
 574        </listitem>
 575       </varlistentry>
 576
 577       <varlistentry>
 578        <term><varname>$TG_level</varname></term>
 579        <listitem>
 580         <para>
 581          The string <literal>ROW</> or <literal>STATEMENT</> depending on the
 582          type of trigger event.
 583         </para>
 584        </listitem>
 585       </varlistentry>
 586
 587       <varlistentry>
 588        <term><varname>$TG_op</varname></term>
 589        <listitem>
 590         <para>
 591          The string <literal>INSERT</>, <literal>UPDATE</>,
 592          <literal>DELETE</>, or <literal>TRUNCATE</> depending on the type of
 593          trigger event.
 594         </para>
 595        </listitem>
 596       </varlistentry>
 597
 598       <varlistentry>
 599        <term><varname>$NEW</varname></term>
 600        <listitem>
 601         <para>
 602          An associative array containing the values of the new table
 603          row for <command>INSERT</> or <command>UPDATE</> actions, or
 604          empty for <command>DELETE</>.  The array is indexed by column
 605          name.  Columns that are null will not appear in the array.
 606          This is not set for statement-level triggers.
 607         </para>
 608        </listitem>
 609       </varlistentry>
 610
 611       <varlistentry>
 612        <term><varname>$OLD</varname></term>
 613        <listitem>
 614         <para>
 615          An associative array containing the values of the old table
 616          row for <command>UPDATE</> or <command>DELETE</> actions, or
 617          empty for <command>INSERT</>.  The array is indexed by column
 618          name.  Columns that are null will not appear in the array.
 619          This is not set for statement-level triggers.
 620         </para>
 621        </listitem>
 622       </varlistentry>
 623
 624       <varlistentry>
 625        <term><varname>$args</varname></term>
 626        <listitem>
 627         <para>
 628          A Tcl list of the arguments to the procedure as given in the
 629          <command>CREATE TRIGGER</command> statement. These arguments are also accessible as
 630          <literal>$1</literal> ... <literal>$<replaceable>n</replaceable></literal> in the procedure body.
 631         </para>
 632        </listitem>
 633       </varlistentry>
 634
 635      </variablelist>
 636     </para>
 637
 638     <para>
 639      The return value from a trigger procedure can be one of the strings
 640      <literal>OK</> or <literal>SKIP</>, or a list as returned by the
 641      <literal>array get</> Tcl command. If the return value is <literal>OK</>,
 642      the operation (<command>INSERT</>/<command>UPDATE</>/<command>DELETE</>) that fired the trigger will proceed
 643      normally. <literal>SKIP</> tells the trigger manager to silently suppress
 644      the operation for this row. If a list is returned, it tells PL/Tcl to
 645      return a modified row to the trigger manager that will be inserted
 646      instead of the one given in <varname>$NEW</>.  (This works for <command>INSERT</> and <command>UPDATE</>
 647      only.) Needless to say that all this is only meaningful when the trigger
 648      is <literal>BEFORE</> and <command>FOR EACH ROW</>; otherwise the return value is ignored.
 649     </para>
 650
 651     <para>
 652      Here's a little example trigger procedure that forces an integer value
 653      in a table to keep track of the number of updates that are performed on the
 654      row. For new rows inserted, the value is initialized to 0 and then
 655      incremented on every update operation.
 656
 657 <programlisting>
 658 CREATE FUNCTION trigfunc_modcount() RETURNS trigger AS $$
 659     switch $TG_op {
 660         INSERT {
 661             set NEW($1) 0
 662         }
 663         UPDATE {
 664             set NEW($1) $OLD($1)
 665             incr NEW($1)
 666         }
 667         default {
 668             return OK
 669         }
 670     }
 671     return [array get NEW]
 672 $$ LANGUAGE pltcl;
 673
 674 CREATE TABLE mytab (num integer, description text, modcnt integer);
 675
 676 CREATE TRIGGER trig_mytab_modcount BEFORE INSERT OR UPDATE ON mytab
 677     FOR EACH ROW EXECUTE PROCEDURE trigfunc_modcount('modcnt');
 678 </programlisting>
 679
 680      Notice that the trigger procedure itself does not know the column
 681      name; that's supplied from the trigger arguments.  This lets the
 682      trigger procedure be reused with different tables.
 683     </para>
 684    </sect1>
 685
 686    <sect1 id="pltcl-unknown">
 687        <title>Modules and the <function>unknown</> command</title>
 688        <para>
 689         PL/Tcl has support for autoloading Tcl code when used.
 690         It recognizes a special table, <literal>pltcl_modules</>, which
 691         is presumed to contain modules of Tcl code.  If this table
 692         exists, the module <literal>unknown</> is fetched from the table
 693         and loaded into the Tcl interpreter immediately after creating
 694         the interpreter.
 695        </para>
 696        <para>
 697         While the <literal>unknown</> module could actually contain any
 698         initialization script you need, it normally defines a Tcl
 699         <function>unknown</> procedure that is invoked whenever Tcl does
 700         not recognize an invoked procedure name.  <application>PL/Tcl</>'s standard version
 701         of this procedure tries to find a module in <literal>pltcl_modules</>
 702         that will define the required procedure.  If one is found, it is
 703         loaded into the interpreter, and then execution is allowed to
 704         proceed with the originally attempted procedure call.  A
 705         secondary table <literal>pltcl_modfuncs</> provides an index of
 706         which functions are defined by which modules, so that the lookup
 707         is reasonably quick.
 708        </para>
 709        <para>
 710         The <productname>PostgreSQL</productname> distribution includes
 711         support scripts to maintain these tables:
 712         <command>pltcl_loadmod</>, <command>pltcl_listmod</>,
 713         <command>pltcl_delmod</>, as well as source for the standard
 714         <literal>unknown</> module in <filename>share/unknown.pltcl</>.  This module
 715         must be loaded
 716         into each database initially to support the autoloading mechanism.
 717        </para>
 718        <para>
 719         The tables <literal>pltcl_modules</> and <literal>pltcl_modfuncs</>
 720         must be readable by all, but it is wise to make them owned and
 721         writable only by the database administrator.
 722        </para>
 723    </sect1>
 724
 725    <sect1 id="pltcl-procnames">
 726     <title>Tcl Procedure Names</title>
 727
 728     <para>
 729      In <productname>PostgreSQL</productname>, the same function name can be used for
 730      different function definitions as long as the number of arguments or their types
 731      differ. Tcl, however, requires all procedure names to be distinct.
 732      PL/Tcl deals with this by making the internal Tcl procedure names contain
 733      the object
 734      ID of the function from the system table <structname>pg_proc</> as part of their name. Thus,
 735      <productname>PostgreSQL</productname> functions with the same name
 736      and different argument types will be different Tcl procedures, too.  This
 737      is not normally a concern for a PL/Tcl programmer, but it might be visible
 738      when debugging.
 739     </para>
 740
 741    </sect1>
 742  </chapter>