Utilities

The function get_database_parameters 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$;
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$;
create or replace function public.write2log(msg text)
    returns void
    language 'plpgsql'
as $BODY$
begin

        insert into public.log 
		(id, info, timestamp)
	values   
		(nextval('public.log_id_seq'), msg, now());

end $BODY$