From: | Kirk Wolak <wolakk(at)gmail(dot)com> |
---|---|
To: | Jelte Fennema-Nio <postgres(at)jeltef(dot)nl> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Álvaro Herrera <alvherre(at)kurilemu(dot)de>, Kirill Reshke <reshkekirill(at)gmail(dot)com>, Andrew Dunstan <andrew(at)dunslane(dot)net>, Ziga <ziga(at)ljudmila(dot)org>, pgsql-hackers(at)lists(dot)postgresql(dot)org |
Subject: | Re: Retail DDL |
Date: | 2025-08-20 19:09:28 |
Message-ID: | CACLU5mS=yU0ujYTyVEyfy1Qw6fiTksmFqLahEm_bmqf3ZarmOQ@mail.gmail.com |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Mon, Aug 18, 2025 at 10:24 AM Jelte Fennema-Nio <postgres(at)jeltef(dot)nl>
wrote:
> On Mon, 18 Aug 2025 at 15:57, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > You haven't actually defined what "this" is. For starters, do you
> > really want this output to be included in \d? Seems like one part
> > or the other of such output would be clutter, so I'd be more minded
> > to leave \d alone and invent some new command. (By analogy to \sf,
> > maybe \st and so on?)
>
> That makes sense, I don't think I had seen the \s ones before. IMO it
> would be super useful to have a \s for every type of object that
> currently has a \d (but those don't have to be all added in the same
> patchset ofcours, starting with tables seems totally sensible)
>
> > But the real issue is what to print.
>
> I think you're making this sound much harder than it actually is. I
> think it would be perfect if it had exactly the same info as \d but in
> SQL form instead of some "easy to understand by humans form". So:
>
> > should we also show its indexes?
>
> Yes
>
> > What about foreign keys to or from other
> > tables?
>
> Yes, both from and to
>
> > If it's a partitioned table, what about the partitions?
>
> Definitely not by default, way too much clutter.
>
> I think having a DESCRIBE keyword is probably not what we want, but
> adding a pg_get_tabledef function seems totally reasonable. I even
> proposed that at some point[1], but apparently never followed up with
> Kirk (cc-ed now). It could even have options for all the questions
> that you're asking like, so we'd "just" need to decide on the
> defaults:
>
> SELECT pg_get_tabledef('my_table', include_indexes => true,
> include_partitions => true)
>
> [1]:
> https://www.postgresql.org/message-id/CAGECzQRuHBs9gjPbvgabQv8XS3QRU9Ex=nH84S_1=wo4POzBzg@mail.gmail.com
Jelte, you helped me start on something with this. Your code was
effectively based on "opening" the reference and loading the type
structures.
TBH, I got bogged down on these kinds of issues. First if we do it as
"\st" type commands, it is limited to psql.
If we do it elsewhere, it becomes server side code, and incredibly version
dependent.
And I could not quite get the buy-in (as in this discussion) as to where
this belongs, and how to codify the limitations.
FWIW, I wrote a BASH script that generates the file using pg_dump as my
workaround.
I was a bit resigned to thinking this is probably best as a SQL based
extension that generates the code.
The stickiness was. Can we justify creating and supporting this code in a
pg_gettabledef() [Where I believe it belongs]
of none of the clients are using this code. Of course, getting psql to use
this code would be trivial for a "\s" once it exists.
but it started feeling like a pretty heavy "sell" to the community.
As I've grown with the community, I understand why it has been missing for
so long. What we want from it as a casual
user wanting to quickly see/copy a complete create table command... Is
almost orthogonal to what the clients (psql, pg_dump)
are looking for, and why they built them into the client.
I think it needs more "buy-in" and a better "definition" of what it is.
Even if we end up with:
pg_get_tabledef() -- User Friendly
pg_get_tabledef_ex() -- Proper for Partitions, Foreign Data Tables, etc.
Thoughts?
Kirk
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2025-08-20 19:13:51 | Re: RFC: extensible planner state |
Previous Message | Kirk Wolak | 2025-08-20 18:52:19 | Re: Cygwin support |