华为云用户手册

参数说明 cursor_name 将要创建的游标名。 取值范围：遵循数据库对象命名规范。 BINARY 指明游标以二进制而不是文本格式返回数据。 NO SCROLL 声明游标检索数据行的方式。 NO SCROLL：声明该游标不能用于以倒序的方式检索数据行。 未声明：根据执行计划的不同，自动判断该游标是否可以用于以倒序的方式检索数据行。 WITH HOLD | WITHOUT HOLD 声明当创建游标的事务结束后，游标是否能继续使用。 WITH HOLD：声明该游标在创建它的事务结束后仍可继续使用。 WITHOUT HOLD：声明该游标在创建它的事务之外不能再继续使用，此游标将在事务结束时被自动关闭。 如果不指定WITH HOLD或WITHOUT HOLD，默认行为是WITHOUT HOLD。 query 使用SELECT或VALUES子句指定游标返回的行。 取值范围：SELECT或VALUES子句。

注意事项 游标命令只能在事务块里使用。 通常游标和SELECT一样返回文本格式。因为数据在系统内部是用二进制格式存储的，系统必须对数据做一定转换以生成文本格式。一旦数据是以文本形式返回，客户端应用需要把它们转换成二进制进行操作。使用FETCH语句，游标可以返回文本或二进制格式。 应该小心使用二进制游标。文本格式一般都比对应的二进制格式占用的存储空间大。二进制游标返回内部二进制形态的数据，可能更易于操作。如果想以文本方式显示数据，则以文本方式检索会为用户节约很多客户端的工作。比如，如果查询从某个整数列返回1，在缺省的游标里将获得一个字符串1，但在二进制游标里将得到一个4字节的包含该数值内部形式的数值（大端顺序）。

示例7 通过外表读取 DLI 多版本外表。DLI多版本外表示例仅8.1.1及以上版本支持。 创建dli_server，对应的foreign data wrapper为DFS_FDW。 1 2 3 4 5 6 7 8 9 CREATE SERVER dli_server FOREIGN DATA WRAPPER DFS_FDW OPTIONS ( ADDRESS 'obs.cn-north-1.myhuaweicloud.com', AC CES S_KEY 'xxxxxxxxx', SECRET_ACCESS_KEY 'yyyyyyyyyyyyy', TYPE 'DLI', DLI_ADDRESS 'dli.example.com', DLI_ACCESS_KEY 'xxxxxxxxx', DLI_SECRET_ACCESS_KEY 'yyyyyyyyyyyyy' ); ADDRESS是OBS的终端节点（Endpoint）。DLI_ADDRESS是DLI的终端节点（Endpoint），请根据实际替换。 ACCESS_KEY和SECRET_ACCESS_KEY是云账号体系访问OBS服务的密钥。请根据实际替换。 DLI_ACCESS_KEY和DLI_SECRET_ACCESS_KEY是云账号体系访问DLI服务的密钥。请根据实际替换。 TYPE表示创建的Server为DLI Server。请保持DLI取值不变。 创建访问DLI多版本的OBS外表customer_address，不包含分区列，表关联的DLI服务器为dli_server。其中project_id为xxxxxxxxxxxxxxx，dli上的database_name为database123，需要访问的table_name为table456，根据实际替换。 认证用的AK和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 CREATE FOREIGN TABLE customer_address ( 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(36,33) , ca_location_type char(20) ) SERVER dli_server OPTIONS ( FORMAT 'ORC', ENCODING 'utf8', DLI_PROJECT_ID 'xxxxxxxxxxxxxxx', DLI_DATABASE_NAME 'database123'， DLI_TABLE_NAME 'table456' ) DISTRIBUTE BY roundrobin; 通过外表查询DLI多版本表的数据。 1 2 3 4 5 SELECT COUNT(*) FROM customer_address; count ------- 20 (1 row)

