PostgreSQLには多くのbuiltin関数があるが,自分で関数を定義することもできる.その定義はいろいろなプログラミング言語で行なうことができるが,ここではSQLによる定義について試してみた.
CREATE FUNCTION
行を一つ返す関数の例
行を複数返す関数の例
引数にレコードを取る関数の例
CREATE FUNCTION
行を一つ返す関数の例
行を一つ返す関数の例として特定のテーブルから特定のフィールドの最大値を返す関数を作成してみる(※).最大値を返すということで,返る行は必ず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)
関数の引数にテーブル名を指定してる点に注意.
※ $nはn番目の引数である.