pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedException handling from trigger
5+ messages / 3 participants
[nested] [flat]
* Exception handling from trigger
@ 2004-08-09 16:47 Vlad Dimitriu <vlad.dimitriu@base.ro>
0 siblings, 2 replies; 5+ messages in thread
From: Vlad Dimitriu @ 2004-08-09 16:47 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Exception handling from trigger
@ 2004-08-09 16:52 Rod Taylor <pg@rbt.ca>
parent: Vlad Dimitriu <vlad.dimitriu@base.ro>
1 sibling, 0 replies; 5+ messages in thread
From: Rod Taylor @ 2004-08-09 16:52 UTC (permalink / raw)
To: Vlad Dimitriu <vlad.dimitriu@base.ro>; +Cc: pgsql-sql
> 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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Exception handling from trigger
@ 2004-08-09 17:14 Tom Lane <tgl@sss.pgh.pa.us>
parent: Vlad Dimitriu <vlad.dimitriu@base.ro>
1 sibling, 2 replies; 5+ messages in thread
From: Tom Lane @ 2004-08-09 17:14 UTC (permalink / raw)
To: Vlad Dimitriu <vlad.dimitriu@base.ro>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Exception handling from trigger
@ 2004-08-09 17:38 Vlad Dimitriu <vlad.dimitriu@base.ro>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Vlad Dimitriu @ 2004-08-09 17:38 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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 ....
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Exception handling from trigger
@ 2004-08-09 17:48 Vlad Dimitriu <vlad.dimitriu@base.ro>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Vlad Dimitriu @ 2004-08-09 17:48 UTC (permalink / raw)
To: pgsql-sql
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 ....
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2004-08-09 17:48 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-09 16:47 Exception handling from trigger Vlad Dimitriu <vlad.dimitriu@base.ro>
2004-08-09 16:52 ` Rod Taylor <pg@rbt.ca>
2004-08-09 17:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-08-09 17:38 ` Vlad Dimitriu <vlad.dimitriu@base.ro>
2004-08-09 17:48 ` Vlad Dimitriu <vlad.dimitriu@base.ro>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox