PostgreSQL 8.2.10 Documentation
Prev | Fast Backward | Chapter 37. PL/pgSQL - SQL Procedural Language | Fast Forward | Next

# 37.6. Basic Statements

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 37.6.2 and Section 37.6.3.

## 37.6.1. Assignment

An assignment of a value to a PL/pgSQL variable or row/record field is written as:

    identifier := expression;

As explained above, 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.

If the expression's result data type doesn't match the variable's data type, or the variable has a specific size/precision (like `char(20)`), the result value will be implicitly converted by the PL/pgSQL interpreter using the result type's output-function and the variable type's input-function. Note that this could potentially 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:

    user_id := 20;
    tax := subtotal * 0.06;

## 37.6.2. Executing a Query With No Result

For any SQL query that does not return rows, for example `INSERT` without a `RETURNING` clause, you can execute the query within a PL/pgSQL function just by writing the query.

Any PL/pgSQL variable name appearing in the query text is replaced by a parameter symbol, and then the current value of the variable is provided as the parameter value at run time. This allows the same textual query to do different things in different calls of the function.

> Note: This two-step process allows PL/pgSQL to plan the query just once and re-use the plan on subsequent executions. As an example, if you write

>     DECLARE
>         key TEXT;
>         delta INTEGER;
>     BEGIN
>         ...
>         UPDATE mytab SET val = val + delta WHERE id = key;

> the query text seen by the main SQL engine will look like

>         UPDATE mytab SET val = val + $1 WHERE id = $2;

> Although you don't normally have to think about this, it's helpful to know it when you need to make sense of syntax-error messages.

Caution
PL/pgSQL will substitute for any identifier matching one of the function's declared variables; it is not bright enough to know whether that's what you meant! Thus, it is a bad idea to use a variable name that is the same as any table or column name that you need to reference in queries within the function. Sometimes you can work around this by using qualified names in the query: PL/pgSQL will not substitute in a qualified name `foo`.`bar`, even if `foo` or `bar` is a declared variable 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`. PL/pgSQL variables will be substituted into the query as usual. Also, the special variable `FOUND` is set to true if the query produced at least one row, or false if it produced no rows.

> Note: One might expect that writing `SELECT` directly would accomplish this result, but at present the only accepted way to do it is `PERFORM`. A 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);

## 37.6.3. Executing a Query with a Single-Row Result

The result of a 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 query as usual. This works for `SELECT`, `INSERT`/`UPDATE`/`DELETE` with `RETURNING`, and utility commands that return row-set results (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 or a variable list is used as target, the query's result columns must exactly match the structure of the target as to number and data types, or a run-time error occurs. When a record variable is the target, it automatically configures itself to the row type of the query 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, then `target` will be set to the first row returned by the query, or to nulls if the query 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 37.6.6) 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 query 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 would be returned.

> Note: The `STRICT` option matches the behavior of Oracle PL/SQL's `SELECT INTO` and related statements.

To handle cases where you need to process multiple result rows from a SQL query, see Section 37.7.4.

## 37.6.4. 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.

## 37.6.5. 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 will not work in such scenarios. To handle this sort of problem, the `EXECUTE` statement is provided:

    EXECUTE command-string [ INTO [STRICT] target ];

where `command-string` is an expression yielding a string (of type `text`) containing the command to be executed and `target` is a record variable, row variable, or a comma-separated list of simple variables and record/row fields.

Note in particular that no substitution of PL/pgSQL variables is done on the computed command string. The values of variables must be inserted in the command string as it is constructed.

Unlike all other commands in PL/pgSQL, a command run by an `EXECUTE` statement is not prepared and saved just once during the life of the session. Instead, the command is prepared each time the statement is run. 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 a SQL command returning rows should be assigned. If a row or variable list is provided, it must exactly match the structure of the query's results (when a record variable is used, 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. If no rows are returned, NULL is assigned to the `INTO` variable. If no `INTO` clause is specified, the query results are discarded.

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

`SELECT INTO` is not currently supported within `EXECUTE`.

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

Dynamic values that are to be inserted into the constructed query require special handling since they might themselves contain quote characters. An example (this assumes that you are using dollar quoting for the function as a whole, so the quote marks need not be doubled):

    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. For safety, expressions containing column and table identifiers should be passed to `quote_ident`. Expressions containing values that should be literal strings in the constructed command should be passed to `quote_literal`. Both take the appropriate steps to return the input text enclosed in double or single quotes respectively, with any embedded special characters properly escaped.

Note that dollar quoting is only useful for quoting fixed text. It would be a very bad idea to try to do the above 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`.

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

## 37.6.6. 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 DIAGNOSTICS variable = item [ , ... ];

This command allows retrieval of system status indicators. Each `item` is a key word identifying a state 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 `ROW_COUNT`, the number of rows processed by the last SQL command sent down to the SQL engine, and `RESULT_OID`, the OID of the last row inserted by the most recent SQL command. Note that `RESULT_OID` is only useful after an `INSERT` command into a table containing OIDs.

An example:

    GET DIAGNOSTICS integer_var = ROW_COUNT;

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) a row, false if no row is produced.

- `UPDATE`, `INSERT`, and `DELETE` 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 `FOR` statement sets `FOUND` true if it iterates one or more times, else false. This applies to all three variants of the `FOR` statement (integer `FOR` loops, record-set `FOR` loops, and dynamic record-set `FOR` loops). `FOUND` is set this way when the `FOR` loop exits; inside the execution of the loop, `FOUND` is not modified by the `FOR` statement, although it may be changed by the execution of other statements within the loop body.

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

Prev | Home | Next
Expressions | Up | Control Structures