Re: [SQLObject] _fromDatabase for Postgres

On Fri, Apr 18, 2003 at 01:37:58AM -0700, David M. Cook wrote:

> 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

Erk, that won't work.  This will

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

Dave