华为云用户手册

示例 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 --建立外表，用来以TEXT格式导入GDS服务器192.168.0.90和192.168.0.91上的数据，导入过程错误信息将记录到err_HR_staffS中。 postgres=# CREATE FOREIGN TABLE foreign_HR_staffS ( staff_ID NUMBER(6) , FIRST_NAME VARCHAR2(20), LAST_NAME VARCHAR2(25), EMAIL VARCHAR2(25), PHONE_NUMBER VARCHAR2(20), HIRE_DATE DATE, employment_ID VARCHAR2(10), SALARY NUMBER(8,2), COMMISSION_PCT NUMBER(2,2), MANAGER_ID NUMBER(6), section_ID NUMBER(4) ) SERVER gsmpp_server OPTIONS (location 'gsfs://192.168.0.90:5000/* | gsfs://192.168.0.91:5000/*', format 'TEXT', delimiter E'\x08', null '') WITH err_HR_staffS; 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 --建立外表，用来以TEXT格式导入GDS服务器192.168.0.90和192.168.0.91上的数据，导入过程错误信息将记录到err_HR_staffS中。本次数据导入允许出现的数据格式错误个数为2。 CREATE FOREIGN TABLE foreign_HR_staffS_ft3 ( staff_ID NUMBER(6) , FIRST_NAME VARCHAR2(20), LAST_NAME VARCHAR2(25), EMAIL VARCHAR2(25), PHONE_NUMBER VARCHAR2(20), HIRE_DATE DATE, employment_ID VARCHAR2(10), SALARY NUMBER(8,2), COMMISSION_PCT NUMBER(2,2), MANAGER_ID NUMBER(6), section_ID NUMBER(4) ) SERVER gsmpp_server OPTIONS (location 'gsfs://192.168.0.90:5000/* | gsfs://192.168.0.91:5000/*', format 'TEXT', delimiter E'\x08', null '',reject_limit '2') WITH err_HR_staffS_ft3; 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 --建立外表，用来以 CS V格式导入input_data目录下存放在各个节点名文件下的所有文件。 postgres=# CREATE FOREIGN TABLE foreign_HR_staffS_ft1 ( staff_ID NUMBER(6) , FIRST_NAME VARCHAR2(20), LAST_NAME VARCHAR2(25), EMAIL VARCHAR2(25), PHONE_NUMBER VARCHAR2(20), HIRE_DATE DATE, employment_ID VARCHAR2(10), SALARY NUMBER(8,2), COMMISSION_PCT NUMBER(2,2), MANAGER_ID NUMBER(6), section_ID NUMBER(4) ) SERVER gsmpp_server OPTIONS (location 'file:///input_data/*', format 'csv', mode 'private', delimiter ',') WITH err_HR_staffS_ft1; 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 --建立外表，用来以CSV格式导出数据到output_data目录下。 postgres=# CREATE FOREIGN TABLE foreign_HR_staffS_ft2 ( staff_ID NUMBER(6) , FIRST_NAME VARCHAR2(20), LAST_NAME VARCHAR2(25), EMAIL VARCHAR2(25), PHONE_NUMBER VARCHAR2(20), HIRE_DATE DATE, employment_ID VARCHAR2(10), SALARY NUMBER(8,2), COMMISSION_PCT NUMBER(2,2), MANAGER_ID NUMBER(6), section_ID NUMBER(4) ) SERVER gsmpp_server OPTIONS (location 'file:///output_data/', format 'csv', delimiter '|', header 'on') WRITE ONLY; 1 2 3 4 5 --删除外表。 postgres=# DROP FOREIGN TABLE foreign_HR_staffS; postgres=# DROP FOREIGN TABLE foreign_HR_staffS_ft1; postgres=# DROP FOREIGN TABLE foreign_HR_staffS_ft2; postgres=# DROP FOREIGN TABLE foreign_HR_staffS_ft3;

语法格式 1 2 3 4 5 6 7 8 9 CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [ { column_name type_name POSITION(offset,length) | LIKE source_table } [, ...] ] ) SERVER gsmpp_server OPTIONS ( { option_name ' value ' } [, ...] ) [ { WRITE ONLY | READ ONLY }] [ WITH error_table_name | LOG INTO error_table_name] [REMOTE LOG 'name'] [PER NODE REJECT LIMIT 'value'] [ TO { GROUP groupname | NODE ( nodename [, ... ] ) } ];

参数概览 创建外表语法提供了多个参数，常用参数分类如下。 必需参数 table_name column_name type_name SERVER gsmpp_server OPTIONS可选参数 可选参数 外表的数据源位置参数location 数据格式参数 format header（仅支持CSV，FIXED格式） fileheader（仅支持CSV，FIXED格式） out_filename_prefix delimiter quote（仅支持CSV格式） escape（仅支持CSV格式） null noescaping（仅支持TEXT格式） encoding eol 容错性参数 fill_missing_fields ignore_extra_data reject_limit compatible_illegal_chars WITH error_table_name LOG INTO error_table_nam... REMOTE LOG 'name' PER NODE REJECT LIMIT 'v...

