Chapter 24. PL/pgSQL - SQL Procedural Language

목차

24.1. 개요
24.1.1. PL/pgSQL 사용의 잇점
24.1.2. PL/pgSQL 에서  개발
24.2. 세부내용
24.2.1. PL/pgSQL 구조
24.2.2. 주석문(Comment)
24.2.3. 변수와 상수
24.2.4. 표현
24.2.5. 문장
24.2.6. 제어문
24.2.7. RECORD 와 작업
24.2.8. 작업중단과 메세지
24.2.9. 예외
24.3. Trigger Procedures
24.4. 예제
24.5. Oracle PL/SQL로부터 변환
24.5.1. 주요 차이점
24.5.2. 변환 Function
24.5.3. Procedures
24.5.4. Packages
24.5.5. 주의해야할 기타사항
24.5.6. 부록 I
24.5.7. 부록 II - PL/pgSQL을 이용한 자바코드

PL/pgSQL 는 Postgres 데이터베이스를 위한 적재가능한 절차형 언어입니다.

24.1. 개요

PL/pgSQL의 디자인의 목표는  다음을 위한 function과 trigger procedure를 생성하고 사용할 수 있는 적재가능한 절차형 언어를 만드는데 있다.

•  function과 trigger procedure 생성
•  SQL 언어에 제어 구조의 추가
•  복잡한 계산의 수행
•  모든 사용자 정의형, 함수, 연산자의 계승
•  서버에 의해 신뢰받을 수 있는 정의
•  사용의 용이

PL/pgSQL 호출 처리기(call handler)는 함수의 소스 코드를 분석하고 그 함수가 처음 호출될 때 내부 바이트코드를 생성한다. 생성된 바이트코드는 함수의 오브젝트 ID를 이용해 호출 처리기에 의해서 각각 구분된다.  이 같은 구조는 DROP/CREATE sequence에 의해 함수의 변경이 이루어질 때 데이터베이스에 대한 새로운 연결 요구없이 이루어지도록 한다.

함수에서 사용된 모든 표현과 SQL 문장에 대해, PL/pgSQL 바이트코드 해석기는 SPI manager의 SPI_prepare() 와 SPI_saveplan() 함수를 이용해서 준비된 실행계획(prepared execution plan) 을 생성한다. 이러한 과정은 PL/pgSQL 함수 안에 각각의 함수의 문장이 최초로 처리될 때 이루어진다. 그래서, 함수는(필요한 실행계획을 포함하는 조건 코드를 가지고 있음) 데이터베이스의 연결이 되어있는 동안만 실제 사용될 실행계획들을 준비하고 저장한다.

그러니까 사용자는 사용자 정의 함수를 만들 때 위의 사항을 유념해야한다.
예를 들면

CREATE FUNCTION populate() RETURNS INTEGER AS '
DECLARE
    -- Declarations
BEGIN
    PERFORM my_function();
END;
' LANGUAGE 'plpgsql';

만약에 사용자가 위의 함수를 만들었다면, 서버는 그 바이트 코드에 my_function()에 대한 OID를 참조할 것입니다. 후에 사용자가 my_function()을 지우고 재생성하였다면,  populate()는 my_function()를 더이상 찾지 못하게 된다. 그러니까 my_function()을 지우고 재생성한 후 populate()도 재생성 해야 한다는 뜻이다.

PL/pgSQL이 실행 계획을 이와같이 처리하기 때문에, PL/pgSQL내에 직접적으로 사용되는 쿼리는 매 실행마다 같은 테이블과 필드를 참조해야만 한다. 이 말은 사용자는 함수의 파라메터를 쿼리에서 사용할 테이블 또는 필드명으로 사용할 수 없다는 것이다. 이 제한을 피하기 위해서 사용자는 PL/pgSQL의 EXECUTE 문을 이용하여 동적 쿼리 (dynamic query)를 사용할 수 있다. -- 그렇지만 매 수행시마다 새로운 쿼리 계획을 만드는 댓가를......

사용자 정의형에 대한 입출력 변환과 계산 함수를 제외하고, C언어 함수에 정의될 수 있는 어떤 형이라도 PL/pgSQL과 함께 사용될 수 있다. 복잡한 조건 계산 함수와 나중에 그 형을 이용하여 연산자를 정의하거나 기능형 index(functional index)에 그 형을 사용할 수 있다.

24.1.1. PL/pgSQL 사용의 잇점

• 더 나은 성능
• SQL 지원
• 이동가능성 또는 이식성 (Portability)

24.1.1.1. 더 나은 성능

SQL은 PostgreSQL (그리고 거의 모든 관계형 데이터베이스)에서 질의 언어(query language)로 사용하는 언어이다. 이 언어는 옮기는 것이 가능하고 배우기 쉽다. 그렇지만 모든 SQL문은 반드시 각각 데이터베이스에 의해 수행되야만 한다. 

그 말은 사용자의 클라이언트는 매 쿼리를 데이터베이스 서버로 전송해야 한다는 말이다.
그래서 그것이 처리되는 동안 기다렸다가 결과를 수신하고, 결과를 토대로 좀 계산을 한다음에 서버로 또다른 쿼리를 보낸다. 이러한 모든 과정은 내부 프로세스 통신을 유발하고 또한 사용자 클라이언트가 데이터베이스 서버와 다른 장비에 있다면 네트워크의 부담을 줄 수 있다.

PL/pgSQL로 사용자는 복수의 계산과 데이터베이스 내의 쿼리들을 모음으로써, 그러니까 절차형 언어의 강력함과 SQL의 사용 용이성과 함께 클라이언트/서버의 통신 부담을 유발하지 않으므로 처리 시간을 단축한다. 그렇기 때문에 PL/pgSQL을 이용하여 사용자는 눈에 띄는 성능 향상을 가져 올 수 있다.

24.1.1.2. SQL 지원

PL/pgSQL은 SQL의 유연성과 용이성에 더해 절차형 언어의 강력함도 가지고 있다. PL/pgSQL로 사용자는 SQL의 모든 자료형(datatype), Column, 연산자와 함수를 사용할 수 있다.

24.1.1.3. 이동가능성 또는 이식성?? (Portability)

PL/pgSQL 함수는 PostgreSQL에서 수행되기 때문에, 이 함수들은 PostgreSQL이 있는 다른 어떤 환경에서도 수행된다. 그렇게 해서 사용자는 코드를 재사용할 수 있고 개발비를 절감할 수 있다. 

24.1.2. PL/pgSQL 에서 개발

PL/pgSQL에서 개발은 상당히 직선적이고, 특히 사용자가 Oracle의 PL/SQL과 같은 다른 데이터베이스의 절차 언어를 개발해본 경험이 있다면  PL/pgSQL에서 개발을 위한 2가지의 좋은 방안이 있다. 그 것은...

• 텍스트 편집기를 이용한 화일을 psql을 통해 적재하는 방식
• PostgreSQL의 GUI 도구인 pgaccess을 사용하기

첫번째 경우는 사용이 간단한 사용자가 원하는 텍스트 편집기를 사용하여 사용자 함수를 만들고 다른 콘솔에서 psql을 이용하여 그 함수들을 적재하는 방식이다. 만일 사용자가 이 방식 을 사용한다면 (또는 사용자가 초보 또는 디버그 중 이면) 항상 함수를 데이터베이스로 적재하기 전에 이미 데이버베이스 안에 있는 기존 함수를 제거해야한다. 예를 들면

drop function testfunc(integer);
create function testfunc(integer) return integer as '
    ....
end;
' language 'plpgsql';

사용자가 처음 위의 함수가 있는 화일을 적재할 때, PostgreSQL은 이 함수가 없다고 경고 메세지를 보여주고나서 그 함수를 생성할 것이다. SQL 화일 (filename.sql)을 "dbname"이라는 데이터베이스로 적재할 경우 다음의 명령을 사용한다.

psql -f filename.sql dbname

두번째의 경우는 PostgreSQL의 GUI 도구인 pgaccess를 사용하는 것이다. 이것은 escaping single-quote 와 같이 편리한 점이 있고, 재생성과 함수 디버그에 편리한 장점이 있다. 

24.2 세부 사항

24.2.1. PL/pgSQL 구조

PL/pgSQL는 블록 구조의 언어다. 모은 키워드나 식별자는 대.소문자를 섞어 사용할 수 있다. 한 블록은 다음과 같이 정의한다.

[<<label>>]
[DECLARE
    변수 선언들 ]
BEGIN
    실행할 문장
END;

블록의 '실행할 문장' 부분에는 얼마든지 하위-블록이 올 수 있다. 하위-블록은 외부 블록의 문장으로부터 변수를 숨기는데 사용될 수 있다. 

변수는 'DECLARE' 부분에서 선언되고 초기화되는데 그 초기값을 줄 수 있고, 해당 블럭을 진입할 때마다 초기화 되는데 이것은 함수가 호출될 때 단 1번 수행되는 것이 아니다. 예를 들면...

CREATE FUNCTION somefunc() RETURNS INTEGER AS '
DECLARE
   quantity INTEGER := 30;
BEGIN
   RAISE NOTICE ''Quantity here is %'',quantity;  -- Quantity here is 30
   quantity := 50;
   --
   -- 하위-블럭의 생성
   --
   DECLARE
      quantity INTEGER := 80;
   BEGIN
      RAISE NOTICE ''Quantity here is %'',quantity;  -- Quantity here is 80
   END;

   RAISE NOTICE ''Quantity here is %'',quantity;  -- Quantity here is 50
END;
' LANGUAGE 'plpgsql';

