★PostgreSQLカンファレンス2024 12月6日開催/チケット販売中★
他のバージョンの文書 16 | 15 | 14 | 13 | 12 | 11 | 10 | 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

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

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

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

SQL関数の本体は、セミコロンで区切ったSQL文のリストでなければなりません。 最後の文の後のセミコロンは省略可能です。 関数がvoidを返すものと宣言されていない限り、最後の文はSELECT、またはRETURNING句を持つINSERTUPDATE、またはDELETEでなければなりません。

SQL言語で作成された、任意のコマンド群はまとめて、関数として定義することができます。 SELECT問い合わせ以外に、データ変更用の問い合わせ(つまり、INSERTUPDATEDELETE)やその他のSQLコマンドを含めることができます。 (例外が1つあります。それはBEGINCOMMITROLLBACKSAVEPOINTSQL関数に含めることはできない点です。) しかし、最後のコマンドは、関数の戻り値型として定義したものを返すSELECT、またはRETURNING句があるものでなければなりません。 その他にも、何か動作をさせるが、有用な値を返さないSQL関数を定義したいのであれば、voidを返すものと定義することで実現可能です。 たとえば、以下の関数はempテーブルから負の給料となっている行を削除します。

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

SELECT clean_emp();

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

(1 row)

CREATE FUNCTIONコマンドの構文では、関数本体は文字列定数として作成される必要があります。 この文字列定数の記述には、通常、ドル引用符付け(項4.1.2.4)が最も便利です。 文字列定数を単一引用符で括る通常の構文では、関数本体中で使用される単一引用符(')とバックスラッシュ(\)(エスケープ文字列構文を仮定)を二重にしなければなりません(項4.1.2.1を参照)。

SQL関数の引数は関数本体内で$nという構文を用いて表すことができます。 つまり、$1は第1引数を示し、$2は第2引数のようになります。 引数が複合型の場合、$1.nameのようなドット表記を用いて引数の属性にアクセスすることができます。 この引数は、識別子としてではなく、データ値としてのみ使用することができます。 したがって、例えば

INSERT INTO mytable VALUES ($1);

は正しいものですが、以下は動作しません。

INSERT INTO $1 VALUES (42);

35.4.1. 基本型を使用するSQL関数

最も簡単なSQL関数は、引数を取らずに単にintegerのような基本型を返すものです。

CREATE FUNCTION one() RETURNS integer AS $$
    SELECT 1 AS result;
$$ LANGUAGE SQL;

-- 文字列リテラルの別の構文では
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;

これは残高を調整し、更新後の残高を返します。 同じことがRETURNINGを使用して1つのコマンドで行えます。

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

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

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

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

INSERT INTO emp VALUES ('Bill', 4200, 45, '(2,1)');

CREATE FUNCTION double_salary(emp) RETURNS numeric 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
------+-------
 Bill |  8400

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

SELECT name, double_salary(emp) AS dream
    FROM emp
    WHERE emp.cubicle ~= point '(2,1)';

しかし、この使用方法は混乱しやすいためお勧めしません。

その場で複合型の引数値を作成することが便利な場合があります。 これはROW式で行うことができます。 例えば、以下のようにして関数に渡すデータを調整することができます。

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

複合型を返す関数を作成することもできます。 以下に単一のemp行を返す関数の例を示します。

CREATE FUNCTION new_emp() RETURNS emp AS $$
    SELECT text 'None' AS name,
        1000.0 AS salary,
        25 AS age,
        point '(2,2)' AS cubicle;
$$ LANGUAGE SQL;

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

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

同じ関数を以下のように定義することもできます。

CREATE FUNCTION new_emp() RETURNS emp AS $$
    SELECT ROW('None', 1000.0, 25, '(2,2)')::emp;
$$ LANGUAGE SQL;

ここで、正しい複合型の単一の列を単に返すSELECTを記述しました。 今回の例ではこれはより優れたものとはいえませんが、例えば、必要な複合値を返す他の関数を呼び出して結果を計算しなければならない場合など、便利な解法になることがあります。

以下の2つの方法でこの関数を直接呼び出すことができます。

SELECT new_emp();

         new_emp
--------------------------
 (None,1000.0,25,"(2,2)")

SELECT * FROM new_emp();

 name | salary | age | cubicle
------+--------+-----+---------
 None | 1000.0 |  25 | (2,2)

2番目の方法については、項35.4.7でより詳しく説明します。

複合型を返す関数を使用する時に、その結果から1つのフィールド(属性)のみを使用したいという場合があります。 これは、以下のような構文で行うことができます。

SELECT (new_emp()).name;

 name
------
 None

パーサが混乱しないように、括弧を追加する必要があります。 括弧なしで行おうとすると、以下のような結果になります。

SELECT new_emp().name;
ERROR:  syntax error at or near "."
LINE 1: SELECT new_emp().name;
                        ^

また、関数表記を使用して属性を抽出することもできます。 簡単に説明すると、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
 Andy

ティップ: 関数表記と属性表記の同等性によって、"計算処理されたフィールド"を模擬するために複合型に対する関数を使用することができます。 例えば、上記で定義されたdouble_salary(emp)を使用して、次のように記述することができます。

SELECT emp.name, emp.double_salary FROM emp;

これを利用するアプリケーションは、double_salaryが実際にはテーブルの列ではないことを直接気にする必要はありません (また、ビューを用いて計算処理されたフィールドを模擬することも可能です)。

複合型を結果として返す関数を使用する他の方法は、その結果を、その行型を入力として受け付ける関数に渡す、以下のような方法です。

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

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

複合型の結果を返す関数の使用方法として他にも、テーブル関数として呼び出す方法があります。 これは項35.4.7で説明します。

35.4.3. パラメータ名付きのSQL関数

以下の例のように、関数のパラメータに名前を割り振ることができます。

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

ここでは、第一パラメータにacct_noという名前が、第二パラメータにはdebitという名前が与えられています。 SQL関数自体を考える限り、こうした名前は単なる飾りです。 まだ、関数本体内ではパラメータを$1$2などのように参照しなければなりません。 (一部の手続き言語ではパラメータ名を代わりに使用します。) しかしパラメータに名前を付けることは、文書化という目的では有用です。 項4.3で説明しますが、関数が多くのパラメータを持つ場合はまた、関数を呼び出す時に名前を使用することが有用です。

35.4.4. 出力パラメータを持つSQL 関数

関数の結果の記述方法には、他にも出力パラメータを使用して定義する方法があります。 以下に例を示します。

CREATE FUNCTION add_em (IN x int, IN y int, OUT sum int)
AS 'SELECT $1 + $2'
LANGUAGE SQL;

SELECT add_em(3,7);
 add_em
--------
     10
(1 row)

項35.4.1で示した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)