示例6 通过外表读取OBS上的json数据。 OBS上有如下json文件，json对象中存在嵌套、数组，部分对象的某些字段缺失，部分对象name重复。 {"A" : "simple1", "B" : {"C" : "nesting1"}, "D" : ["array", 2, {"E" : "complicated"}]} {"A" : "simple2", "D" : ["array", 2, {"E" : "complicated"}]} {"A" : "simple3", "B" : {"C" : "nesting3"}, "D" : ["array", 2, {"E" : "complicated3"}]} {"B" : {"C" : "nesting4"},"A" : "simple4", "D" : ["array", 2, {"E" : "complicated4"}]} {"A" : "simple5", "B" : {"C" : "nesting5"}, "D" : ["array", 2, {"E" : "complicated5"}]} 创建obs_server，对应的foreign data wrapper为DFS_FDW。 1 2 3 4 5 6 CREATE SERVER obs_server FOREIGN DATA WRAPPER DFS_FDW OPTIONS ( ADDRESS 'obs.cn-north-1.myhuaweicloud.com', ACCESS_KEY 'xxxxxxxxx', SECRET_ACCESS_KEY 'yyyyyyyyyyyyy', TYPE 'OBS' ); ADDRESS是OBS的终端节点（Endpoint），请根据实际替换。也是使用region参数，通过指定regionCode在region_map文件中查找对应的 域名 。 ACCESS_KEY和SECRET_ACCESS_KEY是云账号体系访问密钥。请根据实际替换。 TYPE表示创建的Server为OBS Server。请保持OBS取值不变。 创建OBS外表json_f ，定义字段名，以d#2_e为例，从命名可以看出该字段是数组d的第二个元素里嵌套的e对象。表关联的OBS服务器为obs_server。foldername为外表中数据源文件目录，即表数据目录在OBS上对应的文件目录。 认证用的AK和SK硬编码到代码中或者明文存储都有很大的安全风险，建议在配置文件或者环境变量中密文存放，使用时解密，确保安全。 1 2 3 4 5 6 7 8 9 10 11 CREATE FOREIGN TABLE json_f ( a VARCHAR(10), b_c TEXT, d#1 INTEGER, d#2_e VARCHAR(30) )SERVER obs_server OPTIONS ( foldername '/xxx/xxx/', format 'json', encoding 'utf8', force_mapping 'true' )distribute by roundrobin; 查询外表json_f。由于容错性参数force_mapping默认打开，json对象缺失的字段会填NULL；json对象name重复的以最后一次出现的name为准。 1 2 3 4 5 6 7 8 9 SELECT * FROM json_f; a | b_c | d#1 | d#2_e ---------+----------+-----+-------------- simple1 | nesting1 | 2 | complicated1 simple2 | | 2 | complicated2 simple3 | nesting3 | 2 | complicated3 simple4 | nesting4 | 2 | complicated4 repeat | nesting5 | 2 | complicated5 (5 rows)

示例4（仅存算分离3.0版本支持） 通过partsupp外表往obs文件系统读取数据。 创建obs server。 认证用的AK和SK硬编码到代码中或者明文存储都有很大的安全风险，建议在配置文件或者环境变量中密文存放，使用时解密，确保安全。 1 2 3 4 5 6 CREATE SERVER obs_srv FOREIGN DATA WRAPPER dfs_fdw OPTIONS ( address 'xxx', type 'obs', access_key 'xxx', secret_access_key 'xxx' ); 创建obs外表partsupp，使用disk cache中的冷缓存。 1 2 3 4 5 6 7 8 9 10 11 12 13 CREATE FOREIGN TABLE PARTSUPP ( PS_PARTKEY BIGINT NOT NULL, PS_SUPPKEY BIGINT NOT NULL, PS_AVAILQTY BIGINT NOT NULL, PS_SUPPLYCOST DECIMAL(15,2) NOT NULL, PS_COMMENT VARCHAR(199) NOT NULL ) SERVER obs_srv options ( encoding 'utf-8', format 'parquet', foldername 'xxx', cache_policy 'NONE' ) READ ONLY DISTRIBUTE BY ROUNDROBIN; 通过外表partsupp往obs上读取数据。 1 SELECT * FROM PARTSUPP;

示例5 关于包含信息约束（Informational Constraint）HDFS外表的相关操作。 创建含有信息约束（Informational Constraint）的HDFS外表。 1 2 3 4 5 6 7 8 9 10 CREATE FOREIGN TABLE ft_region ( R_REGIONKEY int, R_NAME TEXT, R_COMMENT TEXT , primary key (R_REGIONKEY) not enforced) SERVER hdfs_server OPTIONS(format 'orc', encoding 'utf8', foldername '/user/hive/warehouse/mppdb.db/region_orc11_64stripe') DISTRIBUTE BY roundrobin; 查看region表是否有信息约束索引： 1 2 3 4 5 6 7 8 9 10 11 SELECT relname,relhasindex FROM pg_class WHERE oid='ft_region'::regclass; relname | relhasindex ------------------------+------------- ft_region | f (1 row) SELECT conname, contype, consoft, conopt, conindid, conkey FROM pg_constraint WHERE conname ='ft_region_pkey'; conname | contype | consoft | conopt | conindid | conkey ----------------+---------+---------+--------+----------+-------- ft_region_pkey | p | t | t | 0 | {1} (1 row) 删除信息约束： 1 2 3 4 5 6 ALTER FOREIGN TABLE ft_region DROP CONSTRAINT ft_region_pkey RESTRICT; SELECT conname, contype, consoft, conindid, conkey FROM pg_constraint WHERE conname ='ft_region_pkey'; conname | contype | consoft | conindid | conkey ---------+---------+---------+----------+-------- (0 rows) 添加一个唯一信息约束： 1 ALTER FOREIGN TABLE ft_region ADD CONSTRAINT constr_unique UNIQUE(R_REGIONKEY) NOT ENFORCED; 删除唯一信息约束： 1 2 3 4 5 6 ALTER FOREIGN TABLE ft_region DROP CONSTRAINT constr_unique RESTRICT; SELECT conname, contype, consoft, conindid, conkey FROM pg_constraint WHERE conname ='constr_unique'; conname | contype | consoft | conindid | conkey ---------+---------+---------+----------+-------- (0 rows) 添加一个唯一信息约束： 1 2 3 4 5 6 7 ALTER FOREIGN TABLE ft_region ADD CONSTRAINT constr_unique UNIQUE(R_REGIONKEY) NOT ENFORCED disable query optimization; SELECT relname,relhasindex FROM pg_class WHERE oid='ft_region'::regclass; relname | relhasindex ------------------------+------------- ft_region | f (1 row) 删除唯一信息约束： 1 ALTER FOREIGN TABLE ft_region DROP CONSTRAINT constr_unique CASCADE;