示例 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 --创建表student1。 postgres=# CREATE TABLE student1 ( stuno int, classno int ) DISTRIBUTE BY hash(stuno); --插入数据。 postgres=# INSERT INTO student1 VALUES(1,1); postgres=# INSERT INTO student1 VALUES(2,2); postgres=# INSERT INTO student1 VALUES(3,3); --查看数据。 postgres=# SELECT * FROM student1; --直接更新所有记录的值。 postgres=# UPDATE student1 SET classno = classno*2; --查看数据。 postgres=# SELECT * FROM student1; --删除表。 postgres=# DROP TABLE student1;

参数说明 table_name 要更新的表名，可以使用模式修饰。 取值范围：已存在的表名称。 alias 目标表的别名。 取值范围：字符串，符合标识符命名规范。 column_name 要修改的字段名。 支持使用目标表的别名加字段名来引用这个字段。例如： UPDATE foo AS f SET f.col_name = 'postgres'; 取值范围：已存在的字段名。 expression 赋给字段的值或表达式。 DEFAULT 用对应字段的缺省值填充该字段。 如果没有缺省值，则为NULL。 sub_query 子查询。 使用同一数据库里其他表的信息来更新一个表可以使用子查询的方法。其中SELECT子句具体介绍请参考SELECT。 from_list 一个表的表达式列表，允许在WHERE条件里使用其他表的字段。与在一个SELECT语句的FROM子句里声明表列表类似。 目标表不能出现在from_list里，除非在使用一个自连接（此时它必须以from_list的别名出现）。 condition 一个返回Boolean类型结果的表达式。只有这个表达式返回true的行才会被更新。 output_expression 在所有需要更新的行都被更新之后，UPDATE命令用于计算返回值的表达式。 取值范围：使用任何table以及FROM中列出的表的字段。*表示返回所有字段。 output_name 字段的返回名称。

注意事项 要修改表，用户必须对该表有UPDATE权限。 对expression或condition条件里涉及到的任何表要有SELECT权限。 不允许对表的分布列（distribute column）进行修改。 对于列存表，暂时不支持RETURNING子句。 列存表不支持结果不确定的更新(non-deterministic update)。试图对列存表用多行数据更新一行时会报错。 列存表的更新操作，旧记录空间不会回收，需要执行VACUUM FULL table_name进行清理。 对于列存复制表，暂不支持UPDATE操作。

语法格式 1 2 3 4 5 6 7 8 9 10 11 12 13 14 UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ] SET {column_name = { expression | DEFAULT } |( column_name [, ...] ) = {( { expression | DEFAULT } [, ...] ) |sub_query }}[, ...] [ FROM from_list] [ WHERE condition ] [ RETURNING {* | {output_expression [ [ AS ] output_name ]} [, ...] }]; where sub_query can be: SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ] { * | {expression [ [ AS ] output_name ]} [, ...] } [ FROM from_item [, ...] ] [ WHERE condition ] [ GROUP BY grouping_element [, ...] ] [ HAVING condition [, ...] ]

示例3 常用数据类型使用示例 //bit类型使用示例，注意此处bit类型取值范围[0,1] Statement st = conn.createStatement(); String sqlstr = "create or replace function fun_1()\n" + "returns bit AS $$\n" + "select col_bit from t_bit limit 1;\n" + "$$\n" + "LANGUAGE SQL;"; st.execute(sqlstr); CallableStatement c = conn.prepareCall("{ ? = call fun_1() }"); //注册输出类型，位串类型 c.registerOutParameter(1, Types.BIT); c.execute(); //使用Boolean类型获取结果 System.out.println(c.getBoolean(1));

示例2 客户端内存占用过多解决 此示例主要使用setFetchSize来调整客户端内存使用，它的原理是通过数据库游标来分批获取服务器端数据，但它会加大网络交互，可能会损失部分性能。 由于游标事务内有效，故需要先关闭自动提交，最后需要执行手动提交。 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 // 关闭掉自动提交 conn.setAutoCommit(false); Statement st = conn.createStatement(); // 打开游标，每次获取50行数据 st.setFetchSize(50); ResultSet rs = st.executeQuery("SELECT * FROM mytable"); while (rs.next()) { System.out.print("a row was returned."); } conn.commit(); rs.close(); // 关闭服务器游标。 st.setFetchSize(0); rs = st.executeQuery("SELECT * FROM mytable"); while (rs.next()) { System.out.print("many rows were returned."); } conn.commit(); rs.close(); // Close the statement. st.close(); conn.close(); 执行完毕后可使用如下命令恢复自动提交： conn.setAutoCommit(true);

