[ 살펴보기 ] PostgreSQL - Custom Function, Procedure

PostgreSQL도 다른 programming language와 마찬가지로 반복되는 연산을 function으로 만들어 재사용할 수 있다.

Function

member table의 id을 parameter로 전달받아 member name을 return하는 function가 필요하다고 가정해보자. 새로운 function의 생성은 CREATE FUNCTION keyword를 사용한다.

CREATE FUNCTION select_member(memberId integer) 
RETURNS text AS $$
    SELECT name FROM members WHERE id = memberId;
$$ LANGUAGE SQL;

위와 같이 CREATE FUNCTION keyword 다음 function 이름을 선언하고 function이 전달 받을 parameter 그리고 RETURN keyword를 통해 return type을 지정한다.

Function이 호출 되었을 때 처리할 상세내용은 AS $$ ~ $$ 사이에 추가한다. 위의 sql statement를 실행하고 \df psql command를 실행해보면 select_member라는 이름의 함수가 생성된 것을 확인할 수 있다.

이제 다음과 같이 함수를 호출하면 주어진 parameter에 따라 function에 선언된 작업을 수행한다.

SELECT select_member(20);

만약 기존 function의 내용을 변경하고 싶다면 아래 예제와 같이 CREATE OR REPLACE command를 통해 function의 내용을 변경할 수 있다.

CREATE OR REPLACE FUNCTION select_member(memberId integer) 
RETURNS text AS $$
    SELECT name FROM members_new WHERE id = memberId;
$$ LANGUAGE SQL;

주의할 점은 CREATE OR REPLACE을 통해 function을 변경할 때 function의 parameter type을 변경하면 기존의 function을 변경하는 것이 아니라 새로운 function이 생성된다. ( overloading ) 예를 들어 다음 code는 같은 이름을 가진 또 하나의 function을 생성한다.

CREATE FUNCTION select_member(memberId text) 
RETURNS text AS $$
    SELECT name FROM members WHERE id = CAST(memberId AS integer);
$$ LANGUAGE SQL;

또한 CREATE OR REPLACE FUNCTION을 통해 기존 function의 parameter 이름 또는 return type을 변경할 수는 없다.

만약 function의 return 값이 하나가 아니라 복수의 값이라면 SETOF keyword를 return에 추가해준다.

CREATE FUNCTION select_member(memberName text) 
RETURNS SETOF integer AS $$
    SELECT id FROM members WHERE name = memberName;
$$ LANGUAGE SQL;

members table의 데이터 중 id가 18과 51인 데이터의 name이 “test name”이라고 가정한다면 select_member function을 실행할 때 argument로 “test name”을 전달하여 실행하면 조회 결과는 다음과 같다.

select_member
---------------
            18
            51

혹은 function이 여러가지 fields를 결과로 return해야 한다면 다음과 같이 table keyword를 통해 여러 fields 값을 결과로 return 할 수 있다.

CREATE OR REPLACE FUNCTION select_member(memberId integer) 
RETURNS TABLE (id INTEGER, name text ) AS $$
    SELECT id, name FROM members WHERE id = memberId;
$$ LANGUAGE SQL;

위의 function을 생성하고 SELECT select_member(51); command를 통해 함수를 실행해 보면 결과가 다음과 같이 조회된다.

  select_member
------------------
 (51,"test name")

만약 id와 name을 원래 table에서 조회하듯이 결과를 column 별로 분리하여 조회하고 싶다면 function을 다음과 같이 조회한다.

 SELECT id, name FROM select_member(51);

이번엔 plpgsql languae를 통해 function을 작성하는 방법을 살펴보자. 기본적인 형식은 다음과 같다.