これは基本的に、関数結果用の匿名の複合型の作成を行います。 上の例では、

CREATE TYPE sum_prod AS (sum int, product int);

CREATE FUNCTION sum_n_product (int, int) RETURNS sum_prod
AS 'SELECT $1 + $2, $1 * $2'
LANGUAGE SQL;

と同じ最終結果になります。 しかし、独立した複合型定義に悩まされることがなくなり、便利であるともいえます。 出力パラメータに割り振られた名前が単なる飾りではなく、無名複合型の列名を決定するものであることに注意してください。 (出力パラメータの名前を省略した場合、システム自身が名前を選びます。)

SQLからこうした関数を呼び出す時、出力パラメータが呼び出し側の引数リストに含まれないことに注意してください。 PostgreSQLでは入力パラメータのみが関数の呼び出しシグネチャを定義するとみなしているためです。 これはまた、関数を削除することなどを目的に関数を参照する場合、入力パラメータのみが考慮されることを意味しています。 上の関数は、次のいずれかの方法で削除することができます。

DROP FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int);
DROP FUNCTION sum_n_product (int, int);

パラメータには、IN(デフォルト)、OUTINOUT、またはVARIADICという印を付与できます。 INOUTパラメータは、入力パラメータ(呼び出し引数リストの一部)と出力パラメータ(結果のレコード型の一部)の両方を提供します。 VARIADICパラメータは入力パラメータですが、次に記載するように特別に扱われます。

35.4.5. 可変長引数を取るSQL関数

すべての"オプションの"引数が同じデータ型の場合、SQL関数は可変長の引数を受け付けるように宣言できます。 オプションの引数は配列として関数に渡されます。 この関数は最後のパラメータをVARIADICと印を付けて宣言されます。 このパラメータは配列型であるとして宣言されなければなりません。 例をあげます。

CREATE FUNCTION mleast(VARIADIC arr numeric[]) RETURNS numeric AS $$
    SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL;

SELECT mleast(10, -1, 5, 4.4);
 mleast 
--------
     -1
(1 row)

実際、VARIADICの位置以降の実引数はすべて、あたかも以下のように記述したかのように、1次元の配列としてまとめられます。

SELECT mleast(ARRAY[10, -1, 5, 4.4]);    -- 動作しません

しかし、実際にこのように記述することはできません。 少なくとも、この関数定義に一致しません。 VARIADIC印の付いたパラメータは、自身の型ではなく、その要素型が1つ以上存在することに一致します。

時として、variadic関数に既に構築された配列を渡せることは有用です。 1つのvariadic関数が、自身の配列パラメータを他のものに渡したいとき特に便利です。 呼び出しにVARIADICを指定することで行えます。

SELECT mleast(VARIADIC ARRAY[10, -1, 5, 4.4]);

これは関数のvariadicパラメータがその要素型に拡張するのを防ぎます。 その結果、配列引数値が標準的にマッチされるようになります。 VARIADICは関数呼び出しの最後の実引数としてのみ付加できます。

variadicパラメータから生成される配列要素パラメータは、それ自身にはまったく名前を持たないものとして扱われます。 これは、名前付き引数(項4.3)を使用して可変長の関数を呼び出すことができないことを意味します。 ただし、VARIADICを指定する場合は例外です。 たとえば、

