Postgresql function 개발 및 예제

Postgresql pl/pgsql 을 이용하여 실제 프로젝트에서 쓰일 법한 function을 개발해 보자.

 

## 개발환경

Postgresql 9.4

pgAdmin 3 - 1.22.2

 

1. 사전준비 - CRUD를 수행할 테스트 테스트 테이블 생성

-- CRUD를 수행할 테스트 테이블
create table public.test_table(
    no          integer       not null primary key
  , title       varchar(300)  not null
  , create_date timestamp(0)  not null
  , update_date timestamp(0)  null
);

-- 테스트 테이블 조회
select * from public.test_table;

 

2. INSERT Function 개발

테이블에 데이터가 없으니 INSERT 해주는 Function을 개발

인자값은 p_title 제목으로 하나만 지정

no 컬럼은 기존에 등록되어 있는 데이터의 max + 1 으로 지정(실제 운영프로젝트 Function 개발이면 데이터 중복 이슈로 인해 serial 컬럼타입이나 UUID, 복합키를 권고한다.)

create_date 값은 current_timestamp(현재일시)로 지정한다.

return 값은 등록된 pk 값을 지정하여 준다.

create or replace function public.fn_test_table_insert(p_title varchar)
returns integer AS
$$
    declare v_no integer := -1;
BEGIN

    -- 제목의 필수사항을 확인
    if p_title is null or length(p_title) = 0 then
        raise notice '$1 title 필수사항';
        return -1;
    end if;

    -- primary key : no 채번
    select coalesce(max(no), 0) + 1 into v_no
      from public.test_table;

    insert into public.test_table
      (no, title, create_date)
    values
      (v_no, p_title, current_timestamp);

    return v_no;

END;
$$
LANGUAGE plpgsql

"Query returned successfully with no result in 13 msec." 메세지가 표시되면 정상적으로 생성으니 실행을 해보자.

-- insert function 실행
select public.fn_test_table_insert('제목 INSERT 테스트');
select public.fn_test_table_insert('제목 INSERT 테스트2');

-- public.fn_test_table 데이터 조회
select * from public.test_table

 

3. UPDATE Function 개발

제목(title) 값을 수정해주는 UPDATE Function 개발

title 값 변경시 update_date 를 현재일시(current_timestamp)로 바꾸어 준다.

create or replace function public.fn_test_table_update(p_no integer, p_title varchar)
RETURNS integer AS
$$
declare
    v_row_count integer := 0;
    v_update_count integer  := 0;
BEGIN

    if p_no is null or p_no <= 0 then
        raise notice '$1 no 필수사항';
        return -1;
    end if;

    if p_title is null or length(p_title) = 0 then
        raise notice '$2 title 필수사항';
        return -1;
    end if;

    -- 수정할 Row가 존재하는지 조회
    select count(*) into v_row_count
      from public.test_table
     where no = p_no;

    if v_row_count = 0 then
        raise notice '수정할 Row 조회불가 $1 no = %', p_no;
        return -1;
    end if;

    update public.test_table
       set title = p_title
         , update_date = current_timestamp
     where no = p_no;

    -- update count
    GET DIAGNOSTICS v_update_count = ROW_COUNT;

    return v_update_count;

END;
$$
LANGUAGE plpgsql

Function 실행

select public.fn_test_table_update(20, 'test-update')

 

4. DELETE Function 개발

등록된 데이터를 삭제하는 DELETE Function 개발

create or replace function public.fn_test_table_delete(p_no integer)
returns integer as
$$
declare
    v_row_count integer := 0;
    v_delete_count integer := 0;
BEGIN

    if p_no is null or p_no <= 0 then
        raise notice '$1 no 필수사항';
        return -1;
    end if;

    -- 삭제할 Row가 존재하는지 조회
    select count(*) into v_row_count
      from public.test_table
     where no = p_no;

    if v_row_count = 0 then
        raise notice '삭제할 Row 조회불가 $1 no = %', p_no;
        return -1;
    end if;

    delete from public.test_table
     where no = p_no;

    -- delete count
    GET DIAGNOSTICS v_delete_count = ROW_COUNT;

    return v_delete_count;

END;
$$
LANGUAGE plpgsql

Function 실행

-- select public.fn_test_table_delete(<삭제할 no 값::integer>)
select public.fn_test_table_delete(2)

 

5. 다중 record select 조회

등록된 데이터를 목록으로 표시하는 기능이 필요할때가 있다.

목록개수 표시제한(limit)이 가능한 select 목록조회 function 개발

create or replace function public.fn_test_table_list(p_limit_count integer)
returns table
    (
        no          integer
      , title       varchar
      , create_date timestamp(0)
      , update_date timestamp(0)
    )
