granicus.if.org Git - postgresql/blob - src/backend/catalog/system_views.sql

   1 /*
   2  * PostgreSQL System Views
   3  *
   4  * Copyright (c) 1996-2007, PostgreSQL Global Development Group
   5  *
   6  * $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.42 2007/09/05 18:10:47 tgl Exp $
   7  */
   8
   9 CREATE VIEW pg_roles AS
  10     SELECT
  11         rolname,
  12         rolsuper,
  13         rolinherit,
  14         rolcreaterole,
  15         rolcreatedb,
  16         rolcatupdate,
  17         rolcanlogin,
  18         rolconnlimit,
  19         '********'::text as rolpassword,
  20         rolvaliduntil,
  21         rolconfig,
  22         oid
  23     FROM pg_authid;
  24
  25 CREATE VIEW pg_shadow AS
  26     SELECT
  27         rolname AS usename,
  28         oid AS usesysid,
  29         rolcreatedb AS usecreatedb,
  30         rolsuper AS usesuper,
  31         rolcatupdate AS usecatupd,
  32         rolpassword AS passwd,
  33         rolvaliduntil::abstime AS valuntil,
  34         rolconfig AS useconfig
  35     FROM pg_authid
  36     WHERE rolcanlogin;
  37
  38 REVOKE ALL on pg_shadow FROM public;
  39
  40 CREATE VIEW pg_group AS
  41     SELECT
  42         rolname AS groname,
  43         oid AS grosysid,
  44         ARRAY(SELECT member FROM pg_auth_members WHERE roleid = oid) AS grolist
  45     FROM pg_authid
  46     WHERE NOT rolcanlogin;
  47
  48 CREATE VIEW pg_user AS
  49     SELECT
  50         usename,
  51         usesysid,
  52         usecreatedb,
  53         usesuper,
  54         usecatupd,
  55         '********'::text as passwd,
  56         valuntil,
  57         useconfig
  58     FROM pg_shadow;
  59
  60 CREATE VIEW pg_rules AS
  61     SELECT
  62         N.nspname AS schemaname,
  63         C.relname AS tablename,
  64         R.rulename AS rulename,
  65         pg_get_ruledef(R.oid) AS definition
  66     FROM (pg_rewrite R JOIN pg_class C ON (C.oid = R.ev_class))
  67         LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  68     WHERE R.rulename != '_RETURN';
  69
  70 CREATE VIEW pg_views AS
  71     SELECT
  72         N.nspname AS schemaname,
  73         C.relname AS viewname,
  74         pg_get_userbyid(C.relowner) AS viewowner,
  75         pg_get_viewdef(C.oid) AS definition
  76     FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  77     WHERE C.relkind = 'v';
  78
  79 CREATE VIEW pg_tables AS
  80     SELECT
  81         N.nspname AS schemaname,
  82         C.relname AS tablename,
  83         pg_get_userbyid(C.relowner) AS tableowner,
  84         T.spcname AS tablespace,
  85         C.relhasindex AS hasindexes,
  86         C.relhasrules AS hasrules,
  87         (C.reltriggers > 0) AS hastriggers
  88     FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  89          LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace)
  90     WHERE C.relkind = 'r';
  91
  92 CREATE VIEW pg_indexes AS
  93     SELECT
  94         N.nspname AS schemaname,
  95         C.relname AS tablename,
  96         I.relname AS indexname,
  97         T.spcname AS tablespace,
  98         pg_get_indexdef(I.oid) AS indexdef
  99     FROM pg_index X JOIN pg_class C ON (C.oid = X.indrelid)
 100          JOIN pg_class I ON (I.oid = X.indexrelid)
 101          LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
 102          LEFT JOIN pg_tablespace T ON (T.oid = I.reltablespace)
 103     WHERE C.relkind = 'r' AND I.relkind = 'i';
 104
 105 CREATE VIEW pg_stats AS
 106     SELECT
 107         nspname AS schemaname,
 108         relname AS tablename,
 109         attname AS attname,
 110         stanullfrac AS null_frac,
 111         stawidth AS avg_width,
 112         stadistinct AS n_distinct,
 113         CASE 1
 114             WHEN stakind1 THEN stavalues1
 115             WHEN stakind2 THEN stavalues2
 116             WHEN stakind3 THEN stavalues3
 117             WHEN stakind4 THEN stavalues4
 118         END AS most_common_vals,
 119         CASE 1
 120             WHEN stakind1 THEN stanumbers1
 121             WHEN stakind2 THEN stanumbers2
 122             WHEN stakind3 THEN stanumbers3
 123             WHEN stakind4 THEN stanumbers4
 124         END AS most_common_freqs,
 125         CASE 2
 126             WHEN stakind1 THEN stavalues1
 127             WHEN stakind2 THEN stavalues2
 128             WHEN stakind3 THEN stavalues3
 129             WHEN stakind4 THEN stavalues4
 130         END AS histogram_bounds,
 131         CASE 3
 132             WHEN stakind1 THEN stanumbers1[1]
 133             WHEN stakind2 THEN stanumbers2[1]
 134             WHEN stakind3 THEN stanumbers3[1]
 135             WHEN stakind4 THEN stanumbers4[1]
 136         END AS correlation
 137     FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid)
 138          JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum)
 139          LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
 140     WHERE has_table_privilege(c.oid, 'select');
 141
 142 REVOKE ALL on pg_statistic FROM public;
 143
 144 CREATE VIEW pg_locks AS
 145     SELECT *
 146     FROM pg_lock_status() AS L
 147     (locktype text, database oid, relation oid, page int4, tuple int2,
 148      virtualxid text, transactionid xid, classid oid, objid oid, objsubid int2,
 149      virtualtransaction text, pid int4, mode text, granted boolean);
 150
 151 CREATE VIEW pg_cursors AS
 152     SELECT C.name, C.statement, C.is_holdable, C.is_binary,
 153            C.is_scrollable, C.creation_time
 154     FROM pg_cursor() AS C
 155          (name text, statement text, is_holdable boolean, is_binary boolean,
 156           is_scrollable boolean, creation_time timestamptz);
 157
 158 CREATE VIEW pg_prepared_xacts AS
 159     SELECT P.transaction, P.gid, P.prepared,
 160            U.rolname AS owner, D.datname AS database
 161     FROM pg_prepared_xact() AS P
 162     (transaction xid, gid text, prepared timestamptz, ownerid oid, dbid oid)
 163          LEFT JOIN pg_authid U ON P.ownerid = U.oid
 164          LEFT JOIN pg_database D ON P.dbid = D.oid;
 165
 166 CREATE VIEW pg_prepared_statements AS
 167     SELECT P.name, P.statement, P.prepare_time, P.parameter_types, P.from_sql
 168     FROM pg_prepared_statement() AS P
 169     (name text, statement text, prepare_time timestamptz,
 170      parameter_types regtype[], from_sql boolean);
 171
 172 CREATE VIEW pg_settings AS
 173     SELECT *
 174     FROM pg_show_all_settings() AS A
 175     (name text, setting text, unit text, category text, short_desc text, extra_desc text,
 176      context text, vartype text, source text, min_val text, max_val text);
 177
 178 CREATE RULE pg_settings_u AS
 179     ON UPDATE TO pg_settings
 180     WHERE new.name = old.name DO
 181     SELECT set_config(old.name, new.setting, 'f');
 182
 183 CREATE RULE pg_settings_n AS
 184     ON UPDATE TO pg_settings
 185     DO INSTEAD NOTHING;
 186
 187 GRANT SELECT, UPDATE ON pg_settings TO PUBLIC;
 188
 189 CREATE VIEW pg_timezone_abbrevs AS
 190     SELECT * FROM pg_timezone_abbrevs();
 191
 192 CREATE VIEW pg_timezone_names AS
 193     SELECT * FROM pg_timezone_names();
 194
 195 -- Statistics views
 196
 197 CREATE VIEW pg_stat_all_tables AS
 198     SELECT
 199             C.oid AS relid,
 200             N.nspname AS schemaname,
 201             C.relname AS relname,
 202             pg_stat_get_numscans(C.oid) AS seq_scan,
 203             pg_stat_get_tuples_returned(C.oid) AS seq_tup_read,
 204             sum(pg_stat_get_numscans(I.indexrelid))::bigint AS idx_scan,
 205             sum(pg_stat_get_tuples_fetched(I.indexrelid))::bigint +
 206             pg_stat_get_tuples_fetched(C.oid) AS idx_tup_fetch,
 207             pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins,
 208             pg_stat_get_tuples_updated(C.oid) AS n_tup_upd,
 209             pg_stat_get_tuples_deleted(C.oid) AS n_tup_del,
 210             pg_stat_get_live_tuples(C.oid) AS n_live_tup,
 211             pg_stat_get_dead_tuples(C.oid) AS n_dead_tup,
 212             pg_stat_get_last_vacuum_time(C.oid) as last_vacuum,
 213             pg_stat_get_last_autovacuum_time(C.oid) as last_autovacuum,
 214             pg_stat_get_last_analyze_time(C.oid) as last_analyze,
 215             pg_stat_get_last_autoanalyze_time(C.oid) as last_autoanalyze
 216     FROM pg_class C LEFT JOIN
 217          pg_index I ON C.oid = I.indrelid
 218          LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
 219     WHERE C.relkind IN ('r', 't')
 220     GROUP BY C.oid, N.nspname, C.relname;
 221
 222 CREATE VIEW pg_stat_sys_tables AS
 223     SELECT * FROM pg_stat_all_tables
 224     WHERE schemaname IN ('pg_catalog', 'information_schema') OR
 225           schemaname ~ '^pg_toast';
 226
 227 CREATE VIEW pg_stat_user_tables AS
 228     SELECT * FROM pg_stat_all_tables
 229     WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
 230           schemaname !~ '^pg_toast';
 231
 232 CREATE VIEW pg_statio_all_tables AS
 233     SELECT
 234             C.oid AS relid,
 235             N.nspname AS schemaname,
 236             C.relname AS relname,
 237             pg_stat_get_blocks_fetched(C.oid) -
 238                     pg_stat_get_blocks_hit(C.oid) AS heap_blks_read,
 239             pg_stat_get_blocks_hit(C.oid) AS heap_blks_hit,
 240             sum(pg_stat_get_blocks_fetched(I.indexrelid) -
 241                     pg_stat_get_blocks_hit(I.indexrelid))::bigint AS idx_blks_read,
 242             sum(pg_stat_get_blocks_hit(I.indexrelid))::bigint AS idx_blks_hit,
 243             pg_stat_get_blocks_fetched(T.oid) -
 244                     pg_stat_get_blocks_hit(T.oid) AS toast_blks_read,
 245             pg_stat_get_blocks_hit(T.oid) AS toast_blks_hit,
 246             pg_stat_get_blocks_fetched(X.oid) -
 247                     pg_stat_get_blocks_hit(X.oid) AS tidx_blks_read,
 248             pg_stat_get_blocks_hit(X.oid) AS tidx_blks_hit
 249     FROM pg_class C LEFT JOIN
 250             pg_index I ON C.oid = I.indrelid LEFT JOIN
 251             pg_class T ON C.reltoastrelid = T.oid LEFT JOIN
 252             pg_class X ON T.reltoastidxid = X.oid
 253             LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
 254     WHERE C.relkind IN ('r', 't')
 255     GROUP BY C.oid, N.nspname, C.relname, T.oid, X.oid;
 256
 257 CREATE VIEW pg_statio_sys_tables AS
 258     SELECT * FROM pg_statio_all_tables
 259     WHERE schemaname IN ('pg_catalog', 'information_schema') OR
 260           schemaname ~ '^pg_toast';
 261
 262 CREATE VIEW pg_statio_user_tables AS
 263     SELECT * FROM pg_statio_all_tables
 264     WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
 265           schemaname !~ '^pg_toast';
 266
 267 CREATE VIEW pg_stat_all_indexes AS
 268     SELECT
 269             C.oid AS relid,
 270             I.oid AS indexrelid,
 271             N.nspname AS schemaname,
 272             C.relname AS relname,
 273             I.relname AS indexrelname,
 274             pg_stat_get_numscans(I.oid) AS idx_scan,
 275             pg_stat_get_tuples_returned(I.oid) AS idx_tup_read,
 276             pg_stat_get_tuples_fetched(I.oid) AS idx_tup_fetch
 277     FROM pg_class C JOIN
 278             pg_index X ON C.oid = X.indrelid JOIN
 279             pg_class I ON I.oid = X.indexrelid
 280             LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
 281     WHERE C.relkind IN ('r', 't');
 282
 283 CREATE VIEW pg_stat_sys_indexes AS
 284     SELECT * FROM pg_stat_all_indexes
 285     WHERE schemaname IN ('pg_catalog', 'information_schema') OR
 286           schemaname ~ '^pg_toast';
 287
 288 CREATE VIEW pg_stat_user_indexes AS
 289     SELECT * FROM pg_stat_all_indexes
 290     WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
 291           schemaname !~ '^pg_toast';
 292
 293 CREATE VIEW pg_statio_all_indexes AS
 294     SELECT
 295             C.oid AS relid,
 296             I.oid AS indexrelid,
 297             N.nspname AS schemaname,
 298             C.relname AS relname,
 299             I.relname AS indexrelname,
 300             pg_stat_get_blocks_fetched(I.oid) -
 301                     pg_stat_get_blocks_hit(I.oid) AS idx_blks_read,
 302             pg_stat_get_blocks_hit(I.oid) AS idx_blks_hit
 303     FROM pg_class C JOIN
 304             pg_index X ON C.oid = X.indrelid JOIN
 305             pg_class I ON I.oid = X.indexrelid
 306             LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
 307     WHERE C.relkind IN ('r', 't');
 308
 309 CREATE VIEW pg_statio_sys_indexes AS
 310     SELECT * FROM pg_statio_all_indexes
 311     WHERE schemaname IN ('pg_catalog', 'information_schema') OR
 312           schemaname ~ '^pg_toast';
 313
 314 CREATE VIEW pg_statio_user_indexes AS
 315     SELECT * FROM pg_statio_all_indexes
 316     WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
 317           schemaname !~ '^pg_toast';
 318
 319 CREATE VIEW pg_statio_all_sequences AS
 320     SELECT
 321             C.oid AS relid,
 322             N.nspname AS schemaname,
 323             C.relname AS relname,
 324             pg_stat_get_blocks_fetched(C.oid) -
 325                     pg_stat_get_blocks_hit(C.oid) AS blks_read,
 326             pg_stat_get_blocks_hit(C.oid) AS blks_hit
 327     FROM pg_class C
 328             LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
 329     WHERE C.relkind = 'S';
 330
 331 CREATE VIEW pg_statio_sys_sequences AS
 332     SELECT * FROM pg_statio_all_sequences
 333     WHERE schemaname IN ('pg_catalog', 'information_schema') OR
 334           schemaname ~ '^pg_toast';
 335
 336 CREATE VIEW pg_statio_user_sequences AS
 337     SELECT * FROM pg_statio_all_sequences
 338     WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
 339           schemaname !~ '^pg_toast';
 340
 341 CREATE VIEW pg_stat_activity AS
 342     SELECT
 343             D.oid AS datid,
 344             D.datname AS datname,
 345             pg_stat_get_backend_pid(S.backendid) AS procpid,
 346             pg_stat_get_backend_userid(S.backendid) AS usesysid,
 347             U.rolname AS usename,
 348             pg_stat_get_backend_activity(S.backendid) AS current_query,
 349             pg_stat_get_backend_waiting(S.backendid) AS waiting,
 350             pg_stat_get_backend_txn_start(S.backendid) AS txn_start,
 351             pg_stat_get_backend_activity_start(S.backendid) AS query_start,
 352             pg_stat_get_backend_start(S.backendid) AS backend_start,
 353             pg_stat_get_backend_client_addr(S.backendid) AS client_addr,
 354             pg_stat_get_backend_client_port(S.backendid) AS client_port
 355     FROM pg_database D,
 356             (SELECT pg_stat_get_backend_idset() AS backendid) AS S,
 357             pg_authid U
 358     WHERE pg_stat_get_backend_dbid(S.backendid) = D.oid AND
 359             pg_stat_get_backend_userid(S.backendid) = U.oid;
 360
 361 CREATE VIEW pg_stat_database AS
 362     SELECT
 363             D.oid AS datid,
 364             D.datname AS datname,
 365             pg_stat_get_db_numbackends(D.oid) AS numbackends,
 366             pg_stat_get_db_xact_commit(D.oid) AS xact_commit,
 367             pg_stat_get_db_xact_rollback(D.oid) AS xact_rollback,
 368             pg_stat_get_db_blocks_fetched(D.oid) -
 369                     pg_stat_get_db_blocks_hit(D.oid) AS blks_read,
 370             pg_stat_get_db_blocks_hit(D.oid) AS blks_hit,
 371             pg_stat_get_db_tuples_returned(D.oid) AS tup_returned,
 372             pg_stat_get_db_tuples_fetched(D.oid) AS tup_fetched,
 373             pg_stat_get_db_tuples_inserted(D.oid) AS tup_inserted,
 374             pg_stat_get_db_tuples_updated(D.oid) AS tup_updated,
 375             pg_stat_get_db_tuples_deleted(D.oid) AS tup_deleted
 376     FROM pg_database D;
 377
 378 CREATE VIEW pg_stat_bgwriter AS
 379     SELECT
 380         pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed,
 381         pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req,
 382         pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint,
 383         pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean,
 384         pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean;
 385
 386 -- Tsearch debug function. Defined here because it'd be pretty unwieldy
 387 -- to put it into pg_proc.h
 388
 389 CREATE TYPE ts_debug AS (
 390     "Alias" text,
 391     "Description" text,
 392     "Token" text,
 393     "Dictionaries" regdictionary[],
 394     "Lexized token" text
 395 );
 396
 397 COMMENT ON TYPE ts_debug IS 'type returned from ts_debug() function';
 398
 399 CREATE FUNCTION ts_debug(regconfig, text)
 400 RETURNS SETOF ts_debug AS
 401 $$
 402 SELECT
 403     tt.alias AS "Alias",
 404     tt.description AS "Description",
 405     parse.token AS "Token",
 406     ARRAY ( SELECT m.mapdict::pg_catalog.regdictionary
 407             FROM pg_catalog.pg_ts_config_map AS m
 408             WHERE m.mapcfg = $1 AND m.maptokentype = parse.tokid
 409             ORDER BY m.mapseqno )
 410     AS "Dictionaries",
 411     (
 412         SELECT
 413             dl.mapdict::pg_catalog.regdictionary  || ': ' || dl.lex::pg_catalog.text
 414         FROM
 415             ( SELECT mapdict, pg_catalog.ts_lexize(mapdict, parse.token) AS lex
 416               FROM pg_catalog.pg_ts_config_map AS m
 417               WHERE m.mapcfg = $1 AND m.maptokentype = parse.tokid
 418               ORDER BY m.mapseqno ) dl
 419         WHERE dl.lex IS NOT NULL
 420         LIMIT 1
 421     ) AS "Lexized token"
 422 FROM pg_catalog.ts_parse(
 423         (SELECT cfgparser FROM pg_catalog.pg_ts_config WHERE oid = $1 ), $2
 424     ) AS parse,
 425      pg_catalog.ts_token_type(
 426         (SELECT cfgparser FROM pg_catalog.pg_ts_config WHERE oid = $1 )
 427     ) AS tt
 428 WHERE tt.tokid = parse.tokid
 429 $$
 430 LANGUAGE SQL STRICT STABLE;
 431
 432 COMMENT ON FUNCTION ts_debug(regconfig,text) IS
 433     'debug function for text search configuration';
 434
 435 CREATE FUNCTION ts_debug(text)
 436 RETURNS SETOF ts_debug AS
 437 $$
 438     SELECT * FROM pg_catalog.ts_debug( pg_catalog.get_current_ts_config(), $1);
 439 $$
 440 LANGUAGE SQL STRICT STABLE;
 441
 442 COMMENT ON FUNCTION ts_debug(text) IS
 443     'debug function for current text search configuration';