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.
Examples:
user_id := 20;
tax := subtotal * 0.06;
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 target expressions 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.
DECLARE
users_rec RECORD;
full_name varchar;
BEGIN
SELECT INTO users_rec * FROM users WHERE user_id=3;
IF users_rec.homepage IS NULL THEN
-- user entered no homepage, return "http://"
RETURN ''http://'';
END IF;
END;
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 SELECT
query 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.
An example:
PERFORM create_mv(''cs_session_page_requests_mv'', my_query);
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.
An example:
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.
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.
