定義方法によらず,ユーザ定義関数を作成するには,CREATE FUNCTION文を使用する.

advanced=> \h CREATE FUNCTION
Command:     CREATE FUNCTION
Description: define a new function
Syntax:
CREATE [ OR REPLACE ] FUNCTION name ( [ argtype [, ...] ] )
    RETURNS rettype
    AS 'definition'
    LANGUAGE langname
    [ WITH ( attribute [, ...] ) ]
CREATE [ OR REPLACE ] FUNCTION name ( [ argtype [, ...] ] )
    RETURNS rettype
    AS 'obj_file', 'link_symbol'
    LANGUAGE langname
    [ WITH ( attribute [, ...] ) ]

各項目についての説明を以下に示す.

name

関数名.関数名が同じでも引数の型や数が異なれば別の関数として認識される.

argtype

引数の型.引数は0〜16個持つことができる.

rettype

返り値の型.これが関数の型となり,すべてのユーザ定義関数は型を持つ.

definition

関数定義本体.複数のSQL文を記述できるが,最後はSELECT文で終らなければならない.

langname

関数定義本体で使用される言語.

obj_file

Cで関数を定義する際のオブジェクトファイル.

link_symbol

そのユーザ定義関数で使用するオブジェクトファイル内の関数名.この関数名は小文字アルファベット,アンダースコア,ハイフン,数字で構成される.

attribute

制御パラメータ.

iscachable

オプティマイザのための情報.引数の値が全く同じなら常に同じ値を返す関数に対して指定.

isstrict

引数の値のうち1つ以上がNULLの場合,NULLを返す関数に対して指定.

関数の削除にはDROP FUNCTIONを使用する.

DROP FUNCTION name;

nameは削除する関数名である.

行を一つ返す関数の例として特定のテーブルから特定のフィールドの最大値を返す関数を作成してみる(※).最大値を返すということで,返る行は必ず1行である.まずテーブルを作成する.

advanced=> CREATE TABLE hoge(
advanced(> f1 INTEGER);
CREATE
advanced=> INSERT INTO hoge       
advanced-> VALUES (1);
INSERT 26181 1
advanced=> INSERT INTO hoge
advanced-> VALUES (2);
INSERT 26182 1
advanced=> INSERT INTO hoge
advanced-> VALUES (3);
INSERT 26183 1

作成したテーブルhogeからフィールドf1の最大値を求めて返す関数を作成する.

advanced=> CREATE FUNCTION max_of_hoge_f1()
advanced-> RETURNS INTEGER   
advanced-> AS '
advanced'> SELECT max(f1) FROM hoge'
advanced-> LANGUAGE 'sql';
CREATE

呼び出してみる.

advanced=> SELECT max_of_hoge_f1();
 max_of_hoge_f1 
----------------
              3
(1 row)

テーブルhogeにレコードがない場合,値NULLの行が1行かえる.

advanced=> DELETE FROM hoge;
DELETE 3
advanced=> SELECT max_of_hoge_f1();
 max_of_hoge_f1 
----------------
               
(1 row)

先の例をいじって行を複数返す関数を作成してみる.テーブルhogeからf1が最大値のレコードのf2の値を返す関数を作成する.この関数は,f1が最大値のレコードが複数あれば,f2の値も複数返る.まずテーブルhogeを変更してデータ投入.

advanced=> ALTER TABLE hoge 
advanced-> ADD COLUMN f2 INTEGER;
ALTER
advanced=> INSERT INTO hoge
advanced-> VALUES (1, 1);
INSERT 26185 1
advanced=> INSERT INTO hoge
advanced-> VALUES (2, 1);
INSERT 26186 1
advanced=> INSERT INTO hoge
advanced-> VALUES (2, 2);
INSERT 26187 1
advanced=> INSERT INTO hoge
advanced-> VALUES (2, 3);
INSERT 26188 1

では関数作成.

advanced=> CREATE FUNCTION f2_of_max_hoge_f1()
advanced-> RETURNS SETOF INTEGER
advanced-> AS '
advanced'> SELECT f2 FROM hoge
advanced'> WHERE f1 = (SELECT max(f1) FROM hoge)'
advanced-> LANGUAGE 'sql';
CREATE

複数行を返す関数を定義する場合は,このように

RETURNS SETOF rettype
と返り値の型をSETOFとともに指定する.では作成した関数を呼んでみる.
advanced=> SELECT f2_of_max_hoge_f1();
 f2_of_max_hoge_f1 
-------------------
                 1
                 2
                 3
(3 rows)

	

PostgreSQLではテーブルを作成すると,そのレコードに対してテーブルと同名の型が定義される.これを利用してテーブルのレコードを引数にとる関数を作成することができる.フィールドf1で関連づけられたテーブルhogeとfugaを作成し,テーブルhogeのレコードを引数にとってf1が一致するテーブルfugaのレコードのf3を返す関数を作成する.まずテーブルfugaを作成してデータ投入.

advanced=> CREATE TABLE fuga (
advanced(> f1 INTEGER,
advanced(> f3 INTEGER);
CREATE
advanced=> INSERT INTO fuga
advanced-> VALUES(1, 10);
INSERT 26193 1
advanced=> INSERT INTO fuga
advanced-> VALUES(2, 20);
INSERT 26194 1
advanced=> INSERT INTO fuga
advanced-> VALUES(2, 30);
INSERT 26195 1
advanced=> INSERT INTO fuga
advanced-> VALUES(2, 40);
INSERT 26196 1

では関数を作成してみる.

advanced=> CREATE FUNCTION get_f3_from_hoge(hoge)
advanced-> RETURNS SETOF INTEGER
advanced-> AS'
advanced'> SELECT f3 FROM fuga
advanced'> WHERE f1 = $1.f1'
advanced-> LANGUAGE 'sql';
CREATE

$1は一番目の引数という意味である(※).では呼んでみる.

advanced=> SELECT get_f3_from_hoge(hoge)
advanced-> FROM hoge
advanced-> WHERE f2 = 2;
 get_f3_from_hoge 
------------------
               20
               30
               40
(3 rows)

関数の引数にテーブル名を指定してる点に注意.