PostgreSQL의 프로시져(PL/pgSQL) 예제

추후 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 보안성 오류로 지적받아.. 위와 같이 상당히 불필요한 프로시져까지 사용하게 되었습니다.

답글 남기기 응답 취소