43.5. Basic Statements

43.5. Basic Statements
Prev	Up	Chapter 43. PL/pgSQL — SQL Procedural Language	Home	Next

43.5. Basic Statements #

43.5.1. Assignment
43.5.2. Executing SQL Commands
43.5.3. Executing a Command with a Single-Row Result
43.5.4. Executing Dynamic Commands
43.5.5. Obtaining the Result Status
43.5.6. Doing Nothing At All

+ In this section and the following ones, we describe all the statement + types that are explicitly understood by + PL/pgSQL. + 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 Section 43.5.2. +

43.5.1. Assignment #

+ An assignment of a value to a PL/pgSQL + variable is written as: +

+variable { := | = } expression;
+

+ As explained previously, the expression in such a statement is evaluated + by means of an SQL SELECT 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 (=) can be + used instead of PL/SQL-compliant :=. +

+ 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 Section 10.4). If no assignment cast is known + for the pair of data types involved, the PL/pgSQL + 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. +

+ Examples: +

+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;
+

43.5.2. Executing SQL Commands #

+ In general, any SQL command that does not return rows can be executed + within a PL/pgSQL function just by writing + the command. For example, you could create and fill a table by writing +

+CREATE TABLE mytable (id int primary key, data text);
+INSERT INTO mytable VALUES (1,'one'), (2,'two');
+

+ If the command does return rows (for example SELECT, + or INSERT/UPDATE/DELETE + with RETURNING), 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 INTO clause to capture the output, as described + in Section 43.5.3. + To process all of the output rows, write the command as the data + source for a FOR loop, as described in + Section 43.6.6. +

+ 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. +

+ PL/pgSQL variable values can be + automatically inserted into optimizable SQL commands, which + are SELECT, INSERT, + UPDATE, DELETE, + MERGE, and certain + utility commands that incorporate one of these, such + as EXPLAIN and CREATE TABLE ... AS + SELECT. In these commands, + any PL/pgSQL 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 Section 43.11.1. +

+ When executing an optimizable SQL command in this way, + PL/pgSQL may cache and re-use the execution + plan for the command, as discussed in + Section 43.11.2. +

+ Non-optimizable SQL commands (also called utility commands) are not + capable of accepting query parameters. So automatic substitution + of PL/pgSQL variables does not work in such + commands. To include non-constant text in a utility command executed + from PL/pgSQL, you must build the utility + command as a string and then EXECUTE it, as + discussed in Section 43.5.4. +

+ EXECUTE 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. +

+ Sometimes it is useful to evaluate an expression or SELECT + query but discard the result, for example when calling a function + that has side-effects but no useful result value. To do + this in PL/pgSQL, use the + PERFORM statement: + +

+PERFORM query;
+

+ + This executes query and discards the + result. Write the query the same + way you would write an SQL SELECT command, but replace the + initial keyword SELECT with PERFORM. + For WITH queries, use PERFORM and then + place the query in parentheses. (In this case, the query can only + return one row.) + PL/pgSQL variables will be + substituted into the query just as described above, + and the plan is cached in the same way. Also, the special variable + FOUND is set to true if the query produced at + least one row, or false if it produced no rows (see + Section 43.5.5). +

Note

+ One might expect that writing SELECT directly + would accomplish this result, but at + present the only accepted way to do it is + PERFORM. An SQL command that can return rows, + such as SELECT, will be rejected as an error + unless it has an INTO clause as discussed in the + next section. +

+ An example: +

+PERFORM create_mv('cs_session_page_requests_mv', my_query);
+

43.5.3. Executing a Command with a Single-Row Result #

+ 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 INTO clause. For example, + +

+SELECT select_expressions INTO [STRICT] target FROM ...;
+INSERT ... RETURNING expressions INTO [STRICT] target;
+UPDATE ... RETURNING expressions INTO [STRICT] target;
+DELETE ... RETURNING expressions INTO [STRICT] target;
+

