CODEWITHSUNDEEP
postgresql
eponkratova
eponkratova

Reputation: 477

Pre-fix the foreign table with the schema - postgres_pwd

If I follow: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.html#postgresql-commondbatasks-fdw, how can I pre-fix the tables with the schema I am retrieving tables from, e.g. 

IMPORT FOREIGN SCHEMA lands 
LIMIT TO (land, land2) 
FROM SERVER foreign_server INTO public;

The created tables are named land and land2. Is it possible to prefix land and land2 with 'lands', e.g. 'lands_land' and 'lands_land2'?

Upvotes: 1

Views: 990

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247235

With psql and recent PostgreSQL versions, you could simply run (after the IMPORT FOREIGN SCHEMA):

SELECT format(
          'ALTER FOREIGN TABLE public.%I RENAME TO %I;',
          relname,
          'lands_' || relname
       )
FROM pg_class
WHERE relkind = 'f'  -- foreign table
  AND relnamespace = 'public'::regnamespace \gexec

The \gexec will interpret each result row as an SQL stateent and execute it.

Another option that I'd like better is to keep the original names, but use a different schema for the foreign tables:

IMPORT FOREIGN SCHEMA lands 
LIMIT TO (land, land2) 
FROM SERVER foreign_server INTO lands;

Then all foreign tables will be in a schema lands, and you have the same effect in a more natural fashion. You can adjust search_path to include the lands schema.

Upvotes: 3

Related Questions