示例2 通过HDFS只写外表，将TPC-H benchmark测试数据表region中的数据导出至HDFS文件系统的/user/hive/warehouse/mppdb.db/regin_orc/目录下。 创建HDFS SERVER，对应的foreign data wrapper为HDFS_FDW或者DFS_FDW，同示例一。 创建HDFS只写外表。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 CREATE FOREIGN TABLE ft_wo_region ( R_REGIONKEY INT4, R_NAME TEXT, R_COMMENT TEXT ) SERVER hdfs_server OPTIONS ( FORMAT 'orc', encoding 'utf8', FOLDERNAME '/user/hive/warehouse/mppdb.db/regin_orc/' ) WRITE ONLY; 通过只写外表向HDFS文件系统写入数据。 1 INSERT INTO ft_wo_region SELECT * FROM region;

示例3（仅存算分离3.0版本支持） 通过HDFS多级分区只写外表将TPC-H benchmark测试数据表region表中的数据导出HDFS文件系统的/user/hive/warehouse/mppdb.db/region_orc/目录下。 创建HDFS外表，对应的foreign data wrapper为HDFS_FDW或者DFS_FDW，同示例1。 创建HDFS多级分区只写外表。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 CREATE FOREIGN TABLE ft_wo_region_partition ( R_REGIONKEY INT4, R_NAME TEXT, R_COMMENT TEXT ) SERVER hdfs_server OPTIONS ( FORMAT 'orc', encoding 'utf8', FOLDERNAME '/user/hive/warehouse/mppdb.db/region_orc/' ) WRITE ONLY PARTITION BY (R_REGIONKEY, R_NAME); 通过只写外表向HDFS文件系统写入数据。 1 INSERT INTO ft_wo_region_partition SELECT * FROM region;

示例1 在HDFS通过HIVE导入TPC-H benchmark测试数据表part表及region表。part表的文件路径为/user/hive/warehouse/partition.db/part_4，region表的文件路径为/user/hive/warehouse/mppdb.db/region_orc11_64stripe/。 创建HDFS_Server，对应的foreign data wrapper为HDFS_FDW或者DFS_FDW。 1 CREATE SERVER hdfs_server FOREIGN DATA WRAPPER HDFS_FDW OPTIONS (address '10.10.0.100:25000,10.10.0.101:25000',hdfscfgpath '/opt/hadoop_client/HDFS/hadoop/etc/hadoop',type'HDFS'); 在可选项options里面写入了HDFS集群对应的NameNode的IP地址及端口号。具体端口号请在 MRS -HDFS服务配置中搜索参数“dfs.namenode.rpc.port”查看。本示例假设端口号为25000。 ‘10.10.0.100:25000,10.10.0.101:25000’中列出了两组NameNode的地址及端口号，分别表示HDFS的主NameNode及备NameNode，这里推荐使用该种主备方式填写。两组参量中间使用“,”进行分割。 创建HDFS外表。表关联的HDFS server为hdfs_server，表ft_region对应的HDFS服务器上的文件格式为‘orc’，在HDFS文件系统上对应的文件目录为'/user/hive/warehouse/mppdb.db/region_orc11_64stripe/'。 创建不包含分区列的HDFS外表： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 CREATE FOREIGN TABLE ft_region ( R_REGIONKEY INT4, R_NAME TEXT, R_COMMENT TEXT ) SERVER hdfs_server OPTIONS ( FORMAT 'orc', encoding 'utf8', FOLDERNAME '/user/hive/warehouse/mppdb.db/region_orc11_64stripe/' ) DISTRIBUTE BY roundrobin; 创建包含分区列的HDFS外表： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 CREATE FOREIGN TABLE ft_part ( p_partkey int, p_name text, p_mfgr text, p_brand text, p_type text, p_size int, p_container text, p_retailprice float8, p_comment text ) SERVER hdfs_server OPTIONS ( FORMAT 'orc', encoding 'utf8', FOLDERNAME '/user/hive/warehouse/partition.db/part_4' ) DISTRIBUTE BY roundrobin PARTITION BY (p_mfgr) AUTOMAPPED; GaussDB (DWS)支持2种文件指定方式：通过关键字filenames指定和通过foldername指定。推荐通过使用foldername进行指定。关键字distribute指定了表ft_region的存储分布方式。 查看创建的外表： 1 2 3 4 5 6 7 8 9 10 11 SELECT * FROM pg_foreign_table WHERE ftrelid='ft_region'::regclass; ftrelid | ftserver | ftwriteonly | ftoptions ---------+----------+-------------+------------------------------------------------------------------------------ 16510 | 16509 | f | {format=orc,foldername=/user/hive/warehouse/mppdb.db/region_orc11_64stripe/} (1 row) select * from pg_foreign_table where ftrelid='ft_part'::regclass; ftrelid | ftserver | ftwriteonly | ftoptions ---------+----------+-------------+------------------------------------------------------------------ 16513 | 16509 | f | {format=orc,foldername=/user/hive/warehouse/partition.db/part_4} (1 row)

