The sandbox framework : 6. The attach functions

The following function is central : it maps a foreign table in the sandbox.perform

create or replace function public.attach_table(
	this_database_name text,
	this_from_table_name text,
	this_to_table_name text default null,
	this_from_schema_name text default 'public',
	this_to_schema_name text default 'public')
    returns boolean
    language 'plpgsql'
    cost 100
    volatile 
as $BODY$

declare
    this_fdw_server_name text;
    this_fdw_table_definition text;
    sql_stmt text;
    this_fdw_table_name text;
    fdw_server_exists boolean;
begin

    sql_stmt =     'select public.is_database_connected('
                || ''''
                || this_database_name
                || ''''
                || ');';

    execute sql_stmt into fdw_server_exists;
    
    if fdw_server_exists is false then
        perform public.write2log(
		      'ERROR : attach_table : '  
        	   || quote_ident(this_database_name)
        	   || ' is not connected'
   	 	);
	return false;
    end if;
    
    if this_to_table_name is null then
    	this_to_table_name = this_from_table_name;
    end if;
    
    this_fdw_server_name = 'fdw_' || this_database_name;
        
    this_fdw_table_name = this_database_name || '_fdw_table_definitions';

    sql_stmt =     'select fdw_table_definition from '
               || quote_ident(this_fdw_table_name)
               || ' where table_name = '
               || ''''
               || this_to_table_name
               || ''''
               || ' and schema_name = '
               || ''''
               || this_from_schema_name
               || ''''
               || ';';

    execute sql_stmt into this_fdw_table_definition;
    
    if this_fdw_table_definition is null then
    	 perform public.write2log(
		     'ERROR : attach_table : no fdw definition for table '  
        	   || quote_ident(this_from_table_name)
   		 );
	 return false;
    end if;
 
    -- prefix the name of the table to be mapped with the database name
    this_to_table_name = this_database_name || '_' || this_to_table_name;
    
    select replace(this_fdw_table_definition, 
                   'THIS_FDW_SERVER_NAME', 
                   quote_ident(this_fdw_server_name)
                  ) 
    	into this_fdw_table_definition;
    
    select replace(this_fdw_table_definition, 
                   'THIS_TO_TABLE_NAME', 
                   quote_ident(this_to_table_name)
                  ) 
    	into this_fdw_table_definition;
    
    select replace(this_fdw_table_definition, 
                   'QUOTE', 
                   ''''
                  ) 
     	into this_fdw_table_definition;
        
    select replace(this_fdw_table_definition, 
                   'THIS_FROM_SCHEMA_NAME', 
                   quote_ident(this_from_schema_name)
                  ) 
    	into this_fdw_table_definition;
     
    select replace(this_fdw_table_definition, 
                   'THIS_TO_SCHEMA_NAME', 
                   quote_ident(this_to_schema_name)) 
    	into this_fdw_table_definition;
    
    execute this_fdw_table_definition;

    exception when others then 
  	perform public.write2log(
		        'ERROR : attach_table for table : '  
       	 	      || quote_ident(this_from_table_name)
        	      || sqlerrm
    	           );
	return false;
end 
$BODY$;

This one maps all the relevant tables of a database (the tables for this database type) in the sandbox. And for each table, this function calls attach_table.

create or replace function public.attach_database(this_database_name text)
    returns boolean
    language 'plpgsql'
    cost 100
    volatile 
as $BODY$
declare
    r record;
begin
    for r in     
        select     
            distinct pg_table_name, pg_schema_name     
        from         
            permissions_matrix p         
        inner join         
            sdbx_tables_2_attach s        
        on           
            p.database_type = s.database_type          
        where             
            p.pg_database_name = this_database_name
    loop

    	perform public.attach_table(
       		   this_database_name,
               	   r.pg_table_name,
                   r.pg_table_name,
        	   r.pg_schema_name
       		);
        
    end loop;
	
    return true;
    
    exception when others then 
        perform public.write2log(
                 'ERROR : attach_database for database : '  
               || quote_ident(this_database_name)
               || ', '
               || sqlerrm
            );
        return false;
end 
$BODY$;