Hi,
the default postgres database comes with some handy views (pg_user,
pg_tables, pg_views, etc.). I missed a columns view (among a 'describe
table' functionality), so I created it (see below). I would like this view
to include a column specifying the database (which is not the tableowner).
Does anybody know how to get this done ?
create view columns as
select pg_get_userbyid(t.relowner) AS tableowner, t.relname AS tablename,
c.attname AS columnname, ct.typname AS columntype, c.atttypmod as
columnspecific,
c.attnotnull AS columnnotnull, c.atthasdef AS columnhasdefault
from pg_class t, pg_attribute c, pg_type ct
where (
( (t.relkind='r'::"char") OR (t.relkind='s'::"char") ) AND
(t.oid=c.attrelid) AND (ct.oid=c.atttypid) AND (ct.typtype='b'::"char") AND
(c.attname<>'cmax') AND (c.attname<>'cmin') AND (c.attname<>'ctid') AND
(c.attname<>'tableoid') AND (c.attname<>'xmax') AND (c.attname<>'xmin') AND
(c.attname<>'oid')
)
order by tableowner, tablename, columnname;
You can try this view with the following SQL statement :
select * from columns where tablename like 'pg_%';
fw
