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
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>36.6.Basic Statements</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-expressions.html" title="36.5.Expressions">
<link rel="next" href="plpgsql-control-structures.html" title="36.7.Control Structures">
<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-statements"></a>36.6.Basic Statements</h2></div></div></div>
<p> In this section and the following ones, we describe all the statement
types that are explicitly understood by
<span class="application">PL/pgSQL</span>.
Anything not recognized as one of these statement types is presumed
to be an SQL command and is sent to the main database engine to execute
(after substitution of any <span class="application">PL/pgSQL</span> variables
used in the statement). Thus,
for example, the SQL commands <code class="command">INSERT</code>, <code class="command">UPDATE</code>, and
<code class="command">DELETE</code> may be considered to be statements of
<span class="application">PL/pgSQL</span>, but they are not specifically
listed here.
</p>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="plpgsql-statements-assignment"></a>36.6.1.Assignment</h3></div></div></div>
<p> An assignment of a value to a variable or row/record field is
written as:
</p>
<pre class="synopsis"><em class="replaceable"><code>identifier</code></em> := <em class="replaceable"><code>expression</code></em>;</pre>
<p>
As explained above, the expression in such a statement is evaluated
by means of an SQL <code class="command">SELECT</code> command sent to the main
database engine. The expression must yield a single value.
</p>
<p> If the expression's result data type doesn't match the variable's
data type, or the variable has a specific size/precision
(like <code class="type">char(20)</code>), the result value will be implicitly
converted by the <span class="application">PL/pgSQL</span> interpreter using
the result type's output-function and
the variable type's input-function. Note that this could potentially
result in run-time errors generated by the input function, if the
string form of the result value is not acceptable to the input function.
</p>
<p> Examples:
</p>
<pre class="programlisting">user_id := 20;
tax := subtotal * 0.06;</pre>
<p>
</p>
</div>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="plpgsql-select-into"></a>36.6.2.<code class="command">SELECT INTO</code></h3></div></div></div>
<a name="id723033"></a><p> The result of a <code class="command">SELECT</code> command yielding multiple columns (but
only one row) can be assigned to a record variable, row-type
variable, or list of scalar variables. This is done by:
</p>
<pre class="synopsis">SELECT INTO <em class="replaceable"><code>target</code></em> <em class="replaceable"><code>select_expressions</code></em> FROM ...;</pre>
<p>
where <em class="replaceable"><code>target</code></em> can be a record variable, a row
variable, or a comma-separated list of simple variables and
record/row fields. The <em class="replaceable"><code>select_expressions</code></em>
and the remainder of the command are the same as in regular SQL.
</p>
<p> Note that this is quite different from
<span class="productname">PostgreSQL</span>'s normal interpretation of
<code class="command">SELECT INTO</code>, where the <code class="literal">INTO</code> target
is a newly created table. If you want to create a table from a
<code class="command">SELECT</code> result inside a
<span class="application">PL/pgSQL</span> function, use the syntax
<code class="command">CREATE TABLE ... AS SELECT</code>.
</p>
<p> If a row or a variable list is used as target, the selected values
must exactly match the structure of the target, or a run-time error
occurs. When a record variable is the target, it automatically
configures itself to the row type of the query result columns.
</p>
<p> Except for the <code class="literal">INTO</code> clause, the <code class="command">SELECT</code>
statement is the same as a normal SQL <code class="command">SELECT</code> command
and can use its full power.
</p>
<p> The <code class="literal">INTO</code> clause can appear almost anywhere in the
<code class="command">SELECT</code> statement. Customarily it is written
either just after <code class="literal">SELECT</code> as shown above, or
just before <code class="literal">FROM</code> [mdash ] that is, either just before
or just after the list of <em class="replaceable"><code>select_expressions</code></em>.
</p>
<p> If the query returns zero rows, null values are assigned to the
target(s). If the query returns multiple rows, the first
row is assigned to the target(s) and the rest are discarded.
(Note that “<span class="quote">the first row</span>” is not well-defined unless you've
used <code class="literal">ORDER BY</code>.)
</p>
<p> You can check the special <code class="literal">FOUND</code> variable (see
<a href="plpgsql-statements.html#plpgsql-statements-diagnostics" title="36.6.6.Obtaining the Result Status">Section36.6.6, “Obtaining the Result Status”</a>) after a
<code class="command">SELECT INTO</code> statement to determine whether the
assignment was successful, that is, at least one row was was returned by
the query. For example:
</p>
<pre class="programlisting">SELECT INTO myrec * FROM emp WHERE empname = myname;
IF NOT FOUND THEN
RAISE EXCEPTION 'employee % not found', myname;
END IF;</pre>
<p>
</p>
<p> To test for whether a record/row result is null, you can use the
<code class="literal">IS NULL</code> conditional. There is, however, no
way to tell whether any additional rows might have been
discarded. Here is an example that handles the case where no
rows have been returned:
</p>
<pre class="programlisting">DECLARE
users_rec RECORD;
BEGIN
SELECT INTO users_rec * FROM users WHERE user_id=3;
IF users_rec.homepage IS NULL THEN
-- user entered no homepage, return "http://"
RETURN 'http://';
END IF;
END;</pre>
<p>
</p>
</div>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="plpgsql-statements-perform"></a>36.6.3.Executing an Expression or Query With No Result</h3></div></div></div>
<p> Sometimes one wishes to evaluate an expression or query but
discard the result (typically because one is calling a function
that has useful side-effects but no useful result value). To do
this in <span class="application">PL/pgSQL</span>, use the
<code class="command">PERFORM</code> statement:
</p>
<pre class="synopsis">PERFORM <em class="replaceable"><code>query</code></em>;</pre>
<p>
This executes <em class="replaceable"><code>query</code></em> and discards the
result. Write the <em class="replaceable"><code>query</code></em> the same
way as you would in an SQL <code class="command">SELECT</code> command, but replace the
initial keyword <code class="command">SELECT</code> with <code class="command">PERFORM</code>.
<span class="application">PL/pgSQL</span> variables will be
substituted into the query as usual. Also, the special variable
<code class="literal">FOUND</code> is set to true if the query produced at
least one row or false if it produced no rows.
</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
<h3 class="title">Note</h3>
<p> One might expect that <code class="command">SELECT</code> with no
<code class="literal">INTO</code> clause would accomplish this result, but at
present the only accepted way to do it is
<code class="command">PERFORM</code>.
</p>
</div>
<p> An example:
</p>
<pre class="programlisting">PERFORM create_mv('cs_session_page_requests_mv', my_query);</pre>
<p>
</p>
</div>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="plpgsql-statements-null"></a>36.6.4.Doing Nothing At All</h3></div></div></div>
<p> Sometimes a placeholder statement that does nothing is useful.
For example, it can indicate that one arm of an if/then/else
chain is deliberately empty. For this purpose, use the
<code class="command">NULL</code> statement:
</p>
<pre class="synopsis">NULL;</pre>
<p>
</p>
<p> For example, the following two fragments of code are equivalent:
</p>
<pre class="programlisting"> BEGIN
y := x / 0;
EXCEPTION
WHEN division_by_zero THEN
NULL; -- ignore the error
END;</pre>
<p>
</p>
<pre class="programlisting"> BEGIN
y := x / 0;
EXCEPTION
WHEN division_by_zero THEN -- ignore the error
END;</pre>
<p>
Which is preferable is a matter of taste.
</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
<h3 class="title">Note</h3>
<p> In Oracle's PL/SQL, empty statement lists are not allowed, and so
<code class="command">NULL</code> statements are <span class="emphasis"><em>required</em></span> for situations
such as this. <span class="application">PL/pgSQL</span> allows you to
just write nothing, instead.
</p>
</div>
</div>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="plpgsql-statements-executing-dyn"></a>36.6.5.Executing Dynamic Commands</h3></div></div></div>
<p> Oftentimes you will want to generate dynamic commands inside your
<span class="application">PL/pgSQL</span> functions, that is, commands
that will involve different tables or different data types each
time they are executed. <span class="application">PL/pgSQL</span>'s
normal attempts to cache plans for commands will not work in such
scenarios. To handle this sort of problem, the
<code class="command">EXECUTE</code> statement is provided:
</p>
<pre class="synopsis">EXECUTE <em class="replaceable"><code>command-string</code></em> [ INTO <em class="replaceable"><code>target</code></em> ];</pre>
<p>
where <em class="replaceable"><code>command-string</code></em> is an expression
yielding a string (of type <code class="type">text</code>) containing the
command to be executed and <em class="replaceable"><code>target</code></em> is a
record variable, row variable, or a comma-separated list of
simple variables and record/row fields.
</p>
<p> Note in particular that no substitution of <span class="application">PL/pgSQL</span>
variables is done on the command string. The values of variables must
be inserted in the command string as it is constructed.
</p>
<p> Unlike all other commands in <span class="application">PL/pgSQL</span>, a command
run by an <code class="command">EXECUTE</code> statement is not prepared
and saved just once during the life of the session. Instead, the
command is prepared each time the statement is run. The command
string can be dynamically created within the function to perform
actions on different tables and columns.
</p>
<p> The <code class="literal">INTO</code> clause specifies where the results of
a <code class="command">SELECT</code> command should be assigned. If a row
or variable list is provided, it must exactly match the structure
of the results produced by the <code class="command">SELECT</code> (when a
record variable is used, it will configure itself to match the
result's structure automatically). If multiple rows are returned,
only the first will be assigned to the <code class="literal">INTO</code>
variable. If no rows are returned, NULL is assigned to the
<code class="literal">INTO</code> variable. If no <code class="literal">INTO</code>
clause is specified, the results of a <code class="command">SELECT</code>
command are discarded.
</p>
<p> <code class="command">SELECT INTO</code> is not currently supported within
<code class="command">EXECUTE</code>.
</p>
<p> When working with dynamic commands you will often have to handle escaping
of single quotes. The recommended method for quoting fixed text in your
function body is dollar quoting. (If you have legacy code that does
not use dollar quoting, please refer to the
overview in <a href="plpgsql-development-tips.html#plpgsql-quote-tips" title="36.2.1.Handling of Quotation Marks">Section36.2.1, “Handling of Quotation Marks”</a>, which can save you
some effort when translating said code to a more reasonable scheme.)
</p>
<p> Dynamic values that are to be inserted into the constructed
query require special handling since they might themselves contain
quote characters.
An example (this assumes that you are using dollar quoting for the
function as a whole, so the quote marks need not be doubled):
</p>
<pre class="programlisting">EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
|| ' = '
|| quote_literal(newvalue)
|| ' WHERE key = '
|| quote_literal(keyvalue);</pre>
<p>
</p>
<a name="id723692"></a><a name="id723701"></a><p> This example demonstrates the use of the
<code class="function">quote_ident</code> and
<code class="function">quote_literal</code> functions. For safety,
expressions containing column and table identifiers should be
passed to <code class="function">quote_ident</code>. Expressions containing
values that should be literal strings in the constructed command
should be passed to <code class="function">quote_literal</code>. Both
take the appropriate steps to return the input text enclosed in
double or single quotes respectively, with any embedded special
characters properly escaped.
</p>
<p> Note that dollar quoting is only useful for quoting fixed text.
It would be a very bad idea to try to do the above example as
</p>
<pre class="programlisting">EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
|| ' = $$'
|| newvalue
|| '$$ WHERE key = '
|| quote_literal(keyvalue);</pre>
<p>
because it would break if the contents of <code class="literal">newvalue</code>
happened to contain <code class="literal">$$</code>. The same objection would
apply to any other dollar-quoting delimiter you might pick.
So, to safely quote text that is not known in advance, you
<span class="emphasis"><em>must</em></span> use <code class="function">quote_literal</code>.
</p>
<p> A much larger example of a dynamic command and
<code class="command">EXECUTE</code> can be seen in <a href="plpgsql-porting.html#plpgsql-porting-ex2" title="Example36.6.Porting a Function that Creates Another Function from PL/SQL to PL/pgSQL">Example36.6, “Porting a Function that Creates Another Function from <span class="application">PL/SQL</span> to <span class="application">PL/pgSQL</span>”</a>, which builds and executes a
<code class="command">CREATE FUNCTION</code> command to define a new function.
</p>
</div>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="plpgsql-statements-diagnostics"></a>36.6.6.Obtaining the Result Status</h3></div></div></div>
<p> There are several ways to determine the effect of a command. The
first method is to use the <code class="command">GET DIAGNOSTICS</code>
command, which has the form:
</p>
<pre class="synopsis">GET DIAGNOSTICS <em class="replaceable"><code>variable</code></em> = <em class="replaceable"><code>item</code></em> [<span class="optional"> , ... </span>];</pre>
<p>
This command allows retrieval of system status indicators. Each
<em class="replaceable"><code>item</code></em> is a key word identifying a state
value to be assigned to the specified variable (which should be
of the right data type to receive it). The currently available
status items are <code class="varname">ROW_COUNT</code>, the number of rows
processed by the last <acronym class="acronym">SQL</acronym> command sent down to
the <acronym class="acronym">SQL</acronym> engine, and <code class="varname">RESULT_OID</code>,
the OID of the last row inserted by the most recent
<acronym class="acronym">SQL</acronym> command. Note that <code class="varname">RESULT_OID</code>
is only useful after an <code class="command">INSERT</code> command into a
table containing OIDs.
</p>
<p> An example:
</p>
<pre class="programlisting">GET DIAGNOSTICS integer_var = ROW_COUNT;</pre>
<p>
</p>
<p> The second method to determine the effects of a command is to check the
special variable named <code class="literal">FOUND</code>, which is of
type <code class="type">boolean</code>. <code class="literal">FOUND</code> starts out
false within each <span class="application">PL/pgSQL</span> function call.
It is set by each of the following types of statements:
</p>
<div class="itemizedlist"><ul type="disc">
<li><p> A <code class="command">SELECT INTO</code> statement sets
<code class="literal">FOUND</code> true if it returns a row, false if no
row is returned.
</p></li>
<li><p> A <code class="command">PERFORM</code> statement sets <code class="literal">FOUND</code>
true if it produces (and discards) a row, false if no row is
produced.
</p></li>
<li><p> <code class="command">UPDATE</code>, <code class="command">INSERT</code>, and <code class="command">DELETE</code>
statements set <code class="literal">FOUND</code> true if at least one
row is affected, false if no row is affected.
</p></li>
<li><p> A <code class="command">FETCH</code> statement sets <code class="literal">FOUND</code>
true if it returns a row, false if no row is returned.
</p></li>
<li><p> A <code class="command">FOR</code> statement sets <code class="literal">FOUND</code> true
if it iterates one or more times, else false. This applies to
all three variants of the <code class="command">FOR</code> statement (integer
<code class="command">FOR</code> loops, record-set <code class="command">FOR</code> loops, and
dynamic record-set <code class="command">FOR</code>
loops). <code class="literal">FOUND</code> is set this way when the
<code class="command">FOR</code> loop exits; inside the execution of the loop,
<code class="literal">FOUND</code> is not modified by the
<code class="command">FOR</code> statement, although it may be changed by the
execution of other statements within the loop body.
</p></li>
</ul></div>
<p>
<code class="literal">FOUND</code> is a local variable within each
<span class="application">PL/pgSQL</span> function; any changes to it
affect only the current function.
</p>
</div>
</div></body>
</html>
|
