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

   1 /*
   2  * PostgreSQL System Views
   3  *
   4  * Copyright (c) 1996-2010, PostgreSQL Global Development Group
   5  *
   6  * $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.66 2010/04/26 14:22:37 momjian 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         setconfig as rolconfig,
  22         pg_authid.oid
  23     FROM pg_authid LEFT JOIN pg_db_role_setting s
  24     ON (pg_authid.oid = setrole AND setdatabase = 0);
  25
  26 CREATE VIEW pg_shadow AS
  27     SELECT
  28         rolname AS usename,
  29         pg_authid.oid AS usesysid,
  30         rolcreatedb AS usecreatedb,
  31         rolsuper AS usesuper,
  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)
  38     WHERE rolcanlogin;
  39
  40 REVOKE ALL on pg_shadow FROM public;
  41
  42 CREATE VIEW pg_group AS
  43     SELECT
  44         rolname AS groname,
  45         oid AS grosysid,
  46         ARRAY(SELECT member FROM pg_auth_members WHERE roleid = oid) AS grolist
  47     FROM pg_authid
  48     WHERE NOT rolcanlogin;
  49
  50 CREATE VIEW pg_user AS
  51     SELECT
  52         usename,
  53         usesysid,
  54         usecreatedb,
  55         usesuper,
  56         usecatupd,
  57         '********'::text as passwd,
  58         valuntil,
  59         useconfig
  60     FROM pg_shadow;
  61
  62 CREATE VIEW pg_rules AS
  63     SELECT
  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';
  71
  72 CREATE VIEW pg_views AS
  73     SELECT
  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';
  80
  81 CREATE VIEW pg_tables AS
  82     SELECT
  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';
  93
  94 CREATE VIEW pg_indexes AS
  95     SELECT
  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';
 106
 107 CREATE VIEW pg_stats AS
 108     SELECT
 109         nspname AS schemaname,
 110         relname AS tablename,
 111         attname AS attname,
 112         stainherit AS inherited,
 113         stanullfrac AS null_frac,
 114         stawidth AS avg_width,
 115         stadistinct AS n_distinct,
 116         CASE
 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,
 122         CASE
 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,
 128         CASE
 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,
 134         CASE
 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]
 139         END AS correlation
 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');
 144
 145 REVOKE ALL on pg_statistic FROM public;
 146
 147 CREATE VIEW pg_locks AS
 148     SELECT * FROM pg_lock_status() AS L;
 149
 150 CREATE VIEW pg_cursors AS
 151     SELECT * FROM pg_cursor() AS C;
 152
 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;
 159
 160 CREATE VIEW pg_prepared_statements AS
 161     SELECT * FROM pg_prepared_statement() AS P;
 162
 163 CREATE VIEW pg_settings AS
 164     SELECT * FROM pg_show_all_settings() AS A;
 165
 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');
 170
 171 CREATE RULE pg_settings_n AS
 172     ON UPDATE TO pg_settings
 173     DO INSTEAD NOTHING;
 174
 175 GRANT SELECT, UPDATE ON pg_settings TO PUBLIC;
 176
 177 CREATE VIEW pg_timezone_abbrevs AS
 178     SELECT * FROM pg_timezone_abbrevs();
 179
 180 CREATE VIEW pg_timezone_names AS
 181     SELECT * FROM pg_timezone_names();
 182
 183 -- Statistics views
 184
 185 CREATE VIEW pg_stat_all_tables AS
 186     SELECT
 187             C.oid AS relid,
 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;
 210
 211 CREATE VIEW pg_stat_sys_tables AS
 212     SELECT * FROM pg_stat_all_tables
 213     WHERE schemaname IN ('pg_catalog', 'information_schema') OR
 214           schemaname ~ '^pg_toast';
 215
 216 CREATE VIEW pg_stat_user_tables AS
 217     SELECT * FROM pg_stat_all_tables
 218     WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
 219           schemaname !~ '^pg_toast';
 220
 221 CREATE VIEW pg_statio_all_tables AS
 222     SELECT
 223             C.oid AS relid,
 224             N.nspname AS schemaname,
 225             C.relname AS relname,
 226             pg_stat_get_blocks_fetched(C.oid) -
 227                     pg_stat_get_blocks_hit(C.oid) AS heap_blks_read,
 228             pg_stat_get_blocks_hit(C.oid) AS heap_blks_hit,
 229             sum(pg_stat_get_blocks_fetched(I.indexrelid) -
 230                     pg_stat_get_blocks_hit(I.indexrelid))::bigint AS idx_blks_read,
 231             sum(pg_stat_get_blocks_hit(I.indexrelid))::bigint AS idx_blks_hit,
 232             pg_stat_get_blocks_fetched(T.oid) -
 233                     pg_stat_get_blocks_hit(T.oid) AS toast_blks_read,
 234             pg_stat_get_blocks_hit(T.oid) AS toast_blks_hit,
 235             pg_stat_get_blocks_fetched(X.oid) -
 236                     pg_stat_get_blocks_hit(X.oid) AS tidx_blks_read,
 237             pg_stat_get_blocks_hit(X.oid) AS tidx_blks_hit
 238     FROM pg_class C LEFT JOIN
 239             pg_index I ON C.oid = I.indrelid LEFT JOIN
 240             pg_class T ON C.reltoastrelid = T.oid LEFT JOIN
 241             pg_class X ON T.reltoastidxid = X.oid
 242             LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
 243     WHERE C.relkind IN ('r', 't')
 244     GROUP BY C.oid, N.nspname, C.relname, T.oid, X.oid;
 245
 246 CREATE VIEW pg_statio_sys_tables AS
 247     SELECT * FROM pg_statio_all_tables
 248     WHERE schemaname IN ('pg_catalog', 'information_schema') OR
 249           schemaname ~ '^pg_toast';
 250
 251 CREATE VIEW pg_statio_user_tables AS
 252     SELECT * FROM pg_statio_all_tables
 253     WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
 254           schemaname !~ '^pg_toast';
 255
 256 CREATE VIEW pg_stat_all_indexes AS
 257     SELECT
 258             C.oid AS relid,
 259             I.oid AS indexrelid,
 260             N.nspname AS schemaname,
 261             C.relname AS relname,
 262             I.relname AS indexrelname,
 263             pg_stat_get_numscans(I.oid) AS idx_scan,
 264             pg_stat_get_tuples_returned(I.oid) AS idx_tup_read,
 265             pg_stat_get_tuples_fetched(I.oid) AS idx_tup_fetch
 266     FROM pg_class C JOIN
 267             pg_index X ON C.oid = X.indrelid JOIN
 268             pg_class I ON I.oid = X.indexrelid
 269             LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
 270     WHERE C.relkind IN ('r', 't');
 271
 272 CREATE VIEW pg_stat_sys_indexes AS
 273     SELECT * FROM pg_stat_all_indexes
 274     WHERE schemaname IN ('pg_catalog', 'information_schema') OR
 275           schemaname ~ '^pg_toast';
 276
 277 CREATE VIEW pg_stat_user_indexes AS
 278     SELECT * FROM pg_stat_all_indexes
 279     WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
 280           schemaname !~ '^pg_toast';
 281
 282 CREATE VIEW pg_statio_all_indexes AS
 283     SELECT
 284             C.oid AS relid,
 285             I.oid AS indexrelid,
 286             N.nspname AS schemaname,
 287             C.relname AS relname,
 288             I.relname AS indexrelname,
 289             pg_stat_get_blocks_fetched(I.oid) -
 290                     pg_stat_get_blocks_hit(I.oid) AS idx_blks_read,
 291             pg_stat_get_blocks_hit(I.oid) AS idx_blks_hit
 292     FROM pg_class C JOIN
 293             pg_index X ON C.oid = X.indrelid JOIN
 294             pg_class I ON I.oid = X.indexrelid
 295             LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
 296     WHERE C.relkind IN ('r', 't');
 297
 298 CREATE VIEW pg_statio_sys_indexes AS
 299     SELECT * FROM pg_statio_all_indexes
 300     WHERE schemaname IN ('pg_catalog', 'information_schema') OR
 301           schemaname ~ '^pg_toast';
 302
 303 CREATE VIEW pg_statio_user_indexes AS
 304     SELECT * FROM pg_statio_all_indexes
 305     WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
 306           schemaname !~ '^pg_toast';
 307
 308 CREATE VIEW pg_statio_all_sequences AS
 309     SELECT
 310             C.oid AS relid,
 311             N.nspname AS schemaname,
 312             C.relname AS relname,
 313             pg_stat_get_blocks_fetched(C.oid) -
 314                     pg_stat_get_blocks_hit(C.oid) AS blks_read,
 315             pg_stat_get_blocks_hit(C.oid) AS blks_hit
 316     FROM pg_class C
 317             LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
 318     WHERE C.relkind = 'S';
 319
 320 CREATE VIEW pg_statio_sys_sequences AS
 321     SELECT * FROM pg_statio_all_sequences
 322     WHERE schemaname IN ('pg_catalog', 'information_schema') OR
 323           schemaname ~ '^pg_toast';
 324
 325 CREATE VIEW pg_statio_user_sequences AS
 326     SELECT * FROM pg_statio_all_sequences
 327     WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
 328           schemaname !~ '^pg_toast';
 329
 330 CREATE VIEW pg_stat_activity AS
 331     SELECT
 332             S.datid AS datid,
 333             D.datname AS datname,
 334             S.procpid,
 335             S.usesysid,
 336             U.rolname AS usename,
 337             S.application_name,
 338             S.client_addr,
 339             S.client_port,
 340             S.backend_start,
 341             S.xact_start,
 342             S.query_start,
 343             S.waiting,
 344             S.current_query
 345     FROM pg_database D, pg_stat_get_activity(NULL) AS S, pg_authid U
 346     WHERE S.datid = D.oid AND
 347             S.usesysid = U.oid;
 348
 349 CREATE VIEW pg_stat_database AS
 350     SELECT
 351             D.oid AS datid,
 352             D.datname AS datname,
 353             pg_stat_get_db_numbackends(D.oid) AS numbackends,
 354             pg_stat_get_db_xact_commit(D.oid) AS xact_commit,
 355             pg_stat_get_db_xact_rollback(D.oid) AS xact_rollback,
 356             pg_stat_get_db_blocks_fetched(D.oid) -
 357                     pg_stat_get_db_blocks_hit(D.oid) AS blks_read,
 358             pg_stat_get_db_blocks_hit(D.oid) AS blks_hit,
 359             pg_stat_get_db_tuples_returned(D.oid) AS tup_returned,
 360             pg_stat_get_db_tuples_fetched(D.oid) AS tup_fetched,
 361             pg_stat_get_db_tuples_inserted(D.oid) AS tup_inserted,
 362             pg_stat_get_db_tuples_updated(D.oid) AS tup_updated,
 363             pg_stat_get_db_tuples_deleted(D.oid) AS tup_deleted
 364     FROM pg_database D;
 365
 366 CREATE VIEW pg_stat_user_functions AS
 367     SELECT
 368             P.oid AS funcid,
 369             N.nspname AS schemaname,
 370             P.proname AS funcname,
 371             pg_stat_get_function_calls(P.oid) AS calls,
 372             pg_stat_get_function_time(P.oid) / 1000 AS total_time,
 373             pg_stat_get_function_self_time(P.oid) / 1000 AS self_time
 374     FROM pg_proc P LEFT JOIN pg_namespace N ON (N.oid = P.pronamespace)
 375     WHERE P.prolang != 12  -- fast check to eliminate built-in functions
 376           AND pg_stat_get_function_calls(P.oid) IS NOT NULL;
 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         pg_stat_get_buf_written_backend() AS buffers_backend,
 386         pg_stat_get_buf_alloc() AS buffers_alloc;
 387
 388 CREATE VIEW pg_user_mappings AS
 389     SELECT
 390         U.oid       AS umid,
 391         S.oid       AS srvid,
 392         S.srvname   AS srvname,
 393         U.umuser    AS umuser,
 394         CASE WHEN U.umuser = 0 THEN
 395             'public'
 396         ELSE
 397             A.rolname
 398         END AS usename,
 399         CASE WHEN pg_has_role(S.srvowner, 'USAGE') OR has_server_privilege(S.oid, 'USAGE') THEN
 400             U.umoptions
 401         ELSE
 402             NULL
 403         END AS umoptions
 404     FROM pg_user_mapping U
 405          LEFT JOIN pg_authid A ON (A.oid = U.umuser) JOIN
 406         pg_foreign_server S ON (U.umserver = S.oid);
 407
 408 REVOKE ALL on pg_user_mapping FROM public;
 409
 410 --
 411 -- We have a few function definitions in here, too.
 412 -- At some point there might be enough to justify breaking them out into
 413 -- a separate "system_functions.sql" file.
 414 --
 415
 416 -- Tsearch debug function.  Defined here because it'd be pretty unwieldy
 417 -- to put it into pg_proc.h
 418
 419 CREATE FUNCTION ts_debug(IN config regconfig, IN document text,
 420     OUT alias text,
 421     OUT description text,
 422     OUT token text,
 423     OUT dictionaries regdictionary[],
 424     OUT dictionary regdictionary,
 425     OUT lexemes text[])
 426 RETURNS SETOF record AS
 427 $$
 428 SELECT
 429     tt.alias AS alias,
 430     tt.description AS description,
 431     parse.token AS token,
 432     ARRAY ( SELECT m.mapdict::pg_catalog.regdictionary
 433             FROM pg_catalog.pg_ts_config_map AS m
 434             WHERE m.mapcfg = $1 AND m.maptokentype = parse.tokid
 435             ORDER BY m.mapseqno )
 436     AS dictionaries,
 437     ( SELECT mapdict::pg_catalog.regdictionary
 438       FROM pg_catalog.pg_ts_config_map AS m
 439       WHERE m.mapcfg = $1 AND m.maptokentype = parse.tokid
 440       ORDER BY pg_catalog.ts_lexize(mapdict, parse.token) IS NULL, m.mapseqno
 441       LIMIT 1
 442     ) AS dictionary,
 443     ( SELECT pg_catalog.ts_lexize(mapdict, parse.token)
 444       FROM pg_catalog.pg_ts_config_map AS m
 445       WHERE m.mapcfg = $1 AND m.maptokentype = parse.tokid
 446       ORDER BY pg_catalog.ts_lexize(mapdict, parse.token) IS NULL, m.mapseqno
 447       LIMIT 1
 448     ) AS lexemes
 449 FROM pg_catalog.ts_parse(
 450         (SELECT cfgparser FROM pg_catalog.pg_ts_config WHERE oid = $1 ), $2
 451     ) AS parse,
 452      pg_catalog.ts_token_type(
 453         (SELECT cfgparser FROM pg_catalog.pg_ts_config WHERE oid = $1 )
 454     ) AS tt
 455 WHERE tt.tokid = parse.tokid
 456 $$
 457 LANGUAGE SQL STRICT STABLE;
 458
 459 COMMENT ON FUNCTION ts_debug(regconfig,text) IS
 460     'debug function for text search configuration';
 461
 462 CREATE FUNCTION ts_debug(IN document text,
 463     OUT alias text,
 464     OUT description text,
 465     OUT token text,
 466     OUT dictionaries regdictionary[],
 467     OUT dictionary regdictionary,
 468     OUT lexemes text[])
 469 RETURNS SETOF record AS
 470 $$
 471     SELECT * FROM pg_catalog.ts_debug( pg_catalog.get_current_ts_config(), $1);
 472 $$
 473 LANGUAGE SQL STRICT STABLE;
 474
 475 COMMENT ON FUNCTION ts_debug(text) IS
 476     'debug function for current text search configuration';
 477
 478 --
 479 -- Redeclare built-in functions that need default values attached to their
 480 -- arguments.  It's impractical to set those up directly in pg_proc.h because
 481 -- of the complexity and platform-dependency of the expression tree
 482 -- representation.  (Note that internal functions still have to have entries
 483 -- in pg_proc.h; we are merely causing their proargnames and proargdefaults
 484 -- to get filled in.)
 485 --
 486
 487 CREATE OR REPLACE FUNCTION
 488   pg_start_backup(label text, fast boolean DEFAULT false)
 489   RETURNS text STRICT VOLATILE LANGUAGE internal AS 'pg_start_backup';