postgre SQL : 예외처리

예외처리? 오류처리?

음..anyways...찾아보니 RAISE 라는 것과 Exception이라는 것이 있는 것 같다.
우선 EXCEPTION 을 학습하고 적용시켜 보자^-^


http://www.postgresql.org/docs/8.0/static/plpgsql-control-structures.html


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

Value	Explanation
DEBUG	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	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	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.

http://www.linuxtopia.org/online_books/database_guides/Practical_PostgreSQL_database/PostgreSQL_x20238_003.htm

에러코드
http://www.postgresql.org/docs/9.1/static/errcodes-appendix.html

우선..더 학습이 필요할 것 같다..ㅋㅋ
퇴근 시간이니.. 집으로 =3=3=3;;
눈이 너무 피로하다..ㅠ_ㅠ..