| PostgreSQL 8.3.3문서 | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Fast Forward | Next | |
CREATE [ OR REPLACE ] FUNCTION
name
( [ [
argmode
] [
argname
]
argtype
[, ...] ] )
[ RETURNS
rettype
]
{ LANGUAGE
langname
| IMMUTABLE | STABLE | VOLATILE
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
| AS '
definition
'
| AS '
obj_file
', '
link_symbol
'
} ...
[ WITH (
attribute
[, ...] ) ]
CREATE FUNCTION는 새로운 함수를 정의합니다. CREATE OR REPLACE FUNCTION는, 새로운 함수의 작성, 또는, 기존 정의를 대체합니다.
스키마명이 포함되어 있는 경우, 함수는 지정된 스키마에서 작성됩니다. 스키마명이 없으면, 함수는 현재의 스키마에서 작성됩니다. 같은 스키마 내의 같은 인수 데이터형을 가지는 기존의 함수의 이름은, 새로운 함수의 이름으로 사용할 수 없습니다. 그러나, 다른 인수 데이터형을 가지는 함수이면, 이름이 중복해서 사용해도 상관하지 않습니다. (이것을,overloading라고 말합니다).
기존의 함수 정의를 갱신 하려면 ,CREATE OR REPLACE FUNCTION를 사용해 주세요. 이 방법에서는 함수의 이름이나 인수의 형태를 변경할 수 없는 것에 주의해 주세요 (이것을 실시했을 경우, 새롭고 다른 함수가 작성됩니다). 또한,CREATE OR REPLACE FUNCTION에서는, 기존의 함수의 반환값의 형태를 변경할 수 없습니다. 반환값의 형태를 변경하고 싶은 경우는, 그 함수를 삭제해, 재차 작성해 주세요. (이것은,OUT매개변수를 사용하고 있는 경우, 함수를 삭제하지 않는 한OUT매개변수의 이름이나 형태를 변경할 수 없는 것을 의미합니다. )
함수를 삭제해 재작성했을 경우, 새로운 함수는 이전 함수와 같은 엔티티가 되지 않습니다. 이전 함수를 참조하는, 기존의 룰, 뷰, 트리거 등을 삭제해야 할 것입니다. 함수를 참조하는 객체를 파괴하지 않게 함수 정의를 변경하려면 ,CREATE OR REPLACE FUNCTION를 사용해 주세요.
함수를 작성한 사용자가, 그 함수의 소유자가 됩니다.
작성하는 함수의 이름(검증을 거친 스키마)입니다.
인수의 모드는,IN,OUT또는 INOUT의 둘 중 하나가 됩니다. 생략시의 디폴트는IN입니다.
인수의 이름입니다. 일부 언어(현재 PL/pgSQL만 가능)중 에는 이 이름을 함수 본체로 사용할 수 있는 것도 있습니다 다른 언어에서는, 입력 인수의 이름은 단순한 추가 문서로서 다루어집니다. 그러나, 출력 인수의 이름은, 결과 행형의 열명을 정의하기 때문에 중요합니다. (출력 인수의 이름을 생략 했을 경우, 시스템은 디폴트 열명을 부여합니다. )
함수의 인수의 데이터형입니다(검증을 거친 스키마). 기본 데이터형, 복합 데이터형, 도메인형, 또는, 테이블열의 형태의 참조를 사용할 수가 있습니다.
또, 언어의 실행에 의존합니다만,cstring라고 하는"pseudotypes"을 지정할 수 있는 경우도 있습니다. 유사형은, 인수의 형태의 지정이 불완전하다, 혹은, 통상의 SQL 데이터형의 집합을 넘어서는 것을 나타냅니다.
열의 형태를 참조하려면 , tablename . columnname %TYPE를 기술합니다. 이것을 사용하면, 테이블 정의가 변경되어도 함수가 영향을 받지 않도록 도움을 줄 수 있습니다.
함수가 반환하는 데이터의 형태입니다(검증을 거친 스키마). 기본형, 복합형, 도메인형, 또는, 테이블열의 형태를 참조할 수 있습니다. 또, 실행하고 있는 언어에 의존 합니다만,cstring와 같은"유사형"도 지정하는 것이 가능합니다. 그 함수가 값을 반환하는 것을 상정하고 있지 않는 경우는, 반환값으로서void를 지정해 주세요.
OUT혹은INOUT매개변수가 존재하는 경우,RETURNS구를 생략 할 수가 있습니다. 생략 하지 않는 경우는, 출력용 매개변수가 의미하는 결과형과 일치 해야합니다, 즉, 복수의 출력용 매개변수가 있으면RECORD, 또는 단일의 출력용 매개변수이면 그것과 같은 형태가 아니면 안됩니다.
SETOF수식자는, 그 함수가, 1개는 아니고 복수의 아이템의 집합을 반환하는 것을 나타냅니다.
열의 형태는, tablename . columnname %TYPE라고 기술함으로써, 참조됩니다.
함수를 실행하고 있는 언어의 이름입니다. 이 매개변수에는,SQL,C,internal, 혹은 사용자 정의 절차 언어의 이름을 지정 할 수 있습니다. 후방 호환을 유지하기 위해, 이 이름을 단일 인용부호로 둘러쌀 수도 있습니다.
이러한 속성은, 함수의 동작에 관한 정보를 쿼리 최적화에 제공합니다. 많아야 1개의 키워드만 지정할 수 있습니다. 지정이 없는 경우는, 디폴트로VOLATILE라고 해석됩니다.
IMMUTABLE는, 데이터베이스에 대한 변경을 실시하지 않는 것, 및, 그 함수에 같은 인수치를 주었을 경우에 항상 같은 결과를 반환하는 것을 나타냅니다. 이것은, 데이터베이스를 검색하거나, 인수 리스트중에 직접 존재하지 않는 정보를 사용하거나 하지 않는다고 하는 것입니다. 이 옵션이 지정되었을 경우, 인수가 모두 정수인 함수 호출은, 즉시 함수값으로 대체할 수 있습니다.
STABLE는, 데이터베이스에 대한 변경을 실시하지 않는 것, 및, 단일 테이블 스캔 내에서 같은 인수값에 대해서는 항상 같은 결과를 반환 하지만,
SQL문이 다르면, 결과가 변경될 가능성이 있는 것을 나타냅니다.
이것은, 데이터베이스 검색이나(현재의 시간대와 같은) 매개변수 등에 의존하는 결과를 갖는 함수를 선택하는 것이 적절합니다.
또,current_timestamp계의 함수는, 1개의 트랜잭션내에서는 값이 변화하지 않기 때문에, STABLE인 것에 주의해 주세요.
VOLATILE는, 단일 테이블 스캔 내에 있어 함수의 값이 변화할 가능성이 있기 때문에, 최적화할 수 없는 것을 나타냅니다. 이러한 의미에서, 몇 안되는 변동적인 데이터베이스 함수가 있습니다. 예를 들면,random(),currval(),timeofday()등은 변동적인 함수입니다. 그러나, 예를 들면setval()등의 부작용이 있는 함수는, 그 결과를 완전하게 예측할 수 있다고 해도, 호출을 최적화하지 않게, 변동적으로 분류할 필요가 있는 것에 주의해 주세요.
자세한 것은Section 33.6을 참조해 주세요.
CALLED ON NULL INPUT(디폴트)를 지정하면, 인수에 NULL가 포함되어 있어도, 함수가 호출 됩니다. 그 경우는, 필요에 따라서 NULL값을 확인해, 적절한 대응을 하는 것은 함수 작성자의 책임입니다.
RETURNS NULL ON NULL INPUT혹은STRICT를 지정하면, 함수의 인수에 NULL이 있는 경우, 항상 NULL을 반환합니다. 이 매개변수가 지정되면, NULL 인수가 있는 경우, 함수는 실행되지 않습니다. 대신에, NULL이라고 하는 결과가 자동적으로 주어집니다.
SECURITY INVOKER를 지정하면, 함수를 호출한 유저의 권한으로, 그 함수가 실행됩니다. 이것이 디폴트입니다. SECURITY DEFINER를 지정하면, 함수를 작성한 유저의 권한으로, 그 함수가 실행됩니다.
EXTERNAL키워드는, SQL와의 호환성을 유지하기 위해, 허용되고 있습니다. 그러나, SQL와는 달리, 이 기능은 외부 함수에게만 적용되는 것은 아니기 때문에, 이 키워드는 생략 가능합니다.
함수를 정의하는 문자열 상수입니다. 이 매개변수의 의미는 언어에 의존합니다. 내부적인 함수명, 객체 파일에의 경로, SQL 커멘드, 절차 언어로 기술된 텍스트등을 지정할 수 있습니다.
이 구문의AS구는, C언어의 원시 코드중의 함수명이 SQL 함수의 이름과 같지 않은 경우, 동적으로 로드 되는 C언어 함수에 사용됩니다. obj_file 라고 하는 문자열은 동적으로 로드할 수 있는 객체를 포함한 파일의 이름으로, link_symbol 는 그 함수의 링크 심볼, 즉, C언어 원시 코드중의 함수의 이름입니다. 링크 심볼이 생략 되었을 경우, 정의되는 SQL 함수의 이름과 같은 것이라고 가정됩니다.
함수에 관한 정보를, 부분적으로 선택해 지정하는 전통적인 방법입니다. 여기서 지정할 수 있는 속성을 이하에 나타냅니다.
STRICT또는RETURNS NULL ON NULL INPUT와 같습니다.
isCachable는,IMMUTABLE와 같습니다만, 이미 폐지되고 있습니다. 그러나, 후방 호환성을 보관 유지하기 위해, 아직은 유효합니다.
속성명에서는, 대문자 소문자를 구별하지 않습니다.
한층 더 자세한 외부 함수의 작성 방법에 대해서는Section 33.3을 참조해 주세요.
이 커멘드에서는,SQL형의 구문에 있어서의 입력 인수와 반환값을 고려합니다. 그러나, 형태 지정의 세부사항(예를 들면numeric형의 정밀도 필드등)는, 그 함수의 실행에 책임이 있어,CREATE FUNCTION커멘드에서는 경고하는 일 없이 수용 합니다(이러한 세부사항은 인식되지 않습니다. 또, 세부사항에 대해서, 특정의 사양을 강제하는 일도 없습니다).
PostgreSQL는 overload함수을 허가합니다. 즉, 인수의 형태가 차이가 나면, 복수의 다른 함수에 같은 이름을 사용할 수가 있습니다. 그러나, 모든 함수의 C언어에 있어서의 이름은, 중복 해서는 안됩니다. 따라서, 오버로드된 C언어 함수에는, 다른 C언어의 이름을 줄 필요가 있습니다 (예를 들면, C언어에 있어서의 이름의 일부에 인수의 형태를 사용해 주세요).
여기서 OUT매개변수는 무시하고, 같은 이름 및 같은 입력용 인수형을 가지는 경우, 2개의 함수는 동일하다고 간주합니다. 따라서, 예를 들면 이하의 선언은 충돌하고 있습니다.
CREATE FUNCTION foo(int) ... CREATE FUNCTION foo(int, out text) ...
동일 객체 파일을 참조하는,CREATE FUNCTION호출이 반복 되었을 경우, 그 파일은 한 번만 로드 됩니다. (아마 개발 단계 동안) 파일을 언로드하고 재로드하려면 , LOAD 커멘드를 사용해 주세요.
사용자 정의의 함수를 삭제하려면 DROP FUNCTION 를 사용해 주세요.
함수를 정의하는 문자열을 기술할 때에, 통상의 단일 인용부호는 아니고, 달러 인용부호(Section 4.1.2.2참조)를 사용하면, 유용한 경우가 많습니다. 달러 인용부호를 사용하지 않은 경우, 함수 정의 내의 단일 인용부호나 백슬래시는 반드시 두 번씩 기술하여, 단일 인용부호의 사용을 피해야 합니다.
함수를 정의하려면 , 사용자는 그 언어의USAGE권한이 필요합니다.
여기에, 처음 시작을 돕기 위한 간단한 예가 있습니다. Section 33.3에는, 보다 많은 정보와 예가 기재되어 있습니다.
CREATE FUNCTION add(integer, integer) RETURNS integer
AS 'select $1 + $2;'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;
PL/pgSQL로, 인수명을 사용해, 정수를 증가 합니다.
CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$
BEGIN
RETURN i + 1;
END;
$$ LANGUAGE plpgsql;
복수의 출력용 매개변수를 가지는 레코드를 반환 합니다.
CREATE FUNCTION dup(in int, out f1 int, out f2 text)
AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
LANGUAGE SQL;
SELECT * FROM dup(42);
위와 같은 것을, 명시적인 이름이 붙은 복합형을 사용해, 보다 장황하게 실시할 수가 있습니다.
CREATE TYPE dup_result AS (f1 int, f2 text);
CREATE FUNCTION dup(int) RETURNS dup_result
AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
LANGUAGE SQL;
SELECT * FROM dup(42);
SECURITY DEFINER함수는 함수를 작성한사용자의 권한으로 실행되기 때문에, 그 함수를 잘못 사용할 수 없다는 것에 주의할 필요가 있습니다. 안전상,search_path은, 신뢰할 수 없는 사용자가 기입해 가능한 스키마를 제거한 형태로 설정됩니다. 이것은, 악의가 있는 사용자가 그 함수로 사용되는 객체를 숨기는 것 같은 객체를 작성하는 것을 방지 합니다. 여기서 특히 중요한 것은, 임시 테이블 스키마입니다. 이 스키마는 디폴트로 최초로 검색되고 그리고, 통상 누구라도 기입해 가능합니다. 임시 스키마의 검색을 강제적으로 마지막에 하는 것으로, 보안 조정을 할 수 있습니다. 이를 실행하기 위해,pg_temp를search_path의 마지막 항목으로서 기재해 주세요. 이 함수의 안전한 사용 방법을 이하에 나타냅니다.
CREATE FUNCTION check_password(uname TEXT, pass TEXT)
RETURNS BOOLEAN AS $$
DECLARE passed BOOLEAN;
old_path TEXT;
BEGIN
-- Save old search_path; notice we must qualify current_setting
-- to ensure we invoke the right function
old_path := pg_catalog.current_setting('search_path');
-- Set a secure search_path: trusted schemas, then 'pg_temp'.
-- We set is_local = true so that the old value will be restored
-- in event of an error before we reach the function end.
PERFORM pg_catalog.set_config('search_path', 'admin, pg_temp', true);
-- Do whatever secure work we came for.
SELECT (pwd = $2) INTO passed
FROM pwds
WHERE username = $1;
-- Restore caller's search_path
PERFORM pg_catalog.set_config('search_path', old_path, true);
RETURN passed;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;