oracle 11g에서 pgsql 9.1 로 SQL포팅 중입니다.
to_single_byte / substrb 함수때문에 고민이네요...
pgsql에서 방법이 없을까요?
to_single_byte, to_multi_byte 함수의 문제는
예외 사항이 너무 많아서 범용적인 함수를 못 만들겠더군요.
필요한 부분만 translate 함수를 이용해서 직접 만들어서 사용하면 될 것 같습니다.
substrb 함수는
convert 함수를 사용해서 먼저 문자열을 byte 단위 덩어리로 변환을 하고,
그것을 substr 함수를 사용해서 풉니다.
여기서 주의할 사항은 PostgreSQL에서는 2byte 단위의 완성형 문자셋을 데이터베이스 서버 인코딩으로
사용하면 표현하지 못하는 한글들이 있어, 일반적으로 utf8로 인코딩을 사용합니다.
substr 함수를 사용하는 영역이 서버 영역이기 때문에,
이 인코딩에 대한 적절한 사전 변환 작업이 필요합니다.
$ select convert_from(convert(substr(convert_to('안녕하세요', 'uhc'),1,4), 'uhc','utf8'),'utf8');
--------------
안녕
(1 row)
보는 바와 같이 처리할 문자열을 먼저 uhc 인코딩으로 바꾸어서 substr 작업을 하고,
그것을 리턴하기 위해서, convert 함수로 문자셋을 바꾸고, 그것을 다시 text 형으로 바꿉니다.
문제는 이때, 잘못 잘려져서 문자셋 변환이 되지 않는 경우라면 오류를 내겠죠.
위 방법밖에 없다면 마지막 말씀처럼 (UTF8 서버에서) 한글과 알파벳/숫자가 뒤섞여 있는 컬럼은 substrb 쓰면 안된다는 결론이 나오는군요...
ora_porting=> select convert_from(substr('12안녕'::bytea, 1, 2), 'utf8');
convert_from
12
ora_porting=> select convert_from(substr('12안녕'::bytea, 1, 3), 'utf8');
ERROR: invalid byte sequence for encoding "UTF8": 0xec
ora_porting=> select convert_from(substr('12안녕'::bytea, 1, 5), 'utf8');
12안
오라클 같은 경우는 오류대신 직전까지 완성되는 문자를 리턴하는데 이놈은 성의(?)없어보이게 걍 오류가 나네요... 흉내라도 내야할꺼 같은데... 별도함수 생성해서 들어오는 데이터에 대해서 바이트단위로 검사해야 하나요? ㅜ.ㅜ
PostgreSQL 쪽에서는 그런 편의성이 많이 부족합니다.
잘못된 놈은 잘못된 놈이지 잘된놈까지만 일단 처리할게.
이 방식이 안통하는 아주 깐깐한 놈입니다.
빈문자열과 null 구분도 그래서 아주 신경 쓰이는 부분이기도 하고요.
그래서, 통상 substrb 같은 경우는 plpgsql 로 안만들고, plpython 같은 것으로
언어 자체에서 multibyte 변환이 자유로운 놈을 선택해서,
한글자 한글자씩 처리하다가 처리가 안되는 부분에 대해서는 사용자가 정책에 따라
예외처리하는 방식으로 풉니다.
그래서, 돌고 돌아, 결국 그 부분은 응용프로그램에서 해라, DB에서 거기까지는 신경안쓰겠다
이게 가장 보편적인 모습같습니다.
막힐때마다 도움을 받게 되네요... 고맙습니다.