他のバージョンの文書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 [ OR REPLACE ] FUNCTION
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
    [ RETURNS rettype
      | RETURNS TABLE ( column_name column_type [, ...] ) ]
  { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_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 }
    | 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権限を持たなければなりません。

パラメータ

name

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

argmode

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

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 TABLERETURNS SETOFをも意味する点が異なります。

column_type

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

lang_name

関数を実装している言語の名前です。 このパラメータには、sqlcinternal、もしくはユーザ定義手続き言語(例: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コマンドに対してもほぼ同じです。 つまり、このコマンドの効果は、現在のトランザクションがロールバックされない限り、関数が終了した後も永続化されます。

使用可能なパラメータと値については、SETおよび19章サーバの設定を参照してください。

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

この関数の意図は、テーブル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も類似の機能を持ちますが、完全な互換性はありません。 属性には移植性がありません。また、使用可能な言語も異なります。

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

パラメータのデフォルトに関しては、標準SQLではDEFAULTキーワードの構文のみを規定します。 =を持つ構文はT-SQLおよびFirebirdで使用されています。

関連項目

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