/script_foreign_tables.sql Secret
Last active
October 30, 2019 23:39
Star
You must be signed in to star a gist
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* 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