postgresql笔记010-值表达式

值表达式

值表达式用在各种语法环境中，比如在select命令的目标列表中，在insert, update 中用作新的列值，或者在许多命令
的搜索条件中使用。我们有时候把值表达式的结果叫做标量，以便于一个表达式的结果相区分。因此值表达式也叫作
标量表达式。表达式语法允许对来自基础部分的数值进行算术，逻辑，集合和其他运算。

值表达式具体实现如下：

一个常量或者字面值
一个字段引用
一个位置参数引用(在函数声明体中或预编写的语句中)
一个下标表达式
一个字段选择表达式
一个操作符调用
一个函数调用
一个聚合表达式
一个窗口函数调用
一个类型转换
一个排序规则表达式
一个标量子查询
一个数组构造器
一个行构造器
一个在圆括弧里面的值表达式(可用于子表达式分组和覆盖优先级)

字段引用

tablename.columnname
表名.列名
表别名.列名

位置参数

一个参数的形式如下：
$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]

(expression 表达 / 陈述 / 表情 / 词句。 subscript:下标的 / 写在下方的 / 脚注的 )
每个subscript自己都是一个表达式，他必须生成一个整数值。

通常，数组expression必须用圆括号包围，但是如果只是一个字段引用或者一个位置参数，那么圆括号可以省略。
同样，如果源数据是多维度的，那么多个下标可以连接在一起。如：

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

其中上面最后一个例子中的的圆括号是必须的。

字段选择

如果一个表达式生成一个复合类型（行类型），我们可以用下面的方法抽取一个指定的字段

expression.fieldname

通常行expression必须用圆括号包围，但是如果要选择的表达式只是一个表达引用或者位置参数，可以省略圆括号。如：

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

一个全称的字段引用实际上只是一个字段选择语法的特例。一个重要的特殊情形是提取的表列是一个复合型的字段：

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

在这里，括号是必须的，用来指出compositecol 是列名而不是表名，mytable是表名而不是模式名。

操作符调用

…

函数调用

函数调用的语法是合法函数名，后面跟着包含参数列表的源括号
function_name(expression[,expression …]]…)
比如计算4的平方根

sqrt(4);

聚合函数表达式

大多数的聚合函数会忽略NULL的输入，因此在一个或者多个表达式中产生Null的行会被丢弃。
对所有的内置函数而言，这样做是可以的。
比如：
count(*) 生成输入行的总数；count（f1）生成f1不为Null的输入行数，因为count会忽略NULL；
count（distinct f1）生成f1唯一，且非NULL的行数。

特殊的聚合函数：
array_agg
string_agg

在一个聚合函数中声明distinct 和order by 是pgsql的一个扩展。

select string_agg(name,',' order by name) from eat;

将表中的一列数据name转换成以逗号分隔的字符串。

select percentile_disc(0.5) within group (order by  stu_num) from test.class_stu;

获得来自class_stu的stu_num字段的百分之五十的值，或者说获得stu_num中的中位数。

如果指定了filter，那么仅有filter_clause计算为真的输入航供给聚合函数；丢弃其他行。
案例：

select * from test.class_stu;

具体实现：获得学生数量stu_num总的数量，和学生数量stu_num大于35的数量。

select count(stu_num) as unfiltered,
	count(stu_num) filter( where stu_num>35) as filtered 
from test.class_stu;

案例2：

select * from generate_series(1,10)  as s(i);

上面返回一个以i命名的列，列中的内容为1，2，3，4，，，，8，9，10（从1到10）
下面的结果返回10和小于5的数字的和 4

select count(*) as unfiltered,
	count(*) filter (where i<5) as filtered 
	from generate_series(1,10) as s(i);

一个聚合函数表达式只能在select命令的结果列表或者having子句中出现。禁止在其它字句里出现(特别是where子句)，因为这些子句是在生成聚合结果之前计算。
如果一个聚合表达式出现在一个子查询里，聚合通常是在子查询中进行计算。

窗口调用函数

通过查询筛选出的行的某些部分，窗口调用函数实现了类似聚合函数的功能。不同的是，窗口调用函数不需要将查询结果打包成一行输出。在查询输出中，每一行都是分开的。窗口函数可以扫描所有的行，根据窗口调用函数的分组规范（partition by 列）这些行可能是当前行所在的组的一部分。

注意：over wname并不完全等于over(wname)；后者以为这拷贝和修改窗口定义，并且如果引用的窗口声明包括一个框架子句，则将被拒绝。

partition by 选项将查询的行分为一组进入partitions，这些行在窗口中单独处理。
partition by 和查询级别 group by 子句做相似的工作。
没有partition by 所有由查询产生的行会被视为一个单独的分区。
order by选项决定分区中的行被窗口函数处理的顺序。它和查询级别order by子句做相似的工作，但是在窗口函数中order by不能作为累的名字或者数。
没有order by ，行以一个不被预知的顺序进行处理。

类型转换

一个类型转换声明：一个从一种数据类型到另外一种数据类型的转换。pgsql接受两种等效的类型转换语法：

cast(expression as type)
expression::type

其中cast语法遵循sql标准。::语法是pgsql历史用法。

排序规则表达式

用collate子句重写表达式的排序规则。它附加到应用的表达式上：

expr COLLATE collation

