複合型は、行もしくはレコードの構造を表現します。 本質的には、これは単なるフィールド名とそのデータ型のリストです。 PostgreSQLでは、単純な型において使用される方法と多くは同じ方法で複合型を使用できます。 例えば、テーブルの列は複合型の型のものとして宣言することができます。
複合型の宣言の例を以下に2つ示します。
CREATE TYPE complex AS ( r double precision, i double precision ); CREATE TYPE inventory_item AS ( name text, supplier_id integer, price numeric );
この構文は、フィールド名とその型のみを指定できるという点を除き、CREATE TABLE
と同等です。
現在は、制約(NOT NULL
など)を含めることはできません。
AS
キーワードが重要であることに注意してください。
これがないと、システムはCREATE TYPE
の意味を異なって解釈し、おかしな構文エラーを引き起こします。
定義済みの型を使用して、以下のようにテーブルや関数を生成することができます。
CREATE TABLE on_hand ( item inventory_item, count integer ); INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);
また、関数においては以下のように利用できます。
CREATE FUNCTION price_extension(inventory_item, integer) RETURNS numeric AS 'SELECT $1.price * $2' LANGUAGE SQL; SELECT price_extension(item, 10) FROM on_hand;
テーブルを生成する時には、テーブルの行型を表現するために、テーブル名と同じ名前の複合型も自動的に生成されます。 例えば、以下のように
CREATE TABLE inventory_item ( name text, supplier_id integer REFERENCES suppliers, price numeric CHECK (price > 0) );
テーブルを作成すると、上述のものと同じinventory_item
という複合型が副次的に作成され、同様に使用することができるようになります。
しかし、現在の実装には、次のような重要な制限があることに注意してください。
複合型には制約が関連付けられませんので、テーブル定義に含まれる制約は、テーブルの外部に作成される複合型には適用されません。
(これを回避するためには、複合型を含むドメインを作成し、ドメインのCHECK
制約として望みの制約を適用します。)
複合型をリテラル定数として記述するには、フィールド値をカンマで区切り、それらを括弧で括ります。 フィールド値を二重引用符で括ることができ、また、値にカンマや括弧を含む場合は二重引用符で括らなければなりません (より詳細については後で説明します)。 したがって、複合型の定数の一般的な書式は以下のようになります。
'(val1
,val2
, ... )'
以下に例を示します。
'("fuzzy dice",42,1.99)'
これは、上述のinventory_item
型の値として有効なものです。
フィールドをNULLにするには、リスト中の該当位置を空にします。
例えば、以下の定数は3番目のフィールドにNULLを指定しています。
'("fuzzy dice",42,)'
NULLではなく空文字列にしたいのであれば、以下のように引用符を二重に記述します。
'("",42,)'
これにより、最初のフィールドは非NULLの空文字列に、3番目のフィールドはNULLになります。
(実際には、こうした定数は4.1.2.7で説明した、一般的な型の定数の特殊な場合に過ぎません。 定数はまず、文字列として扱われ、複合型の入力変換処理に渡されます。 定数をどの型に変換するかを示すため、明示的な型指定が必要になることもあります。)
また、ROW
式構文も、複合値を生成する際に使用することができます。
複数の階層に渡る引用符について考慮する必要がないため、おそらくほとんどの場合、これは文字列リテラル構文よりも簡単に使用できます。
上記において、既にこの方法を使用しています。
ROW('fuzzy dice', 42, 1.99) ROW('', 42, NULL)
式の中に2つ以上のフィールドがある場合には、ROWキーワードは実際には省略することができます。 ですので、以下のように簡略化することができます。
('fuzzy dice', 42, 1.99) ('', 42, NULL)
ROW
構文については4.2.13でより詳細に説明します。
複合型の列のフィールドにアクセスするには、テーブル名からフィールドを選択する場合とほぼ同様に、ドットとフィールド名を記述します。
実際、テーブル名からの選択とかなり似ていますので、パーサを混乱させないように括弧を使用しなければならないことがしばしばあります。
例えば、on_hand
というテーブルの例からサブフィールドを選択しようとした場合、以下のように書くかもしれません。
SELECT item.name FROM on_hand WHERE item.price > 9.99;
これは、SQLの構文規則に従ってitem
がon_hand
の列名ではなくテーブル名として解釈されるため、動作しません。
以下のように記述しなければなりません。
SELECT (item).name FROM on_hand WHERE (item).price > 9.99;
また、テーブル名も使用しなければならない場合(例えば複数テーブルに対する問い合わせ)、以下のようになります。
SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;
これで、括弧で括られたオブジェクトは正しくitem
列への参照として解釈され、サブフィールドはそこから選択できるようになります。
似たような構文上の問題は、複合型からフィールドを選択する時、常に発生します。 例えば、複合型の値を返す関数の結果から1つだけフィールドを選択する場合、以下のように記述しなければなりません。
SELECT (my_func(...)).field FROM ...
追加の括弧がないと、これは構文エラーを生成します。
8.16.5でより詳細に説明する通り、*
という特別なフィールド名は「すべてのフィールド」を意味します。
複合型の列への挿入と更新についての適切な構文の例をいくつか示します。 まず、列全体を挿入、更新する例です。
INSERT INTO mytab (complex_col) VALUES((1.1,2.2)); UPDATE mytab SET complex_col = ROW(1.1,2.2) WHERE ...;
最初の例ではROW
を省略し、2番目の例ではROW
を使用しています。
どちらの方法でも行うことができます。
以下のようにして、複合型の列の個々のサブフィールドを更新することができます。
UPDATE mytab SET complex_col.r = (complex_col).r + 1 WHERE ...;
ここで、SET
直後の列名の周りに括弧を記述する必要がないこと(実際には記述できないこと)、しかし、等号の右で同じ列を参照する場合には括弧が必要なことに注意してください。
また、INSERT
の対象としてサブフィールドを指定することもできます。
INSERT INTO mytab (complex_col.r, complex_col.i) VALUES(1.1, 2.2);
列のサブフィールド全ての値を与えていなければ、残りのサブフィールドはNULL値になります。
問い合わせ内での複合型に関連して様々な特別な構文規則や動作があります。 これらの規則により便利なショートカットが提供されますが、その背後にある論理を知らないと混乱を招くかもしれません。
PostgreSQLでは、問い合わせでのテーブル名(または別名)の参照は、実質的にはテーブルの現在行の複合型の値への参照と同じになります。
例えば、前に示したinventory_item
というテーブルがあるとして、次のように記述することができます。
SELECT c FROM inventory_item c;
この問い合わせは単一の複合型の値の列を生成するので、出力は以下のようになります。
c ------------------------ ("fuzzy dice",42,1.99) (1 row)
ただし、単純な名前はテーブル名より先に列名に対してマッチさせられるので、この例は問い合わせのテーブルにc
という名前の列がないから動作したに過ぎないことに注意してください。
通常のtable_name
.
column_name
という列名修飾の構文は、フィールド選択をテーブルの現在行の複合型の値に対して適用していると考えることもできます。
(効率の問題から、実際にはそのような実装にはなっていません。)
SELECT c.* FROM inventory_item c;
上記のSQLについて、標準SQLではテーブルの内容が別々の列に展開されて、次のような結果になることを定めています。
name | supplier_id | price ------------+-------------+------- fuzzy dice | 42 | 1.99 (1 row)
つまりこれは、問い合わせが以下であったかのように動作するということです。
SELECT c.name, c.supplier_id, c.price FROM inventory_item c;
PostgreSQLでは、この展開の動作をすべての複合型の値の式に適用します。
ただし、前に説明したように、.*
をつける値が単純なテーブル名でないときは、必ずそれを括弧で括る必要があります。
例えば、myfunc()
が列a
、b
、c
からなる複合型を返す関数だとすると、次の2つの問い合わせは同じ結果を返します。
SELECT (myfunc(x)).* FROM some_table; SELECT (myfunc(x)).a, (myfunc(x)).b, (myfunc(x)).c FROM some_table;
PostgreSQLでは、上の1番目の構文を2番目の構文に実際に変換することで列の展開を処理します。
従って、この例ではどちらの構文を使ってもmyfunc()
は各行に対して3回ずつ呼び出されます。
それが高価な関数でそのような事態を避けたいなら、次のような問い合わせにすることもできます。
SELECT m.* FROM some_table, LATERAL myfunc(x) AS m;
LATERAL
FROM
項目の中に関数を置くと、関数は1行につき2度以上は呼び出されません。
m.*
はまだm.a, m.b, m.c
に展開されますが、その変数はFROM
項目の出力の単なる参照です。
(LATERAL
キーワードはここでは省略可能ですが、関数がsome_table
からx
を入手していることを明確にするために書きました。)
composite_value
.*
の構文は、それがSELECT
の出力リスト、INSERT
/UPDATE
/DELETE
のRETURNING
リスト、VALUES
句あるいは行コンストラクタの最上位に記述された場合、この種の列展開がされます。
それ以外の場合(これらの構文の内側に入れ子になっている場合を含みます)は、複合型の値に.*
を付加しても、値は変わりません。
なぜなら、それは「すべての列」を意味するため、同じ複合型の値が繰り返し生成されるからです。
例えば、somefunc()
が複合型の値の引数をとるとして、以下の問い合わせは同じです。
SELECT somefunc(c.*) FROM inventory_item c; SELECT somefunc(c) FROM inventory_item c;
どちらの場合もinventory_item
の現在行が単一の複合型の値の引数として関数に渡されます。
このような場合に.*
は何もしませんが、それをつけることにより、複合型の値であることを意図しているのが明確になるので、つけるのは良い習慣です。
特に、パーサがc.*
のc
を列名ではなくテーブル名あるいは別名を参照するものとみなす一方、.*
がないとc
がテーブル名なのか列名なのか明らかではなく、実際には、c
という名前の列があれば列名としての解釈が優先されてしまいます。
これらの考え方を示す別の例をあげると、以下の3つの問い合わせは同じ意味になります。
SELECT * FROM inventory_item c ORDER BY c; SELECT * FROM inventory_item c ORDER BY c.*; SELECT * FROM inventory_item c ORDER BY ROW(c.*);
これらのORDER BY
句はすべて行の複合型の値を指定しており、9.24.6で説明される規則に従って行を並べ替えた結果になります。
ただし、inventory_item
にc
という名前の列がある場合は、最初の例はその列によってのみ並べ替えられるので、他の2つとは異なるものになります。
以前に示したのと同じ列名であるとしたら、以下の問い合わせも上記のものと同じになります。
SELECT * FROM inventory_item c ORDER BY ROW(c.name, c.supplier_id, c.price); SELECT * FROM inventory_item c ORDER BY (c.name, c.supplier_id, c.price);
(最後の例はキーワードROW
を省略した行コンストラクタを使用しています。)
複合型の値に関連したもう一つの特別な構文的動作は、複合型の値のフィールドを取り出す時に関数的記法を使用できることです。
これを簡単に説明するなら、
という記法とfield
(table
)
という記法は相互に交換可能です。
例えば、以下の問い合わせは同等です。
table
.field
SELECT c.name FROM inventory_item c WHERE c.price > 1000; SELECT name(c) FROM inventory_item c WHERE price(c) > 1000;
さらに、複合型の引数を1つだけとる関数があるとして、それをどちらの記法でも呼び出すことができます。 以下の問い合わせはすべて同等です。
SELECT somefunc(c) FROM inventory_item c; SELECT somefunc(c.*) FROM inventory_item c; SELECT c.somefunc FROM inventory_item c;
この関数的記法とフィールド記法の同等性により、複合型に対する関数を使用して「計算されたフィールド」を実装することができます。
上の最後の問い合わせを使用するアプリケーションは、somefunc
がテーブルの真の列ではないことを直接には意識する必要がありません。
このような動作になるため、複合型の引数を一つだけとる関数に、その複合型に含まれるフィールドと同じ名前をつけることは賢明ではありません。
曖昧なときには、フィールド名の構文が使われていれば、フィールド名の解釈が選ばれ、関数呼び出しの構文が使われていれば、関数が選ばれます。
しかしながら、11より前のPostgreSQLのバージョンでは、呼び出し構文が関数呼び出しとしてしか扱えない場合を除いて、常にフィールド名の解釈を選んでいました。
関数としての解釈を強制する一つの方法は、関数名をスキーマ修飾する、つまり
とすることです。
schema
.func
(compositevalue
)
複合型の外部テキスト表現は、個々のフィールド用のI/O変換規則に従って解釈される項目群と、複合構造を意味する修飾から構成されます。
この修飾は、値全体を括る括弧((
および)
)と隣接した項目間のカンマ(,
)で構成されます。
括弧の外側の空白文字は無視されますが、括弧の内部ではフィールド値の一部とみなされます。
ただし、空白に意味があるかないかについては、そのフィールドのデータ型用の入力変換規則に従います。
例えば、
'( 42)'
括弧内の空白文字は、そのフィールド型が整数の場合は無視されますが、テキストの場合は無視されません。
前述の通り、複合型の値を記述する時には、個々のフィールド値を二重引用符で括ることができます。 もし、フィールド値が複合型値用のパーサを混乱させる場合には、これは必須です。 具体的には、括弧、カンマ、二重引用符、バックスラッシュを含むフィールドの場合、二重引用符で括る必要があります。 引用符で括った複合型のフィールド値内に二重引用符やバックスラッシュが存在する場合、その前にバックスラッシュを付けてください (また、引用符で括った複合型のフィールド値内に二重の引用符の組み合わせがあると、これは二重引用符を表す文字として解釈されます。 これは、SQLリテラル文字列内の単一引用符の規則と同じです)。 そのままでは複合型に対する構文として解釈されてしまう、全てのデータ文字を保護する他の方法として、引用符付けをせずにバックスラッシュによるエスケープを使用することができます。
完全な空フィールド値(カンマや括弧の間にまったく文字がないもの)はNULLを表します。
NULLではなく空文字列を値として記述するには ""
と記述してください。
複合型の出力処理では、もしフィールド値が空文字列の場合や括弧、カンマ、二重引用符、バックスラッシュ、空白文字を含む場合には、そのフィールド値を二重引用符で括って出力します (空白文字に対するこの処理は重要ではありませんが、可読性を高めます)。 フィールド値内に埋め込まれた二重引用符やバックスラッシュは二重化されます。
SQLコマンド内部に記述したものは、まず文字列リテラルとして、その後、複合型として解釈されることを覚えておいてください。
これは必要なバックスラッシュの数を倍にします(エスケープ文字列構文が使用されることを仮定しています)。
例えば、複合型の値の中に二重引用符とバックスラッシュを持つtext
フィールドに挿入するには、以下のように書かなければなりません。
INSERT ... VALUES ('("\"\\")');
文字列リテラルプロセッサが第1レベルのバックスラッシュを取り除くため、複合型値のパーサに渡されるものは ("\"\\")
のようになります。
そして、text
データ型の入力関数に渡される文字列は"\
になります
(もし、例えばbytea
といった、その入力関数もバックスラッシュを特別に扱うデータ型を扱っている場合、1つのバックスラッシュを複合型のフィールドに格納するためにコマンド内に8個ものバックスラッシュが必要になります)。
ドル引用符付け(4.1.2.4を参照)を使用して、このバックスラッシュの二重化を防ぐことができます。
SQLコマンド内に複合型の値を書く時、通常、ROW
生成構文の方が複合型のリテラル構文より作業が簡単です。
ROW
による記述では、複合型のメンバ以外の記述方法と同じ方法で個々のフィールド値を記述することができます。