1100
1100
FROM (pg_namespace ncon
1101
1101
INNER JOIN pg_constraint con ON ncon.oid = con.connamespace
1102
INNER JOIN pg_class c ON con.conrelid = c.oid)
1105
INNER JOIN pg_namespace npkc ON pkc.connamespace = npkc.oid)
1106
ON con.confrelid = pkc.conrelid
1107
AND _pg_keysequal(con.confkey, pkc.conkey)
1102
INNER JOIN pg_class c ON con.conrelid = c.oid AND con.contype = 'f')
1103
LEFT JOIN pg_depend d1 -- find constraint's dependency on an index
1104
ON d1.objid = con.oid AND d1.classid = 'pg_constraint'::regclass
1105
AND d1.refclassid = 'pg_class'::regclass AND d1.refobjsubid = 0
1106
LEFT JOIN pg_depend d2 -- find pkey/unique constraint for that index
1107
ON d2.refclassid = 'pg_constraint'::regclass
1108
AND d2.classid = 'pg_class'::regclass
1109
AND d2.objid = d1.refobjid AND d2.objsubid = 0
1110
AND d2.deptype = 'i'
1111
LEFT JOIN pg_constraint pkc ON pkc.oid = d2.refobjid
1112
AND pkc.contype IN ('p', 'u')
1113
AND pkc.conrelid = con.confrelid
1114
LEFT JOIN pg_namespace npkc ON pkc.connamespace = npkc.oid
1109
WHERE c.relkind = 'r'
1110
AND con.contype = 'f'
1111
AND (pkc.contype IN ('p', 'u') OR pkc.contype IS NULL)
1112
AND (pg_has_role(c.relowner, 'USAGE')
1113
-- SELECT privilege omitted, per SQL standard
1114
OR has_table_privilege(c.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1115
OR has_any_column_privilege(c.oid, 'INSERT, UPDATE, REFERENCES') );
1116
WHERE pg_has_role(c.relowner, 'USAGE')
1117
-- SELECT privilege omitted, per SQL standard
1118
OR has_table_privilege(c.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1119
OR has_any_column_privilege(c.oid, 'INSERT, UPDATE, REFERENCES') ;
1117
1121
GRANT SELECT ON referential_constraints TO PUBLIC;