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
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>43.11. PL/pgSQL under the Hood</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="plpgsql-trigger.html" title="43.10. Trigger Functions" /><link rel="next" href="plpgsql-development-tips.html" title="43.12. Tips for Developing in PL/pgSQL" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">43.11. <span class="application">PL/pgSQL</span> under the Hood</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="plpgsql-trigger.html" title="43.10. Trigger Functions">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="plpgsql.html" title="Chapter 43. PL/pgSQL — SQL Procedural Language">Up</a></td><th width="60%" align="center">Chapter 43. <span class="application">PL/pgSQL</span> — <acronym class="acronym">SQL</acronym> Procedural Language</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.2 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="plpgsql-development-tips.html" title="43.12. Tips for Developing in PL/pgSQL">Next</a></td></tr></table><hr /></div><div class="sect1" id="PLPGSQL-IMPLEMENTATION"><div class="titlepage"><div><div><h2 class="title" style="clear: both">43.11. <span class="application">PL/pgSQL</span> under the Hood <a href="#PLPGSQL-IMPLEMENTATION" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="plpgsql-implementation.html#PLPGSQL-VAR-SUBST">43.11.1. Variable Substitution</a></span></dt><dt><span class="sect2"><a href="plpgsql-implementation.html#PLPGSQL-PLAN-CACHING">43.11.2. Plan Caching</a></span></dt></dl></div><p>
This section discusses some implementation details that are
frequently important for <span class="application">PL/pgSQL</span> users to know.
</p><div class="sect2" id="PLPGSQL-VAR-SUBST"><div class="titlepage"><div><div><h3 class="title">43.11.1. Variable Substitution <a href="#PLPGSQL-VAR-SUBST" class="id_link">#</a></h3></div></div></div><p>
SQL statements and expressions within a <span class="application">PL/pgSQL</span> function
can refer to variables and parameters of the function. Behind the scenes,
<span class="application">PL/pgSQL</span> substitutes query parameters for such references.
Query parameters will only be substituted in places where they are
syntactically permissible. As an extreme case, consider
this example of poor programming style:
</p><pre class="programlisting">
INSERT INTO foo (foo) VALUES (foo(foo));
</pre><p>
The first occurrence of <code class="literal">foo</code> must syntactically be a table
name, so it will not be substituted, even if the function has a variable
named <code class="literal">foo</code>. The second occurrence must be the name of a
column of that table, so it will not be substituted either. Likewise
the third occurrence must be a function name, so it also will not be
substituted for. Only the last occurrence is a candidate to be a
reference to a variable of the <span class="application">PL/pgSQL</span>
function.
</p><p>
Another way to understand this is that variable substitution can only
insert data values into an SQL command; it cannot dynamically change which
database objects are referenced by the command. (If you want to do
that, you must build a command string dynamically, as explained in
<a class="xref" href="plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN" title="43.5.4. Executing Dynamic Commands">Section 43.5.4</a>.)
</p><p>
Since the names of variables are syntactically no different from the names
of table columns, there can be ambiguity in statements that also refer to
tables: is a given name meant to refer to a table column, or a variable?
Let's change the previous example to
</p><pre class="programlisting">
INSERT INTO dest (col) SELECT foo + bar FROM src;
</pre><p>
Here, <code class="literal">dest</code> and <code class="literal">src</code> must be table names, and
<code class="literal">col</code> must be a column of <code class="literal">dest</code>, but <code class="literal">foo</code>
and <code class="literal">bar</code> might reasonably be either variables of the function
or columns of <code class="literal">src</code>.
</p><p>
By default, <span class="application">PL/pgSQL</span> will report an error if a name
in an SQL statement could refer to either a variable or a table column.
You can fix such a problem by renaming the variable or column,
or by qualifying the ambiguous reference, or by telling
<span class="application">PL/pgSQL</span> which interpretation to prefer.
</p><p>
The simplest solution is to rename the variable or column.
A common coding rule is to use a
different naming convention for <span class="application">PL/pgSQL</span>
variables than you use for column names. For example,
if you consistently name function variables
<code class="literal">v_<em class="replaceable"><code>something</code></em></code> while none of your
column names start with <code class="literal">v_</code>, no conflicts will occur.
</p><p>
Alternatively you can qualify ambiguous references to make them clear.
In the above example, <code class="literal">src.foo</code> would be an unambiguous reference
to the table column. To create an unambiguous reference to a variable,
declare it in a labeled block and use the block's label
(see <a class="xref" href="plpgsql-structure.html" title="43.2. Structure of PL/pgSQL">Section 43.2</a>). For example,
</p><pre class="programlisting">
<<block>>
DECLARE
foo int;
BEGIN
foo := ...;
INSERT INTO dest (col) SELECT block.foo + bar FROM src;
</pre><p>
Here <code class="literal">block.foo</code> means the variable even if there is a column
<code class="literal">foo</code> in <code class="literal">src</code>. Function parameters, as well as
special variables such as <code class="literal">FOUND</code>, can be qualified by the
function's name, because they are implicitly declared in an outer block
labeled with the function's name.
</p><p>
Sometimes it is impractical to fix all the ambiguous references in a
large body of <span class="application">PL/pgSQL</span> code. In such cases you can
specify that <span class="application">PL/pgSQL</span> should resolve ambiguous references
as the variable (which is compatible with <span class="application">PL/pgSQL</span>'s
behavior before <span class="productname">PostgreSQL</span> 9.0), or as the
table column (which is compatible with some other systems such as
<span class="productname">Oracle</span>).
</p><a id="id-1.8.8.13.3.9" class="indexterm"></a><p>
To change this behavior on a system-wide basis, set the configuration
parameter <code class="literal">plpgsql.variable_conflict</code> to one of
<code class="literal">error</code>, <code class="literal">use_variable</code>, or
<code class="literal">use_column</code> (where <code class="literal">error</code> is the factory default).
This parameter affects subsequent compilations
of statements in <span class="application">PL/pgSQL</span> functions, but not statements
already compiled in the current session.
Because changing this setting
can cause unexpected changes in the behavior of <span class="application">PL/pgSQL</span>
functions, it can only be changed by a superuser.
</p><p>
You can also set the behavior on a function-by-function basis, by
inserting one of these special commands at the start of the function
text:
</p><pre class="programlisting">
#variable_conflict error
#variable_conflict use_variable
#variable_conflict use_column
</pre><p>
These commands affect only the function they are written in, and override
the setting of <code class="literal">plpgsql.variable_conflict</code>. An example is
</p><pre class="programlisting">
CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$
#variable_conflict use_variable
DECLARE
curtime timestamp := now();
BEGIN
UPDATE users SET last_modified = curtime, comment = comment
WHERE users.id = id;
END;
$$ LANGUAGE plpgsql;
</pre><p>
In the <code class="literal">UPDATE</code> command, <code class="literal">curtime</code>, <code class="literal">comment</code>,
and <code class="literal">id</code> will refer to the function's variable and parameters
whether or not <code class="literal">users</code> has columns of those names. Notice
that we had to qualify the reference to <code class="literal">users.id</code> in the
<code class="literal">WHERE</code> clause to make it refer to the table column.
But we did not have to qualify the reference to <code class="literal">comment</code>
as a target in the <code class="literal">UPDATE</code> list, because syntactically
that must be a column of <code class="literal">users</code>. We could write the same
function without depending on the <code class="literal">variable_conflict</code> setting
in this way:
</p><pre class="programlisting">
CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$
<<fn>>
DECLARE
curtime timestamp := now();
BEGIN
UPDATE users SET last_modified = fn.curtime, comment = stamp_user.comment
WHERE users.id = stamp_user.id;
END;
$$ LANGUAGE plpgsql;
</pre><p>
</p><p>
Variable substitution does not happen in a command string given
to <code class="command">EXECUTE</code> or one of its variants. If you need to
insert a varying value into such a command, do so as part of
constructing the string value, or use <code class="literal">USING</code>, as illustrated in
<a class="xref" href="plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN" title="43.5.4. Executing Dynamic Commands">Section 43.5.4</a>.
</p><p>
Variable substitution currently works only in <code class="command">SELECT</code>,
<code class="command">INSERT</code>, <code class="command">UPDATE</code>,
<code class="command">DELETE</code>, and commands containing one of
these (such as <code class="command">EXPLAIN</code> and <code class="command">CREATE TABLE
... AS SELECT</code>),
because the main SQL engine allows query parameters only in these
commands. To use a non-constant name or value in other statement
types (generically called utility statements), you must construct
the utility statement as a string and <code class="command">EXECUTE</code> it.
</p></div><div class="sect2" id="PLPGSQL-PLAN-CACHING"><div class="titlepage"><div><div><h3 class="title">43.11.2. Plan Caching <a href="#PLPGSQL-PLAN-CACHING" class="id_link">#</a></h3></div></div></div><p>
The <span class="application">PL/pgSQL</span> interpreter parses the function's source
text and produces an internal binary instruction tree the first time the
function is called (within each session). The instruction tree
fully translates the
<span class="application">PL/pgSQL</span> statement structure, but individual
<acronym class="acronym">SQL</acronym> expressions and <acronym class="acronym">SQL</acronym> commands
used in the function are not translated immediately.
</p><p>
<a id="id-1.8.8.13.4.3.1" class="indexterm"></a>
As each expression and <acronym class="acronym">SQL</acronym> command is first
executed in the function, the <span class="application">PL/pgSQL</span> interpreter
parses and analyzes the command to create a prepared statement,
using the <acronym class="acronym">SPI</acronym> manager's
<code class="function">SPI_prepare</code> function.
Subsequent visits to that expression or command
reuse the prepared statement. Thus, a function with conditional code
paths that are seldom visited will never incur the overhead of
analyzing those commands that are never executed within the current
session. A disadvantage is that errors
in a specific expression or command cannot be detected until that
part of the function is reached in execution. (Trivial syntax
errors will be detected during the initial parsing pass, but
anything deeper will not be detected until execution.)
</p><p>
<span class="application">PL/pgSQL</span> (or more precisely, the SPI manager) can
furthermore attempt to cache the execution plan associated with any
particular prepared statement. If a cached plan is not used, then
a fresh execution plan is generated on each visit to the statement,
and the current parameter values (that is, <span class="application">PL/pgSQL</span>
variable values) can be used to optimize the selected plan. If the
statement has no parameters, or is executed many times, the SPI manager
will consider creating a <em class="firstterm">generic</em> plan that is not dependent
on specific parameter values, and caching that for re-use. Typically
this will happen only if the execution plan is not very sensitive to
the values of the <span class="application">PL/pgSQL</span> variables referenced in it.
If it is, generating a plan each time is a net win. See <a class="xref" href="sql-prepare.html" title="PREPARE"><span class="refentrytitle">PREPARE</span></a> for more information about the behavior of
prepared statements.
</p><p>
Because <span class="application">PL/pgSQL</span> saves prepared statements
and sometimes execution plans in this way,
SQL commands that appear directly in a
<span class="application">PL/pgSQL</span> function must refer to the
same tables and columns on every execution; that is, you cannot use
a parameter as the name of a table or column in an SQL command. To get
around this restriction, you can construct dynamic commands using
the <span class="application">PL/pgSQL</span> <code class="command">EXECUTE</code>
statement — at the price of performing new parse analysis and
constructing a new execution plan on every execution.
</p><p>
The mutable nature of record variables presents another 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 from one call of the function to the next, since each
expression will be analyzed using the data type that is present
when the expression is first reached. <code class="command">EXECUTE</code> can be
used to get around this problem when necessary.
</p><p>
If the same function is used as a trigger for more than one table,
<span class="application">PL/pgSQL</span> prepares and caches statements
independently for each such table — that is, there is a cache
for each trigger function and table combination, not just for each
function. This alleviates some of the problems with varying
data types; for instance, a trigger function will be able to work
successfully with a column named <code class="literal">key</code> even if it happens
to have different types in different tables.
</p><p>
Likewise, functions having polymorphic argument types have a separate
statement cache for each combination of actual argument types they have
been invoked for, so that data type differences do not cause unexpected
failures.
</p><p>
Statement caching can sometimes have surprising effects on the
interpretation of time-sensitive values. For example there
is a difference between what these two functions do:
</p><pre class="programlisting">
CREATE FUNCTION logfunc1(logtxt text) RETURNS void AS $$
BEGIN
INSERT INTO logtable VALUES (logtxt, 'now');
END;
$$ LANGUAGE plpgsql;
</pre><p>
and:
</p><pre class="programlisting">
CREATE FUNCTION logfunc2(logtxt text) RETURNS void AS $$
DECLARE
curtime timestamp;
BEGIN
curtime := 'now';
INSERT INTO logtable VALUES (logtxt, 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
analyzing 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 <code class="type">timestamp</code>
constant when the
<code class="command">INSERT</code> is analyzed, 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. A better idea is to use the <code class="literal">now()</code> or
<code class="literal">current_timestamp</code> function.
</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">textout</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. Even though this
happens to work as expected, it's not terribly efficient, so
use of the <code class="literal">now()</code> function would still be a better idea.
</p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="plpgsql-trigger.html" title="43.10. Trigger Functions">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="plpgsql.html" title="Chapter 43. PL/pgSQL — SQL Procedural Language">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="plpgsql-development-tips.html" title="43.12. Tips for Developing in PL/pgSQL">Next</a></td></tr><tr><td width="40%" align="left" valign="top">43.10. Trigger Functions </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.2 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 43.12. Tips for Developing in <span class="application">PL/pgSQL</span></td></tr></table></div></body></html>
|
