Обсуждение: A few questions about foreign tables

Hello,

I want to set up a few foreign tables (to access tables from one
PostgreSQL database in another one).  However, I want to do it
"properly", using a database migration tool, so I need to have a "down"
migration to revert to the previous state.

How to revert the effect of "import foreign schema"?  Is just dropping
the imported tables enough?

Also, how can I check the definitions (i.e., what tables on the server
they correspond to) of existing foreign tables?

(May I ask to be cc'd since I'm not subscribed to the list?)

TIA,

-- 
Marcin Borkowski
http://mbork.pl

On Wed, 2023-10-18 at 10:24 +0200, Marcin Borkowski wrote:
> How to revert the effect of "import foreign schema"?  Is just dropping
> the imported tables enough?

Running DROP FOREIGN TABLE ... for all imported tables is enough.

> Also, how can I check the definitions (i.e., what tables on the server
> they correspond to) of existing foreign tables?

\d+ <table name>

> (May I ask to be cc'd since I'm not subscribed to the list?)

But please reply to the list.

Yours,
Laurenz Albe

And SELECT relnamespace::regnamespace::text, relname FROM pg_class WHERE 
relkind='f'; tells you all of the foreign tables.

Thus, this (untested) query generate all of the DROP FOREIGN TABLE statements:
SELECT format('DROP FOREIGN TABLE IF EXISTS %I.%I RESTRICT;'
               , relnamespace::regnamespace::text, relname)
FROM pg_class WHERE relkind='f'
ORDER BY relkind;

On 10/18/23 10:56, Laurenz Albe wrote:
> On Wed, 2023-10-18 at 10:24 +0200, Marcin Borkowski wrote:
>> How to revert the effect of "import foreign schema"?  Is just dropping
>> the imported tables enough?
> Running DROP FOREIGN TABLE ... for all imported tables is enough.
>
>> Also, how can I check the definitions (i.e., what tables on the server
>> they correspond to) of existing foreign tables?
> \d+ <table name>
>
>> (May I ask to be cc'd since I'm not subscribed to the list?)
> But please reply to the list.
>
> Yours,
> Laurenz Albe
>
>

-- 
Born in Arizona, moved to Babylonia.

On 2023-10-18, at 17:56, Laurenz Albe <laurenz.albe@cybertec.at> wrote:

> On Wed, 2023-10-18 at 10:24 +0200, Marcin Borkowski wrote:
>> How to revert the effect of "import foreign schema"? Is just dropping
>> the imported tables enough?
>
> Running DROP FOREIGN TABLE ... for all imported tables is enough.
>
>> Also, how can I check the definitions (i.e., what tables on the server
>> they correspond to) of existing foreign tables?
>
> \d+ <table name>

Thanks.

>> (May I ask to be cc'd since I'm not subscribed to the list?)
>
> But please reply to the list.

Of course.

-- 
Marcin Borkowski
http://mbork.pl

On 2023-10-18, at 18:14, Ron <ronljohnsonjr@gmail.com> wrote:

> And SELECT relnamespace::regnamespace::text, relname FROM pg_class
> WHERE relkind='f'; tells you all of the foreign tables.
>
> Thus, this (untested) query generate all of the DROP FOREIGN TABLE statements:
> SELECT format('DROP FOREIGN TABLE IF EXISTS %I.%I RESTRICT;'
>  , relnamespace::regnamespace::text, relname)
> FROM pg_class WHERE relkind='f'
> ORDER BY relkind;

Thanks, though happily I only have about a dozen of them, so I could
create these by hand (well, actually by some automation features of my
text editor).

Best,

-- 
Marcin Borkowski
http://mbork.pl