The following function gives the IP address of the server where this database resides and the port number. The usual value is 5432. If we have a server with multiple instances of PostgreSQL servers, this value can be different.
create or replace function public.get_database_parameters( this_database_name text, this_username text, out pg_server_address text, out pg_port_number text) returns record language 'sql' AS $BODY$ select pg_server_address, pg_port_number from permissions_matrix where pg_database_name = this_database_name and pg_user_name = this_username; $BODY$;
The two following functions dont need commentaries :
create or replace function public.is_database_connected(this_database_name text) returns boolean language 'plpgsql' as $BODY$ declare r text; begin select srvname into r from pg_foreign_server join pg_foreign_data_wrapper w on w.oid = srvfdw where srvname = 'fdw_' || this_database_name; if r is not null then return true; else return false; end if; end $BODY$;
create or replace function public.list_fdw_servers() returns setof record language 'sql' as $BODY$ select srvname as name, srvowner::regrole as owner, fdwname as wrapper, srvoptions as options from pg_foreign_server join pg_foreign_data_wrapper w on w.oid = srvfdw; $BODY$;
This one is the most important of the framework :
create or replace function public.write2log(msg text) returns void language 'plpgsql' as $BODY$ begin insert into public.log (info, timestamp) values (msg, now()); end $BODY$
select * from log order by 1 desc
gives the last error messages.
An amusing detail : when we give access rights to the table log, we must also give access rights to the sequence. It is not automatically done by PostgreSQL.