back to notes

;; Source: http://dba.stackexchange.com/a/102624

SELECT (currentdatabase())::informationschema.sqlidentifier AS tablecatalog, (nc.nspname)::informationschema.sqlidentifier AS tableschema, (c.relname)::informationschema.sqlidentifier AS tablename, ( CASE WHEN pghasrole(c.relowner, 'USAGE'::text) THEN pggetviewdef(c.oid) ELSE NULL::text END)::informationschema.characterdata AS viewdefinition, ( CASE WHEN ('checkoption=cascaded'::text = ANY (c.reloptions)) THEN 'CASCADED'::text WHEN ('checkoption=local'::text = ANY (c.reloptions)) THEN 'LOCAL'::text ELSE 'NONE'::text END)::informationschema.characterdata AS checkoption, ( CASE WHEN ((pgrelationisupdatable((c.oid)::regclass, false) & 20) = 20) THEN 'YES'::text ELSE 'NO'::text END)::informationschema.yesorno AS isupdatable, ( CASE WHEN ((pgrelationisupdatable((c.oid)::regclass, false) & 8) = 8) THEN 'YES'::text ELSE 'NO'::text END)::informationschema.yesorno AS isinsertableinto, ( CASE WHEN (EXISTS ( SELECT 1 FROM pgtrigger WHERE ((pgtrigger.tgrelid = c.oid) AND (((pgtrigger.tgtype)::integer & 81) = 81)))) THEN 'YES'::text ELSE 'NO'::text END)::informationschema.yesorno AS istriggerupdatable, ( CASE WHEN (EXISTS ( SELECT 1 FROM pgtrigger WHERE ((pgtrigger.tgrelid = c.oid) AND (((pgtrigger.tgtype)::integer & 73) = 73)))) THEN 'YES'::text ELSE 'NO'::text END)::informationschema.yesorno AS istriggerdeletable, ( CASE WHEN (EXISTS ( SELECT 1 FROM pgtrigger WHERE ((pgtrigger.tgrelid = c.oid) AND (((pgtrigger.tgtype)::integer & 69) = 69)))) THEN 'YES'::text ELSE 'NO'::text END)::informationschema.yesorno AS istriggerinsertableinto FROM pgnamespace nc, pgclass c WHERE ((((c.relnamespace = nc.oid) AND (c.relkind = 'm'::"char")) AND (NOT pgisothertempschema(nc.oid))) AND ((pghasrole(c.relowner, 'USAGE':: text) OR hastableprivilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text)) OR hasanycolumn_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text)));



last updated november 2016