默认情况下,普通用户是无法查看data_directory
等目录相关参数,只能由超级用户来查看。
直接上函数语句:
必须以超级用户来创建
CREATE OR REPLACE function public.list_settings(OUT name text, OUT setting text, OUT unit text, OUT category text, OUT short_desc text, OUT extra_desc text, OUT context text, OUT vartype text, OUT source text, OUT min_val text, OUT max_val text, OUT enumvals text[], OUT boot_val text, OUT reset_val text, OUT sourcefile text, OUT sourceline integer)
returns setof record
as
$$
select *
from pg_catalog.pg_show_all_settings();
$$
language sql
security definer;
参数解释:
- security definer:是以创建者的权限来运行函数
opengauss实现函数: 链接
效果:
gaussdb=> select name,setting from pg_settings where name='data_directory';
name | setting
------+---------
(0 rows)
gaussdb=> select name,setting from public.list_settings() where name='data_directory';
name | setting
----------------+-----------------------
data_directory | /data/cluster/data/cn
(1 row)