By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,901 Members | 1,436 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,901 IT Pros & Developers. It's quick & easy.

Trigger function aborts the transaction on exception

P: n/a
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 helpme 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 typecast of char ---> numeric (e.g if it try to insert alphanumeric characters in the numeric datatype ) 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.

Nov 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On Sat, Feb 28, 2004 at 03:21:25PM +0530, Rajat Katyal wrote:
insert into target_table select * from source_table;

Now there could be scenario that in some record error occured due to
the typecast of char ---> numeric (e.g if it try to insert
alphanumeric characters in the numeric datatype ) 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.
Add a where condition to only add lines that are numbers. For example:

insert into target_table select * from source_table where field ~ '^[0-9.]+$';

Hope this helps,
--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/ If the Catholic church can survive the printing press, science fiction
will certainly weather the advent of bookwarez.
http://craphound.com/ebooksneitherenorbooks.txt - Cory Doctorow


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFAQGzYY5Twig3Ge+YRAu5YAKCXqHUdkcA5QBJr5cX5nB RZumcuPgCZAQGf
i11wyDRhf/bodZzFGDmGEdk=
=WsuM
-----END PGP SIGNATURE-----

Nov 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.