★PostgreSQLカンファレンス2024 12月6日開催/チケット販売中★
他のバージョンの文書 16 | 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 [, ...] ] )
    [ 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を使用してください。

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

パラメータ

name

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

argmode

引数のモードで、INOUTINOUTのいずれかです。 省略時のデフォルトはINです。

argname

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

argtype

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

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

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

rettype

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

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

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

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

langname

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

IMMUTABLE
STABLE
VOLATILE

上記の属性を指定すると、実行時の最適化のため、複数の関数の評価を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
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とは異なり、この機能は外部関数にのみ適用されるわけではないため、このキーワードは省略可能です。

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と同じですが、既に廃止されています。 しかし、後方互換性を保持するため、まだ有効とされています。

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

注釈

さらに詳しい外部関数の作成方法については項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関数の安全な作成

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

互換性

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

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

関連項目

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