华为云用户手册

示例2：通过行级控制实现分区权限管理 创建用户alice： 1 CREATE ROLE alice PASSWORD '{password1}'; 创建范围分区表web_returns_p1，并插入数据： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 CREATE TABLE web_returns_p1 ( wr_returned_date_sk integer, wr_returned_time_sk integer, wr_item_sk integer NOT NULL, wr_refunded_customer_sk integer ) WITH (orientation = column) DISTRIBUTE BY HASH (wr_item_sk) PARTITION BY RANGE(wr_returned_date_sk) ( PARTITION p2016 START(800) END(830) EVERY(1) ); INSERT INTO web_returns_p1 values (801,17,11,102); INSERT INTO web_returns_p1 values (802,18,12,103); 将表web_returns_p1的读取权限赋予alice用户： 1 GRANT SELECT ON web_returns_p1 TO alice; 打开行访问控制策略开关： 1 ALTER TABLE web_returns_p1 ENABLE ROW LEVEL SECURITY; 创建行级访问控制策略web_returns_rsl。其中wr_returned_date_sk为web_returns_p1分区表的分区名，801为分区值： 1 CREATE ROW LEVEL SECURITY POLICY web_returns_rsl ON web_returns_p1 USING('wr_returned_date_sk' = '801'); 将行级访问控制策略web_returns_rsl的赋予用户alice： 1 ALTER ROW LEVEL SECURITY POLICY web_returns_rsl ON web_returns_p1 TO alice; 切换至alice用户： 1 set role alice password '{password1}'; 查询表web_returns_p1： 1 select * from web_returns_p1;

语法格式 1 2 3 4 5 CREATE [ ROW LEVEL SECURITY ] POLICY policy_name ON table_name [ AS { PERMISSIVE | RESTRICTIVE } ] [ FOR { ALL | SELECT | UPDATE | DELETE } ] [ TO { role_name | PUBLIC } [, ...] ] USING ( using_expression )

功能描述 对表创建行访问控制策略。 对表创建行访问控制策略时，需打开该表的行访问控制开关（ALTER TABLE ... ENABLE ROW LEVEL SECURITY | ALTER FOREIGN TABLE ... ENABLE ROW LEVEL SECURITY）策略才能生效，否则不生效。 当前行访问控制会影响数据表的读取操作（SELECT、UPDATE、DELETE），暂不影响数据表的写入操作（INSERT、MERGE INTO）。表所有者或系统管理员可以在USING子句中创建表达式，并在客户端执行数据表读取操作时，数据库后台在查询重写阶段会将满足条件的表达式拼接并应用到执行计划中。针对数据表的每一条元组，当USING表达式返回TRUE时，元组对当前用户可见，当USING表达式返回FALSE或NULL时，元组对当前用户不可见。 行访问控制策略名称是针对表的，同一个数据表上不能有同名的行访问控制策略；对不同的数据表，可以有同名的行访问控制策略。 行访问控制策略可以应用到指定的操作（SELECT、UPDATE、DELETE、ALL），ALL表示会影响SELECT、UPDATE、DELETE三种操作；定义行访问控制策略时，若未指定受影响的相关操作，默认为ALL。 行访问控制策略可以应用到指定的用户（角色），也可应用到全部用户（PUBLIC）；定义行访问控制策略时，若未指定受影响的用户，默认为PUBLIC。

注意事项 支持对行存表、行存分区表、列存表、列存分区表、复制表、unlogged表、hash表、非EXTERNAL SCHEMA的外表定义行访问控制策略。 不支持HDFS表、EXTERNAL SCHEMA的外表、临时表定义行访问控制策略。 不支持对视图定义行访问控制策略。 同一张表上可以创建多个行访问控制策略，一张表最多创建100个行访问控制策略。 具有管理员权限的用户、初始运维用户(Ruby)、表的owner和表的owner角色组成员不受行访问控制影响，可以查看表的全量数据。 通过SQL语句、视图、函数、存储过程查询包含行访问控制策略的表，都会受影响。 不支持对行访问控制策略依赖的列进行类型修改。例如，不支持如下修改： 1 ALTER TABLE public.all_data ALTER COLUMN role TYPE text;

参数说明 表1 FETCH参数说明 参数 描述 取值范围 direction_clause 定义抓取数据的方向。 请参见表2。 { FROM | IN } cursor_name 使用关键字FROM或IN指定游标名称。 已创建的游标的名称。 表2 direction_clause取值范围 取值范围 描述 备注 NEXT（缺省值） 从当前关联位置开始，抓取下一行。 - PRIOR 从当前关联位置开始，抓取上一行。 - FIRST 抓取查询的第一行（和ABSOLUTE 1相同）。 - LAST 抓取查询的最后一行（和ABSOLUTE -1相同）。 - ABSOLUTE count 抓取查询中第count行。 ABSOLUTE抓取不会比用相对位移移动到需要的数据行更快，因为下层的实现必须遍历所有中间的行。 count取值范围：有符号的整数 count为正数，就从查询结果的第一行开始，抓取第count行。当count小于当前游标位置时，涉及到rewind操作，暂不支持。 count为负数或0，涉及到反向扫描操作，暂不支持。 RELATIVE count 从当前关联位置开始，抓取随后或前面的第count行。 count取值范围：有符号的整数 count为正数就抓取当前关联位置之后的第count行。 count为负数，涉及到反向扫描操作，暂不支持。 如果有数据的话，RELATIVE 0重新抓取当前行。 count 抓取随后的count行（和FORWARD count一样）。 - ALL 从当前关联位置开始，抓取所有剩余的行（和FORWARD ALL一样）。 - FORWARD 抓取下一行（和NEXT一样）。 - FORWARD count 与RELATIVE count的效果相同，从当前关联位置开始，抓取随后或前面的第count行。 - FORWARD ALL 从当前关联位置开始，抓取所有剩余行。 - BACKWARD 从当前关联位置开始，抓取前面一行（和PRIOR一样）。 - BACKWARD count 从当前关联位置开始，抓取前面的count行（向后扫描）。 count取值范围：有符号的整数 count为正数就抓取当前关联位置之前的第count行。 count为负数就抓取当前关联位置之后的第abs（count）行。 如果有数据的话，BACKWARD 0重新抓取当前行。 BACKWARD ALL 从当前关联位置开始，抓取所有前面的行（向后扫描） 。 -

