|
PL/pgSQL - SQL Procedural Language
이글은 PostgreSQL 메뉴얼 중 PL/pgSQL 부분을 김일형님께서 번역하신 글을 이곳에 옮겨 놓은 것입니다. 'Database 자료' 메뉴에 보시면 원본의 링크가 걸려 있습니다. 참조하시기 바랍니다.
목차
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 와 같이 편리한 점이 있고, 재생성과 함수 디버그에 편리한 장점이 있다.
|