위의 내용을 잘 이해해서 BEGIN/END 사용에 혼선이 없기를 바란다.  BEGIN/END는 트랜젝션을 제어하는 데이터베이스 명령과 PL/pgSQL의 문장을 한 데 묶는 역할을 한다. PL/pgSQL의 BEGIN/END 는 오로지 문장을 한데 묶는데 사용한다. 그것들은 실제 트랜젝션의 시작과 끝이 아니다. function과 trigger procedure는 항상 외부 질의(outer query)에 의해 제공된 트렌젝션 내에서만 수행된다. 그 말은 Postgres는 중첩된 트렌젝션 기능이 없어서 스스로 트렌젝션을 시작하고, commit할 수 없다는 뜻이기도 하다.

24.2.2. 주석문

PL/pgSQL에는 두가지 형태의 주석문이 있다. 
이중 빼기표시 '--'는 현재 위치부터 문장의 끝까지가 주석문이라는 의미이다.
그리고 '/*' 표시는 '*/'을 만날 때까지가 주석문이라는 의미이다. 
주석문은 중첩될 수 없으나 이중 빼기표시 '--'는 이중빼기 표시 주석문의 내부나 '/*' '*/' 주석문 사이에 놓일 수 있다. 

24.2.3. 변수와 상수

블럭 내에서 혹은 하위-블럭 내에서 사용할 모든 변수, row와 record는 반드시 'DECLARE'부 에서 선언되어야 한다. 그러나 FOR 반복문에서 사용되는 정수범위를 가진 반복용 변수는 예외이다. 

PL/pgSQL 변수는 INTEGER, VARCHAR, CHAR와 같은 어떠한 SQL 자료형을 가질 수 있다. 모든 변수는 SQL NULL 값을 초기값으로 갖는다. 

아래는 변수 선언의 몇가지 예를 들어본 것이다.

user_id INTEGER;
quantity NUMBER(5);
url VARCHAR;

24.2.3.1. 상수와 초기치를 갖는 변수

선언은 다음과 같은 문법을 가진다. 

변수이름 [ CONSTANT ] 변수형 [ NOT NULL ] [ { DEFAULT | := } 값 ];

CONSTANT를 사용하여 정의된 변수의 값은 변경할 수 없다. 만일 변수를 선언할 때 NOT NULL을 사용했을 때 그 변수에 NULL값을 할당하게 되면 실행시간 에러가 발생한다. 변수를 NOT NULL로 선언하였으면 반드시 초기값을 명시해야 한다. 그 이유는 모든 변수의 초기값은 SQL NULL이기 때문이다.

변수에 할당된 초기값은 함수가 호출될 때마다 평가된다. 그래서 timestamp형의 변수에 'now'라는 것을 할당하는 것은 함수가 실제 호출될 순간의 시간이 저장되는 것이지 함수가 바이트코드로 해석될 때의 시간이 저장되는 것이 아니다.

예)

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

24.2.3.2. 함수로 넘겨지는 변수

함수로 넘겨지는 변수는 $1, $2 등등으로 이름 지어진다. (최대 16) 몇가지 예를 보자.

CREATE FUNCTION sales_tax(REAL) RETURNS REAL AS '
DECLARE
    subtotal ALIAS FOR $1;
BEGIN
    return subtotal * 0.06;
END;
' LANGUAGE 'plpgsql';


CREATE FUNCTION instr(VARCHAR,INTEGER) RETURNS INTEGER AS '
DECLARE
    v_string ALIAS FOR $1;
    index ALIAS FOR $2;
BEGIN
    -- 계산식등이 온다.
END;
' LANGUAGE 'plpgsql';

24.2.3.3. 속성

%TYPE 과 %ROWTYPE 이라는 속성을 사용함으로써 사용자는 다른 데이터베이스의 자료형 또는 구조를 사용하여 변수를 정의 할 수 있다.

%TYPE
%TYPE을 이용하여 변수 또는 데이터베이스 칼럼의 자료형을 변수의 선언에 사용할 수 있다. 그래서 이 것을 이용하여 데이터베이스의 값을 갖는 변수를 선언할 수 있다. 예를 들면 사용자가 user_id라는 이름의 칼럼을 가지는 users라는 테이블이 있다고 하자. 이 때 테이블 users와 같은 자료형을 갖는 변수를 선언하려면...

user_id users.user_id%TYPE;

%TYPE을 사용함으로써 사용자가 사용하는 데이터베이스 구조체의 자료형을 알 필요가 없다. 그러나 이를 이용함으로써 얻을 수 있는 가장 큰 중요한 점은 만일 미래에라도 참조하고 있는  요소의 자료형이 바뀐다하더라도 사용자는 함수의 정의부분을 바꾸어줄 필요가 없다는 것이다.

변수이름 테이블이름%ROWTYPE;

위의 문장은 주어진 테이블의 구조와 함께 행(Row)을 선언하는 것이다. "테이블이름"은 반드시 데이터베이스에 존재하는 테이블이거나 Viwe의 이름이다. 그 행의 필드는 점 "."을 이용하여 참조를 할 수 있다. 함수의 매개변수는 복합형이 될 수 있다. (테이블 행 통째의) 이 경우, 연관된 식별자 $n은 행의 형일 것이지만 사용자는 사용할 때 ALIAS를 사용하여 이름을 변경해야 한다. 

오로지 테이블 행의 사용자 속성은 그 행에서만 사용할 수 있다. OID 또는 다른 시스텡의 속성은 사용할 수 없다. (왜냐하면 행은 View에서도 올수 있으니까..) 이 행의 자료형(rowtype)의 필드는 테이블의 필드의 크기와 char()의 정밀도 등 자료형을 계승받는다.

DECLARE
    users_rec users%ROWTYPE;
    user_id users%TYPE;
    /*  바로 위의 코드는 잘못된듯.. user_id users.user_id%TYPE;
   가 되야 하지 않을까 */
BEGIN
    user_id := users_rec.user_id;
    ...

create function cs_refresh_one_mv(integer) returns integer as '
   DECLARE
        key ALIAS FOR $1;
        table_data cs_materialized_views%ROWTYPE;
   BEGIN
        SELECT INTO table_data * FROM cs_materialized_views
               WHERE sort_key=key;

        IF NOT FOUND THEN
           RAISE EXCEPTION ''View '' || key || '' not found'';
           RETURN 0;
        END IF;

        -- cs_materialized_views의 mv_name 
        -- 칼럼은 view의 이름을 저장한다.
 
        TRUNCATE TABLE table_data.mv_name;
        INSERT INTO table_data.mv_name || '' '' || table_data.mv_query;

        return 1;
end;
' LANGUAGE 'plpgsql';

24.2.3.4. 변수이름 변경

RENAME을 사용하여 사용자는 변수, 레코드 또는 행의 이름을 바꿀 수 있다. 이것은 NEW 또는 OLD가 trigger procedure 내에서 다른 이름에 의해 참조될 때 유용하다.

문법과 예: 

RENAME oldname TO newname;

RENAME id TO user_id;
RENAME this_var TO that_var;

24.2.4. 표현

PL/pgSQL 문장에서 사용하는 모든 표현은 다른 배후 실행기(backend executor)에 의해 처리된다. 상수를 포함한 표현은 실제로 실행시간 중에 평가가 필요하다. (예: timestamp형의 'now'와 같은 경우) 그래서 PL/pgSQL 분석기(parser)로서는 NULL 키워드와 real의 상수를 구분할 방법이 없다. 
모든 표현은 내부적으로 SPI 관리자를 통하여 다음과 같은 쿼리를 실행함으로써 평가된다.

SELECT 표현식

위의 '표현식'에서 사용되는 변수는 매개변수로 치환이 되고 매개변수 배열에 있는 실제 값이 실행기로 넘겨진다. PL/pgSQL 함수에서 사용되는 표현식은 오직 한번 번역되고 저장된다. 오직 예외가 있다면 쿼리가 매번 바뀔경우에 사용되는 EXECUTE 명령어를 사용할 때이다. 

형의 검사는 Postgres의 주 분석기에 의해 이루어지는데 이에는 상수값의 해석에 영향을 끼친다. 자세하게 설명하자면 아래의 두 함수에는 차이점이 있다.

CREATE FUNCTION logfunc1 (text) RETURNS timestamp AS '
    DECLARE
        logtxt ALIAS FOR $1;
    BEGIN
        INSERT INTO logtable VALUES (logtxt, ''now'');
        RETURN ''now'';
    END;
' LANGUAGE 'plpgsql';
and 
CREATE FUNCTION logfunc2 (text) RETURNS timestamp AS '
    DECLARE
        logtxt ALIAS FOR $1;
        curtime timestamp;
    BEGIN
        curtime := ''now'';
        INSERT INTO logtable VALUES (logtxt, curtime);
        RETURN curtime;
    END;
' LANGUAGE 'plpgsql';

logfunc1()의 경우, Postgres 주 분석기는 INSERT를 해석할 때, logtable의 해당 필드가 timestamp형이므로 문자열 'now'가 timestamp형으로서 해석되어야 한다는 것을 안다. 그래서 주 분석기는 처음 logfunc1()이 호출되는 시점에서 이를 상수로 치환하고 이렇게 해석된 값이 이 함수가 살아있는 동안 유지가 된다.
말할 필요도 없이 이것은 프로그래머가 원하는 방향이 아니다.

logfunc2()의 경우, Postgres 주 분석기는 'now'가 무엇이 되어야 하는지 알 수 없다.
그래서 주 분석기는 'now'를 문자열로 인식하고 있다가 지역변수인 curtime에 할당될 때 PL/pgSQL 해석기는 'now'를 text_out() 과 timestamp_in() 을 호출하여 timestamp 형으로 변경한다.