示例 示例一：SELECT语句，用一个游标读取一个表。 建立一个名为cursor1的游标： 1 CURSOR cursor1 FOR SELECT * FROM tpcds.customer_address ORDER BY 1; 抓取头3行到游标cursor1里： 1 FETCH FORWARD 3 FROM cursor1; 示例二：VALUES子句，用一个游标读取VALUES子句中的内容。 建立一个名为cursor2的游标： 1 CURSOR cursor2 FOR VALUES(1,2),(0,3) ORDER BY 1; 抓取头2行到游标cursor2里： 1 FETCH FORWARD 2 FROM cursor2;

语法格式 1 FETCH [ direction { FROM | IN } ] cursor_name; 其中direction子句为可选参数。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 NEXT | PRIOR | FIRST | LAST | ABSOLUTE count | RELATIVE count | count | ALL | FORWARD | FORWARD count | FORWARD ALL | BACKWARD | BACKWARD count | BACKWARD ALL

功能描述 FETCH通过已创建的游标来检索数据。 每个游标都有一个供FETCH使用的关联位置。游标的关联位置可以在查询结果的第一行之前，或者在结果中的任意行，或者在结果的最后一行之后： 游标刚创建完之后，关联位置在第一行之前的。 在抓取了一些移动行之后，关联位置在检索到的最后一行上。 如果FETCH抓取完了所有可用行，它就停在最后一行后面，或者在反向抓取的情况下是停在第一行前面。 FETCH ALL或FETCH BACKWARD ALL将总是把游标的关联位置放在最后一行或者在第一行前面。

注意事项 如果游标定义了NO SCROLL，则不允许使用例如FETCH BACKWARD之类的反向抓取。 NEXT，PRIOR，FIRST，LAST，ABSOLUTE，RELATIVE形式在恰当地移动游标之后抓取一条记录。如果后面没有数据行，就返回一个空的结果，此时游标就会停在查询结果的最后一行之后（向后查询时）或者第一行之前（向前查询时）。 FORWARD和BACKWARD形式在向前或者向后移动的过程中抓取指定的行数，然后把游标定位在最后返回的行上；或者是，如果count大于可用的行数，则在所有行之后（向后查询时）或者之前（向前查询时）。 RELATIVE 0，FORWARD 0，BACKWARD 0都要求在不移动游标的前提下抓取当前行，也就是重新抓取最近刚抓取过的行。除非游标定位在第一行之前或者最后一行之后，这个动作都应该成功，而在那两种情况下，不返回任何行。 当FETCH的游标上涉及列存表时，不支持BACKWARD、PRIOR、FIRST等涉及反向获取操作。

参数说明 表1 DROP TABLE参数说明 参数 描述 取值范围 IF EXISTS 如果指定的表不存在，则发出一个notice而不是抛出一个错误。 - schema 要删除的表所属的模式名称。 - table_name 要删除的表名称。 已存在的表名称。 CASCADE | RESTRICT 当执行删除操作时，如何处理依赖对象中的相关数据。 CASCADE：级联删除依赖于表的对象（比如视图）。 RESTRICT（缺省项）：如果存在依赖对象，则拒绝删除该表。这个是缺省。 PURGE 指定Purge参数时，无论回收站特性是否开启，直接删除，不放入回收站。该参数仅9.1.0.200及以上集群版本支持。 - KEEP ttl HOUR 对于V3表，回收站特性开启时，设置TTL，物理文件会放入回收站中，后续可以从回收站中恢复，TTL到期之后，后台自动清理回收站中的对象。该参数仅9.1.0.200及以上集群版本支持。 须知： 没有做合理的空间管控，建议在drop和truncate频率低客户场景中使用。 -

注意事项 DROP TABLE会强制删除指定的表，删除表后，依赖该表的索引会被删除，而使用到该表的函数和存储过程将无法执行。删除分区表，会同时删除分区表中的所有分区。 只有表的所有者、模式所有者或者被授予了表的DROP权限的用户才能执行DROP TABLE，系统管理员默认拥有该权限。要清空指定表中的行但是不删除该表定义，可以使用TRUNCATE或者DELETE。 DROP对象操作（如DATABASE、USER/ROLE、SCHEMA、TABLE、VIEW等对象）存在数据丢失风险，尤其含带CASCADE级联删除场景，会将关联的对象一并删除，操作需谨慎，操作前需考虑数据备份。 更多开发设计规范参见总体开发设计规范。

