PL/pgSQL

❤️ PostgreSQL 기준으로 작성된 예시입니다.

짧게 쓰는 배경

주식 자동 거래 프로젝트를 진행하며 PL/pgSQL을 익혀야했다. 다만, 서점, 인터넷 강의 등 여러 곳을 찾아봐도 Oracle의 PL/sql 자료는 존재했지만, pgSQL을 찾을 수는 없었다 ㅠ^ㅠ….. 문법이 오라클과 비슷하지만 사아아알짝 달랐기때문에 공식 Document를 보며 정리해야겠다고 느꼈다. 이것도 거름이 되겠지 ㅎㅎ.

PL/pgSQL 사용 이점

기존 SQL 명령어는 데이터베이스 서버에 의해 각각 수행된다. 이 말은 클라이언트 어플리케이션이 각 쿼리를 서버로 보내야하고 계산 등 실행된 결과를 받아 오는데, 이러한 종속적인 프로세스는 클라이언트가 다른 시스템에 있을 경우 네트워크 오버헤드를 발생시킨다.
PL/pgSQL을 사용하면 계산을 블록화 시키고 데이터베이스 내에서 쿼리를 그룹화시킬 수 있다. 따라서 절차적 언어와 SQL 사용을 쉽게하면서 클라이언트/서버 통신 오버헤드를 절약할 수 있다.

• 클라이언트 ~ 서버 간 왕복 제거
• 클라이언트가 필요하지 않은 중간 결과를 서버와 클라이언트 간에 관리하거나 전송할 필요가 없음.
• 쿼리 구문 분석을 여러 번 수행하지 않을 수 있음

Structure of PL/pgSQL

Create Function

PL/pgSQL로 작성된 함수는 CREATE FUNCTION 커맨드를 실행하여 정의된다.

CREATE FUNCTION somefunc(integer, text) RETURNS integer
AS 'function body text'
LANGUAGE plpgsql;

$ 표시로 함수 Body 부분을 표현하기도 한다.
PL/pgSQL은 블록 구조의 언어인데, 함수 본문의 전체는 텍스트 블록이여야한다. 이는 다음과 같다.

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
END [ label ];

블록 내의 각 선언과 각 문은 세미콜론으로 끝난다. 위에서와 같이 다른 블록 내에 나타나는 블록에는 END 뒤에 세미콜론이 있어야 하지만 함수 본문을 끝내는 최종 END에는 세미콜론이 필요하지 않다.

📌 주의
BEGIN 뒤 세미콜론을 붙이는 경우 Syntax Error !

라벨은 EXIT 명령어에서 블록을 식별할 때 혹은 블록에서 변수를 선언할 때 필요하다. END 뒤에 라벨이 주어질때, 반드시 블록의 시작과 라벨이 일치해야한다.

대소문자

모든 키워드는 대소문자를 구별하지 않는다. 식별자는 일반적인 SQL 명령어에서와 같이 큰따옴표로 묶이지 않을 경우 소문자로 변환된다.

주석

• -- : 한 줄
• /* <text> */ : 여러 줄

상위 변수

블록 안에 서브블록도 지정 가능하다. 서브블록은 논리적으로 그룹핑되어 사용되거나 작은 그룹의 변수를 지역 변수화하는 데 사용된다. 서브블록 내 변수 선언된 변수는 서브 블록 내 이름이 유사한 외부 블록의 변수를 마스킹하지만, 블록의 레이블을 사용하여 변수 이름을 한정하는 경우에도 외부 변수에 액세스할 수 있다.

📌 요약
서브 블록 내에서 블록 밖 변수를 가지고 올 수 있다.

예시

CREATE FUNCTION somefunc() RETURNS integer AS $$
<< outerblock >>
DECLARE
    quantity integer := 30;
BEGIN
    RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 30
    quantity := 50;
    --
    -- Create a subblock
    --
    DECLARE
        quantity integer := 80;
    BEGIN
        RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 80
        RAISE NOTICE 'Outer quantity here is %', outerblock.quantity;  -- Prints 50
    END;

    RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 50

    RETURN quantity;
