Convert HTML docs to more streamlined TXT

[ai-pg] / full-docs / txt / plpgsql-statements.txt

41.5. Basic Statements #

   41.5.1. Assignment
   41.5.2. Executing SQL Commands
   41.5.3. Executing a Command with a Single-Row Result
   41.5.4. Executing Dynamic Commands
   41.5.5. Obtaining the Result Status
   41.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 41.5.2.

41.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;

41.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/MERGE 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 41.5.3. To
   process all of the output rows, write the command as the data source
   for a FOR loop, as described in Section 41.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 41.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 41.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 41.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 41.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);

41.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;
MERGE ... 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/MERGE 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 41.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/MERGE 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: username = '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.

41.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 41.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 41.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 41.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 41.10, which builds and executes a CREATE FUNCTION command to
   define a new function.

41.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 41.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 41.1. Colon-equal (:=) can be used instead of the SQL-standard =
   token. An example:
GET DIAGNOSTICS integer_var = ROW_COUNT;

   Table 41.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 41.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.

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