Drop foreign schema in PostgreSQL using a foreign data wrapper

Question

Using the MySQL foreign data wrapper in PostgreSQL there is a nice trick that enables you to quickly tell Postgres about all the tables in a MySQL database. It is as follows:

IMPORT FOREIGN SCHEMA my_favourite_database FROM SERVER mysql_server INTO public;

There is no analog DROP FOREIGN SCHEMA. Although DROP FOREIGN TABLE could be done piecemeal for each table, this also requires knowing the names of all tables in the foreign database. I am also uncertain if this command actually DROPs the table in the foreign database or just the connection to it.

How would one approach this automatically.

I imagine the same approach would apply to any foreign data wrapper in PostgreSQL

Answer 1

drop foreign table only drops the "local" foreign table definitions, not the table in the remote sever.

You can drop all foreign tables with a short PL/pgSQL block:

do
$$
declare
  l_rec record;
begin
  for l_rec in (select foreign_table_schema, foreign_table_name 
                from information_schema.foreign_tables) loop
     execute format('drop foreign table %I.%I', l_rec.foreign_table_schema, l_rec.foreign_table_name);
  end loop;
end;
$$

This will drop all foreign tables in the current database. If you want to limit that to certain tables only, you can adjust the query in the for loop accordingly, e.g. by selecting only those for a specific foreign server where foreign_server_name = 'mysql'

I would also put all those foreign tables into a separate schema to separate them from the the "regular" tables - but that is a matter of personal taste.

Answer 2

Instead of iterating through all tables you can drop the whole schema and import again:

DROP SCHEMA IF EXISTS ramu cascade;
CREATE SCHEMA ramu;
IMPORT FOREIGN SCHEMA ramu FROM SERVER ramsingh INTO ramu;