他のバージョンの文書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

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

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

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

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

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

33.4.1. 基本型を使用する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

以下にもう少し役に立つ関数を示します。 これは銀行口座の借方票に使用できます。

CREATE FUNCTION tf1 (integer, numeric) RETURNS integer AS '
    UPDATE bank 
        SET balance = balance - $2
        WHERE accountno = $1;
    SELECT 1;
' LANGUAGE SQL;

以下のように、ユーザはこの関数を使用して、口座番号17から100ドルを引き出すことが可能です。

SELECT tf1(17, 100.0);

実際には、関数の結果を定数1よりも分かりやすい形にするために、以下のように定義するとよいでしょう。

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;

これは残高を調整し、更新後の残高を返します。

SQL言語で作成された、任意のコマンド群はまとめて、関数として定義することができます。 SELECT問い合わせ以外に、データの変更コマンド(つまり、INSERTUPDATEDELETE)を含めることができます。 しかし、最後のコマンドは、関数の戻り値型で定義したものを返すSELECTでなければなりません。 その他にも、何か動作をさせるが、有用な値を返さないSQL関数を定義したいのであれば、voidを返すものと定義することで実現可能です。 その場合、関数本体はSELECTで終ってはなりません。 以下に例を示します。

CREATE FUNCTION clean_emp() RETURNS void AS '
    DELETE FROM emp
        WHERE salary <= 0;
' LANGUAGE SQL;

SELECT clean_emp();

 clean_emp
-----------

(1 row)

33.4.2. 複合型を使用するSQL関数

関数の引数に複合型を指定した場合、 (これまで行なっていた$1$2のように)必要な引数だけを指定するだけではなく、その引数の属性も指定する必要があります。 例えば、empが従業員データを持つテーブルとすると、この名前はそのテーブル内の各行を表す複合型の名前でもあります。 以下に示すdouble_salary関数は、もしあれば従業員の給料を倍増させます。

CREATE TABLE emp (
    name        text,
    salary      integer,
    age         integer,
    cubicle     point
);

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.salaryという構文を使用して、引数の行値の1フィールドを選択していることに注目してください。 また、SELECTコマンドの呼び出しでは、複合型の値として、現在のテーブル行全体を表すテーブル名を使用していることにも注目してください。 別の方法として、テーブル行は以下のように参照することができます。

SELECT name, double_salary(emp.*) AS dream
    FROM emp
    WHERE emp.cubicle ~= point '(2,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;

ここでは、各属性を定数で指定していますが、この定数を演算に置き換えることもできます。

関数を定義する上で、2つの重要な注意点を以下に示します。

行(複合型)を返す関数は、後述のテーブル関数として使用できます。 この関数は、その行から属性を1つだけ抽出する場合、あるいは、同じ複合型を受け入れる他の関数に行全体を渡す場合にのみ、SQL式の文脈でも呼び出すことができます。

以下は、その行から属性を1つ抽出する例です。

SELECT (new_emp()).name;

 name
------
 None

パーサが混乱しないように、括弧を追加する必要があります。

SELECT new_emp().name;
ERROR:  syntax error at or near "." at character 17

また、関数表記を使用して属性を抽出することもできます。 簡単に説明すると、attribute(table)table.attributeという表記方法のどちらでも使用できるということです。

SELECT name(new_emp());

 name
------
 None

-- これは
-- SELECT emp.name AS youngster FROM emp WHERE emp.age < 30
-- と同一です。

SELECT name(emp) AS youngster
    FROM emp
    WHERE age(emp) < 30;

 youngster
-----------
 Sam

一行を結果として返す関数を使用する他の方法は、引数として行型を受け付ける2番目の関数を宣言して、1番目の関数の結果を渡す、以下のような方法です。

CREATE FUNCTION getname(emp) RETURNS text AS '
    SELECT $1.name;
' LANGUAGE SQL;

SELECT getname(new_emp());
 getname
---------
 None
(1 row)

33.4.3. テーブルソースとしてのSQL関数

