GIS Developer

아래는 PostgreSQL의 함수를 요약한 내용이며, 보다 자세한 내용과 예제는 https://www.techonthenet.com/postgresql/functions/index.php 참고하기 바랍니다.

String Functions

• btrim : 문자열의 시작과 끝 양쪽에 지정된 문자들을 제거
• char_length : 문자열의 길이를 반환
• character_length : 지정된 문자열의 길이를 반환
• || : 2개의 문자열을 합하여 반환
• initcap : 문자열 중 첫번째 문자는 대문자로 변환하고 나머지는 소문자로 변환
• length : 문자열의 길이를 반환
• lower : 지정된 문자열을 모두 소문자로 변환
• lpad : 지정된 문자열을 원하는 길이로 맞추는데, 이때 부족한 문자를 왼쪽에 채움
• ltrim : 문자열의 왼쪽에서 지정된 모든 문자를 제거
• position : 문자열에서 지정된 문자열이 존재하는 인덱스값을 반환
• repeat : 문자열을 원하는 만큼 반복해서 반환
• replace : 기존의 문자열을 구성하는 부분 문자열을 원하는 문자열로 변환
• rpad : 지정된 문자열을 원하는 길이로 맞추는데, 이때 부족한 문자를 오른쪽에 채움
• rtrim : 문자열의 오른쪽에서 지정된 모든 문자를 제거
• strpos : 기존의 문자열에서 찾고자 하는 부분 문자열의 위치를 반환
• substring : 주어진 문자열에서 원하는 부분 문자열을 추출
• translate : 기존의 문자열 중 치환하고자 하는 문자열을 지정해 변환
• trim : 문자열의 시작과 끝 양쪽에 지정된 문자들을 제거
• upper : 문자열을 모두 대문자로 변환

Numeric/Math Functions

• abs : 절대값 반환
• avg : 평균값 반환
• ceil : 소수점 자리를 무조건 올려 정수 반환
• ceiling : 소수점 자리를 무조건 올려 정수 반환
• count : 개수 반환
• div : 2개의 수를 나눠서 그 몫(정수)을 반환
• exp : e(2.71828182845904)의 제곱값 반환
• floor : 지정된 수와 같거나 작은 정수 반환
• max : 최대값 반환
• min : 최소값 반환
• mod : 2개의 수를 나눈 그 나머지 반환
• power : 거듭 제곱의 결과 반환
• random : [0~1) 사이의 난수값 반환
• round : 원하는 자리수에서의 반올림한 값을 반환
• setseed : 난수 발생시 그 씨앗값을 설정.
• sign : 주어진 수에 대한 부호 반환.
• sqrt : 루투(root) 값 반환.
• sum : 합계 반환.
• trunc : 원하는 자리수까지의 값을 반올림 없이 반환

Date/Time Functions

• age : 두 날짜 사이의 시간 간격을 년, 월, 일로된 문자열로 반환
• current_date : 현재의 날짜 반환
• current_time : Time Zone을 갖는 현재의 시간을 반환
• current_timestamp : Time Zone을 갖는 현재의 날짜와 시간을 반환
• date_part : 시간 또는 날짜로부터 원하는 부분(년, 월, 일, 시, 분, 일, 초 등)을 반환
• extract : 시간 또는 날짜로부터 원하는 부분(년, 월, 일, 시, 분, 일, 초 등)을 반환
• localtime : 현재의 시간을 반환
• localtimestamp : 현재의 시간과 날짜를 반환
• now : Time Zone을 갖는 현재의 날짜와 시간을 반환

Conversion Functions

• to_char : 숫자 또는 날짜를 문자열로 변환
• to_date : 문자열을 날짜로 변환
• to_number : 문자열을 숫자로 변환
• to_timestamp : 문자열을 타임스탬프(Timestamp)로 변환

추후 PostgreSQL에서 프로시져를 작성할 때 참조하기 위해 글을 남깁니다.

CREATE OR REPLACE FUNCTION 
    get_two_rows(id1 TEXT, lyr1 TEXT, id2 TEXT, lyr2 TEXT) 
RETURNS 
TABLE (geom GEOMETRY)
AS $$
BEGIN
	RETURN QUERY EXECUTE
		'SELECT the_geom FROM ' || lyr1 || ' WHERE networkid::text = ''' || id1 || '''' || 
		' UNION ALL ' || 
		'SELECT the_geom FROM ' || lyr2 || ' WHERE networkid::text = ''' || id2 || '''';

END; $$
LANGUAGE PLPGSQL;

SQL문을 문자열 형태로 조합해서 실행하기 위해 ‘QUERY EXECUTE’를 사용해야 하며, 외따옴표(‘)를 문자열에 넣기 위해 연속된 외따옴표 2개가 필요하다는 것에 주의해야 합니다.

사실 위의 프로시져는 단순해서 그냥 SQL문을 통해 Java의 PrepareStatement를 사용해 실행해도 되는데.. PrepareStatement를 통한 SQL 문 실행시 SQL의 문자열 값 중 SELECT의 FROM절에 대한 테이블명은 바인딩할 수 없다(?에 의한..)는 제약때문에 프로시져를 사용하게 되었습니다. 소스코드에 대한 정적검사에서 SQL문에 대한 SQL Injection 보안성 오류로 지적받아.. 위와 같이 상당히 불필요한 프로시져까지 사용하게 되었습니다.