相同表的并发INSERT 事务T1： 1 2 3 START TRANSACTION; INSERT INTO test VALUES(2,'test2','test123'); COMMIT; 事务T2： 1 2 3 START TRANSACTION; INSERT INTO test VALUES(3,'test3','test123'); COMMIT; 场景1： 开启事务T1，不提交的同时开启事务T2，事务T1执行INSERT完成后，执行事务T2的INSERT语句，可以执行成功，读已提交和可重复读隔离级别下，此时在事务T1中执行SELECT语句，看不到事务T2中插入的数据，事务T2中执行查询语句看不到事务T1中插入的数据。 场景2： READ COMMITTED级别 开启事务T1，不提交的同时开启事务T2，事务T1执行INSERT完成后直接提交，事务T2中执行INSERT语句后执行查询语句，可以看到事务T1中插入的数据。 REPEATABLE READ级别 开启事务T1，不提交的同时开启事务T2，事务T1执行INSERT完成后直接提交，事务T2中执行INSERT语句后执行查询语句，看不到事务T1中插入的数据。 父主题： 并发写入示例

排序查询结果 排序试图针对特定查询衡量文档的相关度，从而将众多的匹配文档中相关度最高的文档排在最前。 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 9 10 11 12 postgres=# 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 9 10 11 12 postgres=# 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 9 10 11 12 13 14 15 16 17 18 19 postgres=# CREATE TABLE tsearch.ts_ngram(id int, body text); postgres=# INSERT INTO tsearch.ts_ngram VALUES(1, '中文'); postgres=# INSERT INTO tsearch.ts_ngram VALUES(2, '中文检索'); postgres=# INSERT INTO tsearch.ts_ngram VALUES(3, '检索中文'); --精确匹配 postgres=# 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) --模糊匹配 postgres=# 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限制导致排序慢。可是这是很难避免的，因为实际查询中通常会有大量的匹配。 父主题： 控制文本搜索

JSON函数 JSON函数表示可以用于JSON类型（请参考JSON类型）数据的函数。 array_to_json(anyarray [, pretty_bool]) 描述：返回JSON类型的数组。一个多维数组成为一个JSON数组的数组。如果pretty_bool为true，将在一维元素之间添加换行符。 返回类型：json 示例： 1 2 3 4 5 postgres=# SELECT array_to_json('{{1,5},{99,100}}'::int[]); array_to_json ------------------ [[1,5],[99,100]] (1 row) row_to_json(record [, pretty_bool]) 描述：返回JSON类型的行。如果pretty_bool为true，将在第一级元素之间添加换行符。 返回类型：json 示例： 1 2 3 4 5 postgres=# SELECT row_to_json(row(1,'foo')); row_to_json --------------------- {"f1":1,"f2":"foo"} (1 row) 父主题： 函数和操作符

示例 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 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 -- 创建目标表products和源表newproducts，并插入数据 postgres=# CREATE TABLE products ( product_id INTEGER, product_name VARCHAR2(60), category VARCHAR2(60) ); postgres=# INSERT INTO products VALUES (1501, 'vivitar 35mm', 'electrncs'); postgres=# INSERT INTO products VALUES (1502, 'olympus is50', 'electrncs'); postgres=# INSERT INTO products VALUES (1600, 'play gym', 'toys'); postgres=# INSERT INTO products VALUES (1601, 'lamaze', 'toys'); postgres=# INSERT INTO products VALUES (1666, 'harry potter', 'dvd'); postgres=# CREATE TABLE newproducts ( product_id INTEGER, product_name VARCHAR2(60), category VARCHAR2(60) ); postgres=# INSERT INTO newproducts VALUES (1502, 'olympus camera', 'electrncs'); postgres=# INSERT INTO newproducts VALUES (1601, 'lamaze', 'toys'); postgres=# INSERT INTO newproducts VALUES (1666, 'harry potter', 'toys'); postgres=# INSERT INTO newproducts VALUES (1700, 'wait interface', 'books'); -- 进行MERGE INTO操作 postgres=# MERGE INTO products p USING newproducts np ON (p.product_id = np.product_id) WHEN MATCHED THEN UPDATE SET p.product_name = np.product_name, p.category = np.category WHERE p.product_name != 'play gym' WHEN NOT MATCHED THEN INSERT VALUES (np.product_id, np.product_name, np.category) WHERE np.category = 'books'; MERGE 4 -- 查询更新后的结果 postgres=# SELECT * FROM products ORDER BY product_id; product_id | product_name | category ------------+----------------+----------- 1501 | vivitar 35mm | electrncs 1502 | olympus camera | electrncs 1600 | play gym | toys 1601 | lamaze | toys 1666 | harry potter | toys 1700 | wait interface | books (6 rows) -- 删除表 postgres=# DROP TABLE products; postgres=# DROP TABLE newproducts;

语法格式 1 2 3 4 5 6 7 8 9 10 11 12 13 14 MERGE INTO table_name [ [ AS ] alias ] USING { { table_name | view_name } | subquery } [ [ AS ] alias ] ON ( condition ) [ WHEN MATCHED THEN UPDATE SET { column_name = { expression | DEFAULT } | ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...] [ WHERE condition ] ] [ WHEN NOT MATCHED THEN INSERT { DEFAULT VALUES | [ ( column_name [, ...] ) ] VALUES ( { expression | DEFAULT } [, ...] ) [, ...] [ WHERE condition ] } ];

