GBASE 8C——SQL参考6 sql语法（6）

1. CREATE DOMAIN

功能描述

定义一个新的域。

语法格式

CREATE DOMAIN name [ AS ] data_type

    [ COLLATE collation ]

    [ DEFAULT expression ]

    [ constraint [ ... ] ]

其中 constraint 是：

[ CONSTRAINT constraint_name ]

{ NOT NULL | NULL | CHECK (expression) }

参数说明

1. name：域的名称，可以被模式修饰；
2. data_type：域的底层数据类型，可以包括数组指示符；
3. collation：域的排序规则，若缺省默认使用底层数据类型的默认排序规则；
4. DEFAULT expression：指定域数据类型默认值，缺省为空；
5. CONSTRAINT constraint_name：约束的名称，缺省情况系统会自动生成名称；
6. NOT NULL：域的值不能为空；
7. NULL：域的值允许为空；

CHECK (expression)：指定域的值必须满足的完整性约束或测试，约束必须是一个产生布尔结果的表达式。

注意事项

值转换成域类型的时候会进行约束检查（特别是NOT NULL）。

范例

创建us_postal_code数据类型并且把它用在一个表定义中：

CREATE DOMAIN us_postal_code AS TEXT

CHECK(

   VALUE ~ '^\d{5}$'

OR VALUE ~ '^\d{5}-\d{4}$'

);

CREATE DOMAIN

CREATE TABLE us_snail_addy (

  address_id SERIAL PRIMARY KEY,

  street1 TEXT NOT NULL,

  street2 TEXT,

  street3 TEXT,

  city TEXT NOT NULL,

  postal us_postal_code NOT NULL

);

CREATE TABLE

语法兼容

CREATE DOMAIN符合 SQL 标准。

相关语法

ALTER DOMAIN, DROP DOMAIN

1. CREATE EXTENSION

功能描述

安装扩展。

语法格式

CREATE EXTENSION [ IF NOT EXISTS ] extension_name

    [ WITH ] [ SCHEMA schema_name ]

             [ VERSION version ]

             [ FROM old_version ]

             [ CASCADE ]

参数说明

1. IF NOT EXISTS：已有同名扩展存在时不报错，仅提示；
2. extension_name：待安装的扩展的名称；
3. schema_name：待安装扩展的模式名称，该名称必须已经存在；
4. version：待安装的扩展的版本；
5. old_version：模式升级时需提供老模式版本；
6. CASCADE：自动安装扩展依赖。

注意事项

安装的扩展不能与现有扩展重名。

范例

CREATE EXTENSION hstore;

CREATE EXTENSION

语法兼容

CREATE EXTENSION是PostgreSQL扩展。

相关语法

ALTER EXTENSION, DROP EXTENSION

1. CREATE FUNCTION

功能描述

创建函数。

语法格式

CREATE [ OR REPLACE ] FUNCTION

    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )

    [ RETURNS rettype

      | RETURNS TABLE ( column_name column_type [, ...] ) ]

  { LANGUAGE lang_name

    | TRANSFORM { FOR TYPE type_name } [, ... ]

    | WINDOW

    | IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF

    | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT

    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER

    | PARALLEL { UNSAFE | RESTRICTED | SAFE }

    | COST execution_cost

    | ROWS result_rows

    | SET configuration_parameter { TO value | = value | FROM CURRENT }

    | AS 'definition'

    | AS 'obj_file', 'link_symbol'

  } ...

    [ WITH ( attribute [, ...] ) ]

参数说明