+ + where target can be a record variable, a row + variable, or a comma-separated list of simple variables and + record/row fields. + PL/pgSQL variables will be + substituted into the rest of the command (that is, everything but the + INTO clause) just as described above, + and the plan is cached in the same way. + This works for SELECT, + INSERT/UPDATE/DELETE with + RETURNING, and certain utility commands + that return row sets, such as EXPLAIN. + Except for the INTO clause, the SQL command is the same + as it would be written outside PL/pgSQL. +

Tip

+ Note that this interpretation of SELECT with INTO + is quite different from PostgreSQL's regular + SELECT INTO command, wherein the INTO + target is a newly created table. If you want to create a table from a + SELECT result inside a + PL/pgSQL function, use the syntax + CREATE TABLE ... AS SELECT. +

+ 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. +

+ The INTO clause can appear almost anywhere in the SQL + command. Customarily it is written either just before or just after + the list of select_expressions in a + SELECT command, or at the end of the command for other + command types. It is recommended that you follow this convention + in case the PL/pgSQL parser becomes + stricter in future versions. +

+ If STRICT is not specified in the INTO + clause, then target will be set to the first + row returned by the command, or to nulls if the command returned no rows. + (Note that “the first row” is not + well-defined unless you've used ORDER BY.) Any result rows + after the first row are discarded. + You can check the special FOUND variable (see + Section 43.5.5) to + determine whether a row was returned: + +

+SELECT * INTO myrec FROM emp WHERE empname = myname;
+IF NOT FOUND THEN
+    RAISE EXCEPTION 'employee % not found', myname;
+END IF;
+

+ + If the STRICT option is specified, the command must + return exactly one row or a run-time error will be reported, either + NO_DATA_FOUND (no rows) or TOO_MANY_ROWS + (more than one row). You can use an exception block if you wish + to catch the error, for example: + +

+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;
+

+ Successful execution of a command with STRICT + always sets FOUND to true. +

+ For INSERT/UPDATE/DELETE with + RETURNING, PL/pgSQL reports + an error for more than one returned row, even when + STRICT is not specified. This is because there + is no option such as ORDER BY with which to determine + which affected row should be returned. +

+ If print_strict_params is enabled for the function, + then when an error is thrown because the requirements + of STRICT are not met, the DETAIL part of + the error message will include information about the parameters + passed to the command. + You can change the print_strict_params + setting for all functions by setting + plpgsql.print_strict_params, 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: +

+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;
+

+ On failure, this function might produce an error message such as +

+ERROR:  query returned no rows
+DETAIL:  parameters: $1 = 'nosuchuser'
+CONTEXT:  PL/pgSQL function get_userid(text) line 6 at SQL statement
+

Note

+ The STRICT option matches the behavior of + Oracle PL/SQL's SELECT INTO and related statements. +

43.5.4. Executing Dynamic Commands #

+ Oftentimes you will want to generate dynamic commands inside your + PL/pgSQL functions, that is, commands + that will involve different tables or different data types each + time they are executed. PL/pgSQL's + normal attempts to cache plans for commands (as discussed in + Section 43.11.2) will not work in such + scenarios. To handle this sort of problem, the + EXECUTE statement is provided: + +

+EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];
+

+ + where command-string is an expression + yielding a string (of type text) containing the + command to be executed. The optional target + 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 USING expressions + supply values to be inserted into the command. +

+ No substitution of PL/pgSQL 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. +

+ Also, there is no plan caching for commands executed via + EXECUTE. 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. +

+ The INTO 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 INTO + variable(s). If no rows are returned, NULL is assigned to the + INTO variable(s). If no INTO + clause is specified, the command results are discarded. +

+ If the STRICT option is given, an error is reported + unless the command produces exactly one row. +

+ The command string can use parameter values, which are referenced + in the command as $1, $2, etc. + These symbols refer to values supplied in the USING + 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: +

+EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'
+   INTO c
+   USING checked_user, checked_date;
+

+ 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: +

