xplang.sgml

关系型数据库 Postgresql 6.5.2

<Chapter Id="xplang">
<Title>Procedural Languages</Title>

<!-- **********
     * General information about procedural language support
     **********
-->

<Para>
    Beginning with the release of version 6.3,
    <ProductName>Postgres</ProductName> supports
    the definition of procedural languages.
    In the case of a function or trigger
    procedure defined in a procedural language, the database has
    no builtin knowlege how to interpret the functions source
    text. Instead, the calls are passed into
    a handler that knows the details of the language. The
    handler itself is a special programming language function
    compiled into a shared object
    and loaded on demand.
</Para>

<!-- **********
     * Installation of procedural languages
     **********
-->

<Sect1>
<Title>Installing Procedural Languages</Title>

<Procedure>
    <Title>
    Procedural Language Installation
    </Title>

    <para>
    A procedural language is installed in the database in three steps.
    </para>
	<Step Performance="Required">
	    <Para>
	        The shared object for the language handler
		must be compiled and installed. By default the
		handler for PL/pgSQL is built and installed into the
		database library directory. If Tcl/Tk support is
		configured in, the handler for PL/Tcl is also built
		and installed in the same location.
	    </Para>
	    <Para>
	        Writing a handler for a new procedural language (PL)
		is outside the scope of this manual. 
	    </Para>
	</Step>
	<Step Performance="Required">
	    <Para>
	        The handler must be declared with the command
		<ProgramListing>
    CREATE FUNCTION <Replaceable>handler_function_name</Replaceable> () RETURNS OPAQUE AS
        '<Filename>path-to-shared-object</Filename>' LANGUAGE 'C';
		</ProgramListing>
		The special return type of <Acronym>OPAQUE</Acronym> tells
		the database, that this function does not return one of
		the defined base- or composite types and is not directly usable
		in <Acronym>SQL</Acronym> statements.
	    </Para>
	</Step>
	<Step Performance="Required">
	    <Para>
	    	The PL must be declared with the command
		<ProgramListing>
    CREATE [ TRUSTED ] PROCEDURAL LANGUAGE '<Replaceable>language-name</Replaceable>'
        HANDLER <Replaceable>handler_function_name</Replaceable>
	LANCOMPILER '<Replaceable>description</Replaceable>';
		</ProgramListing>
		The optional keyword <Acronym>TRUSTED</Acronym> tells
		if ordinary database users that have no superuser
		privileges can use this language to create functions
		and trigger procedures. Since PL functions are
		executed inside the database backend it should only be used for
		languages that don't gain access to database backends
		internals or the filesystem. The languages PL/pgSQL and
		PL/Tcl are known to be trusted.
	    </Para>
	</Step>
</Procedure>

<Procedure>
    <Title>Example</Title>
    <Step Performance="Required">
    <Para>
        The following command tells the database where to find the 
	shared object for the PL/pgSQL languages call handler function.
    </Para>
    <ProgramListing>
    CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
        '/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C';
    </ProgramListing>
    </Step>

    <Step Performance="Required">
    <Para>
        The command
    </Para>
    <ProgramListing>
    CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
        HANDLER plpgsql_call_handler
        LANCOMPILER 'PL/pgSQL';
    </ProgramListing>
    <Para>
        then defines that the previously declared call handler
	function should be invoked for functions and trigger procedures
	where the language attribute is 'plpgsql'.
    </Para>
    <Para>
        PL handler functions have a special call interface that is
	different from regular C language functions. One of the arguments
	given to the handler is the object ID in the <FileName>pg_proc</FileName>
	tables entry for the function that should be executed.
        The handler examines various system catalogs to analyze the
	functions call arguments and it's return data type. The source
	text of the functions body is found in the prosrc attribute of
	<FileName>pg_proc</FileName>.
	Due to this, in contrast to C language functions, PL functions
	can be overloaded like SQL language functions. There can be
	multiple different PL functions having the same function name,
	as long as the call arguments differ.
    </Para>
    <Para>
        Procedural languages defined in the <FileName>template1</FileName>
	database are automatically defined in all subsequently created
	databases. So the database administrator can decide which
	languages are available by default.
    </Para>
    </Step>
</Procedure>
</Sect1> <!-- **** End of PL installation **** -->

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

<Sect1>
<Title>PL/pgSQL</Title>

<Para>
    PL/pgSQL is a loadable procedural language for the
    <ProductName>Postgres</ProductName> database system.
</Para>

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

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

<Sect2>
<Title>Overview</Title>

<Para>
    The design goals of PL/pgSQL were to create a loadable procedural
    language that
    <ItemizedList>
    <ListItem>
	<Para>
        can be used to create functions and trigger procedures,
	</Para>
    </ListItem>
    <ListItem>
	<Para>
        adds control structures to the <Acronym>SQL</Acronym> language,
	</Para>
    </ListItem>
    <ListItem>
	<Para>
        can perform complex computations,
	</Para>
    </ListItem>
    <ListItem>
	<Para>
        inherits all user defined types, functions and operators,
	</Para>
    </ListItem>
    <ListItem>
	<Para>
        can be defined to be trusted by the server,
	</Para>
    </ListItem>
    <ListItem>
	<Para>
        is easy to use.
	</Para>
    </ListItem>
    </ItemizedList>
</Para>
<Para>
    The PL/pgSQL call handler parses the functions source text and
    produces an internal binary instruction tree on the first time, the
    function is called by a backend. The produced bytecode is identified
    in the call handler by the object ID of the function. This ensures,
    that changing a function by a DROP/CREATE sequence will take effect
    without establishing a new database connection. 
