-
Notifications
You must be signed in to change notification settings - Fork 1
/
information_schema_views.sql
105 lines (96 loc) · 4.15 KB
/
information_schema_views.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
--
-- information_schema_views.sql
--
--
-- Create a simple view for business tables’ relationships
--
CREATE OR REPLACE VIEW foreign_key_constraints_vw AS
SELECT
tc.table_schema,
tc.table_name,
kcu.column_name,
ctu.table_schema f_table_schema,
ctu.table_name f_table_name,
ccu.column_name f_column_name,
tc.constraint_name,
tc.constraint_type
FROM
information_schema.table_constraints tc,
information_schema.key_column_usage kcu,
information_schema.constraint_table_usage ctu,
information_schema.constraint_column_usage ccu
WHERE
tc.constraint_type = 'FOREIGN KEY'
AND tc.constraint_name = kcu.constraint_name
AND tc.constraint_name = ctu.constraint_name
AND tc.constraint_name = ccu.constraint_name
ORDER BY
tc.constraint_name;
-- Update information_schema views to enable non admin users to view foreign key constraints
--
-- information_schema.constraint_table_usage
-- information_schema.constraint_column_usage
--
-- Give the following commands on psql prompt, copy and remove the last past of the where clause
-- and replace the original view
--
-- # \d+ information_schema.constraint_table_usage
-- ... AND pg_has_role(r.relowner, 'USAGE'::text)
-- # \d+ information_schema.constraint_column_usage
-- ... WHERE pg_has_role(x.tblowner, 'USAGE'::text)
--
-- information_schema.constraint_table_usage
--
CREATE OR REPLACE VIEW information_schema.constraint_table_usage AS
SELECT current_database()::information_schema.sql_identifier AS table_catalog,
nr.nspname::information_schema.sql_identifier AS table_schema,
r.relname::information_schema.sql_identifier AS table_name,
current_database()::information_schema.sql_identifier AS constraint_catalog,
nc.nspname::information_schema.sql_identifier AS constraint_schema,
c.conname::information_schema.sql_identifier AS constraint_name
FROM pg_constraint c,
pg_namespace nc,
pg_class r,
pg_namespace nr
WHERE c.connamespace = nc.oid AND r.relnamespace = nr.oid AND (c.contype = 'f'::"char" AND c.confrelid = r.oid OR (c.contype = ANY (ARRAY['p'::"char", 'u'::"char"])) AND c.conrelid = r.oid) AND r.relkind = 'r'::"char";
--
-- information_schema.constraint_column_usage
--
CREATE OR REPLACE VIEW information_schema.constraint_column_usage AS
SELECT current_database()::information_schema.sql_identifier AS table_catalog,
x.tblschema::information_schema.sql_identifier AS table_schema,
x.tblname::information_schema.sql_identifier AS table_name,
x.colname::information_schema.sql_identifier AS column_name,
current_database()::information_schema.sql_identifier AS constraint_catalog,
x.cstrschema::information_schema.sql_identifier AS constraint_schema,
x.cstrname::information_schema.sql_identifier 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);