Chapter 4. SQL の拡張: 関数

Table of Contents
問合せ言語 (SQL) 関数
プログラミング言語関数

As it turns out, part of defining a new type is the definition of functions that describe its behavior. Consequently, while it is possible to define a new function without defining a new type, the reverse is not true. We therefore describe how to add new functions to Postgres before describing how to add new types. Postgres SQL provides two types of functions: query language functions (functions written in SQL and programming language functions (functions written in a compiled programming language such as C.) Either kind of function can take a base type, a composite type or some combination as arguments (parameters). In addition, both kinds of functions can return a base type or a composite type. It's easier to define SQL functions, so we'll start with those. Examples in this section can also be found in funcs.sql and funcs.c.

結局、振舞を記述する関数の定義とは、新しい型の定義の一部分です。 従って、新しい型を定義せずに新しい関数を定義する事はできますが、 逆はできません。ですので、新しい型を追加する方法を説明する前に、 Postgres に新しい関数を追加する方法を 説明する事にします。Postgres SQL は、2 種類の関数を提供します。問合せ言語関 数( SQL で記述された関数)とプログラミング言語 関数( C といったプログラミング言語で記述、コン パイルされた関数)の2つです。両関数は、基本型、複合型、その組合せ を引数(パラメータ)として取ります。更に、両関数とも基本型、複合型 を返す事ができます。SQL 関数の定義の方が簡単です ので、こちらの説明から始めることにします。この節にある例は、 funcs.sqlfuncs.c にあ ります。

問合せ言語 (SQL) 関数

基本型を使った SQL 関数

The simplest possible SQL function has no arguments and simply returns a base type, such as int4:

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

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

    SELECT one() AS answer;

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

Notice that we defined a target list for the function (with the name RESULT), but the target list of the query that invoked the function overrode the function's target list. Hence, the result is labelled answer instead of one.

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

It's almost as easy to define SQL functions that take base types as arguments. In the example below, notice how we refer to the arguments within the function as $1 and $2.

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

When specifying functions with arguments of composite types (such as EMP), we must not only specify which argument we want (as we did above with $1 and $2) but also the attributes of that argument. For example, take the function double_salary that computes what your salary would be if it were doubled.

( 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  |
         +-----+-------+

Notice the use of the syntax $1.salary. Before launching into the subject of functions that return composite types, we must first introduce the function notation for projecting attributes. The simple way to explain this is that we can usually use the notation attribute(class) and class.attribute interchangably.

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


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

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

As we shall see, however, this is not always the case. This function notation is important when we want to use a function that returns a single instance. We do this by assembling the entire instance within the function, attribute by attribute. This is an example of a function that returns a single EMP instance:

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

In this case we have specified each of the attributes with a constant value, but any computation or expression could have been substituted for these constants. Defining a function like this can be tricky. Some of the more important caveats are as follows:

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

  • The target list order must be exactly the same as that in which the attributes appear in the CREATE TABLE statement (or when you execute a .* query).

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

  • You must typecast the expressions (using ::) very carefully or you will see the following error:

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

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

  • When calling a function that returns an instance, we cannot retrieve the entire instance. We must either project an attribute out of the instance or pass the entire instance into another function.

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

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

  • The reason why, in general, we must use the function syntax for projecting attributes of function return values is that the parser just doesn't understand the other (dot) syntax for projection when combined with function calls.

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

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

Any collection of commands in the SQL query language can be packaged together and defined as a function. The commands can include updates (i.e., insert, update and delete) as well as select queries. However, the final command must be a select that returns whatever is specified as the function's returntype.

SQL 問合せ言語のコマンドの集まりは何でも、一 緒にまとめられた、1 つの関数として定義できます。コマンドは更新系( つまり、 insertupdatedelete )も 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 |
         +--+