The following function is central : it maps a foreign table in the sandbox.
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$;