2 * PostgreSQL System Views
4 * Copyright (c) 1996-2005, PostgreSQL Global Development Group
6 * $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.12 2005/05/09 11:31:32 neilc Exp $
16 '********'::text as passwd,
21 CREATE VIEW pg_rules AS
23 N.nspname AS schemaname,
24 C.relname AS tablename,
25 R.rulename AS rulename,
26 pg_get_ruledef(R.oid) AS definition
27 FROM (pg_rewrite R JOIN pg_class C ON (C.oid = R.ev_class))
28 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
29 WHERE R.rulename != '_RETURN';
31 CREATE VIEW pg_views AS
33 N.nspname AS schemaname,
34 C.relname AS viewname,
35 pg_get_userbyid(C.relowner) AS viewowner,
36 pg_get_viewdef(C.oid) AS definition
37 FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
38 WHERE C.relkind = 'v';
40 CREATE VIEW pg_tables AS
42 N.nspname AS schemaname,
43 C.relname AS tablename,
44 pg_get_userbyid(C.relowner) AS tableowner,
45 T.spcname AS tablespace,
46 C.relhasindex AS hasindexes,
47 C.relhasrules AS hasrules,
48 (C.reltriggers > 0) AS hastriggers
49 FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
50 LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace)
51 WHERE C.relkind = 'r';
53 CREATE VIEW pg_indexes AS
55 N.nspname AS schemaname,
56 C.relname AS tablename,
57 I.relname AS indexname,
58 T.spcname AS tablespace,
59 pg_get_indexdef(I.oid) AS indexdef
60 FROM pg_index X JOIN pg_class C ON (C.oid = X.indrelid)
61 JOIN pg_class I ON (I.oid = X.indexrelid)
62 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
63 LEFT JOIN pg_tablespace T ON (T.oid = I.reltablespace)
64 WHERE C.relkind = 'r' AND I.relkind = 'i';
66 CREATE VIEW pg_stats AS
68 nspname AS schemaname,
71 stanullfrac AS null_frac,
72 stawidth AS avg_width,
73 stadistinct AS n_distinct,
75 WHEN stakind1 THEN stavalues1
76 WHEN stakind2 THEN stavalues2
77 WHEN stakind3 THEN stavalues3
78 WHEN stakind4 THEN stavalues4
79 END AS most_common_vals,
81 WHEN stakind1 THEN stanumbers1
82 WHEN stakind2 THEN stanumbers2
83 WHEN stakind3 THEN stanumbers3
84 WHEN stakind4 THEN stanumbers4
85 END AS most_common_freqs,
87 WHEN stakind1 THEN stavalues1
88 WHEN stakind2 THEN stavalues2
89 WHEN stakind3 THEN stavalues3
90 WHEN stakind4 THEN stavalues4
91 END AS histogram_bounds,
93 WHEN stakind1 THEN stanumbers1[1]
94 WHEN stakind2 THEN stanumbers2[1]
95 WHEN stakind3 THEN stanumbers3[1]
96 WHEN stakind4 THEN stanumbers4[1]
98 FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid)
99 JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum)
100 LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
101 WHERE has_table_privilege(c.oid, 'select');
103 REVOKE ALL on pg_statistic FROM public;
105 CREATE VIEW pg_stat_all_tables AS
108 N.nspname AS schemaname,
109 C.relname AS relname,
110 pg_stat_get_numscans(C.oid) AS seq_scan,
111 pg_stat_get_tuples_returned(C.oid) AS seq_tup_read,
112 sum(pg_stat_get_numscans(I.indexrelid)) AS idx_scan,
113 sum(pg_stat_get_tuples_fetched(I.indexrelid)) AS idx_tup_fetch,
114 pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins,
115 pg_stat_get_tuples_updated(C.oid) AS n_tup_upd,
116 pg_stat_get_tuples_deleted(C.oid) AS n_tup_del
117 FROM pg_class C LEFT JOIN
118 pg_index I ON C.oid = I.indrelid
119 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
120 WHERE C.relkind = 'r'
121 GROUP BY C.oid, N.nspname, C.relname;
123 CREATE VIEW pg_stat_sys_tables AS
124 SELECT * FROM pg_stat_all_tables
125 WHERE schemaname IN ('pg_catalog', 'pg_toast', 'information_schema');
127 CREATE VIEW pg_stat_user_tables AS
128 SELECT * FROM pg_stat_all_tables
129 WHERE schemaname NOT IN ('pg_catalog', 'pg_toast', 'information_schema');
131 CREATE VIEW pg_statio_all_tables AS
134 N.nspname AS schemaname,
135 C.relname AS relname,
136 pg_stat_get_blocks_fetched(C.oid) -
137 pg_stat_get_blocks_hit(C.oid) AS heap_blks_read,
138 pg_stat_get_blocks_hit(C.oid) AS heap_blks_hit,
139 sum(pg_stat_get_blocks_fetched(I.indexrelid) -
140 pg_stat_get_blocks_hit(I.indexrelid)) AS idx_blks_read,
141 sum(pg_stat_get_blocks_hit(I.indexrelid)) AS idx_blks_hit,
142 pg_stat_get_blocks_fetched(T.oid) -
143 pg_stat_get_blocks_hit(T.oid) AS toast_blks_read,
144 pg_stat_get_blocks_hit(T.oid) AS toast_blks_hit,
145 pg_stat_get_blocks_fetched(X.oid) -
146 pg_stat_get_blocks_hit(X.oid) AS tidx_blks_read,
147 pg_stat_get_blocks_hit(X.oid) AS tidx_blks_hit
148 FROM pg_class C LEFT JOIN
149 pg_index I ON C.oid = I.indrelid LEFT JOIN
150 pg_class T ON C.reltoastrelid = T.oid LEFT JOIN
151 pg_class X ON T.reltoastidxid = X.oid
152 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
153 WHERE C.relkind = 'r'
154 GROUP BY C.oid, N.nspname, C.relname, T.oid, X.oid;
156 CREATE VIEW pg_statio_sys_tables AS
157 SELECT * FROM pg_statio_all_tables
158 WHERE schemaname IN ('pg_catalog', 'pg_toast', 'information_schema');
160 CREATE VIEW pg_statio_user_tables AS
161 SELECT * FROM pg_statio_all_tables
162 WHERE schemaname NOT IN ('pg_catalog', 'pg_toast', 'information_schema');
164 CREATE VIEW pg_stat_all_indexes AS
168 N.nspname AS schemaname,
169 C.relname AS relname,
170 I.relname AS indexrelname,
171 pg_stat_get_numscans(I.oid) AS idx_scan,
172 pg_stat_get_tuples_returned(I.oid) AS idx_tup_read,
173 pg_stat_get_tuples_fetched(I.oid) AS idx_tup_fetch
175 pg_index X ON C.oid = X.indrelid JOIN
176 pg_class I ON I.oid = X.indexrelid
177 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
178 WHERE C.relkind = 'r';
180 CREATE VIEW pg_stat_sys_indexes AS
181 SELECT * FROM pg_stat_all_indexes
182 WHERE schemaname IN ('pg_catalog', 'pg_toast', 'information_schema');
184 CREATE VIEW pg_stat_user_indexes AS
185 SELECT * FROM pg_stat_all_indexes
186 WHERE schemaname NOT IN ('pg_catalog', 'pg_toast', 'information_schema');
188 CREATE VIEW pg_statio_all_indexes AS
192 N.nspname AS schemaname,
193 C.relname AS relname,
194 I.relname AS indexrelname,
195 pg_stat_get_blocks_fetched(I.oid) -
196 pg_stat_get_blocks_hit(I.oid) AS idx_blks_read,
197 pg_stat_get_blocks_hit(I.oid) AS idx_blks_hit
199 pg_index X ON C.oid = X.indrelid JOIN
200 pg_class I ON I.oid = X.indexrelid
201 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
202 WHERE C.relkind = 'r';
204 CREATE VIEW pg_statio_sys_indexes AS
205 SELECT * FROM pg_statio_all_indexes
206 WHERE schemaname IN ('pg_catalog', 'pg_toast', 'information_schema');
208 CREATE VIEW pg_statio_user_indexes AS
209 SELECT * FROM pg_statio_all_indexes
210 WHERE schemaname NOT IN ('pg_catalog', 'pg_toast', 'information_schema');
212 CREATE VIEW pg_statio_all_sequences AS
215 N.nspname AS schemaname,
216 C.relname AS relname,
217 pg_stat_get_blocks_fetched(C.oid) -
218 pg_stat_get_blocks_hit(C.oid) AS blks_read,
219 pg_stat_get_blocks_hit(C.oid) AS blks_hit
221 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
222 WHERE C.relkind = 'S';
224 CREATE VIEW pg_statio_sys_sequences AS
225 SELECT * FROM pg_statio_all_sequences
226 WHERE schemaname IN ('pg_catalog', 'pg_toast', 'information_schema');
228 CREATE VIEW pg_statio_user_sequences AS
229 SELECT * FROM pg_statio_all_sequences
230 WHERE schemaname NOT IN ('pg_catalog', 'pg_toast', 'information_schema');
232 CREATE VIEW pg_stat_activity AS
235 D.datname AS datname,
236 pg_stat_get_backend_pid(S.backendid) AS procpid,
237 pg_stat_get_backend_userid(S.backendid) AS usesysid,
238 U.usename AS usename,
239 pg_stat_get_backend_activity(S.backendid) AS current_query,
240 pg_stat_get_backend_activity_start(S.backendid) AS query_start,
241 pg_stat_get_backend_start(S.backendid) AS backend_start,
242 pg_stat_get_backend_client_addr(S.backendid) AS client_addr,
243 pg_stat_get_backend_client_port(S.backendid) AS client_port
245 (SELECT pg_stat_get_backend_idset() AS backendid) AS S,
247 WHERE pg_stat_get_backend_dbid(S.backendid) = D.oid AND
248 pg_stat_get_backend_userid(S.backendid) = U.usesysid;
250 CREATE VIEW pg_stat_database AS
253 D.datname AS datname,
254 pg_stat_get_db_numbackends(D.oid) AS numbackends,
255 pg_stat_get_db_xact_commit(D.oid) AS xact_commit,
256 pg_stat_get_db_xact_rollback(D.oid) AS xact_rollback,
257 pg_stat_get_db_blocks_fetched(D.oid) -
258 pg_stat_get_db_blocks_hit(D.oid) AS blks_read,
259 pg_stat_get_db_blocks_hit(D.oid) AS blks_hit
262 CREATE VIEW pg_locks AS
264 FROM pg_lock_status() AS L(relation oid, database oid,
265 transaction xid, pid int4, mode text, granted boolean);
267 CREATE VIEW pg_settings AS
269 FROM pg_show_all_settings() AS A
270 (name text, setting text, category text, short_desc text, extra_desc text,
271 context text, vartype text, source text, min_val text, max_val text);
273 CREATE RULE pg_settings_u AS
274 ON UPDATE TO pg_settings
275 WHERE new.name = old.name DO
276 SELECT set_config(old.name, new.setting, 'f');
278 CREATE RULE pg_settings_n AS
279 ON UPDATE TO pg_settings
282 GRANT SELECT, UPDATE ON pg_settings TO PUBLIC;