xplang.sgml

来自「关系型数据库 Postgresql 6.5.2」· SGML 代码 · 共 1,579 行 · 第 1/4 页

<!-- **** PL/pgSQL data types **** -->

<Sect3>
<Title>Data Types</Title>

<Para>
    The type of a varible can be any of the existing basetypes of
    the database. <Replaceable>type</Replaceable> in the declarations
    section above is defined as:
</Para>
<Para>
    <ItemizedList>
    <ListItem>
        <Para>
	<ProductName>Postgres</ProductName>-basetype
	</Para>
    </ListItem>
    <ListItem>
        <Para>
	<Replaceable>variable</Replaceable>%TYPE
	</Para>
    </ListItem>
    <ListItem>
        <Para>
	<Replaceable>class.field</Replaceable>%TYPE
	</Para>
    </ListItem>
    </ItemizedList>
</Para>
<Para>
    <Replaceable>variable</Replaceable> is the name of a variable,
previously declared in the 
    same function, that is visible at this point.
</Para>
<Para>
    <Replaceable>class</Replaceable> is the name of an existing table
    or view where <Replaceable>field</Replaceable> is the name of
    an attribute.
</Para>
<Para>
    Using the <Replaceable>class.field</Replaceable>%TYPE
    causes PL/pgSQL to lookup the attributes definitions at the
    first call to the funciton during the lifetime of a backend.
    Have a table with a char(20) attribute and some PL/pgSQL functions
    that deal with it's content in local variables. Now someone
    decides that char(20) isn't enough, dumps the table, drops it,
    recreates it now with the attribute in question defined as
    char(40) and restores the data. Ha - he forgot about the
    funcitons. The computations inside them will truncate the values
    to 20 characters. But if they are defined using the
    <Replaceable>class.field</Replaceable>%TYPE
    declarations, they will automagically handle the size change or
    if the new table schema defines the attribute as text type.
</Para>
</Sect3>

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

<Sect3>
<Title>Expressions</Title>

<Para>
    All expressions used in PL/pgSQL statements are processed using
    the backends executor. Expressions which appear to contain
constants may in fact require run-time evaluation (e.g. 'now' for the
datetime type) so
it is impossible for the PL/pgSQL parser
    to identify real constant values other than the NULL keyword. All
    expressions are evaluated internally by executing a query
    <ProgramListing>
    SELECT <Replaceable>expression</Replaceable>
    </ProgramListing>
    using the SPI manager. In the expression, occurences of variable
    identifiers are substituted by parameters and the actual values from
    the variables are passed to the executor in the parameter array. All
    expressions used in a PL/pgSQL function are only prepared and
    saved once.
</Para>
<Para>
    The type checking done by the <productname>Postgres</productname>
    main parser has some side
    effects to the interpretation of constant values. In detail there
    is a difference between what the two functions

    <ProgramListing>
    CREATE FUNCTION logfunc1 (text) RETURNS datetime AS '
        DECLARE
            logtxt ALIAS FOR $1;
        BEGIN
            INSERT INTO logtable VALUES (logtxt, ''now'');
            RETURN ''now'';
        END;
    ' LANGUAGE 'plpgsql';
    </ProgramListing>

    and

    <ProgramListing>
    CREATE FUNCTION logfunc2 (text) RETURNS datetime AS '
        DECLARE
            logtxt ALIAS FOR $1;
            curtime datetime;
        BEGIN
            curtime := ''now'';
            INSERT INTO logtable VALUES (logtxt, curtime);
            RETURN curtime;
        END;
    ' LANGUAGE 'plpgsql';
    </ProgramListing>

    do. In the case of logfunc1(), the <ProductName>Postgres</ProductName>
    main parser
    knows when preparing the plan for the INSERT, that the string 'now'
    should be interpreted as datetime because the target field of logtable
    is of that type. Thus, it will make a constant from it at this time
    and this constant value is then used in all invocations of logfunc1()
    during the lifetime of the backend. Needless to say that this isn't what the
    programmer wanted.
</Para>
<Para>
    In the case of logfunc2(), the <ProductName>Postgres</ProductName> 
    main parser does not know
    what type 'now' should become and therefor it returns a datatype of
    text containing the string 'now'. During the assignment
    to the local variable curtime, the PL/pgSQL interpreter casts this
    string to the datetime type by calling the text_out() and datetime_in()
    functions for the conversion.