as
$$
declare
    v_record record;
BEGIN

    for v_record in 
    (
     select a.no
          , a.title
          , a.create_date
          , a.update_date
       from public.test_table a
      order by no desc     -- 최근에 등록된 Row를 맨위로 표시
      limit p_limit_count  -- 건수제한 Parameter 지정
    )
    loop
        -- 컬럼값을 수정하여 매핑 가능
        no          := v_record.no;
        title       := v_record.title;
        create_date := v_record.create_date;
        update_date := v_record.update_date;
        return next;
    end loop;

END;
$$
LANGUAGE plpgsql

Function 실행

select * from public.fn_test_table_list(3);
select a.no, a.title, a.create_date, a.update_date from public.fn_test_table_list(3) a;

 

6. public.test_table 의 데이터 총건수를 조회

특정 테이블에 데이터가 총 몇건인지를 조회할때 사용

단순하게 표시하거나 페이징을 처리할때 사용하기도 한다.

create or replace function public.fn_test_table_total_count()
returns integer AS
$$
declare
    v_total_count integer := 0; -- 총건수를 저장하는 변수
BEGIN

    -- 총건수 조회
    select count(*) into v_total_count
      from public.test_table;

    return v_total_count;

END;
$$
LANGUAGE plpgsql

Function 실행

select public.fn_test_table_total_count();
select public.fn_test_table_total_count() as total_count;

 

7. 페이징 목록 조회

pagination 기법이라고도 불리며 게시판에서 특정 페이지 목록을 표시할때 사용한다.

테스트가 원활히 되려면 2번의 public.fn_test_table_insert function을 30번정도 실행하자.

create or replace function public.fn_test_table_pagination_list(p_page_no integer, p_list_size integer)
returns table
(
    no          integer
  , title       varchar
  , create_date timestamp(0)
)
as
$$
declare
    v_page_no   integer := 1;
    v_offset    integer := 0;
    v_lise_size integer := 10;
BEGIN

    -- 목록개수 검증(10,20,50)
    if v_lise_size not in (10, 20, 50) then
        v_lise_size = 10;
    else
        v_lise_size = p_list_size;
    end if;
	
    -- page_no 검증
    if p_page_no is null or p_page_no < 0 then
        v_page_no = 1;
    else
        v_page_no = p_page_no;
    end if;
    
    -- offset 계산
    v_offset = (v_page_no - 1) * v_lise_size;

    return query
        -- update_date 컬럼은 표시하지 않는다.
        -- 표시할 컬럼만 조회하자.
        select a.no
             , a.title
             , a.create_date
          from public.test_table a
         order by no desc
        offset v_offset limit v_lise_size;

END;
$$
LANGUAGE plpgsql

Function 실행

-- 1페이지, 목록 20개 표시
select * from public.fn_test_table_pagination_list(1, 20);

-- 1페이지, 목록 10개 표시
select * from public.fn_test_table_pagination_list(1, 10);

-- 2페이지, 목록 10개 표시
select * from public.fn_test_table_pagination_list(2, 10);
select a.no, a.title, a.create_date from public.fn_test_table_pagination_list(2, 10) a;

 

8. 단일 record 조회

상세정보 조회기능처럼 하나의 Row 만 조회하는 기능의 Function

당연히 해당 테이블의 PK 또는 Unique Index를 인자로 받아야 조회가 가능하고 속도도 빠를 것이다.

create or replace function public.fn_test_table_detail(p_no integer)
returns table
(
    no          integer
  , title       varchar
  , create_date timestamp(0)
  , update_date timestamp(0)
)
as
$$
BEGIN

    return query
        select a.no
             , a.title
             , a.create_date
             , a.update_date
          from public.test_table a
         where a.no = p_no;

END;
$$
LANGUAGE plpgsql

Function 실행

select * from public.fn_test_table_detail(1);
select a.no, a.title, a.create_date, a.update_date from public.fn_test_table_detail(1) a;

 

해당 Function 을 잘 조합하면 웬만한 어플리케이션에서 사용가능한 Function 을 개발 할 수가 있을 것이다.

인자값은 p_ 를,  변수선언시에는 v_ 를 접두사로 사용하여 기존 테이블/컬럼명과 충돌을 방지하는 쪽으로 개발하는 것이 좋을 듯하다. (아니면 ambiguous 에러를 많이 보게될 수도 있다.)

********** Error **********
ERROR: column reference "no" is ambiguous
SQL state: 42702
Detail: It could refer to either a PL/pgSQL variable or a table column.
Context: PL/pgSQL function fn_test_table_detail(integer) line 4 at RETURN QUERY

트랜잭션, 로그 레벨, 다중 Table 반환 기능등은 추후 정리해 보도록 해야 겠다.