Greetings,
> 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...