[PostgreSQL] stored function(stored procedures) 사용하기

얼마 전에 stored function과 trigger를 첨으로 사용해보았다.
엄청 어려운 수준으로 사용은 안 해봤지만, 정리하면 누군가에게 도움이 될 수 있지 않을까.. 하는 마음에
(하루만 지나면 기억 못 하는 나 자신을 위해서...)
stored function, trigger에 대하여 각각 정리해보려고 한다.

참고 URL
http://www.postgresqltutorial.com/postgresql-stored-procedures
https://www.postgresql.org/docs/current/sql-createfunction.html

 

참고 사이트에서는 stored function의 장단점에 대하여 다음과 같이 기술하고 있다.

장점

- application과 database 서버 간의 통신을 줄일 수 있다.
  application에서 발생할 수 있는 여러 번의 SQL 호출을 stored function 호출 한 번으로 처리할 수 있다.
- application의 성능을 향상할 수 있다.
  stored function은 미리 컴파일된 상태로 database에서 동작하기 때문에 application의 부담을 줄일 수 있다.
- 재사용이 편리하다.
  stored function을 정의해두면, 어떤 application에서도 자유롭게 호출하여 사용할 수 있다.

단점

- 개발 속도가 느려질 수 있다.
  application 개발 스킬과 다른 스킬이 필요하기 때문에 개발 속도가 느려질 수 있다.
- 버전 관리 및 디버깅이 어렵다.
- MySQL, Microsoft SQL과 같은 다른 database 서버에서 동작하지 않을 수 있다.

stored function을 어떤 상황에서 사용할지는 각자 판단해야 할 것 같다.

참고 사이트에 stored function의 기초부터 잘 설명이 되어 있기 때문에

여기서는 2가지 상황을 가정하여 function을 구현하였다.

1. A table의 데이터를 읽어와서 B table에 insert

CREATE OR REPLACE FUNCTION insert_A_to_B(cond_id bigint)
returns int
AS $$
DECLARE
  result int := 0;
  data_info record;
  data_value boolean;
  now_date timestamp := now();
BEGIN
  for data_info IN select * from A where id > cond_id loop
    raise notice '%, %', data_info.id, data_info.value;

    if data_info.value = 'PASS' then
      data_value := true;
    else
      data_value := false;
    end if;

    insert into B
      (id, value, created, modified)
    values
      (data_info.id, data_value, now_date, now_date);

    result := result+1;
  end loop;
  return result;
END; $$
LANGUAGE 'plpgsql';

위 예제는 단순한 사용법을 구현한 것이기 때문에 복잡한 조건을 포함하지 않았다.

- function에서 사용할 변수는 DECLARE에서 정의를 해야 한다.
- select 구문의 결과를 사용하기 위해서 record type을 사용하였다.

반복문과 조건문은 기존 언어처럼 다양하게 제공되고 있다.

반복문 : WHILE LOOP, FOR LOOP...
조건문 : IF/ELSEIF/ELSE, CASE/WHEN/ELSE

해당 stored function은 아래와 같이 호출하여 사용할 수 있다.

select insert_A_to_B(100);

2. A table에 data insert (data는 object array로 입력, function 안에서 B table의 값을 참고하기)

object를 parameter로 넘기기 위하여 아래와 같이 사용자 type을 정의한다.

create type a_type as (priority bigint, value varchar(32));

stored function은 다음과 같이 정의한다.

CREATE OR REPLACE FUNCTION insert_A_list(a_type[])
returns int
AS $$
DECLARE
  result int := 0;
  a_object a_type;
  now_date timestamp := now();
BEGIN
  foreach a_object IN array $1 loop
    if exists(select 1 from B where value=a_object.value) then
    
      insert into A
        (priority, value, created, modified)
      values
        (a_object.priority, a_object.value, now_date, now_date);

      result := result+1;
    end if;
  end loop;

  return result;
END; $$
LANGUAGE 'plpgsql';

parameter로 정의 부분에 parameter의 이름은 정의하지 않고
loop 구문에서 $1로 해당 parameter를 사용하였다.
(나 같이 이름 정하는 게 너무 어려운 사람들에게 좋은 방식이다.)

- foreach 구문을 사용하여 parameter로 넘어온 array를 처리하였다.
- exists 구문을 사용하여 table B의 값을 체크하였다.

위의 function은 아래와 같은 형태로 호출할 수 있다.

select insert_keyword_list(ARRAY[(1, 'test1'), (2, 'test2'), (3, 'test3')]::a_type[]);

호출할 때 ARRAY [.......]::a_type []과 같은 형식을 사용하기 싫다면
아래와 같이 variadic을 사용하여 parameter 정의하면

CREATE OR REPLACE FUNCTION insert_A_list(variadic a_type[])

아래와 같은 형식으로 function을 호출할 수 있다.

select insert_keyword_list((1, 'test1'), (2, 'test2'), (3, 'test3'));

 

variadic 사용에 주의할 점이 있다면, parameter가 100개가 넘어가면 에러가 발생하게 된다.