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.

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.

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

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.

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,

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:

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.

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

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

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.

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.