Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h6fTx-0001rJ-Jk for pgsql-gui-dev@arkaria.postgresql.org; Wed, 20 Mar 2019 17:58:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1h6fTw-0005Ul-16 for pgsql-gui-dev@arkaria.postgresql.org; Wed, 20 Mar 2019 17:58:28 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h6fTv-0005Td-R9 for pgsql-gui-dev@lists.postgresql.org; Wed, 20 Mar 2019 17:58:27 +0000 Received: from mail.ljudmila.org ([193.2.132.70]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h6fTs-0002P8-Fg for pgsql-gui-dev@lists.postgresql.org; Wed, 20 Mar 2019 17:58:27 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.ljudmila.org (Postfix) with ESMTP id 18B2523DF78; Wed, 20 Mar 2019 18:58:22 +0100 (CET) X-Virus-Scanned: Debian amavisd-new at mail.ljudmila.org Received: from mail.ljudmila.org ([127.0.0.1]) by localhost (mila.ljudmila.org [127.0.0.1]) (amavisd-new, port 10024) with SMTP id 7z5sFHKAQymp; Wed, 20 Mar 2019 18:58:19 +0100 (CET) Received: from [149.62.96.214] (unknown [149.62.96.214]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by mail.ljudmila.org (Postfix) with ESMTPSA id 40E1323DF5C; Wed, 20 Mar 2019 18:58:19 +0100 (CET) From: =?utf-8?Q?=C5=BDiga_Kranjec?= Message-Id: <918CC997-08F4-4E65-90AF-2CBCFC7F3550@ljudmila.org> Content-Type: multipart/alternative; boundary="Apple-Mail=_D4550AEF-C3E1-46D1-89E7-56EAA859AB57" Mime-Version: 1.0 (Mac OS X Mail 11.5 \(3445.9.1\)) Subject: Re: Export the CREATE TABLE command in pure SQL Date: Wed, 20 Mar 2019 18:58:13 +0100 In-Reply-To: Cc: Stephen Frost , Damien Clochard , Christoph Berg , pgsql-gui-dev@lists.postgresql.org To: Brice Maron References: <2bc470194b4837c1f733a4e05f569bc6@dalibo.info> <20190318151017.GE3087@msg.df7cb.de> <303d6ac0fd3feef40b41da944909ef12@dalibo.info> <20190319030038.GN6197@tamriel.snowman.net> X-Mailer: Apple Mail (2.3445.9.1) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_D4550AEF-C3E1-46D1-89E7-56EAA859AB57 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 I am working on an extension for this. See here: https://github.com/lacanoid/pgddl = Thanks =C5=BDiga > On 19 Mar 2019, at 08:35, Brice Maron wrote: >=20 > Hi,=20 > 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=20 > defeat the purpose ...=20 > 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) >=20 >=20 >=20 > On Tue, Mar 19, 2019 at 4:00 AM Stephen Frost > wrote: > Greetings, >=20 > * Damien Clochard (damien@dalibo.info ) = wrote: > > Le 18.03.2019 16:10, Christoph Berg a =C3=A9crit : > > >Re: Damien Clochard 2019-03-17 > > ><2bc470194b4837c1f733a4e05f569bc6@dalibo.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. > >=20 > > 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. > >=20 > > https://dev.mysql.com/doc/refman/8.0/en/show-create-table.html = > >=20 > > 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... >=20 > 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.. >=20 > > >For a medium-term solution, a plpgsql extension (or the client-side > > >equivalent) seems like the best way. > >=20 > > Agreed. >=20 > 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. >=20 > Thanks! >=20 > Stephen --Apple-Mail=_D4550AEF-C3E1-46D1-89E7-56EAA859AB57 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 I = am working on an extension for this.
See here: https://github.com/lacanoid/pgddl

Thanks
=C5=BDiga

On 19 Mar 2019, at 08:35, Brice Maron <bmaron@gmail.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@snowman.net> wrote:
Greetings,

* Damien Clochard (damien@dalibo.info) wrote:
> Le 18.03.2019 16:10, Christoph Berg a =C3=A9crit :
> >Re: Damien Clochard 2019-03-17
> ><2bc470194b4837c1f733a4e05f569bc6@dalibo.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<= /a>
>
> 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

= --Apple-Mail=_D4550AEF-C3E1-46D1-89E7-56EAA859AB57--