이러한 Postgres 주 분석기의 형검사는 PL/pgSQL가 거의 완성된 후에 구현이 되었다. 그래서 6.3과 6.4 버젼 사이에 차이가 있으며 SPI 관리자를 사용하여 해석기능을 이용하는 모든 함수들에 영향을 끼친다. 현재의 PL/pgSQL에서는 지역변수를 사용하는 위의 경우에서만 정확하게 해석할 수 있는 유일한 방법이다.

만일 표현식이나 문장 내에서 레코드의 필드가 사용될 경우, 각각의 호출이 이루어지는 동안 필드의 자료형은 변경되어서는 안된다. 만일 trigger procedures를 만들 때와 같이 복수개의 테이블을 위한 이벤트를 다루는 경우에는 이점을 유념해야 할 것이다.

24.2.5. 문장

다음에 나오는 경우와 같이 PL/pgSQL 분석기에 의해 해석되지 않는 부분은 쿼리에 놓여져 그대로 데이터베이스로 실행을 위해 보내진다. 쿼리의 결과는 어떤 값도 반환하지 않는다.

24.2.5.1. 값의 대입(할당)

변수 또는 행/레코드 필드로 값을 대입하는 것은 다음과 같이 쓴다.

식별자 := 표현식;

만일 표현식의 결과값이 변수의 자료형과 일치하지 않으면 또는 그 변수에 크기나 정밀도가 명시되어 있다면 (예를 들어 char(20)), 결과 값은 PL/pgSQL 바이트코드 번역기에 의해 강제적으로 변경된다. 이러한 경우는 내제적으로 실행시간 오류를 야기할 수 있다.

user_id := 20;
tax := subtotal * 0.06;

24.2.5.2. 다른 함수의 호출

Postgres 데이터베이스에 정의된 모든 함수는 값을 반환한다. 그렇기에 일반적으로 함수를 호출하는 것은 SELECT 쿼리를 실행하거나 값의 대입이다.

그렇지만 몇몇 사람은 함수의 결과를 필요로 하지 않는 경우도 있다. 이 경우엔 PERFORM 문을 사용한다.

PERFORM 쿼리문

위의 문장은 SPI manager를 통해 SELECT 쿼리를 실행하고 반환값을 삭제한다. 지역변수와 같은 식별자는 여전히 함수 파라메터로 치환된다.

PERFORM create_mv(''cs_session_page_requests_mv'',''
     select   session_id, page_id, count(*) as n_hits,
              sum(dwell_time) as dwell_time, count(dwell_time) as dwell_count
     from     cs_fact_table
     group by session_id, page_id '');

24.2.5.3. 동적 쿼리의 실행

때때로 PL/pgSQL함수 내에서 동적 쿼리를 생성하고자 할 때가 있다. 또는 다른 함수를 생성하는 함수를 가질 수도 있다. PL/pgSQL는 이러한 경우를 위해 EXECUTE 문을 제공한다.

EXECUTE query-string

위에서 "query-string"은 실행할 쿼리를 포함하는 문자열이다.

동적 쿼리를 이용하여 작업할 때에, PL/pgSQL에서 작은 따옴표에 대한 문제에 직면하게 되는데 이에 대한 수고를 덜어주기 위해 자세한 설명이 있는 "Oracle PL/SQL에서 PL/pgSQL로의 변환"에 있는 표를 참조하기 바란다.

PL/pgSQL의 다른 쿼리와는 다르게 EXECUTE문에 의해 수행되는 쿼리는 다른 일반 쿼리와 같이 처음 실행될 때 번역과 저장 작업이 이루어져 서버가 살아 있는 내내 존재하는 것이 아니다. 쿼리 문자열은 다양한 테이블과 필드에서 작업을 수행할 수 있도록 프로시져 내에서 생성된다.

SELECT 쿼리의 결과는 EXECUTE에 의해 버려진다. 그리고 현재까지는 EXECUTE내에서 SELECT INTO는 사용할 수 없다 그래서 동적으로 생성된 SELECT에서 결과는 뽑는 유일한 방법은 FOR...EXECUTE 형식을 사용하는 것인데 이는 추후에 설령을 하겠다.

예) 

EXECUTE ''UPDATE tbl SET ''
        || quote_ident(fieldname)
        || '' = ''
        || quote_literal(newvalue)
        || '' WHERE ...'';

이 예에서 quote_ident(TEXT) 함수와  quote_literal(TEXT) 함수를 사용하였다. 문자열 변수가 필드나 테이블의 이름을 포함하고 있으면 반드시 그 내용을 quote_ident()로 넘겨야 한다. 또한 변수가 동적 쿼리의 문자열의 문자요소를 포함하고 있으면  반드시 그 내용을 quote_literal()로 넘겨야 한다. 앞의 두경우 모두 작은 따옴표 또는 큰 따옴표에 둘러싸인 문자열 그리고 특수문자와 함께 반환하기 위해서는 적절한 절차를 밟아야 한다.

다음은 좀 더 큰 동적쿼리와 EXECUTE를 사용한 예이다.

CREATE FUNCTION cs_update_referrer_type_proc() RETURNS INTEGER AS '
DECLARE
    referrer_keys RECORD;  -- FOR에서 사용하기 위한 일반적인 record를 선언
    a_output varchar(4000);
BEGIN 
    a_output := ''CREATE FUNCTION cs_find_referrer_type(varchar,varchar,varchar) 
                  RETURNS varchar AS '''' 
                     DECLARE 
                         v_host ALIAS FOR $1; 
                         v_domain ALIAS FOR $2; 
                         v_url ALIAS FOR $3; ''; 

    -- 
    -- 어떻게 FOR 반복문을 통해 쿼리의 결과를 훑는지를 주목...
    -- 여기서는 FOR <record> 생성을 이용하였다.
    --

    FOR referrer_keys IN select * from cs_referrer_keys order by try_order LOOP
        a_output := a_output || '' if v_'' || referrer_keys.kind || '' like '''''''''' 
                 || referrer_keys.key_string || '''''''''' then return '''''' 
                 || referrer_keys.referrer_type || ''''''; end if;''; 
    END LOOP; 
  
    a_output := a_output || '' return null; end; '''' language ''''plpgsql'''';''; 
 
    -- 이 방법은 적법하다 왜냐하면 여기서는 어떠한 변수도 치환하지 않았기 때문이다.
    -- 아니면 실패를 하게 된다. 함수를 시행하기 위한 다른 방법인 PERFORM을 보라
    
    EXECUTE a_output; 
end; 
' LANGUAGE 'plpgsql';

24.2.5.4. 다른 결과 상태 포함하기

GET DIAGNOSTICS 변수이름 = item [ , ... ]

이 명령은 시스템 상태를 추출할 때 사용한다. 각 item은 상태값을 나타내는 키워드인데 그 값은 명시된 '변수이름'에 할당된다. (당연히 '변수이름'의 자료형은 item의 자료형과 일치하여야 한다.) 현재 사용가능한 상태 item은 ...

ROW_COUNT: SQL 처리기에 보내진 마지막 SQL 쿼리에 의해 처리된 행의 갯수 
RESULT_OID:  가장 최근의 SQL쿼리에 의해 테이블에 삽입된 행의 OID. 
주지할 사항은 RESULT_OID는 INSERT 쿼리 이후에 사용가능하다. 

24.2.5.5. 함수에서 값의 반환

RETURN 표현식

위의 명령은 함수를 종료하고 '표현식'의 값을 상위 실행기로 반환한다. 함수의 반환 값은 반드시 정의 돼야한다. 만일 프로그램이 함수의 최상위 레벨의 끝에 까지 다다랐는데도 RETURN문이 없으면 실행시간 오류가 발생한다.

'표현식'의 결과는 자동적으로 함수의 반환형으로 변환된다. 이는 할당에서 설명한 것과 같다.

24.2.6. 제어 구조

이 제어 구조가 아마도 PL/SQL에서 가중 유용한 (또 중요한) 부분이 아닐까 한다. PL/pgSQL의 제어구조를 이용하여 사용자는 PostgreSQL의 데이터를 다양하고 강력하게 다룰 수 있다.

24.2.6.1. 조건 제어: IF 문

IF문은 특정 조건에 따라 주어진 일을 하는 문장이다. PL/pgSQL는 세가지 형태를 가지는데 그 것들은 IF: IF-THEN, IF-THEN-ELSE, IF-THEN-ELSE IF 이다.
주목: PL/pgSQL의 IF문은 짝지워진 END IF 가 필요한다. ELSE-IF의 경우는 2개의 END IF가 필요한데 하나는 첫번째 IF를 위해, 그리고 다른 하나는 ELSE IF를 위해서 이다.

IF-THEN
IF-THEN 문은 IF문의 가장 간단한 형태이다. THEN 과 END IF사이의 문장들은 주어진 조건이 true일때 수행된다. 그렇지 않으면 END IF 다음에 나오는 문장들로 실행이 넘어간다.

예)

F v_user_id <> 0 THEN
    UPDATE users SET email = v_email WHERE user_id = v_user_id;
END IF;

IF-THEN-ELSE
IF-THEN-ELSE 문은 IF-THEN 문에 해당 조건이 FALSE가 되었을 경우 실행될 문장들을 추가한 형태이다.

예1)

