안녕하세요, 김일형입니다.

오늘 어떤 분께서 PL/pgSQL함수 내에서 insert를 하였을 경우 RESULT_OID에 대한 사용법을 알려달라고 메일을 주셨습니다.

제가 원래 허접이라 조금 테스트 프로그램을 만들어 보았는데요...

도움이 되었기를 바랍니다.

아래의 코드는 test라고 하는 integer 칼럼하나를 달랑가지는 간단한 테이블이구요,

(1)번 쿼리는 적법한 쿼리이고 (2)은 그 삽입된 행의 OID를 가져오는 코드입니다.

(3)은 잘못된 쿼리입니다.

코드를 봐주세요...

-----------------------------------------------------------------------------

DROP FUNCTION test_func();

CREATE FUNCTION test_func() RETURNS integer AS '

DECLARE

objid INTEGER := -1;

BEGIN

RAISE NOTICE ''Before insertion: %'', objid;

INSERT INTO test VALUES ( 10 ); -- (1)적법한 쿼리

GET DIAGNOSTICS objid = RESULT_OID; -- (2) 삽입된 행의 OID를 가져오기

RAISE NOTICE ''After insertion of correct query: %'', objid;

INSERT INTO test VALUES ( 10, 2 ); -- (3) 틀린 쿼리

GET DIAGNOSTICS objid = RESULT_OID; -- (4) 삽입된 행의 OID를 가져오기

RAISE NOTICE ''After insertion of wrong query: %'', objid;

RETURN objid;

END;

' LANGUAGE 'plpgsql';

-----------------------------------------------------------------------------

그리고 이제는 이것을 실행한 화면입니다.

=> select test_func();

NOTICE: Before insertion: -1

NOTICE: After insertion of correct query: 19786

ERROR: INSERT has more expressions than target columns

RAISE NOTICE ''After insertion of wrong query: %'', objid;

RETURN objid;

END;

' LANGUAGE 'plpgsql';

=> select * from test;

id

----

(0 rows)

=>

아시다시피 첫_째 행은 함수를 실행시키는 쿼리문이구요...

다음 행은 insert 하기전에는 objid가 초기값인 -1을 가지고 있는 것이 보이시죠?

그 다음행은 insert 하고 난 후의 objid 변수의 값입니다. 제대로 값을 가지고 왔습니다.

그 다음 행의 내용은 insert문장이 잘못되어서 에러가 발생한 경우입니다. 그리고 프로세스가 여기서 중단이 되고 함수 밖으로 빠져나왔습니다. 그러니까 (4)번 문장 이후로는 실행이 되지 않은 것입니다.

재미있는 것은 (1)번 문장은 제대로 실행이 되었는데도, 함수를 실행하고난 이후 테이블에는 아무것도 없습니다.

따라서 이것은 메뉴얼에서 설명한 것과 마찬가지로 에러가 발행하면 함수 내에서 실행 했던 쿼리는 rollback이 되고 리턴된다는 것을 보여주고 있습니다.

도움이 되었기를 바랍니다.

김일형.