1. name：要创建的函数的名称（可以被模式修饰），取值范围：字符串；
2. argmode：函数参数的模式，取值范围：IN、OUT、INOUT或者VARIADIC，缺省为IN。只有OUT模式的参数后面能跟VARIADIC（VARIADIC用于声明数组类型的参数），且OUT和INOUT模式的参数不能用在RETURNS TABLE符的函数定义中；
3. argname：函数参数的名称，取值范围：字符串；
4. argtype：函数参数的数据类型；
5. default_expr：参数的默认表达式；
6. rettype：函数返回值的数据类型，如果有OUT或IN OUT参数，可以省略RETURNS子句，如果未省略则RETURNS子句必须和输出参数表示的结果类型一致：若多个输出参数，则为RECORD否则与单个输出参数的类型相同。SETOF修饰词表示该函数将返回一个集合，而不是单独一项；
7. column_name：字段名称；
8. column_type：字段类型；
9. lang_name：用以实现函数的语言的名字。可以是SQL，C，internal，或者是用户定义的过程语言名字。为了保证向下兼容，该名字可以用单引号（包围）。若采用单引号，则引号内必须为大写；
10. TRANSFORM { FOR TYPE type_name } [, ... ] }：类型转换的列表，适用于对函数的调用；
11. WINDOW：表示该函数是窗口函数，通常只用于C语言编写的函数。替换函数定义时不能改变WINDOW属性；
12. IMMUTABLE：表示该函数在给出同样的参数值时总是返回同样的结果；
13. STABLE：表示该函数不能修改数据库，对相同参数值，在同一次表扫描里，该函数的返回值不变，但是返回值可能在不同SQL语句之间变化；
14. VOLATILE：表示该函数值可以在一次表扫描内改变，因此不会做任何优化；
15. LEAKPROOF：指出该函数的参数只包括返回值。LEAKPROOF只能由系统管理员设置；
16. CALLED ON NULL INPUT：表明该函数的某些参数是NULL的时候可以按照正常的方式调用。该参数可以省略；
17. RETURNS NULL ON NULL INPUT | STRICT：RETURNS NULL ON NULL INPUT或STRICT用于指定如果函数的某个参数是NULL，此函数总是返回NULL。如果声明了这个参数，当有NULL值参数时该函数不会被执行；而只是自动返回一个NULL结果；
18. [EXTERNAL] SECURITY INVOKER：表明该函数将带着调用它的用户的权限执行，该参数可以省略，EXTERNAL为可选参数；
19. [EXTERNAL] SECURITY DEFINER：声明该函数将以创建它的用户的权限执行EXTERNAL为可选参数；
20. PARALLEL：表示该函数是否可以下推到DN上执行。PARALLEL UNSAFE表示该函数不能下推到DN上执行且强制使用顺序执行计划，为默认选项；PARALLEL RESTRICTED表示该函数能下推到DN上执行，但是要求必须在DN Leader上执行；PARALLEL SAFE表示该函数能下推到DN上执行，安全且不受限制；
21. execution_cost：用来估计函数的执行成本，execution_cost以cpu_operator_cost为单位，取值范围为正数；
22. result_rows：估计函数返回的行数，用于函数返回的是一个集合，取值范围为正数，默认值1000行；
23. configuration_parameter：把指定的数据库会话参数值设置为给定的值，如果value是DEFAULT或者RESET，则在新的会话中使用系统的缺省设置。OFF关闭设置，取值范围为字符串；
24. definition：定义该函数的字符串常量，可以是内部函数名、对象文件的路径、SQL命令或者用过程语言编写的文本；
25. obj_file, link_symbol：适用于C语言函数，字符串obj_file指定了动态库的绝对路径；link_symbol 指定了该函数的链接符号，也就是该函数在C代码中的函数名称；
26. attribute：函数可选信息，取值范围：isStrict（等效于STRICT或者RETURNS NULL ON NULL INPUT）、isCachable（等效于IMMUTABLE）。

注意事项

GBase 8c数据库允许函数重载，同一个名称可以用于多个不同函数，但他们必须有可以区分的输入参数类型。请注意，所有函数的C名称不能相同，用户必须给被重载的C函数指定不同的C名称。

GBase 8c数据库允许使用完整的SQL语句生命函数的参数和返回值，需要注意的是，CREATE FUNCTION不能识别带圆括号的类型修饰符。

使用CREATE FUNCTION或REPLACE FUNCTION时，不能更改已经分配给其他输入参数的名称；如果有多个输出参数，不能修改输出参数的名称。

如果一个被声明为STRICT的函数带有VARIADIC参数，会严格检查结果是否为空，如果为空，该函数仍将被调用。

范例

CREATE FUNCTION add(integer, integer) RETURNS integer

     AS 'select $1 + $2;'

     LANGUAGE SQL

     IMMUTABLE

     RETURNS NULL ON NULL INPUT;

CREATE FUNCTION

语法兼容

