<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>
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>;
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
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
<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>
<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
<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);
+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 more efficient, because the parameters
- <literal>newvalue</literal> and <literal>keyvalue</literal> are not
- converted to text.
+ 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>
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
</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>
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>
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>
-- Now "mviews" has one record from cs_materialized_views
RAISE NOTICE 'Refreshing materialized view %s ...', quote_ident(mviews.mv_name);
- EXECUTE 'TRUNCATE TABLE ' || quote_ident(mviews.mv_name);
- EXECUTE 'INSERT INTO '
- || quote_ident(mviews.mv_name) || ' '
- || mviews.mv_query;
+ EXECUTE format('TRUNCATE TABLE %I', mviews.mv_name);
+ EXECUTE format('INSERT INTO %I %s', mviews.mv_name, mviews.mv_query);
END LOOP;
RAISE NOTICE 'Done refreshing materialized views.';
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>
</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);
SELECT merge_db(1, 'david');
SELECT merge_db(1, 'dennis');
</programlisting>
- This example assumes the <literal>unique_violation</> error is caused by
- the <command>INSERT</>, and not by an <command>INSERT</> trigger function
- on the table.
+
+ 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
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. As with <command>EXECUTE</command>, parameter values
- can be inserted into the dynamic command via <literal>USING</>.
+ 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(tabname)
- || ' WHERE col1 = $1' USING keyvalue;
+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 textually,
- so use of <function>quote_ident()</> is recommended to guard against
- SQL injection. The comparison value for <literal>col1</> is inserted
- via a <literal>USING</> parameter, so it needs no quoting.
+ 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>
<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>
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>
and hint:
<programlisting>
RAISE EXCEPTION 'Nonexistent ID --> %', user_id
- USING HINT = 'Please check your user id';
+ USING HINT = 'Please check your user ID';
</programlisting>
</para>
</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>
<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).
- For <command>DELETE</> operations, the return value should be
- <varname>OLD</>.
+ (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>
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;
<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 emp_audit table. The current time
+ 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>
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>
<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>
</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 ||
<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">
+ 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;
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;
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