他のバージョンの文書 15 | 14 | 13 | 12 | 11 | 10 | 9.6 | 9.5 | 9.4 | 9.3 | 9.2 | 9.1 | 9.0 | 8.4 | 8.3 | 8.2 | 8.1 | 8.0 | 7.4 | 7.3 | 7.2

CREATE FUNCTION

名前

CREATE FUNCTION -- 新しい関数を定義する

概要

CREATE [ OR REPLACE ] FUNCTION
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } defexpr ] [, ...] ] )
    [ RETURNS rettype
      | RETURNS TABLE ( colname coltype [, ...] ) ]
  { LANGUAGE langname
    | WINDOW
    | IMMUTABLE | STABLE | VOLATILE
    | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | COST execution_cost
    | ROWS result_rows
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
  } ...
    [ WITH ( attribute [, ...] ) ]

説明

CREATE FUNCTIONは新しい関数を定義します。 CREATE OR REPLACE FUNCTIONは、新しい関数の作成、または、既存定義の置換のどちらかを行います。

スキーマ名が含まれている場合、関数は指定されたスキーマで作成されます。 スキーマ名がなければ、関数は現在のスキーマで作成されます。 同じスキーマ内の同じ入力引数データ型を持つ既存の関数の名前は、新しい関数の名前として使用できません。 しかし、異なる引数データ型を持つ関数であれば、名前が重複しても構いません (これを、オーバーロードと言います)。

既存の関数定義を入れ替えるには、CREATE OR REPLACE FUNCTIONを使用してください。 この方法では関数の名前や引数の型を変更することはできないことに注意してください (これを行った場合、新しく別の関数が作成されます)。 また、CREATE OR REPLACE FUNCTIONでは、既存の関数の戻り値の型を変更することはできません。 戻り値の型を変更したい場合は、その関数を削除し、再度作成してください。 (これは、OUTパラメータを使用している場合、関数を削除しない限りOUTパラメータの名前や型を変更できないことを意味します。)

関数を削除し再作成した場合、新しい関数は古いものと同じ実体にはなりません。 古い関数を参照する、既存のルール、ビュー、トリガなどを削除しなければならないでしょう。 関数を参照するオブジェクトを破壊しないように関数定義を変更するには、CREATE OR REPLACE FUNCTIONを使用してください。 また、ALTER FUNCTIONを使用して、既存の関数の補助属性のほとんどを変更することができます。

関数を作成したユーザが、その関数の所有者となります。

パラメータ

name

作成する関数の名前です(スキーマ修飾名も可)。

argmode

引数のモードで、INOUTINOUTVARIADICのいずれかです。 省略時のデフォルトはINです。 OUT引数のみがVARIADICを1つ後に続けることができます。 また、RETURNS TABLE記法では、OUTINOUT引数の両方を使用することはできません。

argname

引数の名前です。 言語の中にはこの名前を関数本体で使用できるものもあります (今のところPL/pgSQLのみです)。 他の言語では、入力引数の名前は単なる追加ドキュメントとして扱われます。 しかし、出力引数の名前は、結果の行型の列名となりますので重要です。 (出力引数の名前を省略した場合、システムはデフォルトの列名を付与します。)

argtype

関数の引数のデータ型です(スキーマ修飾名も可)。 基本データ型、複合データ型、ドメイン型、または、テーブル列の型の参照を使用することができます。

また、言語の実装に依存しますが、cstringといった"疑似型"を指定できる場合もあります。 疑似型は、引数の型の指定が不完全である、もしくは、通常のSQLデータ型の集合を越えていることを示します。

列の型を参照するには、tablename.columnname%TYPEと記述します。 これを使用すると、テーブル定義が変更されても関数が影響を受けないようにするのに役に立つことがあります。

defexpr

パラメータが指定されなかった場合のデフォルト値として使用される式です。 この式はパラメータの引数型と型強制を持たなければなりません。 入力パラメータ(INOUTを含みます)のみがデフォルト値を持つことができます。 デフォルト値を持つパラメータの後ろにあるパラメータはすべて、同様にデフォルト値を持つパラメータでなければなりません。

rettype

関数が返すデータの型です(スキーマ修飾名も可)。 基本型、複合型、ドメイン型、または、テーブル列の型の参照を設定することができます。 また、実装している言語によりますが、cstringのような"疑似型"も指定することが可能です。 その関数が値を返すことを想定していない場合は、戻り値としてvoidを指定してください。

