Re: Export the CREATE TABLE command in pure SQL

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