How to get list of customized options in postgres

  Kiến thức lập trình

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)

Theme wordpress giá rẻ Theme wordpress giá rẻ Thiết kế website

LEAVE A COMMENT