CREATE FUNCTION — 新しい関数を定義する
CREATE [ OR REPLACE ] FUNCTIONname
( [ [argmode
] [argname
]argtype
[ { DEFAULT | = }default_expr
] [, ...] ] ) [ RETURNSrettype
| RETURNS TABLE (column_name
column_type
[, ...] ) ] { LANGUAGElang_name
| TRANSFORM { FOR TYPEtype_name
} [, ... ] | WINDOW | IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER | PARALLEL { UNSAFE | RESTRICTED | SAFE } | COSTexecution_cost
| ROWSresult_rows
| SETconfiguration_parameter
{ TOvalue
| =value
| FROM CURRENT } | AS 'definition
' | AS 'obj_file
', 'link_symbol
' } ... [ WITH (attribute
[, ...] ) ]
CREATE FUNCTION
は新しい関数を定義します。
CREATE OR REPLACE FUNCTION
は、新しい関数の作成、または、既存定義の置換のどちらかを行います。
関数を定義するには、ユーザはその言語のUSAGE
権限が必要です。
スキーマ名が含まれている場合、関数は指定されたスキーマに作成されます。 スキーマ名がなければ、関数は現在のスキーマに作成されます。 同じスキーマ内の同じ入力引数データ型を持つ既存の関数の名前は、新しい関数の名前として使用できません。 しかし、異なる引数データ型を持つ関数であれば、名前が重複しても構いません (これを、オーバーロードと言います)。
既存の関数定義を入れ替えるには、CREATE OR REPLACE FUNCTION
を使用してください。
この方法では関数の名前や引数の型を変更することはできません
(これを行った場合、新しく別の関数が作成されます)。
また、CREATE OR REPLACE FUNCTION
では、既存の関数の戻り値の型を変更することはできません。
戻り値の型を変更したい場合は、その関数を削除し、再度作成してください。
(これは、OUT
パラメータを使用している場合、関数を削除しない限りOUT
パラメータの型を変更できないことを意味します。)
CREATE OR REPLACE FUNCTION
を使用して既存の関数を置き換える場合、関数の所有者と権限は変わりません。
他の関数に関するすべての属性には、そのコマンドで指定された値、または暗黙的な値が設定されます。
関数を置き換えるためにはその関数を所有していなければなりません。
(これには所有するロールのメンバであることが含まれています。)
関数を削除し再作成した場合、新しい関数は古いものと同じ実体にはなりません。
古い関数を参照する、既存のルール、ビュー、トリガなどを削除しなければならないでしょう。
関数を参照するオブジェクトを破壊せずに関数定義を変更するには、CREATE OR REPLACE FUNCTION
を使用してください。
また、ALTER FUNCTION
を使用して、既存の関数の補助属性のほとんどを変更することができます。
関数を作成したユーザが、その関数の所有者となります。
関数を作成するためには、引数の型および戻り値の型に対するUSAGE
権限を持たなければなりません。
name
作成する関数の名前です(スキーマ修飾名も可)。
argmode
引数のモードで、IN
、OUT
、INOUT
、VARIADIC
のいずれかです。
省略時のデフォルトはIN
です。
OUT
引数のみがVARIADIC
の後に続けることができます。
また、RETURNS TABLE
記法では、OUT
とINOUT
引数の両方を使用することはできません。
argname
引数の名前です。 (SQLおよびPL/pgSQLを含む)言語の中にはこの名前を関数本体で使用できるものもあります。 他の言語では、関数そのものに注目する限り、入力引数の名前は単なる追加ドキュメントとして扱われます。 しかし関数呼び出し時に入力引数の名前を使用することで可読性を高めることができます。 (4.3. 関数呼び出し参照) どのような場合であっても、出力引数の名前は、結果の行型の列名となりますので重要です。 (出力引数の名前を省略した場合、システムはデフォルトの列名を付与します。)
argtype
関数の引数のデータ型です(スキーマ修飾名も可)。 基本データ型、複合データ型、ドメイン型、または、テーブル列の型の参照を使用することができます。
また、実装する言語に依存しますが、cstring
といった「疑似型」を指定できる場合もあります。
疑似型は、実引数の型の指定が不完全である、もしくは、通常のSQLデータ型の集合を越えていることを示します。
列の型を参照するには、
と記述します。
これを使用すると、テーブル定義が変更されても関数が影響を受けないようにするのに役に立つことがあります。
table_name
.column_name
%TYPE
default_expr
パラメータが指定されなかった場合のデフォルト値として使用される式です。
この式はパラメータの引数型と変換可能でなければなりません。
入力パラメータ(INOUT
を含みます)のみがデフォルト値を持つことができます。
デフォルト値を持つパラメータの後ろにあるパラメータはすべて、同様にデフォルト値を持たなければなりません。
rettype
関数が返すデータの型です(スキーマ修飾名も可)。
基本型、複合型、ドメイン型、または、テーブル列の型の参照を設定することができます。
また、実装している言語によりますが、cstring
のような「疑似型」も指定することが可能です。
その関数が値を返すことを想定していない場合は、戻り値としてvoid
を指定してください。
OUT
もしくはINOUT
パラメータが存在する場合、RETURNS
句を省略することができます。
省略しない場合は、出力用パラメータが意味する結果型に従ったもの、つまり、複数の出力用パラメータがあればRECORD
、単一の出力用パラメータであればそれと同じ型、でなければなりません。
SETOF
修飾子は、その関数が、1つではなく複数のアイテムの集合を返すことを示します。
列の型は、
と記述することで参照されます。
table_name
.column_name
%TYPE
column_name
RETURNS TABLE
構文における出力列の名前です。
これは実際名前付けされたOUT
パラメータを宣言する別の方法ですが、RETURNS TABLE
がRETURNS SETOF
をも意味する点が異なります。
column_type
RETURNS TABLE
構文における出力列のデータ型です。
lang_name
関数を実装している言語の名前です。
このパラメータには、sql
、c
、internal
、もしくはユーザ定義手続き言語(例:plpgsql
)の名前を指定可能です。
名前を単一引用符で囲むのは廃止予定で、大文字小文字の一致が必要になります。
TRANSFORM { FOR TYPE type_name
} [, ... ] }
関数呼び出しにどの変換を適用すべきかのリストです。 変換はSQLの型と言語独自のデータ型の間の変換を行います(CREATE TRANSFORMを参照)。 手続言語の実装では、通常、ビルトインの型についてハードコードされた知識があるので、それらをこのリストに含める必要はありません。 手続言語の実装が型の処理について定めておらず、変換が提供されない場合は、データ型変換のデフォルトの動作によることになりますが、これは実装に依存します。
WINDOW
WINDOW
は、この関数が普通の関数ではなくウィンドウ関数であることを示します。
現在これはC言語で作成した関数のみに使用することができます。
既存の関数定義を置き換える場合、WINDOW
属性を変更することはできません。
IMMUTABLE
STABLE
VOLATILE
これらの属性は、関数の動作に関する情報を問い合わせオプティマイザに提供します。
いずれか1つのキーワードのみ指定できます。
指定がない場合は、デフォルトでVOLATILE
と解釈されます。
IMMUTABLE
は、関数がデータベースに対する変更を行わないこと、および、その関数に同じ引数値を与えた場合に常に同じ結果を返すことを示します。
つまり、データベースを検索したり、引数リスト中に直接存在しない情報を使用したりしないということです。
このオプションが指定された場合、引数が全て定数である関数呼び出しは、即座に関数値と置き換えることができます。
STABLE
は、関数がデータベースに対する変更を行わないこと、および、その関数に同じ引数値を与えた場合、常に同じ結果を返すが、SQL文が異なると結果が変わってしまう可能性があることを示します。
これは、データベース検索や(現在の時間帯のような)パラメータ変数などに結果が依存する関数に適します。
(これは現在のコマンドで変更された行を問い合わせたいAFTER
トリガには不適切です。)
また、current_timestamp
系の関数は、1つのトランザクション内では値が変化しないため、STABLEであることに注意してください。
VOLATILE
は、1つのテーブルスキャン内でも関数の値が変化する可能性があるため、最適化できないことを示します。
このような意味で変動的(volatile)なデータベース関数は、比較的少数です。
例えば、random()
、currval()
、timeofday()
などは変動的な関数です。
しかし、例えばsetval()
などの副作用がある関数は、その結果を完全に予測できるとしても、呼び出しを最適化しないよう、VOLATILE
(変動的)に分類する必要があることに注意してください。
詳細は36.6. 関数の変動性分類を参照してください。
LEAKPROOF
LEAKPROOF
は、関数が副作用を持たないことを示します。
その引数に関する情報を戻り値以外で漏らしません。
例えば、一部の引数値に対してのみエラーメッセージを返す関数や何らかのエラーメッセージの中に引数の値を含める関数は漏洩防止(leakproof)とはいえません。
これはsecurity_barrier
オプション付きで作成されたビュー、あるいは行単位セキュリティが有効にされたテーブルに対して、システムが問い合わせを実行する方法に影響します。
データが偶然に露見することを防ぐため、システムは、漏洩防止でない関数を含む問い合わせのユーザが提供した条件より前に、セキュリティポリシーおよびセキュリティバリアビューの条件を強制します。
漏洩防止であるとされた関数および演算子は信頼できると見なされ、セキュリティポリシーおよびセキュリティバリアビューによる条件より先に実行されることがあります。
なお、引数を取らない、あるいはセキュリティバリアビューやテーブルから引数を渡されない関数は、セキュリティ条件より前に実行するために漏洩防止とする必要はありません。
CREATE VIEWおよび39.5. ルールと権限を参照してください。
このオプションはスーパーユーザによってのみ設定することができます。
CALLED ON NULL INPUT
RETURNS NULL ON NULL INPUT
STRICT
CALLED ON NULL INPUT
(デフォルト)を指定すると、引数にNULLが含まれていても、関数が通常通り呼び出されます。
その場合は、必要に応じてNULL値を確認し、適切な対応をすることは関数作成者の責任です。
RETURNS NULL ON NULL INPUT
もしくはSTRICT
を指定すると、関数の引数に1つでもNULLがある場合、常にNULLを返します。
このパラメータが指定されると、NULL引数がある場合、関数は実行されません。
代わりに、NULLという結果が自動的に与えられます。
[EXTERNAL] SECURITY INVOKER
[EXTERNAL] SECURITY DEFINER
SECURITY INVOKER
を指定すると、関数を呼び出したユーザの権限で、その関数が実行されます。
これがデフォルトです。
SECURITY DEFINER
を指定すると、関数を作成したユーザの権限で、その関数が実行されます。
EXTERNAL
キーワードは、SQLとの互換性を保つために許されています。
しかし、SQLとは異なり、この機能は外部関数だけではなくすべての関数に適用されるため、このキーワードは省略可能です。
PARALLEL
PARALLEL UNSAFE
は、その関数が並列モードでは実行できないこと、そしてそのような関数がSQL文の中にある場合は順次の実行プランが強制されることを意味します。
これがデフォルトです。
PARALLEL RESTRICTED
はその関数が並列モードで実行できますが、その実行は並列グループのリーダーに制限されることを意味します。
PARALLEL SAFE
はその関数が並列モードで制限なく実行することについて安全であることを意味します。
関数がデータベースの状態に何らかの変更を行う、サブトランザクションを使うなどトランザクションを変更する、シーケンスにアクセスするか設定に恒久的な変更をする(例えばsetval
)という場合はparallel unsafe(並列は安全でない)という印をつけるべきです。
一時テーブル、クライアントの接続状態、カーソル、プリペアド文、その他並列モードでシステムが同期できない様々なバックエンド独自の状態に関数がアクセスする場合、parallel restricted(並列は制限される)という印をつけるべきです(例えば、setseed
はグループのリーダー以外では実行できません。なぜなら他のプロセスでなされた変更がリーダーに反映されないからです)。
一般的に、restrictedあるいはunsafeな関数がsafeとラベル付けされた場合、あるいはunsafeな関数がrestrictedとラベル付けされた場合、それがパラレルクエリ内で使用されると、エラーが発生したり、誤った結果が生成されたりします。
C言語の関数は、ラベルが間違っていると、理論的には全く予想できない動作をすることがあります。
これは任意のCプログラムに対してシステムが自分を保護する手段がないからですが、多くの場合、その結果は他の関数と同程度の悪さでしょう。
よくわからない場合は、デフォルトのUNSAFE
で関数にラベル付けしてください。
execution_cost
この関数の推定実行コストを表す正数で、単位はcpu_operator_costです。 関数が集合を返す場合、これは1行当たりのコストとなります。 このコストが指定されない場合、C言語および内部関数では1、他のすべての言語では100となります。 値をより大きくすると、プランナは必要以上に頻繁に関数を評価しないようになります。
result_rows
プランナが想定する、この関数が返す行数の推定値を表す正数です。 これは、関数が集合を返すものと宣言された場合のみ使用可能です。 デフォルト推定値は1000行です。
configuration_parameter
value
SET
句により、関数が始まった時に指定した設定パラメータを指定した値に設定し、関数の終了時にそれを以前の値に戻すことができます。
SET FROM CURRENT
は、CREATE FUNCTION
の実行時点でのパラメータ値を、関数に入る時に適用する値として保管します。
関数にSET
句が付いている場合、関数内部で実行されるSET LOCAL
コマンドの同一変数に対する効果はその関数に制限されます。
つまり、設定パラメータの前の値は関数が終了する時に元に戻ります。
しかし、通常の(LOCAL
がない)SET
コマンドはSET
句を上書きします。
これは過去に行われたSET LOCAL
コマンドに対してもほぼ同じです。
つまり、このコマンドの効果は、現在のトランザクションがロールバックされない限り、関数が終了した後も永続化されます。
definition
関数を定義する文字列定数です。 このパラメータの意味は言語に依存します。 内部的な関数名、オブジェクトファイルへのパス、SQLコマンド、手続き言語で記述されたテキストなどを指定できます。
関数を定義する文字列を記述する際に、通常の単一引用符ではなく、ドル引用符(4.1.2.4. ドル記号で引用符付けされた文字列定数参照)を使用すると便利なことが多くあります。 ドル引用符を使用しなければ、関数定義内の単一引用符やバックスラッシュは必ず二重にしてエスケープしなければなりません。
obj_file
, link_symbol
この構文のAS
句は、動的にロードされるC言語関数において、C言語のソースコード中の関数名がSQL関数の名前と同じでない場合に使われます。
obj_file
という文字列は動的にロードできるオブジェクトを含むファイルの名前で、link_symbol
はその関数のリンクシンボル、つまり、C言語ソースコード中の関数の名前です。
リンクシンボルが省略された場合、定義されるSQL関数の名前と同じものであるとみなされます。
同一オブジェクトファイルを参照する、CREATE FUNCTION
呼び出しが繰り返された場合、そのファイルはセッション毎に一度だけロードされます。
(おそらく開発段階で)ファイルをアンロードし再ロードするには、新しいセッションを開始してください。
attribute
関数に関するオプション情報を指定する伝統的な方法です。 ここで指定できる属性を以下に示します。
isStrict
STRICT
またはRETURNS NULL ON NULL INPUT
と同じです。
isCachable
isCachable
は、IMMUTABLE
と同じですが、既に廃止されています。
しかし、後方互換性を保持するため、まだ有効とされています。
属性名では、大文字小文字を区別しません。
さらに詳しい関数の作成方法については36.3. ユーザ定義関数を参照してください。
PostgreSQLでは関数のオーバーロードが可能です。 つまり、入力引数の型が異なっていれば、複数の関数に同じ名前を使用することができます。 しかし、全ての関数について、C言語における名前は、重複してはいけません。 したがって、オーバーロードするC言語関数には、異なるC言語の名前を与える必要があります (例えば、C言語における名前の一部に引数の型を使用してください)。
同じ名前、同じ入力用パラメータ型を持つ場合、2つの関数は同一であるとみなされます。
OUT
パラメータは無視されます。
したがって、例えば以下の宣言は競合しています。
CREATE FUNCTION foo(int) ... CREATE FUNCTION foo(int, out text) ...
異なる引数型のリストを持つ関数は、作成時に競合するとはみなされませんが、デフォルト値が指定された場合使用時に競合する可能性があります。 例えば以下を考えてみましょう。
CREATE FUNCTION foo(int) ... CREATE FUNCTION foo(int, int default 42) ...
foo(10)
という呼び出しは、どちらの関数を呼び出すべきかに関して曖昧さがあるために失敗します。
関数の引数と戻り値の宣言において、完全なSQL型の構文が使用できます。
しかし、括弧付けされた型修飾子(例えばnumeric
型の精度フィールド)は、CREATE FUNCTION
により破棄されます。
従って、CREATE FUNCTION foo (varchar(10)) ...
はCREATE FUNCTION foo (varchar) ...
とまったく同じになります。
既存の関数をCREATE OR REPLACE FUNCTION
を使って置き換える場合、パラメータ名の変更に関して制限があります。
すでに何らかの入力パラメータに割り当てられた名前を変更することはできません。
(しかし、これまで名前を持たなかったパラメータに名前を追加することは可能です。)
複数の出力パラメータが存在する場合、関数の結果を表わす無名複合型の列名を変更することになるため、出力パラメータの名前を変更することはできません。
既存の関数呼び出しが置き換わった時に動作しなくなることを確実に防ぐために、これらの制限がなされています。
関数がVARIADIC
引数を持つSTRICT
と宣言された場合、その厳密性検査では、variadic配列全体が非NULLかどうかを検査します。
配列がNULL要素を持っていたとしても関数は呼び出されます。
ここでは、初心者向けの簡単な例をいくつか示します。 36.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_temp
をsearch_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;
この関数の意図は、テーブルadmin.pwds
にアクセスすることです。
しかしSET
句がなければ、あるいはSET
句がadmin
だけしか記述していなければ、pwds
という名前の一時テーブルを作成することで、この関数は無意味になってしまいます。
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
も類似の機能を持ちますが、完全な互換性はありません。
属性には移植性がありません。また、使用可能な言語も異なります。
他のデータベースシステムとの互換性のために、argmode
はargname
の前に書くことも後に書くこともできます。
しかし、1つ目の方法が標準に従っています。
パラメータのデフォルトに関しては、標準SQLではDEFAULT
キーワードの構文のみを規定します。
=
を持つ構文はT-SQLおよびFirebirdで使用されています。