-- get list of tables/views
SELECT	CASE WHEN relkind = 'r' THEN 'table' WHEN relkind = 'v' THEN 'view' ELSE 'unknown' END as type,
			current_database() as Database,
			ns.nspname as Schema,
			relname as Table,
			--CASE WHEN ns.nspname = 'pg_catalog' THEN 'Catalog' WHEN ns.nspname = 'information_schema' THEN 'Information' ELSE 'User' END as TableType,
			pg_get_userbyid(relowner) AS definer,
			reltuples as RowNumber,
			description as Comment,
			relnatts as ColumnNumber,
			reltriggers as TriggersNumber,
			conname as Constraint,
			conkey as ColumnConstrainsIndexes,
			rel.oid as Oid,
			relacl as ACL,
			relhasoids as HasOids,
			relhassubclass as HasSubtables
FROM pg_class rel
INNER JOIN pg_namespace ns ON relnamespace = ns.oid
LEFT OUTER JOIN pg_description des ON des.objoid=rel.oid AND des.objsubid=0
LEFT OUTER JOIN pg_constraint c ON c.conrelid=rel.oid AND c.contype='p'
WHERE (relkind = 'r' OR relkind = 'v') AND
		NOT ns.nspname like 'pg\_%' AND
		ns.nspname != 'information_schema'
ORDER BY relkind, ns.nspname, relname;


-- get list of fields/columns for tables/views/types
SELECT	CASE WHEN cl.relkind = 'r' THEN 'table' WHEN cl.relkind = 'v' THEN 'view' WHEN cl.relkind = 'c' THEN 'type' ELSE 'unknown' END as type,
			na.nspname as Schema,
			cl.relname as Table,
			att.attname AS Column,
			(select nspname from pg_namespace where oid = ty.typnamespace) as TypeSchema,
			CASE WHEN ty.typname = 'bpchar' THEN 'char' WHEN ty.typname = '_bpchar' THEN '_char' ELSE ty.typname END as TypeName,
			CASE WHEN ty.typname = 'bit' or ty.typname = 'interval' and att.atttypmod > 0 THEN att.atttypmod WHEN att.atttypmod > 0 THEN att.atttypmod - 4 ELSE att.atttypmod END as Precision,
			att.attlen as Length,
			att.attnum as Position,
			cs.relname AS SeqName,
			def.adsrc as DefaultValue,
			att.attnotnull = 'f' as Nullable,
			att.attisdropped as IsDropped,
			att.attinhcount as AncestorCount,
			att.attndims as Dimension,
			CASE WHEN attndims > 0 THEN true ELSE false END AS isarray,
			et.typname as elementaltypename,
			description as Comment
FROM pg_attribute att
JOIN pg_type ty ON ty.oid=atttypid
JOIN pg_namespace tn ON tn.oid=ty.typnamespace
JOIN pg_class cl ON cl.oid=attrelid
JOIN pg_namespace na ON na.oid=cl.relnamespace
LEFT OUTER JOIN pg_type et ON et.oid=ty.typelem
LEFT OUTER JOIN pg_attrdef def ON adrelid=attrelid AND adnum=attnum
LEFT OUTER JOIN pg_description des ON des.objoid=attrelid AND des.objsubid=attnum
LEFT OUTER JOIN (pg_depend JOIN pg_class cs ON objid=cs.oid AND cs.relkind='S') ON refobjid=attrelid AND refobjsubid=attnum
LEFT OUTER JOIN pg_namespace ns ON ns.oid=cs.relnamespace
WHERE		(cl.relkind = 'r' OR cl.relkind = 'v' OR cl.relkind = 'c') AND
			attnum > 0 AND
			attisdropped IS FALSE AND
			not na.nspname like 'pg_%' AND
			na.nspname != 'information_schema'
ORDER BY cl.relkind, na.nspname, cl.relname, att.attnum;