他のバージョンの文書9.6 | 9.5 | 9.4 | 9.3 | 9.2 | 9.1 | 9.0 | 8.4 | 8.3 | 8.2 | 8.1 | 8.0 | 7.4 | 7.3 | 7.2

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

SQL関数は、任意なSQL問い合わせ文のリストを実行し、そのリストの最後の問い合わせの結果を返します。この最後の問い合わせは SELECT でなければなりません。単純な(非setの)場合、最後の問い合わせの結果の最初の行が返されます。(複数行の結果のうちの "最初の行"は、ORDER BY を使用しない限り定義づけることができないことは覚えておいて下さい。) 最後の問い合わせが何も行を返さない時は NULL が返されます。

他にも、SQL 関数は、SETOF sometype 型を返すように指定することにより、 set を返すように宣言することもできます。この場合、最後の問い合わせの結果の全ての行が返されます。より詳細は後で説明します。

SQL 関数の本体は、セミコロンで区切った 1 つ以上の SQL 文のリストでなければなりません。 CREATE FUNCTION コマンドの構文では、関数の本体を単一引用符で括る必要がありますので、関数本体中で使用される単一引用符(')を、2つの単一引用符((''))かバックスラッシュ(\')を引用符の部分に付けて、エスケープしなければなりません。

SQL関数の引数は関数本体内で $n という構文を用いて表すことができます。つまり、$1は第1引数を示し、$2は第2引数...となります。引数が複合型の場合、$1.emp のような "ドット表記" を用いて引数の属性にアクセスすることができます。

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つの重要な注意点を以下に示します。

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 において、 Child2Child3などが出力行に表示されていないことに注意して下さい。これは、listchildren はこの入力に対して空の set を返すため出力行が生成されないからです。