GIS Developer

안녕하세요, GIS Developer 김형준입니다. 이번 포스트에서는 조건문 중 CASE 문에 대해 살펴 보겠습니다. CASE 문의 설명을 위해 실제 DBMS에 저장된 Table을 활용해 설명하겠습니다. 사용할 테이블은 다음과 같습니다.

PERSON이라는 이름의 테이블에 총 5개의 Row가 저장되어 있는 간단한 데이터입니다.

CASE 문의 설명을 위해 CASE를 활용한 아래와 같은 함수를 만들어 보겠습니다.

CREATE OR REPLACE FUNCTION get_surname(part_name CHAR(20)) 
RETURNS CHAR(2) AS $$
DECLARE 
    full_name CHAR(20);
    surname CHAR(1);
BEGIN
    SELECT INTO full_name name
    FROM person
    WHERE name LIKE '%' || part_name || '%';
    
    IF full_name IS NOT NULL THEN
        surname = substr(full_name, 1, 1);
        
        CASE surname
        WHEN '김' THEN
            RETURN '金氏';
        WHEN '이' THEN
            RETURN '理氏';
        WHEN '박' THEN
            RETURN '博氏';
        WHEN '최' THEN
            RETURN '崔氏';
        ELSE
            RETURN '모름'; 
        END CASE;
        
    	RETURN surname;
    ELSE
		RETURN NULL;
	END IF;
END; $$
LANGUAGE plpgsql;

위의 함수에 대한 코드 중 14번에 CASE 조건절이 보입니다. surname 변수에는 조회된 이름의 성에 대한 1개의 문자가 할당됩니다. 이 문자에 따라 한자로 그 결과를 반환해 주게 되는데요. 14번의 CASE에 조건과 비교할 대상 값을 담고 있는 변수를 지정하고, 15번과 17번 등의 WHEN에 일치하는 값을 지정합니다. 조건과 일치하는 WHEN을 만나면 WHEN의 밑에 존재하는 코드 부분이 실행됩니다. 다음은 실행 결과입니다.

위의 경우처럼 정확한 값의 일치에 대한 조건 비교가 아닌 어떤 값의 범위에 대한 조건 비교에도 CASE와 WHEN을 사용할 수 있습니다. 이에 대한 다음의 코드는 아래와 같습니다.

CREATE OR REPLACE FUNCTION get_age_level(part_name CHAR(20)) 
RETURNS CHAR(20) AS $$
DECLARE 
    the_age INTEGER;
BEGIN
    SELECT INTO the_age age
    FROM person
    WHERE name LIKE '%' || part_name || '%';
    
    CASE
        WHEN the_age < 10 THEN
            RETURN '어린아이';
        WHEN the_age >= 10 AND the_age < 20 THEN
            RETURN '10대 사춘기';
        WHEN the_age >= 20 AND the_age < 30 THEN
            RETURN '20대 취준생';
        WHEN the_age >= 30 AND the_age < 40 THEN
            RETURN '30대 청춘';        
        ELSE
            RETURN '불노장생';
    END CASE;
END; $$
LANGUAGE plpgsql;

get_age_level은 인자로 지정한 이름과 유사한 Row의 age 필드값의 범위에 따라 각기 그 나이대를 표현하는 문자열을 반환합니다. 이때 나이 범위에 대한 조건을 CASE WHEN 구문으로 분류하고 있습니다. 아래는 실행 결과입니다.

안녕하세요, GIS Developer 김형준입니다. 이번 장에서는 PL/pgSQL에서 제공하는 조건문에 대해 살펴보겠습니다. 조건문은 어떤 조건에 대해 실행할 코드를 분리하기 위한 목적으로 사용됩니다.

PL/pgSQL에서 제공하는 조건문은 IF 문과 CASE 문으로 크게 구분할 수 있습니다. 먼저 IF 문에 대해 살펴보겠습니다.

IF 문의 문법은 아래와 같습니다.

IF <조건> THEN
    <참의 실행 코드>
ELSE
    <거짓의 실행 코드>
END IF;

만약 <조건>이 부합되는 거짓(false)이 아닌 참(true)일 경우 <참의 실행 코드>를 실행하고, <조건>이 거짓일 경우 <거짓의 실행 코드>를 실행합니다. 조건에 대한 실행 코드는 1줄 이상으로 구성할 수 있습니다.

일단 간단한 코드를 통해 ELSE 구문이 없는 IF문의 예를 살펴보겠습니다.

DO $$
DECLARE
    a integer := 20;
    b integer := 40;
    c integer := 20;