IF parentid IS NULL or parentid = ''''
THEN 
    return fullname;
ELSE
    return hp_true_filename(parentid) || ''/'' || fullname;
END IF;

예2)

IF v_count > 0 THEN 
    INSERT INTO users_count(count) VALUES(v_count);
    return ''t'';
ELSE 
    return ''f'';
END IF;

다음에 나오는 예처럼 IF문은 중첩되어 사용될 수 있다.

IF demo_row.sex = ''m'' THEN
  pretty_sex := ''man'';
ELSE
  IF demo_row.sex = ''f'' THEN
    pretty_sex := ''woman'';
  END IF;
END IF;

IF-THEN-ELSE IF
"ELSE IF"문이 사용되는 것은 실제로 ELSE문 안에 IF문을 중첩하는 것과 같다. 그렇기 때문에 END IF문이 각 중첩된 IF와 상위 IF-ELSE를 위해 필요한 것이다. 

예) 

IF demo_row.sex = ''m'' THEN
   pretty_sex := ''man'';
ELSE IF demo_row.sex = ''f'' THEN
        pretty_sex := ''woman'';
     END IF;  /* <-- 요기를 주목하시라... 요게 일반적인 언어와 다른 부분이다. */
END IF;

24.2.6.2. 반복 제어: LOOP, WHILE, FOR 그리고 EXIT

LOOP, WHILE, FOR 그리고 EXIT 문을 이용하여 PL/pgSQL 프로그램을 반복적으로 실행하는 흐름제어를 할 수 있다. 


LOOP 문...

[<<label>>]
LOOP
    실행할 문장
END LOOP;

위와같은 조건이 없는 반복문은 반드시 명백하게 EXIT을 사용하여 종료되야 한다. 선택적으로 사용할 수 있는 label은 중복된 반복문에서 빠져나오고자 할 때 EXIT문에 의해 사용된다.

EXIT 문...

EXIT [ label ] [ WHEN 표현식 ];

만약에 label이 명시되지 않으면 중첩된 반복문에서 내부의 반복문이 끝나면 내부 반복문의 END LOOP 다음에 나오는 문장을 수행한다. 그렇지 않고 label이 명시되면 주어진 label이 붙은 반복문 또는 블럭을 빠져나가게 된다.  그리고 반복문 또는 블럭의 해당되는 END 문 다음 문장을 처리한다. 그 label은 중첩된 반복문에서 현재 수행중인 내부 반복문의 label이거나 상위 반복문의 label이어야한다. 

예) 

LOOP
    -- 실행할 계산문
    IF count > 0 THEN
        EXIT;  -- 반복문 탈출
    END IF;
END LOOP;

LOOP
    -- 실행할 계산문
    EXIT WHEN count > 0;
END LOOP;

BEGIN
    -- 실행할 계산문
    IF stocks > 100000 THEN
        EXIT;  -- 오류! LOOP 블럭 밖에서 EXIT을 사용할 수 없다.
    END IF;
END;

WHILE 문...

WHILE문을 사용하여 주어진 조건을 검사하여 그 조건이 참인 동안 일을 수행하는 반복문을 만들 수 있다.

[<<label>>]
WHILE 표현식 LOOP
    실행할 문장
END LOOP;


예) 

WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
    -- 실행할 계산식
END LOOP;

WHILE NOT boolean_expression LOOP
    -- 실행할 계산식
END LOOP;

FOR 문...

[<<label>>]
FOR 변수이름 IN [ REVERSE ] 표현식 .. 표현식 LOOP
    실행할 문장
END LOOP;

이 반복문은 주어진 정수형의 범위 내에서 반복 수행한다. 변수 '변수이름'은 자동적으로 정수형으로 생성이 되고 오로지 반복문 내에서만 존재를 한다. 그리고 두 '표현식'은 최소와 최대값의 범위를 나타내는 표현식이다. 그리고 반복 될 때마다 변수의 값은 항상 1씩 증가/감소된다.

다음은 FOR 문에 대한 예이다. (레코드를 반복하는 FOR에 대한 예는 24.2.7를 참조) 

FOR i IN 1..10 LOOP
  -- 실행할 표현식

    RAISE NOTICE 'i is %',i;
END LOOP;

FOR i IN REVERSE 1..10 LOOP
    -- 실행할 표현식
END LOOP;

24.2.7. RECORD를 이용한 작업

RECORD는 간단한 데이터베이스 행의 자료형 (rowtype) 이긴 하지만 사전에 구조가 정의 되어있지 않는다. 이는 선택(selection)과 FOR 반복문에서 SELECT 작업을 통해 실제 데이터베이스의 한 행의 값을 갖는다.

24.2.7.1. 선언

RECORD 형의 변수는 다른 선택(selection)에 사용될 수 있다. 한 레코드를 다루거나 값을 레코드 필드로 대입을 하려고 할 때 만일 실제 행(row)이 없다면 실행시간 오류를 야기한다. 변수선언은 다음과 같이 한다.

변수이름 RECORD;

24.2.7.2. 값의 할당

RECORD 또는 Row으로 완전한 선택(selection)의 할당은 다음과 같이 해서 이루어진다.

SELECT INTO 대입받을_변수 표현식 FROM ...;


'대입받을_변수'는 RECORD 또는 ROW 형의 변수이거나 쉼표 ','를 사용하여 구분된 레코드/행의 필드와 변수의 목록이다. 위의 것은 Postgres가 보통 수행하는 SELECT INTO와는 완전히 다른 것이다. Postgres가 보통 수행하는 SELECT INTO에서는 SELECT결과를 이용하여 새로운 테이블을 만들 때 사용하는 것이다. (만일 PL/pgSQL에서 SELECT의 결과를 이용하여 새로이 테이블을 만들려면 CREATE TABLE AS SELECT를 사용해야 한다.)

만약에 row또는 변수목록(','로 구분된)을 '대입받을_변수'로 사용하고자 한다면 선택된 값들은 반드시 "대입받을_변수'와 구조가 일치되어야 한다. 그렇지 않으면 실행시간 오류가 발생한다. FROM 키워드 다음에는 조건, 그룹, 정렬 같은 것들이 따라올 수 있는데 이것들은 SELECT문에도 쓸 수 있는 것들이다.

레코드 또는 행의 값이 RECORD 변수로 할당이 되면 마침표 '.'를 사용하여 해당 필드로 접근할 수 있다.

DECLARE
    users_rec RECORD;
    full_name varchar;
BEGIN
    SELECT INTO users_rec * FROM users WHERE user_id=3;

  full_name := users_rec.first_name || '' '' || users_rec.last_name;

FOUND라고 하는 boolean형의 특별한 변수가 있는데, 이 것은 SELECT INTO 바로 다음에 사용되어 할당이 성공적이었는지를 확인 할 수 있다.

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

또한 IS NULL (또는 ISNULL)을 사용하여 RECORD/ROW가 NULL인지 여부를 검사할 수 있다. 만일 선택이 복수개의 행을 반환하였다면 첫번째만이 '대입받을_변수'에 옮겨진다. 나머지 행들은 누락되어져 버린다.

DECLARE
    users_rec RECORD;
    full_name varchar;
BEGIN
    SELECT INTO users_rec * FROM users WHERE user_id=3;

    IF users_rec.homepage IS NULL THEN
        -- 사용자가 없는 홈페이지에 들어왔다, "http://"를 반환한다.

        return ''http://'';
    END IF;
END;

24.2.7.3. Record를 통한 반복

특별한 FOR 반복문을 통하여 사용자는 쿼리의 결과를 통해 반복을 할 수 있고, 데이터를 사용할 수 있다. 다음은 그 문법이다.

[<<label>>]
FOR 레코드 | 행 IN select문장 LOOP
    실행할 문장
END LOOP;

레코드 또는 행은 'select문장'으로부터 모든 반환된 행을 할당받고 반복문의 문장은 각각의 행을 처리한다. 다음은 그 예이다.

create function cs_refresh_mviews () returns integer as '
DECLARE
     mviews RECORD;

     -- 위의 선언 대신에...
     -- mviews  cs_materialized_views%ROWTYPE;
     -- 이렇게 했다면 이 변수는 오로지 cs_materialized_views 테이블에서만 
     -- 사용할 수 있다. 

BEGIN
     PERFORM cs_log(''Refreshing materialized views...'');

     FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP

         -- 이제 "mviews" 는 cs_materialized_views의 한 레코드를 받았다.

         PERFORM cs_log(''Refreshing materialized view '' || mview.mv_name || ''...'');
         TRUNCATE TABLE mview.mv_name;
         INSERT INTO mview.mv_name || '' '' || mview.mv_query;
     END LOOP;

     PERFORM cs_log(''Done refreshing materialized views.'');
     return 1;
end;
' language 'plpgsql';

만일 위의 반복문이 EXIT으로 빠져나왔다면 마지막에 할당받은 행은 반복문 밖에서도 여전히 사용가능하다.

FOR-IN EXECUTE 문은 결과 레코드 사이를 반복할 수 있는 다른 방법이다. 

[<<label>>]
FOR 레코드 | 행 IN EXECUTE 표현식을_가진_문자열 LOOP 
    실행할 문장
END LOOP;

이의 형태는 앞의 예와 비슷한데, 이 경우에는 SELECT문이 표현식을 가지는 문자열로 대체되었다. 이것은 FOR문에 진입할 때마다 평가되고 해석된다. 여기서 한가지의 선택을 해야하는데 미리 해석된 쿼리를 통한 나은 성능을 택하느냐 아니면 일반적인 EXECUTE문과 같이 동적 쿼리의 유연성을 택하느냐이다. 

24.2.8. 처리 중단과 메세지

RAISE문을 사용하여 Postgres elog 매커니즘으로 출력 메세지를 보낼 수 있다.

RAISE 출력수준 '출력형식' [, identifier [...]];

출력형식에서 뒤 따라오는 identifier의 출력형식을 정할 수 있는 %를 사용할 수 있다. '출력수준'으로 쓸 수 있는 키워드는 다음의 3가지이다.

• DEBUG - 제품수준 환경의 데이터베이스에서 사용되는 조용한 수준의 메세지
• NOTICE - 데이터베이스 log에 기록이 되고 클라이언트로 보내지는 수준의 메세지
• EXCEPTIOM - 데이터베이스 log에 기록이 되고 트랜젝션을 중단하는 수준의 메세지

RAISE NOTICE ''Id number '' || key || '' not found!'';
RAISE NOTICE ''Calling cs_create_job(%)'',v_job_id;

바로 위의 2번째 예에서 v_job_id은 %의 자리에 문자열로 출력된다.

RAISE EXCEPTION ''Inexistent ID --> %'',user_id;

위의 예는 트랜젝션을 중단하고 데이터베이스 log에 기록한다.

24.2.9. 예외

Postgres는 그리 똑똑한 예외처리 모델을 가지고 있지 않다. 분석기(parser), 번역기/최적화 (planner/optimizer) 또는 실행기(executor)에서 더이상 요청된 처리를 수행하지 못할 경우 모든 트랜젝션은 중단되고 main loop로 건너뛰고나서 클라이언트 프로그램에서 다음 쿼리를 수행한다.

이같은 상황을 알리기 위한 메세지를 오류 메커니즘으로 넣는 것이 가능하다. 그러나 현재로는 왜 그러한 상황이 발생하였는지 아는 것은 불가능하다. (입/출력 변환 오류, floating point오류, parse 오류 등등). 그래서 데이터베이스 처리가 오류의 시점에서 자료의 불일치가 발생할 수 있기 때문에 상위 처리부로 귀환한다거나 계속된 명령의 처리는 전체 데이터베이스를 망가뜨릴 수 있다. 그렇기 때문에 처리가 중단이 됐고 이의 사실이 클라이언트에 알려진다 하더라도 계속되는 처리는 무의미하다.

그렇기 때문에 PL/pgSQL이 현재 할 수 있는 것은 함수나 trigger procedure가 처리되는 과정에서 중단(abort)되면 어떤 함수의 어디서 (line number와 문장의 종류) 발생했는지 추가적인 DEBUG lebel의 로그 메세지를 기록해주는 것이 다이다.

24.3. Trigger Procedures

PL/pgSQL는 trigger procedure를 정의 할 수 있다. 이를 생성하는 방법은 일반적인 것과 같이 CREATE FUNCTION를 사용하고 그 함수에 매개변수없이 리턴타입을 OPAQUE로 준다.
 
trigger procedure로 함수를 사용하는데는 Postgres에서만의 몇가지 세부사항이 있다.

첫째는 최상위 선언부(declare section)에 자동으로 생성되는 몇가지 변수들이 있다.

NEW

자료형이 RECORD인 변수로 ROW level trigger에서 INSERT/UPDATE 작업 할 때 데이터베이스의 새로운 행의 값을 갖는다.

OLD

자료형이 RECORD인 변수로 ROW level trigger에서 INSERT/UPDATE 작업 할 때 데이터베이스의 과거 행의 값을 갖는다.

TG_NAME

자료형이 name인 변수로 실제 작동할 trigger의 이름을 갖고 있다.

TG_WHEN

자료형이 test인 문자열로 trigger의 정의에 따라 "BEFORE" 또는 "AFTER"를 갖는다.


TG_LEVEL

자료형이 test인 문자열로 trigger의 정의에 따라 "ROW" 또는 "STATEMENT"를 갖는다.

TG_OP

자료형이 test인 문자열로 trigger가 실제 수행하는 작업을 나타내는데 "INSERT", "UPDATE" 또는 "DELETE"를 갖는다.

TG_RELID

자료형이 oid인 변수로 trigger를 촉발시킨 테이블의 Object ID를 갖는다.

TG_RELNAME

자료형이 name으로 trigger를 촉발시킨 테이블의 이름을 갖는다.

TG_NARGS

자료형이 integer으로 CREATE TRIGGER 문으로 생성된 trigger procedure로 넘겨진 매개변수의 숫자이다.

TG_ARGV[]

자료형이 text인 배열로 CREATE TRIGGER 문의 실제 매개변수 값이다. 배열의 첫째 요소는 0번재 배열에 있고, 만일 배열의 인덱스 값이 0보다 작거나 tg_nargs보다 크면 해당 배열은 NULL을 반환한다.

두번째는 trigger procedure는 NULL 또는 trigger를 동작시킨 테이블과 동인한 구조의 레코드/행을 반납해야한다. "AFTER"로 동작된 trigger는 항상 NULL값을 반납한다. "BEFORE"로 동작한 trigger는 NULL을 반환하면 이 실제의 행에 대한 동작을 건너 뛰도록 신호를 보낸다. 그렇지 않으면 반환된 레코드/행의 값이 삽입되거나 갱신된 행의 값을 덮어쒸울 것이다. signal값을 직접적으로 "NEW"에 기록해서 반환하거나 완전히 새로운 레코드/행을 만들어서 반환하는 것이 가능하다.

Example 24-1. PL/pgSQL Trigger Procedure 예

이 trigger는 테이블에 행 하나가 삽입되거나 갱신될 때마다 현재 사용자 이름과 행에 있는 시간을 확인한다. 그리고 주어진 종업원의 이름과 월급이 양수값인지 확인한다.

CREATE TABLE emp (
    empname text,
    salary integer,
    last_date timestamp,
    last_user text
);

CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS '
    BEGIN
        -- 주어진 empname과 salary를 확인
        IF NEW.empname ISNULL THEN
            RAISE EXCEPTION ''empname은 NULL값이 될 수 없다.'';
        END IF;
        IF NEW.salary ISNULL THEN
            RAISE EXCEPTION ''% 는 NULL로 된 salary를 가질 수 없다'', NEW.empname;
        END IF;

        -- 월급을 받고 있는 사람 중에 종업원인가?
        IF NEW.salary < 0 THEN
            RAISE EXCEPTION ''% 는 음수의 salary를 가질 수 없다.'', NEW.empname;
        END IF;

        -- 언제 누가 월급을 조정했나?
        NEW.last_date := ''now'';
        NEW.last_user := current_user;
        RETURN NEW;
    END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW EXECUTE PROCEDURE emp_stamp();

24.4. 예제

여기에 PL/pgSQL로 함수를 만드는 것이 얼마나 쉬운지를 보여주는 예가 있다. 

PL/pgSQL를 사용하여 함수를 만들 때 한가지 귀찮은 것은 작은 따옴표를 사용하는 것이다. CREATE FUNCTION에 사용하는 함수의 소스는 문자열형이어야 한다. 그렇기 때문에 문자열형 내부의 작은 따옴표는 두번을 사용하던지 ('') 아니면 역슬레쉬에 이은 작은 따옴표 (\')이어야 한다. PL/pgSQL개발자는 여전히 좀더 우아한 방법을 찾고 있고 그동안은 (괴롭겠지만) 아래의 예와 같이 두개의 작은 따옴표를 써야한다. 미래에 어떤 해결점이 나오던지 간에 현재의 사용하는 형태는 미래에도 호환이 될것이다.

다른 상황에서 작은 따옴표에서 벗어나는 방법에 대한 자세한 설명은 24.5.1.1에 있으니 참고 바란다.

Example 24-2. 정수를 증가시키기 위한 간단한 PL/pgSQL 함수

이 함수는 정수를 받아서 1을 증가시키고, 그 증가된 값을 반환한다.

CREATE FUNCTION add_one (integer) RETURNS integer AS '
    BEGIN
        RETURN $1 + 1;
    END;
' LANGUAGE 'plpgsql';

Example 24-3. 문자열을 합치는 간단한 PL/pgSQL 함수

이 함수는 두 문자열을 받아들여서 하나로 합친다음 그 것을 반환한다.

CREATE FUNCTION concat_text (text, text) RETURNS text AS '
    BEGIN
        RETURN $1 || $2;
    END;
' LANGUAGE 'plpgsql';

Example 24-4. 복합형의 PL/pgSQL 함수

이 예에서 EMP라는 테이블과 매개변수로 정수를 받아서 참 또는 거짓을 반환한다. 만약에 EMP테이블의 salary 필드가 NULL이면 "f"를 반한하고 그렇지 않으면 그 필드의 값과 매개변수로 받은 정수값을 비교하여 그 결과값을 반환한다. ( t 또는 f )

CREATE FUNCTION c_overpaid (EMP, integer) RETURNS boolean AS '
    DECLARE
        emprec ALIAS FOR $1;
        sallim ALIAS FOR $2;
    BEGIN
        IF emprec.salary ISNULL THEN
            RETURN ''f'';
        END IF;
        RETURN emprec.salary > sallim;
    END;
' LANGUAGE 'plpgsql';

24.5. Oracle PL/SQL에서 PG/pgSQL로 변환

글쓴이: Roberto Mello (<rmello@fslc.usu.edu>) 

이 장은 Oracle의 PL/SQL과 PostgreSQL의 PL/pgSQL 사이에 다른 점을 설명하고자 한다. Oracle PL/SQL에서 PG/pgSQL로 변환하고자 하는 개발자들에게 도움이 되었으면한다. 여기있는 대부분의 코드는 Roberto Mello가 2000년 여름에 OpenForce Inc.와 internship을 가졌을 때 PostgreSQL로 변환한 ArsDigita Clickstream에서 거의 가져왔다.

PL/pgSQL는 PL/SQL과 외양면에서 상당히 비슷하다. 블록으로 구조화 되어있고 엄격한 언어이다. (모든 변수는 반드시 선언되어져야한다.) PL/SQL은 PostgreSQL보다 더 많은 기능들을 가지고 있긴 하지만, PL/pgSQL는 우수한 기능을 가지고 있고 꾸준하게 향상되어왔다.

24.5.1. 주요 차이점

변환을 하고자 할 때 유념해야 할 것은...

• PostgreSQL에는 기본 parameter가 없다.
• PostgreSQL에서는 함수를 loverload할 수 있다. 이 것은 종종 기본 parameter가 없는 것에 대한 대안으로 사용된다.
• 할당(대입), 반복문 그리고 조건문은 비슷하다.
• PostgreSQL에서는 커서를 필요로 하지 않는다. 그저 쿼리를 FOR 문에 넣으면 된다.
  (아래의 예제 참조)
• PostgreSQL에서는 작은 따옴표 사용에 주의 해야한다.
  (24.5.1.1 참조)
  

24.5.1.1. 작은 따옴표를 잘 사용하는 법

PostgreSQL에서 함수를 선언할 때 작은 따옴표를 잘 사용해야한다. 때때로 이러한 작은 따옴표는 웃기는(?) 코드를 만들기도 하는 데 특히 다른 함수를 만드는 함수를 만들 때 더욱 그렇다. (Example 24-6을 참조바람) 작은 따옴표를 쓸 때 한가지 명심할 것은 오로지 시작하고 끝맺을 때 쓴 작은 따옴표를 빼고는 다른 모든 것들이 짝수로 온다는 것이다.

이런 맥락에서 Table 24-1는 편리함을 제공할 것이다. (당신은 이 작은 도표를 사랑할 껄...) 

Table 24-1. 작은 따옴표를 잘 사용하는 방법

24.5.2. 함수 전환하기

Example 24-5. 간단한 함수 

아래의 예는 Oracle의 함수입니다.

CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name IN varchar, v_version IN varchar)
RETURN varchar IS
BEGIN
    IF v_version IS NULL THEN
        RETURN v_name;
    END IF;
    RETURN v_name || '/' || v_version;
END;
/
SHOW ERRORS;

위의 함수를 한번 살펴보고 PL/pgSQL과의 차이점을 알아보자

OR REPLACE 부분은 PL/pgSQL에서 사용하지 않는다. PL/pgSQL에서는 확실하게 이미 생성된 함수를 삭제하고 생성해야 비슷한 결과는 낼 수 있다. 

PostgreSQL는 매개변수가 이름을 갖지 않는다. 전환할 때 함수 내에서 명확하게 alias를 이용하여 변수를 선언해야한다. 

Oracle은 함수에 값을 넘길 때 IN, OUT 그리고 INOUT 매개변수를 갖는다. 여기서 INOUT은 값을 받고 그 변수를 통해 값을 반환하는 역할을 한다. 그러나 PostgreSQL은 오직 "IN" 매개변수만을 갖고 오직 1개의 값만을 반환한다.

Oracle에서 함수를 정의할 때 사용하는 RETURN 키워드는 PostgreSQL에서는 RETURNS로 사용한다.

PostgreSQL에서 함수는 작은 따옴표를 이용하여 생성되기 때문에 함수 내부의 따옴표에 대해서 조심해서 잘 사용해야한다. (Section 24.5.1.1 참조 바람)

"/show"는 PostgreSQL에는 없기 때문에 오류를 야기한다. 

자.. 이제 PostgreSQL에서 변환되고 나서 어떻게 보이는지 한번 보자.

DROP FUNCTION cs_fmt_browser_version(varchar, varchar);
CREATE FUNCTION cs_fmt_browser_version(varchar, varchar)
RETRUNS varchar AS '
DECLARE
    v_name ALIAS FOR $1;
    v_version ALIAS FOR $2;
BEGIN
    IF v_version IS NULL THEN
        return v_name;
    END IF;
    RETURN v_name || ''/'' || v_version;
END;
' LANGUAGE 'plpgsql';

Example 24-6. 다른 함수를 생성하는 함수 

다음의 procedure는 SELECT 문에서 결과를 받은 다음 IF문 안에서 결과를 가지고 효율성을 위해 커다란 함수를 만든다. 다음의 예로부터 커서, FOR 반복문, PostgreSQL의 작은 따옴표 문제 등의 차이점을 주목해서 보기 바란다.

create or replace procedure cs_update_referrer_type_proc is 
    cursor referrer_keys is 
        select * from cs_referrer_keys 
        order by try_order;

    a_output varchar(4000); 
begin 
    a_output := 'create or replace function cs_find_referrer_type(v_host IN varchar, v_domain IN varchar, 
v_url IN varchar) return varchar is begin'; 

    for referrer_key in referrer_keys loop 
        a_output := a_output || ' if v_' || referrer_key.kind || ' like ''' || 
