9.2. 問い合わせ言語 (SQL) 関数

9.2.1. 例

簡単なSQL関数の例として、下記の銀行口座のからお金を引き出す例をご覧下さい。

CREATE FUNCTION tp1 (integer, numeric) RETURNS integer AS '
    UPDATE bank
        SET balance = balance - $2
        WHERE accountno = $1;
    SELECT 1;
' LANGUAGE SQL;

ユーザは下記の関数を使って、口座番号17から100ドルを引き出すことが可能です。

SELECT tp1(17, 100.0);

実際には、関数の結果を定数 "1" よりも分かりやすい形にするために、以下のように定義するとよいでしょう。

CREATE FUNCTION tp1 (integer, numeric) RETURNS numeric AS '
    UPDATE bank
        SET balance = balance - $2
        WHERE accountno = $1;
    SELECT balance FROM bank WHERE accountno = $1;
' LANGUAGE SQL;

これにより残高が計算され、新しい残高を返します。

SQL 言語内のコマンドの集合は、全てまとめて、1つの関数として定義することができます。そのコマンドには、SELECT 以外にもデータを変更するもの(つまり、INSERT, UPDATE, および、DELETE) も含むことができます。しかし、最後のコマンドは その関数の戻り値型で指定したものを返す、SELECT である必要があります。 また、アクションは行うが値を返さない SQL 関数を定義したい場合は、void を返すように定義することができます。 その場合、 最後のコマンドを SELECT にしてはなりません。たとえば、以下のようにします。

CREATE FUNCTION clean_EMP () RETURNS void AS '
    DELETE FROM EMP
        WHERE EMP.salary <= 0;
' LANGUAGE SQL;

SELECT clean_EMP();

 clean_emp
-----------

(1 row)

9.2.2. 基本型を使用した SQL 関数

SQL 関数の最も単純なものは、引数をとらない、integer 型のような基本型を単に返すものです。

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

9.2.3. 複合型を使用した SQL 関数

関数を複合型の引数をとるように指定した場合、(上の例で $1 と $2 を使用して行ったように)どの引数を使用するかを指定するだけではなく、その引数の属性も指定する必要があります。例えば、EMP が従業員データを持つテーブル、つまり、そのテーブル各行の複合型の名前であるとします。以下に、給与の倍額を計算する、double_salary 関数を示します。

CREATE FUNCTION double_salary(EMP) RETURNS integer 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
------+-------
 Sam  |  2400

引数の行から1つのフィールドを取り出すために $1.salaryという構文を使用していることに注意して下さい。また、複合値として、テーブルの現在行全体を表すためにテーブル名を使用した SELECT コマンドの呼び出し方法にも注意して下さい。

複合型を返す関数を作成することも可能です。) 以下に EMP 型を1行返す関数の例を示します。

CREATE FUNCTION new_emp() RETURNS EMP AS '
    SELECT text ''None'' AS name,
        1000 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

行 (複合型) を返す関数は、下で示すとおりテーブル関数として使用できます。 この関数は、その行から属性を 1 つだけ抽出する場合、あるいは行全体を同じ複合型を受け入れる他の関数に渡す場合にのみ、 SQL 式のコンテキストでも呼び出すことができます。例えば、

SELECT (new_emp()).name;

 name
------
 None

パーサーが混乱しないように、括弧を追加する必要があります。

SELECT new_emp().name;
ERROR:parser:parse error at or near "."

また、関数表記を使用して属性を抽出することもできます。 簡単に説明すると、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

一行を結果として返す関数の使用する、他の方法は、パラメータとして行型を受け付ける2番目の関数を宣言して、関数の結果を渡す、以下のような方法です。

CREATE FUNCTION getname(emp) RETURNS text AS
'SELECT $1.name;'
LANGUAGE SQL;

SELECT getname(new_emp());
 getname
---------
 None
(1 row)

9.2.4. SQL テーブル関数

テーブル関数は、問い合わせの FROM 句で使用することができます。 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
(2 rows)

例から分かるとおり、関数の結果の列は、通常のテーブルの列と同じように扱うことができます。

この関数の結果得られたのは 1 行のみであることに注意してください。 これは SETOF を指定しなかったためです。

9.2.5. set を返す SQL 関数

SQL 関数が SETOF sometype を返すよう宣言されている場合、関数の最後の SELECT 問い合わせは最後まで実行され、出力される行は set の要素として返されます。

この機能を使うには通常、関数をテーブル関数として呼び出します。 この場合、関数によって返される各行は、問い合わせから見えるテーブルの行になります。 例えば、テーブル 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)

現在、set を返す関数は SELECT 問い合わせの対象リスト内でも呼び出すことができます。SELECT が自身によって生成する各行に対し、set を返す関数が呼び出され、関数の結果 set の各要素に対して出力行が生成されます。 ただし、この機能は現在では推奨されておらず、今後のリリースでは削除される可能性があります。 以下は、対象リストから set を返す関数の例です。

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 はこの入力に対して空の set を返すため出力行が生成されないからです。