In postgres I can define custom option using
set x.x = 12;
I can also read it using following query
SHOW x.x;
But I couldn’t find any command to list all such defined options. Is there anything I can use to get a list of custom defined options?
There is not any possibility to show list of custom configuration options.
You’d have to plan that ahead of time. If you list the x.x
setting in postgresql.conf
, it’ll show up in pg_settings
system view where you could then check if its current value is different from its reset/reboot value:
select name,setting,boot_val,reset_val
from pg_settings
where setting is distinct from boot_val
and setting is distinct from reset_val;
But if you only run set x.x=12;
without having it in there beforehand, it won’t show in that view.
Or, as suggested by @Luuk, you could set up your own duplicate set of tables that are meant to store your own settings, then
select set_config(name,default_setting)
from your_settings
where owner=current_user;
At the start of each session to load the profile. Afterwards, to check which ones have been modified:
select current_setting(name,true)
from your_settings
where owner=current_user
and current_setting(name,true) is distinct from default_setting;
Thing is, that’s duplicating/emulating a set of features that are already built-in.
It’s called SQL:
postgres=# CREATE TEMP TABLE x(x INT);
CREATE TABLE
postgres=# insert into x values(12);
INSERT 0 1
postgres=# select * from x;
x
----
12
(1 row)