public inbox for [email protected]help / color / mirror / Atom feed
Export the CREATE TABLE command in pure SQL 8+ messages / 6 participants [nested] [flat]
* Export the CREATE TABLE command in pure SQL @ 2019-03-17 08:49 Damien Clochard <[email protected]> 0 siblings, 2 replies; 8+ messages in thread From: Damien Clochard @ 2019-03-17 08:49 UTC (permalink / raw) To: [email protected] 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 ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Export the CREATE TABLE command in pure SQL @ 2019-03-18 15:10 Christoph Berg <[email protected]> parent: Damien Clochard <[email protected]> 1 sibling, 1 reply; 8+ messages in thread From: Christoph Berg @ 2019-03-18 15:10 UTC (permalink / raw) To: [email protected] Re: Damien Clochard 2019-03-17 <[email protected]> > 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 ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Export the CREATE TABLE command in pure SQL @ 2019-03-18 17:37 Damien Clochard <[email protected]> parent: Christoph Berg <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Damien Clochard @ 2019-03-18 17:37 UTC (permalink / raw) To: Christoph Berg <[email protected]>; +Cc: [email protected] Le 18.03.2019 16:10, Christoph Berg a écrit : > Re: Damien Clochard 2019-03-17 > <[email protected]> >> 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. > 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... > For a medium-term solution, a plpgsql extension (or the client-side > equivalent) seems like the best way. > Agreed. Thanks for your answer -- Damien Clochard ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Export the CREATE TABLE command in pure SQL @ 2019-03-19 03:00 Stephen Frost <[email protected]> parent: Damien Clochard <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Stephen Frost @ 2019-03-19 03:00 UTC (permalink / raw) To: Damien Clochard <[email protected]>; +Cc: Christoph Berg <[email protected]>; [email protected] Greetings, * Damien Clochard ([email protected]) wrote: > Le 18.03.2019 16:10, Christoph Berg a écrit : > >Re: Damien Clochard 2019-03-17 > ><[email protected]> > >>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 Attachments: [application/pgp-signature] signature.asc (819B, ../../[email protected]/2-signature.asc) download ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Export the CREATE TABLE command in pure SQL @ 2019-03-19 07:35 Brice Maron <[email protected]> parent: Stephen Frost <[email protected]> 0 siblings, 2 replies; 8+ messages in thread From: Brice Maron @ 2019-03-19 07:35 UTC (permalink / raw) To: Stephen Frost <[email protected]>; +Cc: Damien Clochard <[email protected]>; Christoph Berg <[email protected]>; [email protected] 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 protected]> wrote: > Greetings, > > * Damien Clochard ([email protected]) wrote: > > Le 18.03.2019 16:10, Christoph Berg a écrit : > > >Re: Damien Clochard 2019-03-17 > > ><[email protected]> > > >>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 > ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Export the CREATE TABLE command in pure SQL @ 2019-03-19 08:48 Stephen Frost <[email protected]> parent: Brice Maron <[email protected]> 1 sibling, 0 replies; 8+ messages in thread From: Stephen Frost @ 2019-03-19 08:48 UTC (permalink / raw) To: Brice Maron <[email protected]>; +Cc: Damien Clochard <[email protected]>; Christoph Berg <[email protected]>; [email protected] 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 ([email protected]) 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 protected]> wrote: > > > Greetings, > > > > * Damien Clochard ([email protected]) wrote: > > > Le 18.03.2019 16:10, Christoph Berg a écrit : > > > >Re: Damien Clochard 2019-03-17 > > > ><[email protected]> > > > >>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 > > Attachments: [application/pgp-signature] signature.asc (819B, ../../[email protected]/2-signature.asc) download ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Export the CREATE TABLE command in pure SQL @ 2019-03-19 08:51 Maxim Sobolevskiy <[email protected]> parent: Damien Clochard <[email protected]> 1 sibling, 0 replies; 8+ messages in thread From: Maxim Sobolevskiy @ 2019-03-19 08:51 UTC (permalink / raw) To: Damien Clochard <[email protected]>; +Cc: [email protected] 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. On Sun, Mar 17, 2019 at 11:49 AM Damien Clochard <[email protected]> wrote: > 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 > > -- Max Sobolevskiy Product Marketing Manager of DataGrip JetBrains ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Export the CREATE TABLE command in pure SQL @ 2019-03-20 17:58 Žiga Kranjec <[email protected]> parent: Brice Maron <[email protected]> 1 sibling, 0 replies; 8+ messages in thread From: Žiga Kranjec @ 2019-03-20 17:58 UTC (permalink / raw) To: Brice Maron <[email protected]>; +Cc: Stephen Frost <[email protected]>; Damien Clochard <[email protected]>; Christoph Berg <[email protected]>; [email protected] 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 protected]> 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 protected] <mailto:[email protected]>> wrote: > Greetings, > > * Damien Clochard ([email protected] <mailto:[email protected]>) wrote: > > Le 18.03.2019 16:10, Christoph Berg a écrit : > > >Re: Damien Clochard 2019-03-17 > > ><[email protected] <mailto:[email protected]>> > > >>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 <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 ^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2019-03-20 17:58 UTC | newest] Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2019-03-17 08:49 Export the CREATE TABLE command in pure SQL Damien Clochard <[email protected]> 2019-03-18 15:10 ` Christoph Berg <[email protected]> 2019-03-18 17:37 ` Damien Clochard <[email protected]> 2019-03-19 03:00 ` Stephen Frost <[email protected]> 2019-03-19 07:35 ` Brice Maron <[email protected]> 2019-03-19 08:48 ` Stephen Frost <[email protected]> 2019-03-20 17:58 ` Žiga Kranjec <[email protected]> 2019-03-19 08:51 ` Maxim Sobolevskiy <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox