The sandbox framework : 4. The function connect_database

The front end applications select the databases to be connected to the sandbox.

The function connect_database creates in the sandbox the foreign data wrapper for the selected database and maps in the sandbox the view fdw_table_definitions of this database with the name database_name_fdw_table_definitions.

This view will be used by the function attach_table that will map the selected tables.

As usual, the name of the variables are self explanatory. Comments are seldom used. It minimizes the risk to have a contradiction between the code and the comments.

create or replace function public.connect_database( 
    this_database_name text, 
    this_user_name text, 
    this_password text
    ) 
    returns boolean 
    language 'plpgsql' 
    cost 100 
    volatile security definer  
as $BODY$ 
declare 
    sql_stmt text; 
    fdw_server_exists boolean; 
    this_fdw_server_name text; 
    this_host_address text; 
    this_port_number text; 
    this_fdw_table_name text; 
begin

    sql_stmt =     'select pg_server_address, pg_port_number '
    		|| 'from public.get_database_parameters('
                || ''''
                || this_database_name
                || ''''
                || ','
                || ''''
                || this_user_name
                || '''' 
                || ')';
                
    execute sql_stmt into this_host_address, this_port_number;
    
    
    this_fdw_server_name = 'fdw_' || this_database_name;
    
    sql_stmt =     'select public.is_database_connected('
                || ''''
                || this_database_name
                || ''''   
                || ');';

    execute sql_stmt into fdw_server_exists;

    if fdw_server_exists is true then
        return true;
    end if;

    sql_stmt = 	   'create server '
    		|| quote_ident(this_fdw_server_name)
		|| ' foreign data wrapper postgres_fdw '
		|| ' options (host '
                || ''''
                || this_host_address
                || ''''
                || ', dbname '
		|| ''''
		|| this_database_name
                || ''''
                || ', port '
                || ''''
                || this_port_number
		|| ''''
                || ')';
 
    execute sql_stmt;
    
    
    sql_stmt = 	   'create user mapping for public '
    		|| ' server '
                || this_fdw_server_name
                || ' options (user '
                || ''''
                || this_user_name
               	|| ''''
                || ', password '
                || ''''
                || this_password
                || ''''
                || ')';
  
    execute sql_stmt;
    
    
    sql_stmt =     'grant usage on foreign server '
    		|| quote_ident(this_fdw_server_name)
                || ' to '
                || quote_ident(this_user_name);
                
               
    execute sql_stmt;
     
    this_fdw_table_name = this_database_name || '_fdw_table_definitions';

    sql_stmt =     'create foreign table ' 
            	|| quote_ident(this_fdw_table_name)
            	|| ' ('
            	|| 'schema_name text,'
            	|| 'table_name text,'
            	|| 'fdw_table_definition text'
            	|| ') server '
            	|| quote_ident(this_fdw_server_name)
            	|| ' options ('
            	|| ' schema_name '
            	|| ''''
            	|| 'public'
            	|| ''''
            	|| ','
            	|| ' table_name '
            	|| ''''
            	|| 'fdw_table_definitions'
            	|| ''''
            	|| ')';

    execute sql_stmt;
    
    perform public.write2log(
		  'connect_database : '  
               || quote_ident(this_database_name)
               || ' connected'
    );
    return true;
    
    exception when others then 
        perform public.write2log(
		        'ERROR : connect_database : '  
                     || sql_stmt 
        	     || ', ' 
        	     || sqlerrm
         );
	return false;
    
end 

$BODY$;

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