★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

39.3. 宣言

ブロック内で使用される全ての変数はそのブロックの宣言部で宣言されなければなりません。 (唯一の例外は、FORループである整数値の範囲に渡って繰り返されるループ変数で、これは、自動的に整数型変数として宣言されます。 同様に、カーソルの結果に対して繰り返し適用されるFORループのループ変数はレコード変数として自動的に宣言されます。)

PL/pgSQL変数は、integervarcharcharといった、任意のSQLデータ型を持つことができます。

変数宣言の例を以下に示します。

user_id integer;
quantity numeric(5);
url varchar;
myrow tablename%ROWTYPE;
myfield tablename.columnname%TYPE;
arow RECORD;

変数宣言の一般的な構文は以下の通りです。

name [ CONSTANT ] type [ COLLATE collation_name ] [ NOT NULL ] [ { DEFAULT | := } expression ];

DEFAULT句が指定された場合、ブロックに入った時に変数に代入される初期値を指定します。 DEFAULT句が指定されない場合、変数はSQLのNULL値に初期化されます。 CONSTANTオプションにより、そのブロック内でその値が不変になるように、その変数への初期化後の代入は禁止されます。 COLLATEオプションは、変数として使用するための照合を指定します(項39.3.6を参照してください)。 NOT NULLが指定された場合、NULL値の代入は実行時エラーになります。 NOT NULLとして宣言した変数は全て、非NULLのデフォルト値を指定しなければなりません。

変数のデフォルト値はブロックに入る度に評価され、変数に代入されます(関数を呼び出す時に一度だけではありません)。 ですから、例えばnow()timestamp型の変数に代入することで、その変数には関数をプリコンパイルした時刻ではなく、関数呼び出し時の現在時刻が格納されます。

例:

quantity integer DEFAULT 32;
url varchar := 'http://mysite.com';
user_id CONSTANT integer := 10;

39.3.1. 関数引数の宣言

関数に渡されるパラメータの名前には$1$2という識別子が付けられます。 省略することもできますが、$nというパラメータ名に別名を宣言することができ、可読性が向上します。 別名、数字による識別子の両方とも引数の値を参照する時に使用することができます。

別名を作成する方法は2つあり、望ましい方法はCREATE FUNCTIONコマンドの中でパラメータを命名するものです。 以下に例を示します。

CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
BEGIN
    RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

他の方法は、宣言構文を用いて別名を明確に宣言するものです。 これは PostgreSQL 8.0より前では、唯一の方法です。

name ALIAS FOR $n;

以下にこの方法による例を示します。

CREATE FUNCTION sales_tax(real) RETURNS real AS $$
DECLARE
    subtotal ALIAS FOR $1;
BEGIN
    RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

注意: この二例は完全に同等ではありません。 最初の例では、subtotalsales_tax.subtotalで参照できますが、次の例ではできません (その代わり、内部ブロックにラベルを付与すれば、subtotalをラベルで修飾することができます)。

さらに数例を示します。

CREATE FUNCTION instr(varchar, integer) RETURNS integer AS $$
DECLARE
    v_string ALIAS FOR $1;
    index ALIAS FOR $2;
BEGIN
    -- v_string とインデックスを使用した何らかの演算を行なう
END;
$$ LANGUAGE plpgsql;


CREATE FUNCTION concat_selected_fields(in_t sometablename) RETURNS text AS $$
BEGIN
    RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;
END;
$$ LANGUAGE plpgsql;

PL/pgSQL関数が出力パラメータと共に宣言されると、通常の入力パラメータと同様に、出力パラメータには$nというパラメータ名と任意の別名が与えられます。 出力パラメータは実質的には最初がNULL値の変数であり、関数の実行中に値が指定されるはずです。 出力パラメータの最後の値は戻り値です。 例えば、消費税の例題は、次のようにすることもできます。

CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$
BEGIN
    tax := subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

RETURNS realを省略したことに注意してください。 含めることもできますが、冗長になります。

出力パラメータは複数の値を返す時に最も有用になります。 簡単な例題を示します。

CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
BEGIN
    sum := x + y;
    prod := x * y;
END;
$$ LANGUAGE plpgsql;

項35.4.4で述べたように、この方法は関数の結果に対する匿名のレコード型を実質的に作成します。 RETURNS句が与えられた時は、RETURNS recordと言わなければなりません。