BEGIN 
    IF a > b THEN
        RAISE NOTICE 'a가 b보다 더 큽니다다.';
    END IF;
 
    IF a < b THEN
        RAISE NOTICE 'a가 b보다 더 작습니다.';
    END IF;
 
    IF a = b THEN
        RAISE NOTICE 'a와 b가 동일합니다.';
    END IF;
    
    IF a >= c THEN
        RAISE NOTICE 'a가 c보다 크거나 같습니다.';
    END IF;
    
    IF a != b THEN
        RAISE NOTICE 'a가 b가 같지 않습니다.';
    END IF;
    
    IF a != b AND a = c THEN
        RAISE NOTICE 'a와 b가 같지 않고 a와 c가 같습니다.';
    END IF;
    
    IF a = b OR a = c THEN
        RAISE NOTICE 'a와 b가 같거나 a와 c가 같습니다.';
    END IF;
    
    IF NOT (a = b OR a = c) THEN
        RAISE NOTICE 'a와 b가 같지지 않고 a와 c도 같지 않습니다.';
    END IF;
END $$;

변수 a, b, c를 각각 20, 40, 20으로 초기화하고, 각 값에 대해 IF 문을 통해 비교하고 있습니다. 먼저 7번 IF 문은 a가 b 보다 크다라는 조건에 부합하지 않으므로 8번 코드를 실행하지 않습니다. 비교 연산은 크다, 작다, 같거나 크다, 같거나 작다, 같지 않다, 같다에 대해 각각 >, <, >=, <=, !=, =입니다. 조건에 대해 AND와 OR 그리고 NOT으로 묶어 조합할 수 있습니다. 27번의 IF 문은 a와 b가 같지 않고(AND) a와 c가 같은가라는 조건으로 참이므로 28번 코드가 실행됩니다. 30번의 IF문은 a와 b가 같거나(OR) a와 c가 같은가라는 조건으로 거짓이므로 31번은 실행되지 않습니다. 그리고 33번의 IF 문은 a와 b가 같거나 a와 c가 같은가에 대한 조건을 NOT으로 부정하고 있는데, a와 b가 같거나 a와 c가 같은가는 참(true)이고 이를 부정하므로 결국 거짓(false)되어 34번 코드는 실행되지 않습니다. 최종 결과는 아래의 화면과 같습니다.

ELSE 구문까지 포함한 IF 문의 예를 보면 아래와 같습니다.

DO $$
DECLARE
    a integer := 20;
    b integer := 40;
BEGIN 
    IF a > b THEN
        RAISE NOTICE 'a가 b보다 더 큽니다.';
    ELSE
        RAISE NOTICE 'a가 b보다 더 크지 않습니다.';
    END IF;
END $$;

IF 문에서 a가 b보다 큰가에 대한 조건이 거짓(false)이므로 ELSE 문 다음의 실행 코드가 실행되므로 그 결과는 다음과 같습니다.

IF 문에 대한 마지막 예로 다음 코드를 살펴보겠습니다.

DO $$
DECLARE
    a integer := 20;
    b integer := 40;
BEGIN 
    IF a > b THEN
        RAISE NOTICE 'a가 b보다 더 큽니다.';
    ELSEIF a = b THEN
        RAISE NOTICE 'a와 b가 같습니다.';
    ELSE
        RAISE NOTICE 'a와 b가 같지 않고, a가 b보다 더 크지도 않습니다.';
    END IF;
END $$;

ELSEIF 문을 사용하고 있는데요. 만약 첫번째 IF 문에 대한 조건이 거짓이면 다음의 ELSEIF 문의 조건을 검사하게 됩니다. 그런데 이 ELSEIF 문 역시 거짓이라면 다음 ELSEIF 문을 검사하게되고, 최종적으로 어떠한 조건도 만족하지 않는다면 ELSE 문의 코드가 실행되게 됩니다. 위의 코드의 실행 결과는 아래와 같습니다.

안녕하세요, GIS Developer 김형준입니다. 이번 포스트는 PL/pgSQL에서 변수와 상수를 정의하고 사용하는 내용에 대해 정리하여 설명하겠습니다.

변수란 어떤 값을 가지고 있으면서, 필요할 경우 그 값을 변경할 수 있습니다. 또한 변수는 그 이름과 변수가 가지는 값의 타입을 지정해야 합니다.

변수의 이름은 개발자에게 의미있는 이름을 부여하는 것이 좋고, 값의 타입은 integer, numeric, char, varchar 등과 같이 각각 정수, 실수, 문자, 가변길이 문자 등으로 지정할 수 있습니다.

