Search Postgresql Archives

Re: After insert trigger question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Wed, Apr 27, 2005 at 05:24:16PM +0200, Stephane Bortzmeyer wrote:
> On Wed, Apr 27, 2005 at 08:45:44AM -0600,
>  mmiranda@xxxxxxxxxxxxxxxxx <mmiranda@xxxxxxxxxxxxxxxxx> wrote 
> > 
> > I am concerned about how reliable is an before insert trigger, i
> > made some computation in my trigger and i want that no matter what
> > happens inside the trigger (exceptions, erros, divide by zero, etc)
> > , the row must be inserted,
> 
> I do not think that pl/pgsql has exception handlers
> (http://www.postgresql.org/docs/7.4/interactive/plpgsql-errors-and-messages.html).

PostgreSQL 8.0 introduced PL/pgSQL exception handlers.

http://www.postgresql.org/docs/8.0/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

Regardless of whether the trigger is BEFORE or AFTER, an untrapped
error will abort the insert.

CREATE FUNCTION trigfunc() RETURNS trigger AS '
DECLARE
    i  integer;
BEGIN
    i := NEW.x / 0;
    RETURN NULL;
END;
' LANGUAGE plpgsql;

CREATE TABLE foo (x integer);

CREATE TRIGGER footrig_after AFTER INSERT ON foo
  FOR EACH ROW EXECUTE PROCEDURE trigfunc();

INSERT INTO foo VALUES (123);
ERROR:  division by zero
CONTEXT:  PL/pgSQL function "trigfunc" line 4 at assignment

SELECT * FROM foo;
 x 
---
(0 rows)

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux