华为云用户手册

参数说明 nodename 节点名，对应在pgxc_node系统表的一行记录，指定后将修改记录中的nodeis_active字段值。 取值范围：字符串，只支持CN，并且要保证该节点名在pgxc_node系统表中有对应的记录。 status pgxc_node系统表中nodeis_acitve字段的更新值。 取值范围： - FALSE - TRUE nodename1[, nodename2, nodename3 ...] 该SQL执行的节点范围，ALTER COORDINATOR执行时会自动下发到范围内的所有节点，需要包含当前执行节点。 取值范围：字符串，只支持CN，要保证该节点名在pgxc_node系统表中有对应的记录，并且节点状态正常，否则SQL执行失败。

示例 集群有3个CN，cn_5001、cn_5002、cn_5003，均处于正常工作状态。 cn_5001发生故障且满足剔除时间要求后，需要将cn_5001从集群中剔除，执行SQL在cn_5002和cn5003节点上刷新pgxc_node系统表中cn_5001对应记录的nodeis_active为false： ALTER COORDINATOR cn_5001 SET False WITH (cn_5002,cn_5003)。 cn_5001故障解除后，为了在集群中加回cn_5001，执行SQL在cn_5002和cn5003节点上刷新pgxc_node系统表中cn_5001对应记录的nodeis_active为ture： ALTER COORDINATOR cn_5001 SET True WITH (cn_5002,cn_5003)。

示例 1 2 3 4 5 6 7 8 910111213141516171819202122232425262728293031323334353637 --创建表customer_demographics_t1。openGauss=# CREATE TABLE customer_demographics_t1( CD_DEMO_SK INTEGER NOT NULL, CD_GENDER CHAR(1) , CD_MARITAL_STATUS CHAR(1) , CD_EDUCATION_STATUS CHAR(20) , CD_PURCHASE_ESTIMATE INTEGER , CD_CREDIT_RATING CHAR(10) , CD_DEP_COUNT INTEGER , CD_DEP_EMPLOYED_COUNT INTEGER , CD_DEP_COLLEGE_COUNT INTEGER)WITH (ORIENTATION = COLUMN,COMPRESSION=MIDDLE)DISTRIBUTE BY HASH (CD_DEMO_SK);--插入记录。openGauss=# INSERT INTO customer_demographics_t1 VALUES(1920801,'M', 'U', 'DOCTOR DEGREE', 200, 'GOOD', 1, 0,0);--开启事务。openGauss=# START TRANSACTION;--更新字段值。openGauss=# UPDATE customer_demographics_t1 SET cd_education_status= 'Unknown';--终止事务，上面所执行的更新会被撤销掉。openGauss=# ABORT; --查询数据。openGauss=# SELECT * FROM customer_demographics_t1 WHERE cd_demo_sk = 1920801;cd_demo_sk | cd_gender | cd_marital_status | cd_education_status | cd_purchase_estimate | cd_credit_rating | cd_dep_count | cd_dep_employed_count | cd_dep_college_count ------------+-----------+-------------------+----------------------+----------------------+------------------+--------------+-----------------------+---------------------- 1920801 | M | U | DOCTOR DEGREE | 200 | GOOD | 1 | 0 | 0(1 row)--删除表。openGauss=# DROP TABLE customer_demographics_t1;

定义负载组 负载组是负载管理（当前特性是实验室特性，使用时请联系华为工程师提供技术支持）模块使用的系统表，主要用于指定相关联的资源池内可运行并发作业的数量。所涉及的SQL语句，请参考表14。 表14 负载组定义相关SQL 功能 相关SQL 创建负载组 CREATE WORKLOAD GROUP 修改负载组属性 ALTER WORKLOAD GROUP 删除负载组 DROP WORKLOAD GROUP

定义应用映射组 应用映射组是负载管理（当前特性是实验室特性，使用时请联系华为工程师提供技术支持）模块的系统表，主要用于关联负载组。用户连接数据库后，只需要指定负载组即可将执行作业关联到给定的资源。所涉及的SQL语句，请参考表15。 表15 应用映射组定义相关SQL 功能 相关SQL 创建应用映射组 CREATE APP WORKLOAD GROUP MAPPING 修改应用映射组属性 ALTER APP WORKLOAD GROUP MAPPING 删除应用映射组 DROP APP WORKLOAD GROUP MAPPING

定义分区表 分区表是一种逻辑表，数据是由普通表存储的，主要用于提升查询性能。所涉及的SQL语句，请参考表7。 表7 分区表定义相关SQL 功能 相关SQL 创建分区表 CREATE TABLE PARTITION 创建分区 ALTER TABLE PARTITION 修改分区表属性 ALTER TABLE PARTITION 删除分区 ALTER TABLE PARTITION 删除分区表 DROP TABLE

示例 1 2 3 4 5 6 7 8 91011121314151617181920 -- 创建一个资源池，其控制组指定为"DefaultClass"组下属的"High" Timeshare Workload控制组。openGauss=# CREATE RESOURCE POOL pool1 WITH (CONTROL_GROUP="High");-- 创建一个负载组，关联已创建的资源池。openGauss=# CREATE WORKLOAD GROUP wg_hr1 USING RESOURCE POOL pool1;--创建一个默认应用映射组，关联默认的负载组。openGauss=# CREATE APP WORKLOAD GROUP MAPPING app_wg_map1;-- 更新一个应用映射组关联的负载组名称。openGauss=# ALTER APP WORKLOAD GROUP MAPPING app_wg_map1 WITH(WORKLOAD_GPNAME=wg_hr1);--删除应用映射组。openGauss=# DROP APP WORKLOAD GROUP MAPPING app_wg_map1;--删除负载组。openGauss=# DROP WORKLOAD GROUP wg_hr1;--删除资源池。openGauss=# DROP RESOURCE POOL pool1;

语法格式 ALTER AUDIT POLICY [ IF EXISTS ] policy_name { ADD | REMOVE } { [ privilege_audit_clause ] [ access_audit_clause ] };ALTER AUDIT POLICY [ IF EXISTS ] policy_name MODIFY ( filter_group_clause );ALTER AUDIT POLICY [ IF EXISTS ] policy_name DROP FILTER; ALTER AUDIT POLICY [ IF EXISTS ] policy_name COMMENTS policy_comments;ALTER AUDIT POLICY [ IF EXISTS ] policy_name { ENABLE | DISABLE }; privilege_audit_clause： 1 PRIVILEGES { DDL | ALL } access_audit_clause： AC CES S { DML | ALL }

参数说明 policy_name 审计策略名称，需要唯一，不可重复； 取值范围：字符串，要符合标识符的命名规范。 DDL 指的是针对数据库执行如下操作时进行审计，目前支持：ALTER、ANALYZE、COMMENT、CREATE、DROP、GRANT、REVOKE、SET、SHOW、 LOG IN_ACCESS、LOGIN_FAILURE、LOGOUT、LOGIN。 ALL 指的是上述DDL支持的所有对数据库的操作。 DML 指的是针对数据库执行如下操作时进行审计，目前支持：COPY、DEALLOCATE、DELETE_P、EXECUTE、REINDEX、INSERT、PREPARE、SELECT、TRUNCATE、UPDATE。 FILTER_TYPE 指定审计策略的过滤信息，过滤类型包括：IP、ROLES、APP。 filter_value 指具体过滤信息内容。 policy_comments 用于记录策略相关的描述信息。 ENABLE|DISABLE 可以打开或关闭统一审计策略。若不指定ENABLE|DISABLE，语句默认为ENABLE。

age解析器测试 函数ts_parse可以直接测试文本搜索解析器。 12 ts_parse(parser_name text, document text, OUT tokid integer, OUT token text) returns setof record ts_parse解析指定的document并返回一系列的记录，一条记录代表一个解析生成的token。每条记录包括标识token类型的tokid，及token文本。例如： 1 2 3 4 5 6 7 8 910 openGauss=# SELECT * FROM ts_parse('default', '123 - a number'); tokid | token-------+-------- 22 | 123 12 | 12 | - 1 | a 12 | 1 | number(6 rows) 函数ts_token_type返回指定解析器的token类型及其描述信息。 12 ts_token_type(parser_name text, OUT tokid integer, OUT alias text, OUT description text) returns setof record ts_token_type返回一个表，这个表描述了指定解析器可以识别的每种token类型。对于每个token类型，表中给出了整数类型的tokid--用于解析器标记对应的token类型；alias——命名分词器命令中的token类型；及简单描述。比如： 1 2 3 4 5 6 7 8 9101112131415161718192021222324252627 openGauss=# SELECT * FROM ts_token_type('default'); tokid | alias | description -------+-----------------+------------------------------------------ 1 | asciiword | Word, all ASCII 2 | word | Word, all letters 3 | numword | Word, letters and digits 4 | email | Email address 5 | url | URL 6 | host | Host 7 | sfloat | Scientific notation 8 | version | Version number 9 | hword_numpart | Hyphenated word part, letters and digits 10 | hword_part | Hyphenated word part, all letters 11 | hword_asciipart | Hyphenated word part, all ASCII 12 | blank | Space symbols 13 | tag | XML tag 14 | protocol | Protocol head 15 | numhword | Hyphenated word, letters and digits 16 | asciihword | Hyphenated word, all ASCII 17 | hword | Hyphenated word, all letters 18 | url_path | URL path 19 | file | File or path name 20 | float | Decimal notation 21 | int | Signed integer 22 | uint | Unsigned integer 23 | entity | XML entity(23 rows) 父主题： 测试和调试文本搜索

词典测试 函数ts_lexize用于进行词典测试。 ts_lexize(dict regdictionary, token text) returns text[]如果输入的token可以被词典识别，那么ts_lexize返回词素的数组；如果token可以被词典识别到它是一个停用词，则返回空数组；如果是一个不可识别的词则返回NULL。 比如： 123456789 openGauss=# SELECT ts_lexize('english_stem', 'stars'); ts_lexize----------- {star}openGauss=# SELECT ts_lexize('english_stem', 'a'); ts_lexize----------- {} ts_lexize函数支持单一token，不支持文本。 父主题： 测试和调试文本搜索

示例 Synonym词典可用于解决语言学相关问题，例如，为避免使单词"Paris"变成"pari"，可在Synonym词典文件中定义一行"Paris paris"，并将该词典放置在预定义的english_stem词典之前。 1 2 3 4 5 6 7 8 91011121314151617181920212223242526272829303132333435363738394041 openGauss=# SELECT * FROM ts_debug('english', 'Paris'); alias | description | token | dictionaries | dictionary | lexemes -----------+-----------------+-------+----------------+--------------+--------- asciiword | Word, all ASCII | Paris | {english_stem} | english_stem | {pari}(1 row)openGauss=# CREATE TEXT SEARCH DICTIONARY my_synonym ( TEMPLATE = synonym, SYNONYMS = my_synonyms, FILEPATH = 'file:///home/dicts/' );openGauss=# ALTER TEXT SEARCH CONFIGURATION english ALTER MAPPING FOR asciiword WITH my_synonym, english_stem;openGauss=# SELECT * FROM ts_debug('english', 'Paris'); alias | description | token | dictionaries | dictionary | lexemes -----------+-----------------+-------+---------------------------+------------+--------- asciiword | Word, all ASCII | Paris | {my_synonym,english_stem} | my_synonym | {paris}(1 row)openGauss=# SELECT * FROM ts_debug('english', 'paris'); alias | description | token | dictionaries | dictionary | lexemes -----------+-----------------+-------+---------------------------+------------+--------- asciiword | Word, all ASCII | Paris | {my_synonym,english_stem} | my_synonym | {paris}(1 row)openGauss=# ALTER TEXT SEARCH DICTIONARY my_synonym ( CASESENSITIVE=true);openGauss=# SELECT * FROM ts_debug('english', 'Paris'); alias | description | token | dictionaries | dictionary | lexemes -----------+-----------------+-------+---------------------------+------------+--------- asciiword | Word, all ASCII | Paris | {my_synonym,english_stem} | my_synonym | {paris}(1 row)openGauss=# SELECT * FROM ts_debug('english', 'paris'); alias | description | token | dictionaries | dictionary | lexemes -----------+-----------------+-------+---------------------------+------------+--------- asciiword | Word, all ASCII | Paris | {my_synonym,english_stem} | my_synonym | {pari}(1 row) 其中，同义词词典文件全名为my_synonyms.syn，所在目录为当前连接CN节点的/home/dicts/下。关于创建词典的语法和更多参数，请参见CREATE TEXT SEARCH DICTIONARY。 星号（*）可用于词典文件中的同义词结尾，表示该同义词是一个前缀。在to_tsvector()中该星号将被忽略，但在to_tsquery()中会匹配该前缀并对应输出结果（参照处理查询一节）。 假设词典文件synonym_sample.syn内容如下： 12345 postgres pgsqlpostgresql pgsql postgre pgsql gogle googl indices index* 创建并使用词典： 1 2 3 4 5 6 7 8 91011121314151617181920212223242526272829303132333435363738 openGauss=# CREATE TEXT SEARCH DICTIONARY syn ( TEMPLATE = synonym, SYNONYMS = synonym_sample);openGauss=# SELECT ts_lexize('syn','indices'); ts_lexize ----------- {index}(1 row)openGauss=# CREATE TEXT SEARCH CONFIGURATION tst (copy=simple);openGauss=# ALTER TEXT SEARCH CONFIGURATION tst ALTER MAPPING FOR asciiword WITH syn;openGauss=# SELECT to_tsvector('tst','indices'); to_tsvector ------------- 'index':1(1 row)openGauss=# SELECT to_tsquery('tst','indices'); to_tsquery ------------ 'index':*(1 row)openGauss=# SELECT 'indexes are very useful'::tsvector; tsvector --------------------------------- 'are' 'indexes' 'useful' 'very'(1 row)openGauss=# SELECT 'indexes are very useful'::tsvector @@ to_tsquery('tst','indices'); ?column? ---------- t(1 row)