OUTもしくはINOUTパラメータが存在する場合、RETURNS句を省略することができます。 省略しない場合は、出力用パラメータが意味する結果型に従ったもの、つまり、複数の出力用パラメータがあればRECORD、単一の出力用パラメータであればそれと同じ型、でなければなりません。

SETOF修飾子は、その関数が、1つではなく複数のアイテムの集合を返すことを示します。

列の型は、tablename.columnname%TYPEと記述することで参照されます。

colname

RETURNS TABLE構文における出力列の名前です。 これは実際名前付けされたOUTパラメータを宣言する別の方法ですが、RETURNS TABLERETURNS SETOFをも意味する点が異なります。

coltype

RETURNS TABLE構文における出力列のデータ型です。

langname

関数を実装している言語の名前です。 このパラメータには、SQLCinternal、もしくはユーザ定義手続き言語の名前を指定可能です。 後方互換を保持するため、この名前を単一引用符で囲むこともできます。

WINDOW

WINDOWは、この関数が普通の関数ではなくウィンドウ関数であることを示します。 現在これはC言語で作成した関数のみに使用することができます。 既存の関数定義を置き換える場合、WINDOW属性を変更することはできません。

IMMUTABLE
STABLE
VOLATILE

これらの属性は、関数の動作に関する情報を問い合わせオプティマイザに提供します。 いずれか1つのキーワードのみ指定できます。 指定がない場合は、デフォルトでVOLATILEと解釈されます。

IMMUTABLEは、データベースに対する変更を行わないこと、および、その関数に同じ引数値を与えた場合に常に同じ結果を返すことを示します。 これは、データベースを検索したり、引数リスト中に直接存在しない情報を使用したりしないということです。 このオプションが指定された場合、引数が全て定数である関数呼び出しは、即座に関数値と置き換えることができます。

STABLEは、データベースに対する変更を行わないこと、および、その関数に同じ引数値を与えた場合、1テーブルスキャン内で常に同じ結果を返すが、 SQL文が異なると結果が変わってしまう可能性があることを示します。 これは、データベース検索や(現在の時間帯のような)パラメータ変数などに結果が依存する関数に適します。 また、current_timestamp系の関数は、1つのトランザクション内では値が変化しないため、STABLEであることに注意してください。

VOLATILEは、1テーブルスキャン内において関数の値が変化する可能性があるため、最適化できないことを示します。 このような意味で変動的(volatile)なデータベース関数は、比較的少数です。 例えば、random()currval()timeofday()などは変動的な関数です。 しかし、例えばsetval()などの副作用がある関数は、その結果を完全に予測できるとしても、呼び出しを最適化しないよう、VOLATILE(変動的)に分類する必要があることに注意してください。

詳細は項34.6を参照してください。

CALLED ON NULL INPUT
RETURNS NULL ON NULL INPUT
STRICT

CALLED ON NULL INPUT(デフォルト)を指定すると、引数にNULLが含まれていても、関数が通常通り呼び出されます。 その場合は、必要に応じてNULL値を確認し、適切な対応をすることは関数作成者の責任です。

RETURNS NULL ON NULL INPUTもしくはSTRICTを指定すると、関数の引数にNULLがある場合、常にNULLを返します。 このパラメータが指定されると、NULL引数がある場合、関数は実行されません。 代わりに、NULLという結果が自動的に与えられます。

[EXTERNAL] SECURITY INVOKER
[EXTERNAL] SECURITY DEFINER

SECURITY INVOKERを指定すると、関数を呼び出したユーザの権限で、その関数が実行されます。 これがデフォルトです。 SECURITY DEFINERを指定すると、関数を作成したユーザの権限で、その関数が実行されます。

EXTERNALキーワードは、SQLとの互換性を保つために許されています。 しかし、SQLとは異なり、この機能は外部関数にのみ適用されるわけではないため、このキーワードは省略可能です。

execution_cost

この関数の推定実行コストを表す正数で、単位はcpu_operator_costです。 関数が集合を返す場合、これは1行当たりのコストとなります。 このコストが指定されない場合、C言語および内部関数では1、他のすべての言語では100となります。 値をより大きくすると、プランナは必要以上に頻繁に関数を評価しないようになります。

result_rows

