Finding table-info per database, not tableowner - Mailing list pgsql-novice

From WIERS,FREDERIK (HP-Netherlands,ex1)
Subject Finding table-info per database, not tableowner
Date
Msg-id 7602716236F9D3118AB50090278CE55B060F3267@escher.neth.hp.com
Whole thread Raw
List pgsql-novice
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

pgsql-novice by date:

Previous
From: ChristophSchmidt
Date:
Subject: PostgreSQL and MS-Excel (ODBC)
Next
From: Patrick Coulombe
Date:
Subject: vacuum analyze - cannot insert duplicate key...