CREATE [ OR REPLACE ] FUNCTION name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] ) [ RETURNS rettype | RETURNS TABLE ( column_name column_type [, ...] ) ] { LANGUAGE lang_name | WINDOW | IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF | 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は、新しい関数の作成、または、既存定義の置換のどちらかを行います。 関数を定義するには、ユーザはその言語のUSAGE権限が必要です。
スキーマ名が含まれている場合、関数は指定されたスキーマに作成されます。 スキーマ名がなければ、関数は現在のスキーマに作成されます。 同じスキーマ内の同じ入力引数データ型を持つ既存の関数の名前は、新しい関数の名前として使用できません。 しかし、異なる引数データ型を持つ関数であれば、名前が重複しても構いません (これを、オーバーロードと言います)。
既存の関数定義を入れ替えるには、CREATE OR REPLACE FUNCTIONを使用してください。 この方法では関数の名前や引数の型を変更することはできません (これを行った場合、新しく別の関数が作成されます)。 また、CREATE OR REPLACE FUNCTIONでは、既存の関数の戻り値の型を変更することはできません。 戻り値の型を変更したい場合は、その関数を削除し、再度作成してください。 (これは、OUTパラメータを使用している場合、関数を削除しない限りOUTパラメータの型を変更できないことを意味します。)
CREATE OR REPLACE FUNCTIONを使用して既存の関数を置き換える場合、関数の所有者と権限は変わりません。 他の関数に関するすべての属性には、そのコマンドで指定された値、または暗黙的な値が設定されます。 関数を置き換えるためにはその関数を所有していなければなりません。 (これには所有するロールのメンバであることが含まれています。)
関数を削除し再作成した場合、新しい関数は古いものと同じ実体にはなりません。 古い関数を参照する、既存のルール、ビュー、トリガなどを削除しなければならないでしょう。 関数を参照するオブジェクトを破壊せずに関数定義を変更するには、CREATE OR REPLACE FUNCTIONを使用してください。 また、ALTER FUNCTIONを使用して、既存の関数の補助属性のほとんどを変更することができます。
関数を作成したユーザが、その関数の所有者となります。
関数を作成するためには、引数の型および戻り値の型に対するUSAGE権限を持たなければなりません。
作成する関数の名前です(スキーマ修飾名も可)。
引数のモードで、IN、OUT、INOUT、VARIADICのいずれかです。 省略時のデフォルトはINです。 OUT引数のみがVARIADICの後に続けることができます。 また、RETURNS TABLE記法では、OUTとINOUT引数の両方を使用することはできません。
引数の名前です。 (SQLおよびPL/pgSQLを含む)言語の中にはこの名前を関数本体で使用できるものもあります。 他の言語では、関数そのものに注目する限り、入力引数の名前は単なる追加ドキュメントとして扱われます。 しかし関数呼び出し時に入力引数の名前を使用することで可読性を高めることができます。 (項4.3参照) どのような場合であっても、出力引数の名前は、結果の行型の列名となりますので重要です。 (出力引数の名前を省略した場合、システムはデフォルトの列名を付与します。)
関数の引数のデータ型です(スキーマ修飾名も可)。 基本データ型、複合データ型、ドメイン型、または、テーブル列の型の参照を使用することができます。
また、実装する言語に依存しますが、cstringといった"疑似型"を指定できる場合もあります。 疑似型は、実引数の型の指定が不完全である、もしくは、通常のSQLデータ型の集合を越えていることを示します。
列の型を参照するには、table_name.column_name%TYPEと記述します。 これを使用すると、テーブル定義が変更されても関数が影響を受けないようにするのに役に立つことがあります。
パラメータが指定されなかった場合のデフォルト値として使用される式です。 この式はパラメータの引数型と変換可能でなければなりません。 入力パラメータ(INOUTを含みます)のみがデフォルト値を持つことができます。 デフォルト値を持つパラメータの後ろにあるパラメータはすべて、同様にデフォルト値を持たなければなりません。
関数が返すデータの型です(スキーマ修飾名も可)。 基本型、複合型、ドメイン型、または、テーブル列の型の参照を設定することができます。 また、実装している言語によりますが、cstringのような"疑似型"も指定することが可能です。 その関数が値を返すことを想定していない場合は、戻り値としてvoidを指定してください。
OUTもしくはINOUTパラメータが存在する場合、RETURNS句を省略することができます。 省略しない場合は、出力用パラメータが意味する結果型に従ったもの、つまり、複数の出力用パラメータがあればRECORD、単一の出力用パラメータであればそれと同じ型、でなければなりません。
SETOF修飾子は、その関数が、1つではなく複数のアイテムの集合を返すことを示します。
列の型は、table_name.column_name%TYPEと記述することで参照されます。
RETURNS TABLE構文における出力列の名前です。 これは実際名前付けされたOUTパラメータを宣言する別の方法ですが、RETURNS TABLEがRETURNS SETOFをも意味する点が異なります。
RETURNS TABLE構文における出力列のデータ型です。
関数を実装している言語の名前です。 このパラメータには、sql、c、internal、もしくはユーザ定義手続き言語(例:plpgsql)の名前を指定可能です。 名前を単一引用符で囲むのは廃止予定で、大文字小文字の一致が必要になります。
WINDOWは、この関数が普通の関数ではなくウィンドウ関数であることを示します。 現在これはC言語で作成した関数のみに使用することができます。 既存の関数定義を置き換える場合、WINDOW属性を変更することはできません。
これらの属性は、関数の動作に関する情報を問い合わせオプティマイザに提供します。 いずれか1つのキーワードのみ指定できます。 指定がない場合は、デフォルトでVOLATILEと解釈されます。
IMMUTABLEは、関数がデータベースに対する変更を行わないこと、および、その関数に同じ引数値を与えた場合に常に同じ結果を返すことを示します。 つまり、データベースを検索したり、引数リスト中に直接存在しない情報を使用したりしないということです。 このオプションが指定された場合、引数が全て定数である関数呼び出しは、即座に関数値と置き換えることができます。
STABLEは、関数がデータベースに対する変更を行わないこと、および、その関数に同じ引数値を与えた場合、常に同じ結果を返すが、SQL文が異なると結果が変わってしまう可能性があることを示します。
これは、データベース検索や(現在の時間帯のような)パラメータ変数などに結果が依存する関数に適します。
(これは現在のコマンドで変更された行を問い合わせたいAFTERトリガには不適切です。)
また、current_timestamp
系の関数は、1つのトランザクション内では値が変化しないため、STABLEであることに注意してください。
VOLATILEは、1つのテーブルスキャン内でも関数の値が変化する可能性があるため、最適化できないことを示します。 このような意味で変動的(volatile)なデータベース関数は、比較的少数です。 例えば、random()、currval()、timeofday()などは変動的な関数です。 しかし、例えばsetval()などの副作用がある関数は、その結果を完全に予測できるとしても、呼び出しを最適化しないよう、VOLATILE(変動的)に分類する必要があることに注意してください。
詳細は項35.6を参照してください。
LEAKPROOFは、関数が副作用を持たないことを示します。 その引数に関する情報を戻り値以外で漏らしません。 例えば、一部の引数値に対してのみエラーメッセージを返す関数や何らかのエラーメッセージの中に引数の値を含める関数は漏洩防止(leakproof)とはいえません。 問い合わせプランナは、security_barrierオプション付きで作成されたビューに漏洩防止関数のみを押し込むことができます。 CREATE VIEWおよび項38.5を参照してください。 このオプションはスーパーユーザによってのみ設定することができます。
CALLED ON NULL INPUT(デフォルト)を指定すると、引数にNULLが含まれていても、関数が通常通り呼び出されます。 その場合は、必要に応じてNULL値を確認し、適切な対応をすることは関数作成者の責任です。
RETURNS NULL ON NULL INPUTもしくはSTRICTを指定すると、関数の引数に1つでもNULLがある場合、常にNULLを返します。 このパラメータが指定されると、NULL引数がある場合、関数は実行されません。 代わりに、NULLという結果が自動的に与えられます。
SECURITY INVOKERを指定すると、関数を呼び出したユーザの権限で、その関数が実行されます。 これがデフォルトです。 SECURITY DEFINERを指定すると、関数を作成したユーザの権限で、その関数が実行されます。
EXTERNALキーワードは、SQLとの互換性を保つために許されています。 しかし、SQLとは異なり、この機能は外部関数だけではなくすべての関数に適用されるため、このキーワードは省略可能です。
この関数の推定実行コストを表す正数で、単位はcpu_operator_costです。 関数が集合を返す場合、これは1行当たりのコストとなります。 このコストが指定されない場合、C言語および内部関数では1、他のすべての言語では100となります。 値をより大きくすると、プランナは必要以上に頻繁に関数を評価しないようになります。
プランナが想定する、この関数が返す行数の推定値を表す正数です。 これは、関数が集合を返すものと宣言された場合のみ使用可能です。 デフォルト推定値は1000行です。
SET句により、関数が始まった時に指定した設定パラメータを指定した値に設定し、関数の終了時にそれを以前の値に戻すことができます。 SET FROM CURRENTは、セッションにおけるパラメータの現在値を関数が始まる時に適用する値として保管します。
関数にSET句が付いている場合、関数内部で実行されるSET LOCALコマンドの同一変数に対する効果はその関数に制限されます。 つまり、設定パラメータの前の値は関数が終了する時に元に戻ります。 しかし、通常の(LOCALがない)SETコマンドはSET句を上書きします。 これは過去に行われたSET LOCALコマンドに対してもほぼ同じです。 つまり、このコマンドの効果は、現在のトランザクションがロールバックされない限り、関数が終了した後も永続化されます。
関数を定義する文字列定数です。 このパラメータの意味は言語に依存します。 内部的な関数名、オブジェクトファイルへのパス、SQLコマンド、手続き言語で記述されたテキストなどを指定できます。
関数を定義する文字列を記述する際に、通常の単一引用符ではなく、ドル引用符(項4.1.2.4参照)を使用すると便利なことが多くあります。 ドル引用符を使用しなければ、関数定義内の単一引用符やバックスラッシュは必ず二重にしてエスケープしなければなりません。
この構文のAS句は、動的にロードされるC言語関数において、C言語のソースコード中の関数名がSQL関数の名前と同じでない場合に使われます。 obj_fileという文字列は動的にロードできるオブジェクトを含むファイルの名前で、link_symbolはその関数のリンクシンボル、つまり、C言語ソースコード中の関数の名前です。 リンクシンボルが省略された場合、定義されるSQL関数の名前と同じものであるとみなされます。
同一オブジェクトファイルを参照する、CREATE FUNCTION呼び出しが繰り返された場合、そのファイルはセッション毎に一度だけロードされます。 (おそらく開発段階で)ファイルをアンロードし再ロードするには、新しいセッションを開始してください。
関数に関するオプション情報を指定する伝統的な方法です。 ここで指定できる属性を以下に示します。
STRICTまたはRETURNS NULL ON NULL INPUTと同じです。
isCachableは、IMMUTABLEと同じですが、既に廃止されています。 しかし、後方互換性を保持するため、まだ有効とされています。
属性名では、大文字小文字を区別しません。
さらに詳しい関数の作成方法については項35.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要素を持っていたとしても関数は呼び出されます。
ここでは、初心者向けの簡単な例をいくつか示します。 項35.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関数は関数を作成したユーザの権限で実行されますので、その関数を間違って使用できないことを確実にしなければなりません。 安全上、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;
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;