xplang.sgml

关系型数据库 Postgresql 6.5.2

<Para>
    Datatype RECORD; variable holding the old database row on UPDATE/DELETE
    operations on ROW level triggers.
</Para>
</ListItem>
</VarListEntry>

<VarListEntry>
<Term>
    TG_NAME
</Term>
<ListItem>
<Para>
    Datatype name; variable that contains the name of the trigger actually
    fired.
</Para>
</ListItem>
</VarListEntry>

<VarListEntry>
<Term>
    TG_WHEN
</Term>
<ListItem>
<Para>
    Datatype text; a string of either 'BEFORE' or 'AFTER' depending on the
    triggers definition.
</Para>
</ListItem>
</VarListEntry>

<VarListEntry>
<Term>
    TG_LEVEL
</Term>
<ListItem>
<Para>
    Datatype text; a string of either 'ROW' or 'STATEMENT' depending on the
    triggers definition.
</Para>
</ListItem>
</VarListEntry>

<VarListEntry>
<Term>
    TG_OP
</Term>
<ListItem>
<Para>
    Datatype text; a string of 'INSERT', 'UPDATE' or 'DELETE' telling
    for which operation the trigger is actually fired.
</Para>
</ListItem>
</VarListEntry>

<VarListEntry>
<Term>
    TG_RELID
</Term>
<ListItem>
<Para>
    Datatype oid; the object ID of the table that caused the
    trigger invocation.
</Para>
</ListItem>
</VarListEntry>

<VarListEntry>
<Term>
    TG_RELNAME
</Term>
<ListItem>
<Para>
    Datatype name; the name of the table that caused the trigger
    invocation.
</Para>
</ListItem>
</VarListEntry>

<VarListEntry>
<Term>
    TG_NARGS
</Term>
<ListItem>
<Para>
    Datatype integer; the number of arguments given to the trigger
    procedure in the CREATE TRIGGER statement.
</Para>
</ListItem>
</VarListEntry>

<VarListEntry>
<Term>
    TG_ARGV[]
</Term>
<ListItem>
<Para>
    Datatype array of text; the arguments from the CREATE TRIGGER statement.
    The index counts from 0 and can be given as an expression. Invalid
    indices (&lt; 0 or &gt;= tg_nargs) result in a NULL value.
</Para>
</ListItem>
</VarListEntry>

</VariableList>

<Para>
    Second they must return either NULL or a record/row containing
    exactly the structure of the table the trigger was fired for.
    Triggers fired AFTER might always return a NULL value with no
    effect. Triggers fired BEFORE signal the trigger manager
    to skip the operation for this actual row when returning NULL.
    Otherwise, the returned record/row replaces the inserted/updated
    row in the operation. It is possible to replace single values directly
    in NEW and return that or to build a complete new record/row to
    return.
</Para>
</Sect3>

<!-- **** PL/pgSQL exceptions **** -->

<Sect3>
<Title>Exceptions</Title>

<Para>
    <ProductName>Postgres</ProductName> does not have a very smart
    exception handling model. Whenever the parser, planner/optimizer
    or executor decide that a statement cannot be processed any longer,
    the whole transaction gets aborted and the system jumps back
    into the mainloop to get the next query from the client application.
</Para>
<Para>
    It is possible to hook into the error mechanism to notice that this
    happens. But currently it's impossible to tell what really
    caused the abort (input/output conversion error, floating point
    error, parse error). And it is possible that the database backend
    is in an inconsistent state at this point so returning to the upper
    executor or issuing more commands might corrupt the whole database.
    And even if, at this point the information, that the transaction
    is aborted, is already sent to the client application, so resuming
    operation does not make any sense.
</Para>
<Para>
    Thus, the only thing PL/pgSQL currently does when it encounters
    an abort during execution of a function or trigger
    procedure is to write some additional DEBUG level log messages
    telling in which function and where (line number and type of
    statement) this happened.
</Para>
</Sect3>
</Sect2>

<!-- **** PL/pgSQL Examples **** -->

<Sect2>
<Title>Examples</Title>

<Para>
Here are only a few functions to demonstrate how easy PL/pgSQL
functions can be written. For more complex examples the programmer
might look at the regression test for PL/pgSQL.
</Para>

<Para>
One painful detail of writing functions in PL/pgSQL is the handling
of single quotes. The functions source text on CREATE FUNCTION must
be a literal string. Single quotes inside of literal strings must be
either doubled or quoted with a backslash. We are still looking for
an elegant alternative. In the meantime, doubling the single qoutes
as in the examples below should be used. Any solution for this
in future versions of <ProductName>Postgres</ProductName> will be
upward compatible.
</Para>

<Sect3>
<Title>Some Simple PL/pgSQL Functions</Title>

<Para>
    The following two PL/pgSQL functions are identical to their
    counterparts from the C language function discussion.

    <ProgramListing>
    CREATE FUNCTION add_one (int4) RETURNS int4 AS '
        BEGIN
            RETURN $1 + 1;
        END;
    ' LANGUAGE 'plpgsql';
    </ProgramListing>

    <ProgramListing>
    CREATE FUNCTION concat_text (text, text) RETURNS text AS '
        BEGIN
            RETURN $1 || $2;
        END;
    ' LANGUAGE 'plpgsql';
    </ProgramListing>
</Para>

</Sect3>

<Sect3>
<Title>PL/pgSQL Function on Composite Type</Title>

