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は、新しい関数の作成、または、既存定義の置換のどちらかを行います。
スキーマ名が含まれている場合、関数は指定されたスキーマで作成されます。 スキーマ名がなければ、関数は現在のスキーマで作成されます。 同じスキーマ内の同じ引数データ型を持つ既存の関数の名前は、新しい関数の名前として使用できません。 しかし、異なる引数データ型を持つ関数であれば、名前が重複しても構いません (これを、オーバーロードと言います)。
既存の関数定義を更新するには、CREATE OR REPLACE FUNCTIONを使用してください。 この方法では関数の名前や引数の型を変更することはできないことに注意してください (これを行った場合、新しく別の関数が作成されます)。 また、CREATE OR REPLACE FUNCTIONでは、既存の関数の戻り値の型を変更することはできません。 戻り値の型を変更したい場合は、その関数を削除し、再度作成してください。 (これは、OUTパラメータを使用している場合、関数を削除しない限りOUTパラメータの名前や型を変更できないことを意味します。)
関数を削除し再作成した場合、新しい関数は古いものと同じ実体にはなりません。 古い関数を参照する、既存のルール、ビュー、トリガなどを削除しなければならないでしょう。 関数を参照するオブジェクトを破壊しないように関数定義を変更するには、CREATE OR REPLACE FUNCTIONを使用してください。
関数を作成したユーザが、その関数の所有者となります。
作成する関数の名前です(スキーマ修飾名も可)。
引数のモードで、IN、OUT、INOUTのいずれかです。 省略時のデフォルトはINです。
引数の名前です。 言語の中にはこの名前を関数本体で使用できるものもあります (今のところPL/pgSQLのみです)。 他の言語では、入力引数の名前は単なる追加ドキュメントとして扱われます。 しかし、出力引数の名前は、結果の行型の列名となりますので重要です。 (出力引数の名前を省略した場合、システムはデフォルトの列名を付与します。)
関数の引数のデータ型です(スキーマ修飾名も可)。 基本データ型、複合データ型、ドメイン型、または、テーブル列の型の参照を使用することができます。
また、言語の実装に依存しますが、cstringといった"疑似型"を指定できる場合もあります。 疑似型は、引数の型の指定が不完全である、もしくは、通常のSQLデータ型の集合を越えていることを示します。
列の型を参照するには、tablename.columnname%TYPEと記述します。 これを使用すると、テーブル定義が変更されても関数が影響を受けないようにするのに役に立つことがあります。
関数が返すデータの型です(スキーマ修飾名も可)。 基本型、複合型、ドメイン型、または、テーブル列の型の参照を設定することができます。 また、実装している言語によりますが、cstringのような"疑似型"も指定することが可能です。
OUTもしくはINOUTパラメータが存在する場合、RETURNS句を省略することができます。 省略しない場合は、出力用パラメータが意味する結果型に従ったもの、つまり、複数の出力用パラメータがあればRECORD、単一の出力用パラメータであればそれと同じ型、でなければなりません。
SETOF修飾子は、その関数が、1つではなく複数のアイテムの集合を返すことを示します。
列の型は、tablename.columnname%TYPEと記述することで参照されます。
関数を実装している言語の名前です。 このパラメータには、SQL、C、internal、もしくはユーザ定義手続き言語の名前を指定可能です。 後方互換を保持するため、この名前を単一引用符で囲むこともできます。
上記の属性を指定すると、実行時の最適化のため、複数の関数の評価を1つの評価に置き換えても問題ないかどうかという情報を、システムに提供することができます。 いずれか1つのキーワードのみ指定します。 指定がない場合は、デフォルトでVOLATILEと解釈されます。
IMMUTABLEは、その関数に同じ引数値を与えた場合、常に同じ結果を返すことを示します。 これは、データベースを検索したり、引数リスト中に直接存在しない情報を使用したりしないということです。 このオプションが指定された場合、引数が全て定数である関数呼び出しは、即座に関数値と置き換えることができます。
STABLEは、その関数に同じ引数値を与えた場合、1テーブルスキャン内で常に同じ結果を返すが、
SQL文が異なると結果が変わってしまう可能性があることを示します。
これは、データベース検索や(現在の時間帯のような)パラメータ変数などに結果が依存する関数に適します。
また、current_timestamp
系の関数は、1つのトランザクション内では値が変化しないため、STABLEであることに注意してください。
VOLATILEは、1テーブルスキャン内において関数の値が変化する可能性があるため、最適化できないことを示します。 このような意味で変動的(volatile)なデータベース関数は、比較的少数です。 例えば、random()、currval()、timeofday()などは変動的な関数です。 しかし、例えばsetval()などの副作用がある関数は、その結果を完全に予測できるとしても、呼び出しを最適化しないよう、VOLATILE(変動的)に分類する必要があることに注意してください。
詳細は項32.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と同じですが、既に廃止されています。 しかし、後方互換性を保持するため、まだ有効とされています。
属性名では、大文字小文字を区別しません。
さらに詳しい外部関数の作成方法については項32.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呼び出しが繰り返された場合、そのファイルは一度だけロードされます。 (おそらく開発段階で)ファイルをアンロードし再ロードするには、LOADコマンドを使用してください。
ユーザ定義の関数を削除するにはDROP FUNCTIONを使用してください。
関数を定義する文字列を記述する際に、通常の単一引用符ではなく、ドル引用符(項4.1.2.2参照)を使用すると便利なことが多くあります。 ドル引用符を使用しない限り、関数定義内の単一引用符やバックスラッシュは必ず二重にしてエスケープしなければなりません。
関数を定義するには、ユーザはその言語のUSAGE権限が必要です。
ここでは、初心者向けの簡単な例をいくつか示します。 項32.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);
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 -- 直前のsearch_pathを保存します。正しい関数を呼び出すことを確実 --にするためにcurrent_settingを付けなければなりません old_path := pg_catalog.current_setting('search_path'); -- 安全なsearch_pathを設定、つまり信頼できるスキーマ指定の後に -- 'pg_temp'を設定します。 -- 関数が終わる前にエラーが起きたとしても過去の値に戻るよう -- にis_local = trueを設定します。 PERFORM pg_catalog.set_config('search_path', 'admin, pg_temp', true); -- 目的の安全な作業を行います。 SELECT (pwd = $2) INTO passed FROM pwds WHERE username = $1; -- 呼び出し元のsearch_pathに戻します。 PERFORM pg_catalog.set_config('search_path', old_path, true); RETURN passed; END; $$ LANGUAGE plpgsql SECURITY DEFINER;