参数说明 表1 CREATE PUBLICATION参数说明 参数 描述 取值范围 name 新发布的名称。 字符串，要符合标识符的命名规范。参见标识符命名规范。 FOR ALL TABLES 将发布标记为复制数据库中所有细粒度容灾主表的更改，包括在将来创建的表。 - FOR TABLE 指定要添加到发布的表的列表。只有细粒度容灾主表才能成为发布的一部分。 - table_name 要添加到发布的表的名字，可以带模式名。 字符串，要符合标识符的命名规范。参见标识符命名规范。 FOR ALL TABLES IN SCHEMA 将发布标记为复制指定模式列表中所有细粒度容灾主表的更改，包括在将来创建的表。 - schema_name 要添加到发布的模式的名字。 字符串，要符合标识符的命名规范。参见标识符命名规范。 WITH ( publication_parameter [=value] [, ... ] ) 该子句指定发布的可选参数，当前支持右列的参数。 publish：这个参数决定了哪些DML操作将由新的发布给订阅者。 取值范围：字符串，用逗号分隔的操作列表。允许的操作是insert， update，delete和truncate。 默认发布所有动作，所以这个选项的默认值是：'insert, update, delete, truncate'。

示例 创建一个发布，发布两个表和两个模式中所有更改。 创建示例表tpcds.ship_mode_t1： CREATE TABLE tpcds.ship_mode_t1 ( SM_SHIP_MODE_SK INTEGER NOT NULL, SM_SHIP_MODE_ID CHAR(16) NOT NULL, SM_TYPE CHAR(30) , SM_CODE CHAR(10) , SM_CARRIER CHAR(20) , SM_CONTRACT CHAR(20) ) WITH (ORIENTATION = COLUMN,enable_disaster_cstore='on') DISTRIBUTE BY HASH(SM_SHIP_MODE_SK); 创建示例表tpcds.customer_address_p1： CREATE TABLE tpcds.customer_address_p1 ( CA_ADDRESS_SK INTEGER NOT NULL, CA_ADDRESS_ID CHAR(16) NOT NULL, CA_STREET_NUMBER CHAR(10) , CA_STREET_NAME VARCHAR(60) , CA_STREET_TYPE CHAR(15) , CA_SUITE_NUMBER CHAR(10) , CA_CITY VARCHAR(60) , CA_COUNTY VARCHAR(30) , CA_STATE CHAR(2) , CA_ZIP CHAR(10) , CA_COUNTRY VARCHAR(20) , CA_GMT_OFFSET DECIMAL(5,2) , CA_LOCATION_TYPE CHAR(20) ) WITH (ORIENTATION = COLUMN,enable_disaster_cstore='on') DISTRIBUTE BY HASH(CA_ADDRESS_SK); 创建示例模式myschema1： CREATE SCHEMA myschema1; 创建示例模式myschema2： CREATE SCHEMA myschema2; 创建发布，发布两个表和两个模式中所有更改。 CREATE PUBLICATION mypublication FOR TABLE users, departments, ALL TABLES IN SCHEMA myschema1, myschema2; 创建一个发布，发布所有表中的所有更改。 CREATE PUBLICATION alltables FOR ALL TABLES;

注意事项 该语法仅8.2.0.100及以上集群版本支持。 如果既没有指定FOR TABLE，也没有指定FOR ALL TABLES， 那么这个发布就是以一组空表开始的，可以在后续添加表。 创建发布不会开始复制。它只为未来的订阅者定义一个分组和过滤逻辑。 要创建一个发布，调用者必须拥有当前数据库的CREATE权限。 要将表添加到发布中，调用者必须拥有该表的所有权。FOR ALL TABLES和FOR ALL TABLES IN SCHEMA子句要求调用者具有系统管理员权限。 对一个待发布表，不能同时通过FOR TABLE和FOR ALL TABLES IN SCHEMA方式添加到同一个发布中。

语法格式 1 2 3 4 CREATE PUBLICATION name [ FOR ALL TABLES | FOR publication_object [, ... ] ] [ WITH ( publication_parameter [=value] [, ... ] ) ]; 其中发布对象publication_object为： TABLE table_name [, ...] | ALL TABLES IN SCHEMA schema_name [, ... ]

