PostgreSQL教程（19）：SQL言語関数

一、基本概念：
    SQL関数は任意の数のクエリーを含むことができますが、関数は最後のクエリ（SELECTでなければならない）の結果だけを返します。簡単な場合は、最後のクエリー結果の最初の行を返します。最後のクエリーが任意の行に戻らない場合、関数はNULL値を返します。この関数が最後のSELECT文のすべての行に戻る場合、関数の戻り値をセットとして定義することができます。すなわちSETOF sometype。
    SQL関数の関数は、セミコロンで区切られたSQL文のリストであるべきで、最後の文の後のセミコロンは任意である。関数宣言がvoidに戻る場合を除き、最後のステートメントはSELECTでなければなりません。実際には、SQL関数にはSELECTクエリ文だけでなく、INSERT、UPDATE、DELETEなどの他の標準的なSQL文も含まれますが、BEGIN、COMMIT、ROLLBACK、SAVPOINTなど、事物に関する語句は含まれません。
    CREATE FUNTIONコマンドの構文要求関数体は文字列テキストを作成します。一般的には、テキスト文字列の定数は、ドル記号（$）で囲まれています。
 

    CREATE FUNCTION clean_emp() RETURNS void AS $$

        DELETE FROM emp WHERE salary < 0;

    $$ LANGUAGE SQL;

 

    最後に、SQL関数のパラメータを説明する必要があります。PostgreSQLは$1を定義し、$2は第二のパラメータであり、これに類推します。パラメータが複合タイプであれば、ドット表現法、すなわち$1.nameを使用して、複合タイプパラメータのnameフィールドにアクセスすることができる。なお、関数パラメータはデータ値としてのみ使用でき、識別子としては使用できない。
 

    INSERT INTO mytable VALUES ($1);   --  

    INSERT INTO $1 VALUES (42);          --   (         )

    

二、基本タイプ：
    
    最も簡単なSQL関数は、パラメータがなく、基本タイプの関数を返します。
 

    CREATE FUNCTION one() RETURNS integer AS $$

        SELECT 1 AS result;

    $$ LANGUAGE SQL;

 

    以下の例は関数としての基本型のパラメータを示している。
 

    CREATE FUNCTION add_em(integer, integer) RETURNS integer AS $$

        SELECT $1 + $2;

    $$ LANGUAGE SQL;

    #   select    。

    postgres=# SELECT add_em(1,2) AS answer;

     answer

    --------

          3

    (1 row)

 

    以下の例では、関数内に複数のSQL文が含まれており、それらの間はセミコロンで区切られている。
 

    CREATE FUNCTION tf1 (integer, numeric) RETURNS numeric AS $$

        UPDATE bank SET balance = balance - $2 WHERE accountno = $1;

        SELECT balance FROM bank WHERE accountno = $1;

    $$ LANGUAGE SQL;

三、複合タイプ：
    次の例を参照してください
    1）データテーブルを作成し、これに対応する複合タイプも生成する。
 

    CREATE TABLE emp (

        name        text,

        salary      numeric,

        age         integer,

    );

 

    2）関数を作成します。そのパラメータは複合タイプです。関数内では、基本型パラメータを参照するように、複合タイプを参照することができます。複合タイプのフィールドにアクセスするには、ポイント表式を使えばいいです。例えば$1.salary。
   

 CREATE FUNCTION double_salary(emp) RETURNS integer AS $$

        SELECT ($1.salary * 2)::integer AS salary;

    $$ LANGUAGE SQL;

 

    3）select文では、emp.*を使ってempテーブルの行全体のデータを表します。
 

    SELECT name, double_salary(emp.*) AS dream FROM emp WHERE age > 30;

 

    4）ROW表現を使ってカスタム複合タイプを作成することもできます。
 

    SELECT name, double_salary(ROW(name, salary*1.1, age)) AS dream FROM emp;

 

    5）戻り値は複合タイプの関数を作成します。
 

    CREATE FUNCTION new_emp() RETURNS emp AS $$

        SELECT ROW('None', 1000.0, 25)::emp;

    $$ LANGUAGE SQL;

 

    6）複合タイプに戻る関数を呼び出します。
 

    SELECT new_emp();

 

    7）複合タイプに戻る関数を呼び出し、その戻り値のあるフィールドにアクセスします。
 

    SELECT (new_emp()).name;

 

