PostgreSQL 接続先環境の確認用ファンクション

日々いろんな環境へ接続して作業をするので誤った環境での操作を予防のために使っています。DBへの接続後、ホスト名の取得ができないのがどうも不便と感じてしまい自作して使っています。（PostgreSQLの場合、接続先のIPアドレスは取得できますがなぜかホスト名は取得できないんですよね、、これがどうも不便と感じてしまいやむなく自作しました）

概要

                   ┌─────────────┐
                   │    function              │指定キーワードに対応する
 ┌─────┐    ├─────────────┤DBプロフィール値を返却する
 │ table    │<-- │current_profile(item_name)│
 ├─────┤    │  returns text            │
 │dbprofile │    └─────────────┘
 │          │    
 │          │<-- ┌─────────────┐
 │          │    │    function table        │全てのDBプロフィール値を
 └─────┘    ├─────────────┤返却する
                   │current_profiles()        │
                   │  returns table           │
                   └─────────────┘

プロフィール値保持テーブル(dbprofile)の作成用DDL

create table  public.dbprofile (
        item_physics_name   text        -- 項目物理名
    ,   item_logical_name   text        -- 項目論理名
    ,   item_value          text        -- 項目値
    ,   note                text        -- 備考
    ,   dispno              integer     -- 表示順
    ,   last_updater        text        -- 最終更新者
    ,   last_updating_time  timestamp   -- 最終更新日時
)   ;

comment on table dbprofile is 'DBプロフィール';
comment on column dbprofile.item_physics_name is '項目物理名';
comment on column dbprofile.item_logical_name is '項目論理名';
comment on column dbprofile.item_value is '項目値';
comment on column dbprofile.note is '備考';
comment on column dbprofile.dispno is '表示順';
comment on column dbprofile.last_updater is '最終更新者';
comment on column dbprofile.last_updating_time is '最終更新日時';

alter table dbprofile alter column item_logical_name set not null;
alter table dbprofile alter column item_value set not null;

alter table dbprofile add constraint pk_dbprofile primary key (
        item_logical_name
)   ;

プロフィール値保持テーブル(dbprofile)の作成用DML

以下各自環境用に書き換えて使ってください。

insert into public.dbprofile(item_physics_name,item_logical_name,item_value,note,dispno,last_updater,last_updating_time) values('host_name','ホスト名','vm102','sooni用マシン',1,'sooni',current_timestamp);
insert into public.dbprofile(item_physics_name,item_logical_name,item_value,note,dispno,last_updater,last_updating_time) values('system_name','システム名','スーニ―システム','',3,'sooni',current_timestamp);
insert into public.dbprofile(item_physics_name,item_logical_name,item_value,note,dispno,last_updater,last_updating_time) values('system_startup_date','システム稼働開始日','2023/12','',5,'sooni',current_timestamp);
insert into public.dbprofile(item_physics_name,item_logical_name,item_value,note,dispno,last_updater,last_updating_time) values('environment','稼働環境','開発環境','',2,'sooni',current_timestamp);
insert into public.dbprofile(item_physics_name,item_logical_name,item_value,note,dispno,last_updater,last_updating_time) values('system_version','System Version','Ver.0.9','',4,'sooni',current_timestamp);

current_profile() 指定キーワードに対応するプロフィール値を取得

上記テーブル作成を行った同じユーザで作成する事想定しています。security definerとする事で上記テーブルへ直接アクセスできないユーザからも呼び出し可能になっています。

create or replace function public.current_profile(item_name text)
	returns text
	language 'plpgsql'
	security definer
as $body$

declare
	vitem_value     public.dbprofile.item_value%type = null;
begin
    select item_value into vitem_value
    from public.dbprofile where lower(item_physics_name) = lower(item_name);
    return vitem_value;
end;
$body$;

current_profiles () 全てのDBプロフィール値を全て取得

create or replace function public.current_profiles ()
	returns table (
		item_physics_name      text,
		item_logical_name      text,
		item_value             text
	)
	language plpgsql
	security definer
as $body$
begin
	return query
	select t.item_physics_name,t.item_logical_name,t.item_value 
	from public.dbprofile t
	order by dispno;
end;
$body$;

接続先プロフィール情報出力（個別）

select public.current_profile('host_name');

接続先プロフィール情報出力（全件）

select * from public.current_profiles();

実行例

esdb=# select public.current_profile('host_name');
 current_profile
-----------------
 e-search-dev
(1 行)


esdb=# select * from public.current_profiles();
  item_physics_name  | item_logical_name  |  item_value
---------------------+--------------------+--------------
 host_name           | ホスト名           | e-search-dev
 environment         | 稼働環境           | 開発環境
 system_name         | システム名         | e-search
 system_version      | System Version     | Ver.0..9
 system_startup_date | システム稼働開始日 | 2023/12
(5 行)


esdb=#