diff options
| author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-04-13 13:44:03 +0000 |
|---|---|---|
| committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-04-13 13:44:03 +0000 |
| commit | 293913568e6a7a86fd1479e1cff8e2ecb58d6568 (patch) | |
| tree | fc3b469a3ec5ab71b36ea97cc7aaddb838423a0c /doc/src/sgml/html/plpgsql-statements.html | |
| parent | Initial commit. (diff) | |
| download | postgresql-16-293913568e6a7a86fd1479e1cff8e2ecb58d6568.tar.xz postgresql-16-293913568e6a7a86fd1479e1cff8e2ecb58d6568.zip | |
Adding upstream version 16.2.upstream/16.2
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'doc/src/sgml/html/plpgsql-statements.html')
| -rw-r--r-- | doc/src/sgml/html/plpgsql-statements.html | 598 |
1 files changed, 598 insertions, 0 deletions
diff --git a/doc/src/sgml/html/plpgsql-statements.html b/doc/src/sgml/html/plpgsql-statements.html new file mode 100644 index 0000000..f9e15dd --- /dev/null +++ b/doc/src/sgml/html/plpgsql-statements.html @@ -0,0 +1,598 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>43.5. Basic Statements</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="plpgsql-expressions.html" title="43.4. Expressions" /><link rel="next" href="plpgsql-control-structures.html" title="43.6. Control Structures" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">43.5. Basic Statements</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="plpgsql-expressions.html" title="43.4. Expressions">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="plpgsql.html" title="Chapter 43. PL/pgSQL — SQL Procedural Language">Up</a></td><th width="60%" align="center">Chapter 43. <span class="application">PL/pgSQL</span> — <acronym class="acronym">SQL</acronym> Procedural Language</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.2 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="plpgsql-control-structures.html" title="43.6. Control Structures">Next</a></td></tr></table><hr /></div><div class="sect1" id="PLPGSQL-STATEMENTS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">43.5. Basic Statements <a href="#PLPGSQL-STATEMENTS" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT">43.5.1. Assignment</a></span></dt><dt><span class="sect2"><a href="plpgsql-statements.html#PLPGSQL-STATEMENTS-GENERAL-SQL">43.5.2. Executing SQL Commands</a></span></dt><dt><span class="sect2"><a href="plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW">43.5.3. Executing a Command with a Single-Row Result</a></span></dt><dt><span class="sect2"><a href="plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN">43.5.4. Executing Dynamic Commands</a></span></dt><dt><span class="sect2"><a href="plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS">43.5.5. Obtaining the Result Status</a></span></dt><dt><span class="sect2"><a href="plpgsql-statements.html#PLPGSQL-STATEMENTS-NULL">43.5.6. Doing Nothing At All</a></span></dt></dl></div><p> + In this section and the following ones, we describe all the statement + types that are explicitly understood by + <span class="application">PL/pgSQL</span>. + Anything not recognized as one of these statement types is presumed + to be an SQL command and is sent to the main database engine to execute, + as described in <a class="xref" href="plpgsql-statements.html#PLPGSQL-STATEMENTS-GENERAL-SQL" title="43.5.2. Executing SQL Commands">Section 43.5.2</a>. + </p><div class="sect2" id="PLPGSQL-STATEMENTS-ASSIGNMENT"><div class="titlepage"><div><div><h3 class="title">43.5.1. Assignment <a href="#PLPGSQL-STATEMENTS-ASSIGNMENT" class="id_link">#</a></h3></div></div></div><p> + An assignment of a value to a <span class="application">PL/pgSQL</span> + variable is written as: +</p><pre class="synopsis"> +<em class="replaceable"><code>variable</code></em> { := | = } <em class="replaceable"><code>expression</code></em>; +</pre><p> + As explained previously, the expression in such a statement is evaluated + by means of an SQL <code class="command">SELECT</code> command sent to the main + database engine. The expression must yield a single value (possibly + a row value, if the variable is a row or record variable). The target + variable can be a simple variable (optionally qualified with a block + name), a field of a row or record target, or an element or slice of + an array target. Equal (<code class="literal">=</code>) can be + used instead of PL/SQL-compliant <code class="literal">:=</code>. + </p><p> + If the expression's result data type doesn't match the variable's + data type, the value will be coerced as though by an assignment cast + (see <a class="xref" href="typeconv-query.html" title="10.4. Value Storage">Section 10.4</a>). If no assignment cast is known + for the pair of data types involved, the <span class="application">PL/pgSQL</span> + interpreter will attempt to convert the result value textually, that is + by applying the result type's output function followed by the variable + type's input function. Note that this could result in run-time errors + generated by the input function, if the string form of the result value + is not acceptable to the input function. + </p><p> + Examples: +</p><pre class="programlisting"> +tax := subtotal * 0.06; +my_record.user_id := 20; +my_array[j] := 20; +my_array[1:3] := array[1,2,3]; +complex_array[n].realpart = 12.3; +</pre><p> + </p></div><div class="sect2" id="PLPGSQL-STATEMENTS-GENERAL-SQL"><div class="titlepage"><div><div><h3 class="title">43.5.2. Executing SQL Commands <a href="#PLPGSQL-STATEMENTS-GENERAL-SQL" class="id_link">#</a></h3></div></div></div><p> + In general, any SQL command that does not return rows can be executed + within a <span class="application">PL/pgSQL</span> function just by writing + the command. For example, you could create and fill a table by writing +</p><pre class="programlisting"> +CREATE TABLE mytable (id int primary key, data text); +INSERT INTO mytable VALUES (1,'one'), (2,'two'); +</pre><p> + </p><p> + If the command does return rows (for example <code class="command">SELECT</code>, + or <code class="command">INSERT</code>/<code class="command">UPDATE</code>/<code class="command">DELETE</code> + with <code class="literal">RETURNING</code>), there are two ways to proceed. + When the command will return at most one row, or you only care about + the first row of output, write the command as usual but add + an <code class="literal">INTO</code> clause to capture the output, as described + in <a class="xref" href="plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW" title="43.5.3. Executing a Command with a Single-Row Result">Section 43.5.3</a>. + To process all of the output rows, write the command as the data + source for a <code class="command">FOR</code> loop, as described in + <a class="xref" href="plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING" title="43.6.6. Looping through Query Results">Section 43.6.6</a>. + </p><p> + Usually it is not sufficient just to execute statically-defined SQL + commands. Typically you'll want a command to use varying data values, + or even to vary in more fundamental ways such as by using different + table names at different times. Again, there are two ways to proceed + depending on the situation. + </p><p> + <span class="application">PL/pgSQL</span> variable values can be + automatically inserted into optimizable SQL commands, which + are <code class="command">SELECT</code>, <code class="command">INSERT</code>, + <code class="command">UPDATE</code>, <code class="command">DELETE</code>, + <code class="command">MERGE</code>, and certain + utility commands that incorporate one of these, such + as <code class="command">EXPLAIN</code> and <code class="command">CREATE TABLE ... AS + SELECT</code>. In these commands, + any <span class="application">PL/pgSQL</span> variable name appearing + in the command text is replaced by a query parameter, and then the + current value of the variable is provided as the parameter value + at run time. This is exactly like the processing described earlier + for expressions; for details see <a class="xref" href="plpgsql-implementation.html#PLPGSQL-VAR-SUBST" title="43.11.1. Variable Substitution">Section 43.11.1</a>. + </p><p> + When executing an optimizable SQL command in this way, + <span class="application">PL/pgSQL</span> may cache and re-use the execution + plan for the command, as discussed in + <a class="xref" href="plpgsql-implementation.html#PLPGSQL-PLAN-CACHING" title="43.11.2. Plan Caching">Section 43.11.2</a>. + </p><p> + Non-optimizable SQL commands (also called utility commands) are not + capable of accepting query parameters. So automatic substitution + of <span class="application">PL/pgSQL</span> variables does not work in such + commands. To include non-constant text in a utility command executed + from <span class="application">PL/pgSQL</span>, you must build the utility + command as a string and then <code class="command">EXECUTE</code> it, as + discussed in <a class="xref" href="plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN" title="43.5.4. Executing Dynamic Commands">Section 43.5.4</a>. + </p><p> + <code class="command">EXECUTE</code> must also be used if you want to modify + the command in some other way than supplying a data value, for example + by changing a table name. + </p><p> + Sometimes it is useful to evaluate an expression or <code class="command">SELECT</code> + query but discard the result, for example when calling a function + that has side-effects but no useful result value. To do + this in <span class="application">PL/pgSQL</span>, use the + <code class="command">PERFORM</code> statement: + +</p><pre class="synopsis"> +PERFORM <em class="replaceable"><code>query</code></em>; +</pre><p> + + This executes <em class="replaceable"><code>query</code></em> and discards the + result. Write the <em class="replaceable"><code>query</code></em> the same + way you would write an SQL <code class="command">SELECT</code> command, but replace the + initial keyword <code class="command">SELECT</code> with <code class="command">PERFORM</code>. + For <code class="command">WITH</code> queries, use <code class="command">PERFORM</code> and then + place the query in parentheses. (In this case, the query can only + return one row.) + <span class="application">PL/pgSQL</span> variables will be + substituted into the query just as described above, + and the plan is cached in the same way. Also, the special variable + <code class="literal">FOUND</code> is set to true if the query produced at + least one row, or false if it produced no rows (see + <a class="xref" href="plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS" title="43.5.5. Obtaining the Result Status">Section 43.5.5</a>). + </p><div class="note"><h3 class="title">Note</h3><p> + One might expect that writing <code class="command">SELECT</code> directly + would accomplish this result, but at + present the only accepted way to do it is + <code class="command">PERFORM</code>. An SQL command that can return rows, + such as <code class="command">SELECT</code>, will be rejected as an error + unless it has an <code class="literal">INTO</code> clause as discussed in the + next section. + </p></div><p> + An example: +</p><pre class="programlisting"> +PERFORM create_mv('cs_session_page_requests_mv', my_query); +</pre><p> + </p></div><div class="sect2" id="PLPGSQL-STATEMENTS-SQL-ONEROW"><div class="titlepage"><div><div><h3 class="title">43.5.3. Executing a Command with a Single-Row Result <a href="#PLPGSQL-STATEMENTS-SQL-ONEROW" class="id_link">#</a></h3></div></div></div><a id="id-1.8.8.7.5.2" class="indexterm"></a><a id="id-1.8.8.7.5.3" class="indexterm"></a><p> + The result of an SQL command yielding a single row (possibly of multiple + columns) can be assigned to a record variable, row-type variable, or list + of scalar variables. This is done by writing the base SQL command and + adding an <code class="literal">INTO</code> clause. For example, + +</p><pre class="synopsis"> +SELECT <em class="replaceable"><code>select_expressions</code></em> INTO [<span class="optional">STRICT</span>] <em class="replaceable"><code>target</code></em> FROM ...; +INSERT ... RETURNING <em class="replaceable"><code>expressions</code></em> INTO [<span class="optional">STRICT</span>] <em class="replaceable"><code>target</code></em>; +UPDATE ... RETURNING <em class="replaceable"><code>expressions</code></em> INTO [<span class="optional">STRICT</span>] <em class="replaceable"><code>target</code></em>; +DELETE ... RETURNING <em class="replaceable"><code>expressions</code></em> INTO [<span class="optional">STRICT</span>] <em class="replaceable"><code>target</code></em>; +</pre><p> + + where <em class="replaceable"><code>target</code></em> can be a record variable, a row + variable, or a comma-separated list of simple variables and + record/row fields. + <span class="application">PL/pgSQL</span> variables will be + substituted into the rest of the command (that is, everything but the + <code class="literal">INTO</code> clause) just as described above, + and the plan is cached in the same way. + This works for <code class="command">SELECT</code>, + <code class="command">INSERT</code>/<code class="command">UPDATE</code>/<code class="command">DELETE</code> with + <code class="literal">RETURNING</code>, and certain utility commands + that return row sets, such as <code class="command">EXPLAIN</code>. + Except for the <code class="literal">INTO</code> clause, the SQL command is the same + as it would be written outside <span class="application">PL/pgSQL</span>. + </p><div class="tip"><h3 class="title">Tip</h3><p> + Note that this interpretation of <code class="command">SELECT</code> with <code class="literal">INTO</code> + is quite different from <span class="productname">PostgreSQL</span>'s regular + <code class="command">SELECT INTO</code> command, wherein the <code class="literal">INTO</code> + target is a newly created table. If you want to create a table from a + <code class="command">SELECT</code> result inside a + <span class="application">PL/pgSQL</span> function, use the syntax + <code class="command">CREATE TABLE ... AS SELECT</code>. + </p></div><p> + If a row variable or a variable list is used as target, + the command's result columns + must exactly match the structure of the target as to number and data + types, or else a run-time error + occurs. When a record variable is the target, it automatically + configures itself to the row type of the command's result columns. + </p><p> + The <code class="literal">INTO</code> clause can appear almost anywhere in the SQL + command. Customarily it is written either just before or just after + the list of <em class="replaceable"><code>select_expressions</code></em> in a + <code class="command">SELECT</code> command, or at the end of the command for other + command types. It is recommended that you follow this convention + in case the <span class="application">PL/pgSQL</span> parser becomes + stricter in future versions. + </p><p> + If <code class="literal">STRICT</code> is not specified in the <code class="literal">INTO</code> + clause, then <em class="replaceable"><code>target</code></em> will be set to the first + row returned by the command, or to nulls if the command returned no rows. + (Note that <span class="quote">“<span class="quote">the first row</span>”</span> is not + well-defined unless you've used <code class="literal">ORDER BY</code>.) Any result rows + after the first row are discarded. + You can check the special <code class="literal">FOUND</code> variable (see + <a class="xref" href="plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS" title="43.5.5. Obtaining the Result Status">Section 43.5.5</a>) to + determine whether a row was returned: + +</p><pre class="programlisting"> +SELECT * INTO myrec FROM emp WHERE empname = myname; +IF NOT FOUND THEN + RAISE EXCEPTION 'employee % not found', myname; +END IF; +</pre><p> + + If the <code class="literal">STRICT</code> option is specified, the command must + return exactly one row or a run-time error will be reported, either + <code class="literal">NO_DATA_FOUND</code> (no rows) or <code class="literal">TOO_MANY_ROWS</code> + (more than one row). You can use an exception block if you wish + to catch the error, for example: + +</p><pre class="programlisting"> +BEGIN + SELECT * INTO STRICT myrec FROM emp WHERE empname = myname; + EXCEPTION + WHEN NO_DATA_FOUND THEN + RAISE EXCEPTION 'employee % not found', myname; + WHEN TOO_MANY_ROWS THEN + RAISE EXCEPTION 'employee % not unique', myname; +END; +</pre><p> + Successful execution of a command with <code class="literal">STRICT</code> + always sets <code class="literal">FOUND</code> to true. + </p><p> + For <code class="command">INSERT</code>/<code class="command">UPDATE</code>/<code class="command">DELETE</code> with + <code class="literal">RETURNING</code>, <span class="application">PL/pgSQL</span> reports + an error for more than one returned row, even when + <code class="literal">STRICT</code> is not specified. This is because there + is no option such as <code class="literal">ORDER BY</code> with which to determine + which affected row should be returned. + </p><p> + If <code class="literal">print_strict_params</code> is enabled for the function, + then when an error is thrown because the requirements + of <code class="literal">STRICT</code> are not met, the <code class="literal">DETAIL</code> part of + the error message will include information about the parameters + passed to the command. + You can change the <code class="literal">print_strict_params</code> + setting for all functions by setting + <code class="varname">plpgsql.print_strict_params</code>, though only subsequent + function compilations will be affected. You can also enable it + on a per-function basis by using a compiler option, for example: +</p><pre class="programlisting"> +CREATE FUNCTION get_userid(username text) RETURNS int +AS $$ +#print_strict_params on +DECLARE +userid int; +BEGIN + SELECT users.userid INTO STRICT userid + FROM users WHERE users.username = get_userid.username; + RETURN userid; +END; +$$ LANGUAGE plpgsql; +</pre><p> + On failure, this function might produce an error message such as +</p><pre class="programlisting"> +ERROR: query returned no rows +DETAIL: parameters: $1 = 'nosuchuser' +CONTEXT: PL/pgSQL function get_userid(text) line 6 at SQL statement +</pre><p> + </p><div class="note"><h3 class="title">Note</h3><p> + The <code class="literal">STRICT</code> option matches the behavior of + Oracle PL/SQL's <code class="command">SELECT INTO</code> and related statements. + </p></div></div><div class="sect2" id="PLPGSQL-STATEMENTS-EXECUTING-DYN"><div class="titlepage"><div><div><h3 class="title">43.5.4. Executing Dynamic Commands <a href="#PLPGSQL-STATEMENTS-EXECUTING-DYN" class="id_link">#</a></h3></div></div></div><p> + Oftentimes you will want to generate dynamic commands inside your + <span class="application">PL/pgSQL</span> functions, that is, commands + that will involve different tables or different data types each + time they are executed. <span class="application">PL/pgSQL</span>'s + normal attempts to cache plans for commands (as discussed in + <a class="xref" href="plpgsql-implementation.html#PLPGSQL-PLAN-CACHING" title="43.11.2. Plan Caching">Section 43.11.2</a>) will not work in such + scenarios. To handle this sort of problem, the + <code class="command">EXECUTE</code> statement is provided: + +</p><pre class="synopsis"> +EXECUTE <em class="replaceable"><code>command-string</code></em> [<span class="optional"> INTO [<span class="optional">STRICT</span>] <em class="replaceable"><code>target</code></em> </span>] [<span class="optional"> USING <em class="replaceable"><code>expression</code></em> [<span class="optional">, ... </span>] </span>]; +</pre><p> + + where <em class="replaceable"><code>command-string</code></em> is an expression + yielding a string (of type <code class="type">text</code>) containing the + command to be executed. The optional <em class="replaceable"><code>target</code></em> + is a record variable, a row variable, or a comma-separated list of + simple variables and record/row fields, into which the results of + the command will be stored. The optional <code class="literal">USING</code> expressions + supply values to be inserted into the command. + </p><p> + No substitution of <span class="application">PL/pgSQL</span> variables is done on the + computed command string. Any required variable values must be inserted + in the command string as it is constructed; or you can use parameters + as described below. + </p><p> + Also, there is no plan caching for commands executed via + <code class="command">EXECUTE</code>. Instead, the command is always planned + each time the statement is run. Thus the command + string can be dynamically created within the function to perform + actions on different tables and columns. + </p><p> + The <code class="literal">INTO</code> clause specifies where the results of + an SQL command returning rows should be assigned. If a row variable + or variable list is provided, it must exactly match the structure + of the command's results; if a + record variable is provided, it will configure itself to match the + result structure automatically. If multiple rows are returned, + only the first will be assigned to the <code class="literal">INTO</code> + variable(s). If no rows are returned, NULL is assigned to the + <code class="literal">INTO</code> variable(s). If no <code class="literal">INTO</code> + clause is specified, the command results are discarded. + </p><p> + If the <code class="literal">STRICT</code> option is given, an error is reported + unless the command produces exactly one row. + </p><p> + The command string can use parameter values, which are referenced + in the command as <code class="literal">$1</code>, <code class="literal">$2</code>, etc. + These symbols refer to values supplied in the <code class="literal">USING</code> + clause. This method is often preferable to inserting data values + into the command string as text: it avoids run-time overhead of + converting the values to text and back, and it is much less prone + to SQL-injection attacks since there is no need for quoting or escaping. + An example is: +</p><pre class="programlisting"> +EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2' + INTO c + USING checked_user, checked_date; +</pre><p> + </p><p> + Note that parameter symbols can only be used for data values + — if you want to use dynamically determined table or column + names, you must insert them into the command string textually. + For example, if the preceding query needed to be done against a + dynamically selected table, you could do this: +</p><pre class="programlisting"> +EXECUTE 'SELECT count(*) FROM ' + || quote_ident(tabname) + || ' WHERE inserted_by = $1 AND inserted <= $2' + INTO c + USING checked_user, checked_date; +</pre><p> + A cleaner approach is to use <code class="function">format()</code>'s <code class="literal">%I</code> + specification to insert table or column names with automatic quoting: +</p><pre class="programlisting"> +EXECUTE format('SELECT count(*) FROM %I ' + 'WHERE inserted_by = $1 AND inserted <= $2', tabname) + INTO c + USING checked_user, checked_date; +</pre><p> + (This example relies on the SQL rule that string literals separated by a + newline are implicitly concatenated.) + </p><p> + Another restriction on parameter symbols is that they only work in + optimizable SQL commands + (<code class="command">SELECT</code>, <code class="command">INSERT</code>, <code class="command">UPDATE</code>, + <code class="command">DELETE</code>, <code class="command">MERGE</code>, and certain commands containing one of these). + In other statement + types (generically called utility statements), you must insert + values textually even if they are just data values. + </p><p> + An <code class="command">EXECUTE</code> with a simple constant command string and some + <code class="literal">USING</code> parameters, as in the first example above, is + functionally equivalent to just writing the command directly in + <span class="application">PL/pgSQL</span> and allowing replacement of + <span class="application">PL/pgSQL</span> variables to happen automatically. + The important difference is that <code class="command">EXECUTE</code> will re-plan + the command on each execution, generating a plan that is specific + to the current parameter values; whereas + <span class="application">PL/pgSQL</span> may otherwise create a generic plan + and cache it for re-use. In situations where the best plan depends + strongly on the parameter values, it can be helpful to use + <code class="command">EXECUTE</code> to positively ensure that a generic plan is not + selected. + </p><p> + <code class="command">SELECT INTO</code> is not currently supported within + <code class="command">EXECUTE</code>; instead, execute a plain <code class="command">SELECT</code> + command and specify <code class="literal">INTO</code> as part of the <code class="command">EXECUTE</code> + itself. + </p><div class="note"><h3 class="title">Note</h3><p> + The <span class="application">PL/pgSQL</span> + <code class="command">EXECUTE</code> statement is not related to the + <a class="link" href="sql-execute.html" title="EXECUTE"><code class="command">EXECUTE</code></a> SQL + statement supported by the + <span class="productname">PostgreSQL</span> server. The server's + <code class="command">EXECUTE</code> statement cannot be used directly within + <span class="application">PL/pgSQL</span> functions (and is not needed). + </p></div><div class="example" id="PLPGSQL-QUOTE-LITERAL-EXAMPLE"><p class="title"><strong>Example 43.1. Quoting Values in Dynamic Queries</strong></p><div class="example-contents"><a id="id-1.8.8.7.6.13.2" class="indexterm"></a><a id="id-1.8.8.7.6.13.3" class="indexterm"></a><a id="id-1.8.8.7.6.13.4" class="indexterm"></a><a id="id-1.8.8.7.6.13.5" class="indexterm"></a><p> + When working with dynamic commands you will often have to handle escaping + of single quotes. The recommended method for quoting fixed text in your + function body is dollar quoting. (If you have legacy code that does + not use dollar quoting, please refer to the + overview in <a class="xref" href="plpgsql-development-tips.html#PLPGSQL-QUOTE-TIPS" title="43.12.1. Handling of Quotation Marks">Section 43.12.1</a>, which can save you + some effort when translating said code to a more reasonable scheme.) + </p><p> + Dynamic values require careful handling since they might contain + quote characters. + An example using <code class="function">format()</code> (this assumes that you are + dollar quoting the function body so quote marks need not be doubled): +</p><pre class="programlisting"> +EXECUTE format('UPDATE tbl SET %I = $1 ' + 'WHERE key = $2', colname) USING newvalue, keyvalue; +</pre><p> + It is also possible to call the quoting functions directly: +</p><pre class="programlisting"> +EXECUTE 'UPDATE tbl SET ' + || quote_ident(colname) + || ' = ' + || quote_literal(newvalue) + || ' WHERE key = ' + || quote_literal(keyvalue); +</pre><p> + </p><p> + This example demonstrates the use of the + <code class="function">quote_ident</code> and + <code class="function">quote_literal</code> functions (see <a class="xref" href="functions-string.html" title="9.4. String Functions and Operators">Section 9.4</a>). For safety, expressions containing column + or table identifiers should be passed through + <code class="function">quote_ident</code> before insertion in a dynamic query. + Expressions containing values that should be literal strings in the + constructed command should be passed through <code class="function">quote_literal</code>. + These functions take the appropriate steps to return the input text + enclosed in double or single quotes respectively, with any embedded + special characters properly escaped. + </p><p> + Because <code class="function">quote_literal</code> is labeled + <code class="literal">STRICT</code>, it will always return null when called with a + null argument. In the above example, if <code class="literal">newvalue</code> or + <code class="literal">keyvalue</code> were null, the entire dynamic query string would + become null, leading to an error from <code class="command">EXECUTE</code>. + You can avoid this problem by using the <code class="function">quote_nullable</code> + function, which works the same as <code class="function">quote_literal</code> except that + when called with a null argument it returns the string <code class="literal">NULL</code>. + For example, +</p><pre class="programlisting"> +EXECUTE 'UPDATE tbl SET ' + || quote_ident(colname) + || ' = ' + || quote_nullable(newvalue) + || ' WHERE key = ' + || quote_nullable(keyvalue); +</pre><p> + If you are dealing with values that might be null, you should usually + use <code class="function">quote_nullable</code> in place of <code class="function">quote_literal</code>. + </p><p> + As always, care must be taken to ensure that null values in a query do + not deliver unintended results. For example the <code class="literal">WHERE</code> clause +</p><pre class="programlisting"> +'WHERE key = ' || quote_nullable(keyvalue) +</pre><p> + will never succeed if <code class="literal">keyvalue</code> is null, because the + result of using the equality operator <code class="literal">=</code> with a null operand + is always null. If you wish null to work like an ordinary key value, + you would need to rewrite the above as +</p><pre class="programlisting"> +'WHERE key IS NOT DISTINCT FROM ' || quote_nullable(keyvalue) +</pre><p> + (At present, <code class="literal">IS NOT DISTINCT FROM</code> is handled much less + efficiently than <code class="literal">=</code>, so don't do this unless you must. + See <a class="xref" href="functions-comparison.html" title="9.2. Comparison Functions and Operators">Section 9.2</a> for + more information on nulls and <code class="literal">IS DISTINCT</code>.) + </p><p> + Note that dollar quoting is only useful for quoting fixed text. + It would be a very bad idea to try to write this example as: +</p><pre class="programlisting"> +EXECUTE 'UPDATE tbl SET ' + || quote_ident(colname) + || ' = $$' + || newvalue + || '$$ WHERE key = ' + || quote_literal(keyvalue); +</pre><p> + because it would break if the contents of <code class="literal">newvalue</code> + happened to contain <code class="literal">$$</code>. The same objection would + apply to any other dollar-quoting delimiter you might pick. + So, to safely quote text that is not known in advance, you + <span class="emphasis"><em>must</em></span> use <code class="function">quote_literal</code>, + <code class="function">quote_nullable</code>, or <code class="function">quote_ident</code>, as appropriate. + </p><p> + Dynamic SQL statements can also be safely constructed using the + <code class="function">format</code> function (see <a class="xref" href="functions-string.html#FUNCTIONS-STRING-FORMAT" title="9.4.1. format">Section 9.4.1</a>). For example: +</p><pre class="programlisting"> +EXECUTE format('UPDATE tbl SET %I = %L ' + 'WHERE key = %L', colname, newvalue, keyvalue); +</pre><p> + <code class="literal">%I</code> is equivalent to <code class="function">quote_ident</code>, and + <code class="literal">%L</code> is equivalent to <code class="function">quote_nullable</code>. + The <code class="function">format</code> function can be used in conjunction with + the <code class="literal">USING</code> clause: +</p><pre class="programlisting"> +EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname) + USING newvalue, keyvalue; +</pre><p> + This form is better because the variables are handled in their native + data type format, rather than unconditionally converting them to + text and quoting them via <code class="literal">%L</code>. It is also more efficient. + </p></div></div><br class="example-break" /><p> + A much larger example of a dynamic command and + <code class="command">EXECUTE</code> can be seen in <a class="xref" href="plpgsql-porting.html#PLPGSQL-PORTING-EX2" title="Example 43.10. Porting a Function that Creates Another Function from PL/SQL to PL/pgSQL">Example 43.10</a>, which builds and executes a + <code class="command">CREATE FUNCTION</code> command to define a new function. + </p></div><div class="sect2" id="PLPGSQL-STATEMENTS-DIAGNOSTICS"><div class="titlepage"><div><div><h3 class="title">43.5.5. Obtaining the Result Status <a href="#PLPGSQL-STATEMENTS-DIAGNOSTICS" class="id_link">#</a></h3></div></div></div><p> + There are several ways to determine the effect of a command. The + first method is to use the <code class="command">GET DIAGNOSTICS</code> + command, which has the form: + +</p><pre class="synopsis"> +GET [<span class="optional"> CURRENT </span>] DIAGNOSTICS <em class="replaceable"><code>variable</code></em> { = | := } <em class="replaceable"><code>item</code></em> [<span class="optional"> , ... </span>]; +</pre><p> + + This command allows retrieval of system status indicators. + <code class="literal">CURRENT</code> is a noise word (but see also <code class="command">GET STACKED + DIAGNOSTICS</code> in <a class="xref" href="plpgsql-control-structures.html#PLPGSQL-EXCEPTION-DIAGNOSTICS" title="43.6.8.1. Obtaining Information about an Error">Section 43.6.8.1</a>). + Each <em class="replaceable"><code>item</code></em> is a key word identifying a status + value to be assigned to the specified <em class="replaceable"><code>variable</code></em> + (which should be of the right data type to receive it). The currently + available status items are shown + in <a class="xref" href="plpgsql-statements.html#PLPGSQL-CURRENT-DIAGNOSTICS-VALUES" title="Table 43.1. Available Diagnostics Items">Table 43.1</a>. Colon-equal + (<code class="literal">:=</code>) can be used instead of the SQL-standard <code class="literal">=</code> + token. An example: +</p><pre class="programlisting"> +GET DIAGNOSTICS integer_var = ROW_COUNT; +</pre><p> + </p><div class="table" id="PLPGSQL-CURRENT-DIAGNOSTICS-VALUES"><p class="title"><strong>Table 43.1. Available Diagnostics Items</strong></p><div class="table-contents"><table class="table" summary="Available Diagnostics Items" border="1"><colgroup><col class="col1" /><col class="col2" /><col class="col3" /></colgroup><thead><tr><th>Name</th><th>Type</th><th>Description</th></tr></thead><tbody><tr><td><code class="varname">ROW_COUNT</code></td><td><code class="type">bigint</code></td><td>the number of rows processed by the most + recent <acronym class="acronym">SQL</acronym> command</td></tr><tr><td><code class="literal">PG_CONTEXT</code></td><td><code class="type">text</code></td><td>line(s) of text describing the current call stack + (see <a class="xref" href="plpgsql-control-structures.html#PLPGSQL-CALL-STACK" title="43.6.9. Obtaining Execution Location Information">Section 43.6.9</a>)</td></tr><tr><td><code class="literal">PG_ROUTINE_OID</code></td><td><code class="type">oid</code></td><td>OID of the current function</td></tr></tbody></table></div></div><br class="table-break" /><p> + The second method to determine the effects of a command is to check the + special variable named <code class="literal">FOUND</code>, which is of + type <code class="type">boolean</code>. <code class="literal">FOUND</code> starts out + false within each <span class="application">PL/pgSQL</span> function call. + It is set by each of the following types of statements: + + </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p> + A <code class="command">SELECT INTO</code> statement sets + <code class="literal">FOUND</code> true if a row is assigned, false if no + row is returned. + </p></li><li class="listitem"><p> + A <code class="command">PERFORM</code> statement sets <code class="literal">FOUND</code> + true if it produces (and discards) one or more rows, false if + no row is produced. + </p></li><li class="listitem"><p> + <code class="command">UPDATE</code>, <code class="command">INSERT</code>, <code class="command">DELETE</code>, + and <code class="command">MERGE</code> + statements set <code class="literal">FOUND</code> true if at least one + row is affected, false if no row is affected. + </p></li><li class="listitem"><p> + A <code class="command">FETCH</code> statement sets <code class="literal">FOUND</code> + true if it returns a row, false if no row is returned. + </p></li><li class="listitem"><p> + A <code class="command">MOVE</code> statement sets <code class="literal">FOUND</code> + true if it successfully repositions the cursor, false otherwise. + </p></li><li class="listitem"><p> + A <code class="command">FOR</code> or <code class="command">FOREACH</code> statement sets + <code class="literal">FOUND</code> true + if it iterates one or more times, else false. + <code class="literal">FOUND</code> is set this way when the + loop exits; inside the execution of the loop, + <code class="literal">FOUND</code> is not modified by the + loop statement, although it might be changed by the + execution of other statements within the loop body. + </p></li><li class="listitem"><p> + <code class="command">RETURN QUERY</code> and <code class="command">RETURN QUERY + EXECUTE</code> statements set <code class="literal">FOUND</code> + true if the query returns at least one row, false if no row + is returned. + </p></li></ul></div><p> + + Other <span class="application">PL/pgSQL</span> statements do not change + the state of <code class="literal">FOUND</code>. + Note in particular that <code class="command">EXECUTE</code> + changes the output of <code class="command">GET DIAGNOSTICS</code>, but + does not change <code class="literal">FOUND</code>. + </p><p> + <code class="literal">FOUND</code> is a local variable within each + <span class="application">PL/pgSQL</span> function; any changes to it + affect only the current function. + </p></div><div class="sect2" id="PLPGSQL-STATEMENTS-NULL"><div class="titlepage"><div><div><h3 class="title">43.5.6. Doing Nothing At All <a href="#PLPGSQL-STATEMENTS-NULL" class="id_link">#</a></h3></div></div></div><p> + Sometimes a placeholder statement that does nothing is useful. + For example, it can indicate that one arm of an if/then/else + chain is deliberately empty. For this purpose, use the + <code class="command">NULL</code> statement: + +</p><pre class="synopsis"> +NULL; +</pre><p> + </p><p> + For example, the following two fragments of code are equivalent: +</p><pre class="programlisting"> +BEGIN + y := x / 0; +EXCEPTION + WHEN division_by_zero THEN + NULL; -- ignore the error +END; +</pre><p> + +</p><pre class="programlisting"> +BEGIN + y := x / 0; +EXCEPTION + WHEN division_by_zero THEN -- ignore the error +END; +</pre><p> + Which is preferable is a matter of taste. + </p><div class="note"><h3 class="title">Note</h3><p> + In Oracle's PL/SQL, empty statement lists are not allowed, and so + <code class="command">NULL</code> statements are <span class="emphasis"><em>required</em></span> for situations + such as this. <span class="application">PL/pgSQL</span> allows you to + just write nothing, instead. + </p></div></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="plpgsql-expressions.html" title="43.4. Expressions">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="plpgsql.html" title="Chapter 43. PL/pgSQL — SQL Procedural Language">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="plpgsql-control-structures.html" title="43.6. Control Structures">Next</a></td></tr><tr><td width="40%" align="left" valign="top">43.4. Expressions </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.2 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 43.6. Control Structures</td></tr></table></div></body></html>
\ No newline at end of file |