|
如何禁止KingbaseES的ksql查询结果自动分页
禁止ksql自动分页，可以：
- 在ksql中执行
\pset pager off命令关闭查询结果自动分页。 - 在当前用户的
HOME目录下，编辑一个名称为.ksqlrc，文件，文件中输入\pset pager off命令，关闭查询结果自动分页。
默认安装的KingbaseES V8的ksql，在输出结果时会自动分页，具体现象如下：
[kingbase@vm21 ~]$ ksql -U system test
ksql (V8.0)
Type "help" for help.
test=# \dS
List of relations
Schema | Name | Type | Owner
-------------+----------------------------------+---------------+--------
pg_catalog | all_arguments | view | system
pg_catalog | dba_arguments | view | system
pg_catalog | kdb_job | table | system
pg_catalog | pg_aggregate | table | system
pg_catalog | pg_am | table | system
pg_catalog | pg_amop | table | system
pg_catalog | pg_amproc | table | system
pg_catalog | pg_attrdef | table | system
pg_catalog | pg_attribute | table | system
pg_catalog | pg_auth_members | table | system
pg_catalog | pg_authid | table | system
pg_catalog | pg_available_extension_versions | view | system
pg_catalog | pg_available_extensions | view | system
pg_catalog | pg_cast | table | system
pg_catalog | pg_class | table | system
pg_catalog | pg_collation | table | system
pg_catalog | pg_config | view | system
pg_catalog | pg_constraint | table | system
pg_catalog | pg_constraint_status | table | system
pg_catalog | pg_context | table | system
pg_catalog | pg_conversion | table | system
pg_catalog | pg_cursors | view | system
pg_catalog | pg_database | table | system
pg_catalog | pg_db_role_setting | table | system
pg_catalog | pg_default_acl | table | system
pg_catalog | pg_depend | table | system
pg_catalog | pg_description | table | system
--More--
即使在控制台中直接执行SQL语句，也会自动分页。
[kingbase@vm21 ~]$ ksql -U system -d test -c 'select relname from pg_class'
relname
------------------------------------------------------
t_rman
walminer_contents
pg_toast_16413
pg_toast_16413_index
t_dexp
t_dimp
t_exp
t_imp
t_kexp
t_kimp
t_dmrman
t_dmrman_args
t_rman_args
t_sys_rman_args
pg_statistic
pg_type
t_sys_rman
pg_toast_2600
pg_toast_2600_index
pg_ts_template
pg_toast_2604
pg_toast_2604_index
pg_toast_3456
pg_toast_3456_index
pg_toast_2606
pg_toast_2606_index
pg_toast_826
pg_toast_826_index
--More--
这一特性与其它数据库产品（如mysql）的默认行为不太一致，一些初学KingbaseES的用户可能会感到不适应。
虽然在控制台执行SQL时，可以通过将结果重定向到一个文件来输出全部内容，但在一些场景下还是感到有点不太方便。
要解决这个问题，可以通过执行\pset pager off，来关闭自动分页。
[kingbase@vm21 ~]$ ksql -U system test
ksql (V8.0)
Type "help" for help.
test=# \pset pager off
Pager usage is off.
test=#
这样后面的查询结果，就会全部输出，不再分页。
[kingbase@vm21 ~]$ ksql -U system test
ksql (V8.0)
Type "help" for help.
test=# \pset pager off
Pager usage is off.
test=# select relname from pg_class;
relname
------------------------------------------------------
walminer_contents
pg_toast_16413
pg_toast_16413_index
pg_statistic
pg_type
......
sys_views
sys_triggers
kdb_job
dba_arguments
all_arguments
user_arguments
v$context
sys_stat_statements
(743 rows)
test=#
这样的设置，只能在当前的ksql环境中生效，当重新进入ksql时，还需要再次设置。
可以通过在当前用户的Home目录下（如/home/kingbase），编辑一个名称为.ksqlrc，文件，文件中输入\pset pager off命令，这样以后再次退出ksql时，就不会再进行结果输出的分页了。
[kingbase@vm21 ~]$ cat .ksqlrc
\pset pager off
再次进入ksql。
[kingbase@vm21 ~]$ ksql -U system test
Pager usage is off.
ksql (V8.0)
Type "help" for help.
test=# \dS
List of relations
Schema | Name | Type | Owner
-------------+----------------------------------+---------------+--------
pg_catalog | all_arguments | view | system
pg_catalog | dba_arguments | view | system
pg_catalog | kdb_job | table | system
pg_catalog | pg_aggregate | table | system
pg_catalog | pg_am | table | system
.....
sys_catalog | sys_ts_template | view | system
sys_catalog | sys_type | view | system
sys_catalog | sys_user | view | system
sys_catalog | sys_user_mapping | view | system
sys_catalog | sys_user_mappings | view | system
sys_catalog | sys_views | view | system
(406 rows)
test=#
|