参数说明 INTO子句 指定正在更新或插入的目标表。目标表为复制表时，暂不支持目标表中某列默认值为volatile函数（如自增列），enable_stream_operator=off时目标表需要包含主键或带有unique not null。 talbe_name 目标表的表名。 alias 目标表的别名。 取值范围：字符串，符合标识符命名规范。 USING子句 指定源表，源表可以为表、视图或子查询。目标表为复制表时，暂不支持USING子句中包含非复制表。 ON子句 关联条件，用于指定目标表和源表的关联条件。不支持更新关联条件中的字段。 WHEN MATCHED子句 当源表和目标表中数据针对关联条件可以匹配上时，选择WHEN MATCHED子句进行UPDATE操作。 不支持更新分布列。不支持更新系统表、系统列。 WHEN NOT MATCHED子句 当源表和目标表中数据针对关联条件无法匹配时，选择WHEN NOT MATCHED子句进行INSERT操作。 不支持INSERT子句中包含多个VALUES。 WHEN MATCHED和WHEN NOT MATCHED子句顺序可以交换，可以缺省其中一个，但不能同时缺省，不支持同时指定两个WHEN MATCHED或WHEN NOT MATCHED子句。 DEFAULT 用对应字段的缺省值填充该字段。 如果没有缺省值，则为NULL。 WHERE condition UPDATE子句和INSERT子句的条件，只有在条件满足时才进行更新操作，可缺省。不支持WHERE条件中引用系统列。