参数说明 表1 CREATE TYPE参数说明 参数 描述 取值范围 name 要创建的类型的名称，可以用模式修饰。 需符合标识符命名规范。 attribute_name 复合类型的一个属性（列）的名称。 - data_type 要成为复合类型的一个列的现有数据类型的名称。 - collation 与复合类型的列关联的现有排序规则的名称。 - input_function 将数据从类型的外部文本形式转换为内部形式的函数名。 输入函数可以被声明为有一个cstring类型的参数，或者有三个类型分别为cstring、 oid、integer的参数。 cstring参数是以C字符串存在的输入文本。 oid参数是该类型自身的OID（对于数组类型则是其元素类型的OID）。 integer参数是目标列的typmod（如果知道，不知道则将传递 -1）。 输入函数必须返回一个该数据类型本身的值。通常，一个输入函数应该被声明为STRICT。 如果不是这样，在读到一个NULL输入值时，调用输入函数时第一个参数会是NULL。在这种情况下，该函数必须仍然返回NULL，除非调用函数发生了错误（这种情况主要是想支持域输入函数，域输入函数可能需要拒绝NULL输入）。 - output_function 将数据从类型的内部形式转换为外部文本形式的函数名。 输出函数必须被声明为有一个新数据类型的参数。输出函数必须返回类型cstring。对于NULL值不会调用输出函数。 - receive_function（可选参数） 将数据从类型的外部二进制形式转换成内部形式的函数名。 如果没有该函数，该类型不能参与到二进制输入中。二进制表达转换成内部形式代价更低，然而却更容易移植（例如，标准的整数数据类型使用网络字节序作为外部二进制表达，而内部表达是机器本地的字节序）。receive_function应该执行足够的检查以确保该值是有效的。 接收函数可以被声明为有一个internal类型的参数，或者有三个类型分别为internal、oid、integer的参数。 internal参数是一个指向StringInfo缓冲区的指针，其中保存着接收到的字节串。 oid和integer参数和文本输入函数的相同。 接收函数必须返回一个该数据类型本身的值。通常，一个接收函数应该被声明为STRICT。如果不是这样，在读到一个NULL输入值时调用接收函数时第一个参数会是NULL。在这种情况下，该函数必须仍然返回NULL，除非接收函数发生了错误（这种情况主要是想支持域接收函数，域接收函数可能需要拒绝NULL输入）。 - send_function（可选参数） 将数据从类型的内部形式转换为外部二进制形式的函数名。 如果没有该函数，该类型将不能参与到二进制输出中。发送函数必须被声明为有一个新数据类型的参数。发送函数必须返回类型bytea。对于NULL值不会调用发送函数。 - type_modifier_input_function（可选参数）参数。 将类型的修饰符数组转换为内部形式的函数名。 - type_modifier_output_function（可选参数） 将类型的修饰符的内部形式转换为外部文本形式的函数名。 - analyze_function（可选参数） 为该数据类型执行统计分析的函数名的可选参数。 默认情况下，如果该类型有一个默认的B-tree操作符类，ANALYZE将尝试用类型的“equals”和“less-than”操作符来收集统计信息。这种行为对于非标量类型并不合适，因此可以通过指定一个自定义分析函数来覆盖这种行为。分析函数必须被声明为有一个类型为internal的参数，并且返回一个boolean结果。 - internallength（可选参数） 一个数字常量，用于指定新类型的内部表达的字节长度。默认为变长。 虽然只有I/O函数和其他为该类型创建的函数才知道新类型的内部表达的细节， 但是内部表达的一些属性必须被向 GaussDB (DWS)声明。其中最重要的是internallength。基本数据类型可以是定长的（这种情况下internallength是一个正整数）或者是变长的（把internallength设置为VARIABLE，在内部通过把typlen设置为-1表示）。所有变长类型的内部表达都必须以一个4字节整数开始，internallength定义了总长度。 - PASSEDBYVALUE（可选参数） 表示此数据类型的值需要被传值而不是传引用。传值的类型必须是定长的，并且它们的内部表达不能超过Datum类型（某些机器上是4字节，其他机器上是8字节）的尺寸。 - alignment（可选参数） 该参数指定数据类型的存储对齐需求。如果被指定，必须是char、int2、int4或者double。默认是int4。 允许的值等同于以1、2、4或8字节边界对齐。要注意变长类型的alignment参数必须至少为4，因为它们需要包含一个int4作为它们的第一个组成部分。 - storage（可选参数） 该数据类型的存储策略。 如果被指定，必须是plain、external、extended或者main。 默认是plain。 plain指定该类型的数据将总是被存储在线内并且不会被压缩。（对定长类型只允许plain） extended指定系统将首先尝试压缩一个长的数据值，并且将在数据仍然太长的情况下把值移出主表行。 external允许值被移出主表， 但是系统将不会尝试对它进行压缩。 main允许压缩，但是不鼓励把值移出主表（如果没有其他办法让行的大小变得合适，具有这种存储策略的数据项仍将被移出主表，但比起extended以及external项来，这种存储策略的数据项会被优先考虑保留在主表中）。 除plain之外所有的storage值都暗示该数据类型的函数能处理被TOAST过的值。指定的值仅决定一种可TOAST数据类型的列的默认TOAST存储策略，用户可以使用ALTER TABLE SET STORAGE为列选取其他策略。 - like_type（可选参数） 与新类型具有相同表达的现有数据类型的名称。会从这个类型中复制internallength、 passedbyvalue、 alignment以及storage的值（ 除非在这个CREATE TYPE命令的其他地方用显式说明覆盖）。 当新类型的底层实现是以一种现有的类型为参考时，用这种方式指定表达特别有用。 - category（可选参数） 此类型的分类码（单个ASCII字符）。 默认为'U'，表示用户定义类型。为了创建自定义分类， 也可以选择其他ASCII字符。 - preferred（可选参数） 如果此类型是其类型分类中的优先类型则为TRUE，否则为FALSE。默认为FALSE。在现有类型分类中创建新的优先类型要非常谨慎， 因为这可能会导致很大的改变。 - default（可选参数） 数据类型的默认值。如果被省略，默认值是空。 如果用户希望该数据类型的列被默认为某种非空值，可以指定一个默认值。默认值可以用DEFAULT关键词指定（这样一个默认值可以被附加到一个特定列的显式DEFAULT子句覆盖）。 - element（可选参数） 被创建的类型是一个数组，element指定了数组元素的类型。例如，要定义一个4字节整数的数组（int4）， 应指定ELEMENT = int4。 - delimiter（可选参数） 指定此类型组成的数组中分隔值的定界符。 可以把delimiter设置为一个特定字符，默认的定界符是逗号（,）。注意定界符是与数组元素类型相关的，而不是数组类型本身相关。 - collatable（可选参数） 如果此类型的操作可以使用排序规则信息，则为TRUE。默认为FALSE。 如果collatable为TRUE，此类型的列定义和表达式可能通过使用COLLATE子句携带有排序规则信息。在该类型上操作的函数的实现负责真正利用这些信息，仅把类型标记为可排序的并不会让它们自动地去使用这类信息。 - label（可选参数） 与枚举类型的一个值相关的文本标签，其值为长度不超过64个字符的非空字符串。 -

