by this <literal>EXCEPTION</literal> clause, but is propagated out.
     A surrounding <literal>EXCEPTION</literal> clause could catch it.
    </para>

    <para>
     When an error is caught by an <literal>EXCEPTION</literal> clause,
     the local variables of the <application>PL/pgSQL</application> function
     remain as they were when the error occurred, but all changes
     to persistent database state within the block are rolled back.
     As an example, consider this fragment:

<programlisting>
INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
BEGIN
    UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
    x := x + 1;
    y := x / 0;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'caught division_by_zero';
        RETURN x;
END;
</programlisting>

     When control reaches the assignment to <literal>y</literal>, it will
     fail with a <literal>division_by_zero</literal> error.  This will be caught by
     the <literal>EXCEPTION</literal> clause.  The value returned in the
     <command>RETURN</command> statement will be the incremented value of
     <literal>x</literal>, but the effects of the <command>UPDATE</command> command will
     have been rolled back.  The <command>INSERT</command> command preceding the
     block is not rolled back, however, so the end result is that the database
     contains <literal>Tom Jones</literal> not <literal>Joe Jones</literal>.
    </para>

    <tip>
     <para>
      A block containing an <literal>EXCEPTION</literal> clause is significantly
      more expensive to enter and exit than a block without one.  Therefore,
      don't use <literal>EXCEPTION</literal> without need.
     </para>
    </tip>

    <example id="plpgsql-upsert-example">
    <title>Exceptions with <command>UPDATE</command>/<command>INSERT</command></title>
    <para>

    This example uses exception handling to perform either
    <command>UPDATE</command> or <command>INSERT</command>, as appropriate.  It is
    recommended that applications use <command>INSERT</command> with
    <literal>ON CONFLICT DO UPDATE</literal> 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);

CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
    LOOP
        -- first try to update the key
        UPDATE db SET b = data WHERE a = key;
        IF found THEN
            RETURN;
        END IF;
        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently,
        -- we could get a unique-key failure
        BEGIN
            INSERT INTO db(a,b) VALUES (key, data);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- Do nothing, and loop to try the UPDATE again.
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

SELECT merge_db(1, 'david');
SELECT merge_db(1, 'dennis');
</programlisting>

     This coding assumes the <literal>unique_violation</literal> error is caused by
     the <command>INSERT</command>, and not by, say, an <command>INSERT</command> 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</application>: special variables and the
     <command>GET STACKED DIAGNOSTICS</command> command.
    </para>

    <para>
     Within