I’d like to dynamically generate a SQL script that pulls together complete DDL (CREATE, ALTER, etc.) for specific objects (especially constraints) in my PostgreSQL 8 database. I want to use this for our development project’s schema migration process, which involves dumping the data, making schema changes, recreating the database, temporarily removing constraints, restoring the data, and re-applying the constraints (this last step requires the dynamic DDL described above).
I’ve noticed that pgAdmin’s “DDL Report” feature can retrieve this DDL for many types of objects – not sure how much work it has to do to build the SQL it’s displaying here. But I haven’t seen anything about getting this directly from the database using SQL , psql, etc. I’ve only seen reference to this as a proposed project on the PostgreSQL Google SoC page
Anyone know how to get object DDL SQL through a script? Ideas on alternative approaches would also be appreciated.
Thanks,
Mark