Re: Adding SHOW CREATE TABLE

On Fri, May 12, 2023 at 8:37 PM Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> Greetings,
> ..
> I mean ... it's already in postgres_fdw, just not in a way that can be
> returned to the user. I don't think I'd want this functionality to
> depend on postgres_fdw or generally on an extension though, it should
> be part of core in some fashion.
>

I will start with postgres_fdw then, but gladly review the other source...
Just thinking about the essence of the syntax.
SHOW CREATE TABLE abc(LIKE real_table); -- Output CREATE TABLE abc();
using real_table?

I'd look at the IMPORT FOREIGN SCHEMA stuff in postgres_fdw. We're
> already largely answering these questions by what options that takes.
>

Will do.

> > But it's a big commitment. I don't mind if it has a reasonable chance of
> > being accepted.
>

> Yes, it's a large effort, no doubt.
>

At least there is a base of code to start with.
I see a strong need to come up with a shell script to that could:

FOR <every schema.table> DO
psql -c "SHOW... \g | cat > <schema.table.DDL> "
pg_dump -- <schema.table> only | remove_comments_normalize | cat
<schema.table.pg_dump>
DIFF <schema.table.DDL> <schema.table.pg_dump>

Of course, since our tests are usually all perl, a perl version.
But I would clearly want some heavy testing/validation.