pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedA 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