+EXECUTE 'SELECT count(*) FROM '
+    || quote_ident(tabname)
+    || ' WHERE inserted_by = $1 AND inserted <= $2'
+   INTO c
+   USING checked_user, checked_date;
+

+ A cleaner approach is to use format()'s %I + specification to insert table or column names with automatic quoting: +

+EXECUTE format('SELECT count(*) FROM %I '
+   'WHERE inserted_by = $1 AND inserted <= $2', tabname)
+   INTO c
+   USING checked_user, checked_date;
+

+ (This example relies on the SQL rule that string literals separated by a + newline are implicitly concatenated.) +

+ Another restriction on parameter symbols is that they only work in + optimizable SQL commands + (SELECT, INSERT, UPDATE, + DELETE, MERGE, 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. +

+ An EXECUTE with a simple constant command string and some + USING parameters, as in the first example above, is + functionally equivalent to just writing the command directly in + PL/pgSQL and allowing replacement of + PL/pgSQL variables to happen automatically. + The important difference is that EXECUTE will re-plan + the command on each execution, generating a plan that is specific + to the current parameter values; whereas + PL/pgSQL 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 + EXECUTE to positively ensure that a generic plan is not + selected. +

+ SELECT INTO is not currently supported within + EXECUTE; instead, execute a plain SELECT + command and specify INTO as part of the EXECUTE + itself. +

Note

+ The PL/pgSQL + EXECUTE statement is not related to the + EXECUTE SQL + statement supported by the + PostgreSQL server. The server's + EXECUTE statement cannot be used directly within + PL/pgSQL functions (and is not needed). +

Example 43.1. Quoting Values in Dynamic Queries

+ 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 Section 43.12.1, which can save you + some effort when translating said code to a more reasonable scheme.) +

+ Dynamic values require careful handling since they might contain + quote characters. + An example using format() (this assumes that you are + dollar quoting the function body so quote marks need not be doubled): +

+EXECUTE format('UPDATE tbl SET %I = $1 '
+   'WHERE key = $2', colname) USING newvalue, keyvalue;
+

+ It is also possible to call the quoting functions directly: +

+EXECUTE 'UPDATE tbl SET '
+        || quote_ident(colname)
+        || ' = '
+        || quote_literal(newvalue)
+        || ' WHERE key = '
+        || quote_literal(keyvalue);
+

+ This example demonstrates the use of the + quote_ident and + quote_literal functions (see Section 9.4). For safety, expressions containing column + or table identifiers should be passed through + quote_ident before insertion in a dynamic query. + Expressions containing values that should be literal strings in the + constructed command should be passed through quote_literal. + 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. +

+ Because quote_literal is labeled + STRICT, it will always return null when called with a + null argument. In the above example, if newvalue or + keyvalue were null, the entire dynamic query string would + become null, leading to an error from EXECUTE. + You can avoid this problem by using the quote_nullable + function, which works the same as quote_literal except that + when called with a null argument it returns the string NULL. + For example, +

+EXECUTE 'UPDATE tbl SET '
+        || quote_ident(colname)
+        || ' = '
+        || quote_nullable(newvalue)
+        || ' WHERE key = '
+        || quote_nullable(keyvalue);
+

+ If you are dealing with values that might be null, you should usually + use quote_nullable in place of quote_literal. +

+ As always, care must be taken to ensure that null values in a query do + not deliver unintended results. For example the WHERE clause +

+'WHERE key = ' || quote_nullable(keyvalue)
+

+ will never succeed if keyvalue is null, because the + result of using the equality operator = 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 +

+'WHERE key IS NOT DISTINCT FROM ' || quote_nullable(keyvalue)
+

