begriffs open source - ai-pg/blob - full-docs/html/plpgsql-statements.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>41.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="41.4. Expressions" /><link rel="next" href="plpgsql-control-structures.html" title="41.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">41.5. Basic Statements</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="plpgsql-expressions.html" title="41.4. Expressions">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="plpgsql.html" title="Chapter 41. PL/pgSQL — SQL Procedural Language">Up</a></td><th width="60%" align="center">Chapter 41. <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 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="plpgsql-control-structures.html" title="41.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">41.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">41.5.1. Assignment</a></span></dt><dt><span class="sect2"><a href="plpgsql-statements.html#PLPGSQL-STATEMENTS-GENERAL-SQL">41.5.2. Executing SQL Commands</a></span></dt><dt><span class="sect2"><a href="plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW">41.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">41.5.4. Executing Dynamic Commands</a></span></dt><dt><span class="sect2"><a href="plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS">41.5.5. Obtaining the Result Status</a></span></dt><dt><span class="sect2"><a href="plpgsql-statements.html#PLPGSQL-STATEMENTS-NULL">41.5.6. Doing Nothing At All</a></span></dt></dl></div><p>
   3     In this section and the following ones, we describe all the statement
   4     types that are explicitly understood by
   5     <span class="application">PL/pgSQL</span>.
   6     Anything not recognized as one of these statement types is presumed
   7     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="41.5.2. Executing SQL Commands">Section 41.5.2</a>.
   9    </p><div class="sect2" id="PLPGSQL-STATEMENTS-ASSIGNMENT"><div class="titlepage"><div><div><h3 class="title">41.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 evaluated
  16      by means of an SQL <code class="command">SELECT</code> command sent to the main
  17      database engine.  The expression must yield a single value (possibly
  18      a row value, if the variable is a row or record variable).  The target
  19      variable can be a simple variable (optionally qualified with a block
  20      name), a field of a row or record target, or an element or slice of
  21      an array target.  Equal (<code class="literal">=</code>) can be
  22      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's
  25      data type, the value will be coerced as though by an assignment cast
  26      (see <a class="xref" href="typeconv-query.html" title="10.4. Value Storage">Section 10.4</a>).  If no assignment cast is known
  27      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 is
  29      by applying the result type's output function followed by the variable
  30      type's input function.  Note that this could result in run-time errors
  31      generated by the input function, if the string form of the result value
  32      is not acceptable to the input function.
  33     </p><p>
  34      Examples:
  35 </p><pre class="programlisting">
  36 tax := subtotal * 0.06;
  37 my_record.user_id := 20;
  38 my_array[j] := 20;
  39 my_array[1:3] := array[1,2,3];
  40 complex_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">41.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 executed
  44      within a <span class="application">PL/pgSQL</span> function just by writing
  45      the command.  For example, you could create and fill a table by writing
  46 </p><pre class="programlisting">
  47 CREATE TABLE mytable (id int primary key, data text);
  48 INSERT 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>, or
  52      <code class="command">INSERT</code>/<code class="command">UPDATE</code>/<code class="command">DELETE</code>/<code class="command">MERGE</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 about
  55      the first row of output, write the command as usual but add
  56      an <code class="literal">INTO</code> clause to capture the output, as described
  57      in <a class="xref" href="plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW" title="41.5.3. Executing a Command with a Single-Row Result">Section 41.5.3</a>.
  58      To process all of the output rows, write the command as the data
  59      source for a <code class="command">FOR</code> loop, as described in
  60      <a class="xref" href="plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING" title="41.6.6. Looping through Query Results">Section 41.6.6</a>.
  61     </p><p>
  62      Usually it is not sufficient just to execute statically-defined SQL
  63      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 different
  65      table names at different times.  Again, there are two ways to proceed
  66      depending on the situation.
  67     </p><p>
  68      <span class="application">PL/pgSQL</span> variable values can be
  69      automatically inserted into optimizable SQL commands, which
  70      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 certain
  73      utility commands that incorporate one of these, such
  74      as <code class="command">EXPLAIN</code> and <code class="command">CREATE TABLE ... AS
  75      SELECT</code>.  In these commands,
  76      any <span class="application">PL/pgSQL</span> variable name appearing
  77      in the command text is replaced by a query parameter, and then the
  78      current value of the variable is provided as the parameter value
  79      at run time.  This is exactly like the processing described earlier
  80      for expressions; for details see <a class="xref" href="plpgsql-implementation.html#PLPGSQL-VAR-SUBST" title="41.11.1. Variable Substitution">Section 41.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 execution
  84      plan for the command, as discussed in
  85      <a class="xref" href="plpgsql-implementation.html#PLPGSQL-PLAN-CACHING" title="41.11.2. Plan Caching">Section 41.11.2</a>.
  86     </p><p>
  87      Non-optimizable SQL commands (also called utility commands) are not
  88      capable of accepting query parameters.  So automatic substitution
  89      of <span class="application">PL/pgSQL</span> variables does not work in such
  90      commands.  To include non-constant text in a utility command executed
  91      from <span class="application">PL/pgSQL</span>, you must build the utility
  92      command as a string and then <code class="command">EXECUTE</code> it, as
  93      discussed in <a class="xref" href="plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN" title="41.5.4. Executing Dynamic Commands">Section 41.5.4</a>.
  94     </p><p>
  95      <code class="command">EXECUTE</code> must also be used if you want to modify
  96      the command in some other way than supplying a data value, for example
  97      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 function
 101      that has side-effects but no useful result value.  To do
 102      this in <span class="application">PL/pgSQL</span>, use the
 103      <code class="command">PERFORM</code> statement:
 104
 105 </p><pre class="synopsis">
 106 PERFORM <em class="replaceable"><code>query</code></em>;
 107 </pre><p>
 108
 109      This executes <em class="replaceable"><code>query</code></em> and discards the
 110      result.  Write the <em class="replaceable"><code>query</code></em> the same
 111      way you would write an SQL <code class="command">SELECT</code> command, but replace the
 112      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 then
 114      place the query in parentheses.  (In this case, the query can only
 115      return one row.)
 116      <span class="application">PL/pgSQL</span> variables will be
 117      substituted into the query just as described above,
 118      and the plan is cached in the same way.  Also, the special variable
 119      <code class="literal">FOUND</code> is set to true if the query produced at
 120      least one row, or false if it produced no rows (see
 121      <a class="xref" href="plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS" title="41.5.5. Obtaining the Result Status">Section 41.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> directly
 124       would accomplish this result, but at
 125       present the only accepted way to do it is
 126       <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 error
 128       unless it has an <code class="literal">INTO</code> clause as discussed in the
 129       next section.
 130      </p></div><p>
 131      An example:
 132 </p><pre class="programlisting">
 133 PERFORM 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">41.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 multiple
 137      columns) can be assigned to a record variable, row-type variable, or list
 138      of scalar variables.  This is done by writing the base SQL command and
 139      adding an <code class="literal">INTO</code> clause.  For example,
 140
 141 </p><pre class="synopsis">
 142 SELECT <em class="replaceable"><code>select_expressions</code></em> INTO [<span class="optional">STRICT</span>] <em class="replaceable"><code>target</code></em> FROM ...;
 143 INSERT ... RETURNING <em class="replaceable"><code>expressions</code></em> INTO [<span class="optional">STRICT</span>] <em class="replaceable"><code>target</code></em>;
 144 UPDATE ... RETURNING <em class="replaceable"><code>expressions</code></em> INTO [<span class="optional">STRICT</span>] <em class="replaceable"><code>target</code></em>;
 145 DELETE ... RETURNING <em class="replaceable"><code>expressions</code></em> INTO [<span class="optional">STRICT</span>] <em class="replaceable"><code>target</code></em>;
 146 MERGE ... RETURNING <em class="replaceable"><code>expressions</code></em> INTO [<span class="optional">STRICT</span>] <em class="replaceable"><code>target</code></em>;
 147 </pre><p>
 148
 149      where <em class="replaceable"><code>target</code></em> can be a record variable, a row
 150      variable, or a comma-separated list of simple variables and
 151      record/row fields.
 152      <span class="application">PL/pgSQL</span> variables will be
 153      substituted into the rest of the command (that is, everything but the
 154      <code class="literal">INTO</code> clause) just as described above,
 155      and the plan is cached in the same way.
 156      This works for <code class="command">SELECT</code>,
 157      <code class="command">INSERT</code>/<code class="command">UPDATE</code>/<code class="command">DELETE</code>/<code class="command">MERGE</code>
 158      with <code class="literal">RETURNING</code>, and certain utility commands
 159      that return row sets, such as <code class="command">EXPLAIN</code>.
 160      Except for the <code class="literal">INTO</code> clause, the SQL command is the same
 161      as it would be written outside <span class="application">PL/pgSQL</span>.
 162     </p><div class="tip"><h3 class="title">Tip</h3><p>
 163      Note that this interpretation of <code class="command">SELECT</code> with <code class="literal">INTO</code>
 164      is quite different from <span class="productname">PostgreSQL</span>'s regular
 165      <code class="command">SELECT INTO</code> command, wherein the <code class="literal">INTO</code>
 166      target is a newly created table.  If you want to create a table from a
 167      <code class="command">SELECT</code> result inside a
 168      <span class="application">PL/pgSQL</span> function, use the syntax
 169      <code class="command">CREATE TABLE ... AS SELECT</code>.
 170     </p></div><p>
 171      If a row variable or a variable list is used as target,
 172      the command's result columns
 173      must exactly match the structure of the target as to number and data
 174      types, or else a run-time error
 175      occurs.  When a record variable is the target, it automatically
 176      configures itself to the row type of the command's result columns.
 177     </p><p>
 178      The <code class="literal">INTO</code> clause can appear almost anywhere in the SQL
 179      command.  Customarily it is written either just before or just after
 180      the list of <em class="replaceable"><code>select_expressions</code></em> in a
 181      <code class="command">SELECT</code> command, or at the end of the command for other
 182      command types.  It is recommended that you follow this convention
 183      in case the <span class="application">PL/pgSQL</span> parser becomes
 184      stricter in future versions.
 185     </p><p>
 186      If <code class="literal">STRICT</code> is not specified in the <code class="literal">INTO</code>
 187      clause, then <em class="replaceable"><code>target</code></em> will be set to the first
 188      row returned by the command, or to nulls if the command returned no rows.
 189      (Note that <span class="quote">“<span class="quote">the first row</span>”</span> is not
 190      well-defined unless you've used <code class="literal">ORDER BY</code>.)  Any result rows
 191      after the first row are discarded.
 192      You can check the special <code class="literal">FOUND</code> variable (see
 193      <a class="xref" href="plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS" title="41.5.5. Obtaining the Result Status">Section 41.5.5</a>) to
 194      determine whether a row was returned:
 195
 196 </p><pre class="programlisting">
 197 SELECT * INTO myrec FROM emp WHERE empname = myname;
 198 IF NOT FOUND THEN
 199     RAISE EXCEPTION 'employee % not found', myname;
 200 END IF;
 201 </pre><p>
 202
 203      If the <code class="literal">STRICT</code> option is specified, the command must
 204      return exactly one row or a run-time error will be reported, either
 205      <code class="literal">NO_DATA_FOUND</code> (no rows) or <code class="literal">TOO_MANY_ROWS</code>
 206      (more than one row). You can use an exception block if you wish
 207      to catch the error, for example:
 208
 209 </p><pre class="programlisting">
 210 BEGIN
 211     SELECT * INTO STRICT myrec FROM emp WHERE empname = myname;
 212     EXCEPTION
 213         WHEN NO_DATA_FOUND THEN
 214             RAISE EXCEPTION 'employee % not found', myname;
 215         WHEN TOO_MANY_ROWS THEN
 216             RAISE EXCEPTION 'employee % not unique', myname;
 217 END;
 218 </pre><p>
 219      Successful execution of a command with <code class="literal">STRICT</code>
 220      always sets <code class="literal">FOUND</code> to true.
 221     </p><p>
 222      For <code class="command">INSERT</code>/<code class="command">UPDATE</code>/<code class="command">DELETE</code>/<code class="command">MERGE</code> with
 223      <code class="literal">RETURNING</code>, <span class="application">PL/pgSQL</span> reports
 224      an error for more than one returned row, even when
 225      <code class="literal">STRICT</code> is not specified.  This is because there
 226      is no option such as <code class="literal">ORDER BY</code> with which to determine
 227      which affected row should be returned.
 228     </p><p>
 229      If <code class="literal">print_strict_params</code> is enabled for the function,
 230      then when an error is thrown because the requirements
 231      of <code class="literal">STRICT</code> are not met, the <code class="literal">DETAIL</code> part of
 232      the error message will include information about the parameters
 233      passed to the command.
 234      You can change the <code class="literal">print_strict_params</code>
 235      setting for all functions by setting
 236      <code class="varname">plpgsql.print_strict_params</code>, though only subsequent
 237      function compilations will be affected.  You can also enable it
 238      on a per-function basis by using a compiler option, for example:
 239 </p><pre class="programlisting">
 240 CREATE FUNCTION get_userid(username text) RETURNS int
 241 AS $$
 242 #print_strict_params on
 243 DECLARE
 244 userid int;
 245 BEGIN
 246     SELECT users.userid INTO STRICT userid
 247         FROM users WHERE users.username = get_userid.username;
 248     RETURN userid;
 249 END;
 250 $$ LANGUAGE plpgsql;
 251 </pre><p>
 252      On failure, this function might produce an error message such as
 253 </p><pre class="programlisting">
 254 ERROR:  query returned no rows
 255 DETAIL:  parameters: username = 'nosuchuser'
 256 CONTEXT:  PL/pgSQL function get_userid(text) line 6 at SQL statement
 257 </pre><p>
 258     </p><div class="note"><h3 class="title">Note</h3><p>
 259       The <code class="literal">STRICT</code> option matches the behavior of
 260       Oracle PL/SQL's <code class="command">SELECT INTO</code> and related statements.
 261      </p></div></div><div class="sect2" id="PLPGSQL-STATEMENTS-EXECUTING-DYN"><div class="titlepage"><div><div><h3 class="title">41.5.4. Executing Dynamic Commands <a href="#PLPGSQL-STATEMENTS-EXECUTING-DYN" class="id_link">#</a></h3></div></div></div><p>
 262      Oftentimes you will want to generate dynamic commands inside your
 263      <span class="application">PL/pgSQL</span> functions, that is, commands
 264      that will involve different tables or different data types each
 265      time they are executed.  <span class="application">PL/pgSQL</span>'s
 266      normal attempts to cache plans for commands (as discussed in
 267      <a class="xref" href="plpgsql-implementation.html#PLPGSQL-PLAN-CACHING" title="41.11.2. Plan Caching">Section 41.11.2</a>) will not work in such
 268      scenarios.  To handle this sort of problem, the
 269      <code class="command">EXECUTE</code> statement is provided:
 270
 271 </p><pre class="synopsis">
 272 EXECUTE <em class="replaceable"><code>command-string</code></em> [<span class="optional"> INTO [<span class="optional">STRICT</span>] <em class="replaceable"><code>target</code></em> </span>] [<span class="optional"> USING <em class="replaceable"><code>expression</code></em> [<span class="optional">, ... </span>] </span>];
 273 </pre><p>
 274
 275      where <em class="replaceable"><code>command-string</code></em> is an expression
 276      yielding a string (of type <code class="type">text</code>) containing the
 277      command to be executed.  The optional <em class="replaceable"><code>target</code></em>
 278      is a record variable, a row variable, or a comma-separated list of
 279      simple variables and record/row fields, into which the results of
 280      the command will be stored.  The optional <code class="literal">USING</code> expressions
 281      supply values to be inserted into the command.
 282     </p><p>
 283      No substitution of <span class="application">PL/pgSQL</span> variables is done on the
 284      computed command string.  Any required variable values must be inserted
 285      in the command string as it is constructed; or you can use parameters
 286      as described below.
 287     </p><p>
 288      Also, there is no plan caching for commands executed via
 289      <code class="command">EXECUTE</code>.  Instead, the command is always planned
 290      each time the statement is run. Thus the command
 291      string can be dynamically created within the function to perform
 292      actions on different tables and columns.
 293     </p><p>
 294      The <code class="literal">INTO</code> clause specifies where the results of
 295      an SQL command returning rows should be assigned. If a row variable
 296      or variable list is provided, it must exactly match the structure
 297      of the command's results; if a
 298      record variable is provided, it will configure itself to match the
 299      result structure automatically. If multiple rows are returned,
 300      only the first will be assigned to the <code class="literal">INTO</code>
 301      variable(s). If no rows are returned, NULL is assigned to the
 302      <code class="literal">INTO</code> variable(s). If no <code class="literal">INTO</code>
 303      clause is specified, the command results are discarded.
 304     </p><p>
 305      If the <code class="literal">STRICT</code> option is given, an error is reported
 306      unless the command produces exactly one row.
 307     </p><p>
 308      The command string can use parameter values, which are referenced
 309      in the command as <code class="literal">$1</code>, <code class="literal">$2</code>, etc.
 310      These symbols refer to values supplied in the <code class="literal">USING</code>
 311      clause.  This method is often preferable to inserting data values
 312      into the command string as text: it avoids run-time overhead of
 313      converting the values to text and back, and it is much less prone
 314      to SQL-injection attacks since there is no need for quoting or escaping.
 315      An example is:
 316 </p><pre class="programlisting">
 317 EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted &lt;= $2'
 318    INTO c
 319    USING checked_user, checked_date;
 320 </pre><p>
 321     </p><p>
 322      Note that parameter symbols can only be used for data values
 323      — if you want to use dynamically determined table or column
 324      names, you must insert them into the command string textually.
 325      For example, if the preceding query needed to be done against a
 326      dynamically selected table, you could do this:
 327 </p><pre class="programlisting">
 328 EXECUTE 'SELECT count(*) FROM '
 329     || quote_ident(tabname)
 330     || ' WHERE inserted_by = $1 AND inserted &lt;= $2'
 331    INTO c
 332    USING checked_user, checked_date;
 333 </pre><p>
 334      A cleaner approach is to use <code class="function">format()</code>'s <code class="literal">%I</code>
 335      specification to insert table or column names with automatic quoting:
 336 </p><pre class="programlisting">
 337 EXECUTE format('SELECT count(*) FROM %I '
 338    'WHERE inserted_by = $1 AND inserted &lt;= $2', tabname)
 339    INTO c
 340    USING checked_user, checked_date;
 341 </pre><p>
 342      (This example relies on the SQL rule that string literals separated by a
 343      newline are implicitly concatenated.)
 344     </p><p>
 345      Another restriction on parameter symbols is that they only work in
 346      optimizable SQL commands
 347      (<code class="command">SELECT</code>, <code class="command">INSERT</code>, <code class="command">UPDATE</code>,
 348      <code class="command">DELETE</code>, <code class="command">MERGE</code>, and certain commands containing one of these).
 349      In other statement
 350      types (generically called utility statements), you must insert
 351      values textually even if they are just data values.
 352     </p><p>
 353      An <code class="command">EXECUTE</code> with a simple constant command string and some
 354      <code class="literal">USING</code> parameters, as in the first example above, is
 355      functionally equivalent to just writing the command directly in
 356      <span class="application">PL/pgSQL</span> and allowing replacement of
 357      <span class="application">PL/pgSQL</span> variables to happen automatically.
 358      The important difference is that <code class="command">EXECUTE</code> will re-plan
 359      the command on each execution, generating a plan that is specific
 360      to the current parameter values; whereas
 361      <span class="application">PL/pgSQL</span> may otherwise create a generic plan
 362      and cache it for re-use.  In situations where the best plan depends
 363      strongly on the parameter values, it can be helpful to use
 364      <code class="command">EXECUTE</code> to positively ensure that a generic plan is not
 365      selected.
 366     </p><p>
 367      <code class="command">SELECT INTO</code> is not currently supported within
 368      <code class="command">EXECUTE</code>; instead, execute a plain <code class="command">SELECT</code>
 369      command and specify <code class="literal">INTO</code> as part of the <code class="command">EXECUTE</code>
 370      itself.
 371     </p><div class="note"><h3 class="title">Note</h3><p>
 372      The <span class="application">PL/pgSQL</span>
 373      <code class="command">EXECUTE</code> statement is not related to the
 374      <a class="link" href="sql-execute.html" title="EXECUTE"><code class="command">EXECUTE</code></a> SQL
 375      statement supported by the
 376      <span class="productname">PostgreSQL</span> server. The server's
 377      <code class="command">EXECUTE</code> statement cannot be used directly within
 378      <span class="application">PL/pgSQL</span> functions (and is not needed).
 379     </p></div><div class="example" id="PLPGSQL-QUOTE-LITERAL-EXAMPLE"><p class="title"><strong>Example 41.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>
 380      When working with dynamic commands you will often have to handle escaping
 381      of single quotes.  The recommended method for quoting fixed text in your
 382      function body is dollar quoting.  (If you have legacy code that does
 383      not use dollar quoting, please refer to the
 384      overview in <a class="xref" href="plpgsql-development-tips.html#PLPGSQL-QUOTE-TIPS" title="41.12.1. Handling of Quotation Marks">Section 41.12.1</a>, which can save you
 385      some effort when translating said code to a more reasonable scheme.)
 386     </p><p>
 387      Dynamic values require careful handling since they might contain
 388      quote characters.
 389      An example using <code class="function">format()</code> (this assumes that you are
 390      dollar quoting the function body so quote marks need not be doubled):
 391 </p><pre class="programlisting">
 392 EXECUTE format('UPDATE tbl SET %I = $1 '
 393    'WHERE key = $2', colname) USING newvalue, keyvalue;
 394 </pre><p>
 395      It is also possible to call the quoting functions directly:
 396 </p><pre class="programlisting">
 397 EXECUTE 'UPDATE tbl SET '
 398         || quote_ident(colname)
 399         || ' = '
 400         || quote_literal(newvalue)
 401         || ' WHERE key = '
 402         || quote_literal(keyvalue);
 403 </pre><p>
 404     </p><p>
 405      This example demonstrates the use of the
 406      <code class="function">quote_ident</code> and
 407      <code class="function">quote_literal</code> functions (see <a class="xref" href="functions-string.html" title="9.4. String Functions and Operators">Section 9.4</a>).  For safety, expressions containing column
 408      or table identifiers should be passed through
 409      <code class="function">quote_ident</code> before insertion in a dynamic query.
 410      Expressions containing values that should be literal strings in the
 411      constructed command should be passed through <code class="function">quote_literal</code>.
 412      These functions take the appropriate steps to return the input text
 413      enclosed in double or single quotes respectively, with any embedded
 414      special characters properly escaped.
 415     </p><p>
 416      Because <code class="function">quote_literal</code> is labeled
 417      <code class="literal">STRICT</code>, it will always return null when called with a
 418      null argument.  In the above example, if <code class="literal">newvalue</code> or
 419      <code class="literal">keyvalue</code> were null, the entire dynamic query string would
 420      become null, leading to an error from <code class="command">EXECUTE</code>.
 421      You can avoid this problem by using the <code class="function">quote_nullable</code>
 422      function, which works the same as <code class="function">quote_literal</code> except that
 423      when called with a null argument it returns the string <code class="literal">NULL</code>.
 424      For example,
 425 </p><pre class="programlisting">
 426 EXECUTE 'UPDATE tbl SET '
 427         || quote_ident(colname)
 428         || ' = '
 429         || quote_nullable(newvalue)
 430         || ' WHERE key = '
 431         || quote_nullable(keyvalue);
 432 </pre><p>
 433      If you are dealing with values that might be null, you should usually
 434      use <code class="function">quote_nullable</code> in place of <code class="function">quote_literal</code>.
 435     </p><p>
 436      As always, care must be taken to ensure that null values in a query do
 437      not deliver unintended results.  For example the <code class="literal">WHERE</code> clause
 438 </p><pre class="programlisting">
 439 'WHERE key = ' || quote_nullable(keyvalue)
 440 </pre><p>
 441      will never succeed if <code class="literal">keyvalue</code> is null, because the
 442      result of using the equality operator <code class="literal">=</code> with a null operand
 443      is always null.  If you wish null to work like an ordinary key value,
 444      you would need to rewrite the above as
 445 </p><pre class="programlisting">
 446 'WHERE key IS NOT DISTINCT FROM ' || quote_nullable(keyvalue)
 447 </pre><p>
 448      (At present, <code class="literal">IS NOT DISTINCT FROM</code> is handled much less
 449      efficiently than <code class="literal">=</code>, so don't do this unless you must.
 450      See <a class="xref" href="functions-comparison.html" title="9.2. Comparison Functions and Operators">Section 9.2</a> for
 451      more information on nulls and <code class="literal">IS DISTINCT</code>.)
 452     </p><p>
 453      Note that dollar quoting is only useful for quoting fixed text.
 454      It would be a very bad idea to try to write this example as:
 455 </p><pre class="programlisting">
 456 EXECUTE 'UPDATE tbl SET '
 457         || quote_ident(colname)
 458         || ' = $$'
 459         || newvalue
 460         || '$$ WHERE key = '
 461         || quote_literal(keyvalue);
 462 </pre><p>
 463      because it would break if the contents of <code class="literal">newvalue</code>
 464      happened to contain <code class="literal">$$</code>.  The same objection would
 465      apply to any other dollar-quoting delimiter you might pick.
 466      So, to safely quote text that is not known in advance, you
 467      <span class="emphasis"><em>must</em></span> use <code class="function">quote_literal</code>,
 468      <code class="function">quote_nullable</code>, or <code class="function">quote_ident</code>, as appropriate.
 469     </p><p>
 470      Dynamic SQL statements can also be safely constructed using the
 471      <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:
 472 </p><pre class="programlisting">
 473 EXECUTE format('UPDATE tbl SET %I = %L '
 474    'WHERE key = %L', colname, newvalue, keyvalue);
 475 </pre><p>
 476      <code class="literal">%I</code> is equivalent to <code class="function">quote_ident</code>, and
 477      <code class="literal">%L</code> is equivalent to <code class="function">quote_nullable</code>.
 478      The <code class="function">format</code> function can be used in conjunction with
 479      the <code class="literal">USING</code> clause:
 480 </p><pre class="programlisting">
 481 EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname)
 482    USING newvalue, keyvalue;
 483 </pre><p>
 484      This form is better because the variables are handled in their native
 485      data type format, rather than unconditionally converting them to
 486      text and quoting them via <code class="literal">%L</code>.  It is also more efficient.
 487     </p></div></div><br class="example-break" /><p>
 488      A much larger example of a dynamic command and
 489      <code class="command">EXECUTE</code> can be seen in <a class="xref" href="plpgsql-porting.html#PLPGSQL-PORTING-EX2" title="Example 41.10. Porting a Function that Creates Another Function from PL/SQL to PL/pgSQL">Example 41.10</a>, which builds and executes a
 490      <code class="command">CREATE FUNCTION</code> command to define a new function.
 491     </p></div><div class="sect2" id="PLPGSQL-STATEMENTS-DIAGNOSTICS"><div class="titlepage"><div><div><h3 class="title">41.5.5. Obtaining the Result Status <a href="#PLPGSQL-STATEMENTS-DIAGNOSTICS" class="id_link">#</a></h3></div></div></div><p>
 492      There are several ways to determine the effect of a command. The
 493      first method is to use the <code class="command">GET DIAGNOSTICS</code>
 494      command, which has the form:
 495
 496 </p><pre class="synopsis">
 497 GET [<span class="optional"> CURRENT </span>] DIAGNOSTICS <em class="replaceable"><code>variable</code></em> { = | := } <em class="replaceable"><code>item</code></em> [<span class="optional"> , ... </span>];
 498 </pre><p>
 499
 500      This command allows retrieval of system status indicators.
 501      <code class="literal">CURRENT</code> is a noise word (but see also <code class="command">GET STACKED
 502      DIAGNOSTICS</code> in <a class="xref" href="plpgsql-control-structures.html#PLPGSQL-EXCEPTION-DIAGNOSTICS" title="41.6.8.1. Obtaining Information about an Error">Section 41.6.8.1</a>).
 503      Each <em class="replaceable"><code>item</code></em> is a key word identifying a status
 504      value to be assigned to the specified <em class="replaceable"><code>variable</code></em>
 505      (which should be of the right data type to receive it).  The currently
 506      available status items are shown
 507      in <a class="xref" href="plpgsql-statements.html#PLPGSQL-CURRENT-DIAGNOSTICS-VALUES" title="Table 41.1. Available Diagnostics Items">Table 41.1</a>.  Colon-equal
 508      (<code class="literal">:=</code>) can be used instead of the SQL-standard <code class="literal">=</code>
 509      token.  An example:
 510 </p><pre class="programlisting">
 511 GET DIAGNOSTICS integer_var = ROW_COUNT;
 512 </pre><p>
 513     </p><div class="table" id="PLPGSQL-CURRENT-DIAGNOSTICS-VALUES"><p class="title"><strong>Table 41.1. Available Diagnostics Items</strong></p><div class="table-contents"><table class="table" summary="Available Diagnostics Items" border="1"><colgroup><col class="col1" /><col class="col2" /><col class="col3" /></colgroup><thead><tr><th>Name</th><th>Type</th><th>Description</th></tr></thead><tbody><tr><td><code class="varname">ROW_COUNT</code></td><td><code class="type">bigint</code></td><td>the number of rows processed by the most
 514           recent <acronym class="acronym">SQL</acronym> command</td></tr><tr><td><code class="literal">PG_CONTEXT</code></td><td><code class="type">text</code></td><td>line(s) of text describing the current call stack
 515           (see <a class="xref" href="plpgsql-control-structures.html#PLPGSQL-CALL-STACK" title="41.6.9. Obtaining Execution Location Information">Section 41.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>
 516      The second method to determine the effects of a command is to check the
 517      special variable named <code class="literal">FOUND</code>, which is of
 518      type <code class="type">boolean</code>.  <code class="literal">FOUND</code> starts out
 519      false within each <span class="application">PL/pgSQL</span> function call.
 520      It is set by each of the following types of statements:
 521
 522          </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
 523             A <code class="command">SELECT INTO</code> statement sets
 524             <code class="literal">FOUND</code> true if a row is assigned, false if no
 525             row is returned.
 526            </p></li><li class="listitem"><p>
 527             A <code class="command">PERFORM</code> statement sets <code class="literal">FOUND</code>
 528             true if it produces (and discards) one or more rows, false if
 529             no row is produced.
 530            </p></li><li class="listitem"><p>
 531             <code class="command">UPDATE</code>, <code class="command">INSERT</code>, <code class="command">DELETE</code>,
 532             and <code class="command">MERGE</code>
 533             statements set <code class="literal">FOUND</code> true if at least one
 534             row is affected, false if no row is affected.
 535            </p></li><li class="listitem"><p>
 536             A <code class="command">FETCH</code> statement sets <code class="literal">FOUND</code>
 537             true if it returns a row, false if no row is returned.
 538            </p></li><li class="listitem"><p>
 539             A <code class="command">MOVE</code> statement sets <code class="literal">FOUND</code>
 540             true if it successfully repositions the cursor, false otherwise.
 541            </p></li><li class="listitem"><p>
 542             A <code class="command">FOR</code> or <code class="command">FOREACH</code> statement sets
 543             <code class="literal">FOUND</code> true
 544             if it iterates one or more times, else false.
 545             <code class="literal">FOUND</code> is set this way when the
 546             loop exits; inside the execution of the loop,
 547             <code class="literal">FOUND</code> is not modified by the
 548             loop statement, although it might be changed by the
 549             execution of other statements within the loop body.
 550            </p></li><li class="listitem"><p>
 551             <code class="command">RETURN QUERY</code> and <code class="command">RETURN QUERY
 552             EXECUTE</code> statements set <code class="literal">FOUND</code>
 553             true if the query returns at least one row, false if no row
 554             is returned.
 555            </p></li></ul></div><p>
 556
 557      Other <span class="application">PL/pgSQL</span> statements do not change
 558      the state of <code class="literal">FOUND</code>.
 559      Note in particular that <code class="command">EXECUTE</code>
 560      changes the output of <code class="command">GET DIAGNOSTICS</code>, but
 561      does not change <code class="literal">FOUND</code>.
 562     </p><p>
 563      <code class="literal">FOUND</code> is a local variable within each
 564      <span class="application">PL/pgSQL</span> function; any changes to it
 565      affect only the current function.
 566     </p></div><div class="sect2" id="PLPGSQL-STATEMENTS-NULL"><div class="titlepage"><div><div><h3 class="title">41.5.6. Doing Nothing At All <a href="#PLPGSQL-STATEMENTS-NULL" class="id_link">#</a></h3></div></div></div><p>
 567      Sometimes a placeholder statement that does nothing is useful.
 568      For example, it can indicate that one arm of an if/then/else
 569      chain is deliberately empty.  For this purpose, use the
 570      <code class="command">NULL</code> statement:
 571
 572 </p><pre class="synopsis">
 573 NULL;
 574 </pre><p>
 575     </p><p>
 576      For example, the following two fragments of code are equivalent:
 577 </p><pre class="programlisting">
 578 BEGIN
 579     y := x / 0;
 580 EXCEPTION
 581     WHEN division_by_zero THEN
 582         NULL;  -- ignore the error
 583 END;
 584 </pre><p>
 585
 586 </p><pre class="programlisting">
 587 BEGIN
 588     y := x / 0;
 589 EXCEPTION
 590     WHEN division_by_zero THEN  -- ignore the error
 591 END;
 592 </pre><p>
 593      Which is preferable is a matter of taste.
 594     </p><div class="note"><h3 class="title">Note</h3><p>
 595       In Oracle's PL/SQL, empty statement lists are not allowed, and so
 596       <code class="command">NULL</code> statements are <span class="emphasis"><em>required</em></span> for situations
 597       such as this.  <span class="application">PL/pgSQL</span> allows you to
 598       just write nothing, instead.
 599      </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="41.4. Expressions">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="plpgsql.html" title="Chapter 41. PL/pgSQL — SQL Procedural Language">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="plpgsql-control-structures.html" title="41.6. Control Structures">Next</a></td></tr><tr><td width="40%" align="left" valign="top">41.4. Expressions </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 41.6. Control Structures</td></tr></table></div></body></html>