To create a foreign table, we have to write :
create foreign_table_name ( col1 data_type1, col2 data_type2, col3 data_type3, ... ) server fdw_server_name options (schema_name ‘schema_name’, table_name ‘table_name’)
If you have a lot of tables to map with fdw, it is tedious to write for each such a statement.
Furthermore, if you modify tables on the source database, you must modify the create foreign table statements.
Our solution solves both problems.
On each database from which we want take tables, we have a view fdw_table_definitions with the create foreign table statement for all tables and views.
This string contains placeholders for variables that will be given when we map the table in the destination database :
In the function where we create the fdw server (that will be explained later), we create a foreign table that maps this view.
We give to this view the name database_name_fdw_table_definitions where database_name is the name of the source database.
Each time that the application needs to map a table, a function attach_table is issued that substitutes placeholders with actual values and executes the statement create foreign table.
This function reads the view database_name_fdw_table_definitions, creates dynamically the create foreign table statement and executes it.
The query to create the fdw table definitions comes from the excellent site Postgres OnLine Journal. This query has been customised for the sandbox framework.
Let’s start with the view fdw_table_definitions :
select tables.table_schema as schema_name ,tables.table_name ,get_fdw_table_definition(tables.table_schema, tables.table_name) as fdw_table_definition from information_schema.tables where tables.table_schema not in ('pg_catalog', 'information_schema');
This view calls the function get_fdw_table_definition :
create or replace function public.get_fdw_table_definition( this_schema_name text, this_table_name text ) returns text language 'sql' as $body$ with cols as ( select cl.relname as table_name ,na.nspname as table_schema ,att.attname as column_name ,format_type(ty.oid,att.atttypmod) as column_type ,attnum as ordinal_position from pg_attribute as att join pg_type as ty on ty.oid = atttypid join pg_namespace as tn on tn.oid = ty.typnamespace join pg_class as cl on cl.oid = att.attrelid join pg_namespace as na on na.oid = cl.relnamespace left outer join pg_type as et on et.oid = ty.typelem left outer join pg_attrdef as def on adrelid = att.attrelid and adnum = att.attnum where cl.relkind in ('v','r') -- take only tables and views and na.nspname = this_schema_name and cl.relname = this_table_name and att.attnum > 0 order by att.attnum ) select 'create foreign table THIS_TO_TABLE_SCHEMA.THIS_TO_TABLE_NAME (' || string_agg(quote_ident(column_name) || ' ' || column_type, ', ' order by ordinal_position) || ') server this_fdw_server_name options (schema_name ' || '''' || 'THIS_FROM_SCHEMA_NAME' || '''' || ',' || 'table_name ' || '''' || table_name || '''' || '); ' from cols group by table_schema, table_name $body$;
THIS_TO_TABLE_SCHEMA, THIS_TO_TABLE_NAME and THIS_FROM_SCHEMA_NAME are the placeholders.