注意事项 如果给定一个模式名，那么该类型将被创建在指定的模式中，否则它会被创建在当前模式中。类型名称必须与同一个模式中任何现有的类型或者域有所区别（因为表具有相关的数据类型，类型名称也必须与同一个模式中任何现有表的名字不同）。 自定义基本类型时，参数可以以任意顺序出现，input_function和output_function为必选参数，其它为可选参数。 category和preferred参数可以被用来帮助控制在混淆的情况下应用哪一种隐式造型。每一种数据类型都属于一个用单个ASCII字符命名的分类，并且每一种类型可以是其所属分类中的“首选”。当有助于解决重载函数或操作符时，解析器将优先造型到首选类型（但是只能从同类的其他类型造型）。对于没有隐式转换到或来自任意其他类型的类型，让这些设置保持默认即可。不过，对于有隐式转换的相关类型的组，把它们都标记为属于同一个类别并且选择一种或两种“最常用”的类型作为该类别的首选通常是很有用的。在把一种用户定义的类型增加到一个现有的内建类别（例如，数字或者字符串类型）中时，category参数特别有用。不过，也可以创建新的全部是用户定义类型的类别。对这样的类别，可选择除大写字母之外的任何ASCII字符。 如果类型支持修饰符（附加在类型声明上的可选约束，例如，char(5)或numeric(30,2)），则需要可选的type_modifier_input_function和type_modifier_output_function。GaussDB(DWS)允许用户定义的类型有一个或者多个简单常量或者标识符作为修饰符。不过，为了存储在系统目录中，该信息必须能被打包到一个非负整数值中。所声明的修饰符会被以cstring数组的形式传递给type_modifier_input_function。type_modifier_input_function必须检查该值的合法性（如果值错误就抛出一个错误），如果值正确，要返回一个非负integer值，该值将被存储在“typmod”列中。如果类型没有type_modifier_input_function则类型修饰符将被拒绝。type_modifier_output_function把内部的整数typmod值转换回正确的形式用于用户显示。type_modifier_output_function必须返回一个cstring值，该值就是追加到类型名称后的字符串。例如，numeric的函数可能会返回(30,2)。如果默认的显示格式就是只把存储的typmod整数值放在圆括号内，则允许省略type_modifier_output_function。 输入和输出函数能被声明为具有新类型的结果或参数是因为：必须在创建新类型之前创建这两个函数。而新类型应该首先被定义为一种shell type，它是一种占位符类型，除了名称和拥有者之外它没有其他属性。这可以通过不带额外参数的命令CREATE TYPE name做到。然后用C写的I/O函数可以被定义为引用这种shell type。最后，用带有完整定义的CREATE TYPE把该shell type替换为一个完全的、合法的类型定义，之后新类型就可以正常使用了。

语法格式 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 CREATE TYPE name AS ( [ attribute_name data_type [ COLLATE collation ] [, ... ] ] ) CREATE TYPE name ( INPUT = input_function, OUTPUT = output_function [ , RECEIVE = receive_function ] [ , SEND = send_function ] [ , TYPMOD_IN = type_modifier_input_function ] [ , TYPMOD_OUT = type_modifier_output_function ] [ , ANALYZE = analyze_function ] [ , INTERNALLENGTH = { internallength | VARIABLE } ] [ , PASSEDBYVALUE ] [ , ALIGNMENT = alignment ] [ , STORAGE = storage ] [ , LIKE = like_type ] [ , CATEGORY = category ] [ , PREFERRED = preferred ] [ , DEFAULT = default ] [ , ELEMENT = element ] [ , DELIMITER = delimiter ] [ , COLLATABLE = collatable ] ) CREATE TYPE name CREATE TYPE name AS ENUM ( [ 'label' [, ... ] ] )

功能描述 在当前数据库中定义新的数据类型。定义数据类型的用户将成为该数据类型的所有者。定义类型只适用于行存表。 有四种形式的CREATE TYPE，分别为：复合类型、基本类型、shell类型和枚举类型。 复合类型 复合类型由一个属性名和数据类型的列表指定。如果属性的数据类型是可排序的，也可以指定该属性的排序规则。复合类型本质上和表的行类型相同，但是如果只想定义一种类型，使用CREATE TYPE避免了创建一个实际的表。单独的复合类型也是很有用的，例如可以作为函数的参数或者返回类型。 为了能够创建复合类型，必须拥有在其所有属性类型上的USAGE特权。 基本类型 用户可以自定义一种新的基本类型（标量类型）。通常来说这些函数必须是用C或者另外一种底层语言所编写。 shell类型 shell类型是一种用于后面要定义的类型的占位符，通过发出一个只带类型名参数的CREATE TYPE命令来创建该类型。在创建基本类型时，需要shell类型作为一种向前引用。 枚举类型 由若干个标签构成的列表，每一个标签值都是一个非空字符串，且字符串长度必须不超过64个字节。

