Amazon Redshift의 저장 프로시저 개요 - Amazon Redshift

Amazon Redshift의 저장 프로시저 개요

저장 프로시저는 일반적으로 데이터 변환 로직, 데이터 검증 및 비즈니스별 로직을 캡슐화하는 데 사용됩니다. 여러 SQL 단계를 저장 프로시저로 결합함으로써 애플리케이션과 데이터베이스 간 왕복 횟수를 줄일 수 있습니다.

세분화된 액세스 제어를 통해 사용자에게 기본 테이블에 대한 액세스 권한을 부여하지 않고도 함수를 수행하도록 저장 프로시저를 생성할 수 있습니다. 예를 들어 소유자 또는 수퍼유저만 테이블을 자를 수 있고, 사용자가 데이터를 테이블에 삽입하려면 쓰기 권한이 필요합니다. 사용자에게 기본 테이블에 대한 권한을 부여하는 대신에, 작업을 수행하는 저장 프로시저를 생성할 수 있습니다. 그런 다음 사용자에게 저장 프로시저를 실행할 권한을 부여합니다.

DEFINER 보안 속성이 있는 저장 프로시저는 저장 프로시저의 소유자 권한으로 실행됩니다. 기본적으로 저장 프로시저에는 INVOKER 보안이 있습니다. 이는 프로시저가 프로시저를 호출하는 사용자의 권한을 사용함을 뜻합니다.

저장 프로시저를 생성하려면 CREATE PROCEDURE 명령을 사용합니다. 프로시저를 실행하려면 CALL 명령을 사용합니다. 이 단윈의 뒷부분에 예제가 나와 있습니다.

참고

일부 클라이언트는 Amazon Redshift 저장 프로시저를 생성할 때 다음 오류를 표시할 수 있습니다.

ERROR: 42601: [Amazon](500310) unterminated dollar-quoted string at or near "$$

이 오류는 클라이언트가 문을 구분하는 세미콜론과 달러 기호($) 인용으로 CREATE PROCEDURE 문을 올바르게 구문 분석할 수 없어 발생합니다. 그 결과 문의 일부만 Amazon Redshift 서버로 전송됩니다. 보통 클라이언트의 Run as batch 또는 Execute selected 옵션을 사용하여 이 오류를 해결할 수 있습니다.

예를 들어 Aginity 클라이언트를 사용하는 경우 Run entire script as batch 옵션을 사용합니다. SQL Workbench/J를 사용하는 경우 버전 124를 사용하는 것이 좋습니다. SQL Workbench/J 버전 125를 사용하는 경우 차선책으로서 대체 구분 기호를 지정하는 것을 고려합니다.

CREATE PROCEDURE에는 세미콜론(;)으로 구분된 SQL 문이 포함되어 있습니다. 슬래시(/) 등의 대체 구분 기호를 정의하고 CREATE PROCEDURE 문의 끝에 이를 배치하면 처리를 위해 문이 Amazon Redshift 서버에 전송됩니다. 다음은 한 예입니다.

CREATE OR REPLACE PROCEDURE test()
AS $$
BEGIN
  SELECT 1 a;
END;
$$
LANGUAGE plpgsql
;
/

자세한 내용은 SQL Workbench/J 설명서의 Alternate delimiter를 참조하십시오. 또는 Amazon Redshift 콘솔의 쿼리 편집기 또는 TablePlus와 같이 CREATE PROCEDURE 문의 구문 분석을 더 잘 지원하는 클라이언트를 사용합니다.

주제

다음 예제에서는 출력 인수가 없는 프로시저를 보여 줍니다. 기본적으로 인수는 입력(IN) 인수입니다.

CREATE OR REPLACE PROCEDURE test_sp1(f1 int, f2 varchar)
AS $$
BEGIN
  RAISE INFO 'f1 = %, f2 = %', f1, f2;
END;
$$ LANGUAGE plpgsql;

call test_sp1(5, 'abc');
INFO: f1 = 5, f2 = abc
CALL

참고

저장 프로시저를 작성할 때는 민감한 값을 보호하기 위한 모범 사례를 참조하는 것이 좋습니다.

민감한 정보를 저장 프로시저 로직 내에 하드 코딩하지 마세요. 예를 들어, 저장 프로시저 본문의 CREATE USER 문에 사용자 암호를 할당하지 마십시오. 하드 코딩된 값이 카탈로그 테이블에 스키마 메타 데이터로 기록될 수 있기 때문에 보안 위험이 따릅니다. 암호와 같은 민감한 값은 파라미터를 사용하여 저장 프로시저에 인수로 전달하십시오.

저장 프로시저에 대한 자세한 내용은 CREATE PROCEDURE 및 Amazon Redshift에서 저장 프로시저 생성 섹션을 참조하세요. 카탈로그 테이블에 대한 자세한 내용은 시스템 카탈로그 테이블 섹션을 참조하세요.

다음 예제에서는 출력 인수가 있는 프로시저를 보여 줍니다. 인수는 입력(IN), 입력 및 출력(INOUT), 출력(OUT)입니다.

CREATE OR REPLACE PROCEDURE test_sp2(f1 IN int, f2 INOUT varchar(256), out_var OUT varchar(256))
AS $$
DECLARE
  loop_var int;
BEGIN
  IF f1 is null OR f2 is null THEN
    RAISE EXCEPTION 'input cannot be null';
  END IF;
  DROP TABLE if exists my_etl;
  CREATE TEMP TABLE my_etl(a int, b varchar);
    FOR loop_var IN 1..f1 LOOP
        insert into my_etl values (loop_var, f2);
        f2 := f2 || '+' || f2;
    END LOOP;
  SELECT INTO out_var count(*) from my_etl;
END;
$$ LANGUAGE plpgsql;


call test_sp2(2,'2019');

         f2          | column2
---------------------+---------
 2019+2019+2019+2019 | 2
(1 row)