四、出力パラメータの関数：
    もう一つの方法は、関数実行の結果、すなわち出力パラメータを返すために使用できます。
 

    CREATE FUNCTION add_em2 (IN x int, IN y int, OUT sum int) AS $$

        SELECT $1 + $2

    $$ LANGUAGE SQL;

 

    呼び出し方法と戻り結果とadd_em(戻り値付きの関数)は完全に一致します。
 

    SELECT add_em(3,7);

    

    これは出力パラメータの関数と前のadd_を持っています。em関数には本質的な違いがありません。実際には、出力パラメータの真の値は、関数に複数のフィールドを返す方法を提供することにある。のように
 

    CREATE FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int) AS $$

        SELECT $1 + $2, $1 * $2

    $$ LANGUAGE SQL;

 

    呼び出し方式は変更されていません。戻り結果が一列多くなります。
 

    SELECT * FROM sum_n_product(11,42);

     sum | product

    -----+---------

      53 |     462

    (1 row)

 

    上記の例では、INは入力パラメータ（デフォルト値、無視できます）を表し、OUTは出力パラメータを表します。
五、戻りの結果を表のデータソースとします。
    すべてのSQL関数は照会のFROMサブルーチンで使用できます。この方法は複合タイプの関数を返すために特に有用であり、この関数が基本タイプを返すと定義されると、関数は単一フィールドテーブルを生成し、この関数が複合タイプを返すと定義されると、複合タイプの各属性の行を生成する。次の例を参照してください
    1）データテーブルを作成します。
 

    CREATE TABLE foo (

        fooid    int, 

        foosubid int, 

        fooname  text

    );

 

    2）SQL関数を作成し、その戻り値はfooテーブルに対応する複合タイプです。
 

    CREATE FUNCTION getfoo(int) RETURNS foo AS $$

        SELECT * FROM foo WHERE fooid = $1;

    $$ LANGUAGE SQL;

 

    3）FROMサブ文でこの関数を呼び出します。
 

    SELECT *, upper(fooname) FROM getfoo(1) AS t1;

 

六、集合のSQL関数を返します。
    SQL関数の戻り値がSETOF sometypeの場合、この関数を呼び出した場合、最後のSELECTクエリのすべてのデータが返されます。この特性は通常、FROMサブカルに関数を入れて呼び出すために使用されます。次の例を参照してください。
    CREATE FUNTION getfoo(int)RETURNS setof foo AS$
        SELECT*FROM foo WHERE fooid=$1
    $$ LANGUAGE SQL   
    FROM文では、複合タイプの集合に戻る関数を呼び出したが、その結果は、SELECT*FROM(SELECT*FROM foo WHERE fooid=1)t 1に等しい。
    SELECT*FROM getfoo(1)AS t 1;    
   
七、多状態のSQL関数：
    SQL関数は、多状態タイプ（anyelementおよびanyarray）を受け入れるパラメータとして宣言することができ、または多状態タイプの戻り値を返すことができる。
    1）関数パラメータと戻り値はいずれも多状態タイプです。
 

    CREATE FUNCTION make_array(anyelement, anyelement) RETURNS anyarray AS $$

        SELECT ARRAY[$1, $2];

    $$ LANGUAGE SQL;

 

    その呼び出し方は、他のタイプのSQL関数と全く同じです。文字列タイプのパラメータを渡すときには、明示的にターゲットタイプに変換する必要があります。そうでなければ、unknownタイプと見なされます。
 

    SELECT make_array(1, 2) AS intarray, make_array('a'::text, 'b') AS textarray;

 

    2）関数のパラメータは多形タイプで、戻り値は基本タイプです。
 

    CREATE FUNCTION is_greater(anyelement, anyelement) RETURNS boolean AS $$

        SELECT $1 > $2;

    $$ LANGUAGE SQL;

 

    3）関数の出力パラメータに多形タイプを使用します。
 

    CREATE FUNCTION dup (f1 anyelement, OUT f2 anyelement, OUT f3 anyarray) AS $$

        SELECT $1, ARRAY[$1,$1]

    $$ LANGUAGE sql;

八、関数の再負荷:
    複数の関数は同じ関数名として定義できますが、それらのパラメータは必ず区別されます。言い換えれば、関数名を再ロードできます。この規則は、オブジェクト言語に向けられた関数の再負荷に似ているものがあります。以下の例を参照してください。
 

    CREATE FUNCTION test(int, real) RETURNS ...

    CREATE FUNCTION test(smallint, double) RETURNS ...

    PostgreSQLでは関数がリロードをサポートしていますので、関数を削除する際にも、パラメータリストを指定しなければなりません。
 

    DROP FUNCTION test(int, real);

    DROP FUNCTION test(smallint,double);