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

12.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 である必要があります。

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

SELECT clean_EMP();

 x
---
 1

12.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

12.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
  

PostgreSQL の現在のリリースでは、複合型を返す関数の使用する上で、望ましくない制限がいくつか存在します。簡単にいうと、一行を返す仮数を呼び出す時、行全体を取り出すことができません。その行から1つの属性を引き出すか、別の関数にそのまま渡すかをする必要があります。(行全体を表示させた場合、意味のない数値になります。)以下に例を示します。

SELECT name(new_emp());

 name
------
 None

この例は、属性を引き出すために関数表記を使用しています。これを簡単に説明すると、 attribute(table) と table.attribute という表記方法はどちらでも使用できるということです。

--
-- これは
--  SELECT EMP.name AS youngster FROM EMP WHERE EMP.age < 30
-- と同じです。
--
SELECT name(EMP) AS youngster
    FROM EMP
    WHERE age(EMP) < 30;

 youngster
-----------
 Sam

関数の戻り値の属性を取り出すために関数構文を使用しなければならない理由は、概して、関数呼び出しが組合わさった時に、パーサがドット構文を解釈できなくなることだけです。

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

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

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

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

12.2.4. set を返す SQL 関数

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

set を返す関数は SELECT 問い合わせの対象リスト内でのみ呼び出すことができます。 SELECT が自身によって生成する各行に対し、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 を返すため出力行が生成されないからです。