xplang.sgml

关系型数据库 Postgresql 6.5.2

    is useful to have some global status data that is held between two
    calls to a procedure. 
    All PL/Tcl procedures executed in one backend share the same
    safe Tcl interpreter.
    To help protecting PL/Tcl procedures from side effects,
    an array is made available to each procedure via the upvar
    command. The global name of this variable is the procedures internal
    name and the local name is GD.
</Para>
</Sect3>

<Sect3>
<Title>Trigger Procedures in PL/Tcl</Title>

<Para>
    Trigger procedures are defined in <ProductName>Postgres</ProductName>
    as functions without
    arguments and a return type of opaque. And so are they in the PL/Tcl
    language.
</Para>
<Para>
    The informations from the trigger manager are given to the procedure body
    in the following variables:
</Para>
<VariableList>

<VarListEntry>
<Term><Replaceable class="Parameter">
$TG_name
</Replaceable></Term>
<ListItem>
<Para>
    The name of the trigger from the CREATE TRIGGER statement.
</Para>
</ListItem>
</VarListEntry>

<VarListEntry>
<Term><Replaceable class="Parameter">
$TG_relid
</Replaceable></Term>
<ListItem>
<Para>
    The object ID of the table that caused the trigger procedure
    to be invoked.
</Para>
</ListItem>
</VarListEntry>

<VarListEntry>
<Term><Replaceable class="Parameter">
$TG_relatts
</Replaceable></Term>
<ListItem>
<Para>
    A Tcl list of the tables field names prefixed with an empty list element.
    So looking up an element name in the list with the lsearch Tcl command
    returns the same positive number starting from 1 as the fields are numbered
    in the pg_attribute system catalog.
</Para>
</ListItem>
</VarListEntry>

<VarListEntry>
<Term><Replaceable class="Parameter">
$TG_when
</Replaceable></Term>
<ListItem>
<Para>
    The string BEFORE or AFTER depending on the event of the trigger call.
</Para>
</ListItem>
</VarListEntry>

<VarListEntry>
<Term><Replaceable class="Parameter">
$TG_level
</Replaceable></Term>
<ListItem>
<Para>
    The string ROW or STATEMENT depending on the event of the trigger call.
</Para>
</ListItem>
</VarListEntry>

<VarListEntry>
<Term><Replaceable class="Parameter">
$TG_op
</Replaceable></Term>
<ListItem>
<Para>
    The string INSERT, UPDATE or DELETE depending on the event of the 
    trigger call.
</Para>
</ListItem>
</VarListEntry>

<VarListEntry>
<Term><Replaceable class="Parameter">
$NEW
</Replaceable></Term>
<ListItem>
<Para>
    An array containing the values of the new table row on INSERT/UPDATE
    actions, or empty on DELETE.
</Para>
</ListItem>
</VarListEntry>

<VarListEntry>
<Term><Replaceable class="Parameter">
$OLD
</Replaceable></Term>
<ListItem>
<Para>
    An array containing the values of the old table row on UPDATE/DELETE
    actions, or empty on INSERT.
</Para>
</ListItem>
</VarListEntry>

<VarListEntry>
<Term><Replaceable class="Parameter">
$GD
</Replaceable></Term>
<ListItem>
<Para>
    The global status data array as described above.
</Para>
</ListItem>
</VarListEntry>

<VarListEntry>
<Term><Replaceable class="Parameter">
$args
</Replaceable></Term>
<ListItem>
<Para>
    A Tcl list of the arguments to the procedure as given in the
    CREATE TRIGGER statement. The arguments are also accessible as $1 ... $n
    in the procedure body.
</Para>
</ListItem>
</VarListEntry>

</VariableList>