语法格式 创建外表。 1 2 3 4 5 6 7 8 9 10 11 12 CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [ { column_name type_name [ { [CONSTRAINT constraint_name] NULL | [CONSTRAINT constraint_name] NOT NULL | column_constraint [...]} ] | table_constraint [, ...]} [, ...] ] ) SERVER server_name OPTIONS ( { option_name ' value ' } [, ...] ) [ {WRITE ONLY | READ ONLY | READ WRITE} ] DISTRIBUTE BY {ROUNDROBIN | REPLICATION} [ PARTITION BY ( column_name ) [ AUTOMAPPED ] ] ; 其中column_constraint为： 1 2 3 [CONSTRAINT constraint_name] {PRIMARY KEY | UNIQUE} [NOT ENFORCED [ENABLE QUERY OPTIMIZATION | DISABLE QUERY OPTIMIZATION] | ENFORCED] 其中table_constraint为： 1 2 3 [CONSTRAINT constraint_name] {PRIMARY KEY | UNIQUE} (column_name) [NOT ENFORCED [ENABLE QUERY OPTIMIZATION | DISABLE QUERY OPTIMIZATION] | ENFORCED]

信息约束（Informational Constraint） 在GaussDB(DWS)中，数据的约束完全由使用者保证，数据源数据能够严格遵守某种信息约束条件，能够加速对已经具有这种约束特征数据的查询。目前外表不支持索引，所以采取使用Informational Constraint信息优化Plan，提高查询性能。 建立外表信息约束的约束条件： 只有用户保证表中的其中一列的非空值具有唯一性时才可以建立Informational Constraint，否则查询结果将与期望值不同。 GaussDB(DWS)的Informational Constraint只支持PRIMARY KEY和UNIQUE两种约束。 GaussDB(DWS)的Informational Constraint支持NOT ENFORCED属性，不支持ENFORCED属性。 一个表上的多列可以分别建立UNIQUE类型的Informational Constraint，但是PRIMARY KEY一个表中只能建立一个。 一个表的一列上可以建立多个Informational Constraint（由于一个列上有多个约束和一个的作用一致，所以不建议一个列上建立多个Informational Constraint），但是Primary Key类型只能建立一个。 不支持表级COMMENT。 不支持多列组合约束。 ORC格式只写外表不支持同一个集群不同CN向同一外表并发导出。 ORC格式只写外表的目录，只能用于GaussDB(DWS)的单个外表的导出目录，不能用于多个外表，并且其他组件不能向此目录写入其他文件。

注意事项 HDFS外表与OBS外表分为只读外表、只写外表和读写外表，只读外表用于查询操作，只写外表可以将GaussDB(DWS)中的数据导出到分布式文件系统中，读写外表可以查询和数据导出，其中读写外表仅9.1.0.100及以上版本支持。 此方式支持ORC、TEXT、 CS V、CARBONDATA、PARQUET和JSON格式的导入查询，OBS外表支持ORC、PARQUET（仅存算分离3.0版本支持）、CSV和TEXT格式的导出。HDFS外表支持ORC、PARQUET格式的导出。 该方式需要用户手动创建外部服务器，具体请参见CREATE SERVER。 若手动创建Server时指定foreign data wrapper为HDFS_FDW或者DFS_FDW，创建只读外表时需DISTRIBUTE BY子句指定分布方式。

功能描述 在当前数据库创建一个HDFS或OBS外表，用来访问存储在HDFS或者OBS分布式集群文件系统上的结构化数据。也可以导出ORC和PARQUET格式数据到HDFS或者OBS上。 数据存储在OBS：数据存储和计算分离，集群存储成本低，存储量不受限制，并且集群可以随时删除，但计算性能取决于OBS访问性能，相对HDFS有所下降，建议在数据计算不频繁场景下使用。 数据存储在HDFS：数据存储和计算不分离，集群成本较高，计算性能高，但存储量受磁盘空间限制，删除集群前需将数据导出保存，建议在数据计算频繁场景下使用。 实时数仓（单机部署）暂不支持HDFS外表。 实时数仓（单机部署）8.2.0及以上集群版本支持OBS外表，但需要指定Server的foreign data wrapper为DFS_FDW。 以下描述的读写外表，仅存算分离3.0版本支持。

