Hi,
How can I generate the DDL of a table programmatically on Postgresql? Is there a system query or command to do it? Googling the issue returned no pointers.
+2
A:
You can use the pg_dump command to dump the contents of the database (both schema and data). The --schema-only switch will dump only the DDL for your table(s).
Greg Hewgill
2009-12-10 23:30:46
Sorry, need to do it programmatically. Most people wouldn't have pg_dump installed or access to a server with pg_dump.
Rui Pacheco
2009-12-11 12:09:46
I don't quite understand your comment. `pg_dump` is installed with PostgreSQL and is available on all servers. No special user privileges are required to use it (normal PostgreSQL access controls apply to the database you're dumping). If you don't consider this to be a programmatic solution, you will have to specify what programming environment you *are* using in order to get an appropriate answer.
Greg Hewgill
2009-12-11 20:47:33
+1
A:
The answer is to check the source code for pg_dump and follow the switches it uses to generate the DDL. Somewhere inside the code there's a number of queries used to retrieve the metadata used to generate the DDL.
Rui Pacheco
2010-01-27 11:23:31
+1
A:
Why would shelling out to psql not count as "programmatically?" It'll dump the entire schema very nicely.
Anyhow, you can get data types (and much more) from the information_schema (8.4 docs referenced here, but this is not a new feature):
=# select column_name, data_type from information_schema.columns
-# where table_name = 'config';
column_name | data_type
--------------------+-----------
id | integer
default_printer_id | integer
master_host_enable | boolean
(3 rows)
Wayne Conrad
2010-01-27 12:55:13
As I said above, most of my users won't have access to pg_dump or a PostgreSQL server and so I must reproduce the behaviour of pg_dump.
Rui Pacheco
2010-01-29 12:48:55
Right. So *your code* queries the information schema (or shells out to psql), parses the results, and shows the result to your users.
Wayne Conrad
2010-01-29 13:40:48
A:
Here is a good article on how to get the meta information from information schema, http://www.alberton.info/postgresql_meta_info.html.
iavci
2010-08-09 08:35:32