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 query, and is sent
to the main database engine to execute (after substitution for
any PL/pgSQL variables used in
the statement). Thus, for example, SQL INSERT, UPDATE, and
DELETE commands may be considered to be
statements of PL/pgSQL. But they
are not specifically listed here.

An assignment of a value to a 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.

The result of a SELECT command yielding multiple columns
(but only one row) can be assigned to a record variable,
row-type variable, or list of scalar variables. This is done
by:

SELECT INTO targetexpressions FROM ...;

where target can be a record
variable, a row variable, or a comma-separated list of simple
variables and record/row fields. Note that this is quite
different from PostgreSQL's
normal interpretation of SELECT INTO, which is that 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 selected
values must exactly match the structure of the target(s), 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.

Except for the INTO clause, the SELECT statement is the same
as a normal SQL SELECT query and can use the full power of
SELECT.

If the SELECT query returns zero rows, null values are
assigned to the target(s). If the SELECT query returns multiple
rows, the first row is assigned to the target(s) and the rest
are discarded. (Note that "the first
row" is not well-defined unless you've used ORDER
BY.)

At present, the INTO clause can appear almost anywhere in
the SELECT query, but it is recommended to place it immediately
after the SELECT keyword as depicted above. Future versions of
PL/pgSQL may be less forgiving
about placement of the INTO clause.

You can use FOUND immediately after
a SELECT INTO statement to determine whether the assignment was
successful (that is, at least one row was was returned by the
SELECT statement). For example:

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

Alternatively, you can use the IS
NULL (or ISNULL) conditional to
test for whether a RECORD/ROW result is null. Note that there
is no way to tell whether any additional rows might have been
discarded.

Sometimes one wishes to evaluate an expression or query but
discard the result (typically because one is calling a function
that has useful side-effects but no useful result value). To do
this in PL/pgSQL, use the
PERFORM statement:

PERFORM query;

This executes a SELECTquery and discards the result.
PL/pgSQL variables are
substituted in 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 SELECT with no INTO
clause would accomplish this result, but at present the
only accepted way to do it is PERFORM.

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

EXECUTE query-string;

where query-string is an
expression yielding a string (of type text) containing the query to be executed. This string is fed
literally to the SQL engine.

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

When working with dynamic queries you will have to face
escaping of single quotes in PL/pgSQL. Please refer to the table in
Section 19.11 for a detailed
explanation that will save you some effort.

Unlike all other queries in PL/pgSQL, a query run by an EXECUTE statement is not
prepared and saved just once during the life of the server.
Instead, the query is prepared
each time the statement is run. The query-string can be dynamically created
within the procedure to perform actions on variable tables and
fields.

The results from SELECT queries are discarded by EXECUTE,
and SELECT INTO is not currently supported within EXECUTE. So,
the only way to extract a result from a dynamically-created
SELECT is to use the FOR-IN-EXECUTE form described later.

This example shows use of the functions quote_ident(TEXT) and
quote_literal(TEXT). Variables containing field and table
identifiers should be passed to function quote_ident(). Variables containing literal
elements of the dynamic query string should be passed to
quote_literal(). Both take the
appropriate steps to return the input text enclosed in single
or double quotes and with any embedded special characters
properly escaped.

Here is a much larger example of a dynamic query and
EXECUTE:

CREATE FUNCTION cs_update_referrer_type_proc() RETURNS INTEGER AS '
DECLARE
referrer_keys RECORD; -- Declare a generic record to be used in a FOR
a_output varchar(4000);
BEGIN
a_output := ''CREATE FUNCTION cs_find_referrer_type(varchar,varchar,varchar)
RETURNS VARCHAR AS ''''
DECLARE
v_host ALIAS FOR $1;
v_domain ALIAS FOR $2;
v_url ALIAS FOR $3;
BEGIN '';
--
-- Notice how we scan through the results of a query in a FOR loop
-- using the FOR <record> construct.
--
FOR referrer_keys IN SELECT * FROM cs_referrer_keys ORDER BY try_order LOOP
a_output := a_output || '' IF v_'' || referrer_keys.kind || '' LIKE ''''''''''
|| referrer_keys.key_string || '''''''''' THEN RETURN ''''''
|| referrer_keys.referrer_type || ''''''; END IF;'';
END LOOP;
a_output := a_output || '' RETURN NULL; END; '''' LANGUAGE ''''plpgsql'''';'';
-- This works because we are not substituting any variables
-- Otherwise it would fail. Look at PERFORM for another way to run functions
EXECUTE a_output;
END;
' LANGUAGE 'plpgsql';

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

GET DIAGNOSTICS variable = item [ , ... ] ;

This command allows retrieval of system status indicators.
Each item is a keyword
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 query sent down to
the SQL engine; and
RESULT_OID, the OID of the last
row inserted by the most recent SQL query. Note that RESULT_OID is only useful after an INSERT
query.

GET DIAGNOSTICS var_integer = ROW_COUNT;

There is a special variable named FOUND of type boolean.
FOUND starts out false within each
PL/pgSQL function. It is set
by each of the following types of statements:

A SELECT INTO statement sets FOUND true if it returns a row, false if no
row is returned.

A PERFORM statement sets FOUND
true if it produces (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 only set 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; any
changes to it affect only the current PL/pgSQL function.