Thread: Exception handling from trigger

Hello,

I would like to handle the exceptions that a database returns. For example,
if a "mycolumn" column is defined as varchar(64), I'd like to catch
if the new.mycolumn is larger than 64 with my own trigger
(for cutoms, internationalized messages, etc ..). So I did,
but the database catch this error before my 'before insert'-trigger
(using character_length(new.mycolumn) ...).

Is this way of handling exceptions possible in postgres ?
If so, what is the normal way to handle this exceptions, from a
plpgsql/trigger(rule??) perspective ?


Thank you ...


Vlad Dimitriu

> Is this way of handling exceptions possible in postgres ?
> If so, what is the normal way to handle this exceptions, from a
> plpgsql/trigger(rule??) perspective ?

8.0 should allow you to do this. 7.4 you need to perform your own checks
and catch whether they succeed or fail.

Vlad Dimitriu <vlad.dimitriu@base.ro> writes:
> I would like to handle the exceptions that a database returns. For example,
> if a "mycolumn" column is defined as varchar(64), I'd like to catch
> if the new.mycolumn is larger than 64 with my own trigger

You can't.  From a logical perspective this is sensible, because the
trigger is handed data already formed into a tuple.  If the presented
tuple contained a mycolumn value wider than 64 characters then it would
not be a legal value of the rowtype (any more than if, say, the column
value were an integer and not a varchar at all).

What I'd suggest if you want this is to remove the datatype restriction
and instead rely on your trigger to enforce the limitation.
        regards, tom lane

Tom Lane wrote:

>You can't.  From a logical perspective this is sensible, because the
>trigger is handed data already formed into a tuple.  If the presented
>tuple contained a mycolumn value wider than 64 characters then it would
>not be a legal value of the rowtype (any more than if, say, the column
>value were an integer and not a varchar at all).
>
>What I'd suggest if you want this is to remove the datatype restriction
>and instead rely on your trigger to enforce the limitation.
>
>  
>
I just realised that. I would design those columns as text
and enforce value length restrictions with triggers.

It's a false problem:  I define the data type for a field and
want to check his value after the exception that I can't fit
larger data into my field.

Thank you, Rod, Tom ....

I would design those columns as text
and enforce value length restrictions with triggers.

It's a false problem:  I define the data type for a field and
want to check his value after the exception that I can't fit
larger data into my field.

Thank you, Rod, Tom ....