Dynamically generating DDL for postgresql object

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, 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

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