On Sat, May 20, 2023 at 2:33 PM Stephen Frost <sfr...@snowman.net> wrote:
> Greetings, > > On Sat, May 20, 2023 at 13:32 David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Sat, May 20, 2023 at 10:26 AM Stephen Frost <sfr...@snowman.net> >> wrote: >> >>> > A server function can be conveniently called from any client code. >>> >>> Clearly any client using libpq can conveniently call code which is in >>> libpq. >>> >> >> Clearly there are clients that don't use libpq. JDBC comes to mind. >> > > Indeed … as I mentioned up-thread already. > > Are we saying that we want this to be available server side, and largely > duplicated, specifically to cater to non-libpq users? I’ll put out there, > again, the idea that perhaps we put it into the common library then and > make it available via both libpq and as a server side function ..? > > We also have similar code in postgres_fdw.. ideally, imv anyway, we’d not > end up with three copies of it. > > Thanks, > > Stephen > First, as the person chasing this down, and a JDBC user, I really would prefer pg_get_tabledef() as Laurenz mentioned. Next, I have reviewed all 3 implementations (pg_dump [most appropriate], psql \d (very similar), and the FDW which is "way off", since it actually focuses on "CREATE FOREIGN TABLE" exclusively, and already fails to handle many pieces not required in creating a "real" table, as it creates a "reflection" of table. I am using pg_dump as my source of truth. But I noticed it does not create "TEMPORARY" tables with that syntax. [Leading to a question on mutating the pg_temp_# schema name back to pg_temp. or just stripping it, in favor of the TEMPORARY] I was surprised to see ~ 2,000 lines of code in the FDW and in psql... Whereas pg_dump is shorter because it gets more detailed table information in a structure passed in. I would love to leverage existing code, in the end. But I want to take my time on this, and become intimate with the details. Each of the above 3 approaches have different goals. And I would prefer the lowest risk:reward possible, and the least expensive maintenance. Having it run server side hides a ton of details, and as Tom pointed out, obviates DDL versioning control for other server versions. Thanks for the references to the old discussions. I have queued them up to review. Kirk...
