- 전체
- Sample DB
- database modeling
- [표준 SQL] Standard SQL
- G-SQL
- 10-Min
- ORACLE
- MS SQLserver
- MySQL
- SQLite
- postgreSQL
- 데이터아키텍처전문가 - 국가공인자격
- 데이터 분석 전문가 [ADP]
- [국가공인] SQL 개발자/전문가
- NoSQL
- hadoop
- hadoop eco system
- big data (빅데이터)
- stat(통계) R 언어
- XML DB & XQuery
- spark
- DataBase Tool
- 데이터분석 & 데이터사이언스
- Engineer Quality Management
- [기계학습] machine learning
- 데이터 수집 및 전처리
- 국가기술자격 빅데이터분석기사
- 암호화폐 (비트코인, cryptocurrency, bitcoin)
[postgreSQL] [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 구문에서 90694로 해당 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개가 넘어가면 에러가 발생하게 된다.
[출처] https://sungtae-kim.tistory.com/40
광고 클릭에서 발생하는 수익금은 모두 웹사이트 서버의 유지 및 관리, 그리고 기술 콘텐츠 향상을 위해 쓰여집니다.