接口介绍 高级功能包DBE_FILE支持的所有接口请参见表 DBE_FILE。 表1 DBE_FILE 接口名称 描述 DBE_FILE.OPEN 根据指定的目录和文件名打开一个文件。 DBE_FILE.IS_CLOSE 检测一个文件句柄是否打开。 DBE_FILE.READ_LINE 根据指定的长度从一个打开的文件句柄中读取出一行数据。 DBE_FILE.WRITE 将BUFFER中指定的数据写入到文件中。 DBE_FILE.NEW_LINE 这个存储过程用于向一个打开的文件中写入一个或者多个行终结符。 DBE_FILE.WRITE_LINE 这个存储过程将BUFFER中的字符串写入到一个打开的文件中。 DBE_FILE.FORMAT_WRITE 这个存储过程是一个格式化的PUT存储过程，行为类似printf()。 DBE_FILE.GET_RAW 从一个打开的文件句柄中读取二进制数据。 DBE_FILE.PUT_RAW 向文件中写入传入的二进制数据。 DBE_FILE.FLUSH 将一个文件句柄中的数据写入到物理文件中。 DBE_FILE.CLOSE 关闭一个打开的文件句柄。 DBE_FILE.CLOSE_ALL 关闭一个会话中打开的所有的文件句柄。 DBE_FILE.REMOVE 这个存储过程删除一个磁盘文件，操作的时候需要有充分的权限。 DBE_FILE.RENAME 对于磁盘上的文件进行重命名，类似Unix的mv。 DBE_FILE.COPY 拷贝一个连续的区域内容到一个新创建的文件中，如果忽略了start_line和end_line会拷贝整个文件。 DBE_FILE.GET_ATTR 读取并返回磁盘文件的属性。 DBE_FILE.SEEK 根据用户指定的字节数向前或者向后调整文件指针的位置。 DBE_FILE.GET_POS 返回文件的偏移量，单位字节。 DBE_FILE.OPEN 该函数用来打开一个文件，可以指定最大行的大小，最多可以同时打开50个文件。并且该函数返回INTEGER类型的一个句柄。 DBE_FILE.OPEN函数原型为： 1 2 3 4 5 6 DBE_FILE.OPEN ( dir IN VARCHAR2, file_name IN VARCHAR2, open_mode IN VARCHAR2, max_line_size IN INTEGER DEFAULT 1024) RETURN INTEGER; 表2 DBE_FILE.OPEN接口参数说明 参数 描述 dir 文件的目录位置，这个字符串是一个目录对象名。 说明： 文件目录的位置，需要添加到系统表PG_DIRECTORY中，如果传入的路径和PG_DIRECTORY中的路径不匹配，会报路径不存在的错误， 下面的涉及location作为参数的函数也是同样的情况。 file_name 文件名，包含扩展（文件类型），不包括路径名。如果文件名中包含路径，在OPEN中会被忽略，在Unix系统中，文件名不能以/.结尾。 open_mode 指定文件的打开模式，包含r：read text，w: write text和a: append text。 说明： 对于写操作，会检测写入文件类型，如果为elf类型文件，会报错退出。 max_line_size 每行最大字符数，包含换行符（最小值是1，最大值是32767）。如果没有指定，会指定一个默认值1024。 DBE_FILE.IS_CLOSE 该函数用于检测一个文件句柄来查看文件是否已经打开，返回一个布尔值，异常情况是INVALID_FILEHANDLE。 DBE_FILE.IS_CLOSE函数原型为： 1 2 3 DBE_FILE.IS_CLOSE ( file IN INTEGERINTEGER) RETURN BOOLEAN; 表3 DBE_FILE.IS_CLOSE接口参数说明 参数 描述 file IN INTEGERINTEGER 传入一个要检测的文件句柄。 DBE_FILE.READ_LINE 该存储过程从一个打开的文件句柄中读取文本，并把读取的结果存放到BUFFER中。读取的时候会读取到行尾，不包含行终结符或者读取到文件末尾，或者达到了len参数指定的大小，读取的长度不能超过OPEN的时候指定的max_line_size。 DBE_FILE.READ_LINE函数原型为： 1 2 3 4 DBE_FILE.READ_LINE ( file IN INTEGER, buffer OUT VARCHAR2, len IN INTEGER DEFAULT NULL) 表4 DBE_FILE.READ_LINE接口参数说明 参数 描述 file 通过调用OPEN打开的文件句柄，文件必须以读的模式打开，否则会抛出INVALID_OPERATION的异常。 buffer 用于接收数据的BUFFER。 len 从文件中读取的字节数，默认是NULL。如果是默认NULL，会使用max_linesize来指定大小。 DBE_FILE.WRITE 该存储过程用于向文件中写入BUFFER中的数据，要写入的文件必须以写模式打开，这个操作不会写入行终结符。 DBE_FILE.WRITE函数原型为： 1 2 3 DBE_FILE.WRITE ( file IN INTEGERINTEGER, buffer IN VARCHAR2); 表5 DBE_FILE.WRITE接口参数说明 参数 描述 file 该存储过程用于向文件中写入BUFFER中的数据，要写入的文件必须以写模式打开，这个操作不会写入行终结符。 buffer 要写入文件的文本数据，BUFFER的最大值是32767个字节。如果在open的时候没有指定值，默认是1024个字节，没有刷新到文件之前，一系列的WRITE操作的BUFFER总和不能超过32767个字节。 说明： 对于写操作，会检测写入文件类型，如果为elf类型文件，会报错退出。 DBE_FILE.NEW_LINE 该存储过程用于向一个打开的文件中写入一个或者多个行终结符。这个存储过程是从WRITE函数中拆分出来的，因为行终结符和平台相关。 DBE_FILE.NEW_LINE函数原型为： 1 2 3 DBE_FILE.NEW_LINE ( file IN INTEGER, line_nums IN INTEGER := 1); 表6 DBE_FILE.NEW_LINE接口参数说明 参数 描述 file 一个打开的文件句柄。 line_nums 写入到文件中的终结符的数量。 DBE_FILE.WRITE_LINE 该存储过程将BUFFER中的字符串写入到一个打开的文件中，文件必须以写模式打开。 DBE_FILE.WRITE_LINE函数原型为： 1 2 3 4 DBE_FILE.WRITE_LINE( file IN INTEGER, buffer IN VARCHAR2, flush IN BOOLEAN DEFAULT FALSE); 表7 DBE_FILE.WRITE_LINE接口参数说明 参数 描述 file 一个打开的文件句柄。 buffer 要写入文件的文本数据，BUFFER的最大值是32767个字节。如果在open的时候没有指定值，默认是1024个字节，没有刷新到文件之前，一系列的PUT操作的BUFFER总和不能超过32767个字节。 说明： 对于写操作，会检测写入文件类型，如果为elf类型文件，会报错退出。 flush 在write后是否要刷到磁盘。 DBE_FILE.FORMAT_WRITE 该存储过程是一个格式化的PUT存储过程，行为类似printf()。 DBE_FILE.FORMAT_WRITE函数原型为： 1 2 3 4 5 6 DBE_FILE.FORMAT_WRITE ( file IN INTEGER, format IN VARCHAR2, arg1 IN VARCHAR2 DEFAULT NULL, . . . arg5 IN VARCHAR2 DEFAULT NULL]); 表8 DBE_FILE.FORMAT_WRITE接口参数说明 参数 描述 file 一个打开的文件句柄。 format 一个要进行格式化的字符串包含，文本和格式符\n和%s。 [arg1. . .arg5] 从1到5个可选的参数串，参数和格式化字符的位置是一一对应的，如果存在格式化字符而没有提供参数，会使用空串来替代%s。 DBE_FILE.GET_RAW 该函数用于从打开的文件描述符中读取二进制数据，从r中返回。 DBE_FILE.GET_RAW函数原型为： 1 2 3 4 DBE_FILE.GET_RAW ( file IN INTEGER, r OUT RAW, length IN INTEGER DEFAULT NULL); 表9 DBE_FILE.GET_RAW接口参数说明 参数 描述 file 一个打开的文件句柄。 r 输出的二进制数据 length 要读取文件的长度，默认值为NULL，读取文件中所有数据，最大长度为1G。 DBE_FILE.PUT_RAW 该函数用于向文件中写入二进制数据。 DBE_FILE.PUT_RAW函数原型为： 1 2 3 4 DBE_FILE.GET_RAW ( file IN INTEGER, r IN RAW, flush IN BOOLEAN DEFAULT FALSE); 表10 DBE_FILE.PUT_RAW接口参数说明 参数 描述 file 一个打开的文件句柄。 r 输出的二进制数据 说明： 对于写操作，会检测写入文件类型，如果为elf类型文件，会报错退出。 flush 是否flush到文件中，默认为false。 DBE_FILE.FLUSH 一个文件句柄中的数据要写入到物理文件中，缓冲区中的数据必须要有一个行终结符。当文件必须在打开时读取，刷新非常有用。例如，调试信息可以刷新到文件中，以便立即读取。 DBE_FILE.FLUSH函数原型为： 1 2 DBE_FILE.FLUSH ( file IN INTEGER); 表11 DBE_FILE.FLUSH接口参数说明 参数 描述 file 一个打开的文件句柄。 DBE_FILE.CLOSE 该存储过程用于关闭一个打开的文件句柄，当调用这个存储过程的时候，如果还有等待写入的缓存的数据，可能会收到异常信息。 DBE_FILE.CLOSE函数原型为： 1 2 DBE_FILE.CLOSE ( file IN OUT INTEGER); 表12 DBE_FILE.CLOSE接口参数说明 参数 描述 file 一个打开的文件句柄。 DBE_FILE.CLOSE_ALL 该存储过程关闭一个会话中打开的所有的文件句柄，可用于紧急的清理操作。 DBE_FILE.CLOSE_ALL函数原型为： 1 DBE_FILE.CLOSE_ALL; 表13 DBE_FILE.CLOSE_ALL接口参数说明 参数 描述 无 无 DBE_FILE.REMOVE 该存储过程删除一个磁盘文件，操作的时候对目录和文件要有充分的权限。 DBE_FILE.REMOVE函数原型为： 1 2 3 DBE_FILE.REMOVE ( dir IN VARCHAR2, file_name IN VARCHAR2); 表14 DBE_FILE.REMOVE接口参数说明 参数 描述 dir 文件所在的目录位置。 file_name 要删除的文件。 DBE_FILE.RENAME 对磁盘上的文件进行重命名，类似Unix的mv。 DBE_FILE.RENAME函数原型为： 1 2 3 4 5 6 DBE_FILE.RENAME ( src_dir IN VARCHAR2, src_file_name IN VARCHAR2, dest_dir IN VARCHAR2, dest_file_name IN VARCHAR2, overwrite IN BOOLEAN DEFAULT FALSE); 表15 DBE_FILE.RENAME接口参数说明 参数 描述 src_dir 原文件的目录位置（大小写敏感）。 src_file_name 要进行命名的原文件。 dest_dir 目的目录（大小写敏感）。 dest_file_name 新的文件名。 overwrite 默认是false，如果目的目录下存在一个同名的文件，不会进行重写。 DBE_FILE.COPY 该存储过程拷贝一个连续的区域内容到一个新创建的文件中，如果忽略了start_line和end_line会拷贝整个文件。 DBE_FILE.COPY函数原型为： 1 2 3 4 5 6 7 DBE_FILE.COPY ( src_dir IN VARCHAR2, src_file_name IN VARCHAR2, dest_dir IN VARCHAR2, dest_file_name IN VARCHAR2, start_line IN INTEGER DEFAULT 1, end_line IN INTEGER DEFAULT NULL); 表16 DBE_FILE.COPY接口参数说明 参数 描述 src_dir 原文件所在的目录。 src_file_name 要拷贝的原文件。 dest_dir 目的文件所在的目录。 dest_file_name 要写入的目的文件。 说明： 对于写操作，会检测写入文件类型，如果为elf类型文件，会报错退出。 start_line 拷贝开始的行号，默认是1。 end_line 拷贝结束的行号，默认是NULL，指定到文件尾。 DBE_FILE.GET_ATTR 该存储过程用于读取并返回磁盘文件的属性。 DBE_FILE.FGETATTR函数原型为： 1 2 3 4 5 6 DBE_FILE.GET_ATTR( dir IN VARCHAR2, file_name IN VARCHAR2, is_exists OUT BOOLEAN, length OUT NUMBER, block_size OUT INTEGER); 表17 DBE_FILE.GET_ATTR接口参数说明 参数 描述 dir 文件所在的目录。 file_name 要检测的文件名。 is_exists 返回文件是否存在的值。 length 文件的字节长度，如果文件不存在返回NULL。 block_size 文件系统的块大小（单位字节），如果文件不存在返回NULL。 DBE_FILE.SEEK 该存储过程会根据用户指定的字节数向前或者向后调整文件指针的位置。 DBE_FILE.SEEK函数原型为： 1 2 3 4 DBE_FILE.SEEK ( file INOUT DBE_FILE.FILE_TYPE, absolute_start IN INTEGER DEFAULT NULL, relative_start IN INTEGER DEFAULT NULL); 表18 DBE_FILE.SEEK接口参数说明 参数 描述 file 一个打开的文件句柄。 absolute_start 文件偏移的绝对位置，这个默认值为NULL。 relative_start 文件偏移的相对位置。如果这个值是正数，向前偏移；如果是负数，向后偏移；默认值为NULL。如果和absolute_offset参数同时指定，以absolute_offset参数为准。 DBE_FILE.GET_POS 函数返回文件的偏移量，单位字节。 DBE_FILE.FGETPOS函数原型为： 1 2 3 DBE_FILE.GET_POS ( file IN INTEGER) RETURN BIGINT; 表19 DBE_FILE.GET_POS接口参数说明 参数 描述 file 一个打开的文件句柄。

