As you might recognize, this is supposed to be a psql \d imitation in one
shot:
SELECT usename as "Owner", relname as "Relation",
(CASE WHEN relkind='r' THEN (CASE WHEN 0<(select count(*) from pg_views where viewname = relname)THEN 'view' ELSE
'table'END) WHEN relkind='i' THEN 'index' WHEN relkind='S' THEN 'sequence' ELSE 'other'
END) as "Type"
FROM pg_class, pg_user
WHERE usesysid = relowner AND
( relkind = 'r' OR relkind = 'i' OR relkind = 'S') AND
relname !~ '^pg_' AND
(relkind != 'i' OR relname !~ '^xinx') ORDER BY relname;
ERROR: flatten_tlistentry: Cannot handle node type 108
However, if you do
- (CASE WHEN 0<(select count(*) from pg_views where viewname = relname)
- THEN 'view' ELSE 'table' END)
+ 'relation'
if works fine. No nested CASE's?
PostgreSQL 6.5.2 on i586-pc-linux-gnu, compiled by egcs
--
Peter Eisentraut - peter_e@gmx.net
http://yi.org/peter-e
