pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedLooking for a "show create table <name>" equivalent
6+ messages / 5 participants
[nested] [flat]
* Looking for a "show create table <name>" equivalent
@ 2011-07-12 08:33 B.Rathmann <B.Rathmann@ping.de>
2011-07-13 22:16 ` Re: Looking for a "show create table <name>" equivalent Guillaume Lelarge <guillaume@lelarge.info>
2011-07-13 22:38 ` Re: Looking for a "show create table <name>" equivalent Wes James <comptekki@gmail.com>
2011-07-13 22:45 ` Re: Looking for a "show create table <name>" equivalent Wes James <comptekki@gmail.com>
2011-07-14 10:22 ` Re: Looking for a "show create table <name>" equivalent Jasen Betts <jasen@xnet.co.nz>
0 siblings, 4 replies; 6+ messages in thread
From: B.Rathmann @ 2011-07-12 08:33 UTC (permalink / raw)
To: pgsql-sql
Hello,
I've been trying to find out how to find out which sql was run to create
a certain table. As I need this in a program which may access the
database remotely, using pg_dump --schema-only or psql is not an option
(the system my program runs on may not even have those tools installed).
Looking at the source of pgadmin3 it seems as if the program collects
all info about the given table (columns, indeces etc) and creates the
needed SQL by itself, is there no easier way?
E.g. MySQL offers a simple "show create table <name>". I am using
Postgresql 9.0.4 on Gentoo.
Thanks for your help (if there is any documentation which I haven't
found yet, just point me to it).
Sincerely
Bettina
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Looking for a "show create table <name>" equivalent
2011-07-12 08:33 Looking for a "show create table <name>" equivalent B.Rathmann <B.Rathmann@ping.de>
@ 2011-07-13 22:16 ` Guillaume Lelarge <guillaume@lelarge.info>
3 siblings, 0 replies; 6+ messages in thread
From: Guillaume Lelarge @ 2011-07-13 22:16 UTC (permalink / raw)
To: B.Rathmann <B.Rathmann@ping.de>; +Cc: pgsql-sql
On Tue, 2011-07-12 at 10:33 +0200, B.Rathmann wrote:
> [...]
> I've been trying to find out how to find out which sql was run to create
> a certain table. As I need this in a program which may access the
> database remotely, using pg_dump --schema-only or psql is not an option
> (the system my program runs on may not even have those tools installed).
> Looking at the source of pgadmin3 it seems as if the program collects
> all info about the given table (columns, indeces etc) and creates the
> needed SQL by itself, is there no easier way?
>
No. pgAdmin does it this way because it has no other way to do it.
--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Looking for a "show create table <name>" equivalent
2011-07-12 08:33 Looking for a "show create table <name>" equivalent B.Rathmann <B.Rathmann@ping.de>
@ 2011-07-13 22:38 ` Wes James <comptekki@gmail.com>
3 siblings, 0 replies; 6+ messages in thread
From: Wes James @ 2011-07-13 22:38 UTC (permalink / raw)
To: pgsql-sql
On Tue, Jul 12, 2011 at 2:33 AM, B.Rathmann <B.Rathmann@ping.de> wrote:
> Hello,
>
> I've been trying to find out how to find out which sql was run to create
> a certain table. As I need this in a program which may access the
> database remotely, using pg_dump --schema-only or psql is not an option
Maybe turning on some more verbose logging you can see what command is
doing that. I've poked around in the pg docs, but don't see it right
off.
-wes
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Looking for a "show create table <name>" equivalent
2011-07-12 08:33 Looking for a "show create table <name>" equivalent B.Rathmann <B.Rathmann@ping.de>
@ 2011-07-13 22:45 ` Wes James <comptekki@gmail.com>
3 siblings, 0 replies; 6+ messages in thread
From: Wes James @ 2011-07-13 22:45 UTC (permalink / raw)
To: pgsql-sql
On Tue, Jul 12, 2011 at 2:33 AM, B.Rathmann <B.Rathmann@ping.de> wrote:
> Hello,
>
> I've been trying to find out how to find out which sql was run to create
> a certain table. As I need this in a program which may access the
> database remotely, using pg_dump --schema-only or psql is not an option
> (the system my program runs on may not even have those tools installed).
Look at this for logging:
http://www.depesz.com/index.php/2011/05/06/understanding-postgresql-conf-log/
-wes
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Looking for a "show create table <name>" equivalent
2011-07-12 08:33 Looking for a "show create table <name>" equivalent B.Rathmann <B.Rathmann@ping.de>
@ 2011-07-14 10:22 ` Jasen Betts <jasen@xnet.co.nz>
2011-07-14 23:40 ` Re: Looking for a "show create table <name>" equivalent Craig Ringer <craig@postnewspapers.com.au>
3 siblings, 1 reply; 6+ messages in thread
From: Jasen Betts @ 2011-07-14 10:22 UTC (permalink / raw)
To: pgsql-sql
On 2011-07-12, B.Rathmann <B.Rathmann@ping.de> wrote:
> Hello,
>
> I've been trying to find out how to find out which sql was run to create
> a certain table.
That's like trying to find what change was used to create $1.83
there several possible answers all but one of them wrong, but many of
them may .
> As I need this in a program which may access the database remotely,
> using pg_dump --schema-only or psql is not an option
> (the system my program runs on may not even have those tools installed).
why do you think you need this information?
> Looking at the source of pgadmin3 it seems as if the program collects
> all info about the given table (columns, indeces etc) and creates the
> needed SQL by itself, is there no easier way?
>
> E.g. MySQL offers a simple "show create table <name>". I am using
> Postgresql 9.0.4 on Gentoo.
can you find what you need to know in the information schema?
http://www.postgresql.org/docs/8.4/static/information-schema.html
It's an industry standard, and thus should work with every SQL database.
--
⚂⚃ 100% natural
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Looking for a "show create table <name>" equivalent
2011-07-12 08:33 Looking for a "show create table <name>" equivalent B.Rathmann <B.Rathmann@ping.de>
2011-07-14 10:22 ` Re: Looking for a "show create table <name>" equivalent Jasen Betts <jasen@xnet.co.nz>
@ 2011-07-14 23:40 ` Craig Ringer <craig@postnewspapers.com.au>
0 siblings, 0 replies; 6+ messages in thread
From: Craig Ringer @ 2011-07-14 23:40 UTC (permalink / raw)
To: Jasen Betts <jasen@xnet.co.nz>; +Cc: pgsql-sql
On 14/07/2011 6:22 PM, Jasen Betts wrote:
> On 2011-07-12, B.Rathmann<B.Rathmann@ping.de> wrote:
>> Hello,
>>
>> I've been trying to find out how to find out which sql was run to create
>> a certain table.
>
> That's like trying to find what change was used to create $1.83
Yep, and just like that question, sometimes any valid answer is fine.
You might not care if it's $1 + .5 + .2 + .02 + .01 or 183 * 0.01, so
long as the result is the same.
This is turning out to be a reasonably common question, where people
want to be able to use functionality of pg_dump from _within_ the
database system.
If the INFORMATION_SCHEMA isn't sufficient for the OP's purposes or is
too clumsy for re-creating DDL from, there's another option. It's ugly,
but you can add a plpythonu or plperlu server-side function that calls
the server's copy of pg_dump and spits the resulting text back to the
client.
--
Craig Ringer
POST Newspapers
276 Onslow Rd, Shenton Park
Ph: 08 9381 3088 Fax: 08 9388 2258
ABN: 50 008 917 717
http://www.postnewspapers.com.au/
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2011-07-14 23:40 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-07-12 08:33 Looking for a "show create table <name>" equivalent B.Rathmann <B.Rathmann@ping.de>
2011-07-13 22:16 ` Guillaume Lelarge <guillaume@lelarge.info>
2011-07-13 22:38 ` Wes James <comptekki@gmail.com>
2011-07-13 22:45 ` Wes James <comptekki@gmail.com>
2011-07-14 10:22 ` Jasen Betts <jasen@xnet.co.nz>
2011-07-14 23:40 ` Craig Ringer <craig@postnewspapers.com.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox