Skip to content

Instantly share code, notes, and snippets.

@gorthx
Last active October 30, 2019 23:39
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save gorthx/0b10eb07e918cf2378e8 to your computer and use it in GitHub Desktop.
/* 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
$$;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment