This is a huge win for improving the usability of foreign data wrappers. The initial setup today is a bit pain and convenience tools as this will get many more people using them. Though as 9.5 is at minimum a full year a way you could get some similar result of this from the query below. The query will create SQL to run on the one you're trying to create the foreign table to, to establish all tables in the DB as foreign ones.
SELECT
'CREATE FOREIGN TABLE '
|| quote_ident('#{prefix}_' || c.relname)
|| '(' || array_to_string(array_agg(quote_ident(a.attname) || ' ' || t.typname), ', ') || ') '
|| ' SERVER #{prefix}_db OPTIONS'
|| ' (schema_name ''' || quote_ident(n.nspname) || ''', table_name ''' || quote_ident(c.relname) || ''');'
FROM
pg_class c,
pg_attribute a,
pg_type t,
pg_namespace n
WHERE
a.attnum > 0
AND a.attrelid = c.oid
AND a.atttypid = t.oid
AND n.oid = c.relnamespace
AND c.relkind in ('r', 'v')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
GROUP BY c.relname, n.nspname
ORDER BY c.relname
Unrelated: Most SQL is typed in upper-case, and so I read it in my head as a robotic overlord, commanding the masses to IMPORT FOREIGN SCHEMA OR FACE ANNIHILATION.
I agree. Yell-caps in SQL are gross. I write all my sql in lowercase. It is case insensitive, every keyword. There is no other language that I write in where I don't use camel-case or underscore. SQL isn't going to be the exception.
Yell-caps in SQL are gross. I write all my sql in lowercase.
This may not be the right battle to be fighting. SQL reserved words have been written in all caps for a Very Long Time - it is a standard. From my personal experience, when I encounter legacy code where SQL keywords are lowercase I immediately think that the statements have been written by an inexperienced engineer (probably without any formal education).
The last time I encountered SQL statements with lowercase keywords, the individual included a table named user in their schema, causing all kinds of havoc, and reinforcing the stereotype.
> There is no other language that I write in where I don't use camel-case or underscore
underscore is standard for user-defined words (variables, table, databases). All-caps distinguishes built-ins to avoid confusion with user-defined. IMO with conventions, consistency is more important than the convention itself.
I used to feel that way and did the same thing. I'll still do lower-case keywords on the command line for one-off queries. But for checked in code or anything that anyone else will look at, the all caps convention is the way to go. I've actually started thinking that it looks nice, now that I'm used to it. Especially when you format your crazy long query so that each indented line starts with the capital SQL keyword…