END;
$$ LANGUAGE plpgsql;

주의 : BEGIN/END 명령어 혼동

트랜잭션을 컨트롤하기 위한 SQL 명령어와 PL/pgSQL 그룹하기 위한 BEGIN/END 명령어를 혼동하지 않는 것이 중요하다.

🌹 PL/pgSQL의 BEGIN/END 는 그룹화 전용이며 트랜잭션을 시작하거나 종료하지 않는다.

함수와 트리거 프로시저는 항상 외부 쿼리에 의해 설정된 트랜잭션 내에서 실행된다. 이것을 실행할 컨텍스트가 없기 때문에 트랜잭션을 시작하거나 커밋할 수 없다. 그러나, EXCEPTION 을 포함한 불록은 효과적으로 하위 트랜잭션을 형성하여 외부 트랜잭션에 영향을 미치지 않고 롤백할 수 있다.
-> 이거 스스로 자동 실행되게끔 할 수 없다는 말인 것 같다. 외부 쿼리를 이용해서 함수나 트리거를 실행하도록…

Declarations

여러개 변수 선언

변수 1;
변수 2;

quantity integer DEFAULT 32;
url varchar := 'http://mysite.com';
user_id CONSTANT integer := 10;

Alias

-- 1.
CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
BEGIN
    RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

-- 2. $int 사용
CREATE FUNCTION sales_tax(real) RETURNS real AS $$
DECLARE
    subtotal ALIAS FOR $1;
BEGIN
    RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

Table Return

CREATE FUNCTION extended_sales(p_itemno int)
RETURNS TABLE(quantity int, total numeric) AS $$
BEGIN
    RETURN QUERY SELECT s.quantity, s.quantity * s.price FROM sales AS s
                 WHERE s.itemno = p_itemno;
END;
$$ LANGUAGE plpgsql;

COLLATE : 데이터 정렬

-- en_US 컬럼에 대해서 정렬 (?)
DECLARE
    local_a text COLLATE "en_US";
    
-- "C"컬럼에 대해서 정렬(?)
CREATE FUNCTION less_than_c(a text, b text) RETURNS boolean AS $$
BEGIN
    RETURN a < b COLLATE "C";
END;
$$ LANGUAGE plpgsql;

빈 결과 리턴 받기

PERFOMR query;

PERFORM create_mv('cs_session_page_requests_mv', my_query);

Query 실행 결과 Single row 로 받기

INTO 의 사용

SELECT select_expressions INTO [STRICT] target FROM ...;
INSERT ... RETURNING expressions INTO [STRICT] target;
UPDATE ... RETURNING expressions INTO [STRICT] target;
DELETE ... RETURNING expressions INTO [STRICT] target;

여기서 target은 레코드 변수, 행 변수 또는 단순 변수와 레코드/ 행 필드의 쉼표로 구분된 리스트일 수 있다. PL/pgSQL 변수가 쿼리의 나머지 부분에 대체되고 행을 반환하지 않는 명령에 대해 계획이 캐시된다. 이것은 SELECT/ INSERT/ UPDATE/ DELETE 의 RETURNING 을 위한 것으로 행 집합 결과를 반환하는 유틸리티 명령 (EXPLAIN)에 적용된다. INTO 절을 제외하고 SQL 명령은 PL/pgSQL 외부에 작성되는 것과 동일하다.

• SQL 명령어와 헷갈리지 말자. SQL의 경우는 INTO 가 INSERT 에 사용됨
• PL/pgSQL의 경우 SELECT 결과로 테이블을 만드려면 CREATE TABLE .. AS TABLE 을 사용
  

