</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:
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.
- Colon-equal (<literal>:=</>) can be used instead of SQL-standard
- <literal>=</> for <command>GET DIAGNOSTICS</>.
- </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
<application>PL/pgSQL</> has three forms of <command>IF</>:
<itemizedlist>
<listitem>
- <para><literal>IF ... THEN</></>
+ <para><literal>IF ... THEN ... END IF</></>
</listitem>
<listitem>
- <para><literal>IF ... THEN ... ELSE</></>
+ <para><literal>IF ... THEN ... ELSE ... END IF</></>
</listitem>
<listitem>
- <para><literal>IF ... THEN ... ELSIF ... THEN ... ELSE</></>
+ <para><literal>IF ... THEN ... ELSIF ... THEN ... ELSE ... END IF</></>
</listitem>
</itemizedlist>
<para>
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);
</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>text</entry>
+ <entry><type>text</></entry>
<entry>the name of the column related to exception</entry>
</row>
<row>
<entry><literal>CONSTRAINT_NAME</literal></entry>
- <entry>text</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>text</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>text</entry>
+ <entry><type>text</></entry>
<entry>the name of the table related to exception</entry>
</row>
<row>
<entry><literal>SCHEMA_NAME</literal></entry>
- <entry>text</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>
</sect3>
</sect2>
- <sect2 id="plpgsql-get-diagnostics-context">
- <title>Obtaining Current Execution Information</title>
-
- <para>
- The <command>GET <optional> CURRENT </optional> DIAGNOSTICS</command>
- command 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).
- This command has the form:
- </para>
-
-<synopsis>
-GET <optional> CURRENT </optional> DIAGNOSTICS <replaceable>variable</replaceable> { = | := } <replaceable>item</replaceable> <optional> , ... </optional>;
-</synopsis>
+ <sect2 id="plpgsql-call-stack">
+ <title>Obtaining Execution Location Information</title>
<para>
- Currently only one information item is supported. Status
- item <literal>PG_CONTEXT</> will return 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>
+ 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>
</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>
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 <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
+ 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>
Testing of assertions can be enabled or disabled via the configuration
- parameter <literal>plpgsql.check_asserts</>, which takes a boolean
+ 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>
<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>
- <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;
<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>
<para>
To aid the user in finding instances of simple but common problems before
- they cause harm, <application>PL/PgSQL</> provides additional
+ 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
</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 ||
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;
projects / postgresql / blobdiff