변수는 선언 시에 기본값을 지정할 수 있습니다. 만약 지정하지 않는다면 그 변수의 값은 NULL 값을 갖게 됩니다.

다음은 여러 개의 변수를 선언하는 PL/pgSQL 코드입니다. 특히 아래의 코드는 함수를 정의하지 않고 바로 PL/pgSQL을 실행합니다.

DO $$ 
DECLARE
   age integer := 40;
   korean_name varchar(10) := '김형준';
   alias_name varchar(50) := 'Dip2K';
   weight numeric(3,1) := 65.5;
BEGIN 
   RAISE NOTICE '%의 별명은 % 이고 나이는 %이며 몸무게는 %입니다.', 
       korean_name, alias_name, age, weight;
END $$;

위의 코드를 보시면 DECLARE 구문 바로 뒤에 변수를 선언하고 있습니다. 4개의 변수를 선언하고 있고, 초기값 40인 정수형 변수 age와 ‘김형준’ 값을 가지는 가변길이 10인 문자형 변수 korean_name 그리고 65.5 값을 가지는 실수형 변수 weight를 갖습니다. 실수형 타입을 지정하기 위해 numeric(n,m)으로 선언하는데, n은 전체 자리수이고, m은 소수점 아래에 대한 자리수입니다. 8번 줄의 RAISE NOTICE는 해당 변수들을 원하는 문자열로 포맷으로 구성한 것을 PL/pgSQL의 실행환경에서 표시하는 코드입니다. 실행하면 아래와 같은 결과를 볼 수 있습니다.

일반적인 숫자형이나 문자형 이외에도 훨씬 다양한 데이터 타입이 존재합니다. 그 중에 아래의 코드에서 볼 수 있는 시간(time) 타입입니다.

DO $$ 
DECLARE
   current_time time := now();
BEGIN 
   RAISE NOTICE '%', current_time;
END $$;

위 코드의 실행 결과는 아래와 같습니다.

변수의 데이터 타입을 결정할 때, 기존에 존재하는 테이블의 특정 필드의 데이터 타입을 가져와 그 데이터 타입으로 변수의 타입을 지정해야 할 필요가 있습니다. 예를 들어 employee라는 데이블의 salary라는 필드의 데이터 타입을 가져와 my_salary라는 변수를 선언하는 코드는 아래와 같습니다.

my_salary employee.salary#TYPE := 100000.12;

끝으로 변수 선언에 대해 하나더 설명하면, 동일한 대상의 값을 담고 있는 변수에 대해 또 다른 이름(별칭)을 붙일 수 있습니다. 아래의 코드는 current_time이라는 변수를 old_time이라는 별칭을 붙이는 예입니다. 이는 어떤 변수에서, 개발하고자 하는 기능에 좀더 의미에 부합하는 이름을 붙이기 위한 목적입니다.

DO $$ 
DECLARE
   current_time time := now();
   old_time ALIAS FOR current_time;
BEGIN 
   RAISE NOTICE '%', old_time;
END $$;

이제 상수(constant)에 대해 설명하겠습니다. 상수는 변수와 다르게 한번 값이 정해지면 변경할 수 없다는 차이가 있습니다. 상수를 선언하는 방식은 변수의 선언처럼 DECLARE 구문 뒤에서 이루어지며, 상수 이름과 타입 사이에 CONSTANT라고 지정하면 됩니다. 아래의 예를 보면 쉽게 알 수 있습니다.

DO $$ 
DECLARE
   vat CONSTANT numeric := 0.1;
   cost numeric := 100000;
   real_cost numeric;
BEGIN 
   real_cost = cost + cost * vat;
   RAISE NOTICE '부가세를 적용한 실제 가격은 % 입니다', real_cost;
END $$;

위의 3번 코드가 vat라는 실수형 상수를 선언하는 코드입니다.

이상으로 PL/pgSQL에서 변수와 상수를 선언하는 내용에 대해 살펴 보았습니다.

안녕하세요, GIS Developer 김형준입니다. 2번째 PostgreSQL의 Stored Procedure의 튜토리얼입니다. 이번에는 사용자 정의 함수를 생성하는 CREATE FUNCTION 구문의 한가지 예로 아래의 코드를 살펴보겠습니다.

CREATE FUNCTION add(a INTEGER, b INTEGER)
RETURNS INTEGER AS
$$ BEGIN
    RETURN a+b;
END; $$
LANGUAGE PLPGSQL;

위의 코드 중 CREATE FUNCTION 코드 대신 CREATE OR REPLACE FUNCTION 코드로 대체하면 아래와 같은데요.