CREATE FUNCTION命令被定义在SQL:1999及其后的版本中。PostgreSQL的版本类似但不完全兼容。属性是不可移植的，不同的可用语言也是不能移植的。

相关语法

ALTER FUNCTION, DROP FUNCTION, GRANT, LOAD, REVOKE

1. CREATE GROUP

功能描述

创建新的用户组。

语法格式

CREATE GROUP name [ [ WITH ] option [ ... ] ]

其中 option 可以是：

      SUPERUSER | NOSUPERUSER

    | CREATEDB | NOCREATEDB

    | CREATEROLE | NOCREATEROLE

    | INHERIT | NOINHERIT

    | LOGIN | NOLOGIN

    | [ ENCRYPTED ] PASSWORD 'password'

    | VALID UNTIL 'timestamp'

    | IN ROLE role_name [, ...]

    | IN GROUP role_name [, ...]

    | ROLE role_name [, ...]

    | ADMIN role_name [, ...]

    | USER role_name [, ...]

    | SYSID uid

参数说明

参考CREATE ROLE的参数说明。

注意事项

CREATE GROUP是CREATE ROLE的别名。

语法兼容

在 SQL 标准中没有CREATE GROUP语句。

相关语法

CREATE ROLE

1. CREATE INDEX

功能描述

定义新的索引。

语法格式

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON table_name [ USING method ]

    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )

    [ WITH ( storage_parameter = value [, ... ] ) ]

    [ TABLESPACE tablespace_name ]

    [ WHERE predicate ]

参数说明

1. UNIQUE：创建唯一性索引，每次添加数据时检测表中是否有重复值。如果插入或更新的值会引起重复的记录时，将导致报错；
2. CONCURRENTLY：GBase 8c当前不支持CONCURRENTLY。如果使用了该选项，创建索引时不加任何组织在该表上并发插入、更新或删除的锁；
3. IF NOT EXISTS：如果不唯一，不报错，仅提示；
4. name：要创建的索引名，不能包含模式名，索引的模式与表相同；
5. table_name：需要为其创建索引的表的名字，可以用模式修饰
6. method：指定创建索引的名称，取值范围：btree、hash、gist、spgist、gin以及brin，缺省为btree；
7. column_name：表中需要创建索引的列的名字；
8. expression：创建一个基于该表的一个或多个字段的表达式索引，通常必须写在圆括弧中。如果表达式有函数调用的形式，圆括弧可以省略；
9. collation：指定列的排序规则（该列必须是可排列的数据类型）。缺省使用默认的排序规则；
10. opclass：操作符类的名字。对于索引的每一列可以指定一个操作符类，操作符类标识了索引那一列的使用的操作符。例如一个B-tree索引在一个四字节整数上可以使用int4_ops；这个操作符类包括四字节整数的比较函数。实际上对于列上的数据类型默认的操作符类是足够用的。操作符类主要用于一些有多种排序的数据。例如，用户想按照绝对值或者实数部分排序一个复数。能通过定义两个操作符类然后当建立索引时选择合适的类
11. ASC：指定按升序排序（默认）；
12. DESC：指定按降序排序；
13. NULLS FIRST：指定空值在排序中排在非空值之前，当指定DESC排序时，本选项为默认的；
14. NULLS LAST：指定空值在排序中排在非空值之后，未指定DESC排序时，本选项为默认的；
15. storage_parameter：指定索引方法的存储参数；
16. tablespace_name：指定索引的表空间，如果没有声明则使用默认的表空间；
17. predicate：创建一个部分索引。

索引存储参数

可选的WITH语句为索引指定存储参数，每种索引方法都有特定的存储参数集合。btree、hash、gist、spgist索引都接受这个参数：

1. fillfactor：索引的填充因子，是一个百分数，表示使用该索引防范填充页面的程度；

GiST还额外接受这个参数：

1. buffering：beffering为OFF禁用缓冲构建，为ON启用缓冲构建，为AUTO时初始会禁用它，但是一旦索引尺寸到达 effective_cache_size就会随时打开，缺省为AUTO；

GIN索引接受不同的参数：

1. fastupdate：fastupdate为ON启用快速更新，为OFF禁用快速更新，缺省为ON；
2. gin_pending_list_limit：自定义gin_pending_list_limit参数；

