SQL関数は、任意のSQL文のリストを実行し、そのリストの最後の問い合わせの結果を返します。 単純な（非集合の）場合、最後の問い合わせの結果の最初の行が返されます （複数行の結果のうちの"最初の行"は、ORDER BYを使用しない限り定義付けることができないことを覚えておいてください）。 最後の問い合わせが何も行を返さない時はNULL値が返されます。
他にも、SQL関数は、SETOF sometype型を返すように指定することにより、集合を返すように宣言することもできます。 この場合、最後の問い合わせの結果の全ての行が返されます。 詳細は後で説明します。
SQL関数の本体は、セミコロンで区切ったSQL文のリストでなければなりません。 最後の文の後のセミコロンは省略可能です。 関数がvoidを返すものと宣言されていない限り、最後の文はSELECTでなければなりません。
SQL言語で作成された、任意のコマンド群はまとめて、関数として定義することができます。 SELECT問い合わせ以外に、データの変更コマンド（つまり、INSERT、UPDATE、DELETE）を含めることができます。 しかし、最後のコマンドは、関数の戻り値型で定義したものを返すSELECTでなければなりません。 その他にも、何か動作をさせるが、有用な値を返さないSQL関数を定義したいのであれば、voidを返すものと定義することで実現可能です。 その場合、関数本体はSELECTで終わってはなりません。 以下に例を示します。
CREATE FUNCTION clean_emp() RETURNS void AS $$
DELETE FROM emp
WHERE salary <= 0;
$$ LANGUAGE SQL;
SELECT clean_emp();
clean_emp
-----------
(1 row)
CREATE FUNCTIONコマンドの構文では、関数本体は文字列定数として作成される必要があります。 この文字列定数の記述には、通常、ドル引用符付け（項4.1.2.2）が最も便利です。 文字列定数を単一引用符で括る通常の構文では、関数本体中で使用される単一引用符（'）とバックスラッシュ（\）（エスケープ文字列構文を仮定）を、通常はこの文字を二重にしなければなりません（項4.1.2.1を参照）。
SQL関数の引数は関数本体内で$nという構文を用いて表すことができます。 つまり、$1は第1引数を示し、$2は第2引数のようになります。 引数が複合型の場合、$1.nameのようなドット表記を用いて引数の属性にアクセスすることができます。 この引数は、識別子としてではなく、データ値としてのみ使用することができます。 したがって、例えば
INSERT INTO mytable VALUES ($1);
は正しいものですが、以下は動作しません。
INSERT INTO $1 VALUES (42);
最も簡単なSQL関数は、引数を取らずに単にintegerのような基本型を返すものです。
CREATE FUNCTION one() RETURNS integer AS $$
SELECT 1 AS result;
$$ LANGUAGE SQL;
-- 文字列リテラルの別の構文では
CREATE FUNCTION one() RETURNS integer AS '
SELECT 1 AS result;
' LANGUAGE SQL;
SELECT one();
one
-----
1
関数本体内で関数の結果用に列の別名を（resultという名前で）定義したことに注目してください。 しかしこの列の別名はこの関数外からは可視ではありません。 したがって、その結果はresultではなく、oneというラベルで表示されています。
基本型を引数として取る、SQL関数を定義することはほとんどの場合簡単です。 以下の例において、関数内部でその引数を$1と$2としてアクセスしていることに注意してください。
CREATE FUNCTION add_em(integer, integer) RETURNS integer AS $$
SELECT $1 + $2;
$$ LANGUAGE SQL;
SELECT add_em(1, 2) AS answer;
answer
--------
3
以下にもう少し役に立つ関数を示します。 これは銀行口座からの引き落としに使用できます。
CREATE FUNCTION tf1 (integer, numeric) RETURNS integer AS $$
UPDATE bank
SET balance = balance - $2
WHERE accountno = $1;
SELECT 1;
$$ LANGUAGE SQL;以下のように、ユーザはこの関数を使用して、口座番号17から100ドルを引き出すことが可能です。
SELECT tf1(17, 100.0);
実際には、関数の結果を定数1よりもわかりやすい形にするために、以下のように定義するとよいでしょう。
CREATE FUNCTION tf1 (integer, numeric) RETURNS numeric AS $$
UPDATE bank
SET balance = balance - $2
WHERE accountno = $1;
SELECT balance FROM bank WHERE accountno = $1;
$$ LANGUAGE SQL;これは残高を調整し、更新後の残高を返します。
関数の引数に複合型を記述した場合、（これまで行っていた$1と$2のように）必要な引数だけを指定するだけではなく、必要とする引数の属性（フィールド）も指定する必要があります。
例えば、empが従業員データを持つテーブルとすると、この名前はそのテーブル内の各行を表す複合型の名前でもあります。
以下に示すdouble_salary関数は、該当する従業員の給料を倍増させます。
CREATE TABLE emp (
name text,
salary numeric,
age integer,
cubicle point
);
CREATE FUNCTION double_salary(emp) RETURNS numeric AS $$
SELECT $1.salary * 2 AS salary;
$$ LANGUAGE SQL;
SELECT name, double_salary(emp.*) AS dream
FROM emp
WHERE emp.cubicle ~= point '(2,1)';
name | dream
------+-------
Bill | 8400
$1.salaryという構文を使用して、引数の行値の1フィールドを選択していることに注目してください。 また、*を使用したSELECTコマンドの呼び出しでは、複合型の値として、現在のテーブル行全体を表すテーブル名を使用していることにも注目してください。 別の方法として、テーブル行は以下のようにテーブル名だけを使用して参照することができます。
SELECT name, double_salary(emp) AS dream
FROM emp
WHERE emp.cubicle ~= point '(2,1)';しかし、この使用方法は、混乱しやすいためお勧めしません。
その場で複合型の引数値を作成することが便利な場合があります。 これはROW式で行うことができます。 例えば、以下のようにして関数に渡すデータを調整することができます。
SELECT name, double_salary(ROW(name, salary*1.1, age, cubicle)) AS dream
FROM emp;
複合型を返す関数を作成することも可能です。 以下に単一のemp行を返す関数の例を示します。
CREATE FUNCTION new_emp() RETURNS emp AS $$
SELECT text 'None' AS name,
1000.0 AS salary,
25 AS age,
point '(2,2)' AS cubicle;
$$ LANGUAGE SQL;ここでは、各属性を定数で指定していますが、この定数を何らかの演算に置き換えることもできます。
関数を定義する上で、2つの重要な注意点を以下に示します。
問い合わせにおける選択リストの順番は、複合型と関連したテーブル内で現れる列の順番と正確に一致する必要があります （上で行ったように列に名前を付けても、システムは認識しません）。
複合型定義と一致するように式を型キャストしなければなりません。 さもなくば、以下のようなエラーとなります。
ERROR: function declared to return emp returns varchar instead of text at column 1
同じ関数を以下のように定義することもできます。
CREATE FUNCTION new_emp() RETURNS emp AS $$
SELECT ROW('None', 1000.0, 25, '(2,2)')::emp;
$$ LANGUAGE SQL;ここで、正しい複合型の単一の列を単に返すSELECTを記述しました。 これは本来ならばあまり良い解法とは言えませんが、例えば、必要な複合値を返す他の関数を呼び出して結果を計算しなければならない場合など、便利な解法になることがあります。
以下の2つの方法でこの関数を直接呼び出すことができます。
SELECT new_emp();
new_emp
--------------------------
(None,1000.0,25,"(2,2)")
SELECT * FROM new_emp();
name | salary | age | cubicle
------+--------+-----+---------
None | 1000.0 | 25 | (2,2)2番目の方法については、項33.4.4でより詳しく説明します。
SQL式で行（複合型）を返す関数を呼び出す時に、その結果から1つのフィールド（属性）のみを使用したいという場合があります。 これは、以下のような構文で行うことができます。
SELECT (new_emp()).name; name ------ None
パーサが混乱しないように、括弧を追加する必要があります。 括弧なしで行おうとすると、以下のような結果になります。
SELECT new_emp().name;
ERROR: syntax error at or near "." at character 17
LINE 1: SELECT new_emp().name;
^
また、関数表記を使用して属性を抽出することもできます。 簡単に説明すると、attribute(table)とtable.attributeという表記方法のどちらでも使用できるということです。
SELECT name(new_emp()); name ------ None
-- これは -- SELECT emp.name AS youngster FROM emp WHERE emp.age < 30; -- と同一です。 SELECT name(emp) AS youngster FROM emp WHERE age(emp) < 30; youngster ----------- Sam Andy
ティップ: 関数表記と属性表記の同等性によって、"計算処理されたフィールド"を模擬するために複合型に対する関数を使用することができます。 例えば、上記で定義されたdouble_salary(emp)を使用して、次のように記述することができます。
SELECT emp.name, emp.double_salary FROM emp;これを利用するアプリケーションは、double_salaryが実際にはテーブルの列ではないことを直接気にする必要はありません （また、ビューの計算処理されたフィールドを模擬することも可能です）。
複合型を結果として返す関数を使用する他の方法は、その結果を、その行型を入力として受け付ける関数に渡す、以下のような方法です。
CREATE FUNCTION getname(emp) RETURNS text AS $$
SELECT $1.name;
$$ LANGUAGE SQL;
SELECT getname(new_emp());
getname
---------
None
(1 row)
複合型の結果を返す関数の使用方法として他にも、テーブル関数として呼び出す方法があります。 これは項33.4.4で説明します。
関数の結果の記述方法には、他にも出力パラメータを使用して定義する方法があります。 以下に例を示します。
CREATE FUNCTION add_em (IN x int, IN y int, OUT sum int)
AS 'SELECT $1 + $2'
LANGUAGE SQL;
SELECT add_em(3,7);
add_em
--------
10
(1 row)項33.4.1で示したadd_em版と基本的な違いはありません。 複数列を返す関数を定義する簡単な方法を提供することが出力パラメターの本来の価値です。 以下に例を示します。
CREATE FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int) AS 'SELECT $1 + $2, $1 * $2' LANGUAGE SQL; SELECT * FROM sum_n_product(11,42); sum | product -----+--------- 53 | 462 (1 row)
これは基本的に、関数結果用の匿名の複合型の作成を行います。 上の例では、
CREATE TYPE sum_prod AS (sum int, product int); CREATE FUNCTION sum_n_product (int, int) RETURNS sum_prod AS 'SELECT $1 + $2, $1 * $2' LANGUAGE SQL;
と同じ最終結果になります。 しかし、独立した複合型定義に悩まされることがなくなり、便利であるともいえます。
SQLから関数を呼び出す時、出力パラメータが呼び出し側の引数リストに含まれないことに注意してください。 PostgreSQLが、入力パラメータのみが関数の呼び出しシグネチャを定義するとみなしているためです。 これはまた、関数を削除するために関数を参照する場合、入力パラメータのみが考慮されることを意味しています。 上の関数は、次のいずれかの方法で削除することができます。
DROP FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int); DROP FUNCTION sum_n_product (int, int);
パラメータには、IN （デフォルト）、OUTまたはINOUTという印を付与できます。 INOUTパラメータは、入力パラメータ（呼び出し引数リストの一部）と出力パラメータ（結果のレコード型の一部分）の両方を提供します。
全てのSQL関数は問い合わせのFROM句で使用できますが、複合型を返す関数に特に便利です。 関数が基本型を返すよう定義されている場合、テーブル関数は1列からなるテーブルを作成します。 関数が複合型を返すよう定義されている場合、テーブル関数は複合型の列のそれぞれに対して1つの列を作成します。
以下に例を示します。
CREATE TABLE foo (fooid int, foosubid int, fooname text);
INSERT INTO foo VALUES (1, 1, 'Joe');
INSERT INTO foo VALUES (1, 2, 'Ed');
INSERT INTO foo VALUES (2, 1, 'Mary');
CREATE FUNCTION getfoo(int) RETURNS foo AS $$
SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;
SELECT *, upper(fooname) FROM getfoo(1) AS t1;
fooid | foosubid | fooname | upper
-------+----------+---------+-------
1 | 1 | Joe | JOE
(1 row)例からわかる通り、関数の結果の列を通常のテーブルの列と同じように扱うことができます。
この関数の結果得られたのは1行のみであることに注意してください。 これはSETOFを指定しなかったためです。 これについては次節で説明します。
SQL関数がSETOF sometypeを返すよう宣言されている場合、関数の最後のSELECT問い合わせは最後まで実行され、各出力行は結果集合の要素として返されます。
この機能は通常、関数をFROM句内で呼び出す時に使用されます。 この場合、関数によって返される各行は、問い合わせによって見えるテーブルの行になります。 例えば、テーブルfooの内容が上記と同じであれば以下のようになります。
CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;この出力は以下の通りです。
fooid | foosubid | fooname
-------+----------+---------
1 | 1 | Joe
1 | 2 | Ed
(2 rows)
現在、集合を返す関数は問い合わせの選択リスト内でも呼び出すことができます。 問い合わせ自身によって生成する各行に対し、集合を返す関数が呼び出され、関数の結果集合の各要素に対して出力行が生成されます。 ただし、この機能は現在では推奨されておらず、今後のリリースでは削除される可能性があります。 以下は、選択リストから集合を返す関数の例です。
CREATE FUNCTION listchildren(text) RETURNS SETOF text AS $$
SELECT name FROM nodes WHERE parent = $1
$$ LANGUAGE SQL;
SELECT * FROM nodes;
name | parent
-----------+--------
Top |
Child1 | Top
Child2 | Top
Child3 | Top
SubChild1 | Child1
SubChild2 | Child1
(6 rows)
SELECT listchildren('Top');
listchildren
--------------
Child1
Child2
Child3
(3 rows)
SELECT name, listchildren(name) FROM nodes;
name | listchildren
--------+--------------
Top | Child1
Top | Child2
Top | Child3
Child1 | SubChild1
Child1 | SubChild2
(5 rows)
最後のSELECTにおいて、Child2とChild3などが出力行に表示されていないことに注意してください。
これは、listchildrenがこの入力に対して空の集合を返すため出力行が生成されないからです。
SQL関数は、多様型anyelementおよびanyarrayを受け付け、返すように宣言することができます。
多様関数の詳細説明については項33.2.5を参照してください。
以下のmake_array多様関数は、任意の2つのデータ型要素から配列を作成します。
CREATE FUNCTION make_array(anyelement, anyelement) RETURNS anyarray AS $$
SELECT ARRAY[$1, $2];
$$ LANGUAGE SQL;
SELECT make_array(1, 2) AS intarray, make_array('a'::text, 'b') AS textarray;
intarray | textarray
----------+-----------
{1,2} | {a,b}
(1 row)
'a'::textという型キャストを使用して、引数がtext型であることを指定していることに注目してください。 これは引数が単なる文字列リテラルである場合に必要です。 さもないと、unknown型として扱われてしまうため、無効なunknownの配列を返そうとしてしまいます。 型キャストがないと、以下のようなエラーが発生します。
ERROR: could not determine "anyarray"/"anyelement" type because input has type "unknown"
固定の戻り値型で多様引数を持つことは許されますが、逆は許されません。 以下に例を示します。
CREATE FUNCTION is_greater(anyelement, anyelement) RETURNS boolean AS $$
SELECT $1 > $2;
$$ LANGUAGE SQL;
SELECT is_greater(1, 2);
is_greater
------------
f
(1 row)
CREATE FUNCTION invalid_func() RETURNS anyelement AS $$
SELECT 1;
$$ LANGUAGE SQL;
ERROR: cannot determine result data type
DETAIL: A function returning "anyarray" or "anyelement" must have at least one argument of either type.
出力引数を持つ関数でも多様性を使用することができます。 以下に例を示します。
CREATE FUNCTION dup (f1 anyelement, OUT f2 anyelement, OUT f3 anyarray)
AS 'select $1, array[$1,$1]' LANGUAGE sql;
SELECT * FROM dup(22);
f2 | f3
----+---------
22 | {22,22}
(1 row)