CREATE OR REPLACE VIEW scrud.constraint_column_usage
AS
SELECT current_database()::text AS table_catalog,
x.tblschema::text AS table_schema,
x.tblname::text AS table_name,
x.colname::text AS column_name,
current_database()::text AS constraint_catalog,
x.cstrschema::text AS constraint_schema,
x.cstrname::text AS constraint_name
FROM ( SELECT DISTINCT nr.nspname,
r.relname,
r.relowner,
a.attname,
nc.nspname,
c.conname
FROM pg_namespace nr,
pg_class r,
pg_attribute a,
pg_depend d,
pg_namespace nc,
pg_constraint c
WHERE nr.oid = r.relnamespace AND r.oid = a.attrelid AND d.refclassid = 'pg_class'::regclass::oid AND d.refobjid = r.oid AND d.refobjsubid = a.attnum AND d.classid = 'pg_constraint'::regclass::oid AND d.objid = c.oid AND c.connamespace = nc.oid AND c.contype = 'c'::"char" AND r.relkind = 'r'::"char" AND NOT a.attisdropped
UNION ALL
SELECT nr.nspname,
r.relname,
r.relowner,
a.attname,
nc.nspname,
c.conname
FROM pg_namespace nr,
pg_class r,
pg_attribute a,
pg_namespace nc,
pg_constraint c
WHERE nr.oid = r.relnamespace AND r.oid = a.attrelid AND nc.oid = c.connamespace AND
CASE
WHEN c.contype = 'f'::"char" THEN r.oid = c.confrelid AND (a.attnum = ANY (c.confkey))
ELSE r.oid = c.conrelid AND (a.attnum = ANY (c.conkey))
END AND NOT a.attisdropped AND (c.contype = ANY (ARRAY['p'::"char", 'u'::"char", 'f'::"char"])) AND r.relkind = 'r'::"char") x(tblschema, tblname, tblowner, colname, cstrschema, cstrname);