1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
5
>Basic Statements</TITLE
8
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
10
HREF="mailto:pgsql-docs@postgresql.org"><LINK
12
TITLE="PostgreSQL 9.1beta1 Documentation"
13
HREF="index.html"><LINK
15
TITLE="PL/pgSQL - SQL Procedural Language"
16
HREF="plpgsql.html"><LINK
19
HREF="plpgsql-expressions.html"><LINK
21
TITLE="Control Structures"
22
HREF="plpgsql-control-structures.html"><LINK
25
HREF="stylesheet.css"><META
26
HTTP-EQUIV="Content-Type"
27
CONTENT="text/html; charset=ISO-8859-1"><META
29
CONTENT="2011-04-27T21:20:33"></HEAD
35
SUMMARY="Header navigation table"
47
>PostgreSQL 9.1beta1 Documentation</A
57
HREF="plpgsql-expressions.html"
66
TITLE="PL/pgSQL - SQL Procedural Language"
80
> Procedural Language</TD
86
TITLE="PL/pgSQL - SQL Procedural Language"
95
TITLE="Control Structures"
96
HREF="plpgsql-control-structures.html"
110
NAME="PLPGSQL-STATEMENTS"
111
>39.5. Basic Statements</A
114
> In this section and the following ones, we describe all the statement
115
types that are explicitly understood by
120
Anything not recognized as one of these statement types is presumed
121
to be an SQL command and is sent to the main database engine to execute,
123
HREF="plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-NORESULT"
127
HREF="plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW"
136
NAME="PLPGSQL-STATEMENTS-ASSIGNMENT"
137
>39.5.1. Assignment</A
140
> An assignment of a value to a <SPAN
144
variable is written as:
159
As explained previously, the expression in such a statement is evaluated
160
by means of an SQL <TT
163
> command sent to the main
164
database engine. The expression must yield a single value (possibly
165
a row value, if the variable is a row or record variable). The target
166
variable can be a simple variable (optionally qualified with a block
167
name), a field of a row or record variable, or an element of an array
168
that is a simple variable or field.
171
> If the expression's result data type doesn't match the variable's
172
data type, or the variable has a specific size/precision
176
>), the result value will be implicitly
177
converted by the <SPAN
181
the result type's output-function and
182
the variable type's input-function. Note that this could potentially
183
result in run-time errors generated by the input function, if the
184
string form of the result value is not acceptable to the input function.
189
CLASS="PROGRAMLISTING"
190
>tax := subtotal * 0.06;
191
my_record.user_id := 20;</PRE
200
NAME="PLPGSQL-STATEMENTS-SQL-NORESULT"
201
>39.5.2. Executing a Command With No Result</A
204
> For any SQL command that does not return rows, for example
212
execute the command within a <SPAN
216
just by writing the command.
222
> variable name appearing
223
in the command text is treated as a parameter, and then the
224
current value of the variable is provided as the parameter value
225
at run time. This is exactly like the processing described earlier
226
for expressions; for details see <A
227
HREF="plpgsql-implementation.html#PLPGSQL-VAR-SUBST"
232
> When executing a SQL command in this way,
236
> plans the command just once
237
and re-uses the plan on subsequent executions, for the life of
238
the database connection. The implications of this are discussed
240
HREF="plpgsql-implementation.html#PLPGSQL-PLAN-CACHING"
245
> Sometimes it is useful to evaluate an expression or <TT
249
query but discard the result, for example when calling a function
250
that has side-effects but no useful result value. To do
276
result. Write the <TT
282
way you would write an SQL <TT
285
> command, but replace the
297
substituted into the query just as for commands that return no result,
298
and the plan is cached in the same way. Also, the special variable
302
> is set to true if the query produced at
303
least one row, or false if it produced no rows (see
305
HREF="plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS"
316
> One might expect that writing <TT
320
would accomplish this result, but at
321
present the only accepted way to do it is
325
>. A SQL command that can return rows,
329
>, will be rejected as an error
333
> clause as discussed in the
341
CLASS="PROGRAMLISTING"
342
>PERFORM create_mv('cs_session_page_requests_mv', my_query);</PRE
351
NAME="PLPGSQL-STATEMENTS-SQL-ONEROW"
352
>39.5.3. Executing a Query with a Single-row Result</A
355
> The result of a SQL command yielding a single row (possibly of multiple
356
columns) can be assigned to a record variable, row-type variable, or list
357
of scalar variables. This is done by writing the base SQL command and
361
> clause. For example,
368
>select_expressions</I
379
INSERT ... RETURNING <TT
393
UPDATE ... RETURNING <TT
407
DELETE ... RETURNING <TT
428
> can be a record variable, a row
429
variable, or a comma-separated list of simple variables and
435
substituted into the rest of the query, and the plan is cached,
436
just as described above for commands that do not return rows.
454
>, and utility commands that return row-set
462
> clause, the SQL command is the same
463
as it would be written outside <SPAN
475
> Note that this interpretation of <TT
482
is quite different from <SPAN
489
> command, wherein the <TT
493
target is a newly created table. If you want to create a table from a
501
> function, use the syntax
504
>CREATE TABLE ... AS SELECT</TT
510
> If a row or a variable list is used as target, the query's result columns
511
must exactly match the structure of the target as to number and data
512
types, or else a run-time error
513
occurs. When a record variable is the target, it automatically
514
configures itself to the row type of the query result columns.
520
> clause can appear almost anywhere in the SQL
521
command. Customarily it is written either just before or just after
525
>select_expressions</I
531
> command, or at the end of the command for other
532
command types. It is recommended that you follow this convention
537
stricter in future versions.
543
> is not specified in the <TT
552
> will be set to the first
553
row returned by the query, or to nulls if the query returned no rows.
556
>"the first row"</SPAN
558
well-defined unless you've used <TT
562
after the first row are discarded.
563
You can check the special <TT
568
HREF="plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS"
571
determine whether a row was returned:
574
CLASS="PROGRAMLISTING"
575
>SELECT * INTO myrec FROM emp WHERE empname = myname;
577
RAISE EXCEPTION 'employee % not found', myname;
584
> option is specified, the query must
585
return exactly one row or a run-time error will be reported, either
593
(more than one row). You can use an exception block if you wish
594
to catch the error, for example:
597
CLASS="PROGRAMLISTING"
599
SELECT * INTO STRICT myrec FROM emp WHERE empname = myname;
601
WHEN NO_DATA_FOUND THEN
602
RAISE EXCEPTION 'employee % not found', myname;
603
WHEN TOO_MANY_ROWS THEN
604
RAISE EXCEPTION 'employee % not unique', myname;
607
Successful execution of a command with <TT
634
an error for more than one returned row, even when
638
> is not specified. This is because there
639
is no option such as <TT
642
> with which to determine
643
which affected row should be returned.
655
> option matches the behavior of
659
> and related statements.
664
> To handle cases where you need to process multiple result rows
665
from a SQL query, see <A
666
HREF="plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING"
676
NAME="PLPGSQL-STATEMENTS-EXECUTING-DYN"
677
>39.5.4. Executing Dynamic Commands</A
680
> Oftentimes you will want to generate dynamic commands inside your
684
> functions, that is, commands
685
that will involve different tables or different data types each
686
time they are executed. <SPAN
690
normal attempts to cache plans for commands (as discussed in
692
HREF="plpgsql-implementation.html#PLPGSQL-PLAN-CACHING"
694
>) will not work in such
695
scenarios. To handle this sort of problem, the
699
> statement is provided:
739
yielding a string (of type <TT
743
command to be executed. The optional <TT
749
is a record variable, a row variable, or a comma-separated list of
750
simple variables and record/row fields, into which the results of
751
the command will be stored. The optional <TT
755
supply values to be inserted into the command.
758
> No substitution of <SPAN
761
> variables is done on the
762
computed command string. Any required variable values must be inserted
763
in the command string as it is constructed; or you can use parameters
767
> Also, there is no plan caching for commands executed via
772
command is prepared each time the statement is run. Thus the command
773
string can be dynamically created within the function to perform
774
actions on different tables and columns.
780
> clause specifies where the results of
781
a SQL command returning rows should be assigned. If a row
782
or variable list is provided, it must exactly match the structure
783
of the query's results (when a
784
record variable is used, it will configure itself to match the
785
result structure automatically). If multiple rows are returned,
786
only the first will be assigned to the <TT
790
variable. If no rows are returned, NULL is assigned to the
794
> variable(s). If no <TT
798
clause is specified, the query results are discarded.
804
> option is given, an error is reported
805
unless the query produces exactly one row.
808
> The command string can use parameter values, which are referenced
809
in the command as <TT
816
These symbols refer to values supplied in the <TT
820
clause. This method is often preferable to inserting data values
821
into the command string as text: it avoids run-time overhead of
822
converting the values to text and back, and it is much less prone
823
to SQL-injection attacks since there is no need for quoting or escaping.
826
CLASS="PROGRAMLISTING"
827
>EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'
829
USING checked_user, checked_date;</PRE
833
> Note that parameter symbols can only be used for data values
834
— if you want to use dynamically determined table or column
835
names, you must insert them into the command string textually.
836
For example, if the preceding query needed to be done against a
837
dynamically selected table, you could do this:
839
CLASS="PROGRAMLISTING"
840
>EXECUTE 'SELECT count(*) FROM '
842
|| ' WHERE inserted_by = $1 AND inserted <= $2'
844
USING checked_user, checked_date;</PRE
846
Another restriction on parameter symbols is that they only work in
860
> commands. In other statement
861
types (generically called utility statements), you must insert
862
values textually even if they are just data values.
868
> with a simple constant command string and some
872
> parameters, as in the first example above, is
873
functionally equivalent to just writing the command directly in
877
> and allowing replacement of
881
> variables to happen automatically.
882
The important difference is that <TT
886
the command on each execution, generating a plan that is specific
887
to the current parameter values; whereas
891
> normally creates a generic plan
892
and caches it for re-use. In situations where the best plan depends
893
strongly on the parameter values, <TT
897
significantly faster; while when the plan is not sensitive to parameter
898
values, re-planning will be a waste.
904
> is not currently supported within
908
>; instead, execute a plain <TT
912
command and specify <TT
935
> statement is not related to the
937
HREF="sql-execute.html"
940
statement supported by the
944
> server. The server's
948
> statement cannot be used directly within
952
> functions (and is not needed).
959
NAME="PLPGSQL-QUOTE-LITERAL-EXAMPLE"
963
>Example 39-1. Quoting Values In Dynamic Queries</B
966
> When working with dynamic commands you will often have to handle escaping
967
of single quotes. The recommended method for quoting fixed text in your
968
function body is dollar quoting. (If you have legacy code that does
969
not use dollar quoting, please refer to the
971
HREF="plpgsql-development-tips.html#PLPGSQL-QUOTE-TIPS"
973
>, which can save you
974
some effort when translating said code to a more reasonable scheme.)
977
> Dynamic values that are to be inserted into the constructed
978
query require careful handling since they might themselves contain
980
An example (this assumes that you are using dollar quoting for the
981
function as a whole, so the quote marks need not be doubled):
983
CLASS="PROGRAMLISTING"
984
>EXECUTE 'UPDATE tbl SET '
985
|| quote_ident(colname)
987
|| quote_literal(newvalue)
989
|| quote_literal(keyvalue);</PRE
993
> This example demonstrates the use of the
1000
>quote_literal</CODE
1002
HREF="functions-string.html"
1004
>). For safety, expressions containing column
1005
or table identifiers should be passed through
1009
> before insertion in a dynamic query.
1010
Expressions containing values that should be literal strings in the
1011
constructed command should be passed through <CODE
1013
>quote_literal</CODE
1015
These functions take the appropriate steps to return the input text
1016
enclosed in double or single quotes respectively, with any embedded
1017
special characters properly escaped.
1022
>quote_literal</CODE
1027
>, it will always return null when called with a
1028
null argument. In the above example, if <TT
1035
> were null, the entire dynamic query string would
1036
become null, leading to an error from <TT
1040
You can avoid this problem by using the <CODE
1042
>quote_nullable</CODE
1044
function, which works the same as <CODE
1046
>quote_literal</CODE
1048
when called with a null argument it returns the string <TT
1054
CLASS="PROGRAMLISTING"
1055
>EXECUTE 'UPDATE tbl SET '
1056
|| quote_ident(colname)
1058
|| quote_nullable(newvalue)
1060
|| quote_nullable(keyvalue);</PRE
1062
If you are dealing with values that might be null, you should usually
1065
>quote_nullable</CODE
1068
>quote_literal</CODE
1072
> As always, care must be taken to ensure that null values in a query do
1073
not deliver unintended results. For example the <TT
1078
CLASS="PROGRAMLISTING"
1079
>'WHERE key = ' || quote_nullable(keyvalue)</PRE
1081
will never succeed if <TT
1084
> is null, because the
1085
result of using the equality operator <TT
1088
> with a null operand
1089
is always null. If you wish null to work like an ordinary key value,
1090
you would need to rewrite the above as
1092
CLASS="PROGRAMLISTING"
1093
>'WHERE key IS NOT DISTINCT FROM ' || quote_nullable(keyvalue)</PRE
1097
>IS NOT DISTINCT FROM</TT
1098
> is handled much less
1099
efficiently than <TT
1102
>, so don't do this unless you must.
1104
HREF="functions-comparison.html"
1107
more information on nulls and <TT
1113
> Note that dollar quoting is only useful for quoting fixed text.
1114
It would be a very bad idea to try to write this example as:
1116
CLASS="PROGRAMLISTING"
1117
>EXECUTE 'UPDATE tbl SET '
1118
|| quote_ident(colname)
1121
|| '$$ WHERE key = '
1122
|| quote_literal(keyvalue);</PRE
1124
because it would break if the contents of <TT
1128
happened to contain <TT
1131
>. The same objection would
1132
apply to any other dollar-quoting delimiter you might pick.
1133
So, to safely quote text that is not known in advance, you
1142
>quote_literal</CODE
1146
>quote_nullable</CODE
1153
> Dynamic SQL statements can also be safely constructed using the
1158
HREF="functions-string.html"
1162
CLASS="PROGRAMLISTING"
1163
>EXECUTE format('UPDATE tbl SET %I = %L WHERE key = %L', colname, newvalue, keyvalue);</PRE
1168
> function can be used in conjunction with
1174
CLASS="PROGRAMLISTING"
1175
>EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname)
1176
USING newvalue, keyvalue;</PRE
1178
This form is more efficient, because the parameters
1190
> A much larger example of a dynamic command and
1195
HREF="plpgsql-porting.html#PLPGSQL-PORTING-EX2"
1197
>, which builds and executes a
1200
>CREATE FUNCTION</TT
1201
> command to define a new function.
1209
NAME="PLPGSQL-STATEMENTS-DIAGNOSTICS"
1210
>39.5.5. Obtaining the Result Status</A
1213
> There are several ways to determine the effect of a command. The
1214
first method is to use the <TT
1216
>GET DIAGNOSTICS</TT
1218
command, which has the form:
1222
>GET DIAGNOSTICS <TT
1238
This command allows retrieval of system status indicators. Each
1244
> is a key word identifying a state
1245
value to be assigned to the specified variable (which should be
1246
of the right data type to receive it). The currently available
1247
status items are <TT
1250
>, the number of rows
1251
processed by the last <ACRONYM
1262
the OID of the last row inserted by the most recent
1266
> command. Note that <TT
1270
is only useful after an <TT
1274
table containing OIDs.
1279
CLASS="PROGRAMLISTING"
1280
>GET DIAGNOSTICS integer_var = ROW_COUNT;</PRE
1284
> The second method to determine the effects of a command is to check the
1285
special variable named <TT
1296
false within each <SPAN
1300
It is set by each of the following types of statements:
1314
> true if a row is assigned, false if no
1323
> statement sets <TT
1327
true if it produces (and discards) one or more rows, false if
1346
> true if at least one
1347
row is affected, false if no row is affected.
1355
> statement sets <TT
1359
true if it returns a row, false if no row is returned.
1367
> statement sets <TT
1371
true if it successfully repositions the cursor, false otherwise.
1387
if it iterates one or more times, else false.
1391
> is set this way when the
1392
loop exits; inside the execution of the loop,
1396
> is not modified by the
1397
loop statement, although it might be changed by the
1398
execution of other statements within the loop body.
1410
> statements set <TT
1414
true if the query returns at least one row, false if no row
1424
> statements do not change
1429
Note in particular that <TT
1433
changes the output of <TT
1435
>GET DIAGNOSTICS</TT
1446
> is a local variable within each
1450
> function; any changes to it
1451
affect only the current function.
1459
NAME="PLPGSQL-STATEMENTS-NULL"
1460
>39.5.6. Doing Nothing At All</A
1463
> Sometimes a placeholder statement that does nothing is useful.
1464
For example, it can indicate that one arm of an if/then/else
1465
chain is deliberately empty. For this purpose, use the
1477
> For example, the following two fragments of code are equivalent:
1479
CLASS="PROGRAMLISTING"
1483
WHEN division_by_zero THEN
1484
NULL; -- ignore the error
1489
CLASS="PROGRAMLISTING"
1493
WHEN division_by_zero THEN -- ignore the error
1496
Which is preferable is a matter of taste.
1505
> In Oracle's PL/SQL, empty statement lists are not allowed, and so
1509
> statements are <SPAN
1520
just write nothing, instead.
1531
SUMMARY="Footer navigation table"
1542
HREF="plpgsql-expressions.html"
1560
HREF="plpgsql-control-structures.html"
1584
>Control Structures</TD
b'\\ No newline at end of file'
added
removed
doc/src/sgml/html/plpgsql-statements.html