<Para>
    Again it is the PL/pgSQL equivalent to the example from
    The C functions.

    <ProgramListing>
    CREATE FUNCTION c_overpaid (EMP, int4) RETURNS bool AS '
        DECLARE
            emprec ALIAS FOR $1;
            sallim ALIAS FOR $2;
        BEGIN
            IF emprec.salary ISNULL THEN
                RETURN ''f'';
            END IF;
            RETURN emprec.salary > sallim;
        END;
    ' LANGUAGE 'plpgsql';
    </ProgramListing>
</Para>

</Sect3>

<Sect3>
<Title>PL/pgSQL Trigger Procedure</Title>

<Para>
    This trigger ensures, that any time a row is inserted or updated
    in the table, the current username and time are stamped into the
    row. And it ensures that an employees name is given and that the
    salary is a positive value.

    <ProgramListing>
    CREATE TABLE emp (
        empname text,
        salary int4,
        last_date datetime,
        last_user name);

    CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS
        BEGIN
            -- Check that empname and salary are given
            IF NEW.empname ISNULL THEN
                RAISE EXCEPTION ''empname cannot be NULL value'';
            END IF;
            IF NEW.salary ISNULL THEN
                RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname;
            END IF;

            -- Who works for us when she must pay for?
            IF NEW.salary < 0 THEN
                RAISE EXCEPTION ''% cannot have a negative salary'', NEW.empname;
            END IF;

            -- Remember who changed the payroll when
            NEW.last_date := ''now'';
            NEW.last_user := getpgusername();
            RETURN NEW;
        END;
    ' LANGUAGE 'plpgsql';

    CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
        FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
    </ProgramListing>
</Para>

</Sect3>

</Sect2>

</Sect1>

<!-- **********
     * The procedural language PL/Tcl
     **********
-->

<Sect1>
<Title>PL/Tcl</Title>

<Para>
    PL/Tcl is a loadable procedural language for the
    <ProductName>Postgres</ProductName> database system
    that enables the Tcl language to be used to create functions and
    trigger-procedures.
</Para>

<Para>
    This package was originally written by Jan Wieck.
</Para>

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

<Sect2>
<Title>Overview</Title>

<Para>
    PL/Tcl offers most of the capabilities a function
    writer has in the C language, except for some restrictions.
</Para>
<Para>
    The good restriction is, that everything is executed in a safe
    Tcl-interpreter. In addition to the limited command set of safe Tcl, only
    a few commands are available to access the database over SPI and to raise
    messages via elog(). There is no way to access internals of the
    database backend or gaining OS-level access under the permissions of the
    <ProductName>Postgres</ProductName> user ID like in C.
    Thus, any unprivileged database user may be
    permitted to use this language.
</Para>
<Para>
    The other, internal given, restriction is, that Tcl procedures cannot
    be used to create input-/output-functions for new data types.
</Para>
<Para>
    The shared object for the PL/Tcl call handler is automatically built
    and installed in the <ProductName>Postgres</ProductName>
    library directory if the Tcl/Tk support is specified
    in the configuration step of the installation procedure.
</Para>
</Sect2>

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

<Sect2>
<Title>Description</Title>

<Sect3>
<Title><ProductName>Postgres</ProductName> Functions and Tcl Procedure Names</Title>

<Para>
    In <ProductName>Postgres</ProductName>, one and the 
    same function name can be used for
    different functions as long as the number of arguments or their types
    differ. This would collide with Tcl procedure names. To offer the same
    flexibility in PL/Tcl, the internal Tcl procedure names contain the object
    ID of the procedures pg_proc row as part of their name. Thus, different
    argtype versions of the same <ProductName>Postgres</ProductName> 
    function are different for Tcl too.
</Para>

</Sect3>

<Sect3>
<Title>Defining Functions in PL/Tcl</Title>

<Para>
    To create a function in the PL/Tcl language, use the known syntax

    <ProgramListing>
    CREATE FUNCTION <Replaceable>funcname</Replaceable> (<Replaceable>argument-types</Replaceable>) RETURNS <Replaceable>returntype</Replaceable> AS '
        # PL/Tcl function body
    ' LANGUAGE 'pltcl';
    </ProgramListing>

    When calling this function in a query, the arguments are given as
    variables $1 ... $n to the Tcl procedure body. So a little max function
    returning the higher of two int4 values would be created as:

    <ProgramListing>
    CREATE FUNCTION tcl_max (int4, int4) RETURNS int4 AS '
        if {$1 > $2} {return $1}
	return $2
    ' LANGUAGE 'pltcl';
    </ProgramListing>

    Composite type arguments are given to the procedure as Tcl arrays.
    The element names
    in the array are the attribute names of the composite
    type. If an attribute in the actual row
    has the NULL value, it will not appear in the array! Here is
    an example that defines the overpaid_2 function (as found in the
    older <ProductName>Postgres</ProductName> documentation) in PL/Tcl

    <ProgramListing>
    CREATE FUNCTION overpaid_2 (EMP) RETURNS bool AS '
        if {200000.0 < $1(salary)} {
            return "t"
        }
        if {$1(age) < 30 && 100000.0 < $1(salary)} {
            return "t"
        }
        return "f"
    ' LANGUAGE 'pltcl';
    </ProgramListing>
</Para>

</Sect3>

<Sect3>
<Title>Global Data in PL/Tcl</Title>

<Para>
    Sometimes (especially when using the SPI functions described later) it