プランナが想定する、この関数が返す行数の推定値を表す正数です。 これは、関数が集合を返すものと宣言された場合のみ許されます。 デフォルト推定値は1000行です。

configuration_parameter
value

SET句により、関数が始まった時に指定した設定パラメータを指定した値に設定し、関数の終了時にそれを以前の値に戻すことができます。 SET FROM CURRENTは、セッションにおけるパラメータの現在値を関数が始まった時に適用する値として保管します。

使用可能なパラメータと値については、SETおよび第18章を参照してください。

definition

関数を定義する文字列定数です。 このパラメータの意味は言語に依存します。 内部的な関数名、オブジェクトファイルへのパス、SQLコマンド、手続き言語で記述されたテキストなどを指定できます。

obj_file, link_symbol

この構文のAS句は、C言語のソースコード中の関数名がSQL関数の名前と同じでない場合、動的にロードされるC言語関数に使われます。 obj_fileという文字列は動的にロードできるオブジェクトを含むファイルの名前で、link_symbolはそのオブジェクトのリンクシンボル、つまり、C言語ソースコード中の関数の名前です。 リンクシンボルが省略された場合、定義されるSQL関数の名前と同じものであると仮定されます。

attribute

関数に関する情報を、部分的に選択して指定する伝統的な方法です。 ここで指定できる属性を以下に示します。

isStrict

STRICTまたはRETURNS NULL ON NULL INPUTと同じです。

isCachable

isCachableは、IMMUTABLEと同じですが、既に廃止されています。 しかし、後方互換性を保持するため、まだ有効とされています。

属性名では、大文字小文字を区別しません。

注釈

さらに詳しい外部関数の作成方法については項34.3を参照してください。

このコマンドでは、SQL型の構文における入力引数と戻り値を考慮します。 しかし、型指定の細部(例えばnumeric型の精度フィールドなど)は、その関数の実装に責任があり、CREATE FUNCTIONコマンドでは警告することなく受容します(これらの細部は認識されません。また、細部に対して、特定の仕様を強制することもありません)。

PostgreSQLは関数のオーバーロードを許可します。 つまり、入力引数の型が異なっていれば、複数の関数に同じ名前を使用することができます。 しかし、全ての関数のC言語における名前は、重複してはいけません。 したがって、オーバーロードするC言語関数には、異なるC言語の名前を与える必要があります (例えば、C言語における名前の一部に引数の型を使用してください)。

同じ名前、同じ入力用パラメータ型を持つ場合、2つの関数は同一であるとみなされます。 OUTパラメータは無視されます。 したがって、例えば以下の3つの宣言は競合しています。

CREATE FUNCTION foo(int) ...
CREATE FUNCTION foo(int, out text) ...

異なる引数型のリストを持つ関数は、作成時に競合するとはみなされませんが、デフォルト値が指定された場合使用時に競合する可能性があります。 例えば以下を考えてみましょう。

CREATE FUNCTION foo(int) ...
CREATE FUNCTION foo(int, int default 42) ...

foo(10)という呼び出しは、どちらの関数を呼び出すべきかに関して曖昧さがあるために失敗します。

同一オブジェクトファイルを参照する、CREATE FUNCTION呼び出しが繰り返された場合、そのファイルはセッション毎に一度だけロードされます。 (おそらく開発段階で)ファイルをアンロードし再ロードするには、新しいセッションを開始してください。

ユーザ定義の関数を削除するにはDROP FUNCTIONを使用してください。

関数を定義する文字列を記述する際に、通常の単一引用符ではなく、ドル引用符(項4.1.2.4参照)を使用すると便利なことが多くあります。 ドル引用符を使用しない限り、関数定義内の単一引用符やバックスラッシュは必ず二重にしてエスケープしなければなりません。

関数に何らかのSET句が付いている場合、関数内部で実行されるSET LOCALコマンドの同一変数に対する効果はその関数に制限されます。 設定パラメータの前の値は関数が終了する時に元に戻ります。 しかし、通常の(LOCALがない)SETコマンドはSET句を上書きします。 これは過去に行われたSET LOCALコマンドに対してもほぼ同じです。 このコマンドの効果は、現在のトランザクションがロールバックされない限り、関数が終了した後も永続化されます。

関数を定義するには、ユーザはその言語のUSAGE権限が必要です。

