Re: Adding SHOW CREATE TABLE

On Sat, May 20, 2023 at 2:33 PM Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> Greetings,
>
> On Sat, May 20, 2023 at 13:32 David G. Johnston <
> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
>> On Sat, May 20, 2023 at 10:26 AM Stephen Frost <sfrost(at)snowman(dot)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...