script_foreign_tables.sql

/* my riff on Leo & Regina's function found here:
http://www.postgresonline.com/journal/archives/322-Generating-Create-Foreign-Table-Statements-for-postgres_fdw.html
*/

/* NOTE that as of 9.5, you can just IMPORT FOREIGN SCHEMA https://www.postgresql.org/docs/12/sql-importforeignschema.html */

CREATE FUNCTION script_foreign_tables(param_server text, param_schema_search text, param_table_search text, updatable_tf text) RETURNS SETOF text
    LANGUAGE sql
    AS $$
-- params: param_server: name of foreign data server; also name of target schema
--        param_schema_search: wildcard search on schema use % for non-exact
--        param_table_search: name of table
--        updatable_tf: true or false
-- example usage: SELECT script_foreign_tables('my_data_warehouse', 'public', 'my_dimension', 'true');
  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 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
     WHERE
     -- only consider non-materialized views and concrete tables (relations)
     cl.relkind IN('v','r')
      AND na.nspname LIKE param_schema_search AND cl.relname LIKE param_table_search
       AND cl.relname NOT IN('spatial_ref_sys', 'geometry_columns'
          , 'geography_columns', 'raster_columns')
       AND att.attnum > 0
       AND NOT att.attisdropped
     ORDER BY att.attnum )
        SELECT 'CREATE FOREIGN TABLE ' || param_server || '.' || table_name || ' ('
         || string_agg(quote_ident(column_name) || ' ' || column_type
           , '
, ' ORDER BY ordinal_position)
         || ')
SERVER ' || quote_ident(param_server) || '
OPTIONS (schema_name ''' || quote_ident(table_schema
     || ''', table_name ''' || quote_ident(table_name) || ''', updatable ''' || updatable_tf || '''); ' AS result
FROM cols
  GROUP BY table_schema, table_name
$$;