CREATE OR REPLACE FUNCTIONが既存の関数に対して使用された場合、関数の所有者や権限は変更されません。 その他の関数の属性はすべてコマンドで指定または暗黙的に指定された値になります。 置き換えるためには関数を所有していなければなりません。 (所有するロールのメンバである場合も含みます。)

関数がVARIADIC引数を持つSTRICTと宣言された場合、その厳密性検査では可変長配列全体が非NULLかどうかを検査します。 配列にNULLの要素があっても関数は呼び出されます。

ここでは、初心者向けの簡単な例をいくつか示します。 項34.3には、より多くの情報と例が記載されています。

CREATE FUNCTION add(integer, integer) RETURNS integer
    AS 'select $1 + $2;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

PL/pgSQLで、引数名を使用して、整数を1増やします。

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);

複数列を返す別の方法は、TABLE関数を使用することです。

CREATE FUNCTION dup(int) RETURNS TABLE(f1 int, f2 text)
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

SELECT * FROM dup(42);

しかし、これは実際には、1つのレコードではなく、レコードの集合を返しますので、TABLE関数は上の例とは異なります。

SECURITY DEFINER関数の安全な作成

SECURITY DEFINER関数は関数を作成したユーザの権限で実行されますので、その関数を間違って使用できないことを確実にしなければなりません。 安全上、search_pathは、信頼できないユーザが書き込み可能なスキーマを除去した形で設定されます。 これは、悪意のあるユーザがその関数で使用されるオブジェクトを隠すようなオブジェクトを作成することを防ぎます。 ここで特に重要なことは、一時テーブルスキーマです。 このスキーマはデフォルトで最初に検索され、そして、通常誰でも書き込み可能です。 一時スキーマの検索を強制的に最後にすることで、セキュリティを調整できます。 このためには、pg_tempsearch_pathの最後の項目として記載してください。 この関数の安全な使用方法を以下に示します。

CREATE FUNCTION check_password(uname TEXT, pass TEXT)
RETURNS BOOLEAN AS $$
DECLARE passed BOOLEAN;
BEGIN
        SELECT  (pwd = $2) INTO passed
        FROM    pwds
        WHERE   username = $1;

        RETURN passed;
END;
$$  LANGUAGE plpgsql
    SECURITY DEFINER
    -- 信頼できるスキーマ、その後にpg_tempという順でsearch_pathを安全に設定します。
    SET search_path = admin, pg_temp;

PostgreSQLバージョン8.3より前では、SETオプションは利用できません。 このため古い関数には、search_pathを保管し、設定、そして元に戻すという、多少複雑なロジックが含まれているかもしれません。 こうした目的にSETオプションを使用すると、かなり簡単になります。

この他に注意すべき点として、新しく作成された関数ではデフォルトで実行権限がPUBLICに付与されていることがあります。 (詳細はGRANTを参照してください。) SECURITY DEFINER関数の使用を一部のユーザのみに制限したいことはよくあります。 このためには、デフォルトのPUBLIC権限を取り上げ、そして、実行権限の付与を選択して行ってください。 新しい関数がすべてのユーザに実行可能となる隙間が存在することを防ぐためには、単一トランザクション内で作成と権限設定を行ってください。 以下に例を示します。

BEGIN;
CREATE FUNCTION check_password(uname TEXT, pass TEXT) ... SECURITY DEFINER;
REVOKE ALL ON FUNCTION check_password(uname TEXT, pass TEXT) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION check_password(uname TEXT, pass TEXT) TO admins;
COMMIT;

互換性

CREATE FUNCTIONコマンドはSQL:1999以降で定義されています。 PostgreSQLにおけるCREATE FUNCTIONも類似の機能を持ちますが、完全な互換性はありません。 属性には移植性がありません。また、使用可能な言語も異なります。

他のデータベースシステムとの互換性のために、argmodeargnameの前に書くことも後に書くこともできます。 しかし、1つ目の方法が標準に従っています。

標準SQLはパラメータのデフォルトを規定していません。 DEFAULTキーワードを持つ構文はOracleに由来したもので、標準の流れを多少汲んだものです。 SQL/PSMではこれを変数のデフォルト値として使用しています。 =を持つ構文はT-SQLおよびFirebirdで使用されています。

関連項目

ALTER FUNCTION, DROP FUNCTION, GRANT, LOAD, REVOKE, createlang