<para>
The general syntax of a variable declaration is:
<synopsis>
-<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>;
+<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 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>
<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:
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>
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
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
</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 <optional> CURRENT </optional> 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 status
- 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
</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>
+
<note>
<para>
The current implementation of <command>RETURN NEXT</command>
<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>
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);
</example>
<sect3 id="plpgsql-exception-diagnostics">
- <title>Obtaining information about an error</title>
+ <title>Obtaining Information About an Error</title>
<para>
Exception handlers frequently need to identify the specific error that
<command>GET STACKED DIAGNOSTICS</command> command, which has the form:
<synopsis>
-GET STACKED DIAGNOSTICS <replaceable>variable</replaceable> = <replaceable>item</replaceable> <optional> , ... </optional>;
+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 variable (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">.
+ 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 values</title>
+ <title>Error Diagnostics Items</title>
<tgroup cols="3">
<thead>
<row>
<tbody>
<row>
<entry><literal>RETURNED_SQLSTATE</literal></entry>
- <entry>text</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>text</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>text</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>text</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>text</entry>
- <entry>line(s) of text describing the call stack</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>
</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>
<sect1 id="plpgsql-cursors">
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>
<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>
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>
</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">
<secondary>in PL/pgSQL</secondary>
</indexterm>
+ <para>
+ <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>
+ <title>Triggers on Data Changes</title>
- <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.
+ <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>
<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;
</sect2>
<sect2 id="plpgsql-event-trigger">
- <title>Triggers on events</title>
+ <title>Triggers on Events</title>
<para>
- <application>PL/pgSQL</application> can be used to define event
- triggers. <productname>PostgreSQL</> requires that a procedure that
+ <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 a
+ 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:
</para>
<para>
- <xref linkend="plpgsql-event-trigger-example"> shows an example of a
+ <xref linkend="plpgsql-event-trigger-example"> shows an example of an
event trigger procedure in <application>PL/pgSQL</application>.
</para>
</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
projects / postgresql / blobdiff