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
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
|
<?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.5. Basic Statements</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-expressions.html" title="43.4. Expressions" /><link rel="next" href="plpgsql-control-structures.html" title="43.6. Control Structures" /></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.5. Basic Statements</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="plpgsql-expressions.html" title="43.4. Expressions">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-control-structures.html" title="43.6. Control Structures">Next</a></td></tr></table><hr /></div><div class="sect1" id="PLPGSQL-STATEMENTS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">43.5. Basic Statements <a href="#PLPGSQL-STATEMENTS" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT">43.5.1. Assignment</a></span></dt><dt><span class="sect2"><a href="plpgsql-statements.html#PLPGSQL-STATEMENTS-GENERAL-SQL">43.5.2. Executing SQL Commands</a></span></dt><dt><span class="sect2"><a href="plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW">43.5.3. Executing a Command with a Single-Row Result</a></span></dt><dt><span class="sect2"><a href="plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN">43.5.4. Executing Dynamic Commands</a></span></dt><dt><span class="sect2"><a href="plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS">43.5.5. Obtaining the Result Status</a></span></dt><dt><span class="sect2"><a href="plpgsql-statements.html#PLPGSQL-STATEMENTS-NULL">43.5.6. Doing Nothing At All</a></span></dt></dl></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,
as described in <a class="xref" href="plpgsql-statements.html#PLPGSQL-STATEMENTS-GENERAL-SQL" title="43.5.2. Executing SQL Commands">Section 43.5.2</a>.
</p><div class="sect2" id="PLPGSQL-STATEMENTS-ASSIGNMENT"><div class="titlepage"><div><div><h3 class="title">43.5.1. Assignment <a href="#PLPGSQL-STATEMENTS-ASSIGNMENT" class="id_link">#</a></h3></div></div></div><p>
An assignment of a value to a <span class="application">PL/pgSQL</span>
variable is written as:
</p><pre class="synopsis">
<em class="replaceable"><code>variable</code></em> { := | = } <em class="replaceable"><code>expression</code></em>;
</pre><p>
As explained previously, 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 (possibly
a row value, if the variable is a row or record variable). The target
variable can be a simple variable (optionally qualified with a block
name), a field of a row or record target, or an element or slice of
an array target. Equal (<code class="literal">=</code>) can be
used instead of PL/SQL-compliant <code class="literal">:=</code>.
</p><p>
If the expression's result data type doesn't match the variable's
data type, the value will be coerced as though by an assignment cast
(see <a class="xref" href="typeconv-query.html" title="10.4. Value Storage">Section 10.4</a>). If no assignment cast is known
for the pair of data types involved, the <span class="application">PL/pgSQL</span>
interpreter will attempt to convert the result value textually, that is
by applying the result type's output function followed by the variable
type's input function. Note that this could 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">
tax := subtotal * 0.06;
my_record.user_id := 20;
my_array[j] := 20;
my_array[1:3] := array[1,2,3];
complex_array[n].realpart = 12.3;
</pre><p>
</p></div><div class="sect2" id="PLPGSQL-STATEMENTS-GENERAL-SQL"><div class="titlepage"><div><div><h3 class="title">43.5.2. Executing SQL Commands <a href="#PLPGSQL-STATEMENTS-GENERAL-SQL" class="id_link">#</a></h3></div></div></div><p>
In general, any SQL command that does not return rows can be executed
within a <span class="application">PL/pgSQL</span> function just by writing
the command. For example, you could create and fill a table by writing
</p><pre class="programlisting">
CREATE TABLE mytable (id int primary key, data text);
INSERT INTO mytable VALUES (1,'one'), (2,'two');
</pre><p>
</p><p>
If the command does return rows (for example <code class="command">SELECT</code>,
or <code class="command">INSERT</code>/<code class="command">UPDATE</code>/<code class="command">DELETE</code>
with <code class="literal">RETURNING</code>), there are two ways to proceed.
When the command will return at most one row, or you only care about
the first row of output, write the command as usual but add
an <code class="literal">INTO</code> clause to capture the output, as described
in <a class="xref" href="plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW" title="43.5.3. Executing a Command with a Single-Row Result">Section 43.5.3</a>.
To process all of the output rows, write the command as the data
source for a <code class="command">FOR</code> loop, as described in
<a class="xref" href="plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING" title="43.6.6. Looping through Query Results">Section 43.6.6</a>.
</p><p>
Usually it is not sufficient just to execute statically-defined SQL
commands. Typically you'll want a command to use varying data values,
or even to vary in more fundamental ways such as by using different
table names at different times. Again, there are two ways to proceed
depending on the situation.
</p><p>
<span class="application">PL/pgSQL</span> variable values can be
automatically inserted into optimizable SQL commands, which
are <code class="command">SELECT</code>, <code class="command">INSERT</code>,
<code class="command">UPDATE</code>, <code class="command">DELETE</code>,
<code class="command">MERGE</code>, and certain
utility commands that incorporate one of these, such
as <code class="command">EXPLAIN</code> and <code class="command">CREATE TABLE ... AS
SELECT</code>. In these commands,
any <span class="application">PL/pgSQL</span> variable name appearing
in the command text is replaced by a query parameter, and then the
current value of the variable is provided as the parameter value
at run time. This is exactly like the processing described earlier
for expressions; for details see <a class="xref" href="plpgsql-implementation.html#PLPGSQL-VAR-SUBST" title="43.11.1. Variable Substitution">Section 43.11.1</a>.
</p><p>
When executing an optimizable SQL command in this way,
<span class="application">PL/pgSQL</span> may cache and re-use the execution
plan for the command, as discussed in
<a class="xref" href="plpgsql-implementation.html#PLPGSQL-PLAN-CACHING" title="43.11.2. Plan Caching">Section 43.11.2</a>.
</p><p>
Non-optimizable SQL commands (also called utility commands) are not
capable of accepting query parameters. So automatic substitution
of <span class="application">PL/pgSQL</span> variables does not work in such
commands. To include non-constant text in a utility command executed
from <span class="application">PL/pgSQL</span>, you must build the utility
command as a string and then <code class="command">EXECUTE</code> it, as
discussed 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>
<code class="command">EXECUTE</code> must also be used if you want to modify
the command in some other way than supplying a data value, for example
by changing a table name.
</p><p>
Sometimes it is useful to evaluate an expression or <code class="command">SELECT</code>
query but discard the result, for example when calling a function
that has 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 you would write an SQL <code class="command">SELECT</code> command, but replace the
initial keyword <code class="command">SELECT</code> with <code class="command">PERFORM</code>.
For <code class="command">WITH</code> queries, use <code class="command">PERFORM</code> and then
place the query in parentheses. (In this case, the query can only
return one row.)
<span class="application">PL/pgSQL</span> variables will be
substituted into the query just as described above,
and the plan is cached in the same way. 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 (see
<a class="xref" href="plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS" title="43.5.5. Obtaining the Result Status">Section 43.5.5</a>).
</p><div class="note"><h3 class="title">Note</h3><p>
One might expect that writing <code class="command">SELECT</code> directly
would accomplish this result, but at
present the only accepted way to do it is
<code class="command">PERFORM</code>. An SQL command that can return rows,
such as <code class="command">SELECT</code>, will be rejected as an error
unless it has an <code class="literal">INTO</code> clause as discussed in the
next section.
</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" id="PLPGSQL-STATEMENTS-SQL-ONEROW"><div class="titlepage"><div><div><h3 class="title">43.5.3. Executing a Command with a Single-Row Result <a href="#PLPGSQL-STATEMENTS-SQL-ONEROW" class="id_link">#</a></h3></div></div></div><a id="id-1.8.8.7.5.2" class="indexterm"></a><a id="id-1.8.8.7.5.3" class="indexterm"></a><p>
The result of an SQL command yielding a single row (possibly of multiple
columns) can be assigned to a record variable, row-type variable, or list
of scalar variables. This is done by writing the base SQL command and
adding an <code class="literal">INTO</code> clause. For example,
</p><pre class="synopsis">
SELECT <em class="replaceable"><code>select_expressions</code></em> INTO [<span class="optional">STRICT</span>] <em class="replaceable"><code>target</code></em> FROM ...;
INSERT ... RETURNING <em class="replaceable"><code>expressions</code></em> INTO [<span class="optional">STRICT</span>] <em class="replaceable"><code>target</code></em>;
UPDATE ... RETURNING <em class="replaceable"><code>expressions</code></em> INTO [<span class="optional">STRICT</span>] <em class="replaceable"><code>target</code></em>;
DELETE ... RETURNING <em class="replaceable"><code>expressions</code></em> INTO [<span class="optional">STRICT</span>] <em class="replaceable"><code>target</code></em>;
</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.
<span class="application">PL/pgSQL</span> variables will be
substituted into the rest of the command (that is, everything but the
<code class="literal">INTO</code> clause) just as described above,
and the plan is cached in the same way.
This works for <code class="command">SELECT</code>,
<code class="command">INSERT</code>/<code class="command">UPDATE</code>/<code class="command">DELETE</code> with
<code class="literal">RETURNING</code>, and certain utility commands
that return row sets, such as <code class="command">EXPLAIN</code>.
Except for the <code class="literal">INTO</code> clause, the SQL command is the same
as it would be written outside <span class="application">PL/pgSQL</span>.
</p><div class="tip"><h3 class="title">Tip</h3><p>
Note that this interpretation of <code class="command">SELECT</code> with <code class="literal">INTO</code>
is quite different from <span class="productname">PostgreSQL</span>'s regular
<code class="command">SELECT INTO</code> command, wherein 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></div><p>
If a row variable or a variable list is used as target,
the command's result columns
must exactly match the structure of the target as to number and data
types, or else a run-time error
occurs. When a record variable is the target, it automatically
configures itself to the row type of the command's result columns.
</p><p>
The <code class="literal">INTO</code> clause can appear almost anywhere in the SQL
command. Customarily it is written either just before or just after
the list of <em class="replaceable"><code>select_expressions</code></em> in a
<code class="command">SELECT</code> command, or at the end of the command for other
command types. It is recommended that you follow this convention
in case the <span class="application">PL/pgSQL</span> parser becomes
stricter in future versions.
</p><p>
If <code class="literal">STRICT</code> is not specified in the <code class="literal">INTO</code>
clause, then <em class="replaceable"><code>target</code></em> will be set to the first
row returned by the command, or to nulls if the command returned no rows.
(Note that <span class="quote">“<span class="quote">the first row</span>”</span> is not
well-defined unless you've used <code class="literal">ORDER BY</code>.) Any result rows
after the first row are discarded.
You can check the special <code class="literal">FOUND</code> variable (see
<a class="xref" href="plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS" title="43.5.5. Obtaining the Result Status">Section 43.5.5</a>) to
determine whether a row was returned:
</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>
If the <code class="literal">STRICT</code> option is specified, the command must
return exactly one row or a run-time error will be reported, either
<code class="literal">NO_DATA_FOUND</code> (no rows) or <code class="literal">TOO_MANY_ROWS</code>
(more than one row). You can use an exception block if you wish
to catch the error, for example:
</p><pre class="programlisting">
BEGIN
SELECT * INTO STRICT myrec FROM emp WHERE empname = myname;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE EXCEPTION 'employee % not found', myname;
WHEN TOO_MANY_ROWS THEN
RAISE EXCEPTION 'employee % not unique', myname;
END;
</pre><p>
Successful execution of a command with <code class="literal">STRICT</code>
always sets <code class="literal">FOUND</code> to true.
</p><p>
For <code class="command">INSERT</code>/<code class="command">UPDATE</code>/<code class="command">DELETE</code> with
<code class="literal">RETURNING</code>, <span class="application">PL/pgSQL</span> reports
an error for more than one returned row, even when
<code class="literal">STRICT</code> is not specified. This is because there
is no option such as <code class="literal">ORDER BY</code> with which to determine
which affected row should be returned.
</p><p>
If <code class="literal">print_strict_params</code> is enabled for the function,
then when an error is thrown because the requirements
of <code class="literal">STRICT</code> are not met, the <code class="literal">DETAIL</code> part of
the error message will include information about the parameters
passed to the command.
You can change the <code class="literal">print_strict_params</code>
setting for all functions by setting
<code class="varname">plpgsql.print_strict_params</code>, though only subsequent
function compilations will be affected. You can also enable it
on a per-function basis by using a compiler option, for example:
</p><pre class="programlisting">
CREATE FUNCTION get_userid(username text) RETURNS int
AS $$
#print_strict_params on
DECLARE
userid int;
BEGIN
SELECT users.userid INTO STRICT userid
FROM users WHERE users.username = get_userid.username;
RETURN userid;
END;
$$ LANGUAGE plpgsql;
</pre><p>
On failure, this function might produce an error message such as
</p><pre class="programlisting">
ERROR: query returned no rows
DETAIL: parameters: $1 = 'nosuchuser'
CONTEXT: PL/pgSQL function get_userid(text) line 6 at SQL statement
</pre><p>
</p><div class="note"><h3 class="title">Note</h3><p>
The <code class="literal">STRICT</code> option matches the behavior of
Oracle PL/SQL's <code class="command">SELECT INTO</code> and related statements.
</p></div></div><div class="sect2" id="PLPGSQL-STATEMENTS-EXECUTING-DYN"><div class="titlepage"><div><div><h3 class="title">43.5.4. Executing Dynamic Commands <a href="#PLPGSQL-STATEMENTS-EXECUTING-DYN" class="id_link">#</a></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 (as discussed in
<a class="xref" href="plpgsql-implementation.html#PLPGSQL-PLAN-CACHING" title="43.11.2. Plan Caching">Section 43.11.2</a>) 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> [<span class="optional"> INTO [<span class="optional">STRICT</span>] <em class="replaceable"><code>target</code></em> </span>] [<span class="optional"> USING <em class="replaceable"><code>expression</code></em> [<span class="optional">, ... </span>] </span>];
</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. The optional <em class="replaceable"><code>target</code></em>
is a record variable, a row variable, or a comma-separated list of
simple variables and record/row fields, into which the results of
the command will be stored. The optional <code class="literal">USING</code> expressions
supply values to be inserted into the command.
</p><p>
No substitution of <span class="application">PL/pgSQL</span> variables is done on the
computed command string. Any required variable values must be inserted
in the command string as it is constructed; or you can use parameters
as described below.
</p><p>
Also, there is no plan caching for commands executed via
<code class="command">EXECUTE</code>. Instead, the command is always planned
each time the statement is run. Thus 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
an SQL command returning rows should be assigned. If a row variable
or variable list is provided, it must exactly match the structure
of the command's results; if a
record variable is provided, it will configure itself to match the
result structure automatically. If multiple rows are returned,
only the first will be assigned to the <code class="literal">INTO</code>
variable(s). If no rows are returned, NULL is assigned to the
<code class="literal">INTO</code> variable(s). If no <code class="literal">INTO</code>
clause is specified, the command results are discarded.
</p><p>
If the <code class="literal">STRICT</code> option is given, an error is reported
unless the command produces exactly one row.
</p><p>
The command string can use parameter values, which are referenced
in the command as <code class="literal">$1</code>, <code class="literal">$2</code>, etc.
These symbols refer to values supplied in the <code class="literal">USING</code>
clause. This method is often preferable to inserting data values
into the command string as text: it avoids run-time overhead of
converting the values to text and back, and it is much less prone
to SQL-injection attacks since there is no need for quoting or escaping.
An example is:
</p><pre class="programlisting">
EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'
INTO c
USING checked_user, checked_date;
</pre><p>
</p><p>
Note that parameter symbols can only be used for data values
— if you want to use dynamically determined table or column
names, you must insert them into the command string textually.
For example, if the preceding query needed to be done against a
dynamically selected table, you could do this:
</p><pre class="programlisting">
EXECUTE 'SELECT count(*) FROM '
|| quote_ident(tabname)
|| ' WHERE inserted_by = $1 AND inserted <= $2'
INTO c
USING checked_user, checked_date;
</pre><p>
A cleaner approach is to use <code class="function">format()</code>'s <code class="literal">%I</code>
specification to insert table or column names with automatic quoting:
</p><pre class="programlisting">
EXECUTE format('SELECT count(*) FROM %I '
'WHERE inserted_by = $1 AND inserted <= $2', tabname)
INTO c
USING checked_user, checked_date;
</pre><p>
(This example relies on the SQL rule that string literals separated by a
newline are implicitly concatenated.)
</p><p>
Another restriction on parameter symbols is that they only work in
optimizable SQL commands
(<code class="command">SELECT</code>, <code class="command">INSERT</code>, <code class="command">UPDATE</code>,
<code class="command">DELETE</code>, <code class="command">MERGE</code>, and certain commands containing one of these).
In other statement
types (generically called utility statements), you must insert
values textually even if they are just data values.
</p><p>
An <code class="command">EXECUTE</code> with a simple constant command string and some
<code class="literal">USING</code> parameters, as in the first example above, is
functionally equivalent to just writing the command directly in
<span class="application">PL/pgSQL</span> and allowing replacement of
<span class="application">PL/pgSQL</span> variables to happen automatically.
The important difference is that <code class="command">EXECUTE</code> will re-plan
the command on each execution, generating a plan that is specific
to the current parameter values; whereas
<span class="application">PL/pgSQL</span> may otherwise create a generic plan
and cache it for re-use. In situations where the best plan depends
strongly on the parameter values, it can be helpful to use
<code class="command">EXECUTE</code> to positively ensure that a generic plan is not
selected.
</p><p>
<code class="command">SELECT INTO</code> is not currently supported within
<code class="command">EXECUTE</code>; instead, execute a plain <code class="command">SELECT</code>
command and specify <code class="literal">INTO</code> as part of the <code class="command">EXECUTE</code>
itself.
</p><div class="note"><h3 class="title">Note</h3><p>
The <span class="application">PL/pgSQL</span>
<code class="command">EXECUTE</code> statement is not related to the
<a class="link" href="sql-execute.html" title="EXECUTE"><code class="command">EXECUTE</code></a> SQL
statement supported by the
<span class="productname">PostgreSQL</span> server. The server's
<code class="command">EXECUTE</code> statement cannot be used directly within
<span class="application">PL/pgSQL</span> functions (and is not needed).
</p></div><div class="example" id="PLPGSQL-QUOTE-LITERAL-EXAMPLE"><p class="title"><strong>Example 43.1. Quoting Values in Dynamic Queries</strong></p><div class="example-contents"><a id="id-1.8.8.7.6.13.2" class="indexterm"></a><a id="id-1.8.8.7.6.13.3" class="indexterm"></a><a id="id-1.8.8.7.6.13.4" class="indexterm"></a><a id="id-1.8.8.7.6.13.5" class="indexterm"></a><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 class="xref" href="plpgsql-development-tips.html#PLPGSQL-QUOTE-TIPS" title="43.12.1. Handling of Quotation Marks">Section 43.12.1</a>, which can save you
some effort when translating said code to a more reasonable scheme.)
</p><p>
Dynamic values require careful handling since they might contain
quote characters.
An example using <code class="function">format()</code> (this assumes that you are
dollar quoting the function body so quote marks need not be doubled):
</p><pre class="programlisting">
EXECUTE format('UPDATE tbl SET %I = $1 '
'WHERE key = $2', colname) USING newvalue, keyvalue;
</pre><p>
It is also possible to call the quoting functions directly:
</p><pre class="programlisting">
EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
|| ' = '
|| quote_literal(newvalue)
|| ' WHERE key = '
|| quote_literal(keyvalue);
</pre><p>
</p><p>
This example demonstrates the use of the
<code class="function">quote_ident</code> and
<code class="function">quote_literal</code> functions (see <a class="xref" href="functions-string.html" title="9.4. String Functions and Operators">Section 9.4</a>). For safety, expressions containing column
or table identifiers should be passed through
<code class="function">quote_ident</code> before insertion in a dynamic query.
Expressions containing values that should be literal strings in the
constructed command should be passed through <code class="function">quote_literal</code>.
These functions 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>
Because <code class="function">quote_literal</code> is labeled
<code class="literal">STRICT</code>, it will always return null when called with a
null argument. In the above example, if <code class="literal">newvalue</code> or
<code class="literal">keyvalue</code> were null, the entire dynamic query string would
become null, leading to an error from <code class="command">EXECUTE</code>.
You can avoid this problem by using the <code class="function">quote_nullable</code>
function, which works the same as <code class="function">quote_literal</code> except that
when called with a null argument it returns the string <code class="literal">NULL</code>.
For example,
</p><pre class="programlisting">
EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
|| ' = '
|| quote_nullable(newvalue)
|| ' WHERE key = '
|| quote_nullable(keyvalue);
</pre><p>
If you are dealing with values that might be null, you should usually
use <code class="function">quote_nullable</code> in place of <code class="function">quote_literal</code>.
</p><p>
As always, care must be taken to ensure that null values in a query do
not deliver unintended results. For example the <code class="literal">WHERE</code> clause
</p><pre class="programlisting">
'WHERE key = ' || quote_nullable(keyvalue)
</pre><p>
will never succeed if <code class="literal">keyvalue</code> is null, because the
result of using the equality operator <code class="literal">=</code> with a null operand
is always null. If you wish null to work like an ordinary key value,
you would need to rewrite the above as
</p><pre class="programlisting">
'WHERE key IS NOT DISTINCT FROM ' || quote_nullable(keyvalue)
</pre><p>
(At present, <code class="literal">IS NOT DISTINCT FROM</code> is handled much less
efficiently than <code class="literal">=</code>, so don't do this unless you must.
See <a class="xref" href="functions-comparison.html" title="9.2. Comparison Functions and Operators">Section 9.2</a> for
more information on nulls and <code class="literal">IS DISTINCT</code>.)
</p><p>
Note that dollar quoting is only useful for quoting fixed text.
It would be a very bad idea to try to write this 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>,
<code class="function">quote_nullable</code>, or <code class="function">quote_ident</code>, as appropriate.
</p><p>
Dynamic SQL statements can also be safely constructed using the
<code class="function">format</code> function (see <a class="xref" href="functions-string.html#FUNCTIONS-STRING-FORMAT" title="9.4.1. format">Section 9.4.1</a>). For example:
</p><pre class="programlisting">
EXECUTE format('UPDATE tbl SET %I = %L '
'WHERE key = %L', colname, newvalue, keyvalue);
</pre><p>
<code class="literal">%I</code> is equivalent to <code class="function">quote_ident</code>, and
<code class="literal">%L</code> is equivalent to <code class="function">quote_nullable</code>.
The <code class="function">format</code> function can be used in conjunction with
the <code class="literal">USING</code> clause:
</p><pre class="programlisting">
EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname)
USING newvalue, keyvalue;
</pre><p>
This form is better because the variables are handled in their native
data type format, rather than unconditionally converting them to
text and quoting them via <code class="literal">%L</code>. It is also more efficient.
</p></div></div><br class="example-break" /><p>
A much larger example of a dynamic command and
<code class="command">EXECUTE</code> can be seen in <a class="xref" href="plpgsql-porting.html#PLPGSQL-PORTING-EX2" title="Example 43.10. Porting a Function that Creates Another Function from PL/SQL to PL/pgSQL">Example 43.10</a>, which builds and executes a
<code class="command">CREATE FUNCTION</code> command to define a new function.
</p></div><div class="sect2" id="PLPGSQL-STATEMENTS-DIAGNOSTICS"><div class="titlepage"><div><div><h3 class="title">43.5.5. Obtaining the Result Status <a href="#PLPGSQL-STATEMENTS-DIAGNOSTICS" class="id_link">#</a></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 [<span class="optional"> CURRENT </span>] 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.
<code class="literal">CURRENT</code> is a noise word (but see also <code class="command">GET STACKED
DIAGNOSTICS</code> in <a class="xref" href="plpgsql-control-structures.html#PLPGSQL-EXCEPTION-DIAGNOSTICS" title="43.6.8.1. Obtaining Information about an Error">Section 43.6.8.1</a>).
Each <em class="replaceable"><code>item</code></em> is a key word identifying a status
value to be assigned to the specified <em class="replaceable"><code>variable</code></em>
(which should be of the right data type to receive it). The currently
available status items are shown
in <a class="xref" href="plpgsql-statements.html#PLPGSQL-CURRENT-DIAGNOSTICS-VALUES" title="Table 43.1. Available Diagnostics Items">Table 43.1</a>. Colon-equal
(<code class="literal">:=</code>) can be used instead of the SQL-standard <code class="literal">=</code>
token. An example:
</p><pre class="programlisting">
GET DIAGNOSTICS integer_var = ROW_COUNT;
</pre><p>
</p><div class="table" id="PLPGSQL-CURRENT-DIAGNOSTICS-VALUES"><p class="title"><strong>Table 43.1. Available Diagnostics Items</strong></p><div class="table-contents"><table class="table" summary="Available Diagnostics Items" border="1"><colgroup><col class="col1" /><col class="col2" /><col class="col3" /></colgroup><thead><tr><th>Name</th><th>Type</th><th>Description</th></tr></thead><tbody><tr><td><code class="varname">ROW_COUNT</code></td><td><code class="type">bigint</code></td><td>the number of rows processed by the most
recent <acronym class="acronym">SQL</acronym> command</td></tr><tr><td><code class="literal">PG_CONTEXT</code></td><td><code class="type">text</code></td><td>line(s) of text describing the current call stack
(see <a class="xref" href="plpgsql-control-structures.html#PLPGSQL-CALL-STACK" title="43.6.9. Obtaining Execution Location Information">Section 43.6.9</a>)</td></tr><tr><td><code class="literal">PG_ROUTINE_OID</code></td><td><code class="type">oid</code></td><td>OID of the current function</td></tr></tbody></table></div></div><br class="table-break" /><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 class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
A <code class="command">SELECT INTO</code> statement sets
<code class="literal">FOUND</code> true if a row is assigned, false if no
row is returned.
</p></li><li class="listitem"><p>
A <code class="command">PERFORM</code> statement sets <code class="literal">FOUND</code>
true if it produces (and discards) one or more rows, false if
no row is produced.
</p></li><li class="listitem"><p>
<code class="command">UPDATE</code>, <code class="command">INSERT</code>, <code class="command">DELETE</code>,
and <code class="command">MERGE</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 class="listitem"><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 class="listitem"><p>
A <code class="command">MOVE</code> statement sets <code class="literal">FOUND</code>
true if it successfully repositions the cursor, false otherwise.
</p></li><li class="listitem"><p>
A <code class="command">FOR</code> or <code class="command">FOREACH</code> statement sets
<code class="literal">FOUND</code> true
if it iterates one or more times, else false.
<code class="literal">FOUND</code> is set this way when the
loop exits; inside the execution of the loop,
<code class="literal">FOUND</code> is not modified by the
loop statement, although it might be changed by the
execution of other statements within the loop body.
</p></li><li class="listitem"><p>
<code class="command">RETURN QUERY</code> and <code class="command">RETURN QUERY
EXECUTE</code> statements set <code class="literal">FOUND</code>
true if the query returns at least one row, false if no row
is returned.
</p></li></ul></div><p>
Other <span class="application">PL/pgSQL</span> statements do not change
the state of <code class="literal">FOUND</code>.
Note in particular that <code class="command">EXECUTE</code>
changes the output of <code class="command">GET DIAGNOSTICS</code>, but
does not change <code class="literal">FOUND</code>.
</p><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 class="sect2" id="PLPGSQL-STATEMENTS-NULL"><div class="titlepage"><div><div><h3 class="title">43.5.6. Doing Nothing At All <a href="#PLPGSQL-STATEMENTS-NULL" class="id_link">#</a></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"><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><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="plpgsql-expressions.html" title="43.4. Expressions">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-control-structures.html" title="43.6. Control Structures">Next</a></td></tr><tr><td width="40%" align="left" valign="top">43.4. Expressions </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.6. Control Structures</td></tr></table></div></body></html>
|