</Para>
<Para>
    This type checking done by the <ProductName>Postgres</ProductName> main
    parser got implemented after PL/pgSQL was nearly done.
    It is a difference between 6.3 and 6.4 and affects all functions
    using the prepared plan feature of the SPI manager.
    Using a local
    variable in the above manner is currently the only way in PL/pgSQL to get
    those values interpreted correctly.
</Para>
<Para>
    If record fields are used in expressions or statements, the data types of
    fields should not change between calls of one and the same expression.
    Keep this in mind when writing trigger procedures that handle events
    for more than one table.
</Para>
</Sect3>

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

<Sect3>
<Title>Statements</Title>

<Para>
    Anything not understood by the PL/pgSQL parser as specified below
    will be put into a query and sent down to the database engine
    to execute. The resulting query should not return any data.
</Para>

<VariableList>

<VarListEntry>
<Term>
Assignment
</Term>
<ListItem>
<Para>
    An assignment of a value to a variable or row/record field is
    written as
    <ProgramListing>
    <Replaceable>identifier</Replaceable> := <Replaceable>expression</Replaceable>;
    </ProgramListing>
    If the expressions result data type doesn't match the variables
    data type, or the variable has a size/precision that is known
    (as for char(20)), the result value will be implicitly casted by
    the PL/pgSQL bytecode interpreter using the result types output- and
    the variables type input-functions. Note that this could potentially
    result in runtime errors generated by the types input functions.
</Para>
<Para>
    An assignment of a complete selection into a record or row can
    be done by
    <ProgramListing>
    SELECT <Replaceable>expressions</Replaceable> INTO <Replaceable>target</Replaceable> FROM ...;
    </ProgramListing>
    <Replaceable>target</Replaceable> can be a record, a row variable or a
    comma separated list of variables and record-/row-fields.
</Para>
<Para>
    if a row or a variable list is used as target, the selected values
    must exactly match the structure of the target(s) or a runtime error
    occurs. The FROM keyword can be followed by any valid qualification,
    grouping, sorting etc. that can be given for a SELECT statement.
</Para>
<Para>
    There is a special variable named FOUND of type bool that can be used
    immediately after a SELECT INTO to check if an assignment had success.

    <ProgramListing>
    SELECT * INTO myrec FROM EMP WHERE empname = myname;
    IF NOT FOUND THEN
        RAISE EXCEPTION ''employee % not found'', myname;
    END IF;
    </ProgramListing>

    If the selection returns multiple rows, only the first is moved
    into the target fields. All others are silently discarded.
</Para>
</ListItem>
</VarListEntry>

<VarListEntry>
<Term>
Calling another function
</Term>
<ListItem>
<Para>
    All functions defined in a <ProductName>Prostgres</ProductName>
    database return a value. Thus, the normal way to call a function
    is to execute a SELECT query or doing an assignment (resulting
    in a PL/pgSQL internal SELECT). But there are cases where someone
    isn't interested int the functions result.
    <ProgramListing>
    PERFORM <Replaceable>query</Replaceable>
    </ProgramListing>
    executes a 'SELECT <Replaceable>query</Replaceable>' over the
    SPI manager and discards the result. Identifiers like local
    variables are still substituted into parameters.
</Para>
</ListItem>
</VarListEntry>

<VarListEntry>
<Term>
Returning from the function
</Term>
<ListItem>
<Para>
    <ProgramListing>
    RETURN <Replaceable>expression</Replaceable>
    </ProgramListing>
    The function terminates and the value of <Replaceable>expression</Replaceable>
    will be returned to the upper executor. The return value of a function
    cannot be undefined. If control reaches the end of the toplevel block
    of the function without hitting a RETURN statement, a runtime error
    will occur.
</Para>
<Para>
    The expressions result will be automatically casted into the
    functions return type as described for assignments.
</Para>
</ListItem>
</VarListEntry>