referrer_key.key_string || ''' then return ''' || referrer_key.referrer_type || 
'''; end if;'; 
    end loop; 

    a_output := a_output || ' return null; end;'; 
    execute immediate a_output; 
end; 
/ 
show errors

변환된 PostgreSQL: 

CREATE FUNCTION cs_update_referrer_type_proc() RETURNS integer AS '
DECLARE
    referrer_keys RECORD;  -- FOR에서 사용하기 위한 일반 RECORD 선언
    a_output varchar(4000);
BEGIN 
    a_output := ''CREATE FUNCTION cs_find_referrer_type(varchar,varchar,varchar) 
                  RETURNS varchar AS '''' 
                     DECLARE 
                         v_host ALIAS FOR $1; 
                         v_domain ALIAS FOR $2; 
                         v_url ALIAS FOR $3; ''; 

    -- 
    -- 어떻게 FOR 반복문에서 쿼리의 결과를 가져오는지 주목하라
    -- 여기서는 FOR <record> 를 사용하였다..
    --

    FOR referrer_keys IN select * from cs_referrer_keys order by try_order LOOP
        a_output := a_output || '' if v_'' || referrer_keys.kind || '' like '''''''''' 
                 || referrer_keys.key_string || '''''''''' then return '''''' 
                 || referrer_keys.referrer_type || ''''''; end if;''; 
    END LOOP; 
  
    a_output := a_output || '' return null; end; '''' language ''''plpgsql'''';''; 
 
    -- 여기서는 변후를 치환하지 않았기 때문에 적법하다.
    -- 그러지 않으면 실패를 할 것이다. 함수를 작동시기기 위한 다른 방법인 PERFORM 을 볼것.
    
    EXECUTE a_output; 
