pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
A few questions about foreign tables
5+ messages / 3 participants
[nested] [flat]

* A few questions about foreign tables
@ 2023-10-18 08:24  Marcin Borkowski <mbork@mbork.pl>
  0 siblings, 1 reply; 5+ messages in thread

From: Marcin Borkowski @ 2023-10-18 08:24 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: A few questions about foreign tables
@ 2023-10-18 15:56  Laurenz Albe <laurenz.albe@cybertec.at>
  parent: Marcin Borkowski <mbork@mbork.pl>
  0 siblings, 2 replies; 5+ messages in thread

From: Laurenz Albe @ 2023-10-18 15:56 UTC (permalink / raw)
  To: Marcin Borkowski <mbork@mbork.pl>; pgsql-general@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: A few questions about foreign tables
@ 2023-10-18 16:14  Ron <ronljohnsonjr@gmail.com>
  parent: Laurenz Albe <laurenz.albe@cybertec.at>
  1 sibling, 1 reply; 5+ messages in thread

From: Ron @ 2023-10-18 16:14 UTC (permalink / raw)
  To: Marcin Borkowski <mbork@mbork.pl>; pgsql-general@lists.postgresql.org

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.





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: A few questions about foreign tables
@ 2023-10-20 08:26  Marcin Borkowski <mbork@mbork.pl>
  parent: Laurenz Albe <laurenz.albe@cybertec.at>
  1 sibling, 0 replies; 5+ messages in thread

From: Marcin Borkowski @ 2023-10-20 08:26 UTC (permalink / raw)
  To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-general@lists.postgresql.org


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





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: A few questions about foreign tables
@ 2023-10-20 08:27  Marcin Borkowski <mbork@mbork.pl>
  parent: Ron <ronljohnsonjr@gmail.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Marcin Borkowski @ 2023-10-20 08:27 UTC (permalink / raw)
  To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-general@lists.postgresql.org


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





^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2023-10-20 08:27 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-18 08:24 A few questions about foreign tables Marcin Borkowski <mbork@mbork.pl>
2023-10-18 15:56 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-10-18 16:14   ` Ron <ronljohnsonjr@gmail.com>
2023-10-20 08:27     ` Marcin Borkowski <mbork@mbork.pl>
2023-10-20 08:26   ` Marcin Borkowski <mbork@mbork.pl>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox