Redshift : procedure에서 query 돌리기

 

 

 

procedure 내에서 query를 실행하는 것도 가능합니다.

 

create or replace procedure schema.procedure_name(
    f1    in bigint,
    f2    in float,
    f3    in varchar
)
language plpgsql
as $$
    begin
        drop table if exists temp_table_1;
        create temp table temp_table_1 as
        select  bt.*
        from schema.base_table as bt
        where bt.target_id = f1
        ;
        
        drop table if exists schema.result_table;
        create table schema.result_table as
        select  *
        from temp_table_1
        ;
    end;
$$
;

 

별도의 복잡한 구문은 필요 없습니다.

위처럼 그냥 procedure body의 begin ~ end; 사이에 내가 실행할 query를 명시해주면 됩니다.

 

한 가지 중요한 점은 where bt.target_id = f1 에서 명시된 것 처럼

procedure의 parameter를 query에서도 자유롭게 사용할 수 있다는 것입니다.

 

 

 

 

begin;

call schema.procedure_name(100, 3,72, 'xyz')

end;

그리고 위처럼 procedure를 호출하면 procedure가 실행되면서 procedure에 명시된 query가 실행될겁니다.

 

위 예시에서 명시된 query는 table을 생성하는 부분이므로 별도의 결과가 없습니다.

그래서 아래처럼 procedure의 선언 후 별도의 query를 이용하여 procedure 내부에서 만들어진 temporary table, permanent table을 조회하는 것이 가능합니다.

begin;

call schema.procedure_name(100, 3.72, 'xyz')

select  *
from temp_table_1
;

select  *
from schema.result_table
;

end;

 

 

 

 

 

만약 procedure의 호출의 결과에 바로 query 결과를 호출하고 싶으면 refcursor를 사용하면 됩니다.

 

create or replace procedure schema.procedure_name(
    f1    	in bigint,
    f2    	in float,
    f3    	in varchar,
    rs_out	inout refcursor
)
language plpgsql
as $$
    begin
        drop table if exists temp_table_1;
        create temp table temp_table_1 as
        select  bt.*
        from schema.base_table as bt
        where bt.target_id = f1
        ;
        
        drop table if exists schema.result_table;
        create table schema.result_table as
        select  *
        from temp_table_1
        ;
        
        open rs_out for
        select	*
        from schema.result_table
        ;
    end;
$$
;

 

위 예시를 보면 procedure body 아래 부분에 open rs_out for~~라는 내용과 schema.result_table을 select하는 구문이 추가되었습니다.

 

간단히 설명하면

open ~~은 어떠한 변수를 열어서 출력하라는 의미이며

for 뒤에 있는 데이터를 rs_out이라는 변수에 할당해서 넣으라는 것입니다.

 

그리고 procedure 선언 부분을 보면 rs_out이라는 변수가 refcursor라는 type으로 선언되었습니다.

데이터를 저장하는 변수라는 의미입니다.

그리고 이 변수는 데이터를 받아 저장하는 입력(in)과

받은 데이터를 출력(out)할 수 있어야하기에 in(입력)이 아닌 inout(입출력)으로 명시해준 것도 볼 수 있습니다.

 

 

참고 문서

https://docs.aws.amazon.com/ko_kr/redshift/latest/dg/stored-procedure-result-set.html

저장 프로시저에서 결과 세트 반환 - Amazon Redshift