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

지난번 개략적인 PL/pqSQL에 이어 오늘은 세부사항에 대한 소개입니다.

원래 아는게 워낙 짧아서 거의 전부 새롭게 느껴졌었습니다.

오늘 내용에는 중요한 것이 많습니다. 저의 허접한 번역 때문에 방해가 되신다면 직접 원문을 읽어보시는게 .... /_/;;;

제가 번역을 하면서도 잘 내용이 돠닿지 않는 것이 있었는데 그걸 효과적으로 설명드릴 방도를 몰라 죄송하기만 합니다.

자 그럼 쏩니다.....

////////////////////////////////////////////////////////////////////////////

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문이 없으면 실행시간 오류가 발생한다.

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

////////////////////////////////////////////////////////////////////////////

다음에 계속....

김일형.