+ (At present, IS NOT DISTINCT FROM is handled much less + efficiently than =, so don't do this unless you must. + See Section 9.2 for + more information on nulls and IS DISTINCT.) +

+ 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: +

+EXECUTE 'UPDATE tbl SET '
+        || quote_ident(colname)
+        || ' = $$'
+        || newvalue
+        || '$$ WHERE key = '
+        || quote_literal(keyvalue);
+

+ because it would break if the contents of newvalue + happened to contain $$. 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 + must use quote_literal, + quote_nullable, or quote_ident, as appropriate. +

+ Dynamic SQL statements can also be safely constructed using the + format function (see Section 9.4.1). For example: +

+EXECUTE format('UPDATE tbl SET %I = %L '
+   'WHERE key = %L', colname, newvalue, keyvalue);
+

+ %I is equivalent to quote_ident, and + %L is equivalent to quote_nullable. + The format function can be used in conjunction with + the USING clause: +

+EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname)
+   USING newvalue, keyvalue;
+

+ 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 %L. It is also more efficient. +

+ A much larger example of a dynamic command and + EXECUTE can be seen in Example 43.10, which builds and executes a + CREATE FUNCTION command to define a new function. +

43.5.5. Obtaining the Result Status #

+ There are several ways to determine the effect of a command. The + first method is to use the GET DIAGNOSTICS + command, which has the form: + +

+GET [ CURRENT ] DIAGNOSTICS variable { = | := } item [ , ... ];
+

+ + This command allows retrieval of system status indicators. + CURRENT is a noise word (but see also GET STACKED + DIAGNOSTICS in Section 43.6.8.1). + Each item is a key word identifying a status + value to be assigned to the specified variable + (which should be of the right data type to receive it). The currently + available status items are shown + in Table 43.1. Colon-equal + (:=) can be used instead of the SQL-standard = + token. An example: +

+GET DIAGNOSTICS integer_var = ROW_COUNT;
+

Table 43.1. Available Diagnostics Items

Name	Type	Description
`ROW_COUNT`	`bigint`	the number of rows processed by the most + recent SQL command
`PG_CONTEXT`	`text`	line(s) of text describing the current call stack + (see Section 43.6.9)
`PG_ROUTINE_OID`	`oid`	OID of the current function

+ The second method to determine the effects of a command is to check the + special variable named FOUND, which is of + type boolean. FOUND starts out + false within each PL/pgSQL function call. + It is set by each of the following types of statements: + +

+ A SELECT INTO statement sets + FOUND true if a row is assigned, false if no + row is returned. +
+ A PERFORM statement sets FOUND + true if it produces (and discards) one or more rows, false if + no row is produced. +
+ UPDATE, INSERT, DELETE, + and MERGE + statements set FOUND true if at least one + row is affected, false if no row is affected. +
+ A FETCH statement sets FOUND + true if it returns a row, false if no row is returned. +
+ A MOVE statement sets FOUND + true if it successfully repositions the cursor, false otherwise. +
+ A FOR or FOREACH statement sets + FOUND true + if it iterates one or more times, else false. + FOUND is set this way when the + loop exits; inside the execution of the loop, + FOUND is not modified by the + loop statement, although it might be changed by the + execution of other statements within the loop body. +
+ RETURN QUERY and RETURN QUERY + EXECUTE statements set FOUND + true if the query returns at least one row, false if no row + is returned. +

+ + Other PL/pgSQL statements do not change + the state of FOUND. + Note in particular that EXECUTE + changes the output of GET DIAGNOSTICS, but + does not change FOUND. +

+ FOUND is a local variable within each + PL/pgSQL function; any changes to it + affect only the current function. +

43.5.6. Doing Nothing At All #

+ 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 + NULL statement: + +

+NULL;
+

+ For example, the following two fragments of code are equivalent: +

+BEGIN
+    y := x / 0;
+EXCEPTION
+    WHEN division_by_zero THEN
+        NULL;  -- ignore the error
+END;
+

+ +

+BEGIN
+    y := x / 0;
+EXCEPTION
+    WHEN division_by_zero THEN  -- ignore the error
+END;
+

+ Which is preferable is a matter of taste. +

Note

+ In Oracle's PL/SQL, empty statement lists are not allowed, and so + NULL statements are required for situations + such as this. PL/pgSQL allows you to + just write nothing, instead. +

Prev	Up	Next
43.4. Expressions	Home	43.6. Control Structures