-<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.148 2009/12/19 01:49:02 tgl Exp $ -->
+<!-- doc/src/sgml/plpgsql.sgml -->
<chapter id="plpgsql">
<title><application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language</title>
</para>
<para>
- In <productname>PostgreSQL</> 8.5 and later,
+ In <productname>PostgreSQL</> 9.0 and later,
<application>PL/pgSQL</application> is installed by default.
However it is still a loadable module, so especially security-conscious
administrators could choose to remove it.
<application>PL/pgSQL</> functions can also be declared to accept
and return the polymorphic types
<type>anyelement</type>, <type>anyarray</type>, <type>anynonarray</type>,
- and <type>anyenum</>. The actual
+ <type>anyenum</>, and <type>anyrange</type>. The actual
data types handled by a polymorphic function can vary from call to
call, as discussed in <xref linkend="extend-types-polymorphic">.
An example is shown in <xref linkend="plpgsql-declaration-parameters">.
<para>
All key words are case-insensitive.
- Identifiers are implicitly converted to lowercase
+ Identifiers are implicitly converted to lower case
unless double-quoted, just as they are in ordinary SQL commands.
</para>
<para>
The general syntax of a variable declaration is:
<synopsis>
-<replaceable>name</replaceable> <optional> CONSTANT </optional> <replaceable>type</replaceable> <optional> NOT NULL </optional> <optional> { DEFAULT | := } <replaceable>expression</replaceable> </optional>;
+<replaceable>name</replaceable> <optional> CONSTANT </optional> <replaceable>type</replaceable> <optional> COLLATE <replaceable>collation_name</replaceable> </optional> <optional> NOT NULL </optional> <optional> { DEFAULT | := | = } <replaceable>expression</replaceable> </optional>;
</synopsis>
The <literal>DEFAULT</> clause, if given, specifies the initial value assigned
to the variable when the block is entered. If the <literal>DEFAULT</> clause
is not given then the variable is initialized to the
<acronym>SQL</acronym> null value.
The <literal>CONSTANT</> option prevents the variable from being
- assigned to, so that its value will remain constant for the duration of
- the block.
+ assigned to after initialization, so that its value will remain constant
+ for the duration of the block.
+ The <literal>COLLATE</> option specifies a collation to use for the
+ variable (see <xref linkend="plpgsql-declaration-collation">).
If <literal>NOT NULL</>
is specified, an assignment of a null value results in a run-time
error. All variables declared as <literal>NOT NULL</>
must have a nonnull default value specified.
+ Equal (<literal>=</>) can be used instead of PL/SQL-compliant
+ <literal>:=</>.
</para>
<para>
END;
$$ LANGUAGE plpgsql;
</programlisting>
- The other way, which was the only way available before
- <productname>PostgreSQL</productname> 8.0, is to explicitly
- declare an alias, using the declaration syntax
+ The other way is to explicitly declare an alias, using the
+ declaration syntax
<synopsis>
<replaceable>name</replaceable> ALIAS FOR $<replaceable>n</replaceable>;
is with <literal>RETURNS TABLE</>, for example:
<programlisting>
-CREATE FUNCTION extended_sales(p_itemno int) RETURNS TABLE(quantity int, total numeric) AS $$
+CREATE FUNCTION extended_sales(p_itemno int)
+RETURNS TABLE(quantity int, total numeric) AS $$
BEGIN
- RETURN QUERY SELECT quantity, quantity * price FROM sales WHERE itemno = p_itemno;
+ RETURN QUERY SELECT s.quantity, s.quantity * s.price FROM sales AS s
+ WHERE s.itemno = p_itemno;
END;
$$ LANGUAGE plpgsql;
</programlisting>
<para>
When the return type of a <application>PL/pgSQL</application>
function is declared as a polymorphic type (<type>anyelement</type>,
- <type>anyarray</type>, <type>anynonarray</type>, or <type>anyenum</>),
- a special parameter <literal>$0</literal>
+ <type>anyarray</type>, <type>anynonarray</type>, <type>anyenum</type>,
+ or <type>anyrange</type>), a special parameter <literal>$0</literal>
is created. Its data type is the actual return type of the function,
as deduced from the actual input types (see <xref
linkend="extend-types-polymorphic">).
</para>
<para>
- The same effect can be had by declaring one or more output parameters as
+ The same effect can be obtained by declaring one or more output parameters as
polymorphic types. In this case the
special <literal>$0</literal> parameter is not used; the output
parameters themselves serve the same purpose. For example:
structure on-the-fly.
</para>
</sect2>
+
+ <sect2 id="plpgsql-declaration-collation">
+ <title>Collation of <application>PL/pgSQL</application> Variables</title>
+
+ <indexterm>
+ <primary>collation</>
+ <secondary>in PL/pgSQL</>
+ </indexterm>
+
+ <para>
+ When a <application>PL/pgSQL</application> function has one or more
+ parameters of collatable data types, a collation is identified for each
+ function call depending on the collations assigned to the actual
+ arguments, as described in <xref linkend="collation">. If a collation is
+ successfully identified (i.e., there are no conflicts of implicit
+ collations among the arguments) then all the collatable parameters are
+ treated as having that collation implicitly. This will affect the
+ behavior of collation-sensitive operations within the function.
+ For example, consider
+
+<programlisting>
+CREATE FUNCTION less_than(a text, b text) RETURNS boolean AS $$
+BEGIN
+ RETURN a < b;
+END;
+$$ LANGUAGE plpgsql;
+
+SELECT less_than(text_field_1, text_field_2) FROM table1;
+SELECT less_than(text_field_1, text_field_2 COLLATE "C") FROM table1;
+</programlisting>
+
+ The first use of <function>less_than</> will use the common collation
+ of <structfield>text_field_1</> and <structfield>text_field_2</> for
+ the comparison, while the second use will use <literal>C</> collation.
+ </para>
+
+ <para>
+ Furthermore, the identified collation is also assumed as the collation of
+ any local variables that are of collatable types. Thus this function
+ would not work any differently if it were written as
+
+<programlisting>
+CREATE FUNCTION less_than(a text, b text) RETURNS boolean AS $$
+DECLARE
+ local_a text := a;
+ local_b text := b;
+BEGIN
+ RETURN local_a < local_b;
+END;
+$$ LANGUAGE plpgsql;
+</programlisting>
+ </para>
+
+ <para>
+ If there are no parameters of collatable data types, or no common
+ collation can be identified for them, then parameters and local variables
+ use the default collation of their data type (which is usually the
+ database's default collation, but could be different for variables of
+ domain types).
+ </para>
+
+ <para>
+ A local variable of a collatable data type can have a different collation
+ associated with it by including the <literal>COLLATE</> option in its
+ declaration, for example
+
+<programlisting>
+DECLARE
+ local_a text COLLATE "en_US";
+</programlisting>
+
+ This option overrides the collation that would otherwise be
+ given to the variable according to the rules above.
+ </para>
+
+ <para>
+ Also, of course explicit <literal>COLLATE</> clauses can be written inside
+ a function if it is desired to force a particular collation to be used in
+ a particular operation. For example,
+
+<programlisting>
+CREATE FUNCTION less_than_c(a text, b text) RETURNS boolean AS $$
+BEGIN
+ RETURN a < b COLLATE "C";
+END;
+$$ LANGUAGE plpgsql;
+</programlisting>
+
+ This overrides the collations associated with the table columns,
+ parameters, or local variables used in the expression, just as would
+ happen in a plain SQL command.
+ </para>
+ </sect2>
</sect1>
<sect1 id="plpgsql-expressions">
and then this prepared statement is <command>EXECUTE</>d for each
execution of the <command>IF</> statement, with the current values
of the <application>PL/pgSQL</application> variables supplied as
- parameter values.
- The query plan prepared in this way is saved for the life of the database
- connection, as described in
- <xref linkend="plpgsql-plan-caching">. Normally these details are
+ parameter values. Normally these details are
not important to a <application>PL/pgSQL</application> user, but
they are useful to know when trying to diagnose a problem.
+ More information appears in <xref linkend="plpgsql-plan-caching">.
</para>
</sect1>
An assignment of a value to a <application>PL/pgSQL</application>
variable is written as:
<synopsis>
-<replaceable>variable</replaceable> := <replaceable>expression</replaceable>;
+<replaceable>variable</replaceable> { := | = } <replaceable>expression</replaceable>;
</synopsis>
As explained previously, the expression in such a statement is evaluated
by means of an SQL <command>SELECT</> command sent to the main
a row value, if the variable is a row or record variable). The target
variable can be a simple variable (optionally qualified with a block
name), a field of a row or record variable, or an element of an array
- that is a simple variable or field.
+ that is a simple variable or field. Equal (<literal>=</>) can be
+ used instead of PL/SQL-compliant <literal>:=</>.
</para>
<para>
If the expression's result data type doesn't match the variable's
- data type, or the variable has a specific size/precision
- (like <type>char(20)</type>), the result value will be implicitly
- converted by the <application>PL/pgSQL</application> 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.
+ data type, the value will be coerced as though by an assignment cast
+ (see <xref linkend="typeconv-query">). If no assignment cast is known
+ for the pair of data types involved, the <application>PL/pgSQL</>
+ interpreter will attempt to convert the result value textually, that is
+ by applying the result type's output function followed by the variable
+ type's input function. Note that this could 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.
</para>
<para>
<para>
When executing a SQL command in this way,
- <application>PL/pgSQL</application> plans the command just once
- and re-uses the plan on subsequent executions, for the life of
- the database connection. The implications of this are discussed
- in detail in <xref linkend="plpgsql-plan-caching">.
+ <application>PL/pgSQL</application> may cache and re-use the execution
+ plan for the command, as discussed in
+ <xref linkend="plpgsql-plan-caching">.
</para>
<para>
result. Write the <replaceable>query</replaceable> the same
way you would write an SQL <command>SELECT</> command, but replace the
initial keyword <command>SELECT</> with <command>PERFORM</command>.
+ For <command>WITH</> queries, use <command>PERFORM</> and then
+ place the query in parentheses. (In this case, the query can only
+ return one row.)
<application>PL/pgSQL</application> variables will be
substituted into the query just as for commands that return no result,
and the plan is cached in the same way. Also, the special variable
</sect2>
<sect2 id="plpgsql-statements-sql-onerow">
- <title>Executing a Query with a Single-Row Result</title>
+ <title>Executing a Query with a Single-row Result</title>
<indexterm zone="plpgsql-statements-sql-onerow">
<primary>SELECT INTO</primary>
which affected row should be returned.
</para>
+ <para>
+ If <literal>print_strict_params</> is enabled for the function,
+ then when an error is thrown because the requirements
+ of <literal>STRICT</> are not met, the <literal>DETAIL</> part of
+ the error message will include information about the parameters
+ passed to the query.
+ You can change the <literal>print_strict_params</>
+ setting for all functions by setting
+ <varname>plpgsql.print_strict_params</>, though only subsequent
+ function compilations will be affected. You can also enable it
+ on a per-function basis by using a compiler option, for example:
+<programlisting>
+CREATE FUNCTION get_userid(username text) RETURNS int
+AS $$
+#print_strict_params on
+DECLARE
+userid int;
+BEGIN
+ SELECT users.userid INTO STRICT userid
+ FROM users WHERE users.username = get_userid.username;
+ RETURN userid;
+END
+$$ LANGUAGE plpgsql;
+</programlisting>
+ On failure, this function might produce an error message such as
+<programlisting>
+ERROR: query returned no rows
+DETAIL: parameters: $1 = 'nosuchuser'
+CONTEXT: PL/pgSQL function get_userid(text) line 6 at SQL statement
+</programlisting>
+ </para>
+
<note>
<para>
The <literal>STRICT</> option matches the behavior of
<command>EXECUTE</command> statement is provided:
<synopsis>
-EXECUTE <replaceable class="command">command-string</replaceable> <optional> INTO <optional>STRICT</optional> <replaceable>target</replaceable> </optional> <optional> USING <replaceable>expression</replaceable> <optional>, ...</optional> </optional>;
+EXECUTE <replaceable class="command">command-string</replaceable> <optional> INTO <optional>STRICT</optional> <replaceable>target</replaceable> </optional> <optional> USING <replaceable>expression</replaceable> <optional>, ... </optional> </optional>;
</synopsis>
where <replaceable>command-string</replaceable> is an expression
<para>
Also, there is no plan caching for commands executed via
- <command>EXECUTE</command>. Instead, the
- command is prepared each time the statement is run. Thus the command
+ <command>EXECUTE</command>. Instead, the command is always planned
+ each time the statement is run. Thus the command
string can be dynamically created within the function to perform
actions on different tables and columns.
</para>
dynamically selected table, you could do this:
<programlisting>
EXECUTE 'SELECT count(*) FROM '
- || tabname::regclass
+ || quote_ident(tabname)
|| ' WHERE inserted_by = $1 AND inserted <= $2'
INTO c
USING checked_user, checked_date;
+</programlisting>
+ A cleaner approach is to use <function>format()</>'s <literal>%I</>
+ specification for table or column names (strings separated by a
+ newline are concatenated):
+<programlisting>
+EXECUTE format('SELECT count(*) FROM %I '
+ 'WHERE inserted_by = $1 AND inserted <= $2', tabname)
+ INTO c
+ USING checked_user, checked_date;
</programlisting>
Another restriction on parameter symbols is that they only work in
<command>SELECT</>, <command>INSERT</>, <command>UPDATE</>, and
The important difference is that <command>EXECUTE</> will re-plan
the command on each execution, generating a plan that is specific
to the current parameter values; whereas
- <application>PL/pgSQL</application> normally creates a generic plan
- and caches it for re-use. In situations where the best plan depends
- strongly on the parameter values, <command>EXECUTE</> can be
- significantly faster; while when the plan is not sensitive to parameter
- values, re-planning will be a waste.
+ <application>PL/pgSQL</application> may otherwise create a generic plan
+ and cache it for re-use. In situations where the best plan depends
+ strongly on the parameter values, it can be helpful to use
+ <command>EXECUTE</> to positively ensure that a generic plan is not
+ selected.
</para>
<para>
<para>
The <application>PL/pgSQL</application>
<command>EXECUTE</command> statement is not related to the
- <xref linkend="sql-execute" endterm="sql-execute-title"> SQL
+ <xref linkend="sql-execute"> SQL
statement supported by the
<productname>PostgreSQL</productname> server. The server's
<command>EXECUTE</command> statement cannot be used directly within
</note>
<example id="plpgsql-quote-literal-example">
- <title>Quoting values in dynamic queries</title>
+ <title>Quoting Values In Dynamic Queries</title>
<indexterm>
<primary>quote_ident</primary>
- <secondary>use in PL/PgSQL</secondary>
+ <secondary>use in PL/pgSQL</secondary>
</indexterm>
<indexterm>
<primary>quote_literal</primary>
- <secondary>use in PL/PgSQL</secondary>
+ <secondary>use in PL/pgSQL</secondary>
</indexterm>
<indexterm>
<primary>quote_nullable</primary>
- <secondary>use in PL/PgSQL</secondary>
+ <secondary>use in PL/pgSQL</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>format</primary>
+ <secondary>use in PL/pgSQL</secondary>
</indexterm>
<para>
</para>
<para>
- Dynamic values that are to be inserted into the constructed
- query require careful handling since they might themselves contain
+ Dynamic values require careful handling since they might 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):
+ An example using <function>format()</> (this assumes that you are
+ dollar quoting the function body so quote marks need not be doubled):
+<programlisting>
+EXECUTE format('UPDATE tbl SET %I = $1 '
+ 'WHERE key = $2', colname) USING newvalue, keyvalue;
+</programlisting>
+ It is also possible to call the quoting functions directly:
<programlisting>
EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
</para>
<para>
- Because <function>quote_literal</function> is labelled
+ Because <function>quote_literal</function> is labeled
<literal>STRICT</literal>, it will always return null when called with a
null argument. In the above example, if <literal>newvalue</> or
<literal>keyvalue</> were null, the entire dynamic query string would
As always, care must be taken to ensure that null values in a query do
not deliver unintended results. For example the <literal>WHERE</> clause
<programlisting>
- 'WHERE key = ' || quote_nullable(keyvalue)
+'WHERE key = ' || quote_nullable(keyvalue)
</programlisting>
will never succeed if <literal>keyvalue</> is null, because the
result of using the equality operator <literal>=</> with a null operand
is always null. If you wish null to work like an ordinary key value,
you would need to rewrite the above as
<programlisting>
- 'WHERE key IS NOT DISTINCT FROM ' || quote_nullable(keyvalue)
+'WHERE key IS NOT DISTINCT FROM ' || quote_nullable(keyvalue)
</programlisting>
(At present, <literal>IS NOT DISTINCT FROM</> is handled much less
efficiently than <literal>=</>, so don't do this unless you must.
<emphasis>must</> use <function>quote_literal</>,
<function>quote_nullable</>, or <function>quote_ident</>, as appropriate.
</para>
+
+ <para>
+ Dynamic SQL statements can also be safely constructed using the
+ <function>format</function> function (see <xref
+ linkend="functions-string">). For example:
+<programlisting>
+EXECUTE format('UPDATE tbl SET %I = %L '
+ 'WHERE key = %L', colname, newvalue, keyvalue);
+</programlisting>
+ <literal>%I</> is equivalent to <function>quote_ident</>, and
+ <literal>%L</> is equivalent to <function>quote_nullable</function>.
+ The <function>format</function> function can be used in conjunction with
+ the <literal>USING</literal> clause:
+<programlisting>
+EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname)
+ USING newvalue, keyvalue;
+</programlisting>
+ This form is better because the variables are handled in their native
+ data type format, rather than unconditionally converting them to
+ text and quoting them via <literal>%L</>. It is also more efficient.
+ </para>
</example>
<para>
command, which has the form:
<synopsis>
-GET DIAGNOSTICS <replaceable>variable</replaceable> = <replaceable>item</replaceable> <optional> , ... </optional>;
+GET <optional> CURRENT </optional> DIAGNOSTICS <replaceable>variable</replaceable> { = | := } <replaceable>item</replaceable> <optional> , ... </optional>;
</synopsis>
- This command allows retrieval of system status indicators. Each
- <replaceable>item</replaceable> 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 <varname>ROW_COUNT</>, the number of rows
- processed by the last <acronym>SQL</acronym> command sent to
- the <acronym>SQL</acronym> engine, and <varname>RESULT_OID</>,
- the OID of the last row inserted by the most recent
- <acronym>SQL</acronym> command. Note that <varname>RESULT_OID</>
- is only useful after an <command>INSERT</command> command into a
- table containing OIDs.
- </para>
-
- <para>
- An example:
+ This command allows retrieval of system status indicators.
+ <literal>CURRENT</> is a noise word (but see also <command>GET STACKED
+ DIAGNOSTICS</command> in <xref linkend="plpgsql-exception-diagnostics">).
+ Each <replaceable>item</replaceable> is a key word identifying a status
+ value to be assigned to the specified <replaceable>variable</replaceable>
+ (which should be of the right data type to receive it). The currently
+ available status items are shown
+ in <xref linkend="plpgsql-current-diagnostics-values">. Colon-equal
+ (<literal>:=</>) can be used instead of the SQL-standard <literal>=</>
+ token. An example:
<programlisting>
GET DIAGNOSTICS integer_var = ROW_COUNT;
</programlisting>
</para>
+ <table id="plpgsql-current-diagnostics-values">
+ <title>Available Diagnostics Items</title>
+ <tgroup cols="3">
+ <thead>
+ <row>
+ <entry>Name</entry>
+ <entry>Type</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><varname>ROW_COUNT</varname></entry>
+ <entry><type>bigint</></entry>
+ <entry>the number of rows processed by the most
+ recent <acronym>SQL</acronym> command</entry>
+ </row>
+ <row>
+ <entry><varname>RESULT_OID</varname></entry>
+ <entry><type>oid</></entry>
+ <entry>the OID of the last row inserted by the most
+ recent <acronym>SQL</acronym> command (only useful after
+ an <command>INSERT</command> command into a table having
+ OIDs)</entry>
+ </row>
+ <row>
+ <entry><literal>PG_CONTEXT</literal></entry>
+ <entry><type>text</></entry>
+ <entry>line(s) of text describing the current call stack
+ (see <xref linkend="plpgsql-call-stack">)</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
<para>
The second method to determine the effects of a command is to check the
special variable named <literal>FOUND</literal>, which is of
true if it successfully repositions the cursor, false otherwise.
</para>
</listitem>
-
<listitem>
<para>
- A <command>FOR</> statement sets <literal>FOUND</literal> true
- if it iterates one or more times, else false. This applies to
- all four variants of the <command>FOR</> statement (integer
- <command>FOR</> loops, record-set <command>FOR</> loops,
- dynamic record-set <command>FOR</> loops, and cursor
- <command>FOR</> loops).
+ A <command>FOR</> or <command>FOREACH</> statement sets
+ <literal>FOUND</literal> true
+ if it iterates one or more times, else false.
<literal>FOUND</literal> is set this way when the
- <command>FOR</> loop exits; inside the execution of the loop,
+ loop exits; inside the execution of the loop,
<literal>FOUND</literal> is not modified by the
- <command>FOR</> statement, although it might be changed by the
+ loop statement, although it might be changed by the
execution of other statements within the loop body.
</para>
</listitem>
<listitem>
<para>
- A <command>RETURN QUERY</command> and <command>RETURN QUERY
+ <command>RETURN QUERY</command> and <command>RETURN QUERY
EXECUTE</command> statements set <literal>FOUND</literal>
true if the query returns at least one row, false if no row
is returned.
<para>
For example, the following two fragments of code are equivalent:
<programlisting>
- 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
+ NULL; -- ignore the error
+END;
</programlisting>
<programlisting>
- BEGIN
- y := x / 0;
- EXCEPTION
- WHEN division_by_zero THEN -- ignore the error
- END;
+BEGIN
+ y := x / 0;
+EXCEPTION
+ WHEN division_by_zero THEN -- ignore the error
+END;
</programlisting>
Which is preferable is a matter of taste.
</para>
</para>
<para>
- When returning a scalar type, any expression can be used. The
- expression's result will be automatically cast into the
- function's return type as described for assignments. To return a
- composite (row) value, you must write a record or row variable
- as the <replaceable>expression</replaceable>.
+ In a function that returns a scalar type, the expression's result will
+ automatically be cast into the function's return type as described for
+ assignments. But to return a composite (row) value, you must write an
+ expression delivering exactly the requested column set. This may
+ require use of explicit casting.
</para>
<para>
however. In those cases a <command>RETURN</command> statement is
automatically executed if the top-level block finishes.
</para>
+
+ <para>
+ Some examples:
+
+<programlisting>
+-- functions returning a scalar type
+RETURN 1 + 2;
+RETURN scalar_var;
+
+-- functions returning a composite type
+RETURN composite_type_var;
+RETURN (1, 2, 'three'::text); -- must cast columns to correct types
+</programlisting>
+ </para>
</sect3>
<sect3>
<title><command>RETURN NEXT</> and <command>RETURN QUERY</command></title>
<indexterm>
<primary>RETURN NEXT</primary>
- <secondary>in PL/PgSQL</secondary>
+ <secondary>in PL/pgSQL</secondary>
</indexterm>
<indexterm>
<primary>RETURN QUERY</primary>
- <secondary>in PL/PgSQL</secondary>
+ <secondary>in PL/pgSQL</secondary>
</indexterm>
<synopsis>
RETURN NEXT <replaceable>expression</replaceable>;
RETURN QUERY <replaceable>query</replaceable>;
-RETURN QUERY EXECUTE <replaceable class="command">command-string</replaceable> <optional> USING <replaceable>expression</replaceable> <optional>, ...</optional> </optional>;
+RETURN QUERY EXECUTE <replaceable class="command">command-string</replaceable> <optional> USING <replaceable>expression</replaceable> <optional>, ... </optional> </optional>;
</synopsis>
<para>
INSERT INTO foo VALUES (1, 2, 'three');
INSERT INTO foo VALUES (4, 5, 'six');
-CREATE OR REPLACE FUNCTION getAllFoo() RETURNS SETOF foo AS
+CREATE OR REPLACE FUNCTION get_all_foo() RETURNS SETOF foo AS
$BODY$
DECLARE
r foo%rowtype;
BEGIN
- FOR r IN SELECT * FROM foo
- WHERE fooid > 0
+ FOR r IN
+ SELECT * FROM foo WHERE fooid > 0
LOOP
-- can do some processing here
RETURN NEXT r; -- return current row of SELECT
RETURN;
END
$BODY$
-LANGUAGE 'plpgsql' ;
+LANGUAGE plpgsql;
-SELECT * FROM getallfoo();
+SELECT * FROM get_all_foo();
+</programlisting>
+ </para>
+
+ <para>
+ Here is an example of a function using <command>RETURN
+ QUERY</command>:
+
+<programlisting>
+CREATE FUNCTION get_available_flightid(date) RETURNS SETOF integer AS
+$BODY$
+BEGIN
+ RETURN QUERY SELECT flightid
+ FROM flight
+ WHERE flightdate >= $1
+ AND flightdate < ($1 + 1);
+
+ -- Since execution is not finished, we can check whether rows were returned
+ -- and raise exception if not.
+ IF NOT FOUND THEN
+ RAISE EXCEPTION 'No flight at %.', $1;
+ END IF;
+
+ RETURN;
+ END
+$BODY$
+LANGUAGE plpgsql;
+
+-- Returns available flights or raises exception if there are no
+-- available flights.
+SELECT * FROM get_available_flightid(CURRENT_DATE);
</programlisting>
</para>
<application>PL/pgSQL</> has three forms of <command>IF</>:
<itemizedlist>
<listitem>
- <para><literal>IF ... THEN</></>
+ <para><literal>IF ... THEN ... END IF</></>
</listitem>
<listitem>
- <para><literal>IF ... THEN ... ELSE</></>
+ <para><literal>IF ... THEN ... ELSE ... END IF</></>
</listitem>
<listitem>
- <para><literal>IF ... THEN ... ELSIF ... THEN ... ELSE</></>
+ <para><literal>IF ... THEN ... ELSIF ... THEN ... ELSE ... END IF</></>
</listitem>
</itemizedlist>
<para>
The searched form of <command>CASE</> provides conditional execution
- based on truth of boolean expressions. Each <literal>WHEN</> clause's
+ based on truth of Boolean expressions. Each <literal>WHEN</> clause's
<replaceable>boolean-expression</replaceable> is evaluated in turn,
until one is found that yields <literal>true</>. Then the
corresponding <replaceable>statements</replaceable> are executed, and
<para>
With the <literal>LOOP</>, <literal>EXIT</>,
- <literal>CONTINUE</>, <literal>WHILE</>, and <literal>FOR</>
- statements, you can arrange for your <application>PL/pgSQL</>
- function to repeat a series of commands.
+ <literal>CONTINUE</>, <literal>WHILE</>, <literal>FOR</>,
+ and <literal>FOREACH</> statements, you can arrange for your
+ <application>PL/pgSQL</> function to repeat a series of commands.
</para>
<sect3>
When used with a
<literal>BEGIN</literal> block, <literal>EXIT</literal> passes
control to the next statement after the end of the block.
- Note that a label must be used for this purpose; an unlabelled
+ Note that a label must be used for this purpose; an unlabeled
<literal>EXIT</literal> is never considered to match a
<literal>BEGIN</literal> block. (This is a change from
pre-8.4 releases of <productname>PostgreSQL</productname>, which
- would allow an unlabelled <literal>EXIT</literal> to match
+ would allow an unlabeled <literal>EXIT</literal> to match
a <literal>BEGIN</literal> block.)
</para>
</sect3>
<sect3 id="plpgsql-integer-for">
- <title><literal>FOR</> (integer variant)</title>
+ <title><literal>FOR</> (Integer Variant)</title>
<synopsis>
<optional> <<<replaceable>label</replaceable>>> </optional>
DECLARE
mviews RECORD;
BEGIN
- PERFORM cs_log('Refreshing materialized views...');
+ RAISE NOTICE 'Refreshing materialized views...';
FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP
-- Now "mviews" has one record from cs_materialized_views
- PERFORM cs_log('Refreshing materialized view ' || quote_ident(mviews.mv_name) || ' ...');
- EXECUTE 'TRUNCATE TABLE ' || quote_ident(mviews.mv_name);
- EXECUTE 'INSERT INTO ' || quote_ident(mviews.mv_name) || ' ' || mviews.mv_query;
+ RAISE NOTICE 'Refreshing materialized view %s ...', quote_ident(mviews.mv_name);
+ EXECUTE format('TRUNCATE TABLE %I', mviews.mv_name);
+ EXECUTE format('INSERT INTO %I %s', mviews.mv_name, mviews.mv_query);
END LOOP;
- PERFORM cs_log('Done refreshing materialized views.');
+ RAISE NOTICE 'Done refreshing materialized views.';
RETURN 1;
END;
$$ LANGUAGE plpgsql;
rows:
<synopsis>
<optional> <<<replaceable>label</replaceable>>> </optional>
-FOR <replaceable>target</replaceable> IN EXECUTE <replaceable>text_expression</replaceable> <optional> USING <replaceable>expression</replaceable> <optional>, ...</optional> </optional> LOOP
+FOR <replaceable>target</replaceable> IN EXECUTE <replaceable>text_expression</replaceable> <optional> USING <replaceable>expression</replaceable> <optional>, ... </optional> </optional> LOOP
<replaceable>statements</replaceable>
END LOOP <optional> <replaceable>label</replaceable> </optional>;
</synopsis>
</para>
</sect2>
+ <sect2 id="plpgsql-foreach-array">
+ <title>Looping Through Arrays</title>
+
+ <para>
+ The <literal>FOREACH</> loop is much like a <literal>FOR</> loop,
+ but instead of iterating through the rows returned by a SQL query,
+ it iterates through the elements of an array value.
+ (In general, <literal>FOREACH</> is meant for looping through
+ components of a composite-valued expression; variants for looping
+ through composites besides arrays may be added in future.)
+ The <literal>FOREACH</> statement to loop over an array is:
+
+<synopsis>
+<optional> <<<replaceable>label</replaceable>>> </optional>
+FOREACH <replaceable>target</replaceable> <optional> SLICE <replaceable>number</replaceable> </optional> IN ARRAY <replaceable>expression</replaceable> LOOP
+ <replaceable>statements</replaceable>
+END LOOP <optional> <replaceable>label</replaceable> </optional>;
+</synopsis>
+ </para>
+
+ <para>
+ Without <literal>SLICE</>, or if <literal>SLICE 0</> is specified,
+ the loop iterates through individual elements of the array produced
+ by evaluating the <replaceable>expression</replaceable>.
+ The <replaceable>target</replaceable> variable is assigned each
+ element value in sequence, and the loop body is executed for each element.
+ Here is an example of looping through the elements of an integer
+ array:
+
+<programlisting>
+CREATE FUNCTION sum(int[]) RETURNS int8 AS $$
+DECLARE
+ s int8 := 0;
+ x int;
+BEGIN
+ FOREACH x IN ARRAY $1
+ LOOP
+ s := s + x;
+ END LOOP;
+ RETURN s;
+END;
+$$ LANGUAGE plpgsql;
+</programlisting>
+
+ The elements are visited in storage order, regardless of the number of
+ array dimensions. Although the <replaceable>target</replaceable> is
+ usually just a single variable, it can be a list of variables when
+ looping through an array of composite values (records). In that case,
+ for each array element, the variables are assigned from successive
+ columns of the composite value.
+ </para>
+
+ <para>
+ With a positive <literal>SLICE</> value, <literal>FOREACH</>
+ iterates through slices of the array rather than single elements.
+ The <literal>SLICE</> value must be an integer constant not larger
+ than the number of dimensions of the array. The
+ <replaceable>target</replaceable> variable must be an array,
+ and it receives successive slices of the array value, where each slice
+ is of the number of dimensions specified by <literal>SLICE</>.
+ Here is an example of iterating through one-dimensional slices:
+
+<programlisting>
+CREATE FUNCTION scan_rows(int[]) RETURNS void AS $$
+DECLARE
+ x int[];
+BEGIN
+ FOREACH x SLICE 1 IN ARRAY $1
+ LOOP
+ RAISE NOTICE 'row = %', x;
+ END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+
+SELECT scan_rows(ARRAY[[1,2,3],[4,5,6],[7,8,9],[10,11,12]]);
+
+NOTICE: row = {1,2,3}
+NOTICE: row = {4,5,6}
+NOTICE: row = {7,8,9}
+NOTICE: row = {10,11,12}
+</programlisting>
+ </para>
+ </sect2>
+
<sect2 id="plpgsql-error-trapping">
<title>Trapping Errors</title>
<indexterm>
<primary>exceptions</primary>
- <secondary>in PL/PgSQL</secondary>
+ <secondary>in PL/pgSQL</secondary>
</indexterm>
<para>
those shown in <xref linkend="errcodes-appendix">. A category
name matches any error within its category. The special
condition name <literal>OTHERS</> matches every error type except
- <literal>QUERY_CANCELED</>. (It is possible, but often unwise,
- to trap <literal>QUERY_CANCELED</> by name.) Condition names are
+ <literal>QUERY_CANCELED</> and <literal>ASSERT_FAILURE</>.
+ (It is possible, but often unwise, to trap those two error types
+ by name.) Condition names are
not case-sensitive. Also, an error condition can be specified
by <literal>SQLSTATE</> code; for example these are equivalent:
<programlisting>
- WHEN division_by_zero THEN ...
- WHEN SQLSTATE '22012' THEN ...
+WHEN division_by_zero THEN ...
+WHEN SQLSTATE '22012' THEN ...
</programlisting>
</para>
As an example, consider this fragment:
<programlisting>
- INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
- BEGIN
- UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
- x := x + 1;
- y := x / 0;
- EXCEPTION
- WHEN division_by_zero THEN
- RAISE NOTICE 'caught division_by_zero';
- RETURN x;
- END;
+INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
+BEGIN
+ UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
+ x := x + 1;
+ y := x / 0;
+EXCEPTION
+ WHEN division_by_zero THEN
+ RAISE NOTICE 'caught division_by_zero';
+ RETURN x;
+END;
</programlisting>
When control reaches the assignment to <literal>y</>, it will
</para>
</tip>
- <para>
- Within an exception handler, the <varname>SQLSTATE</varname>
- variable contains the error code that corresponds to the
- exception that was raised (refer to <xref
- linkend="errcodes-table"> for a list of possible error
- codes). The <varname>SQLERRM</varname> variable contains the
- error message associated with the exception. These variables are
- undefined outside exception handlers.
- </para>
-
<example id="plpgsql-upsert-example">
<title>Exceptions with <command>UPDATE</>/<command>INSERT</></title>
<para>
This example uses exception handling to perform either
- <command>UPDATE</> or <command>INSERT</>, as appropriate:
+ <command>UPDATE</> or <command>INSERT</>, as appropriate. It is
+ recommended that applications use <command>INSERT</> with
+ <literal>ON CONFLICT DO UPDATE</> rather than actually using
+ this pattern. This example serves primarily to illustrate use of
+ <application>PL/pgSQL</application> control flow structures:
<programlisting>
CREATE TABLE db (a INT PRIMARY KEY, b TEXT);
INSERT INTO db(a,b) VALUES (key, data);
RETURN;
EXCEPTION WHEN unique_violation THEN
- -- do nothing, and loop to try the UPDATE again
+ -- Do nothing, and loop to try the UPDATE again.
END;
END LOOP;
END;
SELECT merge_db(1, 'dennis');
</programlisting>
+ This coding assumes the <literal>unique_violation</> error is caused by
+ the <command>INSERT</>, and not by, say, an <command>INSERT</> in a
+ trigger function on the table. It might also misbehave if there is
+ more than one unique index on the table, since it will retry the
+ operation regardless of which index caused the error.
+ More safety could be had by using the
+ features discussed next to check that the trapped error was the one
+ expected.
</para>
</example>
+
+ <sect3 id="plpgsql-exception-diagnostics">
+ <title>Obtaining Information About an Error</title>
+
+ <para>
+ Exception handlers frequently need to identify the specific error that
+ occurred. There are two ways to get information about the current
+ exception in <application>PL/pgSQL</>: special variables and the
+ <command>GET STACKED DIAGNOSTICS</command> command.
+ </para>
+
+ <para>
+ Within an exception handler, the special variable
+ <varname>SQLSTATE</varname> contains the error code that corresponds to
+ the exception that was raised (refer to <xref linkend="errcodes-table">
+ for a list of possible error codes). The special variable
+ <varname>SQLERRM</varname> contains the error message associated with the
+ exception. These variables are undefined outside exception handlers.
+ </para>
+
+ <para>
+ Within an exception handler, one may also retrieve
+ information about the current exception by using the
+ <command>GET STACKED DIAGNOSTICS</command> command, which has the form:
+
+<synopsis>
+GET STACKED DIAGNOSTICS <replaceable>variable</replaceable> { = | := } <replaceable>item</replaceable> <optional> , ... </optional>;
+</synopsis>
+
+ Each <replaceable>item</replaceable> is a key word identifying a status
+ value to be assigned to the specified <replaceable>variable</replaceable>
+ (which should be of the right data type to receive it). The currently
+ available status items are shown
+ in <xref linkend="plpgsql-exception-diagnostics-values">.
+ </para>
+
+ <table id="plpgsql-exception-diagnostics-values">
+ <title>Error Diagnostics Items</title>
+ <tgroup cols="3">
+ <thead>
+ <row>
+ <entry>Name</entry>
+ <entry>Type</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><literal>RETURNED_SQLSTATE</literal></entry>
+ <entry><type>text</></entry>
+ <entry>the SQLSTATE error code of the exception</entry>
+ </row>
+ <row>
+ <entry><literal>COLUMN_NAME</literal></entry>
+ <entry><type>text</></entry>
+ <entry>the name of the column related to exception</entry>
+ </row>
+ <row>
+ <entry><literal>CONSTRAINT_NAME</literal></entry>
+ <entry><type>text</></entry>
+ <entry>the name of the constraint related to exception</entry>
+ </row>
+ <row>
+ <entry><literal>PG_DATATYPE_NAME</literal></entry>
+ <entry><type>text</></entry>
+ <entry>the name of the data type related to exception</entry>
+ </row>
+ <row>
+ <entry><literal>MESSAGE_TEXT</literal></entry>
+ <entry><type>text</></entry>
+ <entry>the text of the exception's primary message</entry>
+ </row>
+ <row>
+ <entry><literal>TABLE_NAME</literal></entry>
+ <entry><type>text</></entry>
+ <entry>the name of the table related to exception</entry>
+ </row>
+ <row>
+ <entry><literal>SCHEMA_NAME</literal></entry>
+ <entry><type>text</></entry>
+ <entry>the name of the schema related to exception</entry>
+ </row>
+ <row>
+ <entry><literal>PG_EXCEPTION_DETAIL</literal></entry>
+ <entry><type>text</></entry>
+ <entry>the text of the exception's detail message, if any</entry>
+ </row>
+ <row>
+ <entry><literal>PG_EXCEPTION_HINT</literal></entry>
+ <entry><type>text</></entry>
+ <entry>the text of the exception's hint message, if any</entry>
+ </row>
+ <row>
+ <entry><literal>PG_EXCEPTION_CONTEXT</literal></entry>
+ <entry><type>text</></entry>
+ <entry>line(s) of text describing the call stack at the time of the
+ exception (see <xref linkend="plpgsql-call-stack">)</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ If the exception did not set a value for an item, an empty string
+ will be returned.
+ </para>
+
+ <para>
+ Here is an example:
+<programlisting>
+DECLARE
+ text_var1 text;
+ text_var2 text;
+ text_var3 text;
+BEGIN
+ -- some processing which might cause an exception
+ ...
+EXCEPTION WHEN OTHERS THEN
+ GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
+ text_var2 = PG_EXCEPTION_DETAIL,
+ text_var3 = PG_EXCEPTION_HINT;
+END;
+</programlisting>
+ </para>
+ </sect3>
+ </sect2>
+
+ <sect2 id="plpgsql-call-stack">
+ <title>Obtaining Execution Location Information</title>
+
+ <para>
+ The <command>GET DIAGNOSTICS</command> command, previously described
+ in <xref linkend="plpgsql-statements-diagnostics">, retrieves information
+ about current execution state (whereas the <command>GET STACKED
+ DIAGNOSTICS</command> command discussed above reports information about
+ the execution state as of a previous error). Its <literal>PG_CONTEXT</>
+ status item is useful for identifying the current execution
+ location. <literal>PG_CONTEXT</> returns a text string with line(s)
+ of text describing the call stack. The first line refers to the current
+ function and currently executing <command>GET DIAGNOSTICS</command>
+ command. The second and any subsequent lines refer to calling functions
+ further up the call stack. For example:
+
+<programlisting>
+CREATE OR REPLACE FUNCTION outer_func() RETURNS integer AS $$
+BEGIN
+ RETURN inner_func();
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION inner_func() RETURNS integer AS $$
+DECLARE
+ stack text;
+BEGIN
+ GET DIAGNOSTICS stack = PG_CONTEXT;
+ RAISE NOTICE E'--- Call Stack ---\n%', stack;
+ RETURN 1;
+END;
+$$ LANGUAGE plpgsql;
+
+SELECT outer_func();
+
+NOTICE: --- Call Stack ---
+PL/pgSQL function inner_func() line 5 at GET DIAGNOSTICS
+PL/pgSQL function outer_func() line 3 at RETURN
+CONTEXT: PL/pgSQL function outer_func() line 3 at RETURN
+ outer_func
+ ------------
+ 1
+(1 row)
+</programlisting>
+
+ </para>
+
+ <para>
+ <literal>GET STACKED DIAGNOSTICS ... PG_EXCEPTION_CONTEXT</literal>
+ returns the same sort of stack trace, but describing the location
+ at which an error was detected, rather than the current location.
+ </para>
</sect2>
</sect1>
DECLARE
curs1 refcursor;
curs2 CURSOR FOR SELECT * FROM tenk1;
- curs3 CURSOR (key integer) IS SELECT * FROM tenk1 WHERE unique1 = key;
+ curs3 CURSOR (key integer) FOR SELECT * FROM tenk1 WHERE unique1 = key;
</programlisting>
All three of these variables have the data type <type>refcursor</>,
but the first can be used with any query, while the second has
<title><command>OPEN FOR EXECUTE</command></title>
<synopsis>
-OPEN <replaceable>unbound_cursorvar</replaceable> <optional> <optional> NO </optional> SCROLL </optional> FOR EXECUTE <replaceable class="command">query_string</replaceable>;
+OPEN <replaceable>unbound_cursorvar</replaceable> <optional> <optional> NO </optional> SCROLL </optional> FOR EXECUTE <replaceable class="command">query_string</replaceable>
+ <optional> USING <replaceable>expression</replaceable> <optional>, ... </optional> </optional>;
</synopsis>
<para>
command. As usual, this gives flexibility so the query plan can vary
from one run to the next (see <xref linkend="plpgsql-plan-caching">),
and it also means that variable substitution is not done on the
- command string.
+ command string. As with <command>EXECUTE</command>, parameter values
+ can be inserted into the dynamic command via
+ <literal>format()</> and <literal>USING</>.
The <literal>SCROLL</> and
<literal>NO SCROLL</> options have the same meanings as for a bound
cursor.
<para>
An example:
<programlisting>
-OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);
+OPEN curs1 FOR EXECUTE format('SELECT * FROM %I WHERE col1 = $1',tabname) USING keyvalue;
</programlisting>
+ In this example, the table name is inserted into the query via
+ <function>format()</>. The comparison value for <literal>col1</>
+ is inserted via a <literal>USING</> parameter, so it needs
+ no quoting.
</para>
</sect3>
- <sect3>
+ <sect3 id="plpgsql-open-bound-cursor">
<title>Opening a Bound Cursor</title>
<synopsis>
-OPEN <replaceable>bound_cursorvar</replaceable> <optional> ( <replaceable>argument_values</replaceable> ) </optional>;
+OPEN <replaceable>bound_cursorvar</replaceable> <optional> ( <optional> <replaceable>argument_name</replaceable> := </optional> <replaceable>argument_value</replaceable> <optional>, ...</optional> ) </optional>;
</synopsis>
<para>
cursor cannot be open already. A list of actual argument value
expressions must appear if and only if the cursor was declared to
take arguments. These values will be substituted in the query.
+ </para>
+
+ <para>
The query plan for a bound cursor is always considered cacheable;
there is no equivalent of <command>EXECUTE</command> in this case.
- Notice that <literal>SCROLL</> and
- <literal>NO SCROLL</> cannot be specified, as the cursor's scrolling
+ Notice that <literal>SCROLL</> and <literal>NO SCROLL</> cannot be
+ specified in <command>OPEN</>, as the cursor's scrolling
behavior was already determined.
</para>
<para>
- Note that because variable substitution is done on the bound
- cursor's query, there are two ways to pass values into the cursor:
- either with an explicit argument to <command>OPEN</>, or
- implicitly by referencing a <application>PL/pgSQL</> variable
- in the query. However, only variables declared before the bound
- cursor was declared will be substituted into it. In either case
- the value to be passed is determined at the time of the
- <command>OPEN</>.
+ Argument values can be passed using either <firstterm>positional</firstterm>
+ or <firstterm>named</firstterm> notation. In positional
+ notation, all arguments are specified in order. In named notation,
+ each argument's name is specified using <literal>:=</literal> to
+ separate it from the argument expression. Similar to calling
+ functions, described in <xref linkend="sql-syntax-calling-funcs">, it
+ is also allowed to mix positional and named notation.
</para>
- <para>
- Examples:
+ Examples (these use the cursor declaration examples above):
<programlisting>
OPEN curs2;
OPEN curs3(42);
+OPEN curs3(key := 42);
</programlisting>
- </para>
+ </para>
+
+ <para>
+ Because variable substitution is done on a bound cursor's query,
+ there are really two ways to pass values into the cursor: either
+ with an explicit argument to <command>OPEN</>, or implicitly by
+ referencing a <application>PL/pgSQL</> variable in the query.
+ However, only variables declared before the bound cursor was
+ declared will be substituted into it. In either case the value to
+ be passed is determined at the time of the <command>OPEN</>.
+ For example, another way to get the same effect as the
+ <literal>curs3</> example above is
+<programlisting>
+DECLARE
+ key integer;
+ curs4 CURSOR FOR SELECT * FROM tenk1 WHERE unique1 = key;
+BEGIN
+ key := 42;
+ OPEN curs4;
+</programlisting>
+ </para>
</sect3>
</sect2>
<para>
The <replaceable>direction</replaceable> clause can be any of the
- variants allowed in the SQL <xref linkend="sql-fetch"
- endterm="sql-fetch-title"> command except the ones that can fetch
+ variants allowed in the SQL <xref linkend="sql-fetch">
+ command except the ones that can fetch
more than one row; namely, it can be
<literal>NEXT</>,
<literal>PRIOR</>,
<para>
The <replaceable>direction</replaceable> clause can be any of the
- variants allowed in the SQL <xref linkend="sql-fetch"
- endterm="sql-fetch-title"> command, namely
+ variants allowed in the SQL <xref linkend="sql-fetch">
+ command, namely
<literal>NEXT</>,
<literal>PRIOR</>,
<literal>FIRST</>,
restrictions on what the cursor's query can be (in particular,
no grouping) and it's best to use <literal>FOR UPDATE</> in the
cursor. For more information see the
- <xref linkend="sql-declare" endterm="sql-declare-title">
+ <xref linkend="sql-declare">
reference page.
</para>
END;
' LANGUAGE plpgsql;
+-- need to be in a transaction to use cursors.
BEGIN;
SELECT reffunc2();
<synopsis>
<optional> <<<replaceable>label</replaceable>>> </optional>
-FOR <replaceable>recordvar</replaceable> IN <replaceable>bound_cursorvar</replaceable> <optional> ( <replaceable>argument_values</replaceable> ) </optional> LOOP
+FOR <replaceable>recordvar</replaceable> IN <replaceable>bound_cursorvar</replaceable> <optional> ( <optional> <replaceable>argument_name</replaceable> := </optional> <replaceable>argument_value</replaceable> <optional>, ...</optional> ) </optional> LOOP
<replaceable>statements</replaceable>
END LOOP <optional> <replaceable>label</replaceable> </optional>;
</synopsis>
the cursor again when the loop exits. A list of actual argument value
expressions must appear if and only if the cursor was declared to take
arguments. These values will be substituted in the query, in just
- the same way as during an <command>OPEN</>.
+ the same way as during an <command>OPEN</> (see <xref
+ linkend="plpgsql-open-bound-cursor">).
+ </para>
+
+ <para>
The variable <replaceable>recordvar</replaceable> is automatically
defined as type <type>record</> and exists only inside the loop (any
existing definition of the variable name is ignored within the loop).
<sect1 id="plpgsql-errors-and-messages">
<title>Errors and Messages</title>
+ <sect2 id="plpgsql-statements-raise">
+ <title>Reporting Errors and Messages</title>
+
<indexterm>
<primary>RAISE</primary>
+ <secondary>in PL/pgSQL</secondary>
</indexterm>
<indexterm>
<primary>reporting errors</primary>
- <secondary>in PL/PgSQL</secondary>
+ <secondary>in PL/pgSQL</secondary>
</indexterm>
<para>
raise errors.
<synopsis>
-RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> '<replaceable class="parameter">format</replaceable>' <optional>, <replaceable class="parameter">expression</replaceable> <optional>, ...</optional></optional> <optional> USING <replaceable class="parameter">option</replaceable> = <replaceable class="parameter">expression</replaceable> <optional>, ... </optional> </optional>;
+RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> '<replaceable class="parameter">format</replaceable>' <optional>, <replaceable class="parameter">expression</replaceable> <optional>, ... </optional></optional> <optional> USING <replaceable class="parameter">option</replaceable> = <replaceable class="parameter">expression</replaceable> <optional>, ... </optional> </optional>;
RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> <replaceable class="parameter">condition_name</> <optional> USING <replaceable class="parameter">option</replaceable> = <replaceable class="parameter">expression</replaceable> <optional>, ... </optional> </optional>;
RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> SQLSTATE '<replaceable class="parameter">sqlstate</>' <optional> USING <replaceable class="parameter">option</replaceable> = <replaceable class="parameter">expression</replaceable> <optional>, ... </optional> </optional>;
RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> USING <replaceable class="parameter">option</replaceable> = <replaceable class="parameter">expression</replaceable> <optional>, ... </optional>;
Inside the format string, <literal>%</literal> is replaced by the
string representation of the next optional argument's value. Write
<literal>%%</literal> to emit a literal <literal>%</literal>.
+ The number of arguments must match the number of <literal>%</>
+ placeholders in the format string, or an error is raised during
+ the compilation of the function.
</para>
<para>
You can attach additional information to the error report by writing
<literal>USING</> followed by <replaceable
class="parameter">option</replaceable> = <replaceable
- class="parameter">expression</replaceable> items. The allowed
- <replaceable class="parameter">option</replaceable> keywords are
- <literal>MESSAGE</>, <literal>DETAIL</>, <literal>HINT</>, and
- <literal>ERRCODE</>, while each <replaceable
- class="parameter">expression</replaceable> can be any string-valued
- expression.
- <literal>MESSAGE</> sets the error message text (this option can't
- be used in the form of <command>RAISE</> that includes a format
- string before <literal>USING</>).
- <literal>DETAIL</> supplies an error detail message, while
- <literal>HINT</> supplies a hint message.
- <literal>ERRCODE</> specifies the error code (SQLSTATE) to report,
- either by condition name as shown in <xref linkend="errcodes-appendix">,
- or directly as a five-character SQLSTATE code.
+ class="parameter">expression</replaceable> items. Each
+ <replaceable class="parameter">expression</replaceable> can be any
+ string-valued expression. The allowed <replaceable
+ class="parameter">option</replaceable> key words are:
+
+ <variablelist id="raise-using-options">
+ <varlistentry>
+ <term><literal>MESSAGE</literal></term>
+ <listitem>
+ <para>Sets the error message text. This option can't be used in the
+ form of <command>RAISE</> that includes a format string
+ before <literal>USING</>.</para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>DETAIL</literal></term>
+ <listitem>
+ <para>Supplies an error detail message.</para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>HINT</literal></term>
+ <listitem>
+ <para>Supplies a hint message.</para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>ERRCODE</literal></term>
+ <listitem>
+ <para>Specifies the error code (SQLSTATE) to report, either by condition
+ name, as shown in <xref linkend="errcodes-appendix">, or directly as a
+ five-character SQLSTATE code.</para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>COLUMN</literal></term>
+ <term><literal>CONSTRAINT</literal></term>
+ <term><literal>DATATYPE</literal></term>
+ <term><literal>TABLE</literal></term>
+ <term><literal>SCHEMA</literal></term>
+ <listitem>
+ <para>Supplies the name of a related object.</para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
</para>
<para>
This example will abort the transaction with the given error message
and hint:
<programlisting>
-RAISE EXCEPTION 'Nonexistent ID --> %', user_id USING HINT = 'Please check your user id';
+RAISE EXCEPTION 'Nonexistent ID --> %', user_id
+ USING HINT = 'Please check your user ID';
</programlisting>
</para>
The last variant of <command>RAISE</> has no parameters at all.
This form can only be used inside a <literal>BEGIN</> block's
<literal>EXCEPTION</> clause;
- it causes the error currently being handled to be re-thrown to the
- next enclosing block.
+ it causes the error currently being handled to be re-thrown.
</para>
+ <note>
+ <para>
+ Before <productname>PostgreSQL</> 9.1, <command>RAISE</> without
+ parameters was interpreted as re-throwing the error from the block
+ containing the active exception handler. Thus an <literal>EXCEPTION</>
+ clause nested within that handler could not catch it, even if the
+ <command>RAISE</> was within the nested <literal>EXCEPTION</> clause's
+ block. This was deemed surprising as well as being incompatible with
+ Oracle's PL/SQL.
+ </para>
+ </note>
+
<para>
If no condition name nor SQLSTATE is specified in a
<command>RAISE EXCEPTION</command> command, the default is to use
</para>
</note>
+ </sect2>
+
+ <sect2 id="plpgsql-statements-assert">
+ <title>Checking Assertions</title>
+
+ <indexterm>
+ <primary>ASSERT</primary>
+ <secondary>in PL/pgSQL</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>assertions</primary>
+ <secondary>in PL/pgSQL</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary><varname>plpgsql.check_asserts</> configuration parameter</primary>
+ </indexterm>
+
+ <para>
+ The <command>ASSERT</command> statement is a convenient shorthand for
+ inserting debugging checks into <application>PL/pgSQL</application>
+ functions.
+
+<synopsis>
+ASSERT <replaceable class="parameter">condition</replaceable> <optional> , <replaceable class="parameter">message</replaceable> </optional>;
+</synopsis>
+
+ The <replaceable class="parameter">condition</replaceable> is a Boolean
+ expression that is expected to always evaluate to true; if it does,
+ the <command>ASSERT</command> statement does nothing further. If the
+ result is false or null, then an <literal>ASSERT_FAILURE</> exception
+ is raised. (If an error occurs while evaluating
+ the <replaceable class="parameter">condition</replaceable>, it is
+ reported as a normal error.)
+ </para>
+
+ <para>
+ If the optional <replaceable class="parameter">message</replaceable> is
+ provided, it is an expression whose result (if not null) replaces the
+ default error message text <quote>assertion failed</>, should
+ the <replaceable class="parameter">condition</replaceable> fail.
+ The <replaceable class="parameter">message</replaceable> expression is
+ not evaluated in the normal case where the assertion succeeds.
+ </para>
+
+ <para>
+ Testing of assertions can be enabled or disabled via the configuration
+ parameter <literal>plpgsql.check_asserts</>, which takes a Boolean
+ value; the default is <literal>on</>. If this parameter
+ is <literal>off</> then <command>ASSERT</> statements do nothing.
+ </para>
+
+ <para>
+ Note that <command>ASSERT</command> is meant for detecting program
+ bugs, not for reporting ordinary error conditions. Use
+ the <command>RAISE</> statement, described above, for that.
+ </para>
+
+ </sect2>
+
</sect1>
<sect1 id="plpgsql-trigger">
</indexterm>
<para>
- <application>PL/pgSQL</application> can be used to define trigger
- procedures. A trigger procedure is created with the
- <command>CREATE FUNCTION</> command, declaring it as a function with
- no arguments and a return type of <type>trigger</type>. Note that
- the function must be declared with no arguments even if it expects
- to receive arguments specified in <command>CREATE TRIGGER</> —
- trigger arguments are passed via <varname>TG_ARGV</>, as described
- below.
+ <application>PL/pgSQL</application> can be used to define trigger
+ procedures on data changes or database events.
+ A trigger procedure is created with the <command>CREATE FUNCTION</>
+ command, declaring it as a function with no arguments and a return type of
+ <type>trigger</> (for data change triggers) or
+ <type>event_trigger</> (for database event triggers).
+ Special local variables named <varname>PG_<replaceable>something</></> are
+ automatically defined to describe the condition that triggered the call.
+ </para>
+
+ <sect2 id="plpgsql-dml-trigger">
+ <title>Triggers on Data Changes</title>
+
+ <para>
+ A <link linkend="triggers">data change trigger</> is declared as a
+ function with no arguments and a return type of <type>trigger</>.
+ Note that the function must be declared with no arguments even if it
+ expects to receive some arguments specified in <command>CREATE TRIGGER</>
+ — such arguments are passed via <varname>TG_ARGV</>, as described
+ below.
</para>
<para>
<para>
Data type <type>RECORD</type>; variable holding the new
database row for <command>INSERT</>/<command>UPDATE</> operations in row-level
- triggers. This variable is <symbol>NULL</symbol> in statement-level triggers
+ triggers. This variable is unassigned in statement-level triggers
and for <command>DELETE</command> operations.
</para>
</listitem>
<para>
Data type <type>RECORD</type>; variable holding the old
database row for <command>UPDATE</>/<command>DELETE</> operations in row-level
- triggers. This variable is <symbol>NULL</symbol> in statement-level triggers
+ triggers. This variable is unassigned in statement-level triggers
and for <command>INSERT</command> operations.
</para>
</listitem>
<term><varname>TG_WHEN</varname></term>
<listitem>
<para>
- Data type <type>text</type>; a string of either
- <literal>BEFORE</literal> or <literal>AFTER</literal>
- depending on the trigger's definition.
+ Data type <type>text</type>; a string of
+ <literal>BEFORE</literal>, <literal>AFTER</literal>, or
+ <literal>INSTEAD OF</literal>, depending on the trigger's definition.
</para>
</listitem>
</varlistentry>
for this row). If a nonnull
value is returned then the operation proceeds with that row value.
Returning a row value different from the original value
- of <varname>NEW</> alters the row that will be inserted or updated
- (but has no direct effect in the <command>DELETE</> case).
- To alter the row to be stored, it is possible to replace single values
- directly in <varname>NEW</> and return the modified <varname>NEW</>,
- or to build a complete new record/row to return.
+ of <varname>NEW</> alters the row that will be inserted or
+ updated. Thus, if the trigger function wants the triggering
+ action to succeed normally without altering the row
+ value, <varname>NEW</varname> (or a value equal thereto) has to be
+ returned. To alter the row to be stored, it is possible to
+ replace single values directly in <varname>NEW</> and return the
+ modified <varname>NEW</>, or to build a complete new record/row to
+ return. In the case of a before-trigger
+ on <command>DELETE</command>, the returned value has no direct
+ effect, but it has to be nonnull to allow the trigger action to
+ proceed. Note that <varname>NEW</varname> is null
+ in <command>DELETE</command> triggers, so returning that is
+ usually not sensible. The usual idiom in <command>DELETE</command>
+ triggers is to return <varname>OLD</varname>.
</para>
<para>
- The return value of a <literal>BEFORE</> or <literal>AFTER</>
- statement-level trigger or an <literal>AFTER</> row-level trigger is
+ <literal>INSTEAD OF</> triggers (which are always row-level triggers,
+ and may only be used on views) can return null to signal that they did
+ not perform any updates, and that the rest of the operation for this
+ row should be skipped (i.e., subsequent triggers are not fired, and the
+ row is not counted in the rows-affected status for the surrounding
+ <command>INSERT</>/<command>UPDATE</>/<command>DELETE</>).
+ Otherwise a nonnull value should be returned, to signal
+ that the trigger performed the requested operation. For
+ <command>INSERT</> and <command>UPDATE</> operations, the return value
+ should be <varname>NEW</>, which the trigger function may modify to
+ support <command>INSERT RETURNING</> and <command>UPDATE RETURNING</>
+ (this will also affect the row value passed to any subsequent triggers,
+ or passed to a special <varname>EXCLUDED</> alias reference within
+ an <command>INSERT</> statement with an <literal>ON CONFLICT DO
+ UPDATE</> clause). For <command>DELETE</> operations, the return
+ value should be <varname>OLD</>.
+ </para>
+
+ <para>
+ The return value of a row-level trigger
+ fired <literal>AFTER</literal> or a statement-level trigger
+ fired <literal>BEFORE</> or <literal>AFTER</> is
always ignored; it might as well be null. However, any of these types of
triggers might still abort the entire operation by raising an error.
</para>
RAISE EXCEPTION '% cannot have null salary', NEW.empname;
END IF;
- -- Who works for us when she must pay for it?
+ -- Who works for us when they must pay for it?
IF NEW.salary < 0 THEN
RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
END IF;
--
IF (TG_OP = 'DELETE') THEN
INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
- RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
- RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
- RETURN NEW;
END IF;
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
</programlisting>
</example>
+ <para>
+ A variation of the previous example uses a view joining the main table
+ to the audit table, to show when each entry was last modified. This
+ approach still records the full audit trail of changes to the table,
+ but also presents a simplified view of the audit trail, showing just
+ the last modified timestamp derived from the audit trail for each entry.
+ <xref linkend="plpgsql-view-trigger-audit-example"> shows an example
+ of an audit trigger on a view in <application>PL/pgSQL</application>.
+ </para>
+
+ <example id="plpgsql-view-trigger-audit-example">
+ <title>A <application>PL/pgSQL</application> View Trigger Procedure For Auditing</title>
+
+ <para>
+ This example uses a trigger on the view to make it updatable, and
+ ensure that any insert, update or delete of a row in the view is
+ recorded (i.e., audited) in the <literal>emp_audit</literal> table. The current time
+ and user name are recorded, together with the type of operation
+ performed, and the view displays the last modified time of each row.
+ </para>
+
+<programlisting>
+CREATE TABLE emp (
+ empname text PRIMARY KEY,
+ salary integer
+);
+
+CREATE TABLE emp_audit(
+ operation char(1) NOT NULL,
+ userid text NOT NULL,
+ empname text NOT NULL,
+ salary integer,
+ stamp timestamp NOT NULL
+);
+
+CREATE VIEW emp_view AS
+ SELECT e.empname,
+ e.salary,
+ max(ea.stamp) AS last_updated
+ FROM emp e
+ LEFT JOIN emp_audit ea ON ea.empname = e.empname
+ GROUP BY 1, 2;
+
+CREATE OR REPLACE FUNCTION update_emp_view() RETURNS TRIGGER AS $$
+ BEGIN
+ --
+ -- Perform the required operation on emp, and create a row in emp_audit
+ -- to reflect the change made to emp.
+ --
+ IF (TG_OP = 'DELETE') THEN
+ DELETE FROM emp WHERE empname = OLD.empname;
+ IF NOT FOUND THEN RETURN NULL; END IF;
+
+ OLD.last_updated = now();
+ INSERT INTO emp_audit VALUES('D', user, OLD.*);
+ RETURN OLD;
+ ELSIF (TG_OP = 'UPDATE') THEN
+ UPDATE emp SET salary = NEW.salary WHERE empname = OLD.empname;
+ IF NOT FOUND THEN RETURN NULL; END IF;
+
+ NEW.last_updated = now();
+ INSERT INTO emp_audit VALUES('U', user, NEW.*);
+ RETURN NEW;
+ ELSIF (TG_OP = 'INSERT') THEN
+ INSERT INTO emp VALUES(NEW.empname, NEW.salary);
+
+ NEW.last_updated = now();
+ INSERT INTO emp_audit VALUES('I', user, NEW.*);
+ RETURN NEW;
+ END IF;
+ END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER emp_audit
+INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_view
+ FOR EACH ROW EXECUTE PROCEDURE update_emp_view();
+</programlisting>
+ </example>
+
<para>
One use of triggers is to maintain a summary table
of another table. The resulting summary can be used in place of the
--
-- Function and trigger to amend summarized column(s) on UPDATE, INSERT, DELETE.
--
-CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER AS $maint_sales_summary_bytime$
+CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER
+AS $maint_sales_summary_bytime$
DECLARE
delta_time_key integer;
delta_amount_sold numeric(15,2);
-- (probably not too onerous, as DELETE + INSERT is how most
-- changes will be made).
IF ( OLD.time_key != NEW.time_key) THEN
- RAISE EXCEPTION 'Update of time_key : % -> % not allowed', OLD.time_key, NEW.time_key;
+ RAISE EXCEPTION 'Update of time_key : % -> % not allowed',
+ OLD.time_key, NEW.time_key;
END IF;
delta_time_key = OLD.time_key;
SELECT * FROM sales_summary_bytime;
</programlisting>
</example>
+</sect2>
+
+ <sect2 id="plpgsql-event-trigger">
+ <title>Triggers on Events</title>
+
+ <para>
+ <application>PL/pgSQL</application> can be used to define
+ <link linkend="event-triggers">event triggers</>.
+ <productname>PostgreSQL</> requires that a procedure that
+ is to be called as an event trigger must be declared as a function with
+ no arguments and a return type of <literal>event_trigger</>.
+ </para>
+
+ <para>
+ When a <application>PL/pgSQL</application> function is called as an
+ event trigger, several special variables are created automatically
+ in the top-level block. They are:
+
+ <variablelist>
+ <varlistentry>
+ <term><varname>TG_EVENT</varname></term>
+ <listitem>
+ <para>
+ Data type <type>text</type>; a string representing the event the
+ trigger is fired for.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><varname>TG_TAG</varname></term>
+ <listitem>
+ <para>
+ Data type <type>text</type>; variable that contains the command tag
+ for which the trigger is fired.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+
+ <para>
+ <xref linkend="plpgsql-event-trigger-example"> shows an example of an
+ event trigger procedure in <application>PL/pgSQL</application>.
+ </para>
+
+ <example id="plpgsql-event-trigger-example">
+ <title>A <application>PL/pgSQL</application> Event Trigger Procedure</title>
+
+ <para>
+ This example trigger simply raises a <literal>NOTICE</literal> message
+ each time a supported command is executed.
+ </para>
+
+<programlisting>
+CREATE OR REPLACE FUNCTION snitch() RETURNS event_trigger AS $$
+BEGIN
+ RAISE NOTICE 'snitch: % %', tg_event, tg_tag;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE EVENT TRIGGER snitch ON ddl_command_start EXECUTE PROCEDURE snitch();
+</programlisting>
+ </example>
+ </sect2>
</sect1>
column reference is syntactically allowed. As an extreme case, consider
this example of poor programming style:
<programlisting>
- INSERT INTO foo (foo) VALUES (foo);
+INSERT INTO foo (foo) VALUES (foo);
</programlisting>
The first occurrence of <literal>foo</> must syntactically be a table
name, so it will not be substituted, even if the function has a variable
<note>
<para>
- <productname>PostgreSQL</productname> versions before 8.5 would try
+ <productname>PostgreSQL</productname> versions before 9.0 would try
to substitute the variable in all three cases, leading to syntax errors.
</para>
</note>
tables: is a given name meant to refer to a table column, or a variable?
Let's change the previous example to
<programlisting>
- INSERT INTO dest (col) SELECT foo + bar FROM src;
+INSERT INTO dest (col) SELECT foo + bar FROM src;
</programlisting>
Here, <literal>dest</> and <literal>src</> must be table names, and
<literal>col</> must be a column of <literal>dest</>, but <literal>foo</>
declare it in a labeled block and use the block's label
(see <xref linkend="plpgsql-structure">). For example,
<programlisting>
- <<block>>
- DECLARE
- foo int;
- BEGIN
- foo := ...;
- INSERT INTO dest (col) SELECT block.foo + bar FROM src;
+<<block>>
+DECLARE
+ foo int;
+BEGIN
+ foo := ...;
+ INSERT INTO dest (col) SELECT block.foo + bar FROM src;
</programlisting>
Here <literal>block.foo</> means the variable even if there is a column
<literal>foo</> in <literal>src</>. Function parameters, as well as
large body of <application>PL/pgSQL</> code. In such cases you can
specify that <application>PL/pgSQL</> should resolve ambiguous references
as the variable (which is compatible with <application>PL/pgSQL</>'s
- behavior before <productname>PostgreSQL</productname> 8.5), or as the
+ behavior before <productname>PostgreSQL</productname> 9.0), or as the
table column (which is compatible with some other systems such as
<productname>Oracle</productname>).
</para>
<literal>use_column</> (where <literal>error</> is the factory default).
This parameter affects subsequent compilations
of statements in <application>PL/pgSQL</> functions, but not statements
- already compiled in the current session. To set the parameter before
- <application>PL/pgSQL</> has been loaded, it is necessary to have added
- <quote><literal>plpgsql</></> to the <xref
- linkend="guc-custom-variable-classes"> list in
- <filename>postgresql.conf</filename>. Because changing this setting
+ already compiled in the current session.
+ Because changing this setting
can cause unexpected changes in the behavior of <application>PL/pgSQL</>
functions, it can only be changed by a superuser.
</para>
</para>
<para>
+ <indexterm>
+ <primary>preparing a query</>
+ <secondary>in PL/pgSQL</>
+ </indexterm>
As each expression and <acronym>SQL</acronym> command is first
executed in the function, the <application>PL/pgSQL</> interpreter
- creates a prepared execution plan (using the
- <acronym>SPI</acronym> manager's <function>SPI_prepare</function>
- and <function>SPI_saveplan</function>
- functions).<indexterm><primary>preparing a query</><secondary>in
- PL/pgSQL</></> Subsequent visits to that expression or command
- reuse the prepared plan. Thus, a function with conditional code
- that contains many statements for which execution plans might be
- required will only prepare and save those plans that are really
- used during the lifetime of the database connection. This can
- substantially reduce the total amount of time required to parse
- and generate execution plans for the statements in a
- <application>PL/pgSQL</> function. A disadvantage is that errors
+ parses and analyzes the command to create a prepared statement,
+ using the <acronym>SPI</acronym> manager's
+ <function>SPI_prepare</function> function.
+ Subsequent visits to that expression or command
+ reuse the prepared statement. Thus, a function with conditional code
+ paths that are seldom visited will never incur the overhead of
+ analyzing those commands that are never executed within the current
+ session. A disadvantage is that errors
in a specific expression or command cannot be detected until that
part of the function is reached in execution. (Trivial syntax
errors will be detected during the initial parsing pass, but
</para>
<para>
- A saved plan will be re-planned automatically if there is any schema
- change to any table used in the query, or if any user-defined function
- used in the query is redefined. This makes the re-use of prepared plans
- transparent in most cases, but there are corner cases where a stale plan
- might be re-used. An example is that dropping and re-creating a
- user-defined operator won't affect already-cached plans; they'll continue
- to call the original operator's underlying function, if that has not been
- changed. When necessary, the cache can be flushed by starting a fresh
- database session.
+ <application>PL/pgSQL</> (or more precisely, the SPI manager) can
+ furthermore attempt to cache the execution plan associated with any
+ particular prepared statement. If a cached plan is not used, then
+ a fresh execution plan is generated on each visit to the statement,
+ and the current parameter values (that is, <application>PL/pgSQL</>
+ variable values) can be used to optimize the selected plan. If the
+ statement has no parameters, or is executed many times, the SPI manager
+ will consider creating a <firstterm>generic</> plan that is not dependent
+ on specific parameter values, and caching that for re-use. Typically
+ this will happen only if the execution plan is not very sensitive to
+ the values of the <application>PL/pgSQL</> variables referenced in it.
+ If it is, generating a plan each time is a net win. See <xref
+ linkend="sql-prepare"> for more information about the behavior of
+ prepared statements.
</para>
<para>
- Because <application>PL/pgSQL</application> saves execution plans
- in this way, SQL commands that appear directly in a
+ Because <application>PL/pgSQL</application> saves prepared statements
+ and sometimes execution plans in this way,
+ SQL commands that appear directly in a
<application>PL/pgSQL</application> function must refer to the
same tables and columns on every execution; that is, you cannot use
a parameter as the name of a table or column in an SQL command. To get
around this restriction, you can construct dynamic commands using
the <application>PL/pgSQL</application> <command>EXECUTE</command>
- statement — at the price of constructing a new execution plan on
- every execution.
- </para>
-
- <para>
- Another important point is that the prepared plans are parameterized
- to allow the values of <application>PL/pgSQL</application> variables
- to change from one use to the next, as discussed in detail above.
- Sometimes this means that a plan is less efficient than it would be
- if generated for a specific variable value. As an example, consider
-<programlisting>
-SELECT * INTO myrec FROM dictionary WHERE word LIKE search_term;
-</programlisting>
- where <literal>search_term</> is a <application>PL/pgSQL</application>
- variable. The cached plan for this query will never use an index on
- <structfield>word</>, since the planner cannot assume that the
- <literal>LIKE</> pattern will be left-anchored at run time. To use
- an index the query must be planned with a specific constant
- <literal>LIKE</> pattern provided. This is another situation where
- <command>EXECUTE</command> can be used to force a new plan to be
- generated for each execution.
+ statement — at the price of performing new parse analysis and
+ constructing a new execution plan on every execution.
</para>
<para>
connection. When fields of a record variable are used in
expressions or statements, the data types of the fields must not
change from one call of the function to the next, since each
- expression will be planned using the data type that is present
+ expression will be analyzed using the data type that is present
when the expression is first reached. <command>EXECUTE</command> can be
used to get around this problem when necessary.
</para>
<para>
If the same function is used as a trigger for more than one table,
- <application>PL/pgSQL</application> prepares and caches plans
+ <application>PL/pgSQL</application> prepares and caches statements
independently for each such table — that is, there is a cache
for each trigger function and table combination, not just for each
function. This alleviates some of the problems with varying
<para>
Likewise, functions having polymorphic argument types have a separate
- plan cache for each combination of actual argument types they have been
- invoked for, so that data type differences do not cause unexpected
+ statement cache for each combination of actual argument types they have
+ been invoked for, so that data type differences do not cause unexpected
failures.
</para>
<para>
- Plan caching can sometimes have surprising effects on the interpretation
- of time-sensitive values. For example there
+ Statement caching can sometimes have surprising effects on the
+ interpretation of time-sensitive values. For example there
is a difference between what these two functions do:
<programlisting>
<para>
In the case of <function>logfunc1</function>, the
<productname>PostgreSQL</productname> main parser knows when
- preparing the plan for the <command>INSERT</command> that the
+ analyzing the <command>INSERT</command> that the
string <literal>'now'</literal> should be interpreted as
<type>timestamp</type>, because the target column of
<classname>logtable</classname> is of that type. Thus,
- <literal>'now'</literal> will be converted to a constant when the
- <command>INSERT</command> is planned, and then used in all
+ <literal>'now'</literal> will be converted to a <type>timestamp</type>
+ constant when the
+ <command>INSERT</command> is analyzed, and then used in all
invocations of <function>logfunc1</function> during the lifetime
of the session. Needless to say, this isn't what the programmer
- wanted.
+ wanted. A better idea is to use the <literal>now()</> or
+ <literal>current_timestamp</> function.
</para>
<para>
string to the <type>timestamp</type> type by calling the
<function>text_out</function> and <function>timestamp_in</function>
functions for the conversion. So, the computed time stamp is updated
- on each execution as the programmer expects.
+ on each execution as the programmer expects. Even though this
+ happens to work as expected, it's not terribly efficient, so
+ use of the <literal>now()</> function would still be a better idea.
</para>
</sect2>
<para>
Another good way to develop in <application>PL/pgSQL</> is with a
GUI database access tool that facilitates development in a
- procedural language. One example of such as a tool is
+ procedural language. One example of such a tool is
<application>pgAdmin</>, although others exist. These tools often
provide convenient features such as escaping single quotes and
making it easier to recreate and debug functions.
</variablelist>
</sect2>
+ <sect2 id="plpgsql-extra-checks">
+ <title>Additional Compile-time Checks</title>
+
+ <para>
+ To aid the user in finding instances of simple but common problems before
+ they cause harm, <application>PL/pgSQL</> provides additional
+ <replaceable>checks</>. When enabled, depending on the configuration, they
+ can be used to emit either a <literal>WARNING</> or an <literal>ERROR</>
+ during the compilation of a function. A function which has received
+ a <literal>WARNING</> can be executed without producing further messages,
+ so you are advised to test in a separate development environment.
+ </para>
+
+ <para>
+ These additional checks are enabled through the configuration variables
+ <varname>plpgsql.extra_warnings</> for warnings and
+ <varname>plpgsql.extra_errors</> for errors. Both can be set either to
+ a comma-separated list of checks, <literal>"none"</> or <literal>"all"</>.
+ The default is <literal>"none"</>. Currently the list of available checks
+ includes only one:
+ <variablelist>
+ <varlistentry>
+ <term><varname>shadowed_variables</varname></term>
+ <listitem>
+ <para>
+ Checks if a declaration shadows a previously defined variable.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+
+ The following example shows the effect of <varname>plpgsql.extra_warnings</>
+ set to <varname>shadowed_variables</>:
+<programlisting>
+SET plpgsql.extra_warnings TO 'shadowed_variables';
+
+CREATE FUNCTION foo(f1 int) RETURNS int AS $$
+DECLARE
+f1 int;
+BEGIN
+RETURN f1;
+END
+$$ LANGUAGE plpgsql;
+WARNING: variable "f1" shadows a previously defined variable
+LINE 3: f1 int;
+ ^
+CREATE FUNCTION
+</programlisting>
+ </para>
+ </sect2>
</sect1>
<!-- **** Porting from Oracle PL/SQL **** -->
</para>
</listitem>
+ <listitem>
+ <para>
+ Data type names often need translation. For example, in Oracle string
+ values are commonly declared as being of type <type>varchar2</>, which
+ is a non-SQL-standard type. In <productname>PostgreSQL</productname>,
+ use type <type>varchar</> or <type>text</> instead. Similarly, replace
+ type <type>number</> with <type>numeric</>, or use some other numeric
+ data type if there's a more appropriate one.
+ </para>
+ </listitem>
+
<listitem>
<para>
Instead of packages, use schemas to organize your functions
<para>
Here is an <productname>Oracle</productname> <application>PL/SQL</> function:
<programlisting>
-CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar,
- v_version varchar)
-RETURN varchar IS
+CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar2,
+ v_version varchar2)
+RETURN varchar2 IS
BEGIN
IF v_version IS NULL THEN
RETURN v_name;
<application>PL/pgSQL</>:
<itemizedlist>
+ <listitem>
+ <para>
+ The type name <type>varchar2</> has to be changed to <type>varchar</>
+ or <type>text</>. In the examples in this section, we'll
+ use <type>varchar</>, but <type>text</> is often a better choice if
+ you do not need specific string length limits.
+ </para>
+ </listitem>
+
<listitem>
<para>
The <literal>RETURN</literal> key word in the function
ORDER BY try_order;
func_cmd VARCHAR(4000);
BEGIN
- func_cmd := 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host IN VARCHAR,
- v_domain IN VARCHAR, v_url IN VARCHAR) RETURN VARCHAR IS BEGIN';
+ func_cmd := 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host IN VARCHAR2,
+ v_domain IN VARCHAR2, v_url IN VARCHAR2) RETURN VARCHAR2 IS BEGIN';
FOR referrer_key IN referrer_keys LOOP
func_cmd := func_cmd ||
<programlisting>
CREATE OR REPLACE FUNCTION cs_update_referrer_type_proc() RETURNS void AS $func$
DECLARE
- CURSOR referrer_keys IS
+ referrer_keys CURSOR IS
SELECT * FROM cs_referrer_keys
ORDER BY try_order;
func_body text;
<productname>PostgreSQL</> does not have a built-in
<function>instr</function> function, but you can create one
using a combination of other
- functions.<indexterm><primary>instr</></indexterm> In <xref
- linkend="plpgsql-porting-appendix"> there is a
+ functions. In <xref linkend="plpgsql-porting-appendix"> there is a
<application>PL/pgSQL</application> implementation of
<function>instr</function> that you can use to make your porting
easier.
This is the Oracle version:
<programlisting>
CREATE OR REPLACE PROCEDURE cs_parse_url(
- v_url IN VARCHAR,
- v_host OUT VARCHAR, -- This will be passed back
- v_path OUT VARCHAR, -- This one too
- v_query OUT VARCHAR) -- And this one
+ v_url IN VARCHAR2,
+ v_host OUT VARCHAR2, -- This will be passed back
+ v_path OUT VARCHAR2, -- This one too
+ v_query OUT VARCHAR2) -- And this one
IS
a_pos1 INTEGER;
a_pos2 INTEGER;
<programlisting>
CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id IN INTEGER) IS
a_running_job_count INTEGER;
- PRAGMA AUTONOMOUS_TRANSACTION;<co id="co.plpgsql-porting-pragma">
+ PRAGMA AUTONOMOUS_TRANSACTION; -- <co id="co.plpgsql-porting-pragma">
BEGIN
- LOCK TABLE cs_jobs IN EXCLUSIVE MODE;<co id="co.plpgsql-porting-locktable">
+ LOCK TABLE cs_jobs IN EXCLUSIVE MODE; -- <co id="co.plpgsql-porting-locktable">
SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;
IF a_running_job_count > 0 THEN
- COMMIT; -- free lock<co id="co.plpgsql-porting-commit">
- raise_application_error(-20000, 'Unable to create a new job: a job is currently running.');
+ COMMIT; -- free lock <co id="co.plpgsql-porting-commit">
+ raise_application_error(-20000,
+ 'Unable to create a new job: a job is currently running.');
END IF;
DELETE FROM cs_active_job;
SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;
IF a_running_job_count > 0 THEN
- RAISE EXCEPTION 'Unable to create a new job: a job is currently running';<co id="co.plpgsql-porting-raise">
+ RAISE EXCEPTION 'Unable to create a new job: a job is currently running'; -- <co id="co.plpgsql-porting-raise">
END IF;
DELETE FROM cs_active_job;
BEGIN
INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, now());
EXCEPTION
- WHEN unique_violation THEN <co id="co.plpgsql-porting-exception">
+ WHEN unique_violation THEN -- <co id="co.plpgsql-porting-exception">
-- don't worry if it already exists
END;
END;
is equivalent to what you'd get in Oracle with:
<programlisting>
- BEGIN
- SAVEPOINT s1;
+BEGIN
+ SAVEPOINT s1;
+ ... code here ...
+EXCEPTION
+ WHEN ... THEN
+ ROLLBACK TO s1;
... code here ...
- EXCEPTION
- WHEN ... THEN
- ROLLBACK TO s1;
- ... code here ...
- WHEN ... THEN
- ROLLBACK TO s1;
- ... code here ...
- END;
+ WHEN ... THEN
+ ROLLBACK TO s1;
+ ... code here ...
+END;
</programlisting>
If you are translating an Oracle procedure that uses
the function always returns the same result when given the same
arguments) and <quote>strictness</quote> (whether the function
returns null if any argument is null). Consult the <xref
- linkend="sql-createfunction" endterm="sql-createfunction-title">
+ linkend="sql-createfunction">
reference page for details.
</para>
your porting efforts.
</para>
+ <indexterm>
+ <primary><function>instr</> function</primary>
+ </indexterm>
+
<programlisting>
--
-- instr functions that mimic Oracle's counterpart
ELSE
RETURN pos + beg_index - 1;
END IF;
- ELSE
+ ELSIF beg_index < 0 THEN
ss_length := char_length(string_to_search);
length := char_length(string);
beg := length + beg_index - ss_length + 2;
beg := beg - 1;
END LOOP;
+ RETURN 0;
+ ELSE
RETURN 0;
END IF;
END;
ELSE
RETURN beg;
END IF;
- ELSE
+ ELSIF beg_index < 0 THEN
ss_length := char_length(string_to_search);
length := char_length(string);
beg := length + beg_index - ss_length + 2;
beg := beg - 1;
END LOOP;
+ RETURN 0;
+ ELSE
RETURN 0;
END IF;
END;
projects / postgresql / blobdiff