操作步骤 创建一个文本搜索配置ts_conf，复制预定义的文本搜索配置english。 12 openGauss=# CREATE TEXT SEARCH CONFIGURATION ts_conf ( COPY = pg_catalog.english );CREATE TEXT SEARCH CONFIGURATION 创建Synonym词典。 假设同义词词典定义文件pg_dict.syn内容如下： 123 postgres pg pgsql pg postgresql pg 执行如下语句创建Synonym词典： 12345 openGauss=# CREATE TEXT SEARCH DICTIONARY pg_dict ( TEMPLATE = synonym, SYNONYMS = pg_dict, FILEPATH = 'file:///home/dicts' ); 创建一个Ispell词典english_ispell（词典定义文件来自开源词典）。 1234567 openGauss=# CREATE TEXT SEARCH DICTIONARY english_ispell ( TEMPLATE = ispell, DictFile = english, AffFile = english, StopWords = english, FILEPATH = 'file:///home/dicts' ); 设置文本搜索配置ts_conf，修改某些类型的token对应的词典列表。关于token类型的详细信息，请参见解析器。 1234 openGauss=# ALTER TEXT SEARCH CONFIGURATION ts_conf ALTER MAPPING FOR asciiword, asciihword, hword_asciipart, word, hword, hword_part WITH pg_dict, english_ispell, english_stem; 在文本搜索配置中，选择设置不索引或搜索某些token类型。 12 openGauss=# ALTER TEXT SEARCH CONFIGURATION ts_conf DROP MAPPING FOR email, url, url_path, sfloat, float; 使用文本检索调测函数ts_debug()对所创建的词典配置ts_conf进行测试。 12345 openGauss=# SELECT * FROM ts_debug('ts_conf', 'PostgreSQL, the highly scalable, SQL compliant, open source object-relationaldatabase management system, is now undergoing beta testing of the nextversion of our software.'); 可以设置当前session使用ts_conf作为默认的文本搜索配置。此设置仅在当前session有效。 1 2 3 4 5 6 7 8 9101112131415161718192021222324252627 openGauss=# \dF+ ts_conf Text search configuration "public.ts_conf"Parser: "pg_catalog.default" Token | Dictionaries -----------------+------------------------------------- asciihword | pg_dict,english_ispell,english_stem asciiword | pg_dict,english_ispell,english_stem file | simple host | simple hword | pg_dict,english_ispell,english_stem hword_asciipart | pg_dict,english_ispell,english_stem hword_numpart | simple hword_part | pg_dict,english_ispell,english_stem int | simple numhword | simple numword | simple uint | simple version | simple word | pg_dict,english_ispell,english_stemopenGauss=# SET default_text_search_config = 'public.ts_conf';SETopenGauss=# SHOW default_text_search_config; default_text_search_config ---------------------------- public.ts_conf(1 row)

注意事项 由于TZ词典需要识别短语，所以在处理过程中必须保存当前状态并与解析器进行交互，以决定是否处理下一个token或是结束当前识别。此外，TZ词典配置时需谨慎，如果设置TZ词典仅处理asciiword类型的token，则类似one 7的分类词典定义将不会生效，因为uint类型的token不会传给TZ词典处理。 在索引期间要用到分类词典，因此分类词典参数中的任何变化都要求重新索引。对于其他大多数类型的词典来说，类似添加或删除停用词这种修改并不需要强制重新索引。

操作步骤 创建一个名为thesaurus_astro的TZ词典。 以一个简单的天文学词典thesaurus_astro为例，其中定义了两组天文短语及其同义词如下： 12 supernovae stars : sn crab nebulae : crab 执行如下语句创建TZ词典： 123456 openGauss=# CREATE TEXT SEARCH DICTIONARY thesaurus_astro ( TEMPLATE = thesaurus, DictFile = thesaurus_astro, Dictionary = pg_catalog.english_stem, FILEPATH = 'file:///home/dicts/'); 其中，词典定义文件全名为thesaurus_astro.ths，所在目录为当前连接CN节点的/home/dicts/下 。子词典pg_catalog.english_stem是预定义的Snowball类型的英语词干词典，用于规范化输入词，子词典自身相关配置（例如停用词等）不在此处显示。关于创建词典的语法和更多参数，请参见CREATE TEXT SEARCH DICTIONARY。 创建词典后，将其绑定到对应文本搜索配置中需要处理的token类型上： 123 openGauss=# ALTER TEXT SEARCH CONFIGURATION russian ALTER MAPPING FOR asciiword, asciihword, hword_asciipart WITH thesaurus_astro, english_stem; 使用TZ词典。 测试TZ词典。 ts_lexize函数对于测试TZ词典作用不大，因为该函数是按照单个token处理输入。可以使用plainto_tsquery、to_tsvector、to_tsquery函数测试TZ词典，这些函数能够将输入分解成多个token（to_tsquery函数需要将输入加上引号）。 1 2 3 4 5 6 7 8 91011121314151617 openGauss=# SELECT plainto_tsquery('russian','supernova star'); plainto_tsquery ----------------- 'sn'(1 row)openGauss=# SELECT to_tsvector('russian','supernova star'); to_tsvector ------------- 'sn':1(1 row)openGauss=# SELECT to_tsquery('russian','''supernova star'''); to_tsquery ------------ 'sn'(1 row) 其中，supernova star匹配了词典thesaurus_astro定义中的supernovae stars，这是因为在thesaurus_astro词典定义中指定了Snowball类型的子词典english_stem，该词典移除了e和s。 如果同时需要索引原始短语，只要将其同时放置在词典定义文件中对应定义的右侧即可，如下： 1 2 3 4 5 6 7 8 91011 supernovae stars : sn supernovae starsopenGauss=# ALTER TEXT SEARCH DICTIONARY thesaurus_astro ( DictFile = thesaurus_astro, FILEPATH = 'file:///home/dicts/');openGauss=# SELECT plainto_tsquery('russian','supernova star'); plainto_tsquery ----------------------------- 'sn' & 'supernova' & 'star'(1 row)

操作步骤 创建Simple词典。 1234 openGauss=# CREATE TEXT SEARCH DICTIONARY public.simple_dict ( TEMPLATE = pg_catalog.simple, STOPWORDS = english); 其中，停用词表文件全名为english.stop。关于创建simple词典的语法和更多参数，请参见CREATE TEXT SEARCH DICTIONARY。 使用Simple词典。 1 2 3 4 5 6 7 8 91011 openGauss=# SELECT ts_lexize('public.simple_dict','YeS'); ts_lexize ----------- {yes}(1 row)openGauss=# SELECT ts_lexize('public.simple_dict','The'); ts_lexize ----------- {}(1 row) 设置参数ACCEPT=false，使Simple词典返回NULL，而不是返回非停用词的小写形式。 1 2 3 4 5 6 7 8 910111213 openGauss=# ALTER TEXT SEARCH DICTIONARY public.simple_dict ( Accept = false );ALTER TEXT SEARCH DICTIONARYopenGauss=# SELECT ts_lexize('public.simple_dict','YeS'); ts_lexize -----------(1 row)openGauss=# SELECT ts_lexize('public.simple_dict','The'); ts_lexize ----------- {}(1 row)

操作步骤 获取词典定义文件和词缀文件。 用户可以使用开源词典（OpenOffice上可以获取），直接获取的开源词典后缀名可能为.aff和.dic，此时需要将扩展名改为.affix和.dict。此外，对于某些词典文件，还需要使用下面的命令把字符转换成UTF-8编码，比如挪威语词典： 12 iconv -f ISO_8859-1 -t UTF-8 -o nn_no.affix nn_NO.aff iconv -f ISO_8859-1 -t UTF-8 -o nn_no.dict nn_NO.dic 创建Ispell词典。 123456 openGauss=# CREATE TEXT SEARCH DICTIONARY norwegian_ispell ( TEMPLATE = ispell, DictFile = nn_no, AffFile = nn_no, FilePath = 'file:///home/dicts'); 其中，词典文件全名为nn_no.dict和nn_no.affix，所在目录为当前连接CN节点的/home/dicts/下。关于创建词典的语法和更多参数，请参见CREATE TEXT SEARCH DICTIONARY。 使用Ispell词典进行复合词拆分。 12345 openGauss=# SELECT ts_lexize('norwegian_ispell', 'sjokoladefabrikk'); ts_lexize --------------------- {sjokolade,fabrikk}(1 row) MySpell不支持复合词，Hunspell对复合词有较好的支持。 GaussDB 仅支持Hunspell中基本的复合词操作。通常情况下，Ispell词典能够识别的词是一个有限集合，其后应该配置一个更广义的词典，例如一个可以识别所有词的Snowball词典。

