Re: [SQLObject] _fromDatabase for Postgres

[David M. C. <da...@da...>]

On Thu, Apr 17, 2003 at 06:29:59PM -0500, Ian Bicking wrote:

> columnsFromSchema is pretty much it.  I don't believe anyone else has
> indicated they're working on it.  I assume it involves parsing the
> appropriate pg_* tables, but I just didn't know what the appropriate
> tables are (sure are a lot of them).

Looks like guessClass will also need to be generalized a bit as Postgres has
type names like "character varying(30)".  Or maybe this should be a method
of the DBAPI subclass.  Also not sure what to do with booleans, but that's
another thread.

For the curious, here's a query (found using psql -E) that gets the same
info as the MySQL "SHOW COLUMNS FROM %s" query (field, type, notnull,
default):

SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod),
a.attnotnull, substring(d.adsrc for 128)
FROM pg_catalog.pg_attribute a, pg_catalog.pg_attrdef d
WHERE a.attrelid = '%s'::regclass AND d.adrelid=a.attrelid
AND d.adnum = a.attnum AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum

Obvious, isn't it!  However, this returns a string representation of the
default value.

To get foreign key info I use:

SELECT pg_catalog.pg_get_constraintdef(oid) as condef
FROM pg_catalog.pg_constraint r
WHERE r.conrelid = '%s'::regclass AND r.contype = 'f'

which returns the equivalent SQL used to define the constraints, e.g.

FOREIGN KEY (recording_id) REFERENCES recording(id) ON UPDATE NO ACTION ON
DELETE CASCADE

This can be with the regular expression:

\((.+)\) REFERENCES (.+)\(

Dave Cook