Thread: Looking for a "show create table " equivalent

Looking for a "show create table " equivalent

From
"B.Rathmann"
Date:
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


Re: Looking for a "show create table " equivalent

From
Guillaume Lelarge
Date:
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



Re: Looking for a "show create table " equivalent

From
Wes James
Date:
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


Re: Looking for a "show create table " equivalent

From
Wes James
Date:
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


Re: Looking for a "show create table " equivalent

From
Jasen Betts
Date:
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



Re: Looking for a "show create table " equivalent

From
Craig Ringer
Date:
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/