create_audit_csv_foregion_table() 描述：创建读审计日志的分区外表。用户调用该函数生成审计日志外表pgxc_audit_logs，通过读取pgxc_audit_logs可以读取放在obs上的审计日志信息。该函数仅8.2.1.300及以上集群支持。 入参： obs_server：text类型，obs server名称 file_path：text类型，obs文件路径 返回值类型：record 示例： --创建obs server CREATE SERVER obs_server FOREIGN DATA WRAPPER DFS_FDW OPTIONS ( address 'obs.example.xxx:xxx', encrypt 'true', access_key 'xxxxxxxxx', secret_access_key 'xxxxxxxxxxxxxx', type 'obs' ); --调用该函数生成审计日志外表 SELECT * FROM pg_catalog.create_audit_csv_foregion_table('obs_server','/obs-audit/test/'); create_audit_csv_foregion_table --------------------------------- t (1 row) --读取放在obs上的审计日志信息 SELECT * FROM pgxc_audit_logs where year=2023 and month=7 and date=24; year | month | date | begintime | endtime | operation_type | audit_type | result | username | database | client_conninfo | object_name | object_details |command_text| detail_info | transaction_xid | query_id | node_name | session_id | local_port | remote_port | result_rows | error_code ------+-------+------+----------------------------+----------------------------+----------------+-------------------+--------+----------+----------+-----------------+--------------------------------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-----------------+--------------------+-----------+------------------------------------+------------+-------------+-------------+------------ 2023 | 7 | 24 | 2023-07-24 09:56:40.727+08 | 2023-07-24 09:56:42.215+08 | dml | dml_action_select | ok | dbadmin | gaussdb | gsql@[local] | public.pgxc_audit_logs | | select * f rom pgxc_audit_logs where year=2023 and date=24 and month=7;

pg_query_audit_details() 描述：查看审计日志并将审计日志中的object_name和object_details字段由json格式解析出来。该函数仅8.2.1.100及以上集群版本支持。 返回值类型：record 函数返回字段如下： 表4 pg_query_audit()函数返回字段 名称 类型 描述 begintime timestamp with time zone 操作的执行开始时间。 endtime timestamp with time zone 操作的执行结束时间。 operation_type text 操作类型，具体类型见表2。 audit_type text 审计类型，具体类型见表3。 result text 操作结果。 username text 执行操作的用户名。 database text 数据库名称。 client_conninfo text 客户端连接信息，即gsql，jdbc或odbc。 transaction_xid text 事务ID。 query_id text 查询ID。 node_name text 节点名称。 session_id text 会话ID。 local_port text 本地端口。 remote_port text 远端端口。 object_name text 表名、函数名、视图名。 column_name text 列名。 type_of_use text 对象的使用类型： 1：仅涉及（在实际使用中，此标志位暂不存在） 2：执行过程中访问（语句中出现的列以及在解析过程中访问到的列） 4：条件中发现（条件类型语句中，不包括条件表达式及函数） 8：inner join中发现 16：outer join中发现 32：聚合节点中发现（包括distinct、group by、聚集函数） 64：full join中发现 该列数值为叠加显示的数值。 use_type text type_of_use解析出的具体类型： 1：Reference only 2：Access 4：Conditional 8：Inner join 16：Outer join 32：Sum 64：Full join command_text text 操作的执行命令。 示例： 查询审计语句中所有对象的列及其在语句中使用的类型： 1 2 SET audit_object_details = on; SELECT object_name,object_details,result_rows,error_code,command_text FROM pg_query_audit('2023-05-12-03 8:00:00','2023-05-12 22:55:00') where command_text like '%student%'; 查询结果如下： 1 2 3 4 5 6 object_name | object_details | result_rows | error_code | command_text ------------------------------------------------------------------+------------------------------------------------------------------------------------------+-------------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------- student | | 0 | | CREATE TABLE student(stuNo int, stuName TEXT); studentscore | | 0 | | CREATE TABLE studentscore(stuNo int, stuscore int); ["public.student_view01","public.studentscore","public.student"] | | 0 | | CREATE OR REPLACE VIEW student_view01 AS SELECT * FROM student t1 where t1.stuNo in (select stuNo from studentscore t2 where t1.stuNo = t2.stuNo); ["public.student_view01","public.student","public.studentscore"] | {"public.student":[{"stuno":"6"},{"stuname":"2"}],"public.studentscore":[{"stuno":"6"}]} | 0 | | SELECT * FROM student_view01 查询结果object_details中显示部分语句执行过程中，涉及到的列及其使用类型，使用json格式进行记录。 使用pg_query_audit_details函数对object_name和object_details列进行解析： 1 SELECT database,object_name,column_name,type_of_use,use_type FROM pg_query_audit_details('2021-02-03 8:00:00','2024-02-03 22:55:00','current') where command_text like '%student%'; 查询结果如下： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 database | object_name | column_name | type_of_use | use_type ----------+-----------------------+-------------+-------------+-------------------- gaussdb | student | | 2 | Access gaussdb | | | 2 | Access gaussdb | studentscore | | 2 | Access gaussdb | | | 2 | Access gaussdb | public.student_view01 | | 2 | Access gaussdb | public.studentscore | | 2 | Access gaussdb | public.student | | 2 | Access gaussdb | | | 2 | Access gaussdb | public.student | stuno | 6 | Access,Conditional gaussdb | public.student | stuname | 2 | Access gaussdb | public.studentscore | stuno | 6 | Access,Conditional gaussdb | public.student_view01 | | 2 | Access gaussdb | public.student | | 2 | Access gaussdb | public.studentscore | | 2 | Access gaussdb | | | 2 | Access (15 rows)