BRIN索引接受不同的参数：

pages_per_range：定义用于每一个BRIN索引项的块范围由多少个表块组成，缺省为128；

autosummarize：定义每当在下一页检测到插入时，是否为前一页范围调用汇总运行。

注意事项

索引可以用来提高数据库查询性能，但是不恰当的使用将导致数据库性能下降。建议仅在匹配如下某条原则时创建索引：

1. 经常执行查询的字段。
2. 在连接条件上创建索引，对于存在多字段连接的查询，建议在这些字段上建立组合索引。例如，select * from t1 join t2 on t1.a=t2.a and t1.b=t2.b，可以在t1表上的a，b字段上建立组合索引。
3. where子句的过滤条件字段上（尤其是范围条件）。
4. 在经常出现在order by、group by和distinct后的字段。

在分区表上创建唯一索引时，索引项中必须包含分布列和所有分区键。

当前只有B-树、GiST、GIN 和 BRIN 索引方法支持多列索引，只有B-树支持唯一索引。

使用DROP INDEX可以移除索引。

范例

CREATE UNIQUE INDEX customer_idx ON customer(id);

CREATE INDEX

语法兼容

CREATE INDEX是PostgreSQL的语言扩展。

相关语法

ALTER INDEX, DROP INDEX

1. CREATE LANGUAGE

功能描述

定义新的语言。

语法格式

CREATE [ OR REPLACE ] [ PROCEDURAL ] LANGUAGE name

CREATE [ OR REPLACE ] [ TRUSTED ] [ PROCEDURAL ] LANGUAGE name

    HANDLER call_handler [ INLINE inline_handler ] [ VALIDATOR valfunction ]

参数说明

1. TRUSTED：指定数据库语言访问权限，缺省情况下只有超级管理员可以使用该语言创建新函数；
2. PROCEDURAL：程序上的；
3. name：新语言的名称，在数据库中唯一；
4. HANDLER call_handler：执行该语言的函数，当前已经存在的函数；
5. INLINE inline_handler：执行该语言的匿名代码块（DO命令），是当前已经存在的函数；
6. VALIDATOR valfunction：新语言验证器函数；

注意事项

系统表pg_language记录了当前已安装的语言的信息，或使用\dl命令列出当前已安装的语言。

使用DROP LANGUAGE删除过程语言。

范例

创建标准过程语言：

CREATE LANGUAGE plperl;

对于不在pg_pltemplate目录的语言，需要这样的命令序列：

CREATE FUNCTION plsample_call_handler() RETURNS language_handler

    AS '$libdir/plsample'

    LANGUAGE C;

CREATE LANGUAGE plsample

    HANDLER plsample_call_handler;

语法兼容

CREATE LANGUAGE是一种 PostgreSQL扩展。

相关语法

ALTER LANGUAGE, CREATE FUNCTION, DROP LANGUAGE, GRANT, REVOKE

1. CREATE MATERIALIZED VIEW

功能描述

定义新的物化视图。

语法格式

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] table_name

    [ (column_name [, ...] ) ]

    [ WITH ( storage_parameter [= value] [, ... ] ) ]

    [ TABLESPACE tablespace_name ]

    AS query

    [ WITH [ NO ] DATA ]

参数说明

1. IF NOT EXISTS：如果已经存在同名的物化视图时不要报错，仅提示；
2. table_name：要创建的物化视图的名称，可以被模式修饰；
3. column_name：新物化视图的列名，如果没有提供，会使用查询的输出列名；
4. WITH ( storage_parameter [= value] [, ... ] )：为新的物化视图指定可选的存储参数；
5. TABLESPACE tablespace_name：要把新物化视图创建在其中的表空间的名称，缺省情况下将查询default_tablespace；
6. query：SELECT、TABLE 或者VALUES命令；
7. WITH [ NO ] DATA：指定物化视图是否在创建时被填充，如果不填充该物化视图不可扫描并且在被使用前不能查询；

范例

CREATE MATERIALIZED VIEW IF NOT EXISTS mv_customer

AS

SELECT * FROM customer

WITH DATA;

SELECT 9

语法兼容

CREATE MATERIALIZED VIEW是PostgreSQL扩展。