Snowball词典 Snowball词典模板支持词干分析词典，基于Martin Porter的Snowball项目，内置有许多语言的词干分析算法。GaussDB中预定义有多种语言的Snowball词典，可通过系统表PG_TS_DICT查看预定义的词干分析词典以及支持的语言词干分析算法。 无论是否可以简化，Snowball词典将标示所有输入为已识别，因此它应当被放置在词典列表的最后。把Snowball词典放在任何其他词典前面会导致后继词典失效，因为输入token不会通过Snowball词典进入到下一个词典。 关于Snowball词典的语法，请参见CREATE TEXT SEARCH DICTIONARY。 父主题： 词典

注意事项 大多数词典的功能依赖于词典定义文件，词典定义文件名仅支持小写字母、数字、下划线组合。 临时模式pg_temp下不允许创建词典。 词典定义文件的字符集编码必须为UTF-8格式。实际应用时，如果与数据库的字符编码格式不一致，在读入词典定义文件时会进行编码转换。 通常情况下，每个session仅读取词典定义文件一次，当且仅当在第一次使用该词典时。需要修改词典文件时，可通过ALTER TEXT SEARCH DICTIONARY命令进行词典定义文件的更新和重新加载。

停用词 停用词是很常见的词，几乎出现在每一个文档中，并且没有区分值。因此，在全文搜索的语境下可忽视它们。停用词处理逻辑和词典类型相关。例如，Ispell词典会先对标记进行规范化，然后再查看停用词表，而Snowball词典会最先检查输入标记是否为停用词。 例如，每个英文文本包含像a和the的单词，因此没必要将它们存储在索引中。然而，停用词影响tsvector中的位置，同时位置也会影响相关度： 1234 openGauss=# SELECT to_tsvector('english','in the list of stop words'); to_tsvector---------------------------- 'list':3 'stop':5 'word':6 位置1、2、4是停用词，所以不显示。为包含和不包含停用词的文档计算出的排序是完全不同的： 123456789 openGauss=# SELECT ts_rank_cd (to_tsvector('english','in the list of stop words'), to_tsquery('list & stop')); ts_rank_cd------------ .05openGauss=# SELECT ts_rank_cd (to_tsvector('english','list stop words'), to_tsquery('list & stop')); ts_rank_cd------------ .1 父主题： 词典

排序查询结果 排序试图针对特定查询衡量文档的相关度，从而将众多的匹配文档中相关度最高的文档排在最前。GaussDB提供了两个预置的排序函数。函数考虑了词法，距离，和结构信息；也就是，他们考虑查询词在文档中出现的频率、紧密程度、以及他们出现的地方在文档中的重要性。然而，相关性的概念是模糊的，并且是跟应用强相关的。不同的应用程序可能需要额外的信息来排序，比如，文档的修改时间，内置的排序函数等。也可以开发自己的排序函数或者采用附加因素组合这些排序函数的结果来满足特定需求。 两个预置的排序函数： 1 ts_rank([ weights float4[], ] vector tsvector, query tsquery [, normalization integer ]) returns float4 基于词素匹配率对vector进行排序： 1 ts_rank_cd([ weights float4[], ] vector tsvector, query tsquery [, normalization integer ]) returns float4 该函数需要位置信息的输入。因此它不能在"剥离"tsvector值的情况下运行—它将总是返回零。 对于这两个函数，可选的weights参数提供给词加权重的能力，词的权重大小取决于所加的权值。权重阵列指定在排序时为每类词汇加多大的权重。 {D-weight, C-weight, B-weight, A-weight} 如果没有提供weights，则使用缺省值：{0.1, 0.2, 0.4, 1.0}。 通常的权重是用来标记文档特殊领域的词，如标题或最初的摘要，所以相对于文章主体中的词它们有着更高或更低的重要性。 由于较长的文档有更多的机会包含查询词，因此有必要考虑文档的大小。例如，包含有5个搜索词的一百字文档比包含有5个搜索词的一千字文档相关性更高。两个预置的排序函数都采用了一个整型的标准化选项来定义文档长度是否影响排序及如何影响。这个整型选项控制多个行为，所以它是一个屏蔽字：可以使用|指定一个或多个行为（例如，2|4）。 0（缺省）表示：跟长度大小没有关系 1 表示：排名（rank）除以(文档长度的对数+1) 2表示：排名除以文档的长度 4表示：排名除以两个扩展词间的调和平均距离。只能使用ts_rank_cd实现 8表示：排名除以文档中单独词的数量 16表示：排名除以单独词数量的对数+1 32表示：排名除以排名本身+1 当指定多个标志位时，会按照所列的顺序依次进行转换。 需要特别注意的是，排序函数不使用任何全局信息，所以不可能产生一个某些情况下需要的1%或100%的理想标准值。标准化选项32 (rank/(rank+1))可用于所有规模的从零到一之间的排序，当然，这只是一个表面变化；它不会影响搜索结果的排序。 下面是一个例子，仅选择排名前十的匹配： 1 2 3 4 5 6 7 8 9101112 openGauss=# SELECT id, title, ts_rank_cd(to_tsvector(body), query) AS rank FROM tsearch.pgweb, to_tsquery('america') query WHERE query @@ to_tsvector(body) ORDER BY rank DESC LIMIT 10; id | title | rank ----+---------+------ 11 | Brazil | .2 2 | America | .1 12 | Canada | .1 13 | Mexico | .1(4 rows) 这是使用标准化排序的相同例子： 1 2 3 4 5 6 7 8 9101112 openGauss=# SELECT id, title, ts_rank_cd(to_tsvector(body), query, 32 /* rank/(rank+1) */ ) AS rank FROM tsearch.pgweb, to_tsquery('america') query WHERE query @@ to_tsvector(body) ORDER BY rank DESC LIMIT 10; id | title | rank ----+---------+---------- 11 | Brazil | .166667 2 | America | .0909091 12 | Canada | .0909091 13 | Mexico | .0909091(4 rows) 下面是使用中文分词法排序查询的例子： 1 2 3 4 5 6 7 8 910111213141516171819 openGauss=# CREATE TABLE tsearch.ts_ngram(id int, body text);openGauss=# INSERT INTO tsearch.ts_ngram VALUES(1, '中文');openGauss=# INSERT INTO tsearch.ts_ngram VALUES(2, '中文检索');openGauss=# INSERT INTO tsearch.ts_ngram VALUES(3, '检索中文');--精确匹配openGauss=# SELECT id, body, ts_rank_cd(to_tsvector('ngram',body), query) AS rank FROM tsearch.ts_ngram, to_tsquery('中文') query WHERE query @@ to_tsvector(body); id | body | rank ----+------+------ 1 | 中文 | .1(1 row)--模糊匹配openGauss=# SELECT id, body, ts_rank_cd(to_tsvector('ngram',body), query) AS rank FROM tsearch.ts_ngram, to_tsquery('中文') query WHERE query @@ to_tsvector('ngram',body); id | body | rank ----+----------+------ 3 | 检索中文 | .1 1 | 中文 | .1 2 | 中文检索 | .1(3 rows) 排序要遍历每个匹配的tsvector，因此资源消耗多，可能会因为I/O限制导致排序慢。可是这是很难避免的，因为实际查询中通常会有大量的匹配。 父主题： 控制文本搜索

