Subject: Export the CREATE TABLE command in pure SQL

From: Damien Clochard <[email-masked]>
Date: Sun, 17 Mar 2019 09:49:50 +0100

hey guys,

The most effective and simplest way to export the creation command of a 
table is to use `pg_dump --schema-only`

However for one my projects, I don't have access to pg_dump and I need 
to extract the data with a SQL or pl/pgsql function. I already wrote a 
naïve implementation that handles primary keys and temporary tables. But 
I realize it's probably incomplete and I will have to maintain it for 
every new PostgreSQL major version....

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 ?

Have nice day !

-- 
Damien Clochard

---

From: Christoph Berg <[email-masked]>
Date: Mon, 18 Mar 2019 16:10:17 +0100

Re: Damien Clochard 2019-03-17 <[email-masked]>
> 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.

For a medium-term solution, a plpgsql extension (or the client-side
equivalent) seems like the best way.

Christoph

---

From: Damien Clochard <[email-masked]>
Date: Mon, 18 Mar 2019 18:37:34 +0100

Le 18.03.2019 16:10, Christoph Berg a écrit :
> Re: Damien Clochard 2019-03-17 
> <[email-masked]>
>> able

Hi Christoph !

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...

> equivalent) seems like the best way.

Agreed.

Thanks for your answer

-- 
Damien Clochard

---

From: Stephen Frost <[email-masked]>
Date: Mon, 18 Mar 2019 23:00:38 -0400

Greetings,

* Damien Clochard ([email-masked]) wrote:
> 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.
> 
> 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..

> >equivalent) seems like the best way.

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

---

From: Brice Maron <[email-masked]>
Date: Tue, 19 Mar 2019 08:35:24 +0100

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 <[email-masked]> wrote:

> command
> so

---

From: Stephen Frost <[email-masked]>
Date: Tue, 19 Mar 2019 04:48:34 -0400

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

---

From: Maxim Sobolevskiy <[email-masked]>
Date: Tue, 19 Mar 2019 11:51:04 +0300

In DataGrip, which is java-application, we first get all attributes of the
object from the database, then Java-code generates the whole DDL based on
the attributes we have.

-- 
Max Sobolevskiy
Product Marketing Manager of DataGrip
JetBrains

---

From: Žiga Kranjec <[email-masked]>
Date: Wed, 20 Mar 2019 18:58:13 +0100

I am working on an extension for this.
See here: https://github.com/lacanoid/pgddl <https://github.com/lacanoid/pgddl>

Thanks
Žiga

> On 19 Mar 2019, at 08:35, Brice Maron <[email-masked]> 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 <[email-masked] <mailto:[email-masked]>> wrote:
> Greetings,
> 
> * Damien Clochard ([email-masked] <mailto:[email-masked]>) wrote:
> > ><[email-masked] <mailto:[email-masked]>>
> > https://dev.mysql.com/doc/refman/8.0/en/show-create-table.html <https://dev.mysql.com/doc/refman/8.0/en/show-create-table.html>
> >