SELECT mleast(VARIADIC arr := ARRAY[10, -1, 5, 4.4]);

は動作しますが、

SELECT mleast(arr := 10);
SELECT mleast(arr := ARRAY[10, -1, 5, 4.4]);

次は動作しません。

35.4.6. 引数にデフォルト値を持つSQL関数

一部またはすべての入力引数にデフォルト値を持つ関数を宣言することができます。 デフォルト値は、関数が実際の引数の数に足りない数の引数で呼び出された場合に挿入されます。 引数は実引数リストの終端から省略することができますので、デフォルト値を持つパラメータの後にあるパラメータはすべて、同様にデフォルト値を持たなければなりません。 (名前付きの引数記法を使用してこの制限を緩和させることもできますが、まだ位置引数記法が実用的に動作できることが強制されています。)

以下に例を示します。

CREATE FUNCTION foo(a int, b int DEFAULT 2, c int DEFAULT 3)
RETURNS int
LANGUAGE SQL
AS $$
    SELECT $1 + $2 + $3;
$$;

SELECT foo(10, 20, 30);
 foo 
-----
  60
(1 row)

SELECT foo(10, 20);
 foo 
-----
  33
(1 row)

SELECT foo(10);
 foo 
-----
  15
(1 row)

SELECT foo();  -- fails since there is no default for the first argument
ERROR:  function foo() does not exist

=記号をDEFAULTキーワードの代わりに使用することもできます。

35.4.7. テーブルソースとしての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
(1 row)

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

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

35.4.8. 集合を返すSQL関数

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

この機能は通常、関数を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 TABLE tab (y int, z int);
INSERT INTO tab VALUES (1, 2), (3, 4), (5, 6), (7, 8);

CREATE FUNCTION sum_n_product_with_tab (x int, OUT sum int, OUT product int)
RETURNS SETOF record
AS $$
    SELECT $1 + tab.y, $1 * tab.y FROM tab;
$$ LANGUAGE SQL;

SELECT * FROM sum_n_product_with_tab(10);
 sum | product
-----+---------
  11 |      10
  13 |      30
  15 |      50
  17 |      70
(4 rows)

ここで重要な点は、関数が1行だけではなく複数行を返すことを示すためにRETURNS SETOF recordを記述しなければならない点です。 出力パラメータが1つしか存在しない場合は、recordではなく、そのパラメータの型を記述してください。

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

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

注意: もし関数の最後のコマンドがRETURNINGを持つINSERTUPDATE、またはDELETEである場合、関数がSETOF付きで宣言されていない、または呼び出す問い合わせが全ての結果行を取り出さなくても、そのコマンドは完了まで実行されます。 RETURNING句で生成される余計な行はすべて警告無しに削除されますが、コマンド対象のテーブルの変更はそれでも起こります(そして、関数から戻る前にすべて完了します)。

35.4.9. TABLEを返すSQL関数

集合を返すものとして関数を宣言するには、他にも方法があります。 RETURNS TABLE(columns)構文を使用することです。 これは1つ以上のOUTパラメータを使い、さらに、関数をSETOF record(または、適切ならば単一の出力パラメータの型のSETOF)を返すものと印を付けることと等価です。 この記法は標準SQLの最近の版で規定されたものですので、SETOFを使用するより移植性がより高いかもしれません。

例えば前述の合計と積の例はこのように書けます。

CREATE FUNCTION sum_n_product_with_tab (x int)
RETURNS TABLE(sum int, product int) AS $$
    SELECT $1 + tab.y, $1 * tab.y FROM tab;
$$ LANGUAGE SQL;

RETURNS TABLE記法と一緒に、明示的OUTまたはINOUTパラメータを使用することはできません。 全ての出力列をTABLEリストに含めなければなりません。

35.4.10. 多様SQL関数

SQL関数は、多様型anyelementanyarrayanynonarrayおよびanyenumを受け付け、返すように宣言することができます。 多様関数の詳細説明については項35.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 polymorphic 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 a polymorphic type must have at least one polymorphic argument.

出力引数を持つ関数でも多様性を使用することができます。 以下に例を示します。

CREATE FUNCTION dup (f1 anyelement, OUT f2 anyelement, OUT f3 anyarray)
AS 'select $1, array[$1,$1]' LANGUAGE SQL;

SELECT * FROM dup(22);
 f2 |   f3
----+---------
 22 | {22,22}
(1 row)

多様性はvariadic関数とともに使用できます。例をあげます。

CREATE FUNCTION anyleast (VARIADIC anyarray) RETURNS anyelement AS $$
    SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL;

SELECT anyleast(10, -1, 5, 4);
 anyleast 
----------
       -1
(1 row)

SELECT anyleast('abc'::text, 'def');
 anyleast 
----------
 abc
(1 row)

CREATE FUNCTION concat(text, VARIADIC anyarray) RETURNS text AS $$
    SELECT array_to_string($2, $1);
$$ LANGUAGE SQL;

SELECT concat('|', 1, 4, 2);
 concat 
--------
 1|4|2
(1 row)