pg_query_audit() 描述：查看当前CN节点审计日志。 返回值类型：record 函数返回字段如下： 表1 pg_query_audit()函数返回字段 名称 类型 描述 begintime timestamp with time zone 操作的执行开始时间。 endtime timestamp with time zone 操作的执行结束时间。 operation_type text 操作类型，具体类型见表2。 audit_type text 审计类型，具体类型见表3。 result text 操作结果。 username text 执行操作的用户名。 database text 数据库名称。 client_conninfo text 客户端连接信息，即gsql，jdbc或odbc。 object_name text 操作对象名称。 object_details text 记录语句中涉及的列及其使用类型。 command_text text 操作的执行命令。 detail_info text 执行操作详细信息。 transaction_xid text 事务ID。 query_id text 查询ID。 node_name text 节点名称。 session_id text 会话ID。 local_port text 本地端口。 remote_port text 远端端口。 result_rows text 语句执行返回的记录数。 error_code text 语句执行错误时的错误码。 表2 operation_type操作类型项 操作类型 描述 audit_switch 表示对用户打开和关闭审计日志操作场景进行审计。 login_logout 表示对用户登录和登出操作场景进行审计。 system 表示对系统的启停、实例切换操作场景进行审计。 sql_parse 表示对SQL语句解析场景进行审计。 user_lock 表示对用户锁定和解锁操作的场景进行审计。 grant_revoke 表示对用户权限授予和回收操作场景进行审计。 violation 表示对用户访问存在越权的场景进行审计。 ddl 表示对DDL操作场景进行审计，因为DDL操作会根据操作对象进行更细粒度控制，仍然沿用审计开关audit_system_object，即由audit_system_object控制对哪些对象的DDL操作进行审计（此处不配置ddl，只要配置了audit_system_object，审计也会生效)。 dml 表示对DML操作场景进行审计。 select 表示对SELECT操作场景进行审计。 internal_event 表示对内部事件操作场景进行审计。 user_func 表示对用户自定义函数、存储过程、匿名块操作场景进行审计。 说明： 如果自定义函数、存储过程中有fetch语句，则审计fetch语句时，其中common_text字段记录的为其对应的CURSOR内容。 special_func 表示对特殊函数调用操作场景进行审计，特殊函数包括：pg_terminate_backend和pg_cancel_backend。 copy 表示对COPY操作场景进行审计。 set 表示对SET操作场景进行审计。 transaction 表示对事务操作场景进行审计。 vacuum 表示对VACUUM操作场景进行审计。 analyze 表示对ANALYZE操作场景进行审计。 cursor 表示对游标操作的场景进行审计。 anonymous_block 表示对匿名块操作场景进行审计。 explain 表示对EXPLAIN操作场景进行审计。 show 表示对SHOW操作场景进行审计。 lock_table 表示对锁表操作场景进行审计。 comment 表示对COMMENT操作场景进行审计。 preparestmt 表示对PREPARE、EXECUTE、DEALLOCATE操作场景进行审计。 cluster 表示对CLUSTER操作场景进行审计。 constraints 表示对CONSTRAINTS操作场景进行审计。 checkpoint 表示对CHECKPOINT操作场景进行审计。 barrier 表示对BARRIER操作场景进行审计。 cleanconn 表示对CLEAN CONNECTION操作场景进行审计。 seclabel 表示对安全标签操作进行审计。 notify 表示对通知操作进行审计。 load 表示对加载操作进行审计。 discard 表示对清理当前会话所有的全局临时表信息场景进行审计。 表3 audit_type审计类型项 审计类型 描述 audit_open/audit_close 表示审计类型为打开和关闭审计日志操作。 user_login/user_logout 表示审计类型为用户登录/退出成功的操作和用户。 system_start/system_stop/system_recover/system_switch 表示审计类型为系统的启停、实例切换操作。 sql_wait/sql_parse 表示审计类型为SQL语句解析。 lock_user/unlock_user 表示审计类型为用户锁定和解锁成功的操作。 grant_role/revoke__role 表示审计类型为用户权限授予和回收的操作。 user_violation 表示审计类型为用户访问存在越权的操作。 ddl_数据库对象 表示审计类型为DDL操作，因为DDL操作由会根据操作对象进行更细粒度控制，仍然沿用审计开关audit_system_object，即由audit_system_object控制对哪些对象的DDL操作进行审计（此处不配置ddl，只要配置了audit_system_object，审计也会生效）。 例如：ddl_sequence表示审计类型为序列相关操作。 dml_action_insert/dml_action_delete/dml_action_update/dml_action_merge/dml_action_select 表示审计类型为INSERT、DELETE、UPDATE、MERGE等DML操作。 internal_event 表示审计类型为内部事件。 user_func 表示审计类型为用户自定义函数、存储过程、匿名块操作。 说明： 如果自定义函数、存储过程中有fetch语句，则审计fetch语句时，其中common_text字段记录的为其对应的CURSOR内容。 special_func 表示审计类型为特殊函数调用操作，特殊函数包括：pg_terminate_backend和pg_cancel_backend。 copy_to/copy_from 表示审计类型为COPY相关操作。 set_parameter 表示审计类型为SET操作。 trans_begin/trans_commit/trans_prepare/trans_rollback_to/trans_release/trans_savepoint/trans_commit_prepare/trans_rollback_prepare/trans_rollback 表示审计类型为事务相关操作。 vacuum/vacuum_full/vacuum_merge 表示审计类型为VACUUM相关操作。 analyze/analyze_verify 表示审计类型为ANALYZE相关操作。 cursor_declare/cursor_move/cursor_fetch/cursor_close 表示审计类型为游标相关操作。 codeblock_execute 表示审计类型为匿名块。 explain 表示审计类型为EXPLAIN操作。 show 表示审计类型为SHOW操作。 lock_table 表示审计类型为锁表操作。 comment 表示审计类型为COMMENT操作。 prepare/execute/deallocate 表示审计类型为PREPARE、EXECUTE或DEALLOCATE操作。 cluster 表示审计类型为CLUSTER操作。 constraints 表示审计类型为CONSTRAINTS操作。 checkpoint 表示审计类型为CHECKPOINT操作。 barrier 表示审计类型为BARRIER操作。 cleanconn 表示审计类型为CLEAN CONNECTION操作。 seclabel 表示审计类型为安全标签操作。 notify 表示审计类型为通知操作。 load 表示审计类型为加载操作。 discard 表示审计类型为DISCARD操作。

