The sandbox framework : 5. Utililities

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$

A simple

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s