codekingpro/portable-devtools
115k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!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.3 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>3 In this section and the following ones, we describe all the statement4 types that are explicitly understood by5 <span class="application">PL/pgSQL</span>.6 Anything not recognized as one of these statement types is presumed7 to be an SQL command and is sent to the main database engine to execute,8 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>.9 </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>10 An assignment of a value to a <span class="application">PL/pgSQL</span>11 variable is written as:12</p><pre class="synopsis">13<em class="replaceable"><code>variable</code></em> { := | = } <em class="replaceable"><code>expression</code></em>;14</pre><p>15 As explained previously, the expression in such a statement is evaluated16 by means of an SQL <code class="command">SELECT</code> command sent to the main17 database engine. The expression must yield a single value (possibly18 a row value, if the variable is a row or record variable). The target19 variable can be a simple variable (optionally qualified with a block20 name), a field of a row or record target, or an element or slice of21 an array target. Equal (<code class="literal">=</code>) can be22 used instead of PL/SQL-compliant <code class="literal">:=</code>.23 </p><p>24 If the expression's result data type doesn't match the variable's25 data type, the value will be coerced as though by an assignment cast26 (see <a class="xref" href="typeconv-query.html" title="10.4. Value Storage">Section 10.4</a>). If no assignment cast is known27 for the pair of data types involved, the <span class="application">PL/pgSQL</span>28 interpreter will attempt to convert the result value textually, that is29 by applying the result type's output function followed by the variable30 type's input function. Note that this could result in run-time errors31 generated by the input function, if the string form of the result value32 is not acceptable to the input function.33 </p><p>34 Examples:35</p><pre class="programlisting">36tax := subtotal * 0.06;37my_record.user_id := 20;38my_array[j] := 20;39my_array[1:3] := array[1,2,3];40complex_array[n].realpart = 12.3;41</pre><p>42 </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>43 In general, any SQL command that does not return rows can be executed44 within a <span class="application">PL/pgSQL</span> function just by writing45 the command. For example, you could create and fill a table by writing46</p><pre class="programlisting">47CREATE TABLE mytable (id int primary key, data text);48INSERT INTO mytable VALUES (1,'one'), (2,'two');49</pre><p>50 </p><p>51 If the command does return rows (for example <code class="command">SELECT</code>,52 or <code class="command">INSERT</code>/<code class="command">UPDATE</code>/<code class="command">DELETE</code>53 with <code class="literal">RETURNING</code>), there are two ways to proceed.54 When the command will return at most one row, or you only care about55 the first row of output, write the command as usual but add56 an <code class="literal">INTO</code> clause to capture the output, as described57 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>.58 To process all of the output rows, write the command as the data59 source for a <code class="command">FOR</code> loop, as described in60 <a class="xref" href="plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING" title="43.6.6. Looping through Query Results">Section 43.6.6</a>.61 </p><p>62 Usually it is not sufficient just to execute statically-defined SQL63 commands. Typically you'll want a command to use varying data values,64 or even to vary in more fundamental ways such as by using different65 table names at different times. Again, there are two ways to proceed66 depending on the situation.67 </p><p>68 <span class="application">PL/pgSQL</span> variable values can be69 automatically inserted into optimizable SQL commands, which70 are <code class="command">SELECT</code>, <code class="command">INSERT</code>,71 <code class="command">UPDATE</code>, <code class="command">DELETE</code>,72 <code class="command">MERGE</code>, and certain73 utility commands that incorporate one of these, such74 as <code class="command">EXPLAIN</code> and <code class="command">CREATE TABLE ... AS75 SELECT</code>. In these commands,76 any <span class="application">PL/pgSQL</span> variable name appearing77 in the command text is replaced by a query parameter, and then the78 current value of the variable is provided as the parameter value79 at run time. This is exactly like the processing described earlier80 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>.81 </p><p>82 When executing an optimizable SQL command in this way,83 <span class="application">PL/pgSQL</span> may cache and re-use the execution84 plan for the command, as discussed in85 <a class="xref" href="plpgsql-implementation.html#PLPGSQL-PLAN-CACHING" title="43.11.2. Plan Caching">Section 43.11.2</a>.86 </p><p>87 Non-optimizable SQL commands (also called utility commands) are not88 capable of accepting query parameters. So automatic substitution89 of <span class="application">PL/pgSQL</span> variables does not work in such90 commands. To include non-constant text in a utility command executed91 from <span class="application">PL/pgSQL</span>, you must build the utility92 command as a string and then <code class="command">EXECUTE</code> it, as93 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>.94 </p><p>95 <code class="command">EXECUTE</code> must also be used if you want to modify96 the command in some other way than supplying a data value, for example97 by changing a table name.98 </p><p>99 Sometimes it is useful to evaluate an expression or <code class="command">SELECT</code>100 query but discard the result, for example when calling a function101 that has side-effects but no useful result value. To do102 this in <span class="application">PL/pgSQL</span>, use the103 <code class="command">PERFORM</code> statement:104 105</p><pre class="synopsis">106PERFORM <em class="replaceable"><code>query</code></em>;107</pre><p>108 109 This executes <em class="replaceable"><code>query</code></em> and discards the110 result. Write the <em class="replaceable"><code>query</code></em> the same111 way you would write an SQL <code class="command">SELECT</code> command, but replace the112 initial keyword <code class="command">SELECT</code> with <code class="command">PERFORM</code>.113 For <code class="command">WITH</code> queries, use <code class="command">PERFORM</code> and then114 place the query in parentheses. (In this case, the query can only115 return one row.)116 <span class="application">PL/pgSQL</span> variables will be117 substituted into the query just as described above,118 and the plan is cached in the same way. Also, the special variable119 <code class="literal">FOUND</code> is set to true if the query produced at120 least one row, or false if it produced no rows (see121 <a class="xref" href="plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS" title="43.5.5. Obtaining the Result Status">Section 43.5.5</a>).122 </p><div class="note"><h3 class="title">Note</h3><p>123 One might expect that writing <code class="command">SELECT</code> directly124 would accomplish this result, but at125 present the only accepted way to do it is126 <code class="command">PERFORM</code>. An SQL command that can return rows,127 such as <code class="command">SELECT</code>, will be rejected as an error128 unless it has an <code class="literal">INTO</code> clause as discussed in the129 next section.130 </p></div><p>131 An example:132</p><pre class="programlisting">133PERFORM create_mv('cs_session_page_requests_mv', my_query);134</pre><p>135 </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>136 The result of an SQL command yielding a single row (possibly of multiple137 columns) can be assigned to a record variable, row-type variable, or list138 of scalar variables. This is done by writing the base SQL command and139 adding an <code class="literal">INTO</code> clause. For example,140 141</p><pre class="synopsis">142SELECT <em class="replaceable"><code>select_expressions</code></em> INTO [<span class="optional">STRICT</span>] <em class="replaceable"><code>target</code></em> FROM ...;143INSERT ... RETURNING <em class="replaceable"><code>expressions</code></em> INTO [<span class="optional">STRICT</span>] <em class="replaceable"><code>target</code></em>;144UPDATE ... RETURNING <em class="replaceable"><code>expressions</code></em> INTO [<span class="optional">STRICT</span>] <em class="replaceable"><code>target</code></em>;145DELETE ... RETURNING <em class="replaceable"><code>expressions</code></em> INTO [<span class="optional">STRICT</span>] <em class="replaceable"><code>target</code></em>;146</pre><p>147 148 where <em class="replaceable"><code>target</code></em> can be a record variable, a row149 variable, or a comma-separated list of simple variables and150 record/row fields.151 <span class="application">PL/pgSQL</span> variables will be152 substituted into the rest of the command (that is, everything but the153 <code class="literal">INTO</code> clause) just as described above,154 and the plan is cached in the same way.155 This works for <code class="command">SELECT</code>,156 <code class="command">INSERT</code>/<code class="command">UPDATE</code>/<code class="command">DELETE</code> with157 <code class="literal">RETURNING</code>, and certain utility commands158 that return row sets, such as <code class="command">EXPLAIN</code>.159 Except for the <code class="literal">INTO</code> clause, the SQL command is the same160 as it would be written outside <span class="application">PL/pgSQL</span>.161 </p><div class="tip"><h3 class="title">Tip</h3><p>162 Note that this interpretation of <code class="command">SELECT</code> with <code class="literal">INTO</code>163 is quite different from <span class="productname">PostgreSQL</span>'s regular164 <code class="command">SELECT INTO</code> command, wherein the <code class="literal">INTO</code>165 target is a newly created table. If you want to create a table from a166 <code class="command">SELECT</code> result inside a167 <span class="application">PL/pgSQL</span> function, use the syntax168 <code class="command">CREATE TABLE ... AS SELECT</code>.169 </p></div><p>170 If a row variable or a variable list is used as target,171 the command's result columns172 must exactly match the structure of the target as to number and data173 types, or else a run-time error174 occurs. When a record variable is the target, it automatically175 configures itself to the row type of the command's result columns.176 </p><p>177 The <code class="literal">INTO</code> clause can appear almost anywhere in the SQL178 command. Customarily it is written either just before or just after179 the list of <em class="replaceable"><code>select_expressions</code></em> in a180 <code class="command">SELECT</code> command, or at the end of the command for other181 command types. It is recommended that you follow this convention182 in case the <span class="application">PL/pgSQL</span> parser becomes183 stricter in future versions.184 </p><p>185 If <code class="literal">STRICT</code> is not specified in the <code class="literal">INTO</code>186 clause, then <em class="replaceable"><code>target</code></em> will be set to the first187 row returned by the command, or to nulls if the command returned no rows.188 (Note that <span class="quote">“<span class="quote">the first row</span>”</span> is not189 well-defined unless you've used <code class="literal">ORDER BY</code>.) Any result rows190 after the first row are discarded.191 You can check the special <code class="literal">FOUND</code> variable (see192 <a class="xref" href="plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS" title="43.5.5. Obtaining the Result Status">Section 43.5.5</a>) to193 determine whether a row was returned:194 195</p><pre class="programlisting">196SELECT * INTO myrec FROM emp WHERE empname = myname;197IF NOT FOUND THEN198 RAISE EXCEPTION 'employee % not found', myname;199END IF;200</pre><p>201 202 If the <code class="literal">STRICT</code> option is specified, the command must203 return exactly one row or a run-time error will be reported, either204 <code class="literal">NO_DATA_FOUND</code> (no rows) or <code class="literal">TOO_MANY_ROWS</code>205 (more than one row). You can use an exception block if you wish206 to catch the error, for example:207 208</p><pre class="programlisting">209BEGIN210 SELECT * INTO STRICT myrec FROM emp WHERE empname = myname;211 EXCEPTION212 WHEN NO_DATA_FOUND THEN213 RAISE EXCEPTION 'employee % not found', myname;214 WHEN TOO_MANY_ROWS THEN215 RAISE EXCEPTION 'employee % not unique', myname;216END;217</pre><p>218 Successful execution of a command with <code class="literal">STRICT</code>219 always sets <code class="literal">FOUND</code> to true.220 </p><p>221 For <code class="command">INSERT</code>/<code class="command">UPDATE</code>/<code class="command">DELETE</code> with222 <code class="literal">RETURNING</code>, <span class="application">PL/pgSQL</span> reports223 an error for more than one returned row, even when224 <code class="literal">STRICT</code> is not specified. This is because there225 is no option such as <code class="literal">ORDER BY</code> with which to determine226 which affected row should be returned.227 </p><p>228 If <code class="literal">print_strict_params</code> is enabled for the function,229 then when an error is thrown because the requirements230 of <code class="literal">STRICT</code> are not met, the <code class="literal">DETAIL</code> part of231 the error message will include information about the parameters232 passed to the command.233 You can change the <code class="literal">print_strict_params</code>234 setting for all functions by setting235 <code class="varname">plpgsql.print_strict_params</code>, though only subsequent236 function compilations will be affected. You can also enable it237 on a per-function basis by using a compiler option, for example:238</p><pre class="programlisting">239CREATE FUNCTION get_userid(username text) RETURNS int240AS $$241#print_strict_params on242DECLARE243userid int;244BEGIN245 SELECT users.userid INTO STRICT userid246 FROM users WHERE users.username = get_userid.username;247 RETURN userid;248END;249$$ LANGUAGE plpgsql;250</pre><p>251 On failure, this function might produce an error message such as252</p><pre class="programlisting">253ERROR: query returned no rows254DETAIL: parameters: $1 = 'nosuchuser'255CONTEXT: PL/pgSQL function get_userid(text) line 6 at SQL statement256</pre><p>257 </p><div class="note"><h3 class="title">Note</h3><p>258 The <code class="literal">STRICT</code> option matches the behavior of259 Oracle PL/SQL's <code class="command">SELECT INTO</code> and related statements.260 </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>261 Oftentimes you will want to generate dynamic commands inside your262 <span class="application">PL/pgSQL</span> functions, that is, commands263 that will involve different tables or different data types each264 time they are executed. <span class="application">PL/pgSQL</span>'s265 normal attempts to cache plans for commands (as discussed in266 <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 such267 scenarios. To handle this sort of problem, the268 <code class="command">EXECUTE</code> statement is provided:269 270</p><pre class="synopsis">271EXECUTE <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>];272</pre><p>273 274 where <em class="replaceable"><code>command-string</code></em> is an expression275 yielding a string (of type <code class="type">text</code>) containing the276 command to be executed. The optional <em class="replaceable"><code>target</code></em>277 is a record variable, a row variable, or a comma-separated list of278 simple variables and record/row fields, into which the results of279 the command will be stored. The optional <code class="literal">USING</code> expressions280 supply values to be inserted into the command.281 </p><p>282 No substitution of <span class="application">PL/pgSQL</span> variables is done on the283 computed command string. Any required variable values must be inserted284 in the command string as it is constructed; or you can use parameters285 as described below.286 </p><p>287 Also, there is no plan caching for commands executed via288 <code class="command">EXECUTE</code>. Instead, the command is always planned289 each time the statement is run. Thus the command290 string can be dynamically created within the function to perform291 actions on different tables and columns.292 </p><p>293 The <code class="literal">INTO</code> clause specifies where the results of294 an SQL command returning rows should be assigned. If a row variable295 or variable list is provided, it must exactly match the structure296 of the command's results; if a297 record variable is provided, it will configure itself to match the298 result structure automatically. If multiple rows are returned,299 only the first will be assigned to the <code class="literal">INTO</code>300 variable(s). If no rows are returned, NULL is assigned to the301 <code class="literal">INTO</code> variable(s). If no <code class="literal">INTO</code>302 clause is specified, the command results are discarded.303 </p><p>304 If the <code class="literal">STRICT</code> option is given, an error is reported305 unless the command produces exactly one row.306 </p><p>307 The command string can use parameter values, which are referenced308 in the command as <code class="literal">$1</code>, <code class="literal">$2</code>, etc.309 These symbols refer to values supplied in the <code class="literal">USING</code>310 clause. This method is often preferable to inserting data values311 into the command string as text: it avoids run-time overhead of312 converting the values to text and back, and it is much less prone313 to SQL-injection attacks since there is no need for quoting or escaping.314 An example is:315</p><pre class="programlisting">316EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'317 INTO c318 USING checked_user, checked_date;319</pre><p>320 </p><p>321 Note that parameter symbols can only be used for data values322 — if you want to use dynamically determined table or column323 names, you must insert them into the command string textually.324 For example, if the preceding query needed to be done against a325 dynamically selected table, you could do this:326</p><pre class="programlisting">327EXECUTE 'SELECT count(*) FROM '328 || quote_ident(tabname)329 || ' WHERE inserted_by = $1 AND inserted <= $2'330 INTO c331 USING checked_user, checked_date;332</pre><p>333 A cleaner approach is to use <code class="function">format()</code>'s <code class="literal">%I</code>334 specification to insert table or column names with automatic quoting:335</p><pre class="programlisting">336EXECUTE format('SELECT count(*) FROM %I '337 'WHERE inserted_by = $1 AND inserted <= $2', tabname)338 INTO c339 USING checked_user, checked_date;340</pre><p>341 (This example relies on the SQL rule that string literals separated by a342 newline are implicitly concatenated.)343 </p><p>344 Another restriction on parameter symbols is that they only work in345 optimizable SQL commands346 (<code class="command">SELECT</code>, <code class="command">INSERT</code>, <code class="command">UPDATE</code>,347 <code class="command">DELETE</code>, <code class="command">MERGE</code>, and certain commands containing one of these).348 In other statement349 types (generically called utility statements), you must insert350 values textually even if they are just data values.351 </p><p>352 An <code class="command">EXECUTE</code> with a simple constant command string and some353 <code class="literal">USING</code> parameters, as in the first example above, is354 functionally equivalent to just writing the command directly in355 <span class="application">PL/pgSQL</span> and allowing replacement of356 <span class="application">PL/pgSQL</span> variables to happen automatically.357 The important difference is that <code class="command">EXECUTE</code> will re-plan358 the command on each execution, generating a plan that is specific359 to the current parameter values; whereas360 <span class="application">PL/pgSQL</span> may otherwise create a generic plan361 and cache it for re-use. In situations where the best plan depends362 strongly on the parameter values, it can be helpful to use363 <code class="command">EXECUTE</code> to positively ensure that a generic plan is not364 selected.365 </p><p>366 <code class="command">SELECT INTO</code> is not currently supported within367 <code class="command">EXECUTE</code>; instead, execute a plain <code class="command">SELECT</code>368 command and specify <code class="literal">INTO</code> as part of the <code class="command">EXECUTE</code>369 itself.370 </p><div class="note"><h3 class="title">Note</h3><p>371 The <span class="application">PL/pgSQL</span>372 <code class="command">EXECUTE</code> statement is not related to the373 <a class="link" href="sql-execute.html" title="EXECUTE"><code class="command">EXECUTE</code></a> SQL374 statement supported by the375 <span class="productname">PostgreSQL</span> server. The server's376 <code class="command">EXECUTE</code> statement cannot be used directly within377 <span class="application">PL/pgSQL</span> functions (and is not needed).378 </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>379 When working with dynamic commands you will often have to handle escaping380 of single quotes. The recommended method for quoting fixed text in your381 function body is dollar quoting. (If you have legacy code that does382 not use dollar quoting, please refer to the383 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 you384 some effort when translating said code to a more reasonable scheme.)385 </p><p>386 Dynamic values require careful handling since they might contain387 quote characters.388 An example using <code class="function">format()</code> (this assumes that you are389 dollar quoting the function body so quote marks need not be doubled):390</p><pre class="programlisting">391EXECUTE format('UPDATE tbl SET %I = $1 '392 'WHERE key = $2', colname) USING newvalue, keyvalue;393</pre><p>394 It is also possible to call the quoting functions directly:395</p><pre class="programlisting">396EXECUTE 'UPDATE tbl SET '397 || quote_ident(colname)398 || ' = '399 || quote_literal(newvalue)400 || ' WHERE key = '401 || quote_literal(keyvalue);402</pre><p>403 </p><p>404 This example demonstrates the use of the405 <code class="function">quote_ident</code> and406 <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 column407 or table identifiers should be passed through408 <code class="function">quote_ident</code> before insertion in a dynamic query.409 Expressions containing values that should be literal strings in the410 constructed command should be passed through <code class="function">quote_literal</code>.411 These functions take the appropriate steps to return the input text412 enclosed in double or single quotes respectively, with any embedded413 special characters properly escaped.414 </p><p>415 Because <code class="function">quote_literal</code> is labeled416 <code class="literal">STRICT</code>, it will always return null when called with a417 null argument. In the above example, if <code class="literal">newvalue</code> or418 <code class="literal">keyvalue</code> were null, the entire dynamic query string would419 become null, leading to an error from <code class="command">EXECUTE</code>.420 You can avoid this problem by using the <code class="function">quote_nullable</code>421 function, which works the same as <code class="function">quote_literal</code> except that422 when called with a null argument it returns the string <code class="literal">NULL</code>.423 For example,424</p><pre class="programlisting">425EXECUTE 'UPDATE tbl SET '426 || quote_ident(colname)427 || ' = '428 || quote_nullable(newvalue)429 || ' WHERE key = '430 || quote_nullable(keyvalue);431</pre><p>432 If you are dealing with values that might be null, you should usually433 use <code class="function">quote_nullable</code> in place of <code class="function">quote_literal</code>.434 </p><p>435 As always, care must be taken to ensure that null values in a query do436 not deliver unintended results. For example the <code class="literal">WHERE</code> clause437</p><pre class="programlisting">438'WHERE key = ' || quote_nullable(keyvalue)439</pre><p>440 will never succeed if <code class="literal">keyvalue</code> is null, because the441 result of using the equality operator <code class="literal">=</code> with a null operand442 is always null. If you wish null to work like an ordinary key value,443 you would need to rewrite the above as444</p><pre class="programlisting">445'WHERE key IS NOT DISTINCT FROM ' || quote_nullable(keyvalue)446</pre><p>447 (At present, <code class="literal">IS NOT DISTINCT FROM</code> is handled much less448 efficiently than <code class="literal">=</code>, so don't do this unless you must.449 See <a class="xref" href="functions-comparison.html" title="9.2. Comparison Functions and Operators">Section 9.2</a> for450 more information on nulls and <code class="literal">IS DISTINCT</code>.)451 </p><p>452 Note that dollar quoting is only useful for quoting fixed text.453 It would be a very bad idea to try to write this example as:454</p><pre class="programlisting">455EXECUTE 'UPDATE tbl SET '456 || quote_ident(colname)457 || ' = $$'458 || newvalue459 || '$$ WHERE key = '460 || quote_literal(keyvalue);461</pre><p>462 because it would break if the contents of <code class="literal">newvalue</code>463 happened to contain <code class="literal">$$</code>. The same objection would464 apply to any other dollar-quoting delimiter you might pick.465 So, to safely quote text that is not known in advance, you466 <span class="emphasis"><em>must</em></span> use <code class="function">quote_literal</code>,467 <code class="function">quote_nullable</code>, or <code class="function">quote_ident</code>, as appropriate.468 </p><p>469 Dynamic SQL statements can also be safely constructed using the470 <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:471</p><pre class="programlisting">472EXECUTE format('UPDATE tbl SET %I = %L '473 'WHERE key = %L', colname, newvalue, keyvalue);474</pre><p>475 <code class="literal">%I</code> is equivalent to <code class="function">quote_ident</code>, and476 <code class="literal">%L</code> is equivalent to <code class="function">quote_nullable</code>.477 The <code class="function">format</code> function can be used in conjunction with478 the <code class="literal">USING</code> clause:479</p><pre class="programlisting">480EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname)481 USING newvalue, keyvalue;482</pre><p>483 This form is better because the variables are handled in their native484 data type format, rather than unconditionally converting them to485 text and quoting them via <code class="literal">%L</code>. It is also more efficient.486 </p></div></div><br class="example-break" /><p>487 A much larger example of a dynamic command and488 <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 a489 <code class="command">CREATE FUNCTION</code> command to define a new function.490 </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>491 There are several ways to determine the effect of a command. The492 first method is to use the <code class="command">GET DIAGNOSTICS</code>493 command, which has the form:494 495</p><pre class="synopsis">496GET [<span class="optional"> CURRENT </span>] DIAGNOSTICS <em class="replaceable"><code>variable</code></em> { = | := } <em class="replaceable"><code>item</code></em> [<span class="optional"> , ... </span>];497</pre><p>498 499 This command allows retrieval of system status indicators.500 <code class="literal">CURRENT</code> is a noise word (but see also <code class="command">GET STACKED501 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>).502 Each <em class="replaceable"><code>item</code></em> is a key word identifying a status503 value to be assigned to the specified <em class="replaceable"><code>variable</code></em>504 (which should be of the right data type to receive it). The currently505 available status items are shown506 in <a class="xref" href="plpgsql-statements.html#PLPGSQL-CURRENT-DIAGNOSTICS-VALUES" title="Table 43.1. Available Diagnostics Items">Table 43.1</a>. Colon-equal507 (<code class="literal">:=</code>) can be used instead of the SQL-standard <code class="literal">=</code>508 token. An example:509</p><pre class="programlisting">510GET DIAGNOSTICS integer_var = ROW_COUNT;511</pre><p>512 </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 most513 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 stack514 (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>515 The second method to determine the effects of a command is to check the516 special variable named <code class="literal">FOUND</code>, which is of517 type <code class="type">boolean</code>. <code class="literal">FOUND</code> starts out518 false within each <span class="application">PL/pgSQL</span> function call.519 It is set by each of the following types of statements:520 521 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>522 A <code class="command">SELECT INTO</code> statement sets523 <code class="literal">FOUND</code> true if a row is assigned, false if no524 row is returned.525 </p></li><li class="listitem"><p>526 A <code class="command">PERFORM</code> statement sets <code class="literal">FOUND</code>527 true if it produces (and discards) one or more rows, false if528 no row is produced.529 </p></li><li class="listitem"><p>530 <code class="command">UPDATE</code>, <code class="command">INSERT</code>, <code class="command">DELETE</code>,531 and <code class="command">MERGE</code>532 statements set <code class="literal">FOUND</code> true if at least one533 row is affected, false if no row is affected.534 </p></li><li class="listitem"><p>535 A <code class="command">FETCH</code> statement sets <code class="literal">FOUND</code>536 true if it returns a row, false if no row is returned.537 </p></li><li class="listitem"><p>538 A <code class="command">MOVE</code> statement sets <code class="literal">FOUND</code>539 true if it successfully repositions the cursor, false otherwise.540 </p></li><li class="listitem"><p>541 A <code class="command">FOR</code> or <code class="command">FOREACH</code> statement sets542 <code class="literal">FOUND</code> true543 if it iterates one or more times, else false.544 <code class="literal">FOUND</code> is set this way when the545 loop exits; inside the execution of the loop,546 <code class="literal">FOUND</code> is not modified by the547 loop statement, although it might be changed by the548 execution of other statements within the loop body.549 </p></li><li class="listitem"><p>550 <code class="command">RETURN QUERY</code> and <code class="command">RETURN QUERY551 EXECUTE</code> statements set <code class="literal">FOUND</code>552 true if the query returns at least one row, false if no row553 is returned.554 </p></li></ul></div><p>555 556 Other <span class="application">PL/pgSQL</span> statements do not change557 the state of <code class="literal">FOUND</code>.558 Note in particular that <code class="command">EXECUTE</code>559 changes the output of <code class="command">GET DIAGNOSTICS</code>, but560 does not change <code class="literal">FOUND</code>.561 </p><p>562 <code class="literal">FOUND</code> is a local variable within each563 <span class="application">PL/pgSQL</span> function; any changes to it564 affect only the current function.565 </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>566 Sometimes a placeholder statement that does nothing is useful.567 For example, it can indicate that one arm of an if/then/else568 chain is deliberately empty. For this purpose, use the569 <code class="command">NULL</code> statement:570 571</p><pre class="synopsis">572NULL;573</pre><p>574 </p><p>575 For example, the following two fragments of code are equivalent:576</p><pre class="programlisting">577BEGIN578 y := x / 0;579EXCEPTION580 WHEN division_by_zero THEN581 NULL; -- ignore the error582END;583</pre><p>584 585</p><pre class="programlisting">586BEGIN587 y := x / 0;588EXCEPTION589 WHEN division_by_zero THEN -- ignore the error590END;591</pre><p>592 Which is preferable is a matter of taste.593 </p><div class="note"><h3 class="title">Note</h3><p>594 In Oracle's PL/SQL, empty statement lists are not allowed, and so595 <code class="command">NULL</code> statements are <span class="emphasis"><em>required</em></span> for situations596 such as this. <span class="application">PL/pgSQL</span> allows you to597 just write nothing, instead.598 </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.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 43.6. Control Structures</td></tr></table></div></body></html>