Re: Export the CREATE TABLE command in pure SQL

Greetings,

One issue that might be useful to discuss on this list...

Is there really just one API that makes sense here and would be
sufficient? It seems to me that tools like pgAdmin actually need to
pull out the structure of the table itself in individual pieces and
such- a backend command that just returned the full CREATE TABLE
wouldn't actually help that tool much.

I do think that psql users might like such an option, but for most of
them, running pg_dump isn't likely to be an issue, and it would also be
that we could implement this client-side instead of having to have it in
both the client and server..

Thoughts?

Thanks!

Stephen

* Brice Maron (bmaron(at)gmail(dot)com) wrote:
> Hi,
> i've tried a few time to implement this feature and look how other clients
> do but it often (always?) ends up beeing completely buggy or incomplete
> which of course
> defeat the purpose ...
> if we can come up with something as easy as show create table , if would be
> very nice ( can even be usefull for psql users , in combination with other
> \... commands)
>
>
>
> On Tue, Mar 19, 2019 at 4:00 AM Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>
> > Greetings,
> >
> > * Damien Clochard (damien(at)dalibo(dot)info) wrote:
> > > Le 18.03.2019 16:10, Christoph Berg a écrit :
> > > >Re: Damien Clochard 2019-03-17
> > > ><2bc470194b4837c1f733a4e05f569bc6(at)dalibo(dot)info>
> > > >>How do you guys deal with this ? It seems that a lot of GUI tools are
> > > >>able
> > > >>to "export the table structure".... Is there an extension somewhere to
> > > >>do
> > > >>that ? Is it possible to write i in pure SQL ? If yes, there anybody
> > > >>here
> > > >>interested in joining forces to build a shared export function ?
> > > >
> > > >IMHO the cleanest way would be to move all of that into the server.
> > > >There are already functions like pg_getindexdef() which pg_dump can
> > > >(could?) use. But of course that won't fix the problem for existing PG
> > > >versions.
> > >
> > > This was also my first thought. MySQL has a nice "SHOW CREATE TABLE"
> > command
> > > and it seems to me that putting this code on the server would be a
> > benefit
> > > for any GUI tools that tries to export tables structure and probably also
> > > for schema versionning tools.
> > >
> > > https://dev.mysql.com/doc/refman/8.0/en/show-create-table.html
> > >
> > > I looked at the pg_dump code and tried to build a C extension that would
> > > include the dumputils.h header file. But pg_dump is a client-side tool,
> > so
> > > parts of this code would have to be moved to the server-side. I don't
> > have
> > > the time and knowledge to propose a patch like that by myself. But if
> > > someone is interested, I can help...
> >
> > I think the right answer here would be to try and move the code for this
> > into the libpgcommon library but I'm not sure how easy that would be..
> >
> > > >For a medium-term solution, a plpgsql extension (or the client-side
> > > >equivalent) seems like the best way.
> > >
> > > Agreed.
> >
> > I'm a bit concerned that we'd end up with differences between pg_dump
> > and this extension eventually... I guess my thought would be to try and
> > build something into the server for this and use that for future PG
> > versions, and then back-port things into another library for older
> > versions or something.
> >
> > Thanks!
> >
> > Stephen
> >