| PostgreSQL 8.2.10 Documentation | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 37. PL/pgSQL - SQL Procedural Language | Fast Forward | Next | 
All expressions used in PL/pgSQL statements are processed using the server's regular SQL executor. In effect, a query like
SELECT expression
is executed using the SPI manager. Before evaluation, occurrences of PL/pgSQL variable identifiers are replaced by parameters, and the actual values from the variables are passed to the executor in the parameter array. This allows the query plan for the SELECT to be prepared just once and then reused for subsequent evaluations.
The evaluation done by the PostgreSQL main parser has some side effects on the interpretation of constant values. In detail there is a difference between what these two functions do:
CREATE FUNCTION logfunc1(logtxt text) RETURNS timestamp AS $$
    BEGIN
        INSERT INTO logtable VALUES (logtxt, 'now');
        RETURN 'now';
    END;
$$ LANGUAGE plpgsql;and
CREATE FUNCTION logfunc2(logtxt text) RETURNS timestamp AS $$
    DECLARE
        curtime timestamp;
    BEGIN
        curtime := 'now';
        INSERT INTO logtable VALUES (logtxt, curtime);
        RETURN curtime;
    END;
$$ LANGUAGE plpgsql;
     In the case of logfunc1, the
     PostgreSQL main parser knows when
     preparing the plan for the INSERT that the
     string 'now' should be interpreted as
     timestamp because the target column of
     logtable is of that type. Thus,
     'now' will be converted to a constant when the
     INSERT is planned, and then used in all
     invocations of logfunc1 during the lifetime
     of the session. Needless to say, this isn't what the programmer
     wanted.
    
     In the case of logfunc2, the 
     PostgreSQL main parser does not know
     what type 'now' should become and therefore 
     it returns a data value of type text containing the string 
     now. During the ensuing assignment
     to the local variable curtime, the
     PL/pgSQL interpreter casts this
     string to the timestamp type by calling the
     text_out and timestamp_in
     functions for the conversion.  So, the computed time stamp is updated
     on each execution as the programmer expects.
    
The mutable nature of record variables presents a problem in this connection. When fields of a record variable are used in expressions or statements, the data types of the fields must not change between calls of one and the same expression, since the expression will be planned using the data type that is present when the expression is first reached. Keep this in mind when writing trigger procedures that handle events for more than one table. (EXECUTE can be used to get around this problem when necessary.)