基本型を使った SQL 関数

最も簡単にできる SQL 関数は引数をとらずに、 int4 といった基本型を返す次のようなものです。

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

    SELECT one() AS answer;

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

（ RESULT という名前の）この関数用の対象リストを定義しました。 が、この関数を呼び出すクエリーの対象リストは、この関数用の対象 リストを上書きします。従って、結果はこの名前ではなくラベルを付 けられたものになります。

基本型を引数として持つ 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      |
         +-------+

複合型を使った 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 ~= '(2,1)'::point;
     

         +-----+-------+
         |name | dream |
         +-----+-------+
         |Sam  | 2400  |
         +-----+-------+

$1.salary という文法の使用に気を付けて下さい。複合型を返す関数につ いての話をする前に、まず、属性を引き出すための関数記述方法を紹介し なくてはいけません。簡単に説明すると、通常は属性名(クラス名)とクラ ス名.属性名という記述方法をどちらでも使用できるということです。

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

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

しかし、これは常に成り立たないことはいずれわかるでしょう。この関 数記述方法は、一つのインスタンスを返す関数を使いたい時には重要で す。関数内で一つのインスタンス全体を属性毎に組み立てることによっ て、これを行ないます。以下は一つの EMP インスタンスを返す関数の例 です。

    CREATE FUNCTION new_emp() RETURNS EMP
     AS 'SELECT \'None\'::text AS name,
      1000 AS salary,
      25 AS age,
       \'(2,2)\'::point AS cubicle'
      LANGUAGE 'sql';

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

• 対象リストの順番は CREATE TABLE 行（または .* クエリーを実行した時） に現れる属性と正確に同じでなければいけません。

• 注意深く（ :: を使った）式で型キャストを行なう必要があります。さ もないと、次のエラーが表示されます。

     WARN::function declared to return type EMP does not retrieve (EMP.*)

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

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

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

              SELECT new_emp().name AS nobody;
              WARN: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 |
         +--+