end; 
' LANGUAGE 'plpgsql';

Example 24-7. 많은 문자열을 처리하는 함수와 OUT 매개변수 

다음에 나오는 Oracle PL/SQL procedure는 URL을 받아 이를 몇몇개의 부분 (host, path, query)로 쪼게 반환하는 함수이다. 이것은 procedure 이어야 하는 까닭은 PL/pgSQL 함수는 오직 하나의 값만을 반환할 수 잇기 때문이다. (24.5.3 절 참조) 
PostgreSQL에서 이를 해결하는 방법의 3개의 다른 함수로 procedure로 쪼개야한다. 하나는 host를 반환, 다른 하나는 path, 그리고 또다른 하나는 query 식으로....

create or replace procedure cs_parse_url(
    v_url IN varchar,
    v_host OUT varchar,  -- 반환값
    v_path OUT varchar,  -- 이것도 반환값
    v_query OUT varchar) -- 그리고 이것도...
is
    a_pos1 integer;
    a_pos2 integer;
begin
    v_host := NULL;
    v_path := NULL;
    v_query := NULL;
    a_pos1 := instr(v_url, '//'); -- PostgreSQL는 이 함수가 없다.

    if a_pos1 = 0 then
        return;
    end if;
    a_pos2 := instr(v_url, '/', a_pos1 + 2);
    if a_pos2 = 0 then
        v_host := substr(v_url, a_pos1 + 2);
        v_path := '/';
        return;
    end if;

    v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
    a_pos1 := instr(v_url, '?', a_pos2 + 1);

    if a_pos1 = 0 then
        v_path := substr(v_url, a_pos2);
        return;
    end if;

    v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
    v_query := substr(v_url, a_pos1 + 1);
end;
/
show errors;

PostgreSQL로 변환한 뒤: 

drop function cs_parse_url_host(varchar); 
create function cs_parse_url_host(varchar) returns varchar as ' 
declare 
    v_url ALIAS FOR $1; 
    v_host varchar; 
    v_path varchar; 
    a_pos1 integer; 
    a_pos2 integer; 
    a_pos3 integer; 
