評価式は、例えばSELECTコマンドの目的リストとして、INSERTやUPDATEの新しい列の値として、もしくはいくつかのコマンドの検索条件として様々な文脈の中で使われます。 評価式の結果は、テーブル式の結果(つまりテーブル)から区別するために、スカラと呼ばれることもあります。 したがって、評価式はスカラ式(またはもっと簡単に式)とも呼ばれます。 式の構文によって、算術、論理、集合などの演算を行う基本的な部分で値の計算を行うことができます。
評価式は下記のうちのいずれかです。
定数あるいはリテラル値
列の参照
関数宣言の本体やプリペアド文における、位置パラメータ参照
添字付きの式
フィールド選択式
演算子の呼び出し
関数呼び出し
集約式
ウィンドウ関数呼び出し
型キャスト
スカラ副問い合わせ
配列コンストラクタ
行コンストラクタ
これ以外にも、式として分類されるけれども一般的な構文規約には従わない、いくつかの構成要素があります。 これらは一般的に関数あるいは演算子の意味を持ちます。 第9章の該当部分で説明されています。 例を挙げるとIS NULL句があります。
項4.1.2で既に定数については説明しました。 続く節では残りのオプションについて説明します。
列は、下記のような形式で参照することができます。
correlation.columnname
correlationは、テーブル名(スキーマで修飾されている場合もあります)、FROM句で定義されたテーブルの別名、あるいはNEWまたはOLDというキーワードです (NEWとOLDは書き換えルールでしか使用されませんが、他の相関名は全てのSQL文で使用することができます)。 相関名と区切り用のドットは、もし列名が現在の問い合わせで使われる全てのテーブルを通して一意である場合は省略することができます。 (第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は括弧で括らなければなりませんが、添字が付いた式が単なる列参照や位置パラメータであった場合、その括弧を省略することができます。 以下に例を示します。
mytable.arraycolumn[4] mytable.two_d_column[17][34] $1[10:42] (arrayfunction(a,b))[42]
最後の例では括弧が必要です。 配列の詳細は項8.14を参照してください。
式が複合型(行型)の値を生成する場合、行の特定のフィールドは以下のように記述することで展開できます。
expression.fieldname
一般的には、行expressionは括弧で括らなければなりません。 しかし、選択元となる式が単なるテーブル参照や位置パラメータの場合、括弧を省略することができます。 以下に例を示します。
mytable.mycolumn $1.somecolumn (rowfunction(a,b)).col3
(したがって、修飾された列参照は実際のところ、単なるこのフィールド選択構文の特殊な場合です。)重要となる特殊な場合としては、複合型のテーブル列からフィールドを抽出するときです。
(compositecol).somefield (mytable.compositecol).somefield
compositecolがテーブル名でなく列名であること、または2番目の場合のmytableがスキーマ名でなくテーブル名であることを示すため丸括弧が要求されます。
演算子の呼び出しには以下の3構文が可能です。
expression operator expression (二項中置演算子) |
operator expression (単項前置演算子) |
expression operator (単項後置演算子) |
OPERATOR(schema.operatorname)
具体的にどんな演算子が存在し、それが単項か二項かどうかは、システムやユーザによってどんな演算子が定義されたかに依存します。 第9章にて、組み込み演算子について説明します。
関数呼び出しの構文は、関数名(スキーマ名で修飾されている場合があります)に続けてその引数を丸括弧で囲んで列挙したものです。
function_name ([expression [, expression ... ]] )
例えば、以下のものは2の平方根を計算します。
sqrt(2)
組み込み関数の一覧は第9章にあります。 他の関数はユーザが追加できます。
集約式は、問い合わせによって選択される行に対して集約関数が適用されることを意味します。 集約関数は、例えば入力の合計や平均などのように、複数の入力を単一の出力値にします。 集約式の構文は下記のうちのいずれかです。
aggregate_name (expression [ , ... ] ) aggregate_name (ALL expression [ , ... ] ) aggregate_name (DISTINCT expression ) aggregate_name ( * )
ここで、aggregate_nameは事前に定義された集約(スキーマ名で修飾された場合もあります)で、expressionはそれ自体集約式、またはウィンドウ関数呼び出しを含まないどんな値評価式でも構いません。
集約式の最初の構文は、指定された式が非NULL値を返す入力行全てについての集計を呼び出します
(実際にはNULL値を無視するかどうかは集約関数によって異なりますが、全ての標準的な集約関数では無視します)。
ALLはデフォルトなので、2つ目の形式は最初の形式と同じです。
3番目の形式は、入力行の中にある式の、全ての重複しない、非NULL値の集計を呼び出します。
最後の形式はNULL値か非NULL値かにかかわらず、それぞれの入力行に対して1回ずつ集計を呼び出します。
具体的な入力値が指定されていないため、これは一般的にcount(*)
集約関数でのみ役に立ちます。
例えば、count(*)は入力行の合計数を求めます。 count(f1)はf1が非NULLである入力行の数を求めます。 count(distinct f1)はf1の重複しない非NULL値の数を求めます。
定義済みの集約関数は項9.18で説明されています。 ユーザは他の集約関数を追加することができます。
集約式は、SELECTコマンドの結果リストもしくはHAVING句内でのみ記述することができます。 WHEREなどの他の句では許されません。 これらの句は理論上集計結果が形成される前に評価されるためです。
集約式が副問い合わせ(項4.2.10と項9.20を参照)内に現れた場合、通常集約は副問い合わせの行全体に対して評価します。 しかし、その集約の引数が上位レベルの変数のみを持つ場合は例外です。 その場合、上位問い合わせの行全体に対して評価します。 全体として、その集約式は、その後、その集約を含む副問い合わせでは外部参照となり、その副問い合わせにおける評価に対しては定数として動作します。 結果リストもしくはHAVING句にのみ現れるという制約は、その集約が属する問い合わせレベルに関連して適用されます。
注意: 現在PostgreSQLは、1つ以上の入力式を持つDISTINCTをサポートしません。
ウィンドウ関数呼び出しは、問い合わせにより選択された行のある部分に渡って集約のような機能を実現することを表します。 通常の集約関数呼び出しと異なり、これは選択された行を1つの行にグループ化することに束縛されず、各行は別途問い合わせ出力に残ります。 しかしウィンドウ関数は、ウィンドウ関数呼び出しのグループ化指定(PARTITION BYリスト)に従った、現在の行のグループの一部となる行をすべてスキャンすることができます。 ウィンドウ関数呼び出しの構文は以下のいずれかです。
function_name ([expression [, expression ... ]]) OVER ( window_definition ) function_name ([expression [, expression ... ]]) OVER window_name function_name ( * ) OVER ( window_definition ) function_name ( * ) OVER window_name
ここで、window_definitionは以下の構文になります。
[ existing_window_name ] [ PARTITION BY expression [, ...] ] [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ] [ frame_clause ]
オプションのframe_clauseは次の中の1つです。
RANGE UNBOUNDED PRECEDING RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ROWS UNBOUNDED PRECEDING ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
ここで、expressionはそれ自身ウィンドウ関数呼び出しを含まないいかなる値式を表わします。 PARTITION BYおよびORDER BYリストは基本的に、それらの式は常に式であって、出力列名または数値でない限り、問い合わせ全体のGROUP BYとORDER BYと同じ構文と動作です。 window_nameは、問い合わせのWINDOW句で定義された名前付きwindow仕様への参照です。名前付きwindow仕様は通常単にOVER window_nameで参照されますが、同時に丸括弧の内部にウィンドウ名を書くことができ、そして順序付け句とフレーム句の両方またはいずれか一方を供給します(ここでこれらの句が供給されると、参照されたwindowにこれらの句があってはなりません)。後者の構文はWINDOW句の中の既存のウィンドウ名を変更するのと同じ規則に従います(詳細はSELECT参照ページを見てください)。
frame_clauseは、全てのパーティションの替わりにフレーム上で作動するウィンドウ関数に対して、window frameを構成する行の集合を指定します。デフォルトのフレーム化オプションはRANGE UNBOUNDED PRECEDINGで、RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWと同じです。それはORDER BYによる順序付けの中での現在行の最後の同等な行まで行を選択します。オプションの、RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGとROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGも同様に同等です。 これらは常にパーティションの全ての行を選択します。最後に、ROWS UNBOUNDED PRECEDINGまたはそれと冗長な等価なROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWは、(重複に関わらず)現在行までの行全てを選択します。このオプションはORDER BY順序付けが一意的に行を順序付けしないとき、実装に依存した結果をもたらす可能性があることに注意してください。
組み込みウィンドウ関数は表9-44に記載されています。その他のウィンドウ関数をユーザが追加することが可能です。また、全ての組み込み、またはユーザ定義の集約関数もウィンドウ関数として使用できます。
*を使用した構文は、例えばcount(*) OVER (PARTITION BY x ORDER BY y)のように、ウィンドウ関数として呼び出しパラメータのない集約関数を使用します。*は習慣的に非集約ウィンドウ関数には使われません。通常の集約関数と異なり、集約ウィンドウ関数は、関数引数リストの中でDISTINCTが使われることを許可しません。
ウィンドウ関数呼び出しは問い合わせのSELECTリストとORDER BY句の中でのみ許可されます。
型キャストは、あるデータ型から他のデータ型への変換を指定します。 PostgreSQLは型キャストに2つの等価な構文を受け付けます。
CAST ( expression AS type ) expression::type
CAST構文はSQLに準拠したものです。 ::を使用する構文は、PostgreSQLで伝統的に使用されている方法です。
キャストが既知の型の評価式に適用された場合、それは実行時型変換を表します。 このキャストは、適切な型変換操作が定義されている場合のみ成功します。 項4.1.2.7で示すように、これと定数のキャストの使用との微妙な違いに注意してください。 修飾されていない文字列リテラルに対するキャストは、リテラル定数値の初期に割り当てられる型を表します。 ですから、これは(文字列リテラル定数の内容がそのデータ型の入力構文で受け付けられるのであれば)全ての型で成功します。
通常(例えばテーブル列への代入時など)、評価式が生成しなければならない型に曖昧さがない場合、明示的な型キャストは省略することができます。 その場合、システムは自動的に型キャストを適用します。 しかし、自動キャストは、システムカタログに"暗黙的に適用しても問題なし"と示されている場合にのみ実行されます。 その他のキャストは明示的なキャスト構文で呼び出す必要があります。 この制限は、知らないうちに変換が実行されてしまうことを防ぐためのものです。
また、関数のような構文を使用して型キャストを指定することもできます。
typename ( expression )
しかし、これはその型の名前が関数の名前としても有効な場合にのみ動作します。 例えば、double precision はこの方式で使用できませんが、同等のfloat8は使用できます。 また、interval、time、timestampという名前は、構文が衝突するため、二重引用符で括った場合にのみこの方式で使用できます。 このように、この関数のようなキャスト構文は一貫性がなくなりがちですので、おそらくアプリケーションでは使用すべきではありません
注意: この関数のような構文は、実際には単なる関数呼び出しです。 2つの標準的なキャスト構文のうちの1つが実行時変換で使用されると、この構文は登録済みの関数を内部的に呼び出して変換を実行します。 慣習的に、これらの変換関数は自身の出力型と同じ名前を持ち、これにより、"関数のような構文"は背後にある変換用関数を直接呼び出す以上のことを行いません。 移植性を持つアプリケーションが依存すべきものでないことは明確です。 詳細についてはCREATE CASTを参照してください。
スカラ副問い合わせは、正確に1行1列を返す、括弧内の通常のSELECT問い合わせです (問い合わせの記述方法については第7章を参照してください)。 そのSELECT問い合わせは実行され、返される単一の値はその値の前後の評価式で使用されます。 1行を超える行や1列を超える列がスカラ副問い合わせ用の問い合わせとして使用された場合はエラーになります (しかし、ある実行時に、副問い合わせが行を返さない場合はエラーとはなりません。 そのスカラ結果はNULLとして扱われます)。 副問い合わせは、その周りの問い合わせ内の値を参照することができます。 その値は副問い合わせの評価時には定数として扱われます。 副問い合わせに関する他の式については項9.20も参照してください。
例えば、以下は各州で最も人口の多い都市を検索します。
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%'); ?column? ------------------------------------------------------------- {2011,1954,1948,1952,1951,1244,1950,2005,1949,1953,2006,31} (1 row)
副問い合わせは単一の列を返さなければなりません。 その結果である一次元配列は、副問い合わせの出力列と一致する型を要素型とした、副問い合わせの結果内の各行を要素として持ちます。
ARRAYで構築された配列値の添字は、常に1から始まります。 配列についての詳細は項8.14を参照してください。
行コンストラクタは、そのメンバフィールドに対する値を用いて行値(複合値とも呼ばれます)を構築する式です。 行コンストラクタは、ROWキーワード、左括弧、行のフィールド値用の0個以上の式(カンマ区切り)、最後に右括弧からなります。 以下に例を示します。
SELECT ROW(1,2.5,'this is a test');
ROWキーワードは、1つ以上の式がリスト内にある場合は省略することができます。
行コンストラクタにはrowvalue.*構文を含めることができます。 これは、SELECTリストの最上位レベルで.*構文が使用された時とまったく同様に、行値の要素の列挙に展開されます。 たとえば、テーブル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.21を参照してください。 行コンストラクタは、項9.20で説明した、副問い合わせと一緒に使用することもできます。
副式の評価の順序は定義されていません。 特に演算子や関数の入力は、必ずしも左から右などの決まった順序で評価されるわけではありません。
さらに、その式の一部を評価しただけで式の結果を判断できる場合には、他の副式がまったく評価されないこともあります。 例えば、
SELECT true OR somefunc();
では、(おそらく)somefunc()は呼び出されないでしょう。 以下の場合も同様です。
SELECT somefunc() OR true;
これは一部のプログラミング言語に見られる、ブーリアン演算子での左から右への"ショートサーキット"とは異なることに注意してください。
そのため、副次作用がある関数を複雑な式の一部として使用することは推奨されません。 特に、WHERE句およびHAVING句で副次作用や評価順に依存するのは危険です。 これらの句は、実行計画を作成する過程で頻繁に再処理されるからです。 これらの句のブール式(AND/OR/NOTの組み合わせ)は、ブール代数の規則で許されるあらゆる方式で再編成することができます。
評価の順序を強制することが重要であれば、CASE構文(項9.16を参照)を使用できます。 例えば、次の式は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と代わりに記述することが問題を回避するより優れた方法です。)。