PostgreSQL | PL/pgSQL를 이용한 Function 생성

1. 함수생성

개발환경 : DBeaver22.3.2

 

function 생성하면 아래와같이 기본적으로 생성된다

CREATE OR REPLACE FUNCTION 함수명()
	RETURNS int4
	LANGUAGE sql
AS $function$
	begin
    
    함수내용
    
	END;
$function$
;

 

만약 변수를 사용하고싶다면

Language를 plpgsql로 변경해야함!

2. 변수

1) language 설정

declare 에 변수선언을 하려면 

language를 plpgsql로 변경해야함

CREATE OR REPLACE FUNCTION 함수명()
	RETURNS int4
	LANGUAGE plpgsql
AS $function$
	declare
              변수 타입;
	begin
    
              함수내용
    
	END;
$function$
;

 

2) 변수 선언

declare 아래에 작성하면됨

CREATE OR REPLACE FUNCTION 함수명()
	RETURNS int4
	LANGUAGE plpgsql
AS $function$
	declare
              name varchar;
              age numeric;
              time timestamp;
	begin
    
              함수내용
    
	END;
$function$
;

 

3) 변수 값지정

begin과 end 사이에서 작성하면 됨

begin

    select a into 변수명
    from 테이블;

    또는

    select a
    from 테이블
    into 변수명;

    또는

    변수명 := 값;

end

3. 테스트 로그출력

변수안에 든 값이 어떤값으로 나오고있는지 확인하려면

우선 함수를 저장한 후에

새 script 를 생성후 select 함수명(); 을 통해 실행하면

raise info에서 설정한 값이 출력되는데

% 가 변수를 지칭하는 표현이다

RAISE INFO '문자열 %',변수;

 

info말고도 NOTIC, WARNING 이 있다

 

RAISE INFO

: Function 실행시, 파라미터값 표시할때, Function 내 여러 쿼리가 있을 경우 위치를 표시할때

 

RAISE NOTICE

: Temp Table을 생성하거나 Analyze, Vacuum 같이 관리를 위한 구문실행시 사용
추후 문제가 생기는 상황을 예측할 수 있을때 공지하는 용도로 사용

 

RAISE WARNING

:Function 실행이 특정조건 때문에 더 이상 진행되지 않을때 사용하여 정확한 경고를 주는 경우에 사용
  - 참조하는 테이블에 데이터가 없거나 데이터를 생성할 수가 없는 상황을 알려주는 용도로 사용
  - 파라미터의 값이 허용하는 조건에 맞지 않아 Function 을 진행할 수 없을때 사용

 

4. 사용법

select 함수명()

5. 예제코드

CREATE OR REPLACE FUNCTION "NAISYS".func_insert_itl_abac_control_sub()
	RETURNS int4
	LANGUAGE plpgsql
AS $function$
	declare
		msurDt timestamp;
		t numeric;
		minsrt1 numeric;
		minsrt2 numeric;

	begin
		/* select 방식*/
		select INTEL1.tag_6 FROM sbswco_meas_mstr04 MS
		LEFT JOIN (
		    SELECT * 
		    FROM "NAISYS".sbswco_meas_mstr04
			WHERE MEAS_DTM BETWEEN TO_TIMESTAMP(TO_CHAR(CURRENT_TIMESTAMP - '5MINUTES'::INTERVAL, 'YYYY-MM-DD HH24:MI'), 'YYYY-MM-DD HH24:MI') AND CURRENT_TIMESTAMP
		    AND RCS_ID = 'INTEL1' 
		    AND DATA_TYP = 'AI'
		) INTEL1 ON INTEL1.MEAS_DTM = MS.MEAS_DTM
		order by MS.meas_dtm desc limit 1 
		into t;
        
        /* 직접대입 방식*/
        minsrt1 := 20;
        minsrt2 := minsrt1 + 222;

	
		/*--------------------------------------------------------------------------------------------------------------- */
	   	/*데이터 최종 INSERT 문*/
		INSERT INTO "NAISYS".sbswrt_itl_abac_control_sub (
			msur_dt,
			min_srt1,
			min_srt2,

		) VALUES(
			msurDt,
			nh3sp1,
			nh3sp2,
		) ON CONFLICT (msur_dt) DO NOTHING;
	   
	   RAISE NOTICE '값은 %', nh3sp1;
		
	
	END;
$function$
;

 

 

 

 

 

 

참고한 블로그

https://wylee-developer.tistory.com/61

[Greenplum] Function 개발 및 디버깅을 위한 RAISE 로깅 사용방법