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, “Executing a Query With No Result” and Section 37.6.3, “Executing a Query with a Single-Row Result”.
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;
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.
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.
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.
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);
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,
SELECTselect_expressions
INTO [STRICT]target
FROM ...; INSERT ... RETURNINGexpressions
INTO [STRICT]target
; UPDATE ... RETURNINGexpressions
INTO [STRICT]target
; DELETE ... RETURNINGexpressions
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.
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, “Obtaining the Result Status”) 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.
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, “Looping Through Query Results”.
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.
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.
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:
EXECUTEcommand-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, “Handling of Quotation Marks”, 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, “Porting a Function that Creates Another Function from PL/SQL to PL/pgSQL”, 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 DIAGNOSTICSvariable
=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.