CREATE OR REPLACE FUNCTION test_sum(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
DECLARE
    total INTEGER;
BEGIN
    total := a + b;
    RETURN total;
END;
$$ LANGUAGE plpgsql;

plpgsql language를 통해 작성한 function은 위의 예제에서 볼 수 있듯이 DECLARE section에 내부 변수를 선언하여 사용할 수 있다. 그리고 BEGIN과 END 사이의 function logic을 추가한다.

Member id를 parameter로 전달 받아 id와 name을 return하는 functin을 plpgsql language를 통해 다시 작성하면 다음과 같다.

CREATE OR REPLACE FUNCTION select_member(memberid INT)
RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
    RETURN QUERY
    SELECT members.id, members.name
    FROM members
    WHERE members.id = memberid;
END;
$$ LANGUAGE plpgsql;

plpgsql language를 통해 작성한 function 내부에 다음과 같이 if statement와 case statement를 사용할 수 있다. 만약 memberId가 30이하인 데이터만 자유롭게 조회할 수 있고 30이상일 때는 id가 31인 data를 고정해서 return하는 조건을 적용한다면 다음과 같다.

CREATE OR REPLACE FUNCTION select_member(memberid INT)
RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
    IF memberid < 30 THEN
        RETURN QUERY
        SELECT members.id, members.name
        FROM members
        WHERE members.id = memberid;
    ELSE
        RETURN QUERY
        SELECT members.id, members.name
        FROM members
        WHERE members.id = 31;
    END IF;
END;
$$ LANGUAGE plpgsql;

또한 다음과 같이 case statement를 사용할 수도 있다.

CREATE OR REPLACE FUNCTION select_member(memberid INT)
RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
    CASE memberid
    WHEN 1 THEN
        RETURN QUERY
        SELECT members.id, members.name
        FROM members
        WHERE members.id = 18;
    WHEN 2 THEN
        RETURN QUERY
        SELECT members.id, members.name
        FROM members
        WHERE members.id = 19;
    ELSE
        RETURN QUERY
        SELECT members.id, members.name
        FROM members
        WHERE members.id = memberid;
    END CASE;
END;
$$ LANGUAGE plpgsql;

생성한 function을 삭제할 때는 DROP FUNCTION command를 통해 삭제한다.

DROP FUNCTION select_member;

만약 이름은 같지만 parameter type이 다른 function이 다수 존재한다면 삭제할 function의 parameter까지 명시해주어야 삭제할 수 있다.

DROP FUNCTION select_member(memberName text);

Procedure

위에서 살펴본 사용자 정의 function과 유사하지만 다음과 같은 차이점이 있다.

사용자 정의 function과는 다르게 return keyword를 통해 값을 return 하지 않는다. 특정 값을 return하고 싶다면 in,out argument mode를 통해 return 해주어야 한다.
SELECT과 같은 query를 통해 호출하지 않고 CALL keyword를 통해 호출한다.
사용자 정의 function과는 달리 transaction을 통한 commit, rollback 적용이 가능하다.

다음 예제는 memberId와 nickname을 전달 받아 새로운 nickname으로 수정하는 procedure의 예제다.

CREATE PROCEDURE update_nickname (
    memerId INT, 
    nickname TEXT
)
AS $$
BEGIN

    UPDATE members SET nickname = nickname
    WHERE id = memerId;

END;
$$ LANGUAGE plpgsql;

생성한 procedure를 호출하기 위해서 CALL keyword를 사용한다.

CALL transfer_money(20, "new nickname");

생성한 procedure의 내용을 변경하고자 한다면 CREATE OR REPLACE PROCEDURE를 통해 procedure 내용을 변경할 수 있다.

CREATE OR REPLACE PROCEDURE update_nickname (
    memerId INT, 
    nickname TEXT
)
AS $$
BEGIN

    UPDATE members SET nickname = nickname
    WHERE id = memerId;

END;
$$ LANGUAGE plpgsql;

function과 마찬가지로 이미 생성된 procedure의 parameter의 data type을 변경하면 기존의 procedure가 수정되는 것이 아닌 같은 이름을 가진 새로운 procedure가 생성된다. ( overloading ) 예를 들어 아래 code를 실행하면 같은 이름의 procedure가 하나 더 생성된다.

CREATE OR REPLACE PROCEDURE update_nickname (
    memerId TEXT, /* parameter type 변경 */ 
    nickname TEXT
)
AS $$
BEGIN

    UPDATE members SET nickname = nickname
    WHERE id = memerId;

END;
$$ LANGUAGE plpgsql;

또한 function과 마찬가지로 CREATE OR REPLACE FUNCTION을 통해 기존 procedure의 parameter 이름을 변경할 수 없다. 예를 들어 다음 code를 실행하면 오류가 발생한다.

CREATE OR REPLACE PROCEDURE update_nickname (
    userId TEXT, /* parameter name 변경 시도 */ 
    nickname TEXT
)
AS $$
BEGIN

    UPDATE members SET nickname = nickname
    WHERE id = userId;

END;
$$ LANGUAGE plpgsql;