PL/pgSQL関数を宣言する他の方法として、RETURNS TABLEを伴うことが挙げられます。 以下に例を示します。

CREATE FUNCTION extended_sales(p_itemno int)
RETURNS TABLE(quantity int, total numeric) AS $$
BEGIN
    RETURN QUERY SELECT quantity, quantity * price FROM sales
                 WHERE itemno = p_itemno;
END;
$$ LANGUAGE plpgsql;

これは、1つ、またはそれ以上のOUTパラメータを宣言すること、およびRETURNS SETOF 何らかのデータ型を指定することと全く等価です。

PL/pgSQL関数の戻り値が多様型(anyelementanyarrayanynonarrayまたはanyenum)として宣言されると、特別な$0パラメータが作成されます。 このデータ型が、実際の入力型から推定(項35.2.5を参照)された関数の実際の戻り値型です。 $0はNULLで初期化され、関数内で変更することができます。 ですので、必須ではありませんが、これを戻り値を保持するために使用しても構いません。 また$0に別名を付与することもできます。 例えば、以下の関数は+演算子を持つ任意のデータ型に対して稼働します。

CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement)
RETURNS anyelement AS $$
DECLARE
    result ALIAS FOR $0;
BEGIN
    result := v1 + v2 + v3;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

1つ以上の出力パラメータを多様型として宣言することにより、同様の結果を得ることができます。 この場合、特殊な$0パラメータは使用されません。 出力パラメータ自身が同じ目的を果たします。 以下に例を示します。

CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement,
                                 OUT sum anyelement)
AS $$
BEGIN
    sum := v1 + v2 + v3;
END;
$$ LANGUAGE plpgsql;

39.3.2. ALIAS

newname ALIAS FOR oldname;

ALIAS構文は前節で示したものより一般的です。 関数の引数だけではなく、任意の変数に別名を宣言することができます。 この現実的な使用は主に、トリガプロシージャにおけるNEWOLDなど、前もって決まった名前の変数に別の名前を割り当てることです。

以下に例を示します。

DECLARE
  prior ALIAS FOR old;
  updated ALIAS FOR new;

ALIASは同じオブジェクトを命名する2つの異なる手段を提供しますので、無制限に使用すると混乱を招くかもしれません。 前もって決まっている名前を上書きする目的に限定して使用することが最善です。

39.3.3. 型のコピー

variable%TYPE

%TYPEは変数やテーブル列のデータ型を提供します。 これを使用してデータベース値を保持する変数を宣言することができます。 例えば、usersテーブルにuser_idという列があるものとします。 users.user_idと同じデータ型の変数を宣言するには、以下のように記述します。

user_id users.user_id%TYPE;

%TYPEを使用することで、参照する構造のデータ型を把握する必要がなくなります。 また、これが最も重要なことですが、参照される項目のデータ型が将来変更された(例えば、user_idのテーブル定義をintegerからrealに変更した)場合でも、関数定義を変更する必要をなくすことができます。

内部変数用のデータ型は呼び出す度に変わるかもしれませんので%TYPEは特に多様関数で有用です。 関数の引数や結果用のプレースホルダに%TYPEを適用することで、適切な変数を作成することができます。

39.3.4. 行型

name table_name%ROWTYPE;
name composite_type_name;

複合型の変数は、変数(または行型変数)と呼ばれます。 こういった変数には、問い合わせの列集合が変数の型宣言と一致する限り、SELECTFOR問い合わせの結果の行全体を保持することができます。 行変数の個々のフィールド値には、例えば、rowvar.fieldといったドット記法を使用してアクセスすることができます。

table_name%ROWTYPEという記法を使用して、既存のテーブルやビューの行と同じ型を持つ行変数を宣言することができます。 もしくは、複合型の名前を付与して宣言することができます。 (全てのテーブルは、同じ名前の関連する複合型を持ちますので、実際のところPostgreSQLでは、%ROWTYPEと書いても書かなくても問題にはなりません。 しかし、%ROWTYPEの方がより移植性が高まります。)

関数へのパラメータとして複合型(テーブル行全体)を取ることができます。 その場合、対応する識別子$nは行変数であり、そのフィールドを、例えば、$1.user_idで選択することができます。