注意事项 如果仅指定了解析器，则新的文本搜索配置初始没有从标记类型到词典的映射， 因此会忽略所有的单词。后面必须使用ALTER TEXT SEARCH CONFIGURATION命令创建映射使配置生效。如果指定了COPY选项，则会自动拷贝指定的文本搜索配置的解析器、映射、配置选项等信息。 如果指定了模式名称，则会在指定模式中创建文本搜索配置，否则会在当前模式中创建。 定义文本搜索配置的用户成为其所有者。 PARSER和COPY选项是互斥的，因为复制现有配置时，其解析器配置也会被复制。

参数说明 表1 CREATE TEXT SEARCH CONFIGURATION参数说明 参数 描述 取值范围 name 要创建的文本搜索配置的名称，可以用模式修饰。 字符串，需符合标识符命名规范。 parser_name 用于该配置的文本搜索解析器的名称。 - source_config 要复制的现有文本搜索配置的名称。 - configuration_option 文本搜索配置的配置参数，主要是针对parser_name执行的解析器，或者source_config隐含的解析器而言的。 目前共支持default、ngram、zhparser三种类型的解析器。 default类型的解析器没有对应的configuration_option。 ngram、zhparser类型解析器对应的configuration_option如表2所示。 表2 ngram、zhparser类型解析器对应的配置参数 解析器 配置参数 参数描述 取值范围 ngram gram_size 分词长度。 正整数，1~4。 默认值：2。 punctuation_ignore 是否忽略标点符号。 true（默认值）：忽略标点符号。 false：不忽略标点符号。 grapsymbol_ignore 是否忽略图形化字符。 true：忽略图形化字符。 false（默认值）：不忽略图形化字符。 zhparser punctuation_ignore 分词结果是否忽略所有的标点等特殊符号（不会忽略\r和\n）。 true（默认值）：忽略所有的标点等特殊符号。 false：不忽略所有的标点等特殊符号。 seg_with_duality 是否将闲散文字自动以二字分词法聚合。 true：将闲散文字自动以二字分词法聚合。 false（默认值）：不将闲散文字自动以二字分词法聚合。 multi_short 分词执行时是否执行针对长词复合切分。 true（默认值）：执行针对长词复合切分。 false：不执行针对长词复合切分。 multi_duality 设定是否将长词内的文字自动以二字分词法聚合。 true：将长词内的文字自动以二字分词法聚合。 false（默认值）：不将长词内的文字自动以二字分词法聚合。 multi_zmain 是否将重要单字单独显示。 true：将重要单字单独显示。 false（默认值）：不将重要单字单独显示。 multi_zall 是否将全部单字单独显示。 true：将全部单字单独显示。 false（默认值）：不将全部单字单独显示。

示例 创建名为ngram1的文本搜索配置，使用ngram解析器。 1 CREATE TEXT SEARCH CONFIGURATION ngram1 (parser=ngram) WITH (gram_size = 2, grapsymbol_ignore = false); 创建名为ngram2的文本搜索配置，复制现有配置ngram1的所有参数。 1 CREATE TEXT SEARCH CONFIGURATION ngram2 (copy=ngram1); 创建名为english_1的文本搜索配置，使用默认文本解析器。 1 CREATE TEXT SEARCH CONFIGURATION english_1 (parser=default);

参数说明 表1 DROP TYPE参数说明 参数 描述 取值范围 IF EXISTS 如果指定的类型不存在，那么发出一个notice而不是抛出一个错误。 - name 要删除的类型名（可以有模式修饰）。 已存在的类型名。 CASCADE | RESTRICT 当执行删除操作时，如何处理依赖对象中的相关数据。 CASCADE：级联删除依赖该类型的对象（比如字段、函数、操作符等）。 RESTRICT：如果有依赖对象，则拒绝删除该类型（缺省行为）。

语法格式 根据指定的名字创建模式： 1 2 3 4 CREATE SCHEMA schema_name [ AUTHORIZATION user_name ] [ WITH PERM SPACE 'space_limit'] [ schema_element [ ... ] ]; CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION user_name ] [ WITH PERM SPACE 'space_limit'] ; 根据用户名创建模式： 1 2 CREATE SCHEMA AUTHORIZATION user_name [ WITH PERM SPACE 'space_limit'] [ schema_element [ ... ] ]; CREATE SCHEMA IF NOT EXISTS AUTHORIZATION user_name [ WITH PERM SPACE 'space_limit'] ;