在这里的collation是一个可能的模式限定标识符。
collate子句绑定得比操作符更紧密；需要时可以用括号。

如果没有明确声明排序规则，数据库系统要么从表达式中的列获取一个排序规则，要么如果表达式中没有包含列，使用数据库默认排序规则。

标量子查询

一个标量子查询是一个放在圆括号里只返回一行一列的普通select 查询。该select 将被执行，而其返回值奖在周围的值表达式中使用。该select将被执行，而其返回结果将在周围的值表达式中使用。
把一个返回超过一行或者一列的查询用作标量查询是错误的。
子查询可以引用外围查询的变量，这些变量在每次子查询中当做常量使用。
案例：查找出每个州中的最大人后数量的城市。

select name,(select max(pop) from cities where cities.state=states.name) from states;

数组构造器

一个数组构造器是一个表达式，它从自身成员元素上构造一个数组值。
一个简单地数组构造器有关键字array,一个左方括号[,一个或者多个表示数组元素值得表达式(用逗号分隔),一个右方括号] 组成。
如下案例：

select array[1,2,3+4];

返回结果是一个数组。

在数组构造器中，数组元素类型是成员表达式的公共类型，使用union或者case构造一样的规则决定。同时我们可以通过名曲的转换数组构造器为想要的类型来重写这个规则。
案例：

select arry[1,2,5.7] :: integer[];

重定义后，数组中的元素全部返回整数。其中5.7直接返回6。

多维数组值可以通过嵌套数组构造器的方法来制作。内层构造器中的array关键字可以省略。
案例：

select array[array[1,2],array[3,4]];

select array[[1,2],[3,4]];

上面两个select返回相同的结果

因为多维度数组必须是方形，所以同层的内层构造器必须生成同纬度的子数组。任何应用于外层array构造器的类型转换自动的应用到所有的内层构造器。

create table tab_arr(f1 int[],f2 int[]);
insert into tab_arr values(array[[1,2],[3,4]], array[[5,6],[7,8]]);

进行select：

select array[f1,f2,'{{9,10},{11,12}}'::int[]] from tab_arr;

在构造一个空数组的时候，必须明确的将其构造成需要的类型。不然会报错。如：

select array[]::integer[];

返回结果为空

select array(select stu_num from class_stu where stu_num>30);

行构造器

行构造器是一个从提供给他的成员字段数字中构造行值的表达式。
一个行构造器由关键字row，一个左圆括号，零个或者多个作为行字段值的表达式，一个右圆括号组成。
案例：

select row(1,2.5,'this is a test');

如果在列表里面有多个表达式，那么关键字row是可选的。
行构造器可以包含rowvalue.语法，他将被扩展为行值元素的列表，就像将. 语法用于一个select 列表顶层一样。例如，如果表 tab有f1,f2两个字段，那么下面两句是等价的：

select row(tab.*,33) from tab;
select row(tab.f1,tab.f2,42) from tab;

案例：

create table test.mytable(f1 int,f2 float,f3 text);

create function getf1(mytable) returns int as 
	'select $1.f1' LANGUAGE SQL;

select getf1(row(1,2.5,'this is a test'));

省缺时，row表达式创建的值是一个匿名的记录类型。
如果必要，我们可以将它转换成一个命名的复合类型（既可以是一个表的行类型，也可以是一个用create type as 创建的复合类型）。
可能会需要一个明确的转换以避免歧义。
案例：

create type test.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.4,'this is a test'));

select getf1(row(3,2.8,'this is a test')::mytable);

行构造器可以用于制作存储在复合类型字段中的复合类型值，或者是传递给一个接受复合类型参数的函数。
我们也可以用它比较两个行值或者用 is null 或者IS NOT NULL 测试一个行值。
案例：

select row(1,2.5, 'this is a test') = row (1,4,'not the same');

返回结果为f 是false的缩写。意思是不相等。

select row(mytable.*) is  null from mytable;

表达式计算规则

子表达式的计算顺序是未定义的。特别要注意的是，一个操作符或者函数的输入并不一定是按照从左到右的顺序或者以某种特定的顺序进行计算的。

另外，如果一个表达式的结果可以通过值判断他的一部分就可以得到，那么其他子表达式就可以完全不计算了。如案例：

select true or somefunc();

如果我们这样写，那么somefunc()就（可能）根本不会被调用。即使像下面这样写，也是一样：

select somefunc() or true;

注意：这和编程语言里的从左向右“短路”布尔操作符是不一样的。

因此，拿有副作用的函数作为复杂表达式的一部分是不明智的。
在where和having子句中依赖副作用或者计算顺序是特别危险的，因为这些子句都是作为生成一个执行规划的一部分进行了大量的再处理。在这些子句里的布尔表达式（and/or/not的组合）可以用布尔代数运算律允许的任何方式进行识别。

如果要强制计算顺序，那么可以使用case。

如，下面是一种企图避免在where子句里别零除的不可靠方法：

select * from taba where x>0 and y/x >1.5;

下面这个是安全的，可靠的。

select ... where case when x>0 then y/x >1.5 else false end;

这种风格的case 构造会阻止优化，因此应该只在必要的时候才使用。在这个特殊的例子里，
最好将y/x >1.5 改写为 y>1.5*x。