doc/src/sgml/pltcl.sgml

   1 <!-- doc/src/sgml/pltcl.sgml -->
   2
   3  <chapter id="pltcl">
   4   <title>PL/Tcl &mdash; 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="https://www.tcl.tk/">
  18    Tcl language</ulink> to be used to write
  19    <productname>PostgreSQL</productname> functions and 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()</function>.  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</application> called <literal>PL/TclU</literal>
  54     (for untrusted Tcl).  This is exactly the same language except that a full
  55     Tcl interpreter is used.  <emphasis>If <application>PL/TclU</application> 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</application>
  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</application> and
  64     <application>PL/TclU</application> 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</application>
  68     and/or <application>PL/TclU</application> in a particular database, use the
  69     <command>CREATE EXTENSION</command> command, for example
  70     <literal>CREATE EXTENSION pltcl</literal> or
  71     <literal>CREATE EXTENSION pltclu</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</application> language, use
  82      the standard <xref linkend="sql-createfunction"/> syntax:
  83
  84 <programlisting>
  85 CREATE FUNCTION <replaceable>funcname</replaceable> (<replaceable>argument-types</replaceable>) RETURNS <replaceable>return-type</replaceable> AS $$
  86     # PL/Tcl function body
  87 $$ LANGUAGE pltcl;
  88 </programlisting>
  89
  90      <application>PL/TclU</application> is the same, except that the language has to be specified as
  91      <literal>pltclu</literal>.
  92     </para>
  93
  94     <para>
  95      The body of the function is simply a piece of Tcl script.
  96      When the function is called, the argument values are passed to the
  97      Tcl script as variables named <literal>1</literal>
  98      ... <literal><replaceable>n</replaceable></literal>.  The result is
  99      returned from the Tcl code in the usual way, with
 100      a <literal>return</literal> statement.  In a procedure, the return value
 101      from the Tcl code is ignored.
 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</literal>, 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</literal>.  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      PL/Tcl functions can return composite-type results, too.  To do this,
 177      the Tcl code must return a list of column name/value pairs matching
 178      the expected result type.  Any column names omitted from the list
 179      are returned as nulls, and an error is raised if there are unexpected
 180      column names.  Here is an example:
 181
 182 <programlisting>
 183 CREATE FUNCTION square_cube(in int, out squared int, out cubed int) AS $$
 184     return [list squared [expr {$1 * $1}] cubed [expr {$1 * $1 * $1}]]
 185 $$ LANGUAGE pltcl;
 186 </programlisting>
 187     </para>
 188
 189     <para>
 190      Output arguments of procedures are returned in the same way, for example:
 191
 192 <programlisting>
 193 CREATE PROCEDURE tcl_triple(INOUT a integer, INOUT b integer) AS $$
 194     return [list a [expr {$1 * 3}] b [expr {$2 * 3}]]
 195 $$ LANGUAGE pltcl;
 196
 197 CALL tcl_triple(5, 10);
 198 </programlisting>
 199     </para>
 200
 201     <tip>
 202      <para>
 203       The result list can be made from an array representation of the
 204       desired tuple with the <literal>array get</literal> Tcl command.  For example:
 205
 206 <programlisting>
 207 CREATE FUNCTION raise_pay(employee, delta int) RETURNS employee AS $$
 208     set 1(salary) [expr {$1(salary) + $2}]
 209     return [array get 1]
 210 $$ LANGUAGE pltcl;
 211 </programlisting>
 212      </para>
 213     </tip>
 214
 215     <para>
 216      PL/Tcl functions can return sets.  To do this, the Tcl code should
 217      call <function>return_next</function> once per row to be returned,
 218      passing either the appropriate value when returning a scalar type,
 219      or a list of column name/value pairs when returning a composite type.
 220      Here is an example returning a scalar type:
 221
 222 <programlisting>
 223 CREATE FUNCTION sequence(int, int) RETURNS SETOF int AS $$
 224     for {set i $1} {$i &lt; $2} {incr i} {
 225         return_next $i
 226     }
 227 $$ LANGUAGE pltcl;
 228 </programlisting>
 229
 230      and here is one returning a composite type:
 231
 232 <programlisting>
 233 CREATE FUNCTION table_of_squares(int, int) RETURNS TABLE (x int, x2 int) AS $$
 234     for {set i $1} {$i &lt; $2} {incr i} {
 235         return_next [list x $i x2 [expr {$i * $i}]]
 236     }
 237 $$ LANGUAGE pltcl;
 238 </programlisting>
 239     </para>
 240
 241    </sect1>
 242
 243    <sect1 id="pltcl-data">
 244     <title>Data Values in PL/Tcl</title>
 245
 246     <para>
 247      The argument values supplied to a PL/Tcl function's code are simply
 248      the input arguments converted to text form (just as if they had been
 249      displayed by a <command>SELECT</command> statement).  Conversely, the
 250      <literal>return</literal> and <literal>return_next</literal> commands will accept
 251      any string that is acceptable input format for the function's declared
 252      result type, or for the specified column of a composite result type.
 253     </para>
 254
 255    </sect1>
 256
 257    <sect1 id="pltcl-global">
 258     <title>Global Data in PL/Tcl</title>
 259
 260     <indexterm zone="pltcl-global">
 261      <primary>global data</primary>
 262      <secondary>in PL/Tcl</secondary>
 263     </indexterm>
 264
 265     <para>
 266      Sometimes it
 267      is useful to have some global data that is held between two
 268      calls to a function or is shared between different functions.
 269      This is easily done in PL/Tcl, but there are some restrictions that
 270      must be understood.
 271     </para>
 272
 273     <para>
 274      For security reasons, PL/Tcl executes functions called by any one SQL
 275      role in a separate Tcl interpreter for that role.  This prevents
 276      accidental or malicious interference by one user with the behavior of
 277      another user's PL/Tcl functions.  Each such interpreter will have its own
 278      values for any <quote>global</quote> Tcl variables.  Thus, two PL/Tcl
 279      functions will share the same global variables if and only if they are
 280      executed by the same SQL role.  In an application wherein a single
 281      session executes code under multiple SQL roles (via <literal>SECURITY
 282      DEFINER</literal> functions, use of <command>SET ROLE</command>, etc.) you may need to
 283      take explicit steps to ensure that PL/Tcl functions can share data.  To
 284      do that, make sure that functions that should communicate are owned by
 285      the same user, and mark them <literal>SECURITY DEFINER</literal>.  You must of
 286      course take care that such functions can't be used to do anything
 287      unintended.
 288     </para>
 289
 290     <para>
 291      All PL/TclU functions used in a session execute in the same Tcl
 292      interpreter, which of course is distinct from the interpreter(s)
 293      used for PL/Tcl functions.  So global data is automatically shared
 294      between PL/TclU functions.  This is not considered a security risk
 295      because all PL/TclU functions execute at the same trust level,
 296      namely that of a database superuser.
 297     </para>
 298
 299     <para>
 300      To help protect PL/Tcl functions from unintentionally interfering
 301      with each other, a global
 302      array is made available to each function via the <function>upvar</function>
 303      command. The global name of this variable is the function's internal
 304      name, and the local name is <literal>GD</literal>.  It is recommended that
 305      <literal>GD</literal> be used
 306      for persistent private data of a function.  Use regular Tcl global
 307      variables only for values that you specifically intend to be shared among
 308      multiple functions.  (Note that the <literal>GD</literal> arrays are only
 309      global within a particular interpreter, so they do not bypass the
 310      security restrictions mentioned above.)
 311     </para>
 312
 313     <para>
 314      An example of using <literal>GD</literal> appears in the
 315      <function>spi_execp</function> example below.
 316     </para>
 317    </sect1>
 318
 319    <sect1 id="pltcl-dbaccess">
 320     <title>Database Access from PL/Tcl</title>
 321
 322     <para>
 323      In this section, we follow the usual Tcl convention of using question
 324      marks, rather than brackets, to indicate an optional element in a
 325      syntax synopsis.  The following commands are available to access
 326      the database from the body of a PL/Tcl function:
 327
 328     <variablelist>
 329
 330      <varlistentry>
 331       <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>
 332       <listitem>
 333        <para>
 334         Executes an SQL command given as a string.  An error in the command
 335         causes an error to be raised.  Otherwise, the return value of <function>spi_exec</function>
 336         is the number of rows processed (selected, inserted, updated, or
 337         deleted) by the command, or zero if the command is a utility
 338         statement.  In addition, if the command is a <command>SELECT</command> statement, the
 339         values of the selected columns are placed in Tcl variables as
 340         described below.
 341        </para>
 342        <para>
 343         The optional <literal>-count</literal> value tells
 344         <function>spi_exec</function> to stop
 345         once <replaceable>n</replaceable> rows have been retrieved,
 346         much as if the query included a <literal>LIMIT</literal> clause.
 347         If <replaceable>n</replaceable> is zero, the query is run to
 348         completion, the same as when <literal>-count</literal> is omitted.
 349        </para>
 350        <para>
 351         If the command is a <command>SELECT</command> statement, the values of the
 352         result columns are placed into Tcl variables named after the columns.
 353         If the <literal>-array</literal> option is given, the column values are
 354         instead stored into elements of the named associative array, with the
 355         column names used as array indexes.  In addition, the current row
 356         number within the result (counting from zero) is stored into the array
 357         element named <quote><literal>.tupno</literal></quote>, unless that name is
 358         in use as a column name in the result.
 359        </para>
 360        <para>
 361         If the command is a <command>SELECT</command> statement and no <replaceable>loop-body</replaceable>
 362         script is given, then only the first row of results are stored into
 363         Tcl variables or array elements; remaining rows, if any, are ignored.
 364         No storing occurs if the query returns no rows.  (This case can be
 365         detected by checking the result of <function>spi_exec</function>.)
 366         For example:
 367 <programlisting>
 368 spi_exec "SELECT count(*) AS cnt FROM pg_proc"
 369 </programlisting>
 370         will set the Tcl variable <literal>$cnt</literal> to the number of rows in
 371         the <structname>pg_proc</structname> system catalog.
 372        </para>
 373        <para>
 374         If the optional <replaceable>loop-body</replaceable> argument is given, it is
 375         a piece of Tcl script that is executed once for each row in the
 376         query result.  (<replaceable>loop-body</replaceable> is ignored if the given
 377         command is not a <command>SELECT</command>.)
 378         The values of the current row's columns
 379         are stored into Tcl variables or array elements before each iteration.
 380         For example:
 381 <programlisting>
 382 spi_exec -array C "SELECT * FROM pg_class" {
 383     elog DEBUG "have table $C(relname)"
 384 }
 385 </programlisting>
 386         will print a log message for every row of <literal>pg_class</literal>.  This
 387         feature works similarly to other Tcl looping constructs; in
 388         particular <literal>continue</literal> and <literal>break</literal> work in the
 389         usual way inside the loop body.
 390        </para>
 391        <para>
 392         If a column of a query result is null, the target
 393         variable for it is <quote>unset</quote> rather than being set.
 394        </para>
 395       </listitem>
 396      </varlistentry>
 397
 398      <varlistentry>
 399       <term><function>spi_prepare</function> <replaceable>query</replaceable> <replaceable>typelist</replaceable></term>
 400       <listitem>
 401        <para>
 402         Prepares and saves a query plan for later execution.  The
 403         saved plan will be retained for the life of the current
 404         session.<indexterm><primary>preparing a query</primary>
 405         <secondary>in PL/Tcl</secondary></indexterm>
 406        </para>
 407        <para>
 408         The query can use parameters, that is, placeholders for
 409         values to be supplied whenever the plan is actually executed.
 410         In the query string, refer to parameters
 411         by the symbols <literal>$1</literal> ... <literal>$<replaceable>n</replaceable></literal>.
 412         If the query uses parameters, the names of the parameter types
 413         must be given as a Tcl list.  (Write an empty list for
 414         <replaceable>typelist</replaceable> if no parameters are used.)
 415        </para>
 416        <para>
 417         The return value from <function>spi_prepare</function> is a query ID
 418         to be used in subsequent calls to <function>spi_execp</function>. See
 419         <function>spi_execp</function> for an example.
 420        </para>
 421       </listitem>
 422      </varlistentry>
 423
 424      <varlistentry>
 425       <term><literal><function>spi_execp</function> <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>
 426       <listitem>
 427        <para>
 428         Executes a query previously prepared with <function>spi_prepare</function>.
 429         <replaceable>queryid</replaceable> is the ID returned by
 430         <function>spi_prepare</function>.  If the query references parameters,
 431         a <replaceable>value-list</replaceable> must be supplied.  This
 432         is a Tcl list of actual values for the parameters.  The list must be
 433         the same length as the parameter type list previously given to
 434         <function>spi_prepare</function>.  Omit <replaceable>value-list</replaceable>
 435         if the query has no parameters.
 436        </para>
 437        <para>
 438         The optional value for <literal>-nulls</literal> is a string of spaces and
 439         <literal>'n'</literal> characters telling <function>spi_execp</function>
 440         which of the parameters are null values. If given, it must have exactly the
 441         same length as the <replaceable>value-list</replaceable>.  If it
 442         is not given, all the parameter values are nonnull.
 443        </para>
 444        <para>
 445         Except for the way in which the query and its parameters are specified,
 446         <function>spi_execp</function> works just like <function>spi_exec</function>.
 447         The <literal>-count</literal>, <literal>-array</literal>, and
 448         <replaceable>loop-body</replaceable> options are the same,
 449         and so is the result value.
 450        </para>
 451        <para>
 452         Here's an example of a PL/Tcl function using a prepared plan:
 453
 454 <programlisting>
 455 CREATE FUNCTION t1_count(integer, integer) RETURNS integer AS $$
 456     if {![ info exists GD(plan) ]} {
 457         # prepare the saved plan on the first call
 458         set GD(plan) [ spi_prepare \
 459                 "SELECT count(*) AS cnt FROM t1 WHERE num &gt;= \$1 AND num &lt;= \$2" \
 460                 [ list int4 int4 ] ]
 461     }
 462     spi_execp -count 1 $GD(plan) [ list $1 $2 ]
 463     return $cnt
 464 $$ LANGUAGE pltcl;
 465 </programlisting>
 466
 467         We need backslashes inside the query string given to
 468         <function>spi_prepare</function> to ensure that the
 469         <literal>$<replaceable>n</replaceable></literal> markers will be passed
 470         through to <function>spi_prepare</function> as-is, and not replaced by Tcl
 471         variable substitution.
 472
 473        </para>
 474       </listitem>
 475      </varlistentry>
 476
 477      <varlistentry>
 478       <term><function>subtransaction</function> <replaceable>command</replaceable></term>
 479       <listitem>
 480        <para>
 481         The Tcl script contained in <replaceable>command</replaceable> is
 482         executed within an SQL subtransaction.  If the script returns an
 483         error, that entire subtransaction is rolled back before returning the
 484         error out to the surrounding Tcl code.
 485         See <xref linkend="pltcl-subtransactions"/> for more details and an
 486         example.
 487        </para>
 488       </listitem>
 489      </varlistentry>
 490
 491      <varlistentry>
 492       <term><function>quote</function> <replaceable>string</replaceable></term>
 493       <listitem>
 494        <para>
 495         Doubles all occurrences of single quote and backslash characters
 496         in the given string.  This can be used to safely quote strings
 497         that are to be inserted into SQL commands given
 498         to <function>spi_exec</function> or
 499         <function>spi_prepare</function>.
 500         For example, think about an SQL command string like:
 501
 502 <programlisting>
 503 "SELECT '$val' AS ret"
 504 </programlisting>
 505
 506         where the Tcl variable <literal>val</literal> actually contains
 507         <literal>doesn't</literal>. This would result
 508         in the final command string:
 509
 510 <programlisting>
 511 SELECT 'doesn't' AS ret
 512 </programlisting>
 513
 514         which would cause a parse error during
 515         <function>spi_exec</function> or
 516         <function>spi_prepare</function>.
 517         To work properly, the submitted command should contain:
 518
 519 <programlisting>
 520 SELECT 'doesn''t' AS ret
 521 </programlisting>
 522
 523         which can be formed in PL/Tcl using:
 524
 525 <programlisting>
 526 "SELECT '[ quote $val ]' AS ret"
 527 </programlisting>
 528
 529         One advantage of <function>spi_execp</function> is that you don't
 530         have to quote parameter values like this, since the parameters are never
 531         parsed as part of an SQL command string.
 532        </para>
 533       </listitem>
 534      </varlistentry>
 535
 536      <varlistentry>
 537       <term>
 538        <function>elog</function> <replaceable>level</replaceable> <replaceable>msg</replaceable>
 539        <indexterm>
 540         <primary>elog</primary>
 541         <secondary>in PL/Tcl</secondary>
 542        </indexterm>
 543       </term>
 544       <listitem>
 545        <para>
 546         Emits a log or error message. Possible levels are
 547         <literal>DEBUG</literal>, <literal>LOG</literal>, <literal>INFO</literal>,
 548         <literal>NOTICE</literal>, <literal>WARNING</literal>, <literal>ERROR</literal>, and
 549         <literal>FATAL</literal>. <literal>ERROR</literal>
 550         raises an error condition; if this is not trapped by the surrounding
 551         Tcl code, the error propagates out to the calling query, causing
 552         the current transaction or subtransaction to be aborted.  This
 553         is effectively the same as the Tcl <literal>error</literal> command.
 554         <literal>FATAL</literal> aborts the transaction and causes the current
 555         session to shut down.  (There is probably no good reason to use
 556         this error level in PL/Tcl functions, but it's provided for
 557         completeness.)  The other levels only generate messages of different
 558         priority levels.
 559         Whether messages of a particular priority are reported to the client,
 560         written to the server log, or both is controlled by the
 561         <xref linkend="guc-log-min-messages"/> and
 562         <xref linkend="guc-client-min-messages"/> configuration
 563         variables. See <xref linkend="runtime-config"/>
 564         and <xref linkend="pltcl-error-handling"/>
 565         for more information.
 566        </para>
 567       </listitem>
 568      </varlistentry>
 569
 570     </variablelist>
 571     </para>
 572
 573    </sect1>
 574
 575    <sect1 id="pltcl-trigger">
 576     <title>Trigger Functions in PL/Tcl</title>
 577
 578     <indexterm>
 579      <primary>trigger</primary>
 580      <secondary>in PL/Tcl</secondary>
 581     </indexterm>
 582
 583     <para>
 584      Trigger functions can be written in PL/Tcl.
 585      <productname>PostgreSQL</productname> requires that a function that is to be called
 586      as a trigger must be declared as a function with no arguments
 587      and a return type of <literal>trigger</literal>.
 588     </para>
 589     <para>
 590      The information from the trigger manager is passed to the function body
 591      in the following variables:
 592
 593      <variablelist>
 594
 595       <varlistentry>
 596        <term><varname>$TG_name</varname></term>
 597        <listitem>
 598         <para>
 599          The name of the trigger from the <command>CREATE TRIGGER</command> statement.
 600         </para>
 601        </listitem>
 602       </varlistentry>
 603
 604       <varlistentry>
 605        <term><varname>$TG_relid</varname></term>
 606        <listitem>
 607         <para>
 608          The object ID of the table that caused the trigger function
 609          to be invoked.
 610         </para>
 611        </listitem>
 612       </varlistentry>
 613
 614       <varlistentry>
 615        <term><varname>$TG_table_name</varname></term>
 616        <listitem>
 617         <para>
 618          The name of the table that caused the trigger function
 619          to be invoked.
 620         </para>
 621        </listitem>
 622       </varlistentry>
 623
 624       <varlistentry>
 625        <term><varname>$TG_table_schema</varname></term>
 626        <listitem>
 627         <para>
 628          The schema of the table that caused the trigger function
 629          to be invoked.
 630         </para>
 631        </listitem>
 632       </varlistentry>
 633
 634       <varlistentry>
 635        <term><varname>$TG_relatts</varname></term>
 636        <listitem>
 637         <para>
 638          A Tcl list of the table column names, prefixed with an empty list
 639          element. So looking up a column name in the list with <application>Tcl</application>'s
 640          <function>lsearch</function> command returns the element's number starting
 641          with 1 for the first column, the same way the columns are customarily
 642          numbered in <productname>PostgreSQL</productname>.  (Empty list
 643          elements also appear in the positions of columns that have been
 644          dropped, so that the attribute numbering is correct for columns
 645          to their right.)
 646         </para>
 647        </listitem>
 648       </varlistentry>
 649
 650       <varlistentry>
 651        <term><varname>$TG_when</varname></term>
 652        <listitem>
 653         <para>
 654          The string <literal>BEFORE</literal>, <literal>AFTER</literal>, or
 655          <literal>INSTEAD OF</literal>, depending on the type of trigger event.
 656         </para>
 657        </listitem>
 658       </varlistentry>
 659
 660       <varlistentry>
 661        <term><varname>$TG_level</varname></term>
 662        <listitem>
 663         <para>
 664          The string <literal>ROW</literal> or <literal>STATEMENT</literal> depending on the
 665          type of trigger event.
 666         </para>
 667        </listitem>
 668       </varlistentry>
 669
 670       <varlistentry>
 671        <term><varname>$TG_op</varname></term>
 672        <listitem>
 673         <para>
 674          The string <literal>INSERT</literal>, <literal>UPDATE</literal>,
 675          <literal>DELETE</literal>, or <literal>TRUNCATE</literal> depending on the type of
 676          trigger event.
 677         </para>
 678        </listitem>
 679       </varlistentry>
 680
 681       <varlistentry>
 682        <term><varname>$NEW</varname></term>
 683        <listitem>
 684         <para>
 685          An associative array containing the values of the new table
 686          row for <command>INSERT</command> or <command>UPDATE</command> actions, or
 687          empty for <command>DELETE</command>.  The array is indexed by column
 688          name.  Columns that are null will not appear in the array.
 689          This is not set for statement-level triggers.
 690         </para>
 691        </listitem>
 692       </varlistentry>
 693
 694       <varlistentry>
 695        <term><varname>$OLD</varname></term>
 696        <listitem>
 697         <para>
 698          An associative array containing the values of the old table
 699          row for <command>UPDATE</command> or <command>DELETE</command> actions, or
 700          empty for <command>INSERT</command>.  The array is indexed by column
 701          name.  Columns that are null will not appear in the array.
 702          This is not set for statement-level triggers.
 703         </para>
 704        </listitem>
 705       </varlistentry>
 706
 707       <varlistentry>
 708        <term><varname>$args</varname></term>
 709        <listitem>
 710         <para>
 711          A Tcl list of the arguments to the function as given in the
 712          <command>CREATE TRIGGER</command> statement. These arguments are also accessible as
 713          <literal>$1</literal> ... <literal>$<replaceable>n</replaceable></literal> in the function body.
 714         </para>
 715        </listitem>
 716       </varlistentry>
 717
 718      </variablelist>
 719     </para>
 720
 721     <para>
 722      The return value from a trigger function can be one of the strings
 723      <literal>OK</literal> or <literal>SKIP</literal>, or a list of column name/value pairs.
 724      If the return value is <literal>OK</literal>,
 725      the operation (<command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command>)
 726      that fired the trigger will proceed
 727      normally. <literal>SKIP</literal> tells the trigger manager to silently suppress
 728      the operation for this row. If a list is returned, it tells PL/Tcl to
 729      return a modified row to the trigger manager; the contents of the
 730      modified row are specified by the column names and values in the list.
 731      Any columns not mentioned in the list are set to null.
 732      Returning a modified row is only meaningful
 733      for row-level <literal>BEFORE</literal> <command>INSERT</command> or <command>UPDATE</command>
 734      triggers, for which the modified row will be inserted instead of the one
 735      given in <varname>$NEW</varname>; or for row-level <literal>INSTEAD OF</literal>
 736      <command>INSERT</command> or <command>UPDATE</command> triggers where the returned row
 737      is used as the source data for <command>INSERT RETURNING</command> or
 738      <command>UPDATE RETURNING</command> clauses.
 739      In row-level <literal>BEFORE</literal> <command>DELETE</command> or <literal>INSTEAD
 740      OF</literal> <command>DELETE</command> triggers, returning a modified row has the same
 741      effect as returning <literal>OK</literal>, that is the operation proceeds.
 742      The trigger return value is ignored for all other types of triggers.
 743     </para>
 744
 745     <tip>
 746      <para>
 747       The result list can be made from an array representation of the
 748       modified tuple with the <literal>array get</literal> Tcl command.
 749      </para>
 750     </tip>
 751
 752     <para>
 753      Here's a little example trigger function that forces an integer value
 754      in a table to keep track of the number of updates that are performed on the
 755      row. For new rows inserted, the value is initialized to 0 and then
 756      incremented on every update operation.
 757
 758 <programlisting>
 759 CREATE FUNCTION trigfunc_modcount() RETURNS trigger AS $$
 760     switch $TG_op {
 761         INSERT {
 762             set NEW($1) 0
 763         }
 764         UPDATE {
 765             set NEW($1) $OLD($1)
 766             incr NEW($1)
 767         }
 768         default {
 769             return OK
 770         }
 771     }
 772     return [array get NEW]
 773 $$ LANGUAGE pltcl;
 774
 775 CREATE TABLE mytab (num integer, description text, modcnt integer);
 776
 777 CREATE TRIGGER trig_mytab_modcount BEFORE INSERT OR UPDATE ON mytab
 778     FOR EACH ROW EXECUTE FUNCTION trigfunc_modcount('modcnt');
 779 </programlisting>
 780
 781      Notice that the trigger function itself does not know the column
 782      name; that's supplied from the trigger arguments.  This lets the
 783      trigger function be reused with different tables.
 784     </para>
 785    </sect1>
 786
 787    <sect1 id="pltcl-event-trigger">
 788     <title>Event Trigger Functions in PL/Tcl</title>
 789
 790     <indexterm>
 791      <primary>event trigger</primary>
 792      <secondary>in PL/Tcl</secondary>
 793     </indexterm>
 794
 795     <para>
 796      Event trigger functions can be written in PL/Tcl.
 797      <productname>PostgreSQL</productname> requires that a function that is
 798      to be called as an event trigger must be declared as a function with no
 799      arguments and a return type of <literal>event_trigger</literal>.
 800     </para>
 801     <para>
 802      The information from the trigger manager is passed to the function body
 803      in the following variables:
 804
 805      <variablelist>
 806
 807       <varlistentry>
 808        <term><varname>$TG_event</varname></term>
 809        <listitem>
 810         <para>
 811          The name of the event the trigger is fired for.
 812         </para>
 813        </listitem>
 814       </varlistentry>
 815
 816       <varlistentry>
 817        <term><varname>$TG_tag</varname></term>
 818        <listitem>
 819         <para>
 820          The command tag for which the trigger is fired.
 821         </para>
 822        </listitem>
 823       </varlistentry>
 824      </variablelist>
 825     </para>
 826
 827     <para>
 828      The return value of the trigger function is ignored.
 829     </para>
 830
 831     <para>
 832      Here's a little example event trigger function that simply raises
 833      a <literal>NOTICE</literal> message each time a supported command is
 834      executed:
 835
 836 <programlisting>
 837 CREATE OR REPLACE FUNCTION tclsnitch() RETURNS event_trigger AS $$
 838   elog NOTICE "tclsnitch: $TG_event $TG_tag"
 839 $$ LANGUAGE pltcl;
 840
 841 CREATE EVENT TRIGGER tcl_a_snitch ON ddl_command_start EXECUTE FUNCTION tclsnitch();
 842 </programlisting>
 843     </para>
 844    </sect1>
 845
 846    <sect1 id="pltcl-error-handling">
 847     <title>Error Handling in PL/Tcl</title>
 848
 849     <indexterm>
 850      <primary>exceptions</primary>
 851      <secondary>in PL/Tcl</secondary>
 852     </indexterm>
 853
 854     <para>
 855      Tcl code within or called from a PL/Tcl function can raise an error,
 856      either by executing some invalid operation or by generating an error
 857      using the Tcl <function>error</function> command or
 858      PL/Tcl's <function>elog</function> command.  Such errors can be caught
 859      within Tcl using the Tcl <function>catch</function> command.  If an
 860      error is not caught but is allowed to propagate out to the top level of
 861      execution of the PL/Tcl function, it is reported as an SQL error in the
 862      function's calling query.
 863     </para>
 864
 865     <para>
 866      Conversely, SQL errors that occur within PL/Tcl's
 867      <function>spi_exec</function>, <function>spi_prepare</function>,
 868      and <function>spi_execp</function> commands are reported as Tcl errors,
 869      so they are catchable by Tcl's <function>catch</function> command.
 870      (Each of these PL/Tcl commands runs its SQL operation in a
 871      subtransaction, which is rolled back on error, so that any
 872      partially-completed operation is automatically cleaned up.)
 873      Again, if an error propagates out to the top level without being caught,
 874      it turns back into an SQL error.
 875     </para>
 876
 877     <para>
 878      Tcl provides an <varname>errorCode</varname> variable that can represent
 879      additional information about an error in a form that is easy for Tcl
 880      programs to interpret.  The contents are in Tcl list format, and the
 881      first word identifies the subsystem or library reporting the error;
 882      beyond that the contents are left to the individual subsystem or
 883      library.  For database errors reported by PL/Tcl commands, the first
 884      word is <literal>POSTGRES</literal>, the second word is the PostgreSQL
 885      version number, and additional words are field name/value pairs
 886      providing detailed information about the error.
 887      Fields <varname>SQLSTATE</varname>, <varname>condition</varname>,
 888      and <varname>message</varname> are always supplied
 889      (the first two represent the error code and condition name as shown
 890      in <xref linkend="errcodes-appendix"/>).
 891      Fields that may be present include
 892      <varname>detail</varname>, <varname>hint</varname>, <varname>context</varname>,
 893      <varname>schema</varname>, <varname>table</varname>, <varname>column</varname>,
 894      <varname>datatype</varname>, <varname>constraint</varname>,
 895      <varname>statement</varname>, <varname>cursor_position</varname>,
 896      <varname>filename</varname>, <varname>lineno</varname>, and
 897      <varname>funcname</varname>.
 898     </para>
 899
 900     <para>
 901      A convenient way to work with PL/Tcl's <varname>errorCode</varname>
 902      information is to load it into an array, so that the field names become
 903      array subscripts.  Code for doing that might look like
 904 <programlisting>
 905 if {[catch { spi_exec $sql_command }]} {
 906     if {[lindex $::errorCode 0] == "POSTGRES"} {
 907         array set errorArray $::errorCode
 908         if {$errorArray(condition) == "undefined_table"} {
 909             # deal with missing table
 910         } else {
 911             # deal with some other type of SQL error
 912         }
 913     }
 914 }
 915 </programlisting>
 916      (The double colons explicitly specify that <varname>errorCode</varname>
 917      is a global variable.)
 918     </para>
 919    </sect1>
 920
 921    <sect1 id="pltcl-subtransactions">
 922     <title>Explicit Subtransactions in PL/Tcl</title>
 923
 924     <indexterm>
 925      <primary>subtransactions</primary>
 926      <secondary>in PL/Tcl</secondary>
 927     </indexterm>
 928
 929     <para>
 930      Recovering from errors caused by database access as described in
 931      <xref linkend="pltcl-error-handling"/> can lead to an undesirable
 932      situation where some operations succeed before one of them fails,
 933      and after recovering from that error the data is left in an
 934      inconsistent state.  PL/Tcl offers a solution to this problem in
 935      the form of explicit subtransactions.
 936     </para>
 937
 938     <para>
 939      Consider a function that implements a transfer between two accounts:
 940 <programlisting>
 941 CREATE FUNCTION transfer_funds() RETURNS void AS $$
 942     if [catch {
 943         spi_exec "UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'"
 944         spi_exec "UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'"
 945     } errormsg] {
 946         set result [format "error transferring funds: %s" $errormsg]
 947     } else {
 948         set result "funds transferred successfully"
 949     }
 950     spi_exec "INSERT INTO operations (result) VALUES ('[quote $result]')"
 951 $$ LANGUAGE pltcl;
 952 </programlisting>
 953      If the second <command>UPDATE</command> statement results in an
 954      exception being raised, this function will log the failure, but
 955      the result of the first <command>UPDATE</command> will
 956      nevertheless be committed.  In other words, the funds will be
 957      withdrawn from Joe's account, but will not be transferred to
 958      Mary's account.  This happens because each <function>spi_exec</function>
 959      is a separate subtransaction, and only one of those subtransactions
 960      got rolled back.
 961     </para>
 962
 963     <para>
 964      To handle such cases, you can wrap multiple database operations in an
 965      explicit subtransaction, which will succeed or roll back as a whole.
 966      PL/Tcl provides a <function>subtransaction</function> command to manage
 967      this.  We can rewrite our function as:
 968 <programlisting>
 969 CREATE FUNCTION transfer_funds2() RETURNS void AS $$
 970     if [catch {
 971         subtransaction {
 972             spi_exec "UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'"
 973             spi_exec "UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'"
 974         }
 975     } errormsg] {
 976         set result [format "error transferring funds: %s" $errormsg]
 977     } else {
 978         set result "funds transferred successfully"
 979     }
 980     spi_exec "INSERT INTO operations (result) VALUES ('[quote $result]')"
 981 $$ LANGUAGE pltcl;
 982 </programlisting>
 983      Note that use of <function>catch</function> is still required for this
 984      purpose.  Otherwise the error would propagate to the top level of the
 985      function, preventing the desired insertion into
 986      the <structname>operations</structname> table.
 987      The <function>subtransaction</function> command does not trap errors, it
 988      only assures that all database operations executed inside its scope will
 989      be rolled back together when an error is reported.
 990     </para>
 991
 992     <para>
 993      A rollback of an explicit subtransaction occurs on any error reported
 994      by the contained Tcl code, not only errors originating from database
 995      access.  Thus a regular Tcl exception raised inside
 996      a <function>subtransaction</function> command will also cause the
 997      subtransaction to be rolled back.  However, non-error exits out of the
 998      contained Tcl code (for instance, due to <function>return</function>) do
 999      not cause a rollback.
