토토 핫/pgSQL은 로드 가능한 절차 언어입니다.포스트그레스데이터베이스 시스템.
이 패키지는 원래 Jan Wieck에 의해 작성되었습니다.
토토 핫/pgSQL의 설계 목표는 로드 가능한 SQL을 생성하는 것이었습니다. 절차적 언어
함수를 생성하고 프로시저를 트리거하는 데 사용할 수 있습니다.
제어 구조를 다음에 추가합니다SQL언어,
복잡한 계산을 수행할 수 있습니다.
모든 사용자 정의 유형, 함수 및 연산자,
서버에서 신뢰하도록 정의할 수 있습니다.
사용하기 쉽습니다.
토토 핫/pgSQL 호출 핸들러는 함수 소스 텍스트를 구문 분석하고 처음으로 내부 이진 명령어 트리를 생성합니다. 함수는 백엔드에 의해 호출됩니다. 생성된 바이트코드는 다음과 같습니다. 함수의 개체 ID로 호출 처리기에서 식별됩니다. 이는 DROP/CREATE 시퀀스로 함수를 변경하는 것을 보장합니다. 새 데이터베이스를 구축하지 않고도 적용됩니다. 연결.
모든 표현식 및SQL함수, 토토 핫/pgSQL 바이트코드 인터프리터에 사용된 명령문 SPI 관리자를 사용하여 준비된 실행 계획을 생성합니다. SPI_prepare() 및 SPI_saveplan() 함수. 이것은 처음으로 수행됩니다. 시간이 지나면 개별 명령문은 토토 핫/pgSQL에서 처리됩니다. 기능. 따라서 많은 내용을 포함하는 조건부 코드가 있는 함수는 실행 계획이 필요한 명령문은 전체 기간 동안 실제로 사용되는 계획을 준비하고 저장하십시오. 데이터베이스 연결 수명.
입/출력 변환 및 계산 기능 제외 사용자 정의 유형의 경우 C 언어로 정의할 수 있는 모든 것 기능은 토토 핫/pgSQL로도 수행될 수 있습니다. 생성이 가능합니다 복잡한 조건부 계산 함수를 사용하고 나중에 이를 사용하여 연산자를 정의하거나 함수형 인덱스에 사용하세요.
토토 핫/pgSQL 언어는 대소문자를 구분하지 않습니다. 모든 키워드와 식별자는 대문자와 소문자를 혼합하여 사용할 수 있습니다.
토토 핫/pgSQL은 블록 지향 언어입니다. 블록은 다음과 같이 정의됩니다.
[<<라벨]
[선언선언]
시작문장END;문 섹션에는 하위 블록이 얼마든지 있을 수 있습니다
블록의. 하위 블록을 사용하여 외부에서 변수를 숨길 수 있습니다.
진술 블록. 선언에 선언된 변수
블록 앞의 섹션은 기본값으로 초기화됩니다.
함수 호출당 한 번만이 아니라 블록이 입력될 때마다.BEGIN/END의 의미를 오해하지 않는 것이 중요합니다 토토 핫/pgSQL의 문 그룹화 및 데이터베이스 명령 거래 통제. 함수 및 트리거 프로시저를 시작할 수 없습니다. 또는 트랜잭션을 커밋하고포스트그레중첩되지 않았습니다. 거래.
토토 핫/pgSQL에는 두 가지 유형의 주석이 있습니다. 이중 대시 '--' 줄 끝까지 확장되는 주석을 시작합니다. '/*'가 시작됩니다. 다음 '*/' 발생까지 확장되는 블록 주석입니다. 블록 주석은 중첩될 수 없지만 이중 대시 주석은 포함할 수 있습니다. 블록 주석에 추가하고 이중 대시를 사용하면 블록 주석을 숨길 수 있습니다. 구분 기호 '/*' 및 '*/'.
블록에 사용된 모든 변수, 행 및 레코드 하위 블록은 블록의 선언 섹션에서 선언되어야 합니다. 범위를 반복하는 FOR 루프의 루프 변수를 제외하고 정수 값. 토토 핫/pgSQL 함수에 제공되는 매개변수는 다음과 같습니다. 일반적인 식별자 $n을 사용하여 자동으로 선언됩니다. 는 선언에는 다음 구문이 있습니다.
지정된 기본 유형의 변수를 선언합니다. 만약 변수가 CONSTANT로 선언되면 값을 변경할 수 없습니다. NOT NULL을 지정하면 NULL 값이 할당됩니다. 런타임 오류가 발생했습니다. 모든 변수의 기본값은SQLNULL 값, 모든 변수 NOT NULL로 선언된 항목에는 기본값도 있어야 합니다. 지정되었습니다.
기본값은 함수가 실행될 때마다 평가됩니다. 전화했다. 그래서 '를 할당합니다지금' 에 유형의 변수날짜시간변수가 실제 함수의 시간을 갖도록 합니다. 함수가 사전 컴파일되었을 때가 아닙니다. 바이트코드.
주어진 클래스의 구조를 가진 행을 선언합니다. 수업 데이터베이스의 기존 테이블 또는 뷰 이름이어야 합니다. 는 행의 필드는 점 표기법으로 액세스됩니다. 매개변수 함수에는 복합 유형(전체 테이블 행)이 될 수 있습니다. 에서 이 경우 해당 식별자 $n은 행 유형이 됩니다. 그러나 아래에 설명된 ALIAS 명령을 사용하여 별칭을 지정해야 합니다. 테이블 행의 사용자 속성에만 액세스할 수 있습니다. 행, Oid 또는 기타 시스템 속성 없음(따라서 행은 다음과 같을 수 있음) 보기 및 보기 행에는 유용한 시스템이 없습니다. 속성).
rowtype의 필드는 테이블 fieldsizes 또는 char() 등의 데이터 유형에 대한 정밀도.
레코드는 행 유형과 유사하지만 사전 정의된 항목이 없습니다. 구조. 선택 및 FOR 루프에 사용됩니다. SELECT 작업에서 하나의 실제 데이터베이스 행. 하나와 동일한 레코드를 다른 선택에 사용할 수 있습니다. 액세스 레코드를 기록하거나 레코드 필드에 값을 할당하려고 시도하는 경우 실제 행이 없으면 런타임 오류가 발생합니다.
트리거의 NEW 및 OLD 행이 프로시저에 제공됩니다. 기록으로. 이는 다음과 같은 이유로 필요합니다.포스트그레하나의 동일한 트리거 프로시저는 다양한 테이블에 대한 트리거 이벤트를 처리할 수 있습니다.
코드의 더 나은 가독성을 위해 다음을 정의할 수 있습니다. 함수에 대한 위치 매개변수의 별칭입니다.
이 별칭은 다음과 같이 주어진 복합 유형에 필요합니다. 함수에 대한 인수. SQL에서와 같은 점 표기법 $1.salary 토토 핫/pgSQL에서는 함수가 허용되지 않습니다.
변수, 레코드 또는 행의 이름을 변경합니다. 이것은 유용하다 NEW 또는 OLD를 다른 이름으로 참조해야 하는 경우 트리거 절차.
변수 유형은 다음의 기존 기본 유형 중 하나일 수 있습니다. 데이터베이스.유형에 위의 선언 섹션은 다음과 같이 정의됩니다.
포스트그레스-기본 유형
변수%TYPE
class.field%TYPE
변수이름은 이전에 동일한 함수에서 선언된 변수로서 표시됩니다. 이 시점에서.
클래스기존의 이름입니다. 테이블 또는 뷰 위치필드이것은 속성의 이름입니다.
다음을 사용하여class.field%TYPE 토토 핫/pgSQL이 처음에 속성 정의를 조회하게 합니다. 백엔드 수명 동안 기능을 호출합니다. 테이블을 갖다 char(20) 속성과 다음을 처리하는 일부 토토 핫/pgSQL 함수를 사용합니다. 지역 변수에 내용이 있습니다. 이제 누군가가 그걸 결정해 char(20)이 충분하지 않습니다. 테이블을 덤프하고 삭제한 후 지금 다시 생성합니다. 문제의 속성을 char(40)으로 정의하고 데이터. 하 - 그는 기능을 잊어버렸어요. 내부 계산 값이 20자로 잘립니다. 하지만 만약 그들이 를 사용하여 정의됨class.field%TYPE 선언하면 자동으로 크기 변경을 처리합니다. 새 테이블 스키마는 속성을 텍스트 유형으로 정의합니다.
토토 핫/pgSQL 문에 사용된 모든 표현식은 다음을 사용하여 처리됩니다. 백엔드 실행자. 포함하고 있는 것으로 보이는 표현 상수에는 실제로 런타임 평가가 필요할 수 있습니다(예: 'now' 날짜/시간 유형) 따라서 토토 핫/pgSQL 파서가 다음을 수행하는 것은 불가능합니다. NULL 키워드가 아닌 실제 상수 값을 식별합니다. 모두 표현식은 쿼리를 실행하여 내부적으로 평가됩니다.
선택표현SPI 관리자를 사용합니다. 표현에서는 다음과 같은 일이 발생합니다. 변수 식별자는 매개변수로 대체되며 실제 변수의 값은 실행 프로그램에 전달됩니다. 매개변수 배열. 토토 핫/pgSQL 함수에 사용되는 모든 표현식은 다음과 같습니다. 한 번만 준비하고 저장했습니다.
다음에 의해 수행된 유형 검사포스트그레주 파서에는 몇 가지 부작용이 있습니다. 상수 값의 해석. 자세하게는 두 기능의 차이점
CREATE FUNCTION logfunc1(텍스트) RETURNS datetime AS '
선언
$1에 대한 logtxt 별칭;
시작
INSERT INTO 로그 테이블 VALUES(logtxt, ''now'');
RETURN ''지금'';
끝;
' 언어 'plpgsql';그리고CREATE FUNCTION logfunc2 (텍스트) RETURNS datetime AS '
선언
$1에 대한 logtxt 별칭;
curtime 날짜 시간;
시작
curtime := ''지금'';
INSERT INTO 로그 테이블 VALUES(logtxt, curtime);
복귀 시간;
끝;
' 언어 'plpgsql';할. logfunc1()의 경우,포스트그레메인 파서는 다음을 준비할 때 알고 있습니다.
INSERT에 대한 계획은 문자열 'now'가 다음과 같이 해석되어야 함을 의미합니다.
logtable의 대상 필드가 해당 유형이기 때문에 datetime입니다.
따라서 이 시점에서 상수를 만들 것입니다.
그런 다음 상수 값은 동안 logfunc1()의 모든 호출에 사용됩니다.
백엔드의 수명. 이건 아니라고 해도 과언이 아니다
프로그래머가 원했어요.logfunc2()의 경우,포스트그레스주 파서는 어떤 유형인지 모릅니다. 'now'는 다음과 같으므로 텍스트 데이터 유형을 반환합니다. 'now' 문자열을 포함합니다. 현지에 파견되는 동안 curtime 변수를 사용하면 토토 핫/pgSQL 인터프리터는 이 문자열을 text_out() 및 datetime_in() 함수를 호출하여 datetime 유형 전환을 위해.
이 유형 확인은 다음에 의해 수행됩니다.포스트그레스주 파서가 다음 이후에 구현되었습니다. 토토 핫/pgSQL이 거의 완료되었습니다. 6.3과 6.4의 차이입니다. 준비된 계획 기능을 사용하여 모든 기능에 영향을 미칩니다. SPI 관리자. 위의 방식으로 지역 변수를 사용하는 것은 현재 토토 핫/pgSQL에서 해당 값을 해석하는 유일한 방법입니다. 정확하게.
레코드 필드가 표현식이나 명령문에 사용되는 경우 데이터는 필드 유형은 하나의 호출과 동일한 호출 간에 변경되어서는 안 됩니다. 표현. 다음과 같은 트리거 프로시저를 작성할 때 이 점을 명심하십시오. 둘 이상의 테이블에 대한 이벤트를 처리합니다.
토토 핫/pgSQL 파서가 지정된 대로 이해하지 못하는 모든 것 아래는 쿼리에 포함되어 데이터베이스 엔진으로 전송됩니다. 실행하다. 결과 쿼리는 데이터를 반환해서는 안 됩니다.
변수 또는 행/레코드 필드에 값 할당 다음과 같이 작성됩니다.
식별자 := 표현;
식 결과 데이터 유형이 일치하지 않는 경우
변수 데이터 유형이거나 변수의 크기/정밀도가 다음과 같습니다.
알려진 경우(char(20)의 경우) 결과 값은 암시적으로 캐스팅됩니다.
결과 유형 출력을 사용하는 토토 핫/pgSQL 바이트 코드 해석기에 의해
변수 유형은 입력 함수입니다. 이 수 있습니다.
유형 입력으로 인해 런타임 오류가 발생할 가능성이 있습니다.
기능.완전한 선택 항목을 레코드 또는 행에 할당 다음으로 수행할 수 있습니다.
선택표현INTO대상발신 ...;대상레코드, 행일 수 있음 변수 또는 쉼표로 구분된 변수 목록 및 레코드/행 필드.
행 또는 변수 목록이 대상으로 사용되는 경우 선택된 값은 대상의 구조와 정확히 일치해야 합니다. 런타임 오류가 발생합니다. FROM 키워드 뒤에는 어떤 것이든 올 수 있습니다. 부여할 수 있는 유효한 자격, 그룹화, 정렬 등 SELECT 문의 경우.
bool 유형의 FOUND라는 특수 변수가 있습니다. SELECT INTO 직후에 사용하여 다음을 확인할 수 있습니다. 임무가 성공했습니다.
SELECT * INTO myrec FROM EMP WHERE empname = myname;
발견되지 않은 경우
RAISE EXEPTION ''직원 %를 찾을 수 없습니다'', myname;
종료하면;선택이 여러 행을 반환하는 경우 첫 번째 행만
대상 필드로 이동했습니다. 다른 모든 항목은 자동으로 삭제됩니다.a에 정의된 모든 함수프로스트그레스데이터베이스가 값을 반환합니다. 따라서, 함수를 호출하는 일반적인 방법은 SELECT 쿼리를 실행하는 것입니다. 또는 할당을 수행하는 중(토토 핫/pgSQL 내부 선택). 하지만 누군가가 관심을 갖지 않는 경우도 있습니다. 함수 결과입니다.
수행질의'SELECT를 실행합니다.질의' SPI 관리자를 통해 결과를 삭제합니다. 다음과 같은 식별자 지역 변수는 여전히 매개변수로 대체됩니다.
반환표현함수가 종료되고 값은표현상위로 반환됩니다. 집행자. 함수의 반환 값은 정의되지 않을 수 없습니다. 만약에 제어가 함수의 최상위 블록 끝에 도달함 RETURN 문을 누르지 않으면 런타임 오류가 발생합니다.
표현식 결과는 자동으로 함수는 할당에 대해 설명한 대로 유형을 반환합니다.
위의 예에서 알 수 있듯이 RAISE가 있습니다. 메시지를 던질 수 있는 명령문은포스트그레elog 메커니즘.
상승레벨 ''형식'' [, 식별자 [...]];형식 내에서 "%"는 후속 쉼표로 구분된 식별자입니다. 가능한 수준은 DEBUG입니다. (프로덕션 실행 데이터베이스에서는 자동으로 억제됨), 주의사항 (데이터베이스 로그에 기록되어 클라이언트에 전달됨) 애플리케이션) 및 EXCEPTION(데이터베이스 로그에 기록되며 거래를 중단합니다).
IF표현그때문장[ELSE문장]
종료하면;그표현다음을 반환해야 합니다.
최소한 부울 유형으로 캐스팅될 수 있는 값입니다.루프에는 여러 유형이 있습니다.
[<<라벨]
루프문장END LOOP;다음에 의해 명시적으로 종료되어야 하는 무조건 루프입니다.
EXIT 문. 선택적 라벨은 EXIT에서 사용할 수 있습니다.
중첩 루프의 명령문을 사용하여 중첩 수준을 지정해야 합니다.
종료됩니다.[<<라벨]
동안표현루프문장END LOOP;평가가 실행되는 동안 실행되는 조건부 루프
의표현사실입니다.[<<라벨]
에 대한이름IN [ 역방향 ]표현 .. 표현루프문장END LOOP;정수 값 범위를 반복하는 루프입니다. 는
변수이름자동으로
정수 유형으로 생성되며 루프 내부에만 존재합니다. 둘
범위의 하한과 상한을 제공하는 표현식은 다음과 같습니다.
루프에 들어갈 때만 평가됩니다. 반복 단계는 항상
1.[<<라벨]
에 대한기록 | 행INselect_clause루프문장END LOOP;레코드 또는 행은 다음의 결과로 발생한 모든 행에 할당됩니다.
select 절과 각각에 대해 실행되는 명령문입니다. 루프가
EXIT 문으로 종료되어도 마지막으로 할당된 행은 여전히 남아 있습니다.
루프 후에 액세스할 수 있습니다.종료 [라벨] [ 언제표현 ];아니면라벨주어진, 가장 안쪽 루프가 종료되고 END LOOP 다음 명령문이 실행됩니다. 다음에 실행됩니다. 만일라벨이다 주어진 경우 현재 또는 상위 수준의 레이블이어야 합니다. 중첩된 루프 블록. 그런 다음 명명된 루프나 블록이 종료되고 루프/블록 이후의 명령문으로 제어가 계속됩니다. 해당 END.
토토 핫/pgSQL을 사용하여 트리거 프로시저를 정의할 수 있습니다. 그들은 일반적인 CREATE FUNCTION 명령을 사용하여 함수로 생성됩니다. 인수가 없고 반환 유형이 OPAQUE입니다.
몇몇이 있습니다포스트그레트리거 프로시저로 사용되는 함수의 특정 세부정보.
먼저 그들은 다음에서 자동으로 생성된 특별한 변수를 가지고 있습니다. 최상위 블록 선언 섹션. 그들은
데이터 유형 RECORD; 새 데이터베이스 행을 보유하는 변수 ROW 수준 트리거에 대한 INSERT/UPDATE 작업.
데이터 유형 RECORD; 이전 데이터베이스 행을 보유하는 변수 ROW 수준 트리거에 대한 UPDATE/DELETE 작업.
데이터 유형 이름; 이름을 포함하는 변수 트리거가 실제로 실행되었습니다.
데이터 유형 텍스트; 'BEFORE' 또는 'AFTER' 문자열 트리거 정의에 따라 다릅니다.
데이터 유형 텍스트; 'ROW' 또는 'STATEMENT'의 문자열 트리거 정의에 따라 다릅니다.
데이터 유형 텍스트; 'INSERT', 'UPDATE' 또는 'DELETE' 문자열 트리거가 실제로 실행되는 작업을 알려줍니다.
데이터 유형 oid; 오류를 발생시킨 테이블의 개체 ID 트리거 호출.
데이터 유형 이름; 트리거를 발생시킨 테이블의 이름 호출.
데이터 유형 정수; 주어진 인수의 수 CREATE TRIGGER 문의 트리거 프로시저입니다.
텍스트의 데이터 유형 배열; CREATE의 인수 TRIGGER 문. 인덱스는 0부터 계산되며 다음과 같이 주어질 수 있습니다. 표현. 잘못된 인덱스(< 0 또는 = tg_nargs) 결과는 NULL 값이 됩니다.
두 번째로 그들은 NULL 또는 다음을 포함하는 레코드/행을 반환해야 합니다. 트리거가 실행된 테이블의 구조와 정확히 일치합니다. AFTER가 실행된 트리거는 항상 NULL 값을 반환할 수 있습니다. 효과. 트리거 관리자에게 건너뛰도록 신호를 보내기 전에 트리거가 실행됩니다. NULL을 반환할 때 이 실제 행에 대한 작업입니다. 그렇지 않으면, 반환된 레코드/행은 운영. NEW에서 단일 값을 직접 교체하는 것이 가능합니다. 이를 반환하거나 완전히 새로운 레코드/행을 작성하려면 돌아오다.
포스트그레스아주 많지 않습니다 스마트 예외 처리 모델. 파서가 실행될 때마다, 플래너/옵티마이저 또는 실행자는 명령문이 다음과 같이 결정될 수 없다고 결정합니다. 더 이상 처리되지 않으면 전체 거래가 중단되고 시스템은 메인 루프로 다시 점프하여 다음 쿼리를 가져옵니다. 클라이언트 애플리케이션.
오류 메커니즘에 연결하여 다음을 알 수 있습니다. 이런 일이 발생합니다. 하지만 현재로서는 실제로 무엇인지 알 수 없습니다. 중단을 일으켰습니다(입/출력 변환 오류, 부동 소수점 오류, 구문 분석 오류). 그리고 데이터베이스 백엔드가 이 시점에서는 일관성이 없는 상태이므로 상위로 돌아갑니다. 실행 프로그램을 실행하거나 더 많은 명령을 실행하면 전체 데이터베이스가 손상될 수 있습니다. 그리고 이 시점에서 거래가 이루어졌다는 정보가 있다고 하더라도 중단되었으며 이미 클라이언트 애플리케이션으로 전송되었으므로 재개합니다. 조작이 의미가 없습니다.
따라서 토토 핫/pgSQL이 현재 수행하는 유일한 일은 함수 또는 트리거 프로시저 실행 중 중단은 다음과 같습니다. 어떤 추가 DEBUG 수준 로그 메시지를 작성하여 함수 및 where(행 번호 및 명령문 유형) 일어났습니다.
다음은 토토 핫/pgSQL이 얼마나 쉬운지 보여주는 몇 가지 함수입니다. 함수를 작성할 수 있습니다. 더 복잡한 예를 보려면 프로그래머 토토 핫/pgSQL에 대한 회귀 테스트를 살펴볼 수도 있습니다.
토토 핫/pgSQL에서 함수 작성에 대한 한 가지 고통스러운 세부 사항은 작은따옴표 처리. CREATE의 함수 소스 텍스트 FUNCTION은 리터럴 문자열이어야 합니다. 리터럴 내부의 작은따옴표 문자열은 두 배로 묶거나 백슬래시로 묶어야 합니다. 우리는 여전히 우아한 대안을 찾고 있습니다. 그 사이 두배로 늘었다 아래 예와 같은 단일 인용문을 사용해야 합니다. 모두 이에 대한 솔루션은 향후 버전에서 제공됩니다.포스트그레스상위 버전과 호환됩니다.
다음 두 토토 핫/pgSQL 함수는 해당 함수와 동일합니다. C 언어 함수 토론의 대응 부분입니다.
CREATE FUNCTION add_one (int4)는 int4를 '로 반환합니다.
시작
$1 + 1을 반환합니다.
끝;
' 언어 'plpgsql';
CREATE FUNCTION concat_text (text, text) 텍스트를 '로 반환합니다.
시작
1달러를 돌려주세요 || $2;
끝;
' 언어 'plpgsql';
다시 말하면 The C의 예와 동일한 토토 핫/pgSQL입니다. 기능.
CREATE FUNCTION c_overpaid (EMP, int4) RETURNS bool AS '
선언
$1에 대한 emprec 별칭;
살림 별칭 $2;
시작
IF emprec.salary ISNULL 그러면
''f''를 반환합니다.
종료하면;
RETURN emprec.salary sallim;
끝;
' 언어 'plpgsql';
이 트리거는 행이 삽입되거나 업데이트될 때마다 테이블에는 현재 사용자 이름과 시간이 표시됩니다. 행. 그리고 직원의 이름이 제공되고 급여는 양수 값입니다.
테이블 emp 생성(
엠프 이름 텍스트,
급여 int4,
last_date 날짜시간,
last_사용자 이름);
CREATE FUNCTION emp_stamp ()는 불투명한 값을 반환합니다.
시작
-- 사원명과 급여가 입력되었는지 확인하세요.
NEW.empname이 ISNULL인 경우
RAISE EXCEPTION ''empname은 NULL 값이 될 수 없습니다'';
종료하면;
NEW.salary가 NULL인 경우
RAISE EXCEPTION ''%는 NULL 급여를 가질 수 없습니다'', NEW.empname;
종료하면;
-- 그녀가 비용을 지불해야 할 때 누가 우리를 위해 일합니까?
NEW.salary < 0이면
RAISE EXEPTION ''%는 음수 급여를 가질 수 없습니다'', NEW.empname;
종료하면;
-- 언제 급여를 변경했는지 기억해 보세요.
NEW.last_date := ''지금'';
NEW.last_user := getpgusername();
새로운 반품;
끝;
' 언어 'plpgsql';
emp를 삽입하거나 업데이트하기 전에 emp_stamp 트리거를 생성하세요.
각 행 실행 절차 emp_stamp();