The sandbox framework : 2. A fdw table definitions server

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 to table to map with fdw, it is tedious to write for each such a statement.

Furthemore, 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 :

• to_table_name
• from_schema_name
• to_schema_name

In the function where we create the fdw server, we create a foreign table that maps this view with 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.

This function queries the 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 :

  tables.table_schema as schema_name
 ,get_fdw_table_definition(tables.table_schema, tables.table_name) as fdw_table_definition
  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 (
         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
        pg_attribute att
    join pg_type ty
        on ty.oid = atttypid
    join pg_namespace tn
        on tn.oid = ty.typnamespace
    join pg_class cl
        on cl.oid = att.attrelid
    join pg_namespace na 
        on na.oid = cl.relnamespace
    left outer join pg_type et 
        on et.oid = ty.typelem
    left outer join pg_attrdef def 
        on adrelid = att.attrelid and adnum = att.attnum
        cl.relkind in ('v','r')   -- take only tables and views
        na.nspname = this_schema_name 
        cl.relname = this_table_name 
        att.attnum > 0
    order by
       '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 '
    || 'QUOTE'
    || 'QUOTE'
    || ','
    || 'table_name'
    || ' QUOTE'
    || table_name
    || 'QUOTE'
    || '); '
group by
    table_schema, table_name