查看数据 使用系统表pg_tables查询数据库所有表的信息。 1 postgres=# SELECT * FROM pg_tables; 使用gsql的\d+命令查询表的属性。 1 postgres=# \d+ customer_t1; 执行如下命令查询表customer_t1的数据量。 1 postgres=# SELECT count(*) FROM customer_t1; 执行如下命令查询表customer_t1的所有数据。 1 postgres=# SELECT * FROM customer_t1; 执行如下命令只查询字段c_customer_sk的数据。 1 postgres=# SELECT c_customer_sk FROM customer_t1; 执行如下命令过滤字段c_customer_sk的重复数据。 1 postgres=# SELECT DISTINCT( c_customer_sk ) FROM customer_t1; 执行如下命令查询字段c_customer_sk为3869的所有数据。 1 postgres=# SELECT * FROM customer_t1 WHERE c_customer_sk = 3869; 执行如下命令按照字段c_customer_sk进行排序。 1 postgres=# SELECT * FROM customer_t1 ORDER BY c_customer_sk; 父主题： 创建和管理表

示例 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 29 30 31 32 33 34 35 36 37 --创建表customer_demographics_t1。 postgres=# 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); --插入记录。 postgres=# INSERT INTO customer_demographics_t1 VALUES(1920801,'M', 'U', 'DOCTOR DEGREE', 200, 'GOOD', 1, 0,0); --开启事务。 postgres=# START TRANSACTION; --更新字段值。 postgres=# UPDATE customer_demographics_t1 SET cd_education_status= 'Unknown'; --终止事务，上面所执行的更新会被撤销掉。 postgres=# ABORT; --查询数据。 postgres=# 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) --删除表。 postgres=# DROP TABLE customer_demographics_t1;