</Para>
<Para>
    For all expressions and <Acronym>SQL</Acronym> statements used in
    the function, the PL/pgSQL bytecode interpreter creates a
    prepared execution plan using the SPI managers SPI_prepare() and
    SPI_saveplan() functions. This is done the first time, the individual
    statement is processed in the PL/pgSQL function. Thus, a function with
    conditional code that contains many statements for which execution
    plans would be required, will only prepare and save those plans
    that are really used during the entire lifetime of the database
    connection.
</Para>
<Para>
    Except for input-/output-conversion and calculation functions
    for user defined types, anything that can be defined in C language
    functions can also be done with PL/pgSQL. It is possible to
    create complex conditional computation functions and later use
    them to define operators or use them in functional indices.
</Para>
</Sect2>

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

<Sect2>
<Title>Description</Title>

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

<Sect3>
<Title>Structure of PL/pgSQL</Title>

<Para>
    The PL/pgSQL language is case insensitive. All keywords and
    identifiers can be used in mixed upper- and lowercase.
</Para>
<Para>
    PL/pgSQL is a block oriented language. A block is defined as

<ProgramListing>
    [&lt;&lt;label&gt;&gt;]
    [DECLARE
        <replaceable>declarations</replaceable>]
    BEGIN
        <replaceable>statements</replaceable>
    END;
</ProgramListing>

    There can be any number of subblocks in the statement section
    of a block. Subblocks can be used to hide variables from outside a
    block of statements. The variables
    declared in the declarations section preceding a block are
    initialized to their default values every time the block is entered,
    not only once per function call.
</Para>
  
<Para>
    It is important not to misunderstand the meaning of BEGIN/END for
    grouping statements in PL/pgSQL and the database commands for
    transaction control. Functions and trigger procedures cannot
    start or commit transactions and <ProductName>Postgres</ProductName>
    does not have nested transactions.
</Para>
</Sect3>

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

<Sect3>
<Title>Comments</Title>

<Para>
    There are two types of comments in PL/pgSQL. A double dash '--'
    starts a comment that extends to the end of the line. A '/*'
    starts a block comment that extends to the next occurence of '*/'.
    Block comments cannot be nested, but double dash comments can be
    enclosed into a block comment and a double dash can hide
    the block comment delimiters '/*' and '*/'.
</Para>
</Sect3>

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

<Sect3>
<Title>Declarations</Title>

<Para>
    All variables, rows and records used in a block or it's
    subblocks must be declared in the declarations section of a block
    except for the loop variable of a FOR loop iterating over a range
    of integer values. Parameters given to a PL/pgSQL function are
    automatically declared with the usual identifiers $n.
    The declarations have the following syntax:
</Para>
<VariableList>

<VarListEntry>
<Term>
<Replaceable>name</Replaceable> [ CONSTANT ] <Replaceable>type</Replaceable> [ NOT NULL ] [ DEFAULT | := <Replaceable>value</Replaceable> ];
</Term>
<ListItem>
<Para>
    Declares a variable of the specified base type. If the variable
    is declared as CONSTANT, the value cannot be changed. If NOT NULL
    is specified, an assignment of a NULL value results in a runtime
    error. Since the default value of all variables is the
    <Acronym>SQL</Acronym> NULL value, all variables declared as NOT NULL
    must also have a default value specified.
</Para>
<Para>
    The default value is evaluated ever time the function is called. So
    assigning '<Replaceable>now</Replaceable>' to a variable of type
    <Replaceable>datetime</Replaceable> causes the variable to have the
    time of the actual function call, not when the function was
    precompiled into it's bytecode.
</Para>
</ListItem>
</VarListEntry>

<VarListEntry>
<Term>
<Replaceable>name</Replaceable> <Replaceable>class</Replaceable>%ROWTYPE;
</Term>
<ListItem>
<Para>
    Declares a row with the structure of the given class. Class must be
    an existing table- or viewname of the database. The fields of the row
    are accessed in the dot notation. Parameters to a function can
    be composite types (complete table rows). In that case, the
    corresponding identifier $n will be a rowtype, but it
    must be aliased using the ALIAS command described below. Only the user
    attributes of a table row are accessible in the row, no Oid or other
    system attributes (hence the row could be from a view and view rows
    don't have useful system attributes).
</Para>
<Para>
    The fields of the rowtype inherit the tables fieldsizes 
    or precision for char() etc. data types.
</Para>
</ListItem>
</VarListEntry>

<VarListEntry>
<Term>
<Replaceable>name</Replaceable> RECORD;
</Term>
<ListItem>
<Para>
    Records are similar to rowtypes, but they have no predefined structure.
    They are used in selections and FOR loops to hold one actual
    database row from a SELECT operation. One and the same record can be
    used in different selections. Accessing a record or an attempt to assign
    a value to a record field when there is no actual row in it results
    in a runtime error.
</Para>
<Para>
    The NEW and OLD rows in a trigger are given to the procedure as
    records. This is necessary because in <ProductName>Postgres</ProductName>
    one and the same trigger procedure can handle trigger events for
    different tables.
</Para>
</ListItem>
</VarListEntry>

<VarListEntry>
<Term>
<Replaceable>name</Replaceable> ALIAS FOR $n;
</Term>
<ListItem>
<Para>
    For better readability of the code it is possible to define an alias
    for a positional parameter to a function.
</Para>
<Para>
    This aliasing is required for composite types given as arguments to
    a function. The dot notation $1.salary as in SQL functions is not
    allowed in PL/pgSQL.
</Para>
</ListItem>
</VarListEntry>

<VarListEntry>
<Term>
RENAME <Replaceable>oldname</Replaceable> TO <Replaceable>newname</Replaceable>;
</Term>
<ListItem>
<Para>
    Change the name of a variable, record or row. This is useful
    if NEW or OLD should be referenced by another name inside a 
    trigger procedure.
</Para>
</ListItem>
</VarListEntry>

</VariableList>
</Sect3>