HOWTO: Get a table or database definition

[Google Mike]

I guess it would be great if Pgsql had a way to find a database
definition via a system stored procedure like other database platforms
have.

There are two ways I've found so far:

SELECT
attname as "name", typname as "type", atttypmod - 4 as "size",
relhaspkey as "is_primary_key", *
FROM
pg_class AS a
LEFT OUTER JOIN pg_attribute AS b ON (b.attrelid = a.oid)
LEFT OUTER JOIN pg_type AS c ON (b.atttypid = c.oid)
where a.relname = 'names' and b.attstattarget = -1
order by attnum;

....yields great results for a table called 'names'

The other way is:

pg_dump -h localhost -p 5432 -U root -s -C test | grep -i "CREATE" -A
500000 | grep -v "\-\-" | grep -v "\\connect" | grep -v "SET " | tr -s
"\n"

....shows me a result for host 'localhost', port '5432', user 'root',
database 'test'

[Google Mike]

One other option, which I had forgotten for a long time, was:

\d <object name>

....which can describe many things, although this doesn't give you the
CREATE syntax like a pg_dump can do. Please also note that a pg_dump
can dump output to the screen if you don't specify a file, so if
you're only outputting the schema with "-s -C", it doesn't really
impact a live production database much at all.

[Jeff Boes]

Google Mike wrote:

I guess it would be great if Pgsql had a way to find a database
definition via a system stored procedure like other database platforms
have.

There are a few:

nexcerpt=# \df pg_get*def
List of functions
Result data type | Schema | Name | Argument data
types
------------------+------------+----------------------+-----------------------
text | pg_catalog | pg_get_constraintdef | oid
text | pg_catalog | pg_get_constraintdef | oid, boolean
text | pg_catalog | pg_get_indexdef | oid
text | pg_catalog | pg_get_indexdef | oid, integer,
boolean
text | pg_catalog | pg_get_ruledef | oid
text | pg_catalog | pg_get_ruledef | oid, boolean
text | pg_catalog | pg_get_triggerdef | oid
text | pg_catalog | pg_get_viewdef | oid
text | pg_catalog | pg_get_viewdef | oid, boolean
text | pg_catalog | pg_get_viewdef | text
text | pg_catalog | pg_get_viewdef | text, boolean
(11 rows)
It looks like 'pg_get_tabledef' isn't one of them, though.

--
(Posted from an account used as a SPAM dump. If you really want to get
in touch with me, dump the 'jboes' and substitute 'mur'.)
________
Jeffery Boes <>< jb***@qtm.net