2012. 11. 21. 18:25ㆍDataBase/Postgresql
음..anyways...찾아보니 RAISE 라는 것과 Exception이라는 것이 있는 것 같다.
우선 EXCEPTION 을 학습하고 적용시켜 보자^-^
By default, any error occurring in a PL/pgSQL function aborts execution of the function, and indeed of the surrounding transaction as well. You can trap errors and recover from them by using a BEGIN block with an EXCEPTION clause. The syntax is an extension of the normal syntax for a BEGIN block:
[ <<label>> ] [ DECLARE declarations ] BEGIN statements EXCEPTION WHEN condition [ OR condition ... ] THEN handler_statements [ WHEN condition [ OR condition ... ] THEN handler_statements ... ] END;예제 1] When an error is caught by an EXCEPTION clause, the local variables of the PL/pgSQL function remain as they were when the error occurred, but all changes to persistent database state within the block are rolled back. As an example, consider this fragment:
INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones'); BEGIN UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones'; x := x + 1; y := x / 0; EXCEPTION WHEN division_by_zero THEN RAISE NOTICE 'caught division_by_zero'; RETURN x; END;
예제2] This example uses exception handling to perform either UPDATE or INSERT, as appropriate.
CREATE TABLE db (a INT PRIMARY KEY, b TEXT); CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS $$ BEGIN LOOP UPDATE db SET b = data WHERE a = key; IF found THEN RETURN; END IF; BEGIN INSERT INTO db(a,b) VALUES (key, data); RETURN; EXCEPTION WHEN unique_violation THEN -- do nothing END; END LOOP; END; $$ LANGUAGE plpgsql; SELECT merge_db(1, 'david'); SELECT merge_db(1, 'dennis');
Handling Errors and Exceptions
RAISE statements raise errors and exceptions during a
PL/pgSQL function's operation. A RAISE statement sends
specified information to the PostgreSQL elog mechanism
(the standard PostgreSQL error logging utility, which typically logs data either
to /var/log/messages , or to $PGDATA/serverlog , as well as displaying to stderr ).
A RAISE statement is also given the level of error it should raise, and the string it should send to PostgreSQL. Additionally, you can list variables and expressions whose values you wish to have placed into the string. Use percent signs (%) to mark the locations in the string at which you want those values inserted. The syntax of the RAISE statement is as follows:Table 11-1 lists the three possible values for the RAISE statement's level and their meanings.
Table 11-1. Possible level values
DEBUG level statements send the specified text as a DEBUG: message to the PostgreSQL log and the client program if the client is connected to a database cluster running in debug mode. DEBUG level RAISE statements will be ignored by a database running in production mode.
NOTICE level statements send the specified text as a NOTICE: message to the PostgreSQL log and the client program in any PostgreSQL operation mode.
EXCEPTION level statements send the specified text as an ERROR: message to the client program and the PostgreSQL database log. The EXCEPTION level also causes the current transaction to be aborted.
우선..더 학습이 필요할 것 같다..ㅋㅋ
퇴근 시간이니.. 집으로 =3=3=3;;
눈이 너무 피로하다..ㅠ_ㅠ..
'DataBase > Postgresql' 카테고리의 다른 글
|windows postgreSQL autocommit off 만들기 (0)||2013.05.25|
|postgreSQL clob 처리 (2)||2013.05.13|
|postgreSQL 외부접근, DB 공유 (2)||2013.03.22|
|Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections (0)||2013.01.16|
|postgre SQL : 예외처리 (0)||2012.11.21|
|postgresql 학습하기 (0)||2012.11.21|