행 또는 변수 목록이 대상으로 사용되는 경우 조회 결과 열이 수와 데이터 유형에 대해 대상의 구조와 정확히 일치해야한다. 그렇지 않으면 런타임 오류가 발생. 레코드 변수가 대상인 경우 쿼리 결과 열의 행 유형에 맞게 자동으로 구성된다. INTO 절은 SQL 명령에서 자주 쓰이는데, 이 명령은 SELECT 명령의 SELECT_Expression 목록 바로 앞 또는 바로 뒤에 기록되거나 다른 명령 유형의 경우 명령 끝에 기록된다. 이후 버전에서 PL/pgSQL 파서가 엄격해질 경우 이 규칙을 따르는 것이 좋다.

STRICT가 INTO 절에 지정되지 않은 경우, 대상은 쿼리에 의해 반환된 첫 번째 행으로 설정되고 쿼리에 의해 반환된 행이 없으면 null로 설정된다. (ORDER BY를 사용하지 않은 경우 “첫 번째 행”은 잘 정의되지 않습니다.) 첫 번째 행 뒤의 모든 결과 행은 삭제되며 특수 FOUND 변수(제41.5.5절 참조)를 확인하여 행이 반환되었는지 확인할 수 있다.

SELECT * INTO myrec FROM emp WHERE empname = myname;
IF NOT FOUND THEN
    RAISE EXCEPTION 'employee % not found', myname;
END IF;

Exception

STRICT 옵션이 지정된 경우 쿼리는 정확히 한 행을 반환해야하며 그렇지 않을 경우 런타임 에러 반환. NO_DATE_FOUND(no rows) 또는 TOO_MANY_ROWS(한 행이상일 경우) 도 해당된다. Exception을 사용해서 오류 처리를 할 수 있다.

BEGIN
    SELECT * INTO STRICT myrec FROM emp WHERE empname = myname;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE EXCEPTION 'employee % not found', myname;
        WHEN TOO_MANY_ROWS THEN
            RAISE EXCEPTION 'employee % not unique', myname;
END;

print_strict_params

함수에서 이를 활성화할 경우 STRICT 요구 사항 충족되지 않아 오류 발생하면 DETAIL 부분 쿼리에 전달된 파라미터에 대한 모든 정보가 포함된다. plpgsql.print_strict_params를 설정하여 모든 함수의 print_strict_params 설정을 변경할 수 있지만 이 설정 후 함수 컴파일에만 영향을 미친다.

컴파일러 옵션을 사용하여 함수별로 설정할 수 있는데, 예시는 다음과 같다.

-- 개별함수 print_strict_parmas 설정 
CREATE FUNCTION get_userid(username text) RETURNS int
AS $$
#print_strict_params on
DECLARE
userid int;
BEGIN
    SELECT users.userid INTO STRICT userid
        FROM users WHERE users.username = get_userid.username;
    RETURN userid;
END;
$$ LANGUAGE plpgsql;

-- 실패할 경우
ERROR:  query returned no rows
DETAIL:  parameters: $1 = 'nosuchuser'
CONTEXT:  PL/pgSQL function get_userid(text) line 6 at SQL statement

결과 상태 받기

Syntax

GET [ CURRENT ] DIAGNOSTICS variable { = | := } item [ , ... ];

Example

GET DIAGNOSTICS integer_var = ROW_COUNT;

Available Diagnositcs Items

Name	Type	Description
ROW_COUNT	bigint	the number of rows processed by the most recent SQL command
RESULT_OID	oid	the OID of the last row inserted by the most recent SQL command
PG_CONTEXT	text	line(s) of text describing the currennt call stack

동적 명령어 실행

변하는 값으로 query를 얻어야할 때

Syntax

EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];

Example

EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'
   INTO c
   USING checked_user, checked_date;

-- table name 동적 변경시
EXECUTE format('SELECT count(*) FROM %I '
   'WHERE inserted_by = $1 AND inserted <= $2', tabname)
   INTO c
   USING checked_user, checked_date;

결과를 Null 로 받아야할 때

BEGIN
    y := x / 0;
EXCEPTION
    WHEN division_by_zero THEN
        NULL;  -- ignore the error
END;

반복문

Int 반복문

[ <<label>> ]
FOR name IN [ REVERSE ] expression .. expression [ BY expression ] LOOP
    statements