CREATE OR REPLACE FUNCTION add(a INTEGER, b INTEGER)
RETURNS INTEGER AS
$$ BEGIN
    RETURN a+b;
END; $$
LANGUAGE PLPGSQL;

위의 CREATE OR REPLACE FUNCTION 코드는 CREATE FUNCTION와 다르게 이미 동일한 함수가 존재할 경우 함수를 새롭게 교체하라는 의미를 갖습니다. 이 add 함수는 정수형 타입인 a와 b 인자를 갖습니다. 기본적으로 인자는 IN 형식입니다. IN 형식 이외에 OUT과 INOUT 형식의 인자도 지정이 가능합니다. IN 형식으로 지정된 인자는 단지 함수에 값만을 전달해 주는 목적이고, OUT은 함수 종료시 이 인자에 값을 전달해 외부에 반환해주는 목적도 갖습니다. INOUT은 IN과 OUT에 대한 목적을 모두 갖습니다.

함수의 인자를 OUT으로 지정하는 예를 살펴 보겠습니다. 아래의 코드를 예로 들어 OUT 형식의 함수 인자를 살펴 보겠습니다.

CREATE OR REPLACE FUNCTION getmaxmin(
    v1 NUMERIC, 
    v2 NUMERIC,
    OUT min_value NUMERIC,
    OUT max_value NUMERIC)
AS 
$$ BEGIN
    min_value := GREATEST(v1, v2);
    max_value := LEAST(v1, v2);
END; $$
LANGUAGE PLPGSQL;

위의 getmaxmin이라는 함수는 4개의 인자를 받습니다. 첫번째와 두번째 인자는 별도의 지정이 없으므로 IN 형식이고 min_value와 max_value는 OUT 형식으로 지정하였습니다. 이 함수는 첫번째와 두번째로 지정된 인자값중 최대값을 max_value의 인자로 넘기고 최소값은 min_value의 인자로 넘깁니다. 이 함수를 보면 별도의 반환값을 지정하기 위한 RETURNS 구문을 사용하지 않고, 이처럼 OUT 형식으로 지정된 인자에 값을 반환하고 있습니다.

이 getmaxmin 함수는 아래의 화면처럼 실행할 수 있습니다.

다음으로 INOUT 형식은 IN과 OUT에 대한 특성 모드를 갖습니다. 즉, 값은 함수로 전달할 수도 있고 함수 내부에서 값을 변경해 외부로 값을 전달도 할 수 있습니다. 아래의 함수를 예로 살펴보겠습니다.

CREATE OR REPLACE FUNCTION getmaxmin2(
    INOUT v1 NUMERIC, 
    INOUT v2 NUMERIC)
AS 
$$ DECLARE
    v3 NUMERIC := v1;
BEGIN
    v1 := GREATEST(v2, v3);
    v2 := LEAST(v2, v3);
END; $$
LANGUAGE PLPGSQL;

이 getmaxmin2 함수는 INOUT 형식의 인자 2개를 받습니다. 이 두개의 인자값에서 최대, 최소값을 다시 이 인자의 첫번째와 두번째에 각각 기록해 반환합니다. 이를 위해 5~6번에서 v3 변수를 하나 정의해 사용하고 있습니다. 이 변수에 대한 자세한 내용은 추후 다른 튜토리얼에서 설명하도록 하겠습니다. 이 getmaxmin2 함수를 실행하면 다음과 같은 결과를 얻을 수 있습니다.

IN, OUT, INOUT 형식 이외에도 성격은 다르지만 VARIADIC 형식이 있습니다. 이 VARIADIC 형식은 가변인자로써 동일한 데이터 타입의 여러개의 인자들을 한번에 지정할 수 있습니다. 다음의 예를 살펴보겠습니다.

CREATE OR REPLACE FUNCTION sum(VARIADIC params numeric[]) 
RETURNS numeric AS 
$$ DECLARE
    res numeric := 0;
BEGIN
    FOR i IN 1 .. array_length(params, 1) LOOP
        res := res + params[i];
    END LOOP;

    RETURN res;
END; $$ 
LANGUAGE plpgsql;

위의 코드에서 params 인자는 VARIADIC 형식이고 타입은 NUMERIC[]입니다. 지정한 인자의 개수는 array_length 내장 함수를 사용해 얻을 수 있습니다. FOR 반복만을 사용하고 있는데, 1부터 인자의 개수만큼 반복하고 있습니다. 반복문에 대해서는 추후 자세히 설명하겠습니다. 이 함수는 여러개의 숫자값들로 인자를 지정하여 그 합한 결과를 반환하는데요. 이 함수의 실행 결과의 예는 아래와 같습니다.