/* 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