Postgres Pro
Downloads
Home   >   mailing lists

Thread: Trigger function aborts the transaction on exception

Trigger function aborts the transaction on exception

From
"Rajat Katyal"
Date:
Hi:
I came to know from the postgresql documentation that PostgreSQL does not have a very smart exception handling model. I wonder whether anyone can help me or suggest some alternate solution for the problem stated below:
 
I have a trigger function defined for the insert and iam executing sql statement as:
 
insert into target_table select * from source_table;
 
Now there could be scenario that in some record error occured due to the type cast  therefore i want to catch the execption and want to make it entry in some other table but my process of insertion for the other records should go on as they have valid records. But it throws the exception and aborts the transaction.
 
Please help me and suggest some solution for this problem.
 
Thanks in advance.
 
Rajat.

Re: Trigger function aborts the transaction on exception

From
Kris Jurka
Date:

On Sat, 31 Jan 2004, Rajat Katyal wrote:

> Hi: I came to know from the postgresql documentation that PostgreSQL
> does not have a very smart exception handling model. I wonder whether
> anyone can help me or suggest some alternate solution for the problem
> stated below:
>
> I have a trigger function defined for the insert and iam executing sql
> statement as:
>
> insert into target_table select * from source_table;
>
> Now there could be scenario that in some record error occured due to
> the type cast therefore i want to catch the execption and want to make
> it entry in some other table but my process of insertion for the other
> records should go on as they have valid records. But it throws the
> exception and aborts the transaction.
>

This list is for JDBC related questions.  Your question is more
appropriate for pgsql-general@postgresql.org.   That said, with a BEFORE
INSERT trigger you may return NULL instead of NEW to indicate that the row
should not be inserted.

Kris Jurka