原型 1 2 3 4 SQLRETURN SQLSetEnvAttr(SQLHENV EnvironmentHandle SQLINTEGER Attribute, SQLPOINTER ValuePtr, SQLINTEGER StringLength);

参数 表1 SQLSetEnvAttr参数 关键字 参数说明 EnvironmentHandle 环境句柄。 Attribute 需设置的环境属性，可为如下值： SQL_ATTR_ODBC_VERSION：指定ODBC版本。 SQL_CONNECTION_POOLING：连接池属性。 SQL_OUTPUT_NTS：指明驱动器返回字符串的形式。 ValuePtr 指向对应Attribute的值。依赖于Attribute的值，ValuePtr可能是32位整型值，或为以空结束的字符串。 StringLength 如果ValuePtr指向字符串或二进制缓冲区，这个参数是*ValuePtr长度，如果ValuePtr指向整型，忽略StringLength。

更多优化示例 示例1：修改基表为replicate表，并且在过滤列上创建索引。 1 2 3 create table master_table (a int); create table sub_table(a int, b int); select a from master_table group by a having a in (select a from sub_table); 上述事例中存在一个相关性子查询，为了提升查询的性能，可以将sub_table修改为一个relication表，并且在字段a上创建一个index。

货币类型 货币类型存储带有固定小数精度的货币金额。 表1中显示的范围假设有两位小数。可以以任意格式输入，包括整型、浮点型或者典型的货币格式（如“$1,000.00”）。根据区域字符集，输出一般是最后一种形式。 表1 货币类型 名称 存储容量 描述 范围 money 8 字节 货币金额 -92233720368547758.08 到 +92233720368547758.07 numeric，int和bigint类型的值可以转化为money类型。如果从real和double precision类型转换到money类型，可以先转化为numeric类型，再转化为money类型，例如： 1 postgres=# SELECT '12.34'::float8::numeric::money; 这种用法是不推荐使用的。浮点数不应该用来处理货币类型，因为小数点的位数可能会导致错误。 money类型的值可以转换为numeric类型而不丢失精度。转换为其他类型可能丢失精度，并且必须通过以下两步来完成： 1 postgres=# SELECT '52093.89'::money::numeric::float8; 当一个money类型的值除以另一个money类型的值时，结果是double precision（也就是，一个纯数字，而不是money类型）；在运算过程中货币单位相互抵消。 父主题： 数据类型

语法格式 开启匿名块 1 2 3 4 5 [DECLARE [declare_statements]] BEGIN execution_statements END; / 开启事务 1 2 3 4 5 6 7 BEGIN [ WORK | TRANSACTION ] [ { ISOLATION LEVEL { READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE | REPEATABLE READ } | { READ WRITE | READ ONLY } } [, ...] ];

