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. Show 43.5.1. AssignmentAn assignment of a value to a PL/pgSQL variable is written as:
As explained previously, the expression in such a statement is evaluated by means of an SQL 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 CommandsIn 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 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 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
Sometimes it is useful to evaluate an expression or PERFORM This executes NoteOne might expect that writing An example: PERFORM create_mv('cs_session_page_requests_mv', my_query); 43.5.3. Executing a Command with a Single-Row ResultThe 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 SELECT where TipNote that this interpretation of 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 If SELECT * INTO myrec FROM emp WHERE empname = myname; IF NOT FOUND THEN RAISE EXCEPTION 'employee % not found', myname; END IF; If the 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 For If 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 NoteThe 43.5.4. Executing Dynamic CommandsOftentimes 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 where 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 The If the The command
string can use parameter values, which are referenced in the command as 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 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 ( An
NoteThe PL/pgSQL 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 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 Because 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 As always, care must be taken to ensure that null values in a query do not deliver unintended results. For example the 'WHERE key = ' || quote_nullable(keyvalue) will never succeed if 'WHERE key IS NOT DISTINCT FROM ' || quote_nullable(keyvalue) (At present, 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 Dynamic SQL statements can also be safely constructed
using the EXECUTE format('UPDATE tbl SET %I = %L ' 'WHERE key = %L', colname, newvalue, keyvalue);
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 A much larger example of a dynamic command and 43.5.5. Obtaining the Result StatusThere are several ways to determine the effect of a command. The first method is to use the GET [ CURRENT ] DIAGNOSTICS This command allows retrieval of system status indicators. GET DIAGNOSTICS integer_var = ROW_COUNT; Table 43.1. Available Diagnostics Items
The second method to determine the effects of a command is to check the special variable named
Other PL/pgSQL statements do not change the state of
43.5.6. Doing Nothing At AllSometimes 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; 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. NoteIn Oracle's PL/SQL, empty statement lists are not allowed, and so |