词典概述 词典用于定义停用词（stop words），即全文检索时不搜索哪些词。 词典还可以用于对同一词的不同形式进行规范化，这样同一个词的不同派生形式都可以进行匹配。规范化后的词称为词位（lexeme）。 除了提高检索质量外，词的规范化和删除停用词可以减少文档tsvector格式的大小， 从而提高性能。词的规范化和删除停用词并不总是具有语言学意义，用户可以根据应用环境在词典定义文件中自定义规范化和删除规则。 一个词典是一个程序，接收标记（token）作为输入，并返回： 如果token在词典中已知，返回对应lexeme数组（注意，一个标记可能对应多个lexeme）。 一个lexeme。（该lexeme为设置了TSL_FILTER标志的lexeme，TSL_FILTER标志为过滤词典内部自动设置，用户不感知。）一个新token会代替输入token被传递给后继词典（当前词典可被称为过滤词典）。 如果token在词典中已知，但它是一个停用词，返回空数组。 如果词典不能识别输入的token，返回NULL。 GaussDB提供了多种语言的预定义字典，同时提供了五种预定义的词典模板，分别是Simple，Synonym，Thesaurus，Ispell，和Snowball，可用于创建自定义参数的新词典。 在使用全文检索时，建议用户： 可以在文本搜索配置中定义一个解析器，以及一组用于处理该解析器的输出标记词典。对于解析器返回的每个标记类型，可以在配置中指定不同的词典列表进行处理。当解析器输出一种类型的标记后，在对应列表的每个字典中会查阅该标记，直到某个词典识别它。如果它被识别为一个停用词， 或者没有任何词典识别，该token将被丢弃，即不被索引或检索到。通常情况下，第一个返回非空结果的词典决定了最终结果，后继词典将不会继续处理。但是一个过滤类型的词典可以依据规则替换输入token，然后将替换后的token传递给后继词典进行处理。 配置字典列表的一般规则是，第一个位置放置一个应用范围最小的、最具体化定义的词典，其次是更一般化定义的词典， 最后是一个普适定义的词典，比如Snowball词干词典或Simple词典。在下面例子中，对于一个针对天文学的文本搜索配置astro_en，可以定义标记类型asciiword（ASCII词）对应的词典列表为：天文术语的Synonym同义词词典， Ispell英语词典和Snowball 英语词干词典。 12 openGauss=# ALTER TEXT SEARCH CONFIGURATION astro_en ADD MAPPING FOR asciiword WITH astro_syn, english_ispell, english_stem; 过滤类型的词典可以放置在词典列表中除去末尾的任何地方，放置在末尾时是无效的。使用这些词典对标记进行部分规范化，可以有效简化后继词典的处理。 父主题： 词典

搜索表 在不使用索引的情况下也可以进行全文检索。 一个简单查询：将body字段中包含america的每一行打印出来。 1 2 3 4 5 6 7 8 91011121314151617181920212223242526272829303132333435363738 openGauss=# DROP SCHEMA IF EXISTS tsearch CASCADE;openGauss=# CREATE SCHEMA tsearch;openGauss=# CREATE TABLE tsearch.pgweb(id int, body text, title text, last_mod_date date);openGauss=# INSERT INTO tsearch.pgweb VALUES(1, 'China, officially the People''s Republic of China (PRC), located in Asia, is the world''s most populous state.', 'China', '2010-1-1');openGauss=# INSERT INTO tsearch.pgweb VALUES(2, 'America is a rock band, formed in England in 1970 by multi-instrumentalists Dewey Bunnell, Dan Peek, and Gerry Beckley.', 'America', '2010-1-1');openGauss=# INSERT INTO tsearch.pgweb VALUES(3, 'England is a country that is part of the United Kingdom. It shares land borders with Scotland to the north and Wales to the west.', 'England', '2010-1-1');openGauss=# INSERT INTO tsearch.pgweb VALUES(4, 'Australia, officially the Commonwealth of Australia, is a country comprising the mainland of the Australian continent, the island of Tasmania, and numerous smaller islands.', 'Australia', '2010-1-1');openGauss=# INSERT INTO tsearch.pgweb VALUES(6, 'Japan is an island country in East Asia.', 'Japan', '2010-1-1');openGauss=# INSERT INTO tsearch.pgweb VALUES(7, 'Germany, officially the Federal Republic of Germany, is a sovereign state and federal parliamentary republic in central-western Europe.', 'Germany', '2010-1-1');openGauss=# INSERT INTO tsearch.pgweb VALUES(8, 'France, is a sovereign state comprising territory in western Europe and several overseas regions and territories.', 'France', '2010-1-1');openGauss=# INSERT INTO tsearch.pgweb VALUES(9, 'Italy officially the Italian Republic, is a unitary parliamentary republic in Europe.', 'Italy', '2010-1-1');openGauss=# INSERT INTO tsearch.pgweb VALUES(10, 'India, officially the Republic of India, is a country in South Asia.', 'India', '2010-1-1');openGauss=# INSERT INTO tsearch.pgweb VALUES(11, 'Brazil, officially the Federative Republic of Brazil, is the largest country in both South America and Latin America.', 'Brazil', '2010-1-1');openGauss=# INSERT INTO tsearch.pgweb VALUES(12, 'Canada is a country in the northern half of North America.', 'Canada', '2010-1-1');openGauss=# INSERT INTO tsearch.pgweb VALUES(13, 'Mexico, officially the United Mexican States, is a federal republic in the southern part of North America.', 'Mexico', '2010-1-1');openGauss=# SELECT id, body, title FROM tsearch.pgweb WHERE to_tsvector('english', body) @@ to_tsquery('english', 'america'); id | body | title ----+-------------------------------------------------------------------------------------------------------------------------+--------- 2 | America is a rock band, formed in England in 1970 by multi-instrumentalists Dewey Bunnell, Dan Peek, and Gerry Beckley. | America 12 | Canada is a country in the northern half of North America. | Canada 13 | Mexico, officially the United Mexican States, is a federal republic in the southern part of North America. | Mexico 11 | Brazil, officially the Federative Republic of Brazil, is the largest country in both South America and Latin America. | Brazil(4 rows) 像America这样的相关词也会被找到，因为这些词都被处理成了相同标准的词条。 上面的查询指定english配置来解析和规范化字符串。当然也可以省略此配置，通过default_text_search_config进行配置设置： 1 2 3 4 5 6 7 8 91011121314 openGauss=# SHOW default_text_search_config; default_text_search_config ---------------------------- pg_catalog.english(1 row)openGauss=# SELECT id, body, title FROM tsearch.pgweb WHERE to_tsvector(body) @@ to_tsquery('america'); id | body | title ----+-------------------------------------------------------------------------------------------------------------------------+--------- 11 | Brazil, officially the Federative Republic of Brazil, is the largest country in both South America and Latin America. | Brazil 2 | America is a rock band, formed in England in 1970 by multi-instrumentalists Dewey Bunnell, Dan Peek, and Gerry Beckley. | America 12 | Canada is a country in the northern half of North America. | Canada 13 | Mexico, officially the United Mexican States, is a federal republic in the southern part of North America. | Mexico(4 rows) 一个复杂查询：检索出在title或者body字段中包含north和america的最近10篇文档： 123456 openGauss=# SELECT title FROM tsearch.pgweb WHERE to_tsvector(title || ' ' || body) @@ to_tsquery('north & america') ORDER BY last_mod_date DESC LIMIT 10; title -------- Mexico Canada(2 rows) 为了清晰，举例中没有调用coalesce函数在两个字段中查找包含NULL的行。 以上例子均在没有索引的情况下进行查询。对于大多数应用程序来说，这个方法很慢。因此除了偶尔的特定搜索，文本搜索在实际使用中通常需要创建索引。 父主题： 表和索引