<Para>
    The return value from a trigger procedure is one of the strings OK or SKIP,
    or a list as returned by the 'array get' Tcl command. If the return value
    is OK, the normal operation (INSERT/UPDATE/DELETE) that fired this trigger
    will take place. Obviously, SKIP tells the trigger manager to silently
    suppress the operation. The list from 'array get' tells PL/Tcl
    to return a modified row to the trigger manager that will be inserted instead
    of the one given in $NEW (INSERT/UPDATE only). Needless to say that all
    this is only meaningful when the trigger is BEFORE and FOR EACH ROW.
</Para>
<Para>
    Here's a little example trigger procedure that forces an integer value
    in a table to keep track of the # of updates that are performed on the
    row. For new row's inserted, the value is initialized to 0 and then
    incremented on every update operation:

    <ProgramListing>
    CREATE FUNCTION trigfunc_modcount() RETURNS OPAQUE AS '
        switch $TG_op {
            INSERT {
                set NEW($1) 0
            }
            UPDATE {
                set NEW($1) $OLD($1)
                incr NEW($1)
            }
            default {
                return OK
            }
        }
        return [array get NEW]
    ' LANGUAGE 'pltcl';

    CREATE TABLE mytab (num int4, modcnt int4, desc text);

    CREATE TRIGGER trig_mytab_modcount BEFORE INSERT OR UPDATE ON mytab
        FOR EACH ROW EXECUTE PROCEDURE trigfunc_modcount('modcnt');
    </ProgramListing>

</Para>
</Sect3>

<Sect3>
<Title>Database Access from PL/Tcl</Title>

<Para>
    The following commands are available to access the database from
    the body of a PL/Tcl procedure:
</Para>

<VariableList>

<VarListEntry>
<Term>
elog <Replaceable>level</Replaceable> <Replaceable>msg</Replaceable>
</Term>
<ListItem>
<Para>
    Fire a log message. Possible levels are NOTICE, WARN, ERROR,
    FATAL, DEBUG and NOIND
    like for the elog() C function.
</Para>
</ListItem>
</VarListEntry>

<VarListEntry>
<Term>
quote <Replaceable>string</Replaceable>
</Term>
<ListItem>
<Para>
    Duplicates all occurences of single quote and backslash characters.
    It should be used when variables are used in the query string given
    to spi_exec or spi_prepare (not for the value list on spi_execp).
    Think about a query string like

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

    where the Tcl variable val actually contains "doesn't". This would result
    in the final query string

    <ProgramListing>
    "SELECT 'doesn't' AS ret"
    </ProgramListing>

    what would cause a parse error during spi_exec or spi_prepare.
    It should contain

    <ProgramListing>
    "SELECT 'doesn''t' AS ret"
    </ProgramListing>

    and has to be written as

    <ProgramListing>
    "SELECT '[ quote $val ]' AS ret"
    </ProgramListing>
</Para>
</ListItem>
</VarListEntry>

<VarListEntry>
<Term>
spi_exec ?-count <Replaceable>n</Replaceable>? ?-array <Replaceable>name</Replaceable>? <Replaceable>query</Replaceable> ?<Replaceable>loop-body</Replaceable>?
</Term>
<ListItem>
<Para>
    Call parser/planner/optimizer/executor for query.
    The optional -count value tells spi_exec the maximum number of rows
    to be processed by the query.
</Para>
<Para>
    If the query is
    a SELECT statement and the optional loop-body (a body of Tcl commands
    like in a foreach statement) is given, it is evaluated for each
    row selected and behaves like expected on continue/break. The values
    of selected fields are put into variables named as the column names. So a

    <ProgramListing>
    spi_exec "SELECT count(*) AS cnt FROM pg_proc"
    </ProgramListing>

    will set the variable $cnt to the number of rows in the pg_proc system
    catalog. If the option -array is given, the column values are stored
    in the associative array named 'name' indexed by the column name
    instead of individual variables.

    <ProgramListing>
    spi_exec -array C "SELECT * FROM pg_class" {
        elog DEBUG "have table $C(relname)"
    }
    </ProgramListing>

    will print a DEBUG log message for every row of pg_class. The return value
    of spi_exec is the number of rows affected by query as found in
    the global variable SPI_processed.
