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_nameschema_name’, table_nametable_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 :
• to_table_name
• from_schema_name
• to_schema_name

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.

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