处理查询 GaussDB提供了函数和操作符用来操作tsquery类型的查询。 tsquery && tsquery 返回两个给定查询tsquery的与结果。 tsquery || tsquery 返回两个给定查询tsquery的或结果。 !! tsquery 返回给定查询tsquery的非结果。 numnode(query tsquery) returns integer 返回tsquery中的节点数目（词素加操作符），这个函数在检查查询是否有效（返回值大于0），或者只包含停用词（返回值等于0）时，是有用的。例如： 1 2 3 4 5 6 7 8 91011 openGauss=# SELECT numnode(plainto_tsquery('the any'));NOTICE: text-search query contains only stop words or doesn't contain lexemes, ignoredCONTEXT: referenced column: numnode numnode --------- 0openGauss=# SELECT numnode('foo & bar'::tsquery); numnode--------- 3 querytree(query tsquery) returns text 返回可用于索引搜索的tsquery部分，该函数对于检测非索引查询是有用的（例如只包含停用词或否定项）。例如: 12345 openGauss=# SELECT querytree(to_tsquery('!defined')); querytree ----------- T(1 row) 父主题： 附加功能

处理tsvector GaussDB提供了用来操作tsvector类型的函数和操作符。 tsvector || tsvector tsvector连接操作符返回一个新的tsvector类型，它综合了两个tsvector中词素和位置信息，并保留词素的位置信息和权重标签。右侧的tsvector的起始位置位于左侧tsvector的最后位置，因此，新生成的tsvector几乎等同于将两个原始文档字串连接后进行to_tsvector操作。（这个等价是不准确的，因为任何从左边tsvector中删除的停用词都不会影响结果，但是，在使用文本连接时，则会影响词素在右侧tsvector中的位置。） 相较于对文本进行连接后再执行to_tsvector操作，使用tsvector类型进行连接操作的优势在于，可以对文档的不同部分使用不同配置进行解析。因为setweight函数会对给定的tsvector中的语素进行统一设置，如果想要对文档的不同部分设置不同的权重，需要在连接之前对文本进行解析和权重设置。 setweight(vector tsvector, weight "char") returns tsvector setweight返回一个输入tsvector的副本，其中每一个位置都使用给定的权重做了标记。权值可以为A、B、C或D（D是tsvector副本的默认权重，并且不在输出中呈现）。当对tsvector进行连接操作时，这些权重标签将会被保留，文档不同部分以不同的权重进行排序。 权重标签作用于位置，而不是词素。如果传入的tsvector已经被剥离了位置信息，那么setweight函数将什么都不做。 length(vector tsvector) returns integer 返回vector中的词素的数量。 strip(vector tsvector) returns tsvector 返回一个tsvector类型，其中包含输入的tsvector的同义词，但不包含任何位置和权重信息。虽然在相关性排序中，这里返回的tsvector要比未拆分的tsvector的作用小很多，但它通常都比未拆分的tsvector小的多。 父主题： 附加功能

解析查询 GaussDB提供了函数to_tsquery和plainto_tsquery将查询转换为tsquery数据类型，to_tsquery提供比plainto_tsquery更多的功能，但对其输入要求更严格。 to_tsquery([ config regconfig, ] querytext text) returns tsquery to_tsquery从querytext中创建一个tsquery，querytext必须由布尔运算符& (AND)，| (OR)和! (NOT)分割的单个token组成。这些运算符可以用圆括弧分组。换句话说，to_tsquery输入必须遵循tsquery输入的通用规则，具体请参见文本搜索类型。不同的是基本tsquery以token表面值作为输入，而to_tsquery使用指定或默认分词器将每个token标准化成词素，并依据分词器丢弃属于停用词的token。例如： 12345 openGauss=# SELECT to_tsquery('english', 'The & Fat & Rats'); to_tsquery --------------- 'fat' & 'rat'(1 row) 像在基本tsquery中的输入一样，weight(s)可以附加到每个词素来限制它只匹配那些有相同weight(s)的tsvector词素。比如： 12345 openGauss=# SELECT to_tsquery('english', 'Fat | Rats:AB'); to_tsquery ------------------ 'fat' | 'rat':AB(1 row) 同时，*也可以附加到词素来指定前缀匹配： 12345 openGauss=# SELECT to_tsquery('supern:*A & star:A*B'); to_tsquery -------------------------- 'supern':*A & 'star':*AB(1 row) 这样的词素将匹配tsquery中指定字符串和权重的项。 plainto_tsquery([ config regconfig, ] querytext text) returns tsquery plainto_tsquery将未格式化的文本querytext变换为tsquery。类似于to_tsvector，文本被解析并且标准化，然后在存在的词之间插入&(AND)布尔算子。 比如： 12345 openGauss=# SELECT plainto_tsquery('english', 'The Fat Rats'); plainto_tsquery ----------------- 'fat' & 'rat'(1 row) 请注意，plainto_tsquery无法识别布尔运算符、权重标签，或在其输入中的前缀匹配标签： 12345 openGauss=# SELECT plainto_tsquery('english', 'The Fat & Rats:C'); plainto_tsquery --------------------- 'fat' & 'rat' & 'c'(1 row) 在这里，所有输入的标点符号作为空格符号丢弃。 父主题： 控制文本搜索