テーブル行のユーザ定義の属性のみに行型変数でアクセスすることができます。 OIDやその他のシステム属性にはアクセスできません(ビューからの行があり得るためです)。 行型のフィールドは、例えばchar(n)などのテーブルのフィールドの大きさやデータ型の精度を継承します。

以下に複合型を使用する例を示します。 table1及びtable2は、 少なくとも言及するフィールドを有する既存のテーブルです。

CREATE FUNCTION merge_fields(t_row table1) RETURNS text AS $$
DECLARE
    t2_row table2%ROWTYPE;
BEGIN
    SELECT * INTO t2_row FROM table2 WHERE ... ;
    RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7;
END;
$$ LANGUAGE plpgsql;

SELECT merge_fields(t.*) FROM table1 t WHERE ... ;

39.3.5. レコード型

name RECORD;

レコード変数は行型変数と似ていますが、事前に定義された構造を持っていません。 これはSELECTFORコマンドの間で代入された行の実際の行構造を取ります。 レコード変数の副構造は、代入を行う度に変更できます。 つまり、レコード変数は、最初に代入されるまで副構造を持たず、したがって、フィールドへのアクセスを試みると実行時エラーが発生します。

RECORDは本当のデータ型ではなく、単なるプレースホルダであることに注意してください。 PL/pgSQL関数がrecord型を返す時、この関数ではレコード変数を使用してその結果を保持することができますが、これはレコード変数としての概念とはまったく異なることを認識すべきです。 両方とも、関数の作成段階では実際の行構造は不明です。 しかし、レコード変数はその場その場でその行構造を変更できるにもかかわらず、recordを返す関数では呼び出し元の問い合わせが解析された時点で実際の構造は決定されます。

39.3.6. PL/pgSQL変数の照合

PL/pgSQL関数が照合可能なデータ型のパラメータを 1つ以上保有する場合、項22.2に記述したように、実際の引数に割り当てられた照合に従って、関数呼び出し毎に照合が識別されます。 照合の識別に成功した場合(すなわち、引数の間に事実上の照合における衝突がない場合)、照合可能な全てのパラメータは事実上の照合を有するとして扱われます。 これは関数内部において、照合に依存する操作の作用に影響します。 以下の例を考えてください。

CREATE FUNCTION less_than(a text, b text) RETURNS boolean AS $$
BEGIN
    RETURN a < b;
END;
$$ LANGUAGE plpgsql;

SELECT less_than(text_field_1, text_field_2) FROM table1;
SELECT less_than(text_field_1, text_field_2 COLLATE "C") FROM table1;

第一の使用法においてless_thanは、text_field_1text_field_2の比較のための通常の照合として用いられます。 第二の使用法においては、C照合として用いられます。

さらに、識別された照合は、照合可能なデータ型の全ての局所変数の照合としても仮定されます。 したがって、この関数は下に記述する関数と差異なく作動します。

CREATE FUNCTION less_than(a text, b text) RETURNS boolean AS $$
DECLARE
    local_a text := a;
    local_b text := b;
BEGIN
    RETURN local_a < local_b;
END;
$$ LANGUAGE plpgsql;

照合可能なデータ型のパラメータが存在しない場合、または、それらで共通する照合順序を識別できない場合、パラメータと局所変数は自身のデータ型のデフォルトの照合順序(通常これはデータベースのデフォルトの照合順序ですが、ドメイン型の変数の場合は異なるかもしれません)を使用します。

照合可能なデータ型の局所変数は、宣言内でCOLLATEオプションを含めることにより、別の照合と関連づけることができます。 例を示します。

DECLARE
    local_a text COLLATE "en_US";

このオプションは上記ルールにより、変数に他の方法で付与されるはずであった照合を上書きします。

また当然ながら、強制的に特定の操作において特定の照合順序を使用したい場合、明示的なCOLLATE句を関数内部に記述することができます。 例を示します。

CREATE FUNCTION less_than_c(a text, b text) RETURNS boolean AS $$
BEGIN
    RETURN a < b COLLATE "C";
END;
$$ LANGUAGE plpgsql;

単純な SQL コマンドで起こるように、これはテーブルの列、パラメータ、または式の中の局所変数に関連づけられた照合を上書きします