Обсуждение: Dynamically generating DDL for postgresql object
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
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
You can use pg_dump.exe to generate DDL in postgre. see: http://www.postgresql.org/docs/8.2/interactive/app-pgdump.html also you might check out the app in my sig for a tool that generates full reports/documentation about any pg database. hth, Jesse ----------------------- http://www.elsasoft.org On Jun 15, 11:31 am, markaso...@hotmail.com ("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 > <http://www.postgresql.org/developer/summerofcode.html> Google SoC page > > Anyone know how to get object DDL SQL through a script? Ideas on > alternative approaches would also be appreciated. > > Thanks, > > Mark
Thanks, John. This is very helpful in getting me on the right track. The pg_get_constraintdef(oid) function seems to provide what's needed to recreate the constraint. Interestingly, it doesn't include some of the information displayed in pgAdmin (i.e. Match type, On Update, On Delete) - perhaps these property values will be easy to "guess" when recreating the constraint. Example below ... Thank you again, John. Cheers, Mark Example: Case 1: pg_get_constraintdef(oid) output: "FOREIGN KEY (permission_id) REFERENCES auth_permission(id) DEFERRABLE INITIALLY DEFERRED" Caset 2: pgAdmin SQL pane display ALTER TABLE auth_group_permissions ADD CONSTRAINT auth_group_permissions_permission_id_fkey FOREIGN KEY (permission_id) REFERENCES auth_permission (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED; ----------------------- Mark Soper markasoper@hotmail.com 25 Fairmont St #2 Cambridge, MA 02139 (617) 491-4134 -----Original Message----- From: John DeSoi [mailto:desoi@pgedit.com] Sent: Friday, June 15, 2007 9:51 PM To: Mark Soper Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Dynamically generating DDL for postgresql object 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
"Mark Soper" <markasoper@hotmail.com> writes: > Interestingly, it doesn't include some of the > information displayed in pgAdmin (i.e. Match type, On Update, On Delete) - pg_get_constraintdef is aware that those values are the default ... regards, tom lane
Aha ... makes sense. Thank you, Tom. Mark -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Saturday, June 16, 2007 11:21 AM To: Mark Soper Cc: 'John DeSoi'; pgsql-general@postgresql.org Subject: Re: [GENERAL] Dynamically generating DDL for postgresql object "Mark Soper" <markasoper@hotmail.com> writes: > Interestingly, it doesn't include some of the > information displayed in pgAdmin (i.e. Match type, On Update, On Delete) - pg_get_constraintdef is aware that those values are the default ... regards, tom lane