Обсуждение: Can I User @@error of MS SQL in postgresql
I have a plpgsql function to insert a record into a table. In MS SQL, I can user @@error to check the insert action is completedor not. What can I do in postgresql? Thanks a lot.
PostgreSQL is much more strict about error handling. MSSQLServer will continue merrily along without interrupting the procedureor affecting the in-progress transaction if errors occur. It is up to you to catch them and decide how to handlethem. PGSQL will ignore all further queries and roll back the transaction automatically if any errors are returnedfrom a query. In other words, you don't have to worry about it. If there are cases where the action failing wouldbe OK, you need to test for those cases (i.e. duplicate key already exists) before the action. Keep in mind that thosechecks will not always be valid given the status of other transactions and your isolation level. Isn't this fun? Ian Harding Programmer/Analyst II Tacoma-Pierce County Health Department iharding@tpchd.org (253) 798-3549 >>> "annachau" <annachau@hongkong.com> 12/05/02 11:13PM >>> I have a plpgsql function to insert a record into a table. In MS SQL, I can user @@error to check the insert action is completedor not. What can I do in postgresql? Thanks a lot. ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly
On Fri, Dec 06, 2002 at 06:52:14AM -0800, Ian Harding wrote: > PostgreSQL is much more strict about error handling. MSSQLServer will > continue merrily along without interrupting the procedure or affecting > the in-progress transaction if errors occur. It is up to you to catch > them and decide how to handle them. PGSQL will ignore all further > queries and roll back the transaction automatically if any errors are > returned from a query. In other words, you don't have to worry about > it. If there are cases where the action failing would be OK, you need > to test for those cases (i.e. duplicate key already exists) before the > action. Keep in mind that those checks will not always be valid given > the status of other transactions and your isolation level. Given that some ODBC apps don't fully expose the PG error message (Access, for instance, can either show you a dialog box w/the PG error message, or give you a generic error code that you app can act on, but not both), it would would be helpful to be able to figure out what error happened. Does anyone know of anything like "SELECT last_error" to find the last error on a connection? This way, in an Access error-handling function, I could at least try to dig in and get the PG error message, parse it, and provide some meaningful help to the user. - J. -- Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton Independent Knowledge Management Consultant