begin 
    v_host := NULL; 
    a_pos1 := instr(v_url,''//''); 

    if a_pos1 = 0 then 
        return '''';  -- Return a blank
    end if; 

    a_pos2 := instr(v_url,''/'',a_pos1 + 2); 
    if a_pos2 = 0 then 
        v_host := substr(v_url, a_pos1 + 2); 
        v_path := ''/''; 
        return v_host; 
    end if; 

    v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2 ); 
    return v_host; 
end; 
' language 'plpgsql';

주목: PostgreSQL는 instr함수가 없기 때문에 다른 함수를 조합하여 구현하도록 한다. 나는 그렇게 하기가 귀찮아서 나만의 오라클의 기능과 같은 instr함수를 만들었다. ( 24.5.6 참조) 

24.5.3. Procedure

Oracle의 procedure는 좀더 많은 유연성을 제공한다. 왜냐하면 반환값을 명시해줄 필요가 없기 때문이긴 하지만 INOUT 또는 OUT을 사용하여 값을 반환할 수는 있다.

예:

create or replace procedure cs_create_job(v_job_id in integer)
is
    a_running_job_count integer;
    pragma autonomous_transaction;(1)
begin
    lock table cs_jobs in exclusive mode;(2)

    select count(*) into a_running_job_count from cs_jobs
    where end_stamp is null;

    if a_running_job_count > 0 then
        commit; -- free lock(3)
        raise_application_error(-20000, 'Unable to create a new job: a job is currently running.');
    end if;

    delete from cs_active_job;
    insert into cs_active_job(job_id) values(v_job_id);

    begin
        insert into cs_jobs(job_id, start_stamp) values(v_job_id, sysdate);
        exception when dup_val_on_index then null; -- 이미 있어도 걱정말것(4)
    end;
    commit;
end;
/
show errors

위의 예와같은 Procedure는 쉽게 INTEGER를 반환하는 PostgreSQL 함수로 변환할 수 있다. 위의 예는 상당히 재미있게도 몇가지 가르쳐 주는 바가 있다.

1. PostgreSQL에는 'pragma' 문이 없다.
2. PL/pgSQL에서 LOCK TABLE을 사용하면 잠금상태는 트랜젝션이 종료될 때 까지 유지될 것이다.
3. PL/pgSQL procedure 내에는 또 다른 트렌젝션을 가질 수 없다. 함수는 (그리고 여기서 호출된 다른 함수) 하나의 트랜젝션 속에서 실행이 된다. 그래서 어떤 문제가 발생하였을 때 PL/pgSQL는 결과를 roll back한다. 그래서 오직 하나의 BEGIN문 만을 사용할 수 있다.
4. IF문을 쓸 수 있다면 예외처리보다 나을 듯...

아래의 코드는 PL/pgSQL procedure로 전환한 것이다.

drop function cs_create_job(integer);
create function cs_create_job(integer) returns integer as ' declare
    v_job_id alias for $1;
    a_running_job_count integer;
    a_num integer;
    -- pragma autonomous_transaction;
begin
    lock table cs_jobs in exclusive mode;
    select count(*) into a_running_job_count from cs_jobs where end_stamp is null;

    if a_running_job_count > 0 then
        -- commit; -- free lock
        raise exception ''Unable to create a new job: a job is currently running.'';
    end if;

    delete from cs_active_job;
    insert into cs_active_job(job_id) values(v_job_id);

    SELECT count(*) into a_num FROM cs_jobs WHERE job_id=v_job_id;
    IF NOT FOUND THEN  -- If nothing was returned in the last query
        -- This job is not in the table so lets insert it.
        insert into cs_jobs(job_id, start_stamp) values(v_job_id, sysdate());
        return 1;
    ELSE
        raise NOTICE ''Job already running.'';(1)
    END IF;

    return 0;
end;
' language 'plpgsql';

(1) PL/pgSQL에서 어떻게 하면 Notice나 error을 사용할 수 있는지 볼 것...

24.5.4. Package

주목: 나는 이 패키지로 그리 많은 일을 하지 않았다. 그러니 아래의 코드에 오류가 있을 수 있다.

Package는 Oracle이 제공하는 PL/SQL문과 함수들을 하나로 묶어 은패(encapsulate)하는 방법을 제공한다. (자바에서 클래스처럼 함수와 오브젝트를 정의 하는 식의....)
그리고 이러한 오브젝트와 함수들은 점 '.'을 이용하여 접근할 수 있다. 

아래에 ACS 4(the ArsDigita Community System)에서 사용하는 Oracle Package가 있다.

create or replace package body acs
as
  function add_user (
    user_id     in users.user_id%TYPE default null,
    object_type     in acs_objects.object_type%TYPE
               default 'user',
    creation_date   in acs_objects.creation_date%TYPE
               default sysdate,
    creation_user   in acs_objects.creation_user%TYPE
               default null,
    creation_ip     in acs_objects.creation_ip%TYPE default null,
  ...
  ) return users.user_id%TYPE
  is
    v_user_id       users.user_id%TYPE;
    v_rel_id        membership_rels.rel_id%TYPE;
  begin
    v_user_id := acs_user.new (user_id, object_type, creation_date,
                creation_user, creation_ip, email,
    ...
    return v_user_id;
  end;
end acs;
/
show errors

여기서는 다른 오프젝트를 생성하여 변환을 하는데 일반적인 이름을 갖는 함수를 정의하여 Oracle Package를 대신하였다. 여기서 몇가지 주의해야하는데, PostgreSQL 함수의 기본 매개변수의 부족 같은 것이다. 위의 package는 아래와 같은 방식으로 바뀔수 있다.

CREATE FUNCTION acs__add_user(integer,integer,varchar,datetime,integer,integer,...)
RETURNS integer AS '
DECLARE
    user_id ALIAS FOR $1;
    object_type ALIAS FOR $2;
    creation_date ALIAS FOR $3;
    creation_user ALIAS FOR $4;
    creation_ip ALIAS FOR $5;
    ...
    v_user_id users.user_id%TYPE;
    v_rel_id membership_rels.rel_id%TYPE;
BEGIN
    v_user_id := acs_user__new(user_id,object_type,creation_date,creation_user,creation_ip, ...);
    ...

    return v_user_id;
END;
' LANGUAGE 'plpgsql';

　

24.5.5. 눈여겨 봐야할 기타사항

24.5.5.1. EXECUTE

PostgreSQL의 EXECUTE는 잘 작동하지만 Section 24.2.5.3에서 설명하였지만 quote_literal(TEXT) 와 quote_string(TEXT)를 사용할 때는 명심해야하는 것이 있다. EXECUTE ''SELECT * from $1'';를 사용하는 것은 앞에서 말한 함수를 사용하지 않으면 제대로 작동하지 않을 것이다.

24.5.5.2. PL/pgSQL 함수의 최적화

PostgreSQL에는 두가지의 함수 최적화 옵션이 있다.

iscachable (매개변수 값이 같으면 함수는 항상 같은 값을 반환한다.) 와 isstrict (매개변수 중에 NULL이 있으면 함수는 항상 NULL을 반환한다.). 좀더 자세한 것은 CREATE FUNCTION 참고서를 보기바란다. 

이 최적화를 위한 옵션을 사용하기 위해서, WITH 문을 CREATE FUNCTION문과 함께 다음과 같이 사용한다. 

CREATE FUNCTION foo(...) RETURNS integer AS '
...
' LANGUAGE 'plpgsql'
WITH (isstrict, iscachable);

24.5.6. 부록 I

24.5.6.1. instr 함수 코드

--
-- Oracle의 instr 함수와 같은 역할을 하는 함수.
-- 사용법: instr(string1,string2,[n],[m]) [] 부분은 옵션.
-- 
-- string2에서 n번째의 문자부터 탐색하여 string1을 찾는다. n 이 음수이면 
-- 뒤로부터 찾는다. 만일 m이 넘겨지지 않았으면 1로 가정한다.
-- (처음부터 찾는다)
-- 
--
-- by Roberto Mello (rmello@fslc.usu.edu)
-- modified by Robert Gaszewski (graszew@poland.com)
-- Licensed under the GPL v2 or later.
--

DROP FUNCTION instr(varchar,varchar);
CREATE FUNCTION instr(varchar,varchar) RETURNS integer AS '
DECLARE
    pos integer;
BEGIN
    pos:= instr($1,$2,1);
    RETURN pos;
END;
' language 'plpgsql';


DROP FUNCTION instr(varchar,varchar,integer);
CREATE FUNCTION instr(varchar,varchar,integer) RETURNS integer AS '
DECLARE
    string ALIAS FOR $1;
    string_to_search ALIAS FOR $2;
    beg_index ALIAS FOR $3;
    pos integer NOT NULL DEFAULT 0;
    temp_str varchar;
    beg integer;
    length integer;
    ss_length integer;
BEGIN
    IF beg_index > 0 THEN

       temp_str := substring(string FROM beg_index);
       pos := position(string_to_search IN temp_str);

       IF pos = 0 THEN
             RETURN 0;
         ELSE
             RETURN pos + beg_index - 1;
         END IF;
    ELSE
       ss_length := char_length(string_to_search);
       length := char_length(string);
       beg := length + beg_index - ss_length + 2;

       WHILE beg > 0 LOOP

           temp_str := substring(string FROM beg FOR ss_length);
             pos := position(string_to_search IN temp_str);

             IF pos > 0 THEN
                   RETURN beg;
             END IF;

             beg := beg - 1;
       END LOOP;
       RETURN 0;
    END IF;
END;
' language 'plpgsql';

--
-- Written by Robert Gaszewski (graszew@poland.com)
-- Licensed under the GPL v2 or later.
--
DROP FUNCTION instr(varchar,varchar,integer,integer);
CREATE FUNCTION instr(varchar,varchar,integer,integer) RETURNS integer AS '
DECLARE
    string ALIAS FOR $1;
    string_to_search ALIAS FOR $2;
    beg_index ALIAS FOR $3;
    occur_index ALIAS FOR $4;
    pos integer NOT NULL DEFAULT 0;
    occur_number integer NOT NULL DEFAULT 0;
    temp_str varchar;
    beg integer;
    i integer;
    length integer;
    ss_length integer;
BEGIN
    IF beg_index > 0 THEN
        beg := beg_index;
        temp_str := substring(string FROM beg_index);

        FOR i IN 1..occur_index LOOP
            pos := position(string_to_search IN temp_str);

            IF i = 1 THEN
                beg := beg + pos - 1;
            ELSE
                beg := beg + pos;
            END IF;

            temp_str := substring(string FROM beg + 1);
        END LOOP;

        IF pos = 0 THEN
            RETURN 0;
        ELSE
            RETURN beg;
        END IF;
    ELSE
        ss_length := char_length(string_to_search);
        length := char_length(string);
        beg := length + beg_index - ss_length + 2;

        WHILE beg > 0 LOOP
            temp_str := substring(string FROM beg FOR ss_length);
            pos := position(string_to_search IN temp_str);

            IF pos > 0 THEN
                occur_number := occur_number + 1;

                IF occur_number = occur_index THEN
                    RETURN beg;
                END IF;
            END IF;

            beg := beg - 1;
        END LOOP;

        RETURN 0;
    END IF;
END;
' language 'plpgsql';

24.5.7. 부록 II - PL/pgSQL을 이용한 자바코드

안녕하세요, 김일형입니다.

PL/pgSQL 매뉴얼을 번역하다 말고 잠시 옆길로 새어봤습니다.
제가 주로 하는 것이 JAVA이다 보니.... 한번 이 두 개를 붙여보고 싶더군요.

다음은 제가 실행해본 테스트 순서입니다.

24.5.7.1. PL/pgSQL을 사용가능하도록 database 만들기..

이것은 메뉴얼에 나와있습니다만 기본적으로 database에는 PL/pgSQL을 사용할 수 없습니다. 그래서 사용자가 직접 해당 db에 PL/pgSQL을 사용할 수 있도록 해주어야 합니다.
하지만 한줄만 쳐놓으시면 다시는 신경안써도 되죠.

$ createlang plpgsql bbs

라고 합니다. 위의 명령에서 bbs는 PL/pgSQL을 사용할 db명으로 제가 앞으로 테스트하면서 사용할 database이름입니다. 만일 어떤분이 PL/pgSQL을 ABC라는 db에서 사용하기를 원하신다면...

$ createlang plpgsql ABC

라고 입력하시면 됩니다. 아무런 메세지도 안떨어지고 끝납니다. 
그러나 이 작업을 하시기 전에 반드시 postmaster 가 떠있어야 합니다. 안그럼 ...

psql: connectDBStart() -- connect() failed: No such file or directory
        Is the postmaster running locally
        and accepting connections on Unix socket '/tmp/.s.PGSQL.5432'?
createlang: external error

이런 에러가 떨어집니다.

24.5.7.2. 작업용 테이블 작성하기

CREATE TABLE test (
   id   int,
   name varchar
);

이 테이블은 사용자 식별 id하고 이름을 갖는 간단한 테이블입니다.

24.5.7.3. PL/pgSQL 함수 작성하기

예전에 올렸던 메뉴얼을 기준으로 함수를 다음과 같이 만들었습니다.

DROP FUNCTION myInsertRow(VARCHAR);
CREATE FUNCTION myInsertRow(VARCHAR) RETURNS int AS '
DECLARE
    v_name ALIAS FOR $1;
    maxNo  int;
BEGIN
    SELECT INTO maxNo max(id) FROM test;
    IF NOT FOUND OR maxNo IS NULL THEN
        RAISE NOTICE ''MAX Value is not found or null'';
        maxNo := 0;
    END IF;

    maxNo := maxNo + 1;

    INSERT INTO test VALUES( maxNo, v_name );
    RETURN maxNo;
END;
' LANGUAGE 'plpgsql';

이 함수는 일단 첨가할 이름을 입력받고 현재 테이블에 입력된 id값 중에서 최대값을 뽑아낸 다음 거기(maxNo)에 1을 증가시켜 새로운 사용자 id를 만들어 낸 다음 이 새 번호와 입력받은 이름으로 새로운 Row를 테이블에 추가합니다.
간단한 것이죠?
한번 코드를 보시면 그리 어렵지 않게 읽으실 수 있을 것입니다.

위의 코드를 myInsert.sql이라는 화일로 저장을 합니다.

24.5.7.4. PL/pgSQL 함수를 db에 넣기

$ psql -f myInsert.sql bbs

이렇게 하면 myInsert.sql에서 bbs라는 db로 위의 코드를 하나하나 집어넣습니다.
이것을 처음 입력시키면 ...

ERROR:  RemoveFunction: function 'myinsertrow(varchar)' does not exist
psql:03.sql:1: ERROR:  RemoveFunction: function 'myinsertrow(varchar)' does not
exist
CREATE

라고 에러가 발생하는데 당연한 것이 위의 코드 맨 윗줄에 일단 기존의 함수를 drop하는 부분이 있습니다. 당연히 처음 위의 코드를 넣을 때는 기존에 함수가 없으니 에러가 나죠. 이렇게 하는 이유는 일단 코드를 수정해서 입력을 하게되면 기존의 함수를 없애야하니까 그렇습니다.

24.5.7.5. 함수 테스트하기

위에처럼 함수를 일단 생성하시고 나면.... Java하고 붙이기 전에 시험을 해봐야 겠습니다.

$ psql bbs

이렇게 해서 bbs라는 db로 들어갑니다.
그리고 

=# \df myinsertrow

라고 입력하면 

             List of functions
 Result  |  Function   |     Arguments
---------+-------------+-------------------
 integer | myinsertrow | character varying
(1 row)

이렇게 나옵니다. 제대로 함수가 생성이 되었죠?

그럼 이 함수를 psql상에서 시험가동을 시켜보겠습니다.

# SELECT myInsertRow( 'My Name' );
NOTICE:  MAX Value not found
 myinsertrow
-------------
           1
(1 row)

=# SELECT * FROM test;
 id |  name
----+---------
  1 | My Name
(1 row)

어떻습니까 잘 되죠? 여기서 주목하실 것은 함수를 작동시키는 명령어가 'SELECT'입니다.

24.5.7.6. JAVA의 JDBC와 연결...

아래는 자바 코드입니다.

import java.sql.*;

public class pltest {

    public pltest() {
        String url = "jdbc:postgresql://127.0.0.1/bbs";

        try {
            Class.forName("org.postgresql.Driver");
            Connection con = DriverManager.getConnection(url, "id_user", "" );

            PreparedStatement ps = con.prepareStatement("SELECT myInsertRow( ? )");
            ps.setString(1, "This is Name"  );
            ResultSet rs = ps.executeQuery();
            int    result = 0;

            while(rs.next()) {
                result = rs.getInt(1);
                System.out.println( "NEW ID: " + result );
            }

            rs.close();
            ps.close();
            con.close();
        }
        catch( Exception ex ) {
            System.out.println( ex.getMessage() );
        }
    }
    public static void main(String[] args) {
        pltest pltest1 = new pltest();
    }
}

이렇게 그리 길지 않은 코드입니다.

중요한 부분만 간단하게 설명하겠습니다.

Class.forName("org.postgresql.Driver");

PostgreSQL의 JDBC Driver를 가져오는 부분입니다. 이것은 각 DBMS마다 다르게 되어있는 것입니다.

String url = "jdbc:postgresql://127.0.0.1/bbs";
Connection con = DriverManager.getConnection(url, "id_user", "" );

이것은 가져온 드라이버를 바탕으로 해당 address로 실제 DBMS로부터 connection을 가져오는 부분입니다. ip부분을 127.0.0.1로 되어있는 것은 DBMS하고 자바로 실행시키는 application하고 같은 기계안에 있다는 뜻이겠죠... 그리고 url뒷부분의 bbs는 db이름이구요.

PreparedStatement ps = con.prepareStatement("SELECT myInsertRow( ? )");

이 것은 다른 DB의 Stored Procedure를 실행하는 코드와 같은 부분입니다.
그런데 그 안에 들어가는 쿼리 문자열이 아까 전에 보셨던거랑 똑같죠... 매개변수 부분만 틀리고... 일단 이렇게 해서 쿼리문을 준비해 둡니다.

ps.setString(1, "This is Name"  );

이렇게 해서  위의 '?'부분을 'This is Name'이라는 문자열로 치환을 시킵니다.

ResultSet rs = ps.executeQuery();

위의 문장은 이렇게 준비된 쿼리문을 실행시키고 반환값을 ResultSet으로 가져옵니다.

while(rs.next()) {
    result = rs.getInt(1);
    System.out.println( "NEW ID: " + result );
}

그래서 반환값이 있으면 첫번째 반환값을 result 변수로 가져오고 이 값을 표준 출력장치로 출력한다..... 아시지만 작성한 PL/pgSQL함수를 보면 거기에 int값을 반환하게 되있습니다. 그건 새로운 ID값이죠.


그래서 컴팔하고 돌려보면

NEW ID: 2

요렇게 나옵니다. 당연히 아까 전 psql에서 하나 생성했으니까 지금은 새 ID가 2이겠죠.

그리고 psql에 가서 쿼리를 해보면....

=# SELECT * FROM test;
 id |     name
----+--------------
  1 | My Name
  2 | This is Name
(2 rows)

이렇게 나옵니다. 아까 전에 입력한 거랑, 지금 자바를 통해 입력한 거랑 입력이 되어있죠.

이렇게 함으로써 일반코드에서 2개의 쿼리 및 계산식을 코드로 구현을 해서 불러야 하는데 여기서는 그 것을 단순한 하나의 함수 호출하는 것으로 끝이 났습니다. 당연히 2개의 statement를 만들어 네트워크 넘어로 요청을 해야하는데 여기서는 1개의 요청으로 동일한 작업을 끝냈습니다.

이게 PL/pgSQL을 작성하는 또 하나의 이유가 되겠죠....

도움이 되었기를 바랍니다. 
그럼 즐겁게 DB를 디비십시요.