評価式は、例えばSELECT
コマンドの目的リストとして、INSERT
やUPDATE
の新しい列の値として、もしくはいくつかのコマンドの検索条件として様々な文脈の中で使われます。
評価式の結果は、テーブル式の結果(つまりテーブル)から区別するために、スカラと呼ばれることもあります。
したがって、評価式はスカラ式(またはもっと簡単に式)とも呼ばれます。
式の構文によって、基本的な部分から算術、論理、集合などの演算を使って値の計算を行うことができます。
評価式は下記のうちのいずれかです。
これ以外にも、式として分類されるけれども一般的な構文規約には従わない、いくつかの構成要素があります。
これらは一般的に関数あるいは演算子の意味を持ちます。
第9章の該当部分で説明されています。
例を挙げるとIS NULL
句があります。
4.1.2で既に定数については説明しました。 続く節では残りのオプションについて説明します。
列は、下記のような形式で参照することができます。
correlation
.columnname
correlation
は、テーブル名(スキーマで修飾されている場合もあります)、あるいはFROM
句で定義されたテーブルの別名です。
correlationの名前と区切り用のドットは、もし列名が現在の問い合わせで使われる全てのテーブルを通して一意である場合は省略することができます。
(第7章も参照してください)。
位置パラメータ参照は、外部からSQL文に渡される値を示すために使用されます。 パラメータはSQL関数定義およびプリペアド問い合わせの中で使用されます。 また、クライアントライブラリの中には、SQLコマンド文字列とデータ値を分離して指定できる機能をサポートするものもあります。 この場合、パラメータは行外データ値を参照するために使用されます。 パラメータ参照の形式は以下の通りです。
$number
例えば、関数 dept
の定義が以下のようにされたとします。
CREATE FUNCTION dept(text) RETURNS dept AS $$ SELECT * FROM dept WHERE name = $1 $$ LANGUAGE SQL;
ここで$1
は関数が呼び出される時に最初の関数引数の値を参照します。
式が配列型の値となる場合、配列値の特定要素は以下のように記述することで抽出できます。
expression
[subscript
]
また、隣接する複数の要素(「配列の一部分」)は以下のように記述することで抽出できます。
expression
[lower_subscript
:upper_subscript
]
(ここで大括弧[ ]
は文字通りに記述してください(訳注:これはオプション部分を表す大括弧ではありません)。)
各subscript
はそれ自体が式であり、整数値を生成しなければなりません。
一般的には、配列expression
は括弧で括らなければなりませんが、添字を付けるそのexpressionが単なる列参照や位置パラメータであった場合、その括弧を省略することができます。
また、元の配列が多次元の場合は複数の添字を連結することができます。
以下に例を示します。
mytable.arraycolumn[4] mytable.two_d_column[17][34] $1[10:42] (arrayfunction(a,b))[42]
最後の例では括弧が必要です。 配列の詳細は8.15を参照してください。
式が複合型(行型)の値を生成する場合、行の特定のフィールドは以下のように記述することで抽出できます。
expression
.fieldname
一般的には、行expression
は括弧で括らなければなりません。
しかし、選択元となる式が単なるテーブル参照や位置パラメータの場合、括弧を省略することができます。
以下に例を示します。
mytable.mycolumn $1.somecolumn (rowfunction(a,b)).col3
(したがって、修飾された列参照は実際のところ、単なるこのフィールド選択構文の特殊な場合です。) 重要となる特殊な場合としては、複合型のテーブル列からフィールドを抽出するときです。
(compositecol).somefield (mytable.compositecol).somefield
compositecol
がテーブル名でなく列名であること、または2番目の場合のmytable
がスキーマ名でなくテーブル名であることを示すため丸括弧が要求されます。
.*
を記述することで、複合型の全ての値を問い合わせることが可能です。
(compositecol).*
この表記はコンテキストに依存して異なった振る舞いをします。詳細は8.16.5を参照してください。
演算子の呼び出しには以下の3構文が可能です。
expression operator expression (二項中置演算子) |
operator expression (単項前置演算子) |
expression operator (単項後置演算子) |
ここでoperator
トークンは、4.1.3構文規則に従うもの、もしくはキーワードAND
、OR
、NOT
のいずれか、または以下の形式の修飾された演算子名です。
OPERATOR(
schema
.
operatorname
)
具体的にどんな演算子が存在し、それが単項か二項かどうかは、システムやユーザによってどんな演算子が定義されたかに依存します。 第9章にて、組み込み演算子について説明します。
関数呼び出しの構文は、関数名(スキーマ名で修飾されている場合があります)に続けてその引数を丸括弧で囲んで列挙したものです。
function_name
([expression
[,expression
... ]] )
例えば、以下のものは2の平方根を計算します。
sqrt(2)
組み込み関数の一覧は第9章にあります。 他の関数はユーザが追加できます。
あるユーザが他のユーザを信用しないデータベースで問い合わせを発行する場合には、関数呼び出しを書く時に10.3のセキュリティの事前の対策を守ってください。
引数には名前を任意で付与することができます。詳細は4.3を見て下さい。
複合型の単一引数をとる関数はフィールド選択の構文を使っても呼び出すことができます。
反対にフィールド選択を関数形式で記述することもできます。
つまり、col(table)
やtable.col
のどちらを使っても良いということです。
この動作は標準SQLにはありませんが、PostgreSQLでは、これにより「計算されたフィールド」のエミュレートをする関数の利用が可能になるため、提供しています。
詳しくは8.16.5を参照してください。
集約式は、問い合わせによって選択される行に対して集約関数を適用することを表現します。 集約関数は、例えば入力の合計や平均などのように、複数の入力を単一の出力値にします。 集約式の構文は下記のうちのいずれかです。
aggregate_name
(expression
[ , ... ] [order_by_clause
] ) [ FILTER ( WHEREfilter_clause
) ]aggregate_name
(ALLexpression
[ , ... ] [order_by_clause
] ) [ FILTER ( WHEREfilter_clause
) ]aggregate_name
(DISTINCTexpression
[ , ... ] [order_by_clause
] ) [ FILTER ( WHEREfilter_clause
) ]aggregate_name
( * ) [ FILTER ( WHEREfilter_clause
) ]aggregate_name
( [expression
[ , ... ] ] ) WITHIN GROUP (order_by_clause
) [ FILTER ( WHEREfilter_clause
) ]
ここで、aggregate_name
は事前に定義された集約(スキーマ名で修飾された場合もあります)、expression
はそれ自体に集約式またはウィンドウ関数呼び出しを含まない任意の値評価式です。
省略可能なorder_by_clause
とfilter_clause
は後述します。
集約式の最初の構文は、それぞれの入力行に対して1回ずつ集計を呼び出します。
ALL
はデフォルトなので、2つ目の形式は最初の形式と同じです。
3番目の形式は、入力行の中にある式の、全ての重複しない値(複数式では重複しない値集合)の集約を呼び出します。
4番目の形式はそれぞれの入力行に対して1回ずつ集約を呼び出します。具体的な入力値が指定されていないため、これは一般的にcount(*)
集約関数でのみ役に立ちます。
最後の形式は順序集合集約関数で使われるもので、順序集合集約関数については後述します。
ほとんどの集約関数はNULL入力を無視するため、行内の1つ以上の式がNULLを返す行は破棄されます。 特記されていない限り、すべての組み込み集約がそのような動作になると想定して良いです。
例えば、count(*)
は入力行の合計数を求めます。
count
はNULLを無視しますので、count(f1)
はf1
が非NULLである入力行の数を求めます。
count(distinct f1)
はf1
の重複しない非NULL値の数を求めます。
通常、入力行は順序を指定されずに集計関数に与えられます。
多くの場合では問題になりません。たとえばmin
は入力順序に関係なく同一の結果を返します。
しかし一部の集約関数(array_agg
およびstring_agg
など)は入力行の順序に依存した結果を返します。
こうした集約関数を使用する際は、オプションのorder_by_clause
を使用して必要とする順序を指定できます。
order_by_clause
は、7.5で説明する問い合わせレベルのORDER BY
句と同じ構文を取りますが、その式は常に単なる式であり、出力列名や序数とすることはできません。
以下に例を示します。
SELECT array_agg(a ORDER BY b DESC) FROM table;
複数の引数を取る集約関数を扱う場合、ORDER BY
句はすべての集約引数の後に指定することに注意してください。
例えば、
SELECT string_agg(a, ',' ORDER BY a) FROM table;
であり、
SELECT string_agg(a ORDER BY a, ',') FROM table; -- incorrect
ではありません。
後者は構文的には有効なものですが、2つのORDER BY
キーを持つ単一引数の集約関数の呼び出しを表しています。(2つ目のキーは定数なので役には立ちません。)
order_by_clause
に加えDISTINCT
が指定された場合、すべてのORDER BY
式が集約関数の通常の引数に一致しなければなりません。つまり、DISTINCT
リストに含まれない式でソートすることはできません。
集計関数においてDISTINCT
とORDER BY
の両方を指定できる機能はPostgreSQLの拡張です。
上記のように集約の通常の引数リストにORDER BY
を置くことは、汎用的で統計的な集約への入力行を整列する時に使いますが、その整列は省略可能です。
たいていは集約の計算がその入力行の特定の順序に関してのみ意味を持つために、order_by_clause
が必要な順序集合集約と呼ばれる集約関数の副クラスがあります。
順序集合集約の典型的な例は順位や百分位数の計算を含みます。
順序集合集約では、order_by_clause
は上の構文の最後に示したようにWITHIN GROUP (...)
の中に書かれます。
order_by_clause
の式は、通常の集約の引数のように入力行1行につき一度評価され、order_by_clause
の要求に従って整列され、集約関数に入力引数として渡されます。
(非WITHIN GROUP
order_by_clause
ではない場合はこれとは異なり、集約関数の引数としては扱われません。)
WITHIN GROUP
の前に引数の式があれば、order_by_clause
に書かれた集約引数と区別するために直接引数と呼ばれます。
通常の集約引数とは異なり、直接引数は集約の呼び出しの時に一度だけ評価され、入力行1行に一度ではありません。
これは、変数がGROUP BY
によりグループ化された場合にのみ、その変数を含むことが可能であることを意味します。この制限は直接引数が集約式の中に全くない場合と同じです。
直接引数は、典型的には1度の集約計算で1つの値だけが意味がある百分位数のようなもので使われます。
直接引数のリストは空でも構いません。この場合、(*)
ではなく()
と書いてください。
(PostgreSQLは実際にどちらの綴りも受け付けますが、後者だけが標準SQLに準拠しています。)
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY income) FROM households; percentile_cont ----------------- 50489
これは、テーブルhouseholds
からincome
列の50番目の百分位数、すなわち中央値を得ます。
ここで0.5
は直接引数です。百分位数が行毎に変化する値であったら意味がありません。
FILTER
が指定されていれば、filter_clause
が真と評価した入力行のみがウィンドウ関数に渡されます。それ以外の行は破棄されます。
例えば、
SELECT count(*) AS unfiltered, count(*) FILTER (WHERE i < 5) AS filtered FROM generate_series(1,10) AS s(i); unfiltered | filtered ------------+---------- 10 | 4 (1 row)
定義済みの集約関数は9.20で説明されています。 ユーザは他の集約関数を追加することができます。
集約式は、SELECT
コマンドの結果リストもしくはHAVING
句内でのみ記述することができます。
WHERE
などの他の句では許されません。
これらの句は集計結果が形成される前に論理的に評価されるためです。
集約式が副問い合わせ(4.2.11と9.22を参照)内に現れた場合、通常、集約は副問い合わせの行全体に対して評価されます。
しかし、その集約の引数(と、もしあればfilter_clause
)が上位レベルの変数のみを持つ場合は例外です。
その場合、集約は最も近い外側のレベルに属し、その問い合わせの行全体に対して評価されます。
全体として、その集約式は、その後、その集約を含む副問い合わせでは外部参照となり、その副問い合わせにおける評価に対しては定数として動作します。
結果リストもしくはHAVING
句にのみ現れるという制約は、その集約が属する問い合わせレベルに関連して適用されます。
ウィンドウ関数呼び出しは、問い合わせにより選択された行のある部分に渡って集約のような機能を実現することを表します。
非ウィンドウ集約関数呼び出しと異なり、これは選択された行を1つの行にグループ化することに束縛されず、各行は別途問い合わせ出力に残ります。
しかしウィンドウ関数は、ウィンドウ関数呼び出しのグループ化指定(PARTITION BY
リスト)に従った、現在の行のグループの一部となる行にすべてアクセスできます。
ウィンドウ関数呼び出しの構文は以下のいずれかです。
function_name
([expression
[,expression
... ]]) [ FILTER ( WHEREfilter_clause
) ] OVERwindow_name
function_name
([expression
[,expression
... ]]) [ FILTER ( WHEREfilter_clause
) ] OVER (window_definition
)function_name
( * ) [ FILTER ( WHEREfilter_clause
) ] OVERwindow_name
function_name
( * ) [ FILTER ( WHEREfilter_clause
) ] OVER (window_definition
)
ここで、window_definition
は以下の構文になります。
[existing_window_name
] [ PARTITION BYexpression
[, ...] ] [ ORDER BYexpression
[ ASC | DESC | USINGoperator
] [ NULLS { FIRST | LAST } ] [, ...] ] [frame_clause
]
オプションのframe_clause
は次の中の1つです。
{ RANGE | ROWS }frame_start
{ RANGE | ROWS } BETWEENframe_start
ANDframe_end
ここでframe_start
およびframe_end
は以下のいずれかです。
UNBOUNDED PRECEDINGvalue
PRECEDING CURRENT ROWvalue
FOLLOWING UNBOUNDED FOLLOWING
ここで、expression
はそれ自身ウィンドウ関数呼び出しを含まない任意の値式を表わします。
window_name
は、問い合わせのWINDOW
句で定義された名前付きウィンドウ仕様への参照です。
あるいはまた、完全なwindow_definition
をWINDOW
句で定義された名前付きウィンドウと同じ構文を使って丸括弧の中に書くことができます。
詳細はSELECTマニュアルページを見てください。
OVER wname
はOVER (wname ...)
とは厳密には等価ではないことを指摘しておくのは価値のあることでしょう。
後者はウィンドウ定義をコピーしたり修正したりすることを示唆しており、参照されるウィンドウ仕様がフレーム句を含む場合には拒絶されます。
PARTITION BY
句は問い合わせの行をパーティションに纏め、パーティションはウィンドウ関数により別々に処理されます。
PARTITION BY
は、その式が常に式であって出力列名や番号ではないという点を除いて、問い合わせレベルのGROUP BY
句と同様に動作します。
PARTITION BY
がなければ、問い合わせで生じる行すべてが一つのパーティションとして扱われます。
ORDER BY
句はパーティションの行がウィンドウ関数により処理される順序を決定します。
問い合わせレベルのORDER BY
句と同様に動作しますが、やはり出力列名や番号は使えません。
ORDER BY
がなければ、行は不定の順序で処理されます。
frame_clause
は、パーティション全体ではなくフレーム上で作動するウィンドウ関数に対して、ウィンドウフレームを構成する行の集合を指定します。
ウィンドウフレームは現在のパーティションの部分集合になります。
フレームはRANGE
モードでもROWS
モードでも指定できます。
どちらの場合でもframe_start
からframe_end
までです。
frame_end
を省略した場合のデフォルトはCURRENT ROW
です。
frame_start
がUNBOUNDED PRECEDING
であるのはフレームがパーティションの最初の行から始まること意味し、同様に、frame_end
がUNBOUNDED FOLLOWING
であるのはフレームがパーティションの最後の行で終わること意味します。
RANGE
モードでは、frame_start
がCURRENT ROW
であるのは、フレームが現在行の最初のピア行(ORDER BY
が現在行と同じ順序とみなす行)から始まることを意味し、一方、frame_end
がCURRENT ROW
であるのはフレームが現在行の最後の同等なORDER BY
ピア行で終わることを意味します。
ROWS
モードでは、CURRENT ROW
は単に現在行を意味します。
value
PRECEDING
とvalue
FOLLOWING
の形式は、現在のところROWS
モードでのみ許可されています。
これは、フレームの開始もしくは終了点となる現在行の前や後に、指定数の行があることを意味します。
value
は一切の変数、集計関数、あるいはウィンドウ関数を含まない整数式でなければなりません。
またNULLや負数も許可されませんが、現在行を選択することになる0は指定可能です。
デフォルトのフレーム化オプションはRANGE UNBOUNDED PRECEDING
で、RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
と同じです。
ORDER BY
があると、フレームはパーティションの開始から現在行の最後のORDER BY
ピア行までのすべての行になります。
ORDER BY
が無い場合は、すべての行が現在行のピアとなるので、パーティションのすべての行がウィンドウフレームに含まれます。
制限は、frame_start
をUNBOUNDED FOLLOWING
とすることができない点、frame_end
をUNBOUNDED PRECEDING
とすることができない点、およびframe_end
の選択は上のリストの中でframe_start
の選択より先に記述することができない点です。例えば、RANGE BETWEEN CURRENT ROW AND
は許されません。
value
PRECEDING
FILTER
が指定されていれば、filter_clause
が真と評価した入力行のみがウィンドウ関数に渡されます。それ以外の行は破棄されます。
集約ウィンドウ関数だけがFILTER
句を受け付けます。
組み込みウィンドウ関数は表 9.57に記載されています。その他のウィンドウ関数をユーザが追加することが可能です。 また、全ての組み込み、またはユーザ定義の、汎用または統計集約関数もウィンドウ関数として使用できます。 (順序集合と仮想集合集約は現在のところウィンドウ関数として使用できません。)
*
を使用した構文は、例えばcount(*) OVER (PARTITION BY x ORDER BY y)
のように、パラメータのない集約関数をウィンドウ関数として呼び出すために使用されます。
アスタリスク(*
)は習慣的にウィンドウ固有の関数には使われません。
ウィンドウ固有の関数は、関数引数リストの中でDISTINCT
やORDER BY
が使われることを許可しません。
ウィンドウ関数呼び出しは問い合わせのSELECT
リストとORDER BY
句の中でのみ許可されます。
型キャストは、あるデータ型から他のデータ型への変換を指定します。 PostgreSQLは型キャストに2つの等価な構文を受け付けます。
CAST (expression
AStype
)expression
::type
CAST
構文はSQLに準拠したものです。
::
を使用する構文は、PostgreSQLで伝統的に使用されている方法です。
キャストが既知の型の評価式に適用された場合、それは実行時型変換を表します。 このキャストは、適切な型変換操作が定義されている場合のみ成功します。 4.1.2.7で示すように、これと定数のキャストの使用との微妙な違いに注意してください。 修飾されていない文字列リテラルに対するキャストは、リテラル定数値の初期に割り当てられる型を表します。 ですから、これは(文字列リテラル定数の内容がそのデータ型の入力構文で受け付けられるのであれば)全ての型で成功します。
評価式が生成しなければならない型に曖昧さがない場合(例えばテーブル列への代入時など)、明示的な型キャストは通常は省略することができます。 その場合、システムは自動的に型キャストを適用します。 しかし、自動キャストは、システムカタログに「暗黙的に適用しても問題なし」と示されている場合にのみ実行されます。 その他のキャストは明示的なキャスト構文で呼び出す必要があります。 この制限は、知らないうちに変換が実行されてしまうことを防ぐためのものです。
また、関数のような構文を使用して型キャストを指定することもできます。
typename
(expression
)
しかし、これはその型の名前が関数の名前としても有効な場合にのみ動作します。
例えば、double precision
はこの方式で使用できませんが、同等のfloat8
は使用できます。
また、interval
、time
、timestamp
という名前は、構文が衝突するため、二重引用符で括った場合にのみこの方式で使用できます。
このように、この関数のようなキャスト構文は一貫性がなくなりがちですので、おそらくアプリケーションでは使用すべきではありません。
この関数のような構文は、実際には単なる関数呼び出しです。 2つの標準的なキャスト構文のうちの1つが実行時変換で使用されると、この構文は登録済みの関数を内部的に呼び出して変換を実行します。 慣習的に、これらの変換関数は自身の出力型と同じ名前を持ち、これにより、「関数のような構文」は背後にある変換用関数を直接呼び出す以上のことを行いません。 移植性を持つアプリケーションが依存すべきものでないことは明確です。 詳細についてはCREATE CASTを参照してください。
COLLATE
句は式の照合順序規則を上書きします。
適用するため次の様に式の後に追記します。
expr
COLLATEcollation
ここでcollation
は識別子で、スキーマ修飾可能です。
COLLATE
句は演算子よりも結合優先度が高いです。
必要に応じて括弧で囲うことができます。
もし照合順序が何も指定されなければ、データベースシステムは式にある列から照合順序を取得します。もし列に関する照合順序が式になければ、データベースのデフォルトの照合順序を使います。
COLLATE
句の主な使われ方が2つあります。
1つはORDER BY
句での並び替え順序を上書きをするもので、例えば次のようにします。
SELECT a, b, c FROM tbl WHERE ... ORDER BY a COLLATE "C";
もう一つは、計算結果がロケールに依存する関数や演算子の呼び出しについて、照合順序を上書きするもので、例えば次のようにします。
SELECT * FROM tbl WHERE a > 'foo' COLLATE "C";
とします。後者の場合、COLLATE
句が、処理対象と想定している入力演算子の引数に対して付与されることに注意してください。演算子や関数の呼び出しのどの引数に対してCOLLATE
句が付与されるかは問題ではありません。演算子や関数により適用される照合順序は対象となる全ての引数を考慮して引き出され、そして明示的に指定されたCOLLATE
句がその他の全ての引数に対しての照合順序を上書きするからです。(しかし、複数の引数に対して一致しないCOLLATE
句の付与はエラーとなります。詳細は23.2を参照してください)。このため、前述の例と同じ結果を次の様にして取得することができます。
SELECT * FROM tbl WHERE a COLLATE "C" > 'foo';
ただし、次の例はエラーになります。
SELECT * FROM tbl WHERE (a > 'foo') COLLATE "C";
>
演算子の結果に対して照合順序を適用しようとしますが、>
演算子は照合不可能なデータ型であるboolean
となるからです。
スカラ副問い合わせは、正確に1行1列を返す、括弧内の通常のSELECT
問い合わせです
(問い合わせの記述方法については第7章を参照してください)。
そのSELECT
問い合わせは実行され、返される単一の値はその値の前後の評価式で使用されます。
1行を超える行や1列を超える列がスカラ副問い合わせ用の問い合わせとして使用された場合はエラーになります
(しかし、ある実行時に、副問い合わせが行を返さない場合はエラーとはなりません。
そのスカラ結果はNULLとして扱われます)。
副問い合わせは、その周りの問い合わせ内の値を参照することができます。
その値は副問い合わせの評価時には定数として扱われます。
副問い合わせに関する他の式については9.22も参照してください。
例えば、以下は各州の最大都市の人口を検索します。
SELECT name, (SELECT max(pop) FROM cities WHERE cities.state = states.name) FROM states;
配列コンストラクタは、メンバー要素に対する値を用いて配列値を構築する式です。
単純な配列コンストラクタの構成は、ARRAY
キーワード、左大括弧[
、(カンマで区切った)配列要素値用の式のリストで、最後に右大括弧]
です。
以下に例を示します。
SELECT ARRAY[1,2,3+4]; array --------- {1,2,7} (1 row)
デフォルトで配列要素型は、メンバ式の型と同じで、UNION
やCASE
構文と同じ規則を使用して決定されます
(10.5を参照してください)。これを明示的に配列コンストラクタを希望する型にキャストすることで書き換えることができます。例をあげます。
SELECT ARRAY[1,2,22.7]::integer[]; array ---------- {1,2,23} (1 row)
これはそれぞれの式を配列要素の型に個別にキャストするのと同じ効果があります。 キャストについてより多くは4.2.9を参照してください。
多次元配列値は、配列コンストラクタを入れ子にすることで構築できます。
内側のコンストラクタではARRAY
キーワードは省略可能です。
例えば、以下は同じ結果になります。
SELECT ARRAY[ARRAY[1,2], ARRAY[3,4]]; array --------------- {{1,2},{3,4}} (1 row) SELECT ARRAY[[1,2],[3,4]]; array --------------- {{1,2},{3,4}} (1 row)
多次元配列は長方形配列でなければなりませんので、同一レベルの内部コンストラクタは同一次元の副配列を生成しなければなりません。外部ARRAY
コンストラクタに適用される全てのキャストは自動的に全ての内部コンストラクタに伝播します。
多次元配列コンストラクタの要素は、副ARRAY
構文だけでなく、適切な種類の配列を生成するものをとることができます。
以下に例を示します。
CREATE TABLE arr(f1 int[], f2 int[]); INSERT INTO arr VALUES (ARRAY[[1,2],[3,4]], ARRAY[[5,6],[7,8]]); SELECT ARRAY[f1, f2, '{{9,10},{11,12}}'::int[]] FROM arr; array ------------------------------------------------ {{{1,2},{3,4}},{{5,6},{7,8}},{{9,10},{11,12}}} (1 row)
空配列を構築できますが、型を所有しない配列を持つことは不可能なので、空配列を望まれる型に明示的にキャストしなければなりません。例をあげます。
SELECT ARRAY[]::integer[]; array ------- {} (1 row)
また、副問い合わせの結果から配列を構成することも可能です。
この形式の場合、配列コンストラクタはARRAY
キーワードの後に括弧(大括弧ではない)で括られた副問い合わせとして記述されます。
以下に例を示します。
SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%'); array ----------------------------------------------------------------------- {2011,1954,1948,1952,1951,1244,1950,2005,1949,1953,2006,31,2412,2413} (1 row) SELECT ARRAY(SELECT ARRAY[i, i*2] FROM generate_series(1,5) AS a(i)); array ---------------------------------- {{1,2},{2,4},{3,6},{4,8},{5,10}} (1 row)
副問い合わせは単一の列を返さなければなりません。 副問い合わせの出力列が非配列型であれば、その結果である一次元配列は、副問い合わせの出力列と一致する型を要素型とした、副問い合わせの結果内の各行を要素として持ちます。 副問い合わせの出力列が配列型であれば、その結果は、同じ型で1つ次元の高い配列になります。この場合、副問い合わせの列はすべて同じ次元の配列とならなければなりません。そうでないと結果が長方形になりません。
ARRAY
で構築された配列値の添字は、常に1から始まります。
配列についての詳細は8.15を参照してください。
行コンストラクタは、そのメンバフィールドに対する値を用いて行値(複合値とも呼ばれます)を構築する式です。
行コンストラクタは、ROW
キーワード、左括弧、行のフィールド値用の0個以上の式(カンマ区切り)、最後に右括弧からなります。
以下に例を示します。
SELECT ROW(1,2.5,'this is a test');
ROW
キーワードは、2つ以上の式がリスト内にある場合は省略することができます。
行コンストラクタにはrowvalue
.*
構文を含めることができます。
これは、SELECT
リストの最上位レベルで.*
構文が使用された時とまったく同様に、行値の要素の列挙に展開されます(8.16.5参照)。
たとえば、テーブルt
がf1
列とf2
列を持つ場合、以下は同一です。
SELECT ROW(t.*, 42) FROM t; SELECT ROW(t.f1, t.f2, 42) FROM t;
PostgreSQL 8.2より前では、.*
構文は行コンストラクタ内では展開されませんでした。
ROW(t.*, 42)
と記述すると、1つ目のフィールドにもう一つの行値を持つ、2つのフィールドからなる行が作成されました。
たいていの場合、新しい動作はより使いやすくなっています。
入れ子状の行値という古い動作が必要であれば、内側の行値には.*
を使用せずに、たとえばROW(t, 42)
と記述してください。
デフォルトでは、ROW
式により作成される値は匿名レコード型になります。
必要に応じて、名前付きの複合型、つまりテーブルの行型あるいはCREATE TYPE AS
で作成された複合型にキャストすることができます。
曖昧性を防止するために明示的なキャストが必要となることもあります。
以下に例を示します。
CREATE TABLE mytable(f1 int, f2 float, f3 text); CREATE FUNCTION getf1(mytable) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL; -- getf1()が1つしか存在しないためキャスト不要。 SELECT getf1(ROW(1,2.5,'this is a test')); getf1 ------- 1 (1 row) CREATE TYPE myrowtype AS (f1 int, f2 text, f3 numeric); CREATE FUNCTION getf1(myrowtype) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL; -- ここでは、どの関数を呼び出すのかを示すためにキャストが必要。 SELECT getf1(ROW(1,2.5,'this is a test')); ERROR: function getf1(record) is not unique SELECT getf1(ROW(1,2.5,'this is a test')::mytable); getf1 ------- 1 (1 row) SELECT getf1(CAST(ROW(11,'this is a test',2.5) AS myrowtype)); getf1 ------- 11 (1 row)
行コンストラクタは、複合型のテーブル列に格納する複合型の値を構築するため、あるいは複合型のパラメータを受け付ける関数に渡すために使用することができます。
また、以下の例のように、2つの行値を比較することも、IS NULL
もしくはIS NOT NULL
で行を検査することも可能です。
SELECT ROW(1,2.5,'this is a test') = ROW(1, 3, 'not the same'); SELECT ROW(table.*) IS NULL FROM table; -- すべてがNULLの行を検出します。
詳細は9.23を参照してください。 行コンストラクタは、9.22で説明するように、副問い合わせと一緒に使用することもできます。
副式の評価の順序は定義されていません。 特に演算子や関数の入力は、必ずしも左から右などの決まった順序で評価されるわけではありません。
さらに、その式の一部を評価しただけで式の結果を決定できる場合には、他の副式がまったく評価されないこともあります。 例えば、
SELECT true OR somefunc();
では、(おそらく)somefunc()
は呼び出されないでしょう。
以下の場合も同様です。
SELECT somefunc() OR true;
これは一部のプログラミング言語に見られる、ブーリアン演算子での左から右への「短絡評価」とは異なることに注意してください。
そのため、副次作用がある関数を複雑な式の一部として使用することは推奨されません。
特に、WHERE
句およびHAVING
句で副次作用や評価順に依存するのは危険です。
これらの句は、実行計画を作成する過程で頻繁に再処理されるからです。
これらの句のブール式(AND
/OR
/NOT
の組み合わせ)は、ブール代数の規則で許されるあらゆる方式で再編成される可能性があります。
評価の順序を強制することが重要であれば、CASE
構文(9.17を参照)を使用できます。
例えば、次の式はWHERE
句で0除算を避ける方法としては信頼性の低いものです。
SELECT ... WHERE x > 0 AND y/x > 1.5;
しかし、次のようにすれば安全です。
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
このような方法で使用されるCASE
構文は最適化を妨げるものなので、必要な場合にのみ使用してください。
(特に、この例では、y > 1.5*x
と代わりに記述することが問題を回避するより優れた方法です。)
しかしながら、CASE
はそのような問題に対する万能薬ではありません。
上で示したような方法の限界の1つは、定数副式が早く評価されるのを防げないことです。
37.6に記すように、IMMUTABLE
と印をつけられた関数と演算子は、実行される時ではなく問い合わせが計画される時に評価されるかもしれません。
そのため、例えば
SELECT CASE WHEN x > 0 THEN x ELSE 1/0 END FROM tab;
は、たとえテーブルのすべての行がx > 0
であり、実行時にはELSE
節に決して入らないとしても、プランナが定数副式を単純化しようとするためにゼロによる除算での失敗という結果に終わるでしょう。
この特別な例は馬鹿げたものに見えるかもしれませんが、定数を含むことが明らかではない関連する場合が関数の中で実行される問い合わせで起こり得ます。関数の引数とローカル変数は計画する目的のために定数として問い合わせに入れられることがあるからです。
例えば、PL/pgSQL関数の中では、IF
-THEN
-ELSE
文を使って危険な計算を保護する方がCASE
式の中で入れ子にするよりもずっと安全です。
同種の別の限界は、その中に含まれる集約式の評価をCASE
が防げないことです。なぜなら、SELECT
リストやHAVING
句の別の式が考慮される前に、集約式が計算されるからです。
例えば、以下の問い合わせは対策を施しているように見えるにも関わらずゼロ除算エラーになり得ます。
SELECT CASE WHEN min(employees) > 0 THEN avg(expenses / employees) END FROM departments;
min()
とavg()
集約は入力行すべてに対して同時に計算されますので、もしemployees
がゼロになる行があれば、min()
の結果が検査される機会の前にゼロ除算エラーが起こります。
代わりに、まずは問題のある入力行が集約関数に渡されないようにするためにWHERE
またはFILTER
句を使ってください。