2 * PostgreSQL System Views
4 * Copyright (c) 1996-2010, PostgreSQL Global Development Group
6 * $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.67 2010/08/08 16:27:03 tgl Exp $
9 CREATE VIEW pg_roles AS
19 '********'::text as rolpassword,
21 setconfig as rolconfig,
23 FROM pg_authid LEFT JOIN pg_db_role_setting s
24 ON (pg_authid.oid = setrole AND setdatabase = 0);
26 CREATE VIEW pg_shadow AS
29 pg_authid.oid AS usesysid,
30 rolcreatedb AS usecreatedb,
32 rolcatupdate AS usecatupd,
33 rolpassword AS passwd,
34 rolvaliduntil::abstime AS valuntil,
35 setconfig AS useconfig
36 FROM pg_authid LEFT JOIN pg_db_role_setting s
37 ON (pg_authid.oid = setrole AND setdatabase = 0)
40 REVOKE ALL on pg_shadow FROM public;
42 CREATE VIEW pg_group AS
46 ARRAY(SELECT member FROM pg_auth_members WHERE roleid = oid) AS grolist
48 WHERE NOT rolcanlogin;
50 CREATE VIEW pg_user AS
57 '********'::text as passwd,
62 CREATE VIEW pg_rules AS
64 N.nspname AS schemaname,
65 C.relname AS tablename,
66 R.rulename AS rulename,
67 pg_get_ruledef(R.oid) AS definition
68 FROM (pg_rewrite R JOIN pg_class C ON (C.oid = R.ev_class))
69 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
70 WHERE R.rulename != '_RETURN';
72 CREATE VIEW pg_views AS
74 N.nspname AS schemaname,
75 C.relname AS viewname,
76 pg_get_userbyid(C.relowner) AS viewowner,
77 pg_get_viewdef(C.oid) AS definition
78 FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
79 WHERE C.relkind = 'v';
81 CREATE VIEW pg_tables AS
83 N.nspname AS schemaname,
84 C.relname AS tablename,
85 pg_get_userbyid(C.relowner) AS tableowner,
86 T.spcname AS tablespace,
87 C.relhasindex AS hasindexes,
88 C.relhasrules AS hasrules,
89 C.relhastriggers AS hastriggers
90 FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
91 LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace)
92 WHERE C.relkind = 'r';
94 CREATE VIEW pg_indexes AS
96 N.nspname AS schemaname,
97 C.relname AS tablename,
98 I.relname AS indexname,
99 T.spcname AS tablespace,
100 pg_get_indexdef(I.oid) AS indexdef
101 FROM pg_index X JOIN pg_class C ON (C.oid = X.indrelid)
102 JOIN pg_class I ON (I.oid = X.indexrelid)
103 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
104 LEFT JOIN pg_tablespace T ON (T.oid = I.reltablespace)
105 WHERE C.relkind = 'r' AND I.relkind = 'i';
107 CREATE VIEW pg_stats AS
109 nspname AS schemaname,
110 relname AS tablename,
112 stainherit AS inherited,
113 stanullfrac AS null_frac,
114 stawidth AS avg_width,
115 stadistinct AS n_distinct,
117 WHEN stakind1 IN (1, 4) THEN stavalues1
118 WHEN stakind2 IN (1, 4) THEN stavalues2
119 WHEN stakind3 IN (1, 4) THEN stavalues3
120 WHEN stakind4 IN (1, 4) THEN stavalues4
121 END AS most_common_vals,
123 WHEN stakind1 IN (1, 4) THEN stanumbers1
124 WHEN stakind2 IN (1, 4) THEN stanumbers2
125 WHEN stakind3 IN (1, 4) THEN stanumbers3
126 WHEN stakind4 IN (1, 4) THEN stanumbers4
127 END AS most_common_freqs,
129 WHEN stakind1 = 2 THEN stavalues1
130 WHEN stakind2 = 2 THEN stavalues2
131 WHEN stakind3 = 2 THEN stavalues3
132 WHEN stakind4 = 2 THEN stavalues4
133 END AS histogram_bounds,
135 WHEN stakind1 = 3 THEN stanumbers1[1]
136 WHEN stakind2 = 3 THEN stanumbers2[1]
137 WHEN stakind3 = 3 THEN stanumbers3[1]
138 WHEN stakind4 = 3 THEN stanumbers4[1]
140 FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid)
141 JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum)
142 LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
143 WHERE NOT attisdropped AND has_column_privilege(c.oid, a.attnum, 'select');
145 REVOKE ALL on pg_statistic FROM public;
147 CREATE VIEW pg_locks AS
148 SELECT * FROM pg_lock_status() AS L;
150 CREATE VIEW pg_cursors AS
151 SELECT * FROM pg_cursor() AS C;
153 CREATE VIEW pg_prepared_xacts AS
154 SELECT P.transaction, P.gid, P.prepared,
155 U.rolname AS owner, D.datname AS database
156 FROM pg_prepared_xact() AS P
157 LEFT JOIN pg_authid U ON P.ownerid = U.oid
158 LEFT JOIN pg_database D ON P.dbid = D.oid;
160 CREATE VIEW pg_prepared_statements AS
161 SELECT * FROM pg_prepared_statement() AS P;
163 CREATE VIEW pg_settings AS
164 SELECT * FROM pg_show_all_settings() AS A;
166 CREATE RULE pg_settings_u AS
167 ON UPDATE TO pg_settings
168 WHERE new.name = old.name DO
169 SELECT set_config(old.name, new.setting, 'f');
171 CREATE RULE pg_settings_n AS
172 ON UPDATE TO pg_settings
175 GRANT SELECT, UPDATE ON pg_settings TO PUBLIC;
177 CREATE VIEW pg_timezone_abbrevs AS
178 SELECT * FROM pg_timezone_abbrevs();
180 CREATE VIEW pg_timezone_names AS
181 SELECT * FROM pg_timezone_names();
185 CREATE VIEW pg_stat_all_tables AS
188 N.nspname AS schemaname,
189 C.relname AS relname,
190 pg_stat_get_numscans(C.oid) AS seq_scan,
191 pg_stat_get_tuples_returned(C.oid) AS seq_tup_read,
192 sum(pg_stat_get_numscans(I.indexrelid))::bigint AS idx_scan,
193 sum(pg_stat_get_tuples_fetched(I.indexrelid))::bigint +
194 pg_stat_get_tuples_fetched(C.oid) AS idx_tup_fetch,
195 pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins,
196 pg_stat_get_tuples_updated(C.oid) AS n_tup_upd,
197 pg_stat_get_tuples_deleted(C.oid) AS n_tup_del,
198 pg_stat_get_tuples_hot_updated(C.oid) AS n_tup_hot_upd,
199 pg_stat_get_live_tuples(C.oid) AS n_live_tup,
200 pg_stat_get_dead_tuples(C.oid) AS n_dead_tup,
201 pg_stat_get_last_vacuum_time(C.oid) as last_vacuum,
202 pg_stat_get_last_autovacuum_time(C.oid) as last_autovacuum,
203 pg_stat_get_last_analyze_time(C.oid) as last_analyze,
204 pg_stat_get_last_autoanalyze_time(C.oid) as last_autoanalyze
205 FROM pg_class C LEFT JOIN
206 pg_index I ON C.oid = I.indrelid
207 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
208 WHERE C.relkind IN ('r', 't')
209 GROUP BY C.oid, N.nspname, C.relname;
211 CREATE VIEW pg_stat_xact_all_tables AS
214 N.nspname AS schemaname,
215 C.relname AS relname,
216 pg_stat_get_xact_numscans(C.oid) AS seq_scan,
217 pg_stat_get_xact_tuples_returned(C.oid) AS seq_tup_read,
218 sum(pg_stat_get_xact_numscans(I.indexrelid))::bigint AS idx_scan,
219 sum(pg_stat_get_xact_tuples_fetched(I.indexrelid))::bigint +
220 pg_stat_get_xact_tuples_fetched(C.oid) AS idx_tup_fetch,
221 pg_stat_get_xact_tuples_inserted(C.oid) AS n_tup_ins,
222 pg_stat_get_xact_tuples_updated(C.oid) AS n_tup_upd,
223 pg_stat_get_xact_tuples_deleted(C.oid) AS n_tup_del,
224 pg_stat_get_xact_tuples_hot_updated(C.oid) AS n_tup_hot_upd
225 FROM pg_class C LEFT JOIN
226 pg_index I ON C.oid = I.indrelid
227 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
228 WHERE C.relkind IN ('r', 't')
229 GROUP BY C.oid, N.nspname, C.relname;
231 CREATE VIEW pg_stat_sys_tables AS
232 SELECT * FROM pg_stat_all_tables
233 WHERE schemaname IN ('pg_catalog', 'information_schema') OR
234 schemaname ~ '^pg_toast';
236 CREATE VIEW pg_stat_xact_sys_tables AS
237 SELECT * FROM pg_stat_xact_all_tables
238 WHERE schemaname IN ('pg_catalog', 'information_schema') OR
239 schemaname ~ '^pg_toast';
241 CREATE VIEW pg_stat_user_tables AS
242 SELECT * FROM pg_stat_all_tables
243 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
244 schemaname !~ '^pg_toast';
246 CREATE VIEW pg_stat_xact_user_tables AS
247 SELECT * FROM pg_stat_xact_all_tables
248 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
249 schemaname !~ '^pg_toast';
251 CREATE VIEW pg_statio_all_tables AS
254 N.nspname AS schemaname,
255 C.relname AS relname,
256 pg_stat_get_blocks_fetched(C.oid) -
257 pg_stat_get_blocks_hit(C.oid) AS heap_blks_read,
258 pg_stat_get_blocks_hit(C.oid) AS heap_blks_hit,
259 sum(pg_stat_get_blocks_fetched(I.indexrelid) -
260 pg_stat_get_blocks_hit(I.indexrelid))::bigint AS idx_blks_read,
261 sum(pg_stat_get_blocks_hit(I.indexrelid))::bigint AS idx_blks_hit,
262 pg_stat_get_blocks_fetched(T.oid) -
263 pg_stat_get_blocks_hit(T.oid) AS toast_blks_read,
264 pg_stat_get_blocks_hit(T.oid) AS toast_blks_hit,
265 pg_stat_get_blocks_fetched(X.oid) -
266 pg_stat_get_blocks_hit(X.oid) AS tidx_blks_read,
267 pg_stat_get_blocks_hit(X.oid) AS tidx_blks_hit
268 FROM pg_class C LEFT JOIN
269 pg_index I ON C.oid = I.indrelid LEFT JOIN
270 pg_class T ON C.reltoastrelid = T.oid LEFT JOIN
271 pg_class X ON T.reltoastidxid = X.oid
272 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
273 WHERE C.relkind IN ('r', 't')
274 GROUP BY C.oid, N.nspname, C.relname, T.oid, X.oid;
276 CREATE VIEW pg_statio_sys_tables AS
277 SELECT * FROM pg_statio_all_tables
278 WHERE schemaname IN ('pg_catalog', 'information_schema') OR
279 schemaname ~ '^pg_toast';
281 CREATE VIEW pg_statio_user_tables AS
282 SELECT * FROM pg_statio_all_tables
283 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
284 schemaname !~ '^pg_toast';
286 CREATE VIEW pg_stat_all_indexes AS
290 N.nspname AS schemaname,
291 C.relname AS relname,
292 I.relname AS indexrelname,
293 pg_stat_get_numscans(I.oid) AS idx_scan,
294 pg_stat_get_tuples_returned(I.oid) AS idx_tup_read,
295 pg_stat_get_tuples_fetched(I.oid) AS idx_tup_fetch
297 pg_index X ON C.oid = X.indrelid JOIN
298 pg_class I ON I.oid = X.indexrelid
299 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
300 WHERE C.relkind IN ('r', 't');
302 CREATE VIEW pg_stat_sys_indexes AS
303 SELECT * FROM pg_stat_all_indexes
304 WHERE schemaname IN ('pg_catalog', 'information_schema') OR
305 schemaname ~ '^pg_toast';
307 CREATE VIEW pg_stat_user_indexes AS
308 SELECT * FROM pg_stat_all_indexes
309 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
310 schemaname !~ '^pg_toast';
312 CREATE VIEW pg_statio_all_indexes AS
316 N.nspname AS schemaname,
317 C.relname AS relname,
318 I.relname AS indexrelname,
319 pg_stat_get_blocks_fetched(I.oid) -
320 pg_stat_get_blocks_hit(I.oid) AS idx_blks_read,
321 pg_stat_get_blocks_hit(I.oid) AS idx_blks_hit
323 pg_index X ON C.oid = X.indrelid JOIN
324 pg_class I ON I.oid = X.indexrelid
325 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
326 WHERE C.relkind IN ('r', 't');
328 CREATE VIEW pg_statio_sys_indexes AS
329 SELECT * FROM pg_statio_all_indexes
330 WHERE schemaname IN ('pg_catalog', 'information_schema') OR
331 schemaname ~ '^pg_toast';
333 CREATE VIEW pg_statio_user_indexes AS
334 SELECT * FROM pg_statio_all_indexes
335 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
336 schemaname !~ '^pg_toast';
338 CREATE VIEW pg_statio_all_sequences AS
341 N.nspname AS schemaname,
342 C.relname AS relname,
343 pg_stat_get_blocks_fetched(C.oid) -
344 pg_stat_get_blocks_hit(C.oid) AS blks_read,
345 pg_stat_get_blocks_hit(C.oid) AS blks_hit
347 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
348 WHERE C.relkind = 'S';
350 CREATE VIEW pg_statio_sys_sequences AS
351 SELECT * FROM pg_statio_all_sequences
352 WHERE schemaname IN ('pg_catalog', 'information_schema') OR
353 schemaname ~ '^pg_toast';
355 CREATE VIEW pg_statio_user_sequences AS
356 SELECT * FROM pg_statio_all_sequences
357 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
358 schemaname !~ '^pg_toast';
360 CREATE VIEW pg_stat_activity AS
363 D.datname AS datname,
366 U.rolname AS usename,
375 FROM pg_database D, pg_stat_get_activity(NULL) AS S, pg_authid U
376 WHERE S.datid = D.oid AND
379 CREATE VIEW pg_stat_database AS
382 D.datname AS datname,
383 pg_stat_get_db_numbackends(D.oid) AS numbackends,
384 pg_stat_get_db_xact_commit(D.oid) AS xact_commit,
385 pg_stat_get_db_xact_rollback(D.oid) AS xact_rollback,
386 pg_stat_get_db_blocks_fetched(D.oid) -
387 pg_stat_get_db_blocks_hit(D.oid) AS blks_read,
388 pg_stat_get_db_blocks_hit(D.oid) AS blks_hit,
389 pg_stat_get_db_tuples_returned(D.oid) AS tup_returned,
390 pg_stat_get_db_tuples_fetched(D.oid) AS tup_fetched,
391 pg_stat_get_db_tuples_inserted(D.oid) AS tup_inserted,
392 pg_stat_get_db_tuples_updated(D.oid) AS tup_updated,
393 pg_stat_get_db_tuples_deleted(D.oid) AS tup_deleted
396 CREATE VIEW pg_stat_user_functions AS
399 N.nspname AS schemaname,
400 P.proname AS funcname,
401 pg_stat_get_function_calls(P.oid) AS calls,
402 pg_stat_get_function_time(P.oid) / 1000 AS total_time,
403 pg_stat_get_function_self_time(P.oid) / 1000 AS self_time
404 FROM pg_proc P LEFT JOIN pg_namespace N ON (N.oid = P.pronamespace)
405 WHERE P.prolang != 12 -- fast check to eliminate built-in functions
406 AND pg_stat_get_function_calls(P.oid) IS NOT NULL;
408 CREATE VIEW pg_stat_xact_user_functions AS
411 N.nspname AS schemaname,
412 P.proname AS funcname,
413 pg_stat_get_xact_function_calls(P.oid) AS calls,
414 pg_stat_get_xact_function_time(P.oid) / 1000 AS total_time,
415 pg_stat_get_xact_function_self_time(P.oid) / 1000 AS self_time
416 FROM pg_proc P LEFT JOIN pg_namespace N ON (N.oid = P.pronamespace)
417 WHERE P.prolang != 12 -- fast check to eliminate built-in functions
418 AND pg_stat_get_xact_function_calls(P.oid) IS NOT NULL;
420 CREATE VIEW pg_stat_bgwriter AS
422 pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed,
423 pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req,
424 pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint,
425 pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean,
426 pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,
427 pg_stat_get_buf_written_backend() AS buffers_backend,
428 pg_stat_get_buf_alloc() AS buffers_alloc;
430 CREATE VIEW pg_user_mappings AS
434 S.srvname AS srvname,
436 CASE WHEN U.umuser = 0 THEN
441 CASE WHEN pg_has_role(S.srvowner, 'USAGE') OR has_server_privilege(S.oid, 'USAGE') THEN
446 FROM pg_user_mapping U
447 LEFT JOIN pg_authid A ON (A.oid = U.umuser) JOIN
448 pg_foreign_server S ON (U.umserver = S.oid);
450 REVOKE ALL on pg_user_mapping FROM public;
453 -- We have a few function definitions in here, too.
454 -- At some point there might be enough to justify breaking them out into
455 -- a separate "system_functions.sql" file.
458 -- Tsearch debug function. Defined here because it'd be pretty unwieldy
459 -- to put it into pg_proc.h
461 CREATE FUNCTION ts_debug(IN config regconfig, IN document text,
463 OUT description text,
465 OUT dictionaries regdictionary[],
466 OUT dictionary regdictionary,
468 RETURNS SETOF record AS
472 tt.description AS description,
473 parse.token AS token,
474 ARRAY ( SELECT m.mapdict::pg_catalog.regdictionary
475 FROM pg_catalog.pg_ts_config_map AS m
476 WHERE m.mapcfg = $1 AND m.maptokentype = parse.tokid
477 ORDER BY m.mapseqno )
479 ( SELECT mapdict::pg_catalog.regdictionary
480 FROM pg_catalog.pg_ts_config_map AS m
481 WHERE m.mapcfg = $1 AND m.maptokentype = parse.tokid
482 ORDER BY pg_catalog.ts_lexize(mapdict, parse.token) IS NULL, m.mapseqno
485 ( SELECT pg_catalog.ts_lexize(mapdict, parse.token)
486 FROM pg_catalog.pg_ts_config_map AS m
487 WHERE m.mapcfg = $1 AND m.maptokentype = parse.tokid
488 ORDER BY pg_catalog.ts_lexize(mapdict, parse.token) IS NULL, m.mapseqno
491 FROM pg_catalog.ts_parse(
492 (SELECT cfgparser FROM pg_catalog.pg_ts_config WHERE oid = $1 ), $2
494 pg_catalog.ts_token_type(
495 (SELECT cfgparser FROM pg_catalog.pg_ts_config WHERE oid = $1 )
497 WHERE tt.tokid = parse.tokid
499 LANGUAGE SQL STRICT STABLE;
501 COMMENT ON FUNCTION ts_debug(regconfig,text) IS
502 'debug function for text search configuration';
504 CREATE FUNCTION ts_debug(IN document text,
506 OUT description text,
508 OUT dictionaries regdictionary[],
509 OUT dictionary regdictionary,
511 RETURNS SETOF record AS
513 SELECT * FROM pg_catalog.ts_debug( pg_catalog.get_current_ts_config(), $1);
515 LANGUAGE SQL STRICT STABLE;
517 COMMENT ON FUNCTION ts_debug(text) IS
518 'debug function for current text search configuration';
521 -- Redeclare built-in functions that need default values attached to their
522 -- arguments. It's impractical to set those up directly in pg_proc.h because
523 -- of the complexity and platform-dependency of the expression tree
524 -- representation. (Note that internal functions still have to have entries
525 -- in pg_proc.h; we are merely causing their proargnames and proargdefaults
526 -- to get filled in.)
529 CREATE OR REPLACE FUNCTION
530 pg_start_backup(label text, fast boolean DEFAULT false)
531 RETURNS text STRICT VOLATILE LANGUAGE internal AS 'pg_start_backup';