END LOOP [ label ];

• REVERSE : 10 9 8 7 처럼 역순으로 갈 경우
• BY expression : Like Python range(1,10,2) 2씩 건너 뜀 1,3,5,7,9 ..

예시

FOR i IN 1..10 LOOP
    -- i will take on the values 1,2,3,4,5,6,7,8,9,10 within the loop
END LOOP;

FOR i IN REVERSE 10..1 LOOP
    -- i will take on the values 10,9,8,7,6,5,4,3,2,1 within the loop
END LOOP;

FOR i IN REVERSE 10..1 BY 2 LOOP
    -- i will take on the values 10,8,6,4,2 within the loop
END LOOP;

Looping through Query Results

[ <<label>> ]
FOR target IN query LOOP
    statements
END LOOP [ label ];

target 은 record variable, row variable, 또는 컴마로 분리된 리스트 변수가 올 수 있다. target은 쿼리로부터 연속적인 행 결과가 수행된다. 또한 loop body도 각 행에 대하여 실행된다. 즉, 각 행에 대하여 for문 하나씩 돈다. 루프가 EXIT 로 끝나는 경우, 마지막으로 수행된 행을 루프가 끝난 후에도 접근할 수 있다.

예시

CREATE FUNCTION refresh_mviews() RETURNS integer AS $$
DECLARE
    mviews RECORD;
BEGIN
    RAISE NOTICE 'Refreshing all materialized views...';

    FOR mviews IN
       SELECT n.nspname AS mv_schema,
              c.relname AS mv_name,
              pg_catalog.pg_get_userbyid(c.relowner) AS owner
         FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)
        WHERE c.relkind = 'm'
     ORDER BY 1
    LOOP

        -- Now "mviews" has one record with information about the materialized view

        RAISE NOTICE 'Refreshing materialized view %.% (owner: %)...',
                     quote_ident(mviews.mv_schema),
                     quote_ident(mviews.mv_name),
                     quote_ident(mviews.owner);
        EXECUTE format('REFRESH MATERIALIZED VIEW %I.%I', mviews.mv_schema, mviews.mv_name);
    END LOOP;

    RAISE NOTICE 'Done refreshing materialized views.';
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

Looping through Arrays

FOREACH : SQL query에 의해 순차적으로 반복하는 것 대신에 array 값으로 반복한다.

[ <<label>> ]
FOREACH target [ SLICE number ] IN ARRAY expression LOOP
    statements
END LOOP [ label ];

슬라이스가 없거나 슬라이스 0이 지정된 경우 Loop는 식을 평가하여 생성된 Array의 개별요소를 통해 반복한다. SLICE 값이 양수이면 FOREACH 단일 요소가 아닌 배열의 슬라이스를 통해 반복된다. SCLIE 값은 배열 차원 수보다 크지않은 정수여야한다. 대상 변수는 배열이어야하며 배열 값의 연속 슬라이스를 수신한다. 여기서 각 슬라이스는 SLICE에 지정된 차원의 수이다. 다음은 1차원 슬라이스를 통해 반복하는 예.

예시 : SLICE 지정 X

CREATE FUNCTION sum(int[]) RETURNS int8 AS $$
DECLARE
  s int8 := 0;
  x int;
BEGIN
  FOREACH x IN ARRAY $1
  LOOP
    s := s + x;
  END LOOP;
  RETURN s;
END;
$$ LANGUAGE plpgsql;

예시 : SLICE 1

CREATE FUNCTION scan_rows(int[]) RETURNS void AS $$
DECLARE
  x int[];
BEGIN
  FOREACH x SLICE 1 IN ARRAY $1
  LOOP
    RAISE NOTICE 'row = %', x;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT scan_rows(ARRAY[[1,2,3],[4,5,6],[7,8,9],[10,11,12]]);

NOTICE:  row = {1,2,3}
NOTICE:  row = {4,5,6}
NOTICE:  row = {7,8,9}
NOTICE:  row = {10,11,12}