반응형
24.2.7. RECORD를 이용한 작업
RECORD는 간단한 데이터베이스 행의 자료형 (rowtype) 이긴 하지만 사전에 구조가 정의 되어있지 않는다. 이는 선택(selection)과 FOR 반복문에서 SELECT 작업을 통해 실제 데이터베이스의 한 행의 값을 갖는다.
24.2.7.1. 선언
RECORD 형의 변수는 다른 선택(selection)에 사용될 수 있다. 한 레코드를 다루거나 값을 레코드 필드로 대입을 하려고 할 때 만일 실제 행(row)이 없다면 실행시간 오류를 야기한다. 변수선언은 다음과 같이 한다.
변수이름 RECORD;
24.2.7.2. 값의 할당.
RECORD 또는 Row으로 완전한 선택(selection)의 할당은 다음과 같이 해서 이루어진다.
SELECT INTO 대입받을_변수 표현식 FROM ...;
'대입받을_변수'는 RECORD 또는 ROW 형의 변수이거나 쉼표 ','를 사용하여 구분된 레코드/행의 필드와 변수의 목록이다. 위의 것은 Postgres가 보통 수행하는 SELECT INTO와는 완전히 다른 것이다. Postgres가 보통 수행하는 SELECT INTO에서는 SELECT결과를 이용하여 새로운 테이블을 만들 때 사용하는 것이다. (만일 PL/pgSQL에서 SELECT의 결과를 이용하여 새로이 테이블을 만들려면 CREATE TABLE AS SELECT를 사용해야 한다.)
만약에 row또는 변수목록(','로 구분된)을 '대입받을_변수'로 사용하고자 한다면 선택된 값들은 반드시 "대입받을_변수'와 구조가 일치되어야 한다. 그렇지 않으면 실행시간 오류가 발생한다. FROM 키워드 다음에는 조건, 그룹, 정렬 같은 것들이 따라올 수 있는데 이것들은 SELECT문에도 쓸 수 있는 것들이다.
레코드 또는 행의 값이 RECORD 변수로 할당이 되면 마침표 '.'를 사용하여 해당 필드로 접근할 수 있다.
DECLARE
users_rec RECORD;
full_name varchar;
BEGIN
SELECT INTO users_rec * FROM users WHERE user_id=3;
full_name := users_rec.first_name || '' '' || users_rec.last_name;
FOUND라고 하는 boolean형의 특별한 변수가 있는데, 이 것은 SELECT INTO 바로 다음에 사용되어 할당이 성공적이었는지를 확인 할 수 있다.
SELECT INTO myrec * FROM EMP WHERE empname = myname;
IF NOT FOUND THEN
RAISE EXCEPTION ''employee % not found'', myname;
END IF;
또한 IS NULL (또는 ISNULL)을 사용하여 RECORD/ROW가 NULL인지 여부를 검사할 수 있다. 만일 선택이 복수개의 행을 반환하였다면 첫번째만이 '대입받을_변수'에 옮겨진다. 나머지 행들은 누락되어져 버린다.
DECLARE
users_rec RECORD;
full_name varchar;
BEGIN
SELECT INTO users_rec * FROM users WHERE user_id=3;
IF users_rec.homepage IS NULL THEN
// 사용자가 없는 홈페이지에 들어왔다, "http://"를 반환한다.
return ''http://'';
END IF;
END;
24.2.7.3. Record를 통한 반복
특별한 FOR 반복문을 통하여 사용자는 쿼리의 결과를 통해 반복을 할 수 있고, 데이터를 사용할 수 있다. 다음은 그 문법이다.
[<<label>>]
FOR 레코드 | 행 IN select문장 LOOP
실행할 문장
END LOOP;
레코드 또는 행은 'select문장'으로부터 모든 반환된 행을 할당받고 반복문의 문장은 각각의 행을 처리한다. 다음은 그 예이다.
create function cs_refresh_mviews () returns integer as '
DECLARE
mviews RECORD;
// 위의 선언 대신에...
// mviews cs_materialized_views%ROWTYPE;
// 이렇게 했다면 이 변수는 오로지 cs_materialized_views 테이블에서만
// 사용할 수 있다.
BEGIN
PERFORM cs_log(''Refreshing materialized views...'');
FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP
// 이제 "mviews" 는 cs_materialized_views의 한 레코드를 받았다.
PERFORM cs_log(''Refreshing materialized view '' || mview.mv_name || ''...'');
TRUNCATE TABLE mview.mv_name;
INSERT INTO mview.mv_name || '' '' || mview.mv_query;
END LOOP;
PERFORM cs_log(''Done refreshing materialized views.'');
return 1;
end;
' language 'plpgsql';
만일 위의 반복문이 EXIT으로 빠져나왔다면 마지막에 할당받은 행은 반복문 밖에서도 여전히 사용가능하다.
FOR/IN EXECUTE 문은 결과 레코드 사이를 반복할 수 있는 다른 방법이다.
[<<label>>]
FOR 레코드 | 행 IN EXECUTE 표현식을_가진_문자열 LOOP
실행할 문장
END LOOP;
이의 형태는 앞의 예와 비슷한데, 이 경우에는 SELECT문이 표현식을 가지는 문자열로 대체되었다. 이것은 FOR문에 진입할 때마다 평가되고 해석된다. 여기서 한가지의 선택을 해야하는데 미리 해석된 쿼리를 통한 나은 성능을 택하느냐 아니면 일반적인 EXECUTE문과 같이 동적 쿼리의 유연성을 택하느냐이다.
24.2.8. 처리 중단과 메세지
RAISE문을 사용하여 Postgres elog 매커니즘으로 출력 메세지를 보낼 수 있다.
RAISE 출력수준 '출력형식' [, identifier [...]];
출력형식에서 뒤 따라오는 identifier의 출력형식을 정할 수 있는 %를 사용할 수 있다. '출력수준'으로 쓸 수 있는 키워드는 다음의 3가지이다.
DEBUG / 제품수준 환경의 데이터베이스에서 사용되는 조용한 수준의 메세지
NOTICE / 데이터베이스 log에 기록이 되고 클라이언트로 보내지는 수준의 메세지
EXCEPTIOM / 데이터베이스 log에 기록이 되고 트랜젝션을 중단하는 수준의 메세지
RAISE NOTICE ''Id number '' || key || '' not found!'';
RAISE NOTICE ''Calling cs_create_job(%)'',v_job_id;
바로 위의 2번째 예에서 v_job_id은 %의 자리에 문자열로 출력된다.
RAISE EXCEPTION ''Inexistent ID //> %'',user_id;
위의 예는 트랜젝션을 중단하고 데이터베이스 log에 기록한다.
24.2.9. 예외
Postgres는 그리 똑똑한 예외처리 모델을 가지고 있지 않다. 분석기(parser), 번역기/최적화 (planner/optimizer) 또는 실행기(executor)에서 더이상 요청된 처리를 수행하지 못할 경우 모든 트랜젝션은 중단되고 main loop로 건너뛰고나서 클라이언트 프로그램에서 다음 쿼리를 수행한다.
이같은 상황을 알리기 위한 메세지를 오류 메커니즘으로 넣는 것이 가능하다. 그러나 현재로는 왜 그러한 상황이 발생하였는지 아는 것은 불가능하다. (입/출력 변환 오류, floating point오류, parse 오류 등등). 그래서 데이터베이스 처리가 오류의 시점에서 자료의 불일치가 발생할 수 있기 때문에 상위 처리부로 귀환한다거나 계속된 명령의 처리는 전체 데이터베이스를 망가뜨릴 수 있다. 그렇기 때문에 처리가 중단이 됐고 이의 사실이 클라이언트에 알려진다 하더라도 계속되는 처리는 무의미하다.
그렇기 때문에 PL/pgSQL이 현재 할 수 있는 것은 함수나 trigger procedure가 처리되는 과정에서 중단(abort)되면 어떤 함수의 어디서 (line number와 문장의 종류) 발생했는지 추가적인 DEBUG lebel의 로그 메세지를 기록해주는 것이 다이다.
RECORD는 간단한 데이터베이스 행의 자료형 (rowtype) 이긴 하지만 사전에 구조가 정의 되어있지 않는다. 이는 선택(selection)과 FOR 반복문에서 SELECT 작업을 통해 실제 데이터베이스의 한 행의 값을 갖는다.
24.2.7.1. 선언
RECORD 형의 변수는 다른 선택(selection)에 사용될 수 있다. 한 레코드를 다루거나 값을 레코드 필드로 대입을 하려고 할 때 만일 실제 행(row)이 없다면 실행시간 오류를 야기한다. 변수선언은 다음과 같이 한다.
변수이름 RECORD;
24.2.7.2. 값의 할당.
RECORD 또는 Row으로 완전한 선택(selection)의 할당은 다음과 같이 해서 이루어진다.
SELECT INTO 대입받을_변수 표현식 FROM ...;
'대입받을_변수'는 RECORD 또는 ROW 형의 변수이거나 쉼표 ','를 사용하여 구분된 레코드/행의 필드와 변수의 목록이다. 위의 것은 Postgres가 보통 수행하는 SELECT INTO와는 완전히 다른 것이다. Postgres가 보통 수행하는 SELECT INTO에서는 SELECT결과를 이용하여 새로운 테이블을 만들 때 사용하는 것이다. (만일 PL/pgSQL에서 SELECT의 결과를 이용하여 새로이 테이블을 만들려면 CREATE TABLE AS SELECT를 사용해야 한다.)
만약에 row또는 변수목록(','로 구분된)을 '대입받을_변수'로 사용하고자 한다면 선택된 값들은 반드시 "대입받을_변수'와 구조가 일치되어야 한다. 그렇지 않으면 실행시간 오류가 발생한다. FROM 키워드 다음에는 조건, 그룹, 정렬 같은 것들이 따라올 수 있는데 이것들은 SELECT문에도 쓸 수 있는 것들이다.
레코드 또는 행의 값이 RECORD 변수로 할당이 되면 마침표 '.'를 사용하여 해당 필드로 접근할 수 있다.
DECLARE
users_rec RECORD;
full_name varchar;
BEGIN
SELECT INTO users_rec * FROM users WHERE user_id=3;
full_name := users_rec.first_name || '' '' || users_rec.last_name;
FOUND라고 하는 boolean형의 특별한 변수가 있는데, 이 것은 SELECT INTO 바로 다음에 사용되어 할당이 성공적이었는지를 확인 할 수 있다.
SELECT INTO myrec * FROM EMP WHERE empname = myname;
IF NOT FOUND THEN
RAISE EXCEPTION ''employee % not found'', myname;
END IF;
또한 IS NULL (또는 ISNULL)을 사용하여 RECORD/ROW가 NULL인지 여부를 검사할 수 있다. 만일 선택이 복수개의 행을 반환하였다면 첫번째만이 '대입받을_변수'에 옮겨진다. 나머지 행들은 누락되어져 버린다.
DECLARE
users_rec RECORD;
full_name varchar;
BEGIN
SELECT INTO users_rec * FROM users WHERE user_id=3;
IF users_rec.homepage IS NULL THEN
// 사용자가 없는 홈페이지에 들어왔다, "http://"를 반환한다.
return ''http://'';
END IF;
END;
24.2.7.3. Record를 통한 반복
특별한 FOR 반복문을 통하여 사용자는 쿼리의 결과를 통해 반복을 할 수 있고, 데이터를 사용할 수 있다. 다음은 그 문법이다.
[<<label>>]
FOR 레코드 | 행 IN select문장 LOOP
실행할 문장
END LOOP;
레코드 또는 행은 'select문장'으로부터 모든 반환된 행을 할당받고 반복문의 문장은 각각의 행을 처리한다. 다음은 그 예이다.
create function cs_refresh_mviews () returns integer as '
DECLARE
mviews RECORD;
// 위의 선언 대신에...
// mviews cs_materialized_views%ROWTYPE;
// 이렇게 했다면 이 변수는 오로지 cs_materialized_views 테이블에서만
// 사용할 수 있다.
BEGIN
PERFORM cs_log(''Refreshing materialized views...'');
FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP
// 이제 "mviews" 는 cs_materialized_views의 한 레코드를 받았다.
PERFORM cs_log(''Refreshing materialized view '' || mview.mv_name || ''...'');
TRUNCATE TABLE mview.mv_name;
INSERT INTO mview.mv_name || '' '' || mview.mv_query;
END LOOP;
PERFORM cs_log(''Done refreshing materialized views.'');
return 1;
end;
' language 'plpgsql';
만일 위의 반복문이 EXIT으로 빠져나왔다면 마지막에 할당받은 행은 반복문 밖에서도 여전히 사용가능하다.
FOR/IN EXECUTE 문은 결과 레코드 사이를 반복할 수 있는 다른 방법이다.
[<<label>>]
FOR 레코드 | 행 IN EXECUTE 표현식을_가진_문자열 LOOP
실행할 문장
END LOOP;
이의 형태는 앞의 예와 비슷한데, 이 경우에는 SELECT문이 표현식을 가지는 문자열로 대체되었다. 이것은 FOR문에 진입할 때마다 평가되고 해석된다. 여기서 한가지의 선택을 해야하는데 미리 해석된 쿼리를 통한 나은 성능을 택하느냐 아니면 일반적인 EXECUTE문과 같이 동적 쿼리의 유연성을 택하느냐이다.
24.2.8. 처리 중단과 메세지
RAISE문을 사용하여 Postgres elog 매커니즘으로 출력 메세지를 보낼 수 있다.
RAISE 출력수준 '출력형식' [, identifier [...]];
출력형식에서 뒤 따라오는 identifier의 출력형식을 정할 수 있는 %를 사용할 수 있다. '출력수준'으로 쓸 수 있는 키워드는 다음의 3가지이다.
DEBUG / 제품수준 환경의 데이터베이스에서 사용되는 조용한 수준의 메세지
NOTICE / 데이터베이스 log에 기록이 되고 클라이언트로 보내지는 수준의 메세지
EXCEPTIOM / 데이터베이스 log에 기록이 되고 트랜젝션을 중단하는 수준의 메세지
RAISE NOTICE ''Id number '' || key || '' not found!'';
RAISE NOTICE ''Calling cs_create_job(%)'',v_job_id;
바로 위의 2번째 예에서 v_job_id은 %의 자리에 문자열로 출력된다.
RAISE EXCEPTION ''Inexistent ID //> %'',user_id;
위의 예는 트랜젝션을 중단하고 데이터베이스 log에 기록한다.
24.2.9. 예외
Postgres는 그리 똑똑한 예외처리 모델을 가지고 있지 않다. 분석기(parser), 번역기/최적화 (planner/optimizer) 또는 실행기(executor)에서 더이상 요청된 처리를 수행하지 못할 경우 모든 트랜젝션은 중단되고 main loop로 건너뛰고나서 클라이언트 프로그램에서 다음 쿼리를 수행한다.
이같은 상황을 알리기 위한 메세지를 오류 메커니즘으로 넣는 것이 가능하다. 그러나 현재로는 왜 그러한 상황이 발생하였는지 아는 것은 불가능하다. (입/출력 변환 오류, floating point오류, parse 오류 등등). 그래서 데이터베이스 처리가 오류의 시점에서 자료의 불일치가 발생할 수 있기 때문에 상위 처리부로 귀환한다거나 계속된 명령의 처리는 전체 데이터베이스를 망가뜨릴 수 있다. 그렇기 때문에 처리가 중단이 됐고 이의 사실이 클라이언트에 알려진다 하더라도 계속되는 처리는 무의미하다.
그렇기 때문에 PL/pgSQL이 현재 할 수 있는 것은 함수나 trigger procedure가 처리되는 과정에서 중단(abort)되면 어떤 함수의 어디서 (line number와 문장의 종류) 발생했는지 추가적인 DEBUG lebel의 로그 메세지를 기록해주는 것이 다이다.
반응형