Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
plpgsql-statements.html598 linesDownload Raw Back to html
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 &lt;= $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 &lt;= $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 &lt;= $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>
codekingpro/portable-devtools · Team Ai