Re: postgresql FDW vs dblink for DDL

Στις 9/9/24 18:40, ο/η Tom Lane έγραψε:
> Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> writes:
>> On 9/9/24 03:24, Achilleas Mantzios - cloud wrote:
>>> And the thing is that this creation via DDL is inside our design.
>>> Certain users create some backup tables of the public data in their own
>>> schema (via our app), then do some manipulations on the public data,
>>> then restore to the public or merge with the backups. When done, those
>>> backup tables are dropped. So the DDL is inside the app. And the
>>> question was if dblink is my only option, in the sense of doing this in
>>> a somewhat elegant manner. (and not resort to scripts, etc)
>> My sense is yes, if you want to encapsulate all of this within the
>> database/app you will need to use dblink.
> postgres_fdw certainly can't do it, nor any other FDW -- the FDW APIs
> simply don't cover issuance of DDL. If you don't like dblink, you
> could consider writing code within plperlu or plpythonu or another
> "untrusted" PL, making use of whatever Postgres client library exists
> within that PL's ecosystem to connect to the remote server. It's also
> possible that there's some third-party extension that overlaps
> dblink's functionality. dblink sure seems like the path of least
> resistance, though.
Thank you Tom and Adrian.
>
> regards, tom lane

--
Achilleas Mantzios
IT DEV - HEAD
IT DEPT
Dynacom Tankers Mgmt (as agents only)