创建索引 为了加速文本搜索，可以创建GIN索引。 1 openGauss=# CREATE INDEX pgweb_idx_1 ON tsearch.pgweb USING gin(to_tsvector('english', body)); to_tsvector()函数有两个版本。只输一个参数的版本和输两个参数的版本。只输一个参数时，系统默认采用default_text_search_config所指定的分词器。 请注意：创建索引时必须使用to_tsvector的两参数版本。只有指定了分词器名称的全文检索函数才可以在索引表达式中使用。这是因为索引的内容必须不受default_text_search_config的影响，否则索引内容可能不一致。由于default_text_search_config的值可以随时调整，从而导致不同条目生成的tsvector采用了不同的分词器，并且没有办法区分究竟使用了哪个分词器。正确地转储和恢复这样的索引也是不可能的。 因为在上述创建索引中to_tsvector使用了两个参数，只有当查询时也使用了两个参数，且参数值与索引中相同时，才会使用该索引。也就是说，WHERE to_tsvector('english', body) @@ 'a & b' 可以使用索引，但WHERE to_tsvector(body) @@ 'a & b'不能使用索引。这确保只使用这样的索引——索引各条目是使用相同的分词器创建的。 索引中的分词器名称由另一列指定时可以建立更复杂的表达式索引。例如： 1 openGauss=# CREATE INDEX pgweb_idx_2 ON tsearch.pgweb USING gin(to_tsvector('ngram', body)); 其中body是pgweb表中的一列。当对索引的各条目使用了哪个分词器进行记录时，允许在同一索引中存在混合分词器。在某些场景下这将是有用的。例如，文档集合中包含不同语言的文档时。再次强调，打算使用索引的查询必须措辞匹配，例如，WHERE to_tsvector(config_name, body) @@ 'a & b'与索引中的to_tsvector措辞匹配。 索引甚至可以连接列： 1 openGauss=# CREATE INDEX pgweb_idx_3 ON tsearch.pgweb USING gin(to_tsvector('english', title || ' ' || body)); 另一个方法是创建一个单独的tsvector列控制to_tsvector的输出。下面的例子是title和body的连接， 当其它是NULL的时候，使用coalesce确保一个字段仍然会被索引： 12 openGauss=# ALTER TABLE tsearch.pgweb ADD COLUMN textsearchable_index_col tsvector;openGauss=# UPDATE tsearch.pgweb SET textsearchable_index_col = to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,'')); 然后为加速搜索创建一个GIN索引： 1 openGauss=# CREATE INDEX textsearch_idx_4 ON tsearch.pgweb USING gin(textsearchable_index_col); 现在，就可以执行一个快速全文搜索了： 1 2 3 4 5 6 7 8 91011 openGauss=# SELECT title FROM tsearch.pgweb WHERE textsearchable_index_col @@ to_tsquery('north & america') ORDER BY last_mod_date DESC LIMIT 10; title -------- Canada Mexico(2 rows) 相比于一个表达式索引，单独列方法的一个优势是：它没有必要在查询时明确指定分词器以便能使用索引。正如上面例子所示，查询可以依赖于default_text_search_config。另一个优势是搜索比较快速，因为它没有必要重新利用to_tsvector调用来验证索引匹配。表达式索引方法更容易建立，且它需要较少的磁盘空间，因为tsvector形式没有明确存储。 父主题： 表和索引

解析文档 GaussDB中提供了to_tsvector函数把文档处理成tsvector数据类型。 1 to_tsvector([ config regconfig, ] document text) returns tsvector to_tsvector将文本文档解析为token，再将token简化到词素，并返回一个tsvector。其中tsvector中列出了词素及它们在文档中的位置。文档是根据指定的或默认的文本搜索分词器进行处理的。这里有一个简单的例子： 1234 openGauss=# SELECT to_tsvector('english', 'a fat cat sat on a mat - it ate a fat rats'); to_tsvector----------------------------------------------------- 'ate':9 'cat':3 'fat':2,11 'mat':7 'rat':12 'sat':4 通过以上例子可发现结果tsvector不包含词a、on或者it，rats变成rat，并且忽略标点符号-。 to_tsvector函数内部调用一个解析器，将文档的文本分解成token并给每个token指定一个类型。对于每个token，有一系列词典可供查询。词典系列因token类型的不同而不同。识别token的第一本词典将发出一个或多个标准词素来表示token。例如： rats变成rat因为词典认为词rats是rat的复数形式。 有些词被作为停用词（请参考停用词），这样它们就会被忽略，因为它们出现得太过频繁以致于搜索中没有用处。比如例子中的a、on和it。 如果没有词典识别token，那么它也被忽略。在这个例子中，符号“-”被忽略，因为词典没有给它分配token类型（空间符号），即空间记号永远不会被索引。 语法解析器、词典和要索引的token类型由选定的文本搜索分词器决定。可以在同一个数据库中有多种不同的分词器，以及提供各种语言的预定义分词器。在以上例子中，使用缺省分词器english。 函数setweight可以给tsvector的记录加权重，权重是字母A、B、C、D之一。这通常用于标记来自文档不同部分的记录，比如标题、正文。之后，这些信息可以用于排序搜索结果。 因为to_tsvector(NULL)会返回空，当字段可能是空的时候，建议使用coalesce。以下是推荐的为结构化文档创建tsvector的方法： 1 2 3 4 5 6 7 8 910 openGauss=# CREATE TABLE tsearch.tt (id int, title text, keyword text, abstract text, body text, ti tsvector);openGauss=# INSERT INTO tsearch.tt(id, title, keyword, abstract, body) VALUES (1, 'China', 'Beijing', 'China','China, officially the People''s Republic of China (PRC), located in Asia, is the world''s most populous state.');openGauss=# UPDATE tsearch.tt SET ti = setweight(to_tsvector(coalesce(title,'')), 'A') || setweight(to_tsvector(coalesce(keyword,'')), 'B') || setweight(to_tsvector(coalesce(abstract,'')), 'C') || setweight(to_tsvector(coalesce(body,'')), 'D');openGauss=# DROP TABLE tsearch.tt; 上例使用setweight标记已完成的tsvector中的每个词的来源，并且使用tsvector连接操作符||合并标记过的tsvector值，处理tsvector一节详细介绍了这些操作。 父主题： 控制文本搜索