<VarListEntry>
<Term>
Aborting and messages
</Term>
<ListItem>
<Para>
    As indicated in the above examples there is a RAISE statement that
    can throw messages into the <ProductName>Postgres</ProductName>
    elog mechanism.
    <ProgramListing>
    RAISE <replaceable class="parameter">level</replaceable> ''<replaceable class="parameter">format</replaceable>'' [, <replaceable class="parameter">identifier</replaceable> [...]];
    </ProgramListing>
    Inside the format, <quote>%</quote> is used as a placeholder for the
    subsequent comma-separated identifiers. Possible levels are
    DEBUG (silently suppressed in production running databases), NOTICE 
    (written into the database log and forwarded to the client application)
    and EXCEPTION (written into the database log and aborting the transaction).
</Para>
</ListItem>
</VarListEntry>

<VarListEntry>
<Term>
Conditionals
</Term>
<ListItem>
<Para>
    <ProgramListing>
    IF <Replaceable>expression</Replaceable> THEN
        <replaceable>statements</replaceable>
    [ELSE
        <replaceable>statements</replaceable>]
    END IF;
    </ProgramListing>
    The <Replaceable>expression</Replaceable> must return a value that
    at least can be casted into a boolean type.
</Para>
</ListItem>
</VarListEntry>

<VarListEntry>
<Term>
Loops
</Term>
<ListItem>
<Para>
    There are multiple types of loops.
    <ProgramListing>
    [&lt;&lt;label&gt;&gt;]
    LOOP
        <replaceable>statements</replaceable>
    END LOOP;
    </ProgramListing>
    An unconditional loop that must be terminated explicitly
    by an EXIT statement. The optional label can be used by
    EXIT statements of nested loops to specify which level of
    nesting should be terminated.
    <ProgramListing>
    [&lt;&lt;label&gt;&gt;]
    WHILE <Replaceable>expression</Replaceable> LOOP
        <replaceable>statements</replaceable>
    END LOOP;
    </ProgramListing>
    A conditional loop that is executed as long as the evaluation
    of <Replaceable>expression</Replaceable> is true.
    <ProgramListing>
    [&lt;&lt;label&gt;&gt;]
    FOR <Replaceable>name</Replaceable> IN [ REVERSE ] <Replaceable>expression</Replaceable> .. <Replaceable>expression</Replaceable> LOOP
        <replaceable>statements</replaceable>
    END LOOP;
    </ProgramListing>
    A loop that iterates over a range of integer values. The variable
    <Replaceable>name</Replaceable> is automatically created as type
    integer and exists only inside the loop. The two expressions giving
    the lower and upper bound of the range are evaluated only when entering
    the loop. The iteration step is always 1.
    <ProgramListing>
    [&lt;&lt;label&gt;&gt;]
    FOR <Replaceable>record | row</Replaceable> IN <Replaceable>select_clause</Replaceable> LOOP
        <replaceable>statements</replaceable>
    END LOOP;
    </ProgramListing>
    The record or row is assigned all the rows resulting from the select
    clause and the statements executed for each. If the loop is terminated
    with an EXIT statement, the last assigned row is still accessible 
    after the loop.
    <ProgramListing>
    EXIT [ <Replaceable>label</Replaceable> ] [ WHEN <Replaceable>expression</Replaceable> ];
    </ProgramListing>
    If no <Replaceable>label</Replaceable> given,
 the innermost loop is terminated and the
    statement following END LOOP is executed next.
 If <Replaceable>label</Replaceable> is given, it
    must be the label of the current or an upper level of nested loop
    blocks. Then the named loop or block is terminated and control
    continues with the statement after the loops/blocks corresponding
    END.
</Para>
</ListItem>
</VarListEntry>

</VariableList>

</Sect3>

<!-- **** PL/pgSQL trigger procedures **** -->

<Sect3>
<Title>Trigger Procedures</Title>

<Para>
    PL/pgSQL can be used to define trigger procedures. They are created
    with the usual CREATE FUNCTION command as a function with no
    arguments and a return type of OPAQUE.
</Para>
<Para>
    There are some <ProductName>Postgres</ProductName> specific details
    in functions used as trigger procedures.
</Para>
<Para>
    First they have some special variables created automatically in the 
    toplevel blocks declaration section. They are
</Para>

<VariableList>

<VarListEntry>
<Term>
    NEW
</Term>
<ListItem>
<Para>
    Datatype RECORD; variable holding the new database row on INSERT/UPDATE
    operations on ROW level triggers.
</Para>
</ListItem>
</VarListEntry>

<VarListEntry>
<Term>
    OLD
</Term>
<ListItem>