login_audit_messages(flag boolean) 描述：查看登录用户的登录信息。 返回值类型：元组 示例： 查看上一次登录认证通过的日期、时间和IP等信息： 1 2 3 4 5 SELECT * FROM login_audit_messages(true); username | database | logintime | type | result | client_conninfo | session_id ------------+----------+------------------------+---------------+--------+--------------------+----------------------------------------- dbadmin | gaussdb | 2017-06-02 15:28:34+08 | login_success | ok | gsql@[local] | 1663745305.140192538154752.coordinator1 (1 row) 查看上一次登录认证失败的日期、时间和IP等信息： 1 2 3 4 SELECT * FROM login_audit_messages(false) ORDER BY logintime desc limit 1; username | database | logintime | type | result | client_conninfo | session_id ------------+----------+------------------------+--------------+--------+-------------------------+------------ (0 rows) 查看自从最后一次认证通过以来失败的尝试次数、日期和时间： 1 2 3 4 SELECT * FROM login_audit_messages(false); username | database | logintime | type | result | client_conninfo | session_id ------------+----------+------------------------+--------------+--------+-------------------------+------------ (0 rows)

gs_password_expiration() 描述：显示当前账户距离密码过期的时间。密码过期后用户无法登录数据库。与创建用户的DDL语句PASSWORD EXPIRATION period相关，函数返回值大于等于-1，如果创建用户时未指定PASSWORD EXPIRATION period，该函数的缺省值为-1，表示没有过期限制。 返回值类型：interval 示例： 1 2 3 4 5 SELECT gs_password_expiration(); gs_password_expiration ------------------------- 29 days 23:59:49.731482 (1 row)

login_audit_messages_pid(flag boolean) 描述：查看登录用户的登录信息。与login_audit_messages的区别在于结果基于当前backendid向前查找。所以不会因为同一用户的后续登录，而影响本次登录的查询结果。也就是查询不到该用户后续登录的信息。 返回值类型：元组 示例： 查看上一次登录认证通过的日期、时间和IP等信息： 1 2 3 4 5 SELECT * FROM login_audit_messages_pid(true); username | database | logintime | type | result | client_conninfo | backendid | session_id ------------+----------+------------------------+---------------+--------+--------------------+----------------------------------------- dbadmin | postgres | 2017-06-02 15:28:34+08 | login_success | ok | gsql@[local] | 140311900702464 | 1663745305.140192538154752.coordinator1 (1 row) 查看上一次登录认证失败的日期、时间和IP等信息： 1 2 3 4 SELECT * FROM login_audit_messages_pid(false) ORDER BY logintime desc limit 1; username | database | logintime | type | result | client_conninfo | backendid | session_id ------------+----------+------------------------+--------------+--------+-------------------------+------------ (0 rows) 查看自从最后一次认证通过以来失败的尝试次数、日期和时间： 1 2 3 4 SELECT * FROM login_audit_messages_pid(false); username | database | logintime | type | result | client_conninfo | backendid | session_id ------------+----------+------------------------+--------------+--------+-------------------------+------------ (0 rows)

gs_password_deadline() 描述：显示当前账户距离密码过期的时间。密码过期后提示用户修改密码。与GUC参数password_effect_time相关。 返回值类型：interval 示例： 1 2 3 4 5 SELECT gs_password_deadline(); gs_password_deadline ------------------------- 83 days 17:44:32.196094 (1 row)

语法格式 1 2 3 4 5 ALTER [ ROW LEVEL SECURITY ] POLICY [ IF EXISTS ] policy_name ON table_name RENAME TO new_policy_name ALTER [ ROW LEVEL SECURITY ] POLICY policy_name ON table_name [ TO { role_name | PUBLIC } [, ...] ] [ USING ( using_expression ) ]

json_to_tsvector(config regconfig, ] json, jsonb) 描述：将json格式转换为用于支持全文检索的文件格式tsvector。 返回类型：jsonb 示例： 1 2 3 4 5 SELECT json_to_tsvector('{"a":1, "b":2, "c":3}'::json, to_jsonb('key'::text)); json_to_tsvector ------------------ 'b':2 'c':4 (1 row)

jsonb_insert(target jsonb, path text[], new_value jsonb [, insert_after boolean]) 描述：返回target，并插入new_value。如果path指定的target部分位于JSONB数组中，则new_value将在目标之前或insert_after为true（默认值为false）之后插入。如果在JSONB对象中由path指定的target部分，则仅当target不存在时才插入new_value。与面向路径的运算符一样，path中出现的负整数从JSON数组的末尾开始计数。 返回类型：jsonb 示例： 1 2 3 4 5 SELECT jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"'); jsonb_insert ------------------------------- {"a": [0, "new_value", 1, 2]} (1 row)

jsonb_to_recordset(array-json) 描述：参考函数jsonb_to_record，对数组内个每个元素，执行上述函数的操作，因此这要求数组内的每个元素都得是object-jsonb。 返回类型：setof record 示例： 1 2 3 4 5 6 SELECT * FROM jsonb_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]') AS x(a INT, b text, c boolean); a | b | c ---+-----+--- 1 | foo | 2 | bar | t (2 rows)

jsonb_to_record(object-json) 描述：正如所有返回record的函数一样，调用者必须用一个AS子句显式地定义记录的结构。会将object-json的键值对进行拆分重组，把键当做列名，去匹配填充AS显示指定的记录的结构。 返回类型：record 示例： 1 2 3 4 5 SELECT * FROM jsonb_to_record('{"a":1,"b":"foo","c":"bar"}'::jsonb) as x(a int, b text, d text); a | b | d ---+-----+--- 1 | foo | (1 row)

jsonb_exists_any(jsonb, text[]) 描述：同操作符?|，字符串数组$2里面是否存在的元素，在$1的顶层以key\elem\scalar的形式存在。 返回类型： 示例： 1 2 3 4 5 SELECT jsonb_exists_any('["1","2",3]', '{1, 2, 4}'); jsonb_exists_any ------------------ t (1 row)

jsonb_exists_all(jsonb, text[]) 描述：同操作符?&，字符串数组$2里面是否所有的元素，都在$1的顶层以key\elem\scalar的形式存在。 返回类型： bool 示例： 1 2 3 4 5 SELECT jsonb_exists_all('["1","2",3]', '{1, 2}'); jsonb_exists_all ------------------ t (1 row)

jsonb_concat(jsonb, jsonb) 描述：连接两个jsonb对象为一个jsonb。 返回类型：jsonb 示例： 1 2 3 4 5 SELECT jsonb_concat('{"a":1, "b":2}'::jsonb, '{"c":3, "d":4}'::jsonb); jsonb_concat ---------------------------------- {"a": 1, "b": 2, "c": 3, "d": 4} (1 row)

jsonb_delete_path(jsonb, text[]) 描述：删除jsonb数组中指定路径的元素。 返回类型：jsonb 示例： 1 2 3 4 5 SELECT jsonb_delete_path('{"a":{"b":{"c":1, "d":2}}, "e":3}'::jsonb , array['a', 'b']); jsonb_delete_path ------------------- {"a": {}, "e": 3} (1 row)

jsonb_pretty(jsonb) 描述：以缩进的JSON文本形式返回。 返回类型：jsonb 示例： 1 2 3 4 5 6 7 8 9 10 11 12 13 SELECT jsonb_pretty('{"a":{"b":{"c":1, "d":2}}, "e":3}'::jsonb); jsonb_pretty --------------------- { + "a": { + "b": { + "c": 1,+ "d": 2 + } + }, + "e": 3 + } (1 row)