PostgreSQL数据库Dig9

数值表达式（Value Expressions）

数值表达式被用在各种上下文中，如SELECT命令的目标列表中、作为INSERT 或者 UPDATE命令的新列、或者在一串搜索条件中。数值表达式的结果一般被称作标量，区别于表的表达式。数值表达式因此也被称作标量表达式(或者称作简洁表达式)。表达式的语法允许计算原始部分（使用算术、逻辑、集合或者其他操作）的值。 数值表达式时以下形式中的一种：

常量或者字面值（A constant or literal value）列参考（A column reference）函数体内或者意愿表达式的位置参数参考（）A positional parameter reference, in the body of a function definition or prepared statement下标表达式（A subscripted expression）字段选择表达式（A field selection expression）操作符调用（An operator invocation）函数调用（A function call）统计表达式（An aggregate expression）窗口函数调用（A window function call）类型转换（A type cast）集合表达式（A collation expression）标量子查询（A scalar subquery）数组构造器（An array constructor）行构造器（A row constructor）圆括号里的另一个数值表达式（组的子查询和重写运算优先级的用法）（Another value expression in parentheses (used to group subexpressions and override precedence)）

除了以上列出，还有很多的构造体可以被归为数值表达式（但不遵循任何的语法规则）。这些构造体通常有函数或者操作符语义（第九章有描述）。 一个简单的例子就是 IS NULL 定语.

1. 列参考/引用（Column References）

列可以通过以下形式被参考引用：

correlation . columnname

关联性是表名(也可能是指定的模式名), 或者是通过FROM定语定义的表的别名。如果列名唯一那么在查询的所有表中关联名称和分隔点会被忽略(Chapter 7查看更多)

2. 位置参数（Positional Parameters）

位置参数参考引用用来表明一个SQL表达的外部提供。参数被用在SQL函数定义和意愿查询，部分客户端库也支持将明确的数据值与SQL命令串分开，这种情况下参数通常参考行外数据值。 参数参考的格式如下：

$ number

例如，考虑以下函数定义 dept

CREATE FUNCTION dept(text) RETURNS dept AS $$ SELECT * FROM dept WHERE name = $1 $$ LANGUAGE SQL;

在这里当函数启用的时候$1参考函数的第一个参数。

3. 下标（Subscripts）

如果一个表达式产生了数组类型的值，那么数组中的某个元素就可以通过 表达式[ 下标 ]来获得 ，或者通过下面的形式截取多个元素序列：

表达式 [ 小的下标 : 大的下标 ]

(在这里，中括号[ ] 是一种格式)每一个下标自成表达式，下标必须是数组值域内的整数。 通常数组表达式需要被括号包含进来，如果下标表达式是列引用或者位置参数的话括号可以省略。而且当数组是多维的时候，多下标可以被连接起来，例如：

mytable.arraycolumn[4] mytable.two_d_column[17][34] $1[10:42] (arrayfunction(a,b))[42]

最后一个例子中的括号是必须的，更多内容请查阅 8.15

4. 字段选择（Field Selection）

如果一个表达式产生了复合类型（行类型），那么某个字段或者行可以通过以下形式提取：

expression . fieldname

例如：

mytable.mycolumn $1.somecolumn (rowfunction(a,b)).col3

一个很特殊的例子是从符合类型的表列中提取字段

(compositecol).somefield (mytable.compositecol).somefield

括号在这里定义了compositecol是列名而不是表名，mytable是表名而不是模式名。 也可以通过以下形式调用复合值的所有字段：

(compositecol).*

表示法根据上下文会有所不同

5. 操作符调用（Operator Invocations）

对于操作符的调用有下面三种语法：

expression operator expression (binary infix operator) operator expression (unary prefix operator) expression operator (unary postfix operator)

操作符遵循Section 4.1.3中的规则,或者是关键字AND ,OR , NOT , 或者是以下格式的合法操作符名字:

OPERATOR(schema.operatorname)

6. 函数调用（Function Calls）

函数调用的语法是一个函数名（也可能是指定的模式名）跟着有括号包围的参数序列：

function_name ([ expression [, expression ... ]] )

如下例是计算2的平方根的函数调用：

sqrt(2)

内建函数类表可以在第9章中查询，用户也可以自定义一些函数来实现自己想要的功能。

Note: A function that takes a single argument of composite type can optionally be called using field-selection syntax, and conversely field selection can be written in functional style. That is, the notations col(table) and table.col are interchangeable. This behavior is not SQL-standard but is provided in PostgreSQL because it allows use of functions to emulate “computed fields”.

更多内容请查看8.16.5.

7. 统计表达式（Aggregate Expressions）

统计表达式是指对选中的行进行统计函数处理的应用。 统计函数减少了单一输出的多次输入，例如sum和average的输入，其语法如下：

aggregate_name ( expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ] aggregate_name (ALL expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause aggregate_name (DISTINCT expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_c aggregate_name ( * ) [ FILTER ( WHERE filter_clause ) ] aggregate_name ( [ expression [ , ... ] ] ) WITHIN GROUP ( order_by_clause ) [ FILTER ( WHER

aggregate_name是预先定义好的统计和表达式。 通常统计函数回忽略空输入，所以对于表达式生成的空行会被忽略。 一般情况下，统计函数是不会要求输入顺序的，但是像array_agg 和d string_agg是根据输入行来生成结果的，所以当我们使用这些函数的时候就需要使用可选的 order_by_clause为输入指定顺序，order_by_clause和ORDER BY clause有相同的语法，如下：

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; -- 这是错误的

后面的这种结构形式虽然是语法正确的，但是它意味着使用两个ORDER BY关键字调用一个单参数统计函数。

8. 窗口函数调用（Window Function Calls）

窗口函数类似统计函数，但不同于统计函数调用的时，它并不会将选中的行数据归到单一的输出行中，每一行在查询输出中都保持独立。然而窗口函数能够根据窗口函数调用时的指定浏览所有行。 窗口函数的语法如下

function_name([ expression [,([ expression [,[ FILTER ( WHERE filter_clause ) ] function_name([ expression [,([ expression [,[ FILTER ( WHERE filter_clause ) ] function_name( * ) [ FILTER( WHERE filter_clause ) ] OVER window_name function_name( * ) [ FILTER( WHERE filter_clause ) ] OVER ( window_definition )

window_definition有以下语法：

[existing_window_name ] [PARTITION BY expression [, ...] ] [ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ] [frame_clause ]

可选的frame_clause可以是以下：

{ RANGE | ROWS } frame_start { RANGE | ROWS } BETWEEN frame_start AND frame_end

frame_start和frame_end可以是以下：

UNBOUNDED PRECEDING value PRECEDING CURRENT ROW value FOLLOWING UNBOUNDED FOLLOWING

9. 类型转换（Type Casts）

类型转化就是将一种数据转化为另外一种，PostgreSQL接收以下两种语法：

CAST ( expression AS type ) expression :: type

10 集合表达式（Collation Expressions）

COLLATE定语覆盖一个表达式的collation。应用表达式如下：

expr COLLATE collation

COLLATE定语的两个常用示例 ：

SELECT a, b, c FROM tbl WHERE ... ORDER BY a COLLATE "C"; SELECT * FROM tbl WHERE a > ’foo’ COLLATE "C";

11. 标量子查询（Scalar subquery）

表量子查询是SELECT用括号括起来的查询，这个查询返回具体的一个或者一行列表

例如以下，查找城市中人口最多的州:

SELECT name, (SELECT max(pop) FROM cities WHERE cities.state = states.name) FROM states;