File: plpgsql-expressions.html

package info (click to toggle)
pgadmin3 1.4.3-2
  • links: PTS
  • area: main
  • in suites: etch, etch-m68k
  • size: 29,796 kB
  • ctags: 10,758
  • sloc: cpp: 55,356; sh: 6,164; ansic: 1,520; makefile: 576; sql: 482; xml: 100; perl: 18
file content (94 lines) | stat: -rw-r--r-- 4,703 bytes parent folder | download
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>36.5.Expressions</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="plpgsql.html" title="Chapter36.PL/pgSQL - SQL Procedural Language">
<link rel="prev" href="plpgsql-declarations.html" title="36.4.Declarations">
<link rel="next" href="plpgsql-statements.html" title="36.6.Basic Statements">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="sect1" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="plpgsql-expressions"></a>36.5.Expressions</h2></div></div></div>
<p>     All expressions used in <span class="application">PL/pgSQL</span>
     statements are processed using the server's regular
     <acronym class="acronym">SQL</acronym> executor.  In effect, a query like
</p>
<pre class="synopsis">SELECT <em class="replaceable"><code>expression</code></em></pre>
<p>
     is executed using the <acronym class="acronym">SPI</acronym> manager. Before evaluation,
     occurrences of <span class="application">PL/pgSQL</span> 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 <code class="command">SELECT</code> to
     be prepared just once and then reused for subsequent
     evaluations.
    </p>
<p>     The evaluation done by the <span class="productname">PostgreSQL</span>
     main parser has some side
     effects on the interpretation of constant values. In detail there
     is a difference between what these two functions do:

</p>
<pre class="programlisting">CREATE FUNCTION logfunc1(logtxt text) RETURNS timestamp AS $$
    BEGIN
        INSERT INTO logtable VALUES (logtxt, 'now');
        RETURN 'now';
    END;
$$ LANGUAGE plpgsql;</pre>
<p>

     and

</p>
<pre class="programlisting">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;</pre>
<p>
    </p>
<p>     In the case of <code class="function">logfunc1</code>, the
     <span class="productname">PostgreSQL</span> main parser knows when
     preparing the plan for the <code class="command">INSERT</code> that the
     string <code class="literal">'now'</code> should be interpreted as
     <code class="type">timestamp</code> because the target column of
     <code class="classname">logtable</code> is of that type. Thus,
     <code class="literal">'now'</code> will be converted to a constant when the
     <code class="command">INSERT</code> is planned, and then used in all
     invocations of <code class="function">logfunc1</code> during the lifetime
     of the session. Needless to say, this isn't what the programmer
     wanted.
    </p>
<p>     In the case of <code class="function">logfunc2</code>, the 
     <span class="productname">PostgreSQL</span> main parser does not know
     what type <code class="literal">'now'</code> should become and therefore 
     it returns a data value of type <code class="type">text</code> containing the string 
     <code class="literal">now</code>. During the ensuing assignment
     to the local variable <code class="varname">curtime</code>, the
     <span class="application">PL/pgSQL</span> interpreter casts this
     string to the <code class="type">timestamp</code> type by calling the
     <code class="function">text_out</code> and <code class="function">timestamp_in</code>
     functions for the conversion.  So, the computed time stamp is updated
     on each execution as the programmer expects.
    </p>
<p>     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.  (<code class="command">EXECUTE</code> can be used to get around
     this problem when necessary.)
    </p>
</div></body>
</html>