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


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

Re: Exception handling from trigger

> 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.



---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Re: Exception handling from trigger

Vlad Dimitriu 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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Re: Exception handling from trigger

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 ....

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: Exception handling from trigger

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 ....


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings