华为云用户手册

pgxc_group_add_subscription(src_vw_name, target_vw_name) 描述：存算分离架构下，逻辑集群（Virtual Warehouse，以下简称VW）之间建立KV订阅关系，建立了订阅关系之后，消费者VW的KVcahce会定期的从生产者的obs cudesc快照目录增量同步sst文件到本地，后续就可以支持在消费者VM中跨VW查询生产者VW中的表。 创建的订阅关系可以在pgxc_group_subscription表中查询到记录。该函数仅9.0.3及以上集群版本支持。 返回值类型：void 返回信息如下： 表1 pgxc_group_add_subscription(src_vw_name, target_vw_name)返回字段 名称 类型 描述 src_vw_name text 生产者VW名称，通常作为写入数据的VW。 target_vw_name text 消费者VW名称，通常作为读取数据的VW。 示例： 1 2 3 4 5 SELECT pgxc_group_add_subscription('write_group', 'single_read_group'); pgxc_group_add_subscription ------------------ (1 row)

pg_shared_chunk_dump(contextname char(64)) 描述：将指定共享内存下内存上下文申请的所有chunk信息打印成文件。 参数contextname，表示内存上下文名称。 返回值类型：boolean 示例： 1 2 3 4 5 SELECT * FROM pg_shared_chunk_dump('pgstat file hash table'); pg_shared_chunk_dump ---------------------- t (1 row)

pv_session_chunk_dump(tid int, contextname char(64)) 描述：将指定线程创建的某个内存上下文申请的所有chunk信息打印成文件。 参数tid，表示线程ID；参数contextname，表示内存上下文名称。 返回值类型：boolean 示例： 1 2 3 4 5 SELECT * FROM pv_session_chunk_dump(140472797325280, 'Timezones'); pv_session_chunk_dump ----------------------- t (1 row)

pg_shared_chunk_detail(contextname char(64)) 描述：查询指定共享内存下内存上下文申请的所有chunk信息。 参数contextname，表示内存上下文名称。 使用该函数需先使用pg_shared_chunk_dump(contextname char(64))函数将相关信息打印成文件。 返回值类型：record 示例： 1 2 3 4 5 6 7 8 SELECT * FROM pg_shared_chunk_detail('pgstat file hash table'); parent | level | file_name | line_number | chunk_size | requested_number | total_size ---------------------+-------+--------------+-------------+------------+------------------+------------ pgstat file context | 2 | dynahash.cpp | 158 | 2048 | 1 | 2048 pgstat file context | 2 | dynahash.cpp | 158 | 256 | 1 | 256 pgstat file context | 2 | dynahash.cpp | 158 | 4096 | 9 | 36864 pgstat file context | 2 | dynahash.cpp | 158 | 8192 | 4 | 32768 (4 rows)

pv_session_chunk_detail(tid int, contextname char(64)) 描述：查询指定线程创建的某个内存上下文申请的所有chunk信息。 参数tid，表示线程ID；参数contextname，表示内存上下文名称。 使用该函数需使用pv_session_chunk_dump(tid int, contextname char(64))函数将相关信息打印成文件。 返回值类型：record 示例： 1 2 3 4 5 6 7 8 9 SELECT * FROM pv_session_chunk_detail(140178810990936, 'Timezones'); parent | level | file_name | line_number | chunk_size | requested_number | total_size ------------------+-------+---------------+-------------+------------+------------------+------------ TopMemoryContext | 1 | dynahash.cpp | 158 | 1280 | 2 | 2560 TopMemoryContext | 1 | dynahash.cpp | 158 | 160 | 1 | 160 TopMemoryContext | 1 | dynahash.cpp | 158 | 2560 | 2 | 5120 TopMemoryContext | 1 | localtime.cpp | 1965 | 128 | 1 | 128 TopMemoryContext | 1 | localtime.cpp | 1965 | 448 | 1 | 448 (5 rows)

pg_get_external_schema_table_options(text, text) 描述：获取external schema表的option。 入参: 第一个入参为external schema名称，第二个入参为表名称。 返回值类型：setof record 示例： 1 2 3 4 5 6 7 SELECT * FROM pg_get_external_schema_table_options('ex_lf', 'test_lf'); option_name | option_value -------------+------------------------------------ encoding | utf8 format | parquet foldername | /***/***/*** (3 rows)

pg_get_external_schema_table_col(text, text) 描述：获取external schema表的列信息。 入参: 第一个入参为external schema名称，第二个入参为表名称。 返回值类型：setof record 示例： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 SELECT * FROM pg_get_external_schema_table_col('ex_lf', 'test_lf'); col_name | col_type | part_col ------------------------+---------------+---------- field_smallint | smallint | f field_int | int | f field_integer | int | f fileld_bigint | bigint | f field_float | float | f field_double | double | f field_double_precision | double | f field_decimal | decimal(10,0) | f field_numeric | decimal(10,0) | f field_timestamp | timestamp | f field_date | date | f field_varchar | varchar(5) | f field_char | char(5) | f field_boolean | boolean | f field_string | string | f (15 rows)

pgxc_archive_scan_residualfiles(query_flag) 描述：pg_archive_scan_residualfiles()的CN统一执行函数。该函数为集群级函数，与当前所在的数据库相关，在CN实例上运行。 参数query_flag，参数类型int，用于表示执行范围。1表示CN，2表示主DN，4表示备DN，通过或运算可以得到查询并集，如1|2=3表示CN+主DN，1|2|4=7表示CN+主DN+备DN。默认值是7。 返回类型：record 返回信息如下： 表6 返回字段 名称 类型 描述 nodename name 节点名称。 instance_id text 实例名称。 archive text 归档后的本地文件夹路径。OBS路径的残留文件归档在对应OBS数据库目录下。 count bigint 归档的残留文件数量。 size bigint 归档的残留文件总大小，单位为byte。 示例： 1 2 3 4 5 6 SELECT * FROM pgxc_archive_scan_residualfiles(); node_name | instance_id | archive | count | size --------------+-------------+--------------------------------------------------------------+-------+------- cn_5001 | cn_5001 | pg_residualfiles/archive/pgscrf_archive_20231027145050896440 | 1 | 40960 dn_6007_6008 | dn_6008 | pg_residualfiles/archive/pgscrf_archive_20231027145051018138 | 1 | 24576 (2 rows) 此函数只能归档当前登录的数据库中记录过的残留文件，归档时会记录中的残留文件进行相应的校验，校验结果分为以下三种情况： 校验通过：校验通过后会对残留文件进行归档，并标记为已处理。 校验失败：校验失败会跳过归档，并标记为已处理。 延迟校验：延迟校验会跳过归档，通常表示当前未满足校验时机，延迟校验的情况通常和事务完成情况及备机REDO进度有关。 实际归档目录与对应表空间在同一个文件系统下，删除表空间也会删除对应的残留文件归档。 延迟DDL开启后，此函数无法使用。 此函数为重载操作，不支持单节点并发使用，也不建议在业务繁忙和资源负载较高时使用。

pg_rm_scan_residualfiles_archive() 描述：用于删除当前节点所有归档的残留文件记录。该函数为实例级函数，与当前所在的数据库无关，可以在任意实例上运行。 返回类型：record 返回信息如下： 表7 返回字段 名称 类型 描述 count bigint 归档中已删除的残留文件数量。本地路径的残留文件统计所删除的文件数，OBS路径的残留文件统计所删除的表目录数量。 size bigint 归档中已删除的残留文件总大小，单位为byte。OBS路径的残留文件该项均为0。 示例： 1 2 3 4 5 SELECT * FROM pg_rm_scan_residualfiles_archive(); count | size -------+------- 4 | 81920 (1 row)

pgxc_rm_scan_residualfiles_archive(query_flag) 描述：pg_rm_scan_residualfiles_archive()的CN统一执行函数。该函数为集群级函数，与当前所在的数据库无关，在CN实例上运行。 参数query_flag，参数类型int，用于表示执行范围。1表示CN，2表示主DN，4表示备DN，通过或运算可以得到查询并集，如1|2=3表示CN+主DN，1|2|4=7表示CN+主DN+备DN。默认值是7。 返回类型：record 返回信息如下： 表8 返回字段 名称 类型 描述 nodename name 节点名称。 instance_id text 实例名称。 count bigint 归档中已删除的残留文件数量。本地路径的残留文件统计所删除的文件数，OBS路径的残留文件统计所删除的表目录数量。 size bigint 归档中已删除的残留文件总大小，单位为byte。OBS路径的残留文件该项均为0。 示例： 1 2 3 4 5 6 7 8 SELECT * FROM pgxc_rm_scan_residualfiles_archive(); node_name | instance_id | count | size --------------+-------------+-------+------- dn_6001_6002 | dn_6001 | 4 | 81920 cn_5001 | cn_5001 | 1 | 40960 dn_6007_6008 | dn_6008 | 1 | 24576 coordinator1 | coordinator1| 1 | 0 (4 rows)

pg_archive_scan_residualfiles() 描述：用于归档当前节点所连接数据库上所有扫描到的残留文件记录。该函数为实例级函数，与当前所在的数据库相关，可以在任意实例上运行。 返回类型：record 返回信息如下： 表5 返回字段 名称 类型 描述 archive text 归档后的本地文件夹路径。OBS路径的残留文件归档在对应OBS数据库目录下。 count bigint 归档的残留文件数量。 size bigint 归档的残留文件总大小，单位为byte。 示例： 1 2 3 4 5 SELECT * FROM pg_archive_scan_residualfiles(); archive | count | size --------------------------------------------------------------+-------+------- pg_residualfiles/archive/pgscrf_archive_20231027144613791801 | 4 | 81920 (1 row) 此函数只能归档当前登录的数据库中记录过的残留文件，归档时会记录中的残留文件进行相应的校验，校验结果分为以下三种情况： 校验通过：校验通过后会对残留文件进行归档，并标记为已处理。 校验失败：校验失败会跳过归档，并标记为已处理。 延迟校验：延迟校验会跳过归档，通常表示当前未满足校验时机，延迟校验的情况通常和事务完成情况及备机REDO进度有关。 实际归档目录与对应表空间在同一个文件系统下，删除表空间也会删除对应的残留文件归档。 延迟DDL开启后，此函数无法使用。 此函数为重载操作，不支持单节点并发使用，也不建议在业务繁忙和资源负载较高时使用。

pg_get_scan_residualfiles() 描述：用于获取当前节点所有扫描到的残留文件记录。该函数为实例级函数，与当前所在的数据库无关，可以在任意实例上运行。 返回类型：record 返回信息如下： 表3 返回字段 名称 类型 描述 handled bool 残留文件是否已经被处理，被移动或者被更改。 dbname text 数据库名称。 residualfile text 残留文件路径。 size bigint 文件大小，单位为byte。OBS路径的残留文件该项为0。 inode bigint 残留文件stat信息中的Inode。OBS路径的残留文件该项为0。 atime timestamptz 残留文件stat信息中的Access time。OBS路径的残留文件该项为空。 mtime timestamptz 残留文件stat信息中的Modifie time。OBS路径的残留文件该项为空。 ctime timestamptz 残留文件stat信息中的Chang time。OBS路径的残留文件该项为空。 filepath text 记录残留文件信息的元文件本地路径，对应pgscrf_meta文件。 notes text 注释。

pg_scan_residualfiles() 描述：用于扫描当前节点所连接数据库的所有残留文件记录。该函数为库级函数，与当前所在的数据库相关，可以在任意实例上运行。 连接到CN执行时，扫描当前CN节点所在数据库的本地残留文件和OBS全部残留文件。 连接到DN执行时，扫描当前DN节点所在数据库的本地残留文件。 返回类型：record 返回信息如下： 表1 返回字段 名称 类型 描述 pgscrf text 记录残留文件信息的元文件本地路径。 示例： 1 2 3 4 5 6 SELECT * FROM pg_scan_residualfiles(); pgscrf -------------------------------------------------------------------- pg_residualfiles/pgscrf_meta_1663_16323_20231027143716005244 pg_residualfiles/pgscrf_meta_2147484281_16323_20231027143716005713 (2 rows)

pgxc_scan_residualfiles(query_flag) 描述：pg_scan_residualfiles()的CN统一执行函数。该函数为集群级函数，与当前所在的数据库相关，在CN实例上运行。 参数说明：query_flag。参数类型int，表示执行范围。1表示CN，2表示主DN，4表示备DN，通过或运算可以得到查询并集，如1|2=3表示CN+主DN，1|2|4=7表示CN+主DN+备DN，默认值是7。 返回类型：record 返回信息如下： 表2 返回字段 名称 类型 描述 nodename name 节点名称。 instance_id text 实例名称。 pgscrf text 记录残留文件信息的元文件本地路径。 示例： 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 SELECT * FROM pgxc_scan_residualfiles(); node_name | instance_id | pgscrf --------------+-------------+-------------------------------------------------------------------- dn_6001_6002 | dn_6001 | pg_residualfiles/pgscrf_meta_1663_16323_20231027144103839354 dn_6001_6002 | dn_6001 | pg_residualfiles/pgscrf_meta_2147484281_16323_20231027144103839826 cn_5001 | cn_5001 | pg_residualfiles/pgscrf_meta_1663_16323_20231027144103946217 dn_6007_6008 | dn_6008 | pg_residualfiles/pgscrf_meta_1663_16323_20231027144104171311 (4 rows) SELECT * FROM pgxc_scan_residualfiles(1); node_name | instance_id | pgscrf -----------+-------------+-------------------------------------------------------------- cn_5001 | cn_5001 | pg_residualfiles/pgscrf_meta_1663_16323_20231027144421861628 (1 row) SELECT * FROM pgxc_scan_residualfiles(2); node_name | instance_id | pgscrf --------------+-------------+-------------------------------------------------------------------- dn_6001_6002 | dn_6001 | pg_residualfiles/pgscrf_meta_1663_16323_20231027144424210395 dn_6001_6002 | dn_6001 | pg_residualfiles/pgscrf_meta_2147484281_16323_20231027144424210855 (2 rows) SELECT * FROM pgxc_scan_residualfiles(4); node_name | instance_id | pgscrf --------------+-------------+-------------------------------------------------------------- dn_6007_6008 | dn_6008 | pg_residualfiles/pgscrf_meta_1663_16323_20231027144427492060 (1 row)

pgxc_get_scan_residualfiles(query_flag) 描述：pg_get_scan_residualfiles()的CN统一执行函数。该函数为集群级函数，与当前所在的数据库无关，在CN实例上运行。 参数query_flag，参数类型int，用于表示执行范围。1表示CN，2表示主DN，4表示备DN，通过或运算可以得到查询并集，如1|2=3表示CN+主DN，1|2|4=7表示CN+主DN+备DN。默认值是7。 返回类型：record 返回信息如下： 表4 返回字段 名称 类型 描述 nodename name 节点名称。 instance_id text 实例名称。 handled bool 残留文件是否已经被处理，被移动或者被更改。 dbname text 数据库名称。 residualfile text 残留文件路径。 size bigint 文件大小，单位为byte。OBS路径的残留文件该项为0。 inode bigint 残留文件stat信息中的Inode。OBS路径的残留文件该项为0。 atime timestamptz 残留文件stat信息中的Access time。OBS路径的残留文件该项为空。 mtime timestamptz 残留文件stat信息中的Modifie time。OBS路径的残留文件该项为空。 ctime timestamptz 残留文件stat信息中的Chang time。OBS路径的残留文件该项为空。 filepath text 记录残留文件信息的元文件本地路径，对应pgscrf_meta文件。 notes text 注释。

range_retention_count 描述：记录每个用户的留存情况，该函数返回数组，可以作为range_retention_sum函数的入参。 语法 1 range_retention_count(is_first, is_active, dt, retention_interval, retention_granularity, output_format) 入参说明 is_first：bool类型，是否符合初始行为，true表示符合， false表示不符合 。 is_active：bool类型，是否符合留存行为，true表示符合， false表示不符合 。 dt：date类型，发生行为的日期。 retention_interval：数组类型，表示留存间隔，最多支持15个留存间隔。例如ARRAY[1,3,5,7,15,30]。 retention_granularity：text类型，表示留存分析粒度，执行日（day）、周（week）、月（month）三种。 output_format：text类型，表示输出格式，支持normal（默认）和expand（可取得每日留存明细）两种。 返回值：用户留存情况BIGINT数组。

示例 创建表funnel_test： CREATE TABLE IF NOT EXISTS funnel_test ( user_id INT , event_type TEXT, event_time TIMESTAMP, event_timez TIMESTAMP WITH TIME ZONE, event_time_int BIGINT ); 插入数据： INSERT INTO funnel_test VALUES (1,'浏览页面','2021-01-31 11:00:00', '2021-01-31 11:00:00+08', 10), (1,'单击商品','2021-01-31 11:10:00', '2021-01-31 11:10:00+07', 20), (1,'加入购物车','2021-01-31 11:20:00', '2021-01-31 11:20:00+06', 30), (1,'支付货款','2021-01-31 11:30:00', '2021-01-31 11:30:00+05', 40), (2,'加入购物车','2021-01-31 11:00:00', '2021-01-31 11:00:00+08', 11), (2,'支付货款','2021-01-31 11:10:00', '2021-01-31 11:10:00+08', 12), (1,'浏览页面','2021-01-31 11:00:00', '2021-01-31 11:00:00+01', 50), (3,'浏览页面','2021-01-31 11:20:00', '2021-01-31 11:20:00-04', 30), (3,'单击商品','2021-01-31 12:00:00', '2021-01-31 12:00:00-04', 80), (4,'浏览页面','2021-01-31 11:50:00', '2021-01-31 11:50:00-01', 1000), (4,'支付货款','2021-01-31 12:00:00', '2021-01-31 12:00:00-02', 900), (4,'加入购物车','2021-01-31 12:00:00', '2021-01-31 12:00:00-03', 1001), (4,'单击商品','2021-01-31 12:00:00', '2021-01-31 12:00:00-04', 1001), (5,'浏览页面','2021-01-31 11:50:00', '2021-01-31 11:50:00+08', NULL), (5,'单击商品','2021-01-31 12:00:00', '2021-01-31 12:00:00+08', 776), (5,'加入购物车','2021-01-31 11:10:00', '2021-01-31 11:10:00+08', 999), (6,'浏览页面','2021-01-31 11:50:00', '2021-01-31 11:50:00+01', -1), (6,'单击商品','2021-01-31 12:00:00', '2021-01-31 12:00:00+02', -2), (6,'加入购物车','2021-01-31 12:10:00', '2021-01-31 12:00:00+03', -3); 计算每个用户的漏斗情况。返回结果如下，其中level=0表示用户在窗口期内匹配最大事件深度为0，level=1表示用户在窗口期内匹配最大事件深度为1： SELECT user_id, windowFunnel( 0, 'default', event_timez, event_type = '浏览页面', event_type = '单击商品', event_type = '加入购物车', event_type = '支付货款' ) AS level FROM funnel_test GROUP BY user_id ORDER by user_id; user_id | level ---------+------- 1 | 1 2 | 0 3 | 1 4 | 1 5 | 1 6 | 1 (6 rows) 计算每个用户的漏斗情况，指定滑动的时间窗口的大小为NULL，返回报错： SELECT user_id, windowFunnel( NULL, 'default', event_time, event_type = '浏览页面', event_type = '单击商品', event_type = '加入购物车', event_type = '支付货款' ) AS level FROM funnel_test GROUP BY user_id ORDER by user_id; ERROR: Invalid parameter : window length or mode is null. 计算每个用户的漏斗情况，指定多个条件： SELECT user_id, windowFunnel( 40, 'default', date(event_time), true, true, false, true ) AS level FROM funnel_test GROUP BY user_id ORDER by user_id; user_id | level ---------+------- 1 | 2 2 | 2 3 | 2 4 | 2 5 | 2 6 | 2 (6 rows) 分析用户的留存情况： SELECT user_id, retention( event_type = '浏览页面', event_type = '单击商品', event_type = '加入购物车', event_type = '支付货款' ) AS r FROM funnel_test GROUP BY user_id ORDER BY user_id ASC; user_id | r ---------+----------- 1 | {1,1,1,1} 2 | {0,0,0,0} 3 | {1,1,0,0} 4 | {1,1,1,1} 5 | {1,1,1,0} 6 | {1,1,1,0} (6 rows) 分析用户的留存情况，指定第一个时间为false： SELECT user_id, retention( false, event_type = '浏览页面', event_type = '单击商品', event_type = '加入购物车', event_type = '支付货款' ) AS r FROM funnel_test GROUP BY user_id ORDER BY user_id ASC; user_id | r ---------+------------- 1 | {0,0,0,0,0} 2 | {0,0,0,0,0} 3 | {0,0,0,0,0} 4 | {0,0,0,0,0} 5 | {0,0,0,0,0} 6 | {0,0,0,0,0} (6 rows) 分析用户的留存情况总和： SELECT sum(r[1]), sum(r[2]), sum(r[3]), sum(r[4]) FROM ( SELECT retention(event_type = '浏览页面', event_type = '单击商品', event_type = '加入购物车', event_type = '支付货款') AS r FROM funnel_test GROUP BY user_id ); sum | sum | sum | sum -----+-----+-----+----- 5 | 5 | 4 | 2 (1 row) 统计每个用户在1,3,7天后的付费留存率： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 SELECT user_id, range_retention_count(event_type = '浏览页面', event_type = '支付货款', DATE(event_time), ARRAY[1, 3, 7], 'day') as r FROM funnel_test GROUP BY user_id ORDER BY user_id; user_id | r ---------+------------------ 1 | {80135499808768} 2 | {} 3 | {80135499808768} 4 | {80135499808768} 5 | {80135499808768} 6 | {80135499808768} (6 rows)

retention retention函数可以将一组条件作为参数，分析事件是否满足该条件。 语法 1 retention(cond1, cond2, ..., cond32); 入参说明 cond：变长boolean数组，最大长度32，用来表示事件是否满足特定条件。 GaussDB (DWS)只支持1~32个condition，不在此范围报错处理。 返回值 retention condition：tinyint数组类型。返回结果的表达式，与入参cond长度一致，若第cond1和condi条件满足，则返回值第i个值为1，否则为0。

windowfunnel windowfunnel函数用于在滑动的时间窗口中搜索事件列表并计算条件匹配的事件列表的最大长度。GaussDB(DWS)根据用户定义的事件列表，从第一个事件开始匹配，依次做有序最长的匹配，返回匹配的最大长度。一旦匹配失败，结束整个匹配。具体介绍如下： 假设在窗口足够大的条件下： 条件事件为c1，c2，c3，而用户数据为c1，c2，c3，c4，最终匹配到c1，c2，c3，函数返回值为3。 条件事件为c1，c2，c3，而用户数据为c4，c3，c2，c1，最终匹配到c1，函数返回值为1。 条件事件为c1，c2，c3，而用户数据为c4，c3，最终没有匹配到事件，函数返回值为0。 语法 1 windowFunnel(window, mode, timestamp, cond1, cond2, ..., condN) 入参说明 window：bigint类型。滑动的时间窗口的大小，指从第一个事件开始，往后推移的时间窗口大小，单位为秒。 mode：text类型。目前仅支持default模式，其他模式报错处理。Default模式是指在同一个窗口期内，从第一个事件开始匹配，尽量匹配多的事件。 timestamp：事件发生的时间范围，支持timestamp without time zone、timestamp with time zone、date、int、bigint类型。 cond：变长boolean数组。指当前Tuple的数据满足事件的哪个步骤。GaussDB(DWS)只支持1~32个condition，不在此范围报错处理。 返回值 level：int类型。条件匹配的事件列表的最大长度。

构造复合值 要把复合值写作文字常量，可以将字段值括在圆括号中，并用逗号分隔。可以在任何字段值加上双引号，如果字段值包含逗号或括号则必须这样做。复合常量的一般格式如下： 1 '( val1 , val2 , ... )' 上文中的'("fuzzy dice",42,1.99)'便属于inventory_item类型的一个合法值。 要让一个字段为NULL，在列表中对应位置上空出即可。如果需要一个字段为空字符串，使用引号即可。例如下列示例，第一个字段是非NULL空字符串，第三个是NULL： 1 '("",42,)' ROW表达式也能被用来构建组合值。例如： 1 2 ROW('fuzzy dice', 42, 1.99) ROW('', 42, NULL)

复合类型的声明 GaussDB(DWS)支持用户使用CREATE TYPE定义复合类型： 1 2 3 4 5 6 7 8 9 10 CREATE TYPE complex AS ( r double precision, i double precision ); CREATE TYPE inventory_item AS ( name text, supplier_id integer, price numeric ); 定义复合类型之后，可用来创建表或函数： 1 2 3 4 5 6 CREATE TABLE on_hand ( item inventory_item, count integer ); INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000); 1 2 3 4 CREATE FUNCTION price_extension(inventory_item, integer) RETURNS numeric AS 'SELECT $1.price * $2' LANGUAGE SQL; SELECT price_extension(item, 10) FROM on_hand;

范围的输入/输出 范围值的输入必须遵循下列模式之一： (lower-bound,upper-bound) (lower-bound,upper-bound] [lower-bound,upper-bound) [lower-bound,upper-bound] empty 圆括号或方括号指示上下界是否为排除的或者包含的。最后一个模式是empty，它表示一个空范围（一个不包含点的范围）。 lower-bound可以作为子类型的合法输入的一个字符串，或者是空，表示没有下界。同样，upper-bound可以是作为子类型的合法输入的一个字符串，或者是空，表示没有上界。 每个界限值可以使用双引号引用。如果界限值包含圆括号、方括号、逗号、双引号或反斜线时，则必须使用双引号引用，否则这些符号会被认作范围语法的一部分。要想把双引号或反斜线放在被引用的界限值中，需要在双引号或反斜线前面加一个反斜线（在双引号引用的界限值中的一对双引号表示一个双引号字符，这与SQL字符串中的单引号规则类似）。此外，可以避免引用并且使用反斜线转义来保护所有数据字符，否则它们会被当做返回语法的一部分。什么都不写则表示一个无限界限，因此，要表示空字符串的界限值，可以写成""。 范围值前后允许有空格，但是圆括号或方括号之间的任何空格会被当做上下界值的一部分（取决于元素类型，它可能是有意义的也可能是无意义的）。 示例 查询包括3，不包括7，并且包括3和7之间的所有点： SELECT '[3,7)'::int4range; int4range ----------- [3,7) (1 row) 查询既不包括3也不包括 7，但是包括之间的所有点： SELECT '(3,7)'::int4range; int4range ----------- [4,7) (1 row) 查询只包括单独一个点4： SELECT '[4,4]'::int4range; int4range ----------- [4,5) (1 row) 查询不包括点（并且将被标准化为 '空'）： SELECT '[4,4)'::int4range; int4range ----------- empty (1 row)

构造范围 每一种范围类型都有一个与其同名的构造器函数。使用构造器函数比写一个范围文字常数更方便，因为它避免了对界限值的额外引用。构造器函数接受两个或三个参数。两个参数的形式以标准的形式构造一个范围（包含下界，排除上界），而三个参数的形式按照第三个参数指定的界限形式构造一个范围。第三个参数必须是下列字符串之一： “()”、 “(]”、 “[)”或者 “[]”。 例如： 完整形式是：下界、上界以及指示界限包含性/排除性的文本参数： SELECT numrange(1.0, 14.0, '(]'); numrange ------------ (1.0,14.0] (1 row) 如果第三个参数被忽略，则假定为 '[)'： SELECT numrange(1.0, 14.0); numrange ------------ [1.0,14.0) (1 row) 尽管这里指定了'(]'，单返回结果时该值将被转换成标准形式，因为int8range是一种离散范围类型： SELECT int8range(1, 14, '(]'); int8range ----------- [2,15) (1 row) 界限使用NULL导致范围是无界的： SELECT numrange(NULL, 2.2); numrange ---------- (,2.2) (1 row)

包含和排除边界 每一个非空范围都有两个界限：下界和上界。这些值之间的所有点都被包括在范围内。包含界限意味着边界点本身也被包括在范围内，而排除边界意味着边界点不被包括在范围内。 在范围的文本形式中，包含下界用“[”表示，排除下界用“(”表示。同样，包含上界用“]”表示，排除上界用“)”表示。 函数lower_inc(anyrange)和lower_inc(anyrange)分别测试一个范围值的上下界。

无限（无界）范围 范围的下界可以省略，这意味着所有小于上界的值都包括在范围中，例如(,3]。同样，范围的上界被省略，则所有大于下界的值都包括在范围中。如果下界和上界都被省略，则该元素类型的所有值都被认为在范围内。如果缺失的边界指定为包含则自动将包含转换为排除，例如[,]将转换为(,)。可以将这些缺失的值视为+/-无穷大，但它们是特殊的范围类型值，并且被视为超出任何范围元素类型的+/-无穷大值。 具有“无穷大”概念的元素类型可以将其作为显式边界值。例如，在时间戳范围，[today,infinity)不包括特殊的timestamp值infinity，尽管[today,infinity]包括它，就好比 [today,)和[today,]。 函数lower_inf(anyrange)和upper_inf(anyrange)分别测试一个范围的无限上下界。

离散范围类型 离散范围是指其元素类型具有定义明确的“步长”的范围，如integer或date。在这些类型中，当两个元素之间没有有效值时，它们可以被说成是相邻。 离散范围类型的每个元素值都有一个明确的“下一个”或“上一个”值。这样就可以通过选择下一个或上一个元素值，在范围界限的包含和排除表达之间转换。例如，在整数范围类型中，[4,8]和(3,9)表示相同的值集合，但对于超过numeric的范围，情况并非如此。 离散范围类型应具有识别元素类型所需步长的规范函数。规范化函数负责将范围类型的等价值转换为具有相同的表示，特别是与包含或者排除界限一致。如果未指定规范化函数，则具有不同格式的范围将始终被视为不相等，即使它们实际上是表达相同的一组值。 内置范围类型int4range、int8range和daterange都使用规范形式，该形式包括下界并且排除上界，也就是[)。但是，用户定义的范围类型可以使用其他约定。

枚举类型的安全性 每一种枚举数据类型都是独立的并且不能和其他枚举类型相比较。 1 2 3 4 5 6 7 8 9 10 11 12 CREATE TYPE happiness AS ENUM ('happy', 'very happy', 'ecstatic'); CREATE TABLE holidays (num_weeks integer, happiness happiness); INSERT INTO holidays(num_weeks,happiness) VALUES (4, 'happy'); INSERT INTO holidays(num_weeks,happiness) VALUES (6, 'very happy'); INSERT INTO holidays(num_weeks,happiness) VALUES (8, 'ecstatic'); INSERT INTO holidays(num_weeks,happiness) VALUES (2, 'sad'); ERROR: invalid input value for enum happiness: "sad" SELECT person.name, holidays.num_weeks FROM person, holidays WHERE person.current_mood = holidays.happiness; ERROR: operator does not exist: mood = happiness 如果需要作比较，可以使用自定义的操作符或者在查询中加上显式类型： 1 2 3 4 5 6 SELECT person.name, holidays.num_weeks FROM person, holidays WHERE person.current_mood::text = holidays.happiness::text; name | num_weeks ------+----------- Moe | 4 (1 row)

枚举类型的声明 枚举类型可以使用CREATE TYPE命令创建，例如： 1 CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy'); 枚举类型被创建后，可以在表和函数定义中使用： 1 2 3 4 5 6 7 8 CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy'); CREATE TABLE person (name text, current_mood mood); INSERT INTO person VALUES ('Moe', 'happy'); SELECT * FROM person WHERE current_mood = 'happy'; name | current_mood ------+-------------- Moe | happy (1 row)

修改数组 更新数组 更新整个数组数据： UPDATE books SET price_by_quarter = '{30,30,30,30}' WHERE title = 'Robinson Crusoe'; 使用ARRAY表达式语法更新整个数组数据： UPDATE books SET price_by_quarter = ARRAY[30,30,30,30] WHERE title = 'Robinson Crusoe'; 更新数组中的一个元素： UPDATE books SET price_by_quarter[4] = 35 WHERE title = 'Robinson Crusoe'; 更新数组中的一个切片元素： UPDATE books SET price_by_quarter[1:2] = '{27,27}' WHERE title = 'Robinson Crusoe'; 一个已存储的数组值可以被通过对其还不存在的元素赋值来扩大大小。任何位于已存在的元素和新元素之间的位置都将被空值填充。例如，如果数组myarray目前有4个元素，使用UPDATE对myarray[6]赋值后它将有6个元素，其中myarray[5]为空值。目前，采用这种方式扩大数组只允许使用在一维数组上。 构建新数组 新的数组值也可以通过串接操作符“||”构建。串接操作符允许把一个单独的元素加入到一个一维数组的开头或末尾。也可接受两个N维数组，或者一个N维数组和一个N+1维数组。 SELECT ARRAY[1,2] || ARRAY[3,4]; ?column? ----------- {1,2,3,4} (1 row) SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]]; ?column? --------------------- {{5,6},{1,2},{3,4}} (1 row) 使用函数array_prepend、array_append或array_cat构建数组。 SELECT array_prepend(1, ARRAY[2,3]); array_prepend --------------- {1,2,3} (1 row) SELECT array_append(ARRAY[1,2], 3); array_append -------------- {1,2,3} (1 row) SELECT array_cat(ARRAY[1,2], ARRAY[3,4]); array_cat ----------- {1,2,3,4} (1 row) SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]); array_cat --------------------- {{1,2},{3,4},{5,6}} (1 row) SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]); array_cat --------------------- {{5,6},{1,2},{3,4}} (1 row)

数组类型的定义 一个数组数据类型可以通过在数组元素的数据类型名称后面加上方括号（[]）来命名。 例如，创建表books，其中表示书本价格的列price的类型为一维integer类型数组，表示书本标签的列tag的类型为二维text类型数组。 1 CREATE TABLE books (id SERIAL PRIMARY KEY, title VARCHAR(100), price_by_quarter int[], tags TEXT[][]); CREATE TABLE语法可以指定数组的大小，例如： 1 CREATE TABLE test ( a int[3]); 当前的数据库实现会忽略语句中数组的大小限制，即其行为与未指定长度的数组相同。同时，也不会强制所声明的维度数。一个特定元素类型的数组全部被当作是相同的类型，而忽略其大小或维度数。 也可以使用关键词ARRAY来定义一维数组。表books中的列price使用ARRAY定义并指定数组大小，如下所示： 1 price_by_quarter int ARRAY[4] 使用ARRAY定义，不指定数组尺寸： 1 price_by_quarter int ARRAY