</Para>
</ListItem>
</VarListEntry>

<VarListEntry>
<Term>
spi_prepare <Replaceable>query</Replaceable> <Replaceable>typelist</Replaceable>
</Term>
<ListItem>
<Para>
    Prepares AND SAVES a query plan for later execution. It is a bit different
    from the C level SPI_prepare in that the plan is automatically copied to the
    toplevel memory context. Thus, there is currently no way of preparing a
    plan without saving it.
</Para>
<Para>
    If the query references arguments, the type names must be given as a Tcl
    list. The return value from spi_prepare is a query ID to be used in
    subsequent calls to spi_execp. See spi_execp for a sample.
</Para>
</ListItem>
</VarListEntry>

<VarListEntry>
<Term>
spi_exec ?-count <Replaceable>n</Replaceable>? ?-array <Replaceable>name</Replaceable>? ?-nulls <Replaceable>str</Replaceable>? <Replaceable>query</Replaceable> ?<Replaceable>valuelist</Replaceable>? ?<Replaceable>loop-body</Replaceable>?
</Term>
<ListItem>
<Para>
    Execute a prepared plan from spi_prepare with variable substitution.
    The optional -count value tells spi_execp the maximum number of rows
    to be processed by the query.
</Para>
<Para>
    The optional value for -nulls is a string of spaces and 'n' characters
    telling spi_execp which of the values are NULL's. If given, it must
    have exactly the length of the number of values.
</Para>
<Para>
    The queryid is the ID returned by the spi_prepare call.
</Para>
<Para>
    If there was a typelist given to spi_prepare, a Tcl list of values of
    exactly the same length must be given to spi_execp after the query. If
    the type list on spi_prepare was empty, this argument must be omitted.
</Para>
<Para>
    If the query is a SELECT statement, the same as described for spi_exec
    happens for the loop-body and the variables for the fields selected.
</Para>
<Para>
    Here's an example for a PL/Tcl function using a prepared plan:

    <ProgramListing>
    CREATE FUNCTION t1_count(int4, int4) RETURNS int4 AS '
        if {![ info exists GD(plan) ]} {
            # prepare the saved plan on the first call
            set GD(plan) [ spi_prepare \\
                    "SELECT count(*) AS cnt FROM t1 WHERE num &gt;= \\$1 AND num &lt;= \\$2" \\
                    int4 ]
        }
        spi_execp -count 1 $GD(plan) [ list $1 $2 ]
        return $cnt
    ' LANGUAGE 'pltcl';
    </ProgramListing>

    Note that each backslash that Tcl should see must be doubled in
    the query creating the function, since the main parser processes
    backslashes too on CREATE FUNCTION.
    Inside the query string given to spi_prepare should
    really be dollar signs to mark the parameter positions and to not let
    $1 be substituted by the value given in the first function call.
</Para>
</ListItem>
</VarListEntry>

<VarListEntry>
<Term>
Modules and the unknown command
</Term>
<ListItem>
<Para>
    PL/Tcl has a special support for things often used. It recognizes two
    magic tables, pltcl_modules and pltcl_modfuncs.
    If these exist, the module 'unknown' is loaded into the interpreter
    right after creation. Whenever an unknown Tcl procedure is called,
    the unknown proc is asked to check if the procedure is defined in one
    of the modules. If this is true, the module is loaded on demand.
    To enable this behavior, the PL/Tcl call handler must be compiled
    with -DPLTCL_UNKNOWN_SUPPORT set.
</Para>
<Para>
    There are support scripts to maintain these tables in the modules
    subdirectory of the PL/Tcl source including the source for the
    unknown module that must get installed initially.
</Para>
</ListItem>
</VarListEntry>

</VariableList>

</Sect3>

</Sect2>
</Sect1>


</Chapter>