条件表达式 在执行SQL语句时，可通过条件表达式筛选出符合条件的数据。 条件表达式主要有以下几种： CASE CASE表达式是条件表达式，类似于其他编程语言中的CASE语句。 CASE表达式的语法图请参考图1。 图1 case::= CASE子句可以用于合法的表达式中。condition是一个返回BOOLEAN数据类型的表达式： 如果结果为真，CASE表达式的结果就是符合该条件所对应的result。 如果结果为假，则以相同方式处理随后的WHEN或ELSE子句。 如果各WHEN condition都不为真，表达式的结果就是在ELSE子句执行的result。如果省略了ELSE子句且没有匹配的条件，结果为NULL。 示例： 1 2 3 4 5 6 7 8 9101112131415161718192021 openGauss=# CREATE TABLE tpcds.case_when_t1(CW_COL1 INT) DISTRIBUTE BY HASH (CW_COL1);openGauss=# INSERT INTO tpcds.case_when_t1 VALUES (1), (2), (3);openGauss=# SELECT * FROM tpcds.case_when_t1; a --- 1 2 3(3 rows)openGauss=# SELECT CW_COL1, CASE WHEN CW_COL1=1 THEN 'one' WHEN CW_COL1=2 THEN 'two' ELSE 'other' END FROM tpcds.case_when_t1 ORDER BY 1; cw_col1 | case ---------+------- 1 | one 2 | two 3 | other(3 rows)openGauss=# DROP TABLE tpcds.case_when_t1; DECODE DECODE的语法图请参见图2。 图2 decode::= 将表达式base_expr与后面的每个compare(n) 进行比较，如果匹配返回相应的value(n)。如果没有发生匹配，则返回default。 示例请参见条件表达式函数。 12345 openGauss=# SELECT DECODE('A','A',1,'B',2,0); case ------ 1(1 row) COALESCE COALESCE的语法图请参见图3。 图3 coalesce::= COALESCE返回它的第一个非NULL的参数值。如果参数都为NULL，则返回NULL。它常用于在显示数据时用缺省值替换NULL。和CASE表达式一样，COALESCE只计算用来判断结果的参数，即在第一个非空参数右边的参数不会被计算。 示例 1 2 3 4 5 6 7 8 91011121314151617 openGauss=# CREATE TABLE tpcds.c_tabl(description varchar(10), short_description varchar(10), last_value varchar(10)) DISTRIBUTE BY HASH (last_value);openGauss=# INSERT INTO tpcds.c_tabl VALUES('abc', 'efg', '123');openGauss=# INSERT INTO tpcds.c_tabl VALUES(NULL, 'efg', '123');openGauss=# INSERT INTO tpcds.c_tabl VALUES(NULL, NULL, '123');openGauss=# SELECT description, short_description, last_value, COALESCE(description, short_description, last_value) FROM tpcds.c_tabl ORDER BY 1, 2, 3, 4; description | short_description | last_value | coalesce-------------+-------------------+------------+---------- abc | efg | 123 | abc | efg | 123 | efg | | 123 | 123(3 rows)openGauss=# DROP TABLE tpcds.c_tabl; 如果description不为NULL，则返回description的值，否则计算下一个参数short_description；如果short_description不为NULL，则返回short_description的值，否则计算下一个参数last_value；如果last_value不为NULL，则返回last_value的值，否则返回（none）。 12345 openGauss=# SELECT COALESCE(NULL,'Hello World'); coalesce --------------- Hello World(1 row) NULLIF NULLIF的语法图请参见图4。 图4 nullif::= 只有当value1和value2相等时，NULLIF才返回NULL。否则它返回value1。 示例 1 2 3 4 5 6 7 8 910111213141516 openGauss=# CREATE TABLE tpcds.null_if_t1 ( NI_VALUE1 VARCHAR(10), NI_VALUE2 VARCHAR(10))DISTRIBUTE BY HASH (NI_VALUE1);openGauss=# INSERT INTO tpcds.null_if_t1 VALUES('abc', 'abc');openGauss=# INSERT INTO tpcds.null_if_t1 VALUES('abc', 'efg');openGauss=# SELECT NI_VALUE1, NI_VALUE2, NULLIF(NI_VALUE1, NI_VALUE2) FROM tpcds.null_if_t1 ORDER BY 1, 2, 3; ni_value1 | ni_value2 | nullif -----------+-----------+-------- abc | abc | abc | efg | abc(2 rows)openGauss=# DROP TABLE tpcds.null_if_t1; 如果value1等于value2则返回NULL，否则返回value1。 12345 openGauss=# SELECT NULLIF('Hello','Hello World'); nullif -------- Hello(1 row) GREATEST（最大值），LEAST（最小值） GREATEST的语法图请参见图5。 图5 greatest::= 从一个任意数字表达式的列表里选取最大的数值。 12345 openGauss=# SELECT greatest(9000,155555,2.01); greatest ---------- 155555(1 row) LEAST的语法图请参见图6。 图6 least::= 从一个任意数字表达式的列表里选取最小的数值。 以上的数字表达式必须都可以转换成一个普通的数据类型，该数据类型将是结果类型。 列表中的NULL值将被忽略。只有所有表达式的结果都是NULL的时候，结果才是NULL。 12345 openGauss=# SELECT least(9000,2); least ------- 2(1 row) 示例请参见条件表达式函数。 NVL NVL的语法图请参见图7。 图7 nvl::= 如果value1为NULL则返回value2，如果value1非NULL，则返回value1。 示例： 12345 openGauss=# SELECT nvl(null,1);NVL ----- 1(1 row) 12345 openGauss=# SELECT nvl ('Hello World' ,1); nvl --------------- Hello World(1 row) 父主题： 表达式

示例 示例1：Union中的待定类型解析。这里，unknown类型文本'b'将被解析成text类型。 123456 openGauss=# SELECT text 'a' AS "text" UNION SELECT 'b'; text------ a b(2 rows) 示例2：简单Union中的类型解析。文本1.2的类型为numeric，而且integer类型的1可以隐含地转换为numeric，因此使用这个类型。 123456 openGauss=# SELECT 1.2 AS "numeric" UNION SELECT 1; numeric--------- 1 1.2(2 rows) 示例3：转置Union中的类型解析。这里，因为类型real不能被隐含转换成integer，但是integer可以隐含转换成real，那么联合的结果类型将是real。 123456 openGauss=# SELECT 1 AS "real" UNION SELECT CAST('2.2' AS REAL); real------ 1 2.2(2 rows) 示例4：TD模式下，coalesce参数输入int和varchar类型，那么解析成varchar类型。ORA模式下会报错。 --在oracle模式下，创建oracle兼容模式的数据库oracle_1。openGauss=# CREATE DATABASE oracle_1 dbcompatibility = 'ORA';--切换数据库为oracle_1。openGauss=# \c oracle_1--创建表t1。oracle_1=# CREATE TABLE t1(a int, b varchar(10)); --删除表。oracle_1=# DROP TABLE t1;--切换数据库为postgres。oracle_1=# \c postgres--在TD模式下，创建TD兼容模式的数据库td_1。openGauss=# CREATE DATABASE td_1 dbcompatibility = 'TD';--切换数据库为td_1。openGauss=# \c td_1--创建表t2。td_1=# CREATE TABLE t2(a int, b varchar(10));--查看coalesce参数输入int和varchar类型的查询语句的执行计划。td_1=# EXPLAIN VERBOSE select coalesce(a, b) from t2; QUERY PLAN--------------------------------------------------------------------------------------- Data Node Scan (cost=0.00..0.00 rows=0 width=0) Output: (COALESCE((t2.a)::character varying, t2.b)) Node/s: All datanodes Remote query: SELECT COALESCE(a::character varying, b) AS "coalesce" FROM public.t2(4 rows)--删除表。td_1=# DROP TABLE t2;--切换数据库为postgres。td_1=# \c postgres--删除Oracle和TD模式的数据库。openGauss=# DROP DATABASE oracle_1;openGauss=# DROP DATABASE td_1; 示例5：ORA模式下，将整个表达式最终的返回值类型定为result1的数据类型，或者与result1同类型范畴的更高精度的数据类型。 --在ORA模式下，创建ORA兼容模式的数据库ora_1。openGauss=# CREATE DATABASE ora_1 dbcompatibility = 'A';--切换数据库为ora_1。openGauss=# \c ora_1--开启Decode兼容性参数。set sql_beta_feature='a_style_coerce';--创建表t1。ora_1=# CREATE TABLE t1(c_int int, c_float8 float8, c_char char(10), c_text text, c_date date);--插入数据。ora_1=# INSERT INTO t1 VALUES(1, 2, '3', '4', date '12-10-2010');--result1类型为char，defresult类型为text，text精度更高，返回值的类型由char更新为text。ora_1=# SELECT decode(1, 2, c_char, c_text) AS result, pg_typeof(result) FROM t1; result | pg_typeof --------+----------- 4 | text(1 row)--result1类型为int，属于数值类型范畴，返回值的类型置为numeric。ora_1=# SELECT decode(1, 2, c_int, c_float8) AS result, pg_typeof(result) FROM t1; result | pg_typeof --------+----------- 2 | numeric(1 row)--不存在defresult数据类型向result1数据类型之间的隐式转换，报错处理。ora_1=# SELECT decode(1, 2, c_int, c_date) FROM t1;ERROR: CASE types integer and timestamp without time zone cannot be matchedLINE 1: SELECT decode(1, 2, c_int, c_date) FROM t1; ^CONTEXT: referenced column: c_date--关闭Decode兼容性参数。set sql_beta_feature='none';--删除表。ora_1=# DROP TABLE t1;DROP TABLE--切换数据库为postgres。ora_1=# \c postgres--删除ORA模式的数据库。openGauss=# DROP DATABASE ora_1;DROP DATABASE