参数说明 表1 CREATE SCHEMA参数说明 参数 描述 取值范围 schema_name 模式名字。 字符串，要符合标识符的命名规范。参见标识符命名规范。 须知： 模式名不能和当前数据库里其他的模式重名。 模式的名字不可以“pg_”开头。 AUTHORIZATION user_name 指定模式的所有者。当不指定schema_name时，把user_name当作模式名，此时user_name只能是角色名。 取值范围：已存在的用户名/角色名。 WITH PERM SPACE 'space_limit' 指定模式的永久表存储空间上限。当不指定space_limit时，则不限制。 取值范围：字符串格式为正整数+单位，单位当前支持K/M/G/T/P。解析后的数值以K为单位，且范围不能够超过64比特表示的有符号整数，即1KB~9007199254740991KB。 schema_element 在模式里创建对象的SQL语句。目前仅支持CREATE TABLE、CREATE VIEW、CREATE INDEX、CREATE PARTITION、GRANT子句。 子命令所创建的对象都被AUTHORIZATION子句指定的用户所拥有。 - IF NOT EXISTS 指定IF NOT EXISTS时，若不存在同名SCHEMA，则可以成功创建SCHEMA。若已存在同名SCHEMA，创建时不会报错，仅会提示该SCHEMA已存在，且不执行任何操作。当使用该参数时，不能包括Schema_element子命令。 IF NOT EXISTS参数仅9.1.0及以上版本支持。 说明： 如果当前搜索路径上的模式中存在同名对象时，需要明确指定引用对象所在的模式。可以通过命令SHOW SEARCH_PATH来查看当前搜索路径上的模式。

示例 创建一个角色role1： 1 CREATE ROLE role1 IDENTIFIED BY '{password}'; 为用户role1创建一个同名schema，子命令创建的表films和winners的拥有者为role1： 1 2 3 4 CREATE SCHEMA AUTHORIZATION role1 CREATE TABLE films (title text, release date, awards text[]) CREATE VIEW winners AS SELECT title, release FROM films WHERE awards IS NOT NULL;

POSIX正则表达式 正则表达式是一个字符序列，它是定义一个串集合 （一个正则集）的缩写。 如果一个串是正则表达式描述的正则集中的一员时， 那么就说这个串匹配该正则表达式。 POSIX正则表达式提供了比LIKE和SIMILAR TO操作符更强大的含义。表2列出了所有可用于POSIX正则表达式模式匹配的操作符。 表2 正则表达式匹配操作符 操作符 描述 例子 ~ 匹配正则表达式，大小写敏感 'thomas' ~ '.*thomas.*' ~* 匹配正则表达式，大小写不敏感 'thomas' ~* '.*Thomas.*' ！~ 不匹配正则表达式，大小写敏感 'thomas' !~ '.*Thomas.*' ！~* 不匹配正则表达式，大小写不敏感 'thomas' !~* '.*vadim.*' 匹配规则 与LIKE不同，正则表达式允许匹配串里的任何位置，除非该正则表达式显式地挂接在串的开头或者结尾。 除了上文提到的元字符外， POSIX正则表达式还支持下表的模式匹配元字符。 表3 模式匹配元字符 元字符 含义 ^ 表示串开头的匹配。 $ 表示串末尾的匹配。 . 匹配任意单个字符。 正则表达式函数 POSIX正则表达式支持下面函数。 substring(string from pattern)函数提供了抽取一个匹配POSIX正则表达式模式的子串的方法。 regexp_replace(string, pattern, replacement [,flags ])函数提供了将匹配POSIX正则表达式模式的子串替换为新文本的功能。 regexp_matches(string text, pattern text [, flags text])函数返回一个文本数组，该数组由匹配一个POSIX正则表达式模式得到的所有被捕获子串构成。 regexp_split_to_table(string text, pattern text [, flags text])函数把一个POSIX正则表达式模式当作一个定界符来分离一个串。 regexp_split_to_array(string text, pattern text [, flags text ])和regexp_split_to_table类似，是一个正则表达式分离函数，不过它的结果以一个text数组的形式返回。 正则表达式分离函数会忽略零长度的匹配，这种匹配发生在串的开头或结尾或者正好发生在前一个匹配之后。这和正则表达式匹配的严格定义是相悖的，后者由regexp_matches实现，但是通常前者是实际中最常用的行为。 示例 1 2 3 4 5 SELECT 'abc' ~ 'Abc' AS RESULT; result -------- f (1 row) 1 2 3 4 5 SELECT 'abc' ~* 'Abc' AS RESULT; result -------- t (1 row) 1 2 3 4 5 SELECT 'abc' !~ 'Abc' AS RESULT; result -------- t (1 row) 1 2 3 4 5 SELECT 'abc'!~* 'Abc' AS RESULT; result -------- f (1 row) 1 2 3 4 5 SELECT 'abc' ~ '^a' AS RESULT; result -------- t (1 row) 1 2 3 4 5 SELECT 'abc' ~ '(b|d)'AS RESULT; result -------- t (1 row) 1 2 3 4 5 SELECT 'abc' ~ '^(b|c)'AS RESULT; result -------- f (1 row) 虽然大部分的正则表达式搜索都能很快地执行，但是正则表达式仍可能被人为地控制，通过任意长的时间和任意量的内存进行处理。不建议接受非安全来源的正则表达式搜索模式，如果必须这样做，建议加上语句超时限制。使用SIMILAR TO模式的搜索具有同样的安全性危险， 因为SIMILAR TO提供了很多和POSIX-风格正则表达式相同的能力。LIKE搜索比其他两种选项简单得多，因此在接受非安全来源的搜索模式时要更安全些。

