Re: Adding SHOW CREATE TABLE

On 5/13/23 02:25, Kirk Wolak wrote:
> On Sat, May 13, 2023 at 1:03 AM Ron <ronljohnsonjr(at)gmail(dot)com> wrote:
>
> On 5/12/23 18:00, Kirk Wolak wrote:
> [snip]
>> Where do we draw the lines?
>
> At other tables.
>
>> Does Table DDL include all indexes?
>
> Absolutely!
>
>> It should include constraints, clearly.  I would not think it should
>> have triggers.
>
> Definitely triggers.  And foreign keys.
>
>> Literally everything within the <<CREATE TABLE X(...);>>.  (ie, no
>> ALTER .. OWNER TO...)
>>
>
> ALTER statements, too.  If CREATE TABLE ... LIKE ... { INCLUDING |
> EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS |
> GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL } can do
> it, then so should SHOW CREATE TABLE.
>
> --
> Born in Arizona, moved to Babylonia.
>
>
> I can see the ALTER statements now.  Which is why I asked.
> I don't like the idea of including the trigger DDL, because that would
> never execute in a clean environment.

I would not be grumpy if trigger statements weren't included.

> (I've never used a tool that tried to do that when I've wanted the DDL)
> I can go either way on index creation.
>
> Does this imply SQL SYNTAX like:
>
> SHOW CREATE TABLE <table_name>
>   [ INCLUDING { ALL | INDEXES |  SEQUENCES | ??? }]
>   [EXCLUDING { PK | FK | COMMENTS | STORAGE | } ]
>   [FOR {V11 | V12 | V13 | V14 | V15 }] ??
> ?

"FOR {V...}" is a complication too far, IMO.  No one expects "pg_dump
--schema-only" to have a --version= option, so one should not expect SHOW
CREATE TABLE to have a "FOR {V...}" clause.

> The goal for me  is to open the discussion, and then CONSTRAIN the focus.
>
> Personally, the simple syntax:
> SHOW CREATE TABLE table1;
>
> Should give me a create table command with the table attributes and the
> column attributes, FKs, PKs, Defaults. Etc.
> But I would not expect it to generate index commands, etc.

--
Born in Arizona, moved to Babylonia.