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

user330315 · Accepted Answer · 2016-11-22 07:47:00Z

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.

virtual void · Accepted Answer · 2017-05-15 19:28:14Z

11

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;

answered May 15, 2017 at 19:28

virtual void

1432 silver badges5 bronze badges

Collectives™ on Stack Overflow

Drop foreign schema in PostgreSQL using a foreign data wrapper

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related