相关语法

ALTER MATERIALIZED VIEW, CREATE TABLE AS, CREATE VIEW, DROP MATERIALIZED VIEW, REFRESH MATERIALIZED VIEW

1. CREATE NODE

功能描述

创建新的集群节点，CREATE NODE是集群管理工具封装的内部接口，用来实现集群管理，该接口不建议用户直接使用，以免对集群状态造成影响。

语法格式

CREATE NODE nodename WITH

  (

    [ TYPE = nodetype,]

    [ HOST = hostname,]

    [ PORT = portnum,]

    [ PRIMARY [ = boolean ],]

    [ PREFERRED [ = boolean ] ]

  )

参数说明

1. nodename：节点名称；
2. TYPE：节点类型，取值范围：Coordinator或Datanode；
3. PRIMARY：声明该节点是否为主节点；
4. PREFERRED：声明该节点是否为读操作的首选节点；
5. nodetype：节点类型，取值范围：Coordinator或Datanode；
6. hostname：集群节点名称或者IP地址；
7. portnum：集群节点主机端口号；

注意事项

CREATE NODE不建议用户直接使用，以免对集群状态造成影响。

语法兼容

CREATE NOD不是标准SQL语法。

相关语法

ALTER NODE， DROP NODE

1. CREATE NODE GROUP

功能描述

创建新的集群节点组，CREATE NODE GROUP是集群管理工具封装的内部接口，用来实现集群管理，该接口不建议用户直接使用，以免对集群状态造成影响。

语法格式

CREATE NODE GROUP groupname

WITH ( nodename [, ... ] )

参数说明

groupname：节点组名称；

nodename：节点名称；

注意事项

CREATE NODE GROUP是集群管理工具封装的内部接口，用来实现集群管理，该接口不建议用户直接使用，以免对集群状态造成影响。

语法兼容

CREATE NODE GROUP不是标准SQL语法。

相关语法

DROP NODE GROUP

1. CREATE OPERATOR

功能描述

定义新的操作符。

语法格式

CREATE OPERATOR name (

    PROCEDURE = function_name

    [, LEFTARG = left_type ] [, RIGHTARG = right_type ]

    [, COMMUTATOR = com_op ] [, NEGATOR = neg_op ]

    [, RESTRICT = res_proc ] [, JOIN = join_proc ]

    [, HASHES ] [, MERGES ]

)

参数说明

1. name：新的操作符的名称，操作符名称最大NAMEDATALEN-1（默认为 63）个字符的序列，字符的可选范围：+ - * / < > = ~ ! @ # % ^ & | ` ?；
   1. -- and /*不能出现在 操作符名称中，因为它们会被当做一段注释的开始；
   2. 多字符操作符名称不能以+或者- 结束，除非该名称也包含至少一个如下字符：~ ! @ # % ^ & | ` ?；
   3. 不推荐将=>用作一个操作符名称；
2. function_name：用来实现这个操作符的函数；
3. left_type：这个操作符的左操作数（如果有）的数据类型，缺省情况下可以写成NONE；
4. right_type：这个操作符的右操作数（如果有）的数据类型，缺省情况下可以写成NONE；
5. com_op：这个操作符的交换子；
6. neg_op：这个操作符的求反器；
7. res_proc：用于这个操作符的限制选择度估计函数；
8. join_proc：用于这个操作符的连接选择度估算函数；
9. HASHES：表示这个操作符可以支持哈希连接；
10. MERGES：表示这个操作符可以支持归并连接。

注意事项

无法在CREATE OPERATOR中指定操作符的优先级。

使用DROP OPERATOR从数据库中删除用户定义的操作符。 使用ALTER OPERATOR修改数据库中的操作符。

范例

CREATE OPERATOR === (

    LEFTARG = box,

    RIGHTARG = box,

    PROCEDURE = area_equal_procedure,

    COMMUTATOR = ===,

    NEGATOR = !==,

    RESTRICT = area_restriction_procedure,

    JOIN = area_join_procedure,

    HASHES, MERGES

);

语法兼容

CREATE OPERATOR是PostgreSQL扩展。

相关语法

ALTER OPERATOR, DROP OPERATOR, CREATE OPERATOR CLASS,