1000     </para>
1001    </sect1>
1002
1003    <sect1 id="pltcl-transactions">
1004     <title>Transaction Management</title>
1005
1006     <para>
1007      In a procedure called from the top level or an anonymous code block
1008      (<command>DO</command> command) called from the top level it is possible
1009      to control transactions.  To commit the current transaction, call the
1010      <literal>commit</literal> command.  To roll back the current transaction,
1011      call the <literal>rollback</literal> command.  (Note that it is not
1012      possible to run the SQL commands <command>COMMIT</command> or
1013      <command>ROLLBACK</command> via <function>spi_exec</function> or similar.
1014      It has to be done using these functions.)  After a transaction is ended,
1015      a new transaction is automatically started, so there is no separate
1016      command for that.
1017     </para>
1018
1019     <para>
1020      Here is an example:
1021 <programlisting>
1022 CREATE PROCEDURE transaction_test1()
1023 LANGUAGE pltcl
1024 AS $$
1025 for {set i 0} {$i &lt; 10} {incr i} {
1026     spi_exec "INSERT INTO test1 (a) VALUES ($i)"
1027     if {$i % 2 == 0} {
1028         commit
1029     } else {
1030         rollback
1031     }
1032 }
1033 $$;
1034
1035 CALL transaction_test1();
1036 </programlisting>
1037     </para>
1038
1039     <para>
1040      Transactions cannot be ended when an explicit subtransaction is active.
1041     </para>
1042    </sect1>
1043
1044    <sect1 id="pltcl-config">
1045     <title>PL/Tcl Configuration</title>
1046
1047     <para>
1048      This section lists configuration parameters that
1049      affect <application>PL/Tcl</application>.
1050     </para>
1051
1052     <variablelist>
1053
1054      <varlistentry id="guc-pltcl-start-proc" xreflabel="pltcl.start_proc">
1055       <term>
1056        <varname>pltcl.start_proc</varname> (<type>string</type>)
1057        <indexterm>
1058         <primary><varname>pltcl.start_proc</varname> configuration parameter</primary>
1059        </indexterm>
1060       </term>
1061       <listitem>
1062        <para>
1063         This parameter, if set to a nonempty string, specifies the name
1064         (possibly schema-qualified) of a parameterless PL/Tcl function that
1065         is to be executed whenever a new Tcl interpreter is created for
1066         PL/Tcl.  Such a function can perform per-session initialization, such
1067         as loading additional Tcl code.  A new Tcl interpreter is created
1068         when a PL/Tcl function is first executed in a database session, or
1069         when an additional interpreter has to be created because a PL/Tcl
1070         function is called by a new SQL role.
1071        </para>
1072
1073        <para>
1074         The referenced function must be written in the <literal>pltcl</literal>
1075         language, and must not be marked <literal>SECURITY DEFINER</literal>.
1076         (These restrictions ensure that it runs in the interpreter it's
1077         supposed to initialize.)  The current user must have permission to
1078         call it, too.
1079        </para>
1080
1081        <para>
1082         If the function fails with an error it will abort the function call
1083         that caused the new interpreter to be created and propagate out to
1084         the calling query, causing the current transaction or subtransaction
1085         to be aborted.  Any actions already done within Tcl won't be undone;
1086         however, that interpreter won't be used again.  If the language is
1087         used again the initialization will be attempted again within a fresh
1088         Tcl interpreter.
1089        </para>
1090
1091        <para>
1092         Only superusers can change this setting.  Although this setting
1093         can be changed within a session, such changes will not affect Tcl
1094         interpreters that have already been created.
1095        </para>
1096       </listitem>
1097      </varlistentry>
1098
1099      <varlistentry id="guc-pltclu-start-proc" xreflabel="pltclu.start_proc">
1100       <term>
1101        <varname>pltclu.start_proc</varname> (<type>string</type>)
1102        <indexterm>
1103         <primary><varname>pltclu.start_proc</varname> configuration parameter</primary>
1104        </indexterm>
1105       </term>
1106       <listitem>
1107        <para>
1108         This parameter is exactly like <varname>pltcl.start_proc</varname>,
1109         except that it applies to PL/TclU.  The referenced function must
1110         be written in the <literal>pltclu</literal> language.
1111        </para>
1112       </listitem>
1113      </varlistentry>
1114
1115     </variablelist>
1116    </sect1>
1117
1118    <sect1 id="pltcl-procnames">
1119     <title>Tcl Procedure Names</title>
1120
1121     <para>
1122      In <productname>PostgreSQL</productname>, the same function name can be used for
1123      different function definitions as long as the number of arguments or their types
1124      differ. Tcl, however, requires all procedure names to be distinct.
1125      PL/Tcl deals with this by making the internal Tcl procedure names contain
1126      the object
1127      ID of the function from the system table <structname>pg_proc</structname> as part of their name. Thus,
1128      <productname>PostgreSQL</productname> functions with the same name
1129      and different argument types will be different Tcl procedures, too.  This
1130      is not normally a concern for a PL/Tcl programmer, but it might be visible
1131      when debugging.
1132     </para>
1133
1134    </sect1>
1135  </chapter>