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
|