语法格式 CREATE CLIENT MASTER KEY client_master_key_name WITH '(' master_key_params ')'; master_key_params： 1 KEY_STORE '=' key_store_value ',' KEY_PATH '=' key_path_value ',' ALGORITHM '=' algorithm_value

参数说明 client_master_key_name 同一命名空间下，客户端加密主密钥(cmk)名称，需要唯一，不可重复。 取值范围：字符串，要符合标识符的命名规范。 mater_key_params 指的是创建客户端加密主密钥时所涉及的参数信息，具体包括： KEY_STORE，目前取值为gs_ktool。 KEY_PATH值为KeyTool工具生成密钥的ID，类似"gs_ktool/1"这种写法。 ALGORITHM为加密列加密密钥使用的算法，目前只支持AES_256_CBC。

背景信息 GaussDB数据库 支持的分区表为范围分区表。 范围分区表：将数据基于范围映射到每一个分区，这个范围是由创建分区表时指定的分区键决定的。这种分区方式是最为常用的，并且分区键经常采用日期，例如将销售数据按照月份进行分区。 分区表和普通表相比具有以下优点： 改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索效率。 增强可用性：如果分区表的某个分区出现故障，表在其他分区的数据仍然可用。 方便维护：如果分区表的某个分区出现故障，需要修复数据，只修复该分区即可。 普通表若要转成分区表，需要新建分区表，然后把普通表中的数据导入到新建的分区表中。因此在初始设计表时，请根据业务提前规划是否使用分区表。

示例 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 --创建模式ds。 postgres=# CREATE SCHEMA ds; --将当前模式ds更名为ds_new。 postgres=# ALTER SCHEMA ds RENAME TO ds_new; --创建用户jack。 postgres=# CREATE USER jack PASSWORD 'xxxxxxxxxx'; --将DS_NEW的所有者修改为jack。 postgres=# ALTER SCHEMA ds_new OWNER TO jack; --删除用户jack和模式ds_new。 postgres=# DROP SCHEMA ds_new; postgres=# DROP USER jack;

参数说明 schema_name 现有模式的名称。 取值范围：已存在的模式名。 RENAME TO new_name 修改模式的名称。 new_name：模式的新名称。 取值范围：字符串，要符合标识符命名规范。 OWNER TO new_owner 修改模式的所有者。非系统管理员要改变模式的所有者，该用户还必须是新的所有角色的直接或间接成员， 并且该成员必须在此数据库上有CREATE权限。 new_owner：模式的新所有者。 取值范围：已存在的用户名/角色名。

示例 下面列举了基本的匿名块程序： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 --空语句块 postgres=# BEGIN NULL; END; / --将信息打印到控制台： postgres=# BEGIN dbe_output.print_line('hello world!'); END; / --将变量内容打印到控制台： postgres=# DECLARE my_var VARCHAR2(30); BEGIN my_var :='world'; dbe_output.print_line('hello'||my_var); END; /

操作步骤 连接数据库。 查询审计记录。 1 postgres=# SELECT * FROM pg_query_audit('2021-03-03 21:30:00', '2021-03-03 22:00:00') where type = 'login_success' and username = 'user1'; 查询结果如下： time | type | result | userid | username | database | client_conninfo | object_name | detail_info | node_name | thread_id | local_port | remote_port ------------------------+---------------+--------+--------+----------+----------+-----------------+-------------+------------------------------------------------------+-----------+-- -------------------------------+------------+------------- 2021-03-03 21:30:31+08 | login_success | ok | 16398 | user1 | postgres | gsql@[local] | postgres | login db(postgres) success,the current user is:user1 | cn_5001 | 1 39634608699136@668093431256149 | 18000 | null (1 row) 该条记录表明，用户user1在2021-03-03 21:30:31+08登录数据库postgres。其中client_conninfo字段在log_hostname启动且IP连接时，字符@后显示反向DNS查找得到的主机名。 查询所有CN节点审计记录。 1 postgres=# SELECT * FROM pgxc_query_audit('2021-03-03 21:30:00', '2021-03-03 22:00:00') where type = 'login_success' and username = 'user1'; 查询结果如下： time | type | result | userid | username | database | client_conninfo | object_name | detail_info | node_name | thread_id | local_port | remote_port ------------------------+---------------+--------+--------+----------+----------+-----------------+-------------+------------------------------------------------------+-----------+-- -------------------------------+------------+------------- 2021-03-03 21:30:31+08 | login_success | ok | 16398 | user1 | postgres | gsql@[local] | postgres | login db(postgres) success,the current user is:user1 | cn_5001 | 1 39634608699136@668093431256149 | 18000 | null 2021-03-03 21:36:09+08 | login_success | ok | 16398 | user1 | postgres | gsql@[local] | postgres | login db(postgres) success,the current user is:user1 | cn_5003 | 1 39779716937472@668093769836394 | 18000 | null (2 rows) 查询结果显示，用户user1在cn_5001和cn_5003的成功登录记录。