SIMILAR TO SIMILAR TO操作符根据自己的模式判断是否匹配给定串而返回真或者假。它和LIKE非常类似，只不过它使用SQL标准定义的正则表达式理解模式。 匹配规则 和LIKE一样，SIMILAR TO操作符只有在它的模式匹配整个串的时候才返回真。如果要匹配在串内任何位置的序列，该模式必须以百分号开头和结尾。 下划线 （_）代表（匹配）任何单个字符； 百分号（%）代表任意串的通配符。 SIMILAR TO也支持下面这些从POSIX正则表达式借用的模式匹配元字符。 表1 模式匹配元字符 元字符 含义 | 表示选择（两个候选之一）。 * 表示重复前面的项零次或更多次。 + 表示重复前面的项一次或更多次。 ? 表示重复前面的项零次或一次。 {m} 表示重复前面的项刚好m次。 {m,} 表示重复前面的项m次或更多次。 {m,n} 表示重复前面的项至少m次并且不超过n次。 () 把多个项组合成一个逻辑项。 [...] 声明一个字符类，就像POSIX正则表达式一样。 前导逃逸字符可以禁止所有这些元字符的特殊含义。逃逸字符的使用规则和LIKE一样。 注意事项 如果SIMILAR TO正则表达式重复匹配字符数量非常庞大，由于受递归大小限制，执行语句会失败并报错invalid regular expression: regular expression is too complex，可尝试调大GUC参数max_stack_depth。 正则表达式函数 支持使用函数substring(string from pattern for escape)截取匹配SQL正则表达式的子字符串。 示例 1 2 3 4 5 SELECT 'abc' SIMILAR TO 'abc' AS RESULT; result ----------- t (1 row) 1 2 3 4 5 SELECT 'abc' SIMILAR TO 'a' AS RESULT; result ----------- f (1 row) 1 2 3 4 5 SELECT 'abc' SIMILAR TO '%(b|d)%' AS RESULT; result ----------- t (1 row) 1 2 3 4 5 SELECT 'abc' SIMILAR TO '(b|c)%' AS RESULT; result ----------- f (1 row)

LIKE 判断字符串是否能匹配上LIKE后的模式字符串。如果字符串与提供的模式匹配，则LIKE表达式返回为真（NOT LIKE表达式返回假），否则返回为假（NOT LIKE表达式返回真）。 匹配规则 此操作符只有在它的模式匹配整个串的时候才能成功。如果要匹配在串内任何位置的序列，该模式必须以百分号开头和结尾。 下划线 （_）代表（匹配）任何单个字符； 百分号（%）代表任意串的通配符。 要匹配文本里的下划线（_）或者百分号（%），在提供的模式里相应字符必须前导逃逸字符。逃逸字符的作用是禁用元字符的特殊含义，缺省的逃逸字符是反斜线，也可以用ESCAPE子句指定一个不同的逃逸字符。 要匹配逃逸字符本身，需写两个逃逸字符。例如要写一个包含反斜线的模式常量，那就要在SQL语句里写两个反斜线。 参数standard_conforming_strings设置为off时，在文串常量中写的任何反斜线都需要被双写。因此写一个匹配单个反斜线的模式实际上要在语句里写四个反斜线。可通过用ESCAPE选择一个不同的逃逸字符来避免这种情况，这样反斜线就不再是LIKE的特殊字符了。但仍然是字符文本分析器的特殊字符，所以还是需要两个反斜线。也可通过写ESCAPE ''的方式不选择逃逸字符，这样可以有效地禁用逃逸机制，但是没有办法关闭下划线和百分号在模式中的特殊含义。 关键字ILIKE可以用于替换LIKE，区别是LIKE大小写敏感，ILIKE大小写不敏感。 操作符~~等效于LIKE，操作符~~*等效于ILIKE。 示例 1 2 3 4 5 SELECT 'abc' LIKE 'abc' AS RESULT; result ----------- t (1 row) 1 2 3 4 5 SELECT 'abc' LIKE 'a%' AS RESULT; result ----------- t (1 row) 1 2 3 4 5 SELECT 'abc' LIKE '_b_' AS RESULT; result ----------- t (1 row) 1 2 3 4 5 SELECT 'abc' LIKE 'c' AS RESULT; result ----------- f (1 row)

参数说明 表1 CREATE PROCEDURE参数说明 参数 描述 取值范围 OR REPLACE 当存在同名的存储过程时，替换原来的定义。 - procedure_name 创建的存储过程名字，可以带有模式名。 字符串，要符合标识符的命名规范。参见标识符命名规范。 argmode 参数的模式。 须知： VARIADIC用于声明数组类型的参数。 取值范围： IN，OUT，INOUT或VARIADIC。缺省值是IN。只有OUT模式的参数后面能跟VARIADIC。并且OUT和INOUT模式的参数不能用在RETURNS TABLE的过程定义中。 argname 参数的名字。 字符串，要符合标识符的命名规范。参见标识符命名规范。 argtype 参数的数据类型。 取值范围：可用的数据类型。 说明： argname和argmode的顺序没有严格要求，推荐按照argname、argmode、argtype的顺序使用。 IMMUTABLE、STABLE等 行为约束可选项。各参数的功能与CREATE FUNCTION类似，详细说明见CREATE FUNCTION。 - plsql_body PL/SQL存储过程体。 须知： 当在存储过程体中进行创建用户等涉及用户密码相关操作时，系统表及csv日志中会记录密码的明文。因此不建议用户在存储过程体中进行涉及用户密码的相关操作。