13.1.1. 例

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

CREATE FUNCTION tp1 (int4, float8) 
    RETURNS int4
    AS 'UPDATE bank 
        SET balance = bank.balance - $2
        WHERE bank.acctountno = $1;
        SELECT 1;'
LANGUAGE 'sql';
     

SELECT tp1( 17,100.0);
     

下記の関数はEMP型の引数を1つを持った関数の例です。 これは引数は1つですが、複数の結果を返すものです。

CREATE FUNCTION hobbies (EMP) RETURNS SETOF hobbies
    AS 'SELECT hobbies.* FROM hobbies
        WHERE $1.name = hobbies.person'
    LANGUAGE 'sql';
     

13.1.2. 基本型を使ったSQL関数

最も簡単なSQL関数は引数がなく、 返す型がint4などの基本型の場合です。

CREATE FUNCTION one() 
    RETURNS int4
    AS 'SELECT 1 as RESULT;' 
    LANGUAGE 'sql';

SELECT one() AS answer;

+-------+
|answer |
+-------+
|1      |
+-------+
     

上記の関数内で、RESULTという名前を関数の結果の列名として 指定しましたが、関数の外ではその指定した名前は 見えないので、列名がanswerとなっていることに ご注意下さい。

基本型を引数として持つSQL関数を定義することも 同じように簡単です。下記の例において、関数内で引数を 参照するために$1と$2をどのように使用しているかに注意して下さい。

CREATE FUNCTION add_em(int4, int4) 
    RETURNS int4
    AS 'SELECT $1 + $2;' 
    LANGUAGE 'sql';

SELECT add_em(1, 2) AS answer;

+-------+
|answer |
+-------+
|3      |
+-------+
     

13.1.3. 複合型を使ったSQL関数

EMPなどのような複合型の引数を持つ関数を指定する時には、 使用する引数を上記の例では単に$1、$2としていましたが、 それに加えて引数の属性も指定する必要があります。 例えば、下記のような給料が倍になったらいくらになるのかを 計算するdouble_salary関数をご覧下さい。

CREATE FUNCTION double_salary(EMP) 
    RETURNS int4
    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.salaryという文法の使用にご注意下さい。複合型を返す関数の話に 進む前に、まず、属性を引き出すための関数記述方法を 紹介しなくてはいけません。簡単にそれを説明すると、 通常は属性(テーブル)とクラス名.属性(table.attribute)の どちらの記述方法でも使用できます。

--
-- this is the same as:
--  SELECT EMP.name AS youngster FROM EMP WHERE EMP.age < 30
--
SELECT name(EMP) AS youngster
    FROM EMP
    WHERE age(EMP) < 30;

+----------+
|youngster |
+----------+
|Sam       |
+----------+
     

ご覧のように、これは常に成り立たないことがお分かり頂けると 思います。この関数記述方法は、1つの行を返す関数を使いたい時には 重要になります。それを行うには、 関数内で1つの行すべてを 属性毎に組み立てます。下記は1つのEMP行を返す関数の例です。

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';
     

この例では、各属性を定数を用いて指定していますが、 これらの定数を演算や式に置き換える事も可能です。 このような関数の定義はコツが必要になります。 より重要な注意点のうちの数点を下記に示します。

• 対象リストの順番はCREATE TABLE行(または .* クエリーを実行した時)に 書かれている属性と正確に同じでである必要があります。

• 型キャストを行なう必要があります。行わない場合、下記のような エラーメッセージが表示されます。

  	 ERROR:  function declared to return emp returns varchar instead of text at column 1
  	 
  	

• 行を返す関数を呼び出す時、行全体を取り出すことはできません。 その行から属性を引き出すか、別の関数にそのまま渡すかを する必要があります。

  SELECT name(new_emp()) AS nobody;
  
  +-------+
  |nobody |
  +-------+
  |None   |
  +-------+
  	

• 一般的に、関数の戻り値の属性を引き出すためにその関数構文を 使用しなければいけない理由は、関数の呼び出しが組合わさった時、 反映用のもう一方の(ドット)文法をパーサが理解する事ができない ためです。

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

SQL問合せ言語のコマンドの集まりはすべて、 一緒にまとめて、1つの関数として定義することが可能です。 コマンドは更新系(INSERT文、UPDATE文、 DELETE文など)や、SELECT文を 使用できます。しかし、関数の最後のコマンドは関数の返り型として 指定されたものを返すSELECT文である必要があります。

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

SELECT clean_EMP();

+--+
|x |
+--+
|1 |
+--+