SQL関数はすべてFROM句で使用できますが、複合型を返す関数に特に便利です。 関数が基本型を返すよう定義されている場合、テーブル関数は1列から成るテーブルを作成します。 関数が複合型を返すよう定義されている場合、テーブル関数は複合型の列のそれぞれに対して1つの列を作成します。

以下に例を示します。

CREATE TABLE foo (fooid int, foosubid int, fooname text);
INSERT INTO foo VALUES (1, 1, 'Joe');
INSERT INTO foo VALUES (1, 2, 'Ed');
INSERT INTO foo VALUES (2, 1, 'Mary');

CREATE FUNCTION getfoo(int) RETURNS foo AS '
    SELECT * FROM foo WHERE fooid = $1;
' LANGUAGE SQL;

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

 fooid | foosubid | fooname | upper
-------+----------+---------+-------
     1 |        1 | Joe     | JOE
(2 rows)

例から分かるとおり、関数の結果の列を通常のテーブルの列と同じように扱うことができます。

この関数の結果得られたのは1行のみであることに注意してください。 これはSETOFを指定しなかったためです。 これについては次節で説明します。

33.4.4. 集合を返すSQL関数

SQL関数がSETOF sometypeを返すよう宣言されている場合、関数の最後のSELECT問い合わせは最後まで実行され、各出力行は結果集合の要素として返されます。

この機能は通常、関数をFROM句内で呼び出す時に使用されます。 この場合、関数によって返される各行は、問い合わせから見えるテーブルの行になります。 例えば、テーブルfooの内容が上記と同じであれば以下のようになります。

CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS '
    SELECT * FROM foo WHERE fooid = $1;
' LANGUAGE SQL;

SELECT * FROM getfoo(1) AS t1;

この出力は以下の通りです。

 fooid | foosubid | fooname
-------+----------+---------
     1 |        1 | Joe
     1 |        2 | Ed
(2 rows)

現在、集合を返す関数は選択リスト内でも呼び出すことができます。 問い合わせ自身によって生成する各行に対し、集合を返す関数が呼び出され、関数の結果集合の各要素に対して出力行が生成されます。 ただし、この機能は現在では推奨されておらず、今後のリリースでは削除される可能性があります。 以下は、選択リストから集合を返す関数の例です。

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がこの入力に対して空の集合を返すため出力行が生成されないからです。

33.4.5. 多様SQL関数

SQL関数は、多様型anyelementおよびanyarrayを受け付け、返すように宣言することができます。 多様関数の詳細説明については項33.2.5を参照してください。 以下のmake_array多様関数は、任意の2つのデータ型要素から配列を作成します。

CREATE FUNCTION make_array(anyelement, anyelement) RETURNS anyarray AS '
    SELECT ARRAY[$1, $2];
' LANGUAGE SQL;

SELECT make_array(1, 2) AS intarray, make_array('a'::text, 'b') AS textarray;
 intarray | textarray
----------+-----------
 {1,2}    | {a,b}
(1 row)

'a'::textという型キャストを使用して、引数がtext型であることを指定していることに注目してください。 これは引数が単なる文字列リテラルである場合に必要です。 さもないと、unknown型として扱われてしまうため、無効なunknownの配列を返そうとしてしまいます。 型キャストがないと、以下のようなエラーが発生します。

ERROR:  could not determine "anyarray"/"anyelement" type because input has type "unknown"

決定された戻り値型で多様引数を持つことは許されますが、逆は許されません。 以下に例を示します。

CREATE FUNCTION is_greater(anyelement, anyelement) RETURNS boolean AS '
    SELECT $1 > $2;
' LANGUAGE SQL;

SELECT is_greater(1, 2);
 is_greater
------------
 f
(1 row)

CREATE FUNCTION invalid_func() RETURNS anyelement AS '
    SELECT 1;
' LANGUAGE SQL;
ERROR:  cannot determine result data type
DETAIL:  A function returning "anyarray" or "anyelement" must have at least one argument of either type.