On Jun 15, 2007, at 2:31 PM, Mark Soper wrote:
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.
There are a number of built-in functions for getting the SQL DDL, but
it depends on the version you are using. Here is what I see for 8.2:
=== psql 1 ===
\df pg_get*
List of functions
Schema | Name | Result data type | Argument
data types
------------+------------------------+------------------
+-----------------------
pg_catalog | pg_get_constraintdef | text | oid
pg_catalog | pg_get_constraintdef | text | oid, boolean
pg_catalog | pg_get_expr | text | text, oid
pg_catalog | pg_get_expr | text | text, oid,
boolean
pg_catalog | pg_get_indexdef | text | oid
pg_catalog | pg_get_indexdef | text | oid,
integer, boolean
pg_catalog | pg_get_ruledef | text | oid
pg_catalog | pg_get_ruledef | text | oid, boolean
pg_catalog | pg_get_serial_sequence | text | text, text
pg_catalog | pg_get_triggerdef | text | oid
pg_catalog | pg_get_userbyid | name | oid
pg_catalog | pg_get_viewdef | text | oid
pg_catalog | pg_get_viewdef | text | oid, boolean
pg_catalog | pg_get_viewdef | text | text
pg_catalog | pg_get_viewdef | text | text, boolean
(15 rows)
For example, if you know the table oid, you can get the constraint
definitions with something like this:
select 'CONSTRAINT ' || conname || ' ' || pg_get_constraintdef(oid)
|| ';' as condef
from pg_constraint where conrelid = oidxyz;
Getting the full DDL for a table requires a lot more work using the
system catalogs. See pg_dump for hints.
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
