1/* 2 * PostgreSQL System Views 3 * 4 * Copyright (c) 1996-2017, PostgreSQL Global Development Group 5 * 6 * src/backend/catalog/system_views.sql 7 * 8 * Note: this file is read in single-user -j mode, which means that the 9 * command terminator is semicolon-newline-newline; whenever the backend 10 * sees that, it stops and executes what it's got. If you write a lot of 11 * statements without empty lines between, they'll all get quoted to you 12 * in any error message about one of them, so don't do that. Also, you 13 * cannot write a semicolon immediately followed by an empty line in a 14 * string literal (including a function body!) or a multiline comment. 15 */ 16 17CREATE VIEW pg_roles AS 18 SELECT 19 rolname, 20 rolsuper, 21 rolinherit, 22 rolcreaterole, 23 rolcreatedb, 24 rolcanlogin, 25 rolreplication, 26 rolconnlimit, 27 '********'::text as rolpassword, 28 rolvaliduntil, 29 rolbypassrls, 30 setconfig as rolconfig, 31 pg_authid.oid 32 FROM pg_authid LEFT JOIN pg_db_role_setting s 33 ON (pg_authid.oid = setrole AND setdatabase = 0); 34 35CREATE VIEW pg_shadow AS 36 SELECT 37 rolname AS usename, 38 pg_authid.oid AS usesysid, 39 rolcreatedb AS usecreatedb, 40 rolsuper AS usesuper, 41 rolreplication AS userepl, 42 rolbypassrls AS usebypassrls, 43 rolpassword AS passwd, 44 rolvaliduntil::abstime AS valuntil, 45 setconfig AS useconfig 46 FROM pg_authid LEFT JOIN pg_db_role_setting s 47 ON (pg_authid.oid = setrole AND setdatabase = 0) 48 WHERE rolcanlogin; 49 50REVOKE ALL on pg_shadow FROM public; 51 52CREATE VIEW pg_group AS 53 SELECT 54 rolname AS groname, 55 oid AS grosysid, 56 ARRAY(SELECT member FROM pg_auth_members WHERE roleid = oid) AS grolist 57 FROM pg_authid 58 WHERE NOT rolcanlogin; 59 60CREATE VIEW pg_user AS 61 SELECT 62 usename, 63 usesysid, 64 usecreatedb, 65 usesuper, 66 userepl, 67 usebypassrls, 68 '********'::text as passwd, 69 valuntil, 70 useconfig 71 FROM pg_shadow; 72 73CREATE VIEW pg_policies AS 74 SELECT 75 N.nspname AS schemaname, 76 C.relname AS tablename, 77 pol.polname AS policyname, 78 CASE 79 WHEN pol.polpermissive THEN 80 'PERMISSIVE' 81 ELSE 82 'RESTRICTIVE' 83 END AS permissive, 84 CASE 85 WHEN pol.polroles = '{0}' THEN 86 string_to_array('public', '') 87 ELSE 88 ARRAY 89 ( 90 SELECT rolname 91 FROM pg_catalog.pg_authid 92 WHERE oid = ANY (pol.polroles) ORDER BY 1 93 ) 94 END AS roles, 95 CASE pol.polcmd 96 WHEN 'r' THEN 'SELECT' 97 WHEN 'a' THEN 'INSERT' 98 WHEN 'w' THEN 'UPDATE' 99 WHEN 'd' THEN 'DELETE' 100 WHEN '*' THEN 'ALL' 101 END AS cmd, 102 pg_catalog.pg_get_expr(pol.polqual, pol.polrelid) AS qual, 103 pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid) AS with_check 104 FROM pg_catalog.pg_policy pol 105 JOIN pg_catalog.pg_class C ON (C.oid = pol.polrelid) 106 LEFT JOIN pg_catalog.pg_namespace N ON (N.oid = C.relnamespace); 107 108CREATE VIEW pg_rules AS 109 SELECT 110 N.nspname AS schemaname, 111 C.relname AS tablename, 112 R.rulename AS rulename, 113 pg_get_ruledef(R.oid) AS definition 114 FROM (pg_rewrite R JOIN pg_class C ON (C.oid = R.ev_class)) 115 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 116 WHERE R.rulename != '_RETURN'; 117 118CREATE VIEW pg_views AS 119 SELECT 120 N.nspname AS schemaname, 121 C.relname AS viewname, 122 pg_get_userbyid(C.relowner) AS viewowner, 123 pg_get_viewdef(C.oid) AS definition 124 FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 125 WHERE C.relkind = 'v'; 126 127CREATE VIEW pg_tables AS 128 SELECT 129 N.nspname AS schemaname, 130 C.relname AS tablename, 131 pg_get_userbyid(C.relowner) AS tableowner, 132 T.spcname AS tablespace, 133 C.relhasindex AS hasindexes, 134 C.relhasrules AS hasrules, 135 C.relhastriggers AS hastriggers, 136 C.relrowsecurity AS rowsecurity 137 FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 138 LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace) 139 WHERE C.relkind IN ('r', 'p'); 140 141CREATE VIEW pg_matviews AS 142 SELECT 143 N.nspname AS schemaname, 144 C.relname AS matviewname, 145 pg_get_userbyid(C.relowner) AS matviewowner, 146 T.spcname AS tablespace, 147 C.relhasindex AS hasindexes, 148 C.relispopulated AS ispopulated, 149 pg_get_viewdef(C.oid) AS definition 150 FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 151 LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace) 152 WHERE C.relkind = 'm'; 153 154CREATE VIEW pg_indexes AS 155 SELECT 156 N.nspname AS schemaname, 157 C.relname AS tablename, 158 I.relname AS indexname, 159 T.spcname AS tablespace, 160 pg_get_indexdef(I.oid) AS indexdef 161 FROM pg_index X JOIN pg_class C ON (C.oid = X.indrelid) 162 JOIN pg_class I ON (I.oid = X.indexrelid) 163 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 164 LEFT JOIN pg_tablespace T ON (T.oid = I.reltablespace) 165 WHERE C.relkind IN ('r', 'm') AND I.relkind = 'i'; 166 167CREATE OR REPLACE VIEW pg_sequences AS 168 SELECT 169 N.nspname AS schemaname, 170 C.relname AS sequencename, 171 pg_get_userbyid(C.relowner) AS sequenceowner, 172 S.seqtypid::regtype AS data_type, 173 S.seqstart AS start_value, 174 S.seqmin AS min_value, 175 S.seqmax AS max_value, 176 S.seqincrement AS increment_by, 177 S.seqcycle AS cycle, 178 S.seqcache AS cache_size, 179 CASE 180 WHEN has_sequence_privilege(C.oid, 'SELECT,USAGE'::text) 181 THEN pg_sequence_last_value(C.oid) 182 ELSE NULL 183 END AS last_value 184 FROM pg_sequence S JOIN pg_class C ON (C.oid = S.seqrelid) 185 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 186 WHERE NOT pg_is_other_temp_schema(N.oid) 187 AND relkind = 'S'; 188 189CREATE VIEW pg_stats WITH (security_barrier) AS 190 SELECT 191 nspname AS schemaname, 192 relname AS tablename, 193 attname AS attname, 194 stainherit AS inherited, 195 stanullfrac AS null_frac, 196 stawidth AS avg_width, 197 stadistinct AS n_distinct, 198 CASE 199 WHEN stakind1 = 1 THEN stavalues1 200 WHEN stakind2 = 1 THEN stavalues2 201 WHEN stakind3 = 1 THEN stavalues3 202 WHEN stakind4 = 1 THEN stavalues4 203 WHEN stakind5 = 1 THEN stavalues5 204 END AS most_common_vals, 205 CASE 206 WHEN stakind1 = 1 THEN stanumbers1 207 WHEN stakind2 = 1 THEN stanumbers2 208 WHEN stakind3 = 1 THEN stanumbers3 209 WHEN stakind4 = 1 THEN stanumbers4 210 WHEN stakind5 = 1 THEN stanumbers5 211 END AS most_common_freqs, 212 CASE 213 WHEN stakind1 = 2 THEN stavalues1 214 WHEN stakind2 = 2 THEN stavalues2 215 WHEN stakind3 = 2 THEN stavalues3 216 WHEN stakind4 = 2 THEN stavalues4 217 WHEN stakind5 = 2 THEN stavalues5 218 END AS histogram_bounds, 219 CASE 220 WHEN stakind1 = 3 THEN stanumbers1[1] 221 WHEN stakind2 = 3 THEN stanumbers2[1] 222 WHEN stakind3 = 3 THEN stanumbers3[1] 223 WHEN stakind4 = 3 THEN stanumbers4[1] 224 WHEN stakind5 = 3 THEN stanumbers5[1] 225 END AS correlation, 226 CASE 227 WHEN stakind1 = 4 THEN stavalues1 228 WHEN stakind2 = 4 THEN stavalues2 229 WHEN stakind3 = 4 THEN stavalues3 230 WHEN stakind4 = 4 THEN stavalues4 231 WHEN stakind5 = 4 THEN stavalues5 232 END AS most_common_elems, 233 CASE 234 WHEN stakind1 = 4 THEN stanumbers1 235 WHEN stakind2 = 4 THEN stanumbers2 236 WHEN stakind3 = 4 THEN stanumbers3 237 WHEN stakind4 = 4 THEN stanumbers4 238 WHEN stakind5 = 4 THEN stanumbers5 239 END AS most_common_elem_freqs, 240 CASE 241 WHEN stakind1 = 5 THEN stanumbers1 242 WHEN stakind2 = 5 THEN stanumbers2 243 WHEN stakind3 = 5 THEN stanumbers3 244 WHEN stakind4 = 5 THEN stanumbers4 245 WHEN stakind5 = 5 THEN stanumbers5 246 END AS elem_count_histogram 247 FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid) 248 JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum) 249 LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace) 250 WHERE NOT attisdropped 251 AND has_column_privilege(c.oid, a.attnum, 'select') 252 AND (c.relrowsecurity = false OR NOT row_security_active(c.oid)); 253 254REVOKE ALL on pg_statistic FROM public; 255 256CREATE VIEW pg_publication_tables AS 257 SELECT 258 P.pubname AS pubname, 259 N.nspname AS schemaname, 260 C.relname AS tablename 261 FROM pg_publication P, pg_class C 262 JOIN pg_namespace N ON (N.oid = C.relnamespace) 263 WHERE C.oid IN (SELECT relid FROM pg_get_publication_tables(P.pubname)); 264 265CREATE VIEW pg_locks AS 266 SELECT * FROM pg_lock_status() AS L; 267 268CREATE VIEW pg_cursors AS 269 SELECT * FROM pg_cursor() AS C; 270 271CREATE VIEW pg_available_extensions AS 272 SELECT E.name, E.default_version, X.extversion AS installed_version, 273 E.comment 274 FROM pg_available_extensions() AS E 275 LEFT JOIN pg_extension AS X ON E.name = X.extname; 276 277CREATE VIEW pg_available_extension_versions AS 278 SELECT E.name, E.version, (X.extname IS NOT NULL) AS installed, 279 E.superuser, E.relocatable, E.schema, E.requires, E.comment 280 FROM pg_available_extension_versions() AS E 281 LEFT JOIN pg_extension AS X 282 ON E.name = X.extname AND E.version = X.extversion; 283 284CREATE VIEW pg_prepared_xacts AS 285 SELECT P.transaction, P.gid, P.prepared, 286 U.rolname AS owner, D.datname AS database 287 FROM pg_prepared_xact() AS P 288 LEFT JOIN pg_authid U ON P.ownerid = U.oid 289 LEFT JOIN pg_database D ON P.dbid = D.oid; 290 291CREATE VIEW pg_prepared_statements AS 292 SELECT * FROM pg_prepared_statement() AS P; 293 294CREATE VIEW pg_seclabels AS 295SELECT 296 l.objoid, l.classoid, l.objsubid, 297 CASE WHEN rel.relkind IN ('r', 'p') THEN 'table'::text 298 WHEN rel.relkind = 'v' THEN 'view'::text 299 WHEN rel.relkind = 'm' THEN 'materialized view'::text 300 WHEN rel.relkind = 'S' THEN 'sequence'::text 301 WHEN rel.relkind = 'f' THEN 'foreign table'::text END AS objtype, 302 rel.relnamespace AS objnamespace, 303 CASE WHEN pg_table_is_visible(rel.oid) 304 THEN quote_ident(rel.relname) 305 ELSE quote_ident(nsp.nspname) || '.' || quote_ident(rel.relname) 306 END AS objname, 307 l.provider, l.label 308FROM 309 pg_seclabel l 310 JOIN pg_class rel ON l.classoid = rel.tableoid AND l.objoid = rel.oid 311 JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid 312WHERE 313 l.objsubid = 0 314UNION ALL 315SELECT 316 l.objoid, l.classoid, l.objsubid, 317 'column'::text AS objtype, 318 rel.relnamespace AS objnamespace, 319 CASE WHEN pg_table_is_visible(rel.oid) 320 THEN quote_ident(rel.relname) 321 ELSE quote_ident(nsp.nspname) || '.' || quote_ident(rel.relname) 322 END || '.' || att.attname AS objname, 323 l.provider, l.label 324FROM 325 pg_seclabel l 326 JOIN pg_class rel ON l.classoid = rel.tableoid AND l.objoid = rel.oid 327 JOIN pg_attribute att 328 ON rel.oid = att.attrelid AND l.objsubid = att.attnum 329 JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid 330WHERE 331 l.objsubid != 0 332UNION ALL 333SELECT 334 l.objoid, l.classoid, l.objsubid, 335 CASE WHEN pro.proisagg = true THEN 'aggregate'::text 336 WHEN pro.proisagg = false THEN 'function'::text 337 END AS objtype, 338 pro.pronamespace AS objnamespace, 339 CASE WHEN pg_function_is_visible(pro.oid) 340 THEN quote_ident(pro.proname) 341 ELSE quote_ident(nsp.nspname) || '.' || quote_ident(pro.proname) 342 END || '(' || pg_catalog.pg_get_function_arguments(pro.oid) || ')' AS objname, 343 l.provider, l.label 344FROM 345 pg_seclabel l 346 JOIN pg_proc pro ON l.classoid = pro.tableoid AND l.objoid = pro.oid 347 JOIN pg_namespace nsp ON pro.pronamespace = nsp.oid 348WHERE 349 l.objsubid = 0 350UNION ALL 351SELECT 352 l.objoid, l.classoid, l.objsubid, 353 CASE WHEN typ.typtype = 'd' THEN 'domain'::text 354 ELSE 'type'::text END AS objtype, 355 typ.typnamespace AS objnamespace, 356 CASE WHEN pg_type_is_visible(typ.oid) 357 THEN quote_ident(typ.typname) 358 ELSE quote_ident(nsp.nspname) || '.' || quote_ident(typ.typname) 359 END AS objname, 360 l.provider, l.label 361FROM 362 pg_seclabel l 363 JOIN pg_type typ ON l.classoid = typ.tableoid AND l.objoid = typ.oid 364 JOIN pg_namespace nsp ON typ.typnamespace = nsp.oid 365WHERE 366 l.objsubid = 0 367UNION ALL 368SELECT 369 l.objoid, l.classoid, l.objsubid, 370 'large object'::text AS objtype, 371 NULL::oid AS objnamespace, 372 l.objoid::text AS objname, 373 l.provider, l.label 374FROM 375 pg_seclabel l 376 JOIN pg_largeobject_metadata lom ON l.objoid = lom.oid 377WHERE 378 l.classoid = 'pg_catalog.pg_largeobject'::regclass AND l.objsubid = 0 379UNION ALL 380SELECT 381 l.objoid, l.classoid, l.objsubid, 382 'language'::text AS objtype, 383 NULL::oid AS objnamespace, 384 quote_ident(lan.lanname) AS objname, 385 l.provider, l.label 386FROM 387 pg_seclabel l 388 JOIN pg_language lan ON l.classoid = lan.tableoid AND l.objoid = lan.oid 389WHERE 390 l.objsubid = 0 391UNION ALL 392SELECT 393 l.objoid, l.classoid, l.objsubid, 394 'schema'::text AS objtype, 395 nsp.oid AS objnamespace, 396 quote_ident(nsp.nspname) AS objname, 397 l.provider, l.label 398FROM 399 pg_seclabel l 400 JOIN pg_namespace nsp ON l.classoid = nsp.tableoid AND l.objoid = nsp.oid 401WHERE 402 l.objsubid = 0 403UNION ALL 404SELECT 405 l.objoid, l.classoid, l.objsubid, 406 'event trigger'::text AS objtype, 407 NULL::oid AS objnamespace, 408 quote_ident(evt.evtname) AS objname, 409 l.provider, l.label 410FROM 411 pg_seclabel l 412 JOIN pg_event_trigger evt ON l.classoid = evt.tableoid 413 AND l.objoid = evt.oid 414WHERE 415 l.objsubid = 0 416UNION ALL 417SELECT 418 l.objoid, l.classoid, l.objsubid, 419 'publication'::text AS objtype, 420 NULL::oid AS objnamespace, 421 quote_ident(p.pubname) AS objname, 422 l.provider, l.label 423FROM 424 pg_seclabel l 425 JOIN pg_publication p ON l.classoid = p.tableoid AND l.objoid = p.oid 426WHERE 427 l.objsubid = 0 428UNION ALL 429SELECT 430 l.objoid, l.classoid, 0::int4 AS objsubid, 431 'subscription'::text AS objtype, 432 NULL::oid AS objnamespace, 433 quote_ident(s.subname) AS objname, 434 l.provider, l.label 435FROM 436 pg_shseclabel l 437 JOIN pg_subscription s ON l.classoid = s.tableoid AND l.objoid = s.oid 438UNION ALL 439SELECT 440 l.objoid, l.classoid, 0::int4 AS objsubid, 441 'database'::text AS objtype, 442 NULL::oid AS objnamespace, 443 quote_ident(dat.datname) AS objname, 444 l.provider, l.label 445FROM 446 pg_shseclabel l 447 JOIN pg_database dat ON l.classoid = dat.tableoid AND l.objoid = dat.oid 448UNION ALL 449SELECT 450 l.objoid, l.classoid, 0::int4 AS objsubid, 451 'tablespace'::text AS objtype, 452 NULL::oid AS objnamespace, 453 quote_ident(spc.spcname) AS objname, 454 l.provider, l.label 455FROM 456 pg_shseclabel l 457 JOIN pg_tablespace spc ON l.classoid = spc.tableoid AND l.objoid = spc.oid 458UNION ALL 459SELECT 460 l.objoid, l.classoid, 0::int4 AS objsubid, 461 'role'::text AS objtype, 462 NULL::oid AS objnamespace, 463 quote_ident(rol.rolname) AS objname, 464 l.provider, l.label 465FROM 466 pg_shseclabel l 467 JOIN pg_authid rol ON l.classoid = rol.tableoid AND l.objoid = rol.oid; 468 469CREATE VIEW pg_settings AS 470 SELECT * FROM pg_show_all_settings() AS A; 471 472CREATE RULE pg_settings_u AS 473 ON UPDATE TO pg_settings 474 WHERE new.name = old.name DO 475 SELECT set_config(old.name, new.setting, 'f'); 476 477CREATE RULE pg_settings_n AS 478 ON UPDATE TO pg_settings 479 DO INSTEAD NOTHING; 480 481GRANT SELECT, UPDATE ON pg_settings TO PUBLIC; 482 483CREATE VIEW pg_file_settings AS 484 SELECT * FROM pg_show_all_file_settings() AS A; 485 486REVOKE ALL on pg_file_settings FROM PUBLIC; 487REVOKE EXECUTE ON FUNCTION pg_show_all_file_settings() FROM PUBLIC; 488 489CREATE VIEW pg_hba_file_rules AS 490 SELECT * FROM pg_hba_file_rules() AS A; 491 492REVOKE ALL on pg_hba_file_rules FROM PUBLIC; 493REVOKE EXECUTE ON FUNCTION pg_hba_file_rules() FROM PUBLIC; 494 495CREATE VIEW pg_timezone_abbrevs AS 496 SELECT * FROM pg_timezone_abbrevs(); 497 498CREATE VIEW pg_timezone_names AS 499 SELECT * FROM pg_timezone_names(); 500 501CREATE VIEW pg_config AS 502 SELECT * FROM pg_config(); 503 504REVOKE ALL on pg_config FROM PUBLIC; 505REVOKE EXECUTE ON FUNCTION pg_config() FROM PUBLIC; 506 507-- Statistics views 508 509CREATE VIEW pg_stat_all_tables AS 510 SELECT 511 C.oid AS relid, 512 N.nspname AS schemaname, 513 C.relname AS relname, 514 pg_stat_get_numscans(C.oid) AS seq_scan, 515 pg_stat_get_tuples_returned(C.oid) AS seq_tup_read, 516 sum(pg_stat_get_numscans(I.indexrelid))::bigint AS idx_scan, 517 sum(pg_stat_get_tuples_fetched(I.indexrelid))::bigint + 518 pg_stat_get_tuples_fetched(C.oid) AS idx_tup_fetch, 519 pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins, 520 pg_stat_get_tuples_updated(C.oid) AS n_tup_upd, 521 pg_stat_get_tuples_deleted(C.oid) AS n_tup_del, 522 pg_stat_get_tuples_hot_updated(C.oid) AS n_tup_hot_upd, 523 pg_stat_get_live_tuples(C.oid) AS n_live_tup, 524 pg_stat_get_dead_tuples(C.oid) AS n_dead_tup, 525 pg_stat_get_mod_since_analyze(C.oid) AS n_mod_since_analyze, 526 pg_stat_get_last_vacuum_time(C.oid) as last_vacuum, 527 pg_stat_get_last_autovacuum_time(C.oid) as last_autovacuum, 528 pg_stat_get_last_analyze_time(C.oid) as last_analyze, 529 pg_stat_get_last_autoanalyze_time(C.oid) as last_autoanalyze, 530 pg_stat_get_vacuum_count(C.oid) AS vacuum_count, 531 pg_stat_get_autovacuum_count(C.oid) AS autovacuum_count, 532 pg_stat_get_analyze_count(C.oid) AS analyze_count, 533 pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count 534 FROM pg_class C LEFT JOIN 535 pg_index I ON C.oid = I.indrelid 536 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 537 WHERE C.relkind IN ('r', 't', 'm') 538 GROUP BY C.oid, N.nspname, C.relname; 539 540CREATE VIEW pg_stat_xact_all_tables AS 541 SELECT 542 C.oid AS relid, 543 N.nspname AS schemaname, 544 C.relname AS relname, 545 pg_stat_get_xact_numscans(C.oid) AS seq_scan, 546 pg_stat_get_xact_tuples_returned(C.oid) AS seq_tup_read, 547 sum(pg_stat_get_xact_numscans(I.indexrelid))::bigint AS idx_scan, 548 sum(pg_stat_get_xact_tuples_fetched(I.indexrelid))::bigint + 549 pg_stat_get_xact_tuples_fetched(C.oid) AS idx_tup_fetch, 550 pg_stat_get_xact_tuples_inserted(C.oid) AS n_tup_ins, 551 pg_stat_get_xact_tuples_updated(C.oid) AS n_tup_upd, 552 pg_stat_get_xact_tuples_deleted(C.oid) AS n_tup_del, 553 pg_stat_get_xact_tuples_hot_updated(C.oid) AS n_tup_hot_upd 554 FROM pg_class C LEFT JOIN 555 pg_index I ON C.oid = I.indrelid 556 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 557 WHERE C.relkind IN ('r', 't', 'm') 558 GROUP BY C.oid, N.nspname, C.relname; 559 560CREATE VIEW pg_stat_sys_tables AS 561 SELECT * FROM pg_stat_all_tables 562 WHERE schemaname IN ('pg_catalog', 'information_schema') OR 563 schemaname ~ '^pg_toast'; 564 565CREATE VIEW pg_stat_xact_sys_tables AS 566 SELECT * FROM pg_stat_xact_all_tables 567 WHERE schemaname IN ('pg_catalog', 'information_schema') OR 568 schemaname ~ '^pg_toast'; 569 570CREATE VIEW pg_stat_user_tables AS 571 SELECT * FROM pg_stat_all_tables 572 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND 573 schemaname !~ '^pg_toast'; 574 575CREATE VIEW pg_stat_xact_user_tables AS 576 SELECT * FROM pg_stat_xact_all_tables 577 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND 578 schemaname !~ '^pg_toast'; 579 580CREATE VIEW pg_statio_all_tables AS 581 SELECT 582 C.oid AS relid, 583 N.nspname AS schemaname, 584 C.relname AS relname, 585 pg_stat_get_blocks_fetched(C.oid) - 586 pg_stat_get_blocks_hit(C.oid) AS heap_blks_read, 587 pg_stat_get_blocks_hit(C.oid) AS heap_blks_hit, 588 sum(pg_stat_get_blocks_fetched(I.indexrelid) - 589 pg_stat_get_blocks_hit(I.indexrelid))::bigint AS idx_blks_read, 590 sum(pg_stat_get_blocks_hit(I.indexrelid))::bigint AS idx_blks_hit, 591 pg_stat_get_blocks_fetched(T.oid) - 592 pg_stat_get_blocks_hit(T.oid) AS toast_blks_read, 593 pg_stat_get_blocks_hit(T.oid) AS toast_blks_hit, 594 sum(pg_stat_get_blocks_fetched(X.indexrelid) - 595 pg_stat_get_blocks_hit(X.indexrelid))::bigint AS tidx_blks_read, 596 sum(pg_stat_get_blocks_hit(X.indexrelid))::bigint AS tidx_blks_hit 597 FROM pg_class C LEFT JOIN 598 pg_index I ON C.oid = I.indrelid LEFT JOIN 599 pg_class T ON C.reltoastrelid = T.oid LEFT JOIN 600 pg_index X ON T.oid = X.indrelid 601 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 602 WHERE C.relkind IN ('r', 't', 'm') 603 GROUP BY C.oid, N.nspname, C.relname, T.oid, X.indrelid; 604 605CREATE VIEW pg_statio_sys_tables AS 606 SELECT * FROM pg_statio_all_tables 607 WHERE schemaname IN ('pg_catalog', 'information_schema') OR 608 schemaname ~ '^pg_toast'; 609 610CREATE VIEW pg_statio_user_tables AS 611 SELECT * FROM pg_statio_all_tables 612 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND 613 schemaname !~ '^pg_toast'; 614 615CREATE VIEW pg_stat_all_indexes AS 616 SELECT 617 C.oid AS relid, 618 I.oid AS indexrelid, 619 N.nspname AS schemaname, 620 C.relname AS relname, 621 I.relname AS indexrelname, 622 pg_stat_get_numscans(I.oid) AS idx_scan, 623 pg_stat_get_tuples_returned(I.oid) AS idx_tup_read, 624 pg_stat_get_tuples_fetched(I.oid) AS idx_tup_fetch 625 FROM pg_class C JOIN 626 pg_index X ON C.oid = X.indrelid JOIN 627 pg_class I ON I.oid = X.indexrelid 628 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 629 WHERE C.relkind IN ('r', 't', 'm'); 630 631CREATE VIEW pg_stat_sys_indexes AS 632 SELECT * FROM pg_stat_all_indexes 633 WHERE schemaname IN ('pg_catalog', 'information_schema') OR 634 schemaname ~ '^pg_toast'; 635 636CREATE VIEW pg_stat_user_indexes AS 637 SELECT * FROM pg_stat_all_indexes 638 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND 639 schemaname !~ '^pg_toast'; 640 641CREATE VIEW pg_statio_all_indexes AS 642 SELECT 643 C.oid AS relid, 644 I.oid AS indexrelid, 645 N.nspname AS schemaname, 646 C.relname AS relname, 647 I.relname AS indexrelname, 648 pg_stat_get_blocks_fetched(I.oid) - 649 pg_stat_get_blocks_hit(I.oid) AS idx_blks_read, 650 pg_stat_get_blocks_hit(I.oid) AS idx_blks_hit 651 FROM pg_class C JOIN 652 pg_index X ON C.oid = X.indrelid JOIN 653 pg_class I ON I.oid = X.indexrelid 654 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 655 WHERE C.relkind IN ('r', 't', 'm'); 656 657CREATE VIEW pg_statio_sys_indexes AS 658 SELECT * FROM pg_statio_all_indexes 659 WHERE schemaname IN ('pg_catalog', 'information_schema') OR 660 schemaname ~ '^pg_toast'; 661 662CREATE VIEW pg_statio_user_indexes AS 663 SELECT * FROM pg_statio_all_indexes 664 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND 665 schemaname !~ '^pg_toast'; 666 667CREATE VIEW pg_statio_all_sequences AS 668 SELECT 669 C.oid AS relid, 670 N.nspname AS schemaname, 671 C.relname AS relname, 672 pg_stat_get_blocks_fetched(C.oid) - 673 pg_stat_get_blocks_hit(C.oid) AS blks_read, 674 pg_stat_get_blocks_hit(C.oid) AS blks_hit 675 FROM pg_class C 676 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 677 WHERE C.relkind = 'S'; 678 679CREATE VIEW pg_statio_sys_sequences AS 680 SELECT * FROM pg_statio_all_sequences 681 WHERE schemaname IN ('pg_catalog', 'information_schema') OR 682 schemaname ~ '^pg_toast'; 683 684CREATE VIEW pg_statio_user_sequences AS 685 SELECT * FROM pg_statio_all_sequences 686 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND 687 schemaname !~ '^pg_toast'; 688 689CREATE VIEW pg_stat_activity AS 690 SELECT 691 S.datid AS datid, 692 D.datname AS datname, 693 S.pid, 694 S.usesysid, 695 U.rolname AS usename, 696 S.application_name, 697 S.client_addr, 698 S.client_hostname, 699 S.client_port, 700 S.backend_start, 701 S.xact_start, 702 S.query_start, 703 S.state_change, 704 S.wait_event_type, 705 S.wait_event, 706 S.state, 707 S.backend_xid, 708 s.backend_xmin, 709 S.query, 710 S.backend_type 711 FROM pg_stat_get_activity(NULL) AS S 712 LEFT JOIN pg_database AS D ON (S.datid = D.oid) 713 LEFT JOIN pg_authid AS U ON (S.usesysid = U.oid); 714 715CREATE VIEW pg_stat_replication AS 716 SELECT 717 S.pid, 718 S.usesysid, 719 U.rolname AS usename, 720 S.application_name, 721 S.client_addr, 722 S.client_hostname, 723 S.client_port, 724 S.backend_start, 725 S.backend_xmin, 726 W.state, 727 W.sent_lsn, 728 W.write_lsn, 729 W.flush_lsn, 730 W.replay_lsn, 731 W.write_lag, 732 W.flush_lag, 733 W.replay_lag, 734 W.sync_priority, 735 W.sync_state 736 FROM pg_stat_get_activity(NULL) AS S 737 JOIN pg_stat_get_wal_senders() AS W ON (S.pid = W.pid) 738 LEFT JOIN pg_authid AS U ON (S.usesysid = U.oid); 739 740CREATE VIEW pg_stat_wal_receiver AS 741 SELECT 742 s.pid, 743 s.status, 744 s.receive_start_lsn, 745 s.receive_start_tli, 746 s.received_lsn, 747 s.received_tli, 748 s.last_msg_send_time, 749 s.last_msg_receipt_time, 750 s.latest_end_lsn, 751 s.latest_end_time, 752 s.slot_name, 753 s.conninfo 754 FROM pg_stat_get_wal_receiver() s 755 WHERE s.pid IS NOT NULL; 756 757CREATE VIEW pg_stat_subscription AS 758 SELECT 759 su.oid AS subid, 760 su.subname, 761 st.pid, 762 st.relid, 763 st.received_lsn, 764 st.last_msg_send_time, 765 st.last_msg_receipt_time, 766 st.latest_end_lsn, 767 st.latest_end_time 768 FROM pg_subscription su 769 LEFT JOIN pg_stat_get_subscription(NULL) st 770 ON (st.subid = su.oid); 771 772CREATE VIEW pg_stat_ssl AS 773 SELECT 774 S.pid, 775 S.ssl, 776 S.sslversion AS version, 777 S.sslcipher AS cipher, 778 S.sslbits AS bits, 779 S.sslcompression AS compression, 780 S.sslclientdn AS clientdn 781 FROM pg_stat_get_activity(NULL) AS S; 782 783CREATE VIEW pg_replication_slots AS 784 SELECT 785 L.slot_name, 786 L.plugin, 787 L.slot_type, 788 L.datoid, 789 D.datname AS database, 790 L.temporary, 791 L.active, 792 L.active_pid, 793 L.xmin, 794 L.catalog_xmin, 795 L.restart_lsn, 796 L.confirmed_flush_lsn 797 FROM pg_get_replication_slots() AS L 798 LEFT JOIN pg_database D ON (L.datoid = D.oid); 799 800CREATE VIEW pg_stat_database AS 801 SELECT 802 D.oid AS datid, 803 D.datname AS datname, 804 pg_stat_get_db_numbackends(D.oid) AS numbackends, 805 pg_stat_get_db_xact_commit(D.oid) AS xact_commit, 806 pg_stat_get_db_xact_rollback(D.oid) AS xact_rollback, 807 pg_stat_get_db_blocks_fetched(D.oid) - 808 pg_stat_get_db_blocks_hit(D.oid) AS blks_read, 809 pg_stat_get_db_blocks_hit(D.oid) AS blks_hit, 810 pg_stat_get_db_tuples_returned(D.oid) AS tup_returned, 811 pg_stat_get_db_tuples_fetched(D.oid) AS tup_fetched, 812 pg_stat_get_db_tuples_inserted(D.oid) AS tup_inserted, 813 pg_stat_get_db_tuples_updated(D.oid) AS tup_updated, 814 pg_stat_get_db_tuples_deleted(D.oid) AS tup_deleted, 815 pg_stat_get_db_conflict_all(D.oid) AS conflicts, 816 pg_stat_get_db_temp_files(D.oid) AS temp_files, 817 pg_stat_get_db_temp_bytes(D.oid) AS temp_bytes, 818 pg_stat_get_db_deadlocks(D.oid) AS deadlocks, 819 pg_stat_get_db_blk_read_time(D.oid) AS blk_read_time, 820 pg_stat_get_db_blk_write_time(D.oid) AS blk_write_time, 821 pg_stat_get_db_stat_reset_time(D.oid) AS stats_reset 822 FROM pg_database D; 823 824CREATE VIEW pg_stat_database_conflicts AS 825 SELECT 826 D.oid AS datid, 827 D.datname AS datname, 828 pg_stat_get_db_conflict_tablespace(D.oid) AS confl_tablespace, 829 pg_stat_get_db_conflict_lock(D.oid) AS confl_lock, 830 pg_stat_get_db_conflict_snapshot(D.oid) AS confl_snapshot, 831 pg_stat_get_db_conflict_bufferpin(D.oid) AS confl_bufferpin, 832 pg_stat_get_db_conflict_startup_deadlock(D.oid) AS confl_deadlock 833 FROM pg_database D; 834 835CREATE VIEW pg_stat_user_functions AS 836 SELECT 837 P.oid AS funcid, 838 N.nspname AS schemaname, 839 P.proname AS funcname, 840 pg_stat_get_function_calls(P.oid) AS calls, 841 pg_stat_get_function_total_time(P.oid) AS total_time, 842 pg_stat_get_function_self_time(P.oid) AS self_time 843 FROM pg_proc P LEFT JOIN pg_namespace N ON (N.oid = P.pronamespace) 844 WHERE P.prolang != 12 -- fast check to eliminate built-in functions 845 AND pg_stat_get_function_calls(P.oid) IS NOT NULL; 846 847CREATE VIEW pg_stat_xact_user_functions AS 848 SELECT 849 P.oid AS funcid, 850 N.nspname AS schemaname, 851 P.proname AS funcname, 852 pg_stat_get_xact_function_calls(P.oid) AS calls, 853 pg_stat_get_xact_function_total_time(P.oid) AS total_time, 854 pg_stat_get_xact_function_self_time(P.oid) AS self_time 855 FROM pg_proc P LEFT JOIN pg_namespace N ON (N.oid = P.pronamespace) 856 WHERE P.prolang != 12 -- fast check to eliminate built-in functions 857 AND pg_stat_get_xact_function_calls(P.oid) IS NOT NULL; 858 859CREATE VIEW pg_stat_archiver AS 860 SELECT 861 s.archived_count, 862 s.last_archived_wal, 863 s.last_archived_time, 864 s.failed_count, 865 s.last_failed_wal, 866 s.last_failed_time, 867 s.stats_reset 868 FROM pg_stat_get_archiver() s; 869 870CREATE VIEW pg_stat_bgwriter AS 871 SELECT 872 pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed, 873 pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req, 874 pg_stat_get_checkpoint_write_time() AS checkpoint_write_time, 875 pg_stat_get_checkpoint_sync_time() AS checkpoint_sync_time, 876 pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint, 877 pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean, 878 pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean, 879 pg_stat_get_buf_written_backend() AS buffers_backend, 880 pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync, 881 pg_stat_get_buf_alloc() AS buffers_alloc, 882 pg_stat_get_bgwriter_stat_reset_time() AS stats_reset; 883 884CREATE VIEW pg_stat_progress_vacuum AS 885 SELECT 886 S.pid AS pid, S.datid AS datid, D.datname AS datname, 887 S.relid AS relid, 888 CASE S.param1 WHEN 0 THEN 'initializing' 889 WHEN 1 THEN 'scanning heap' 890 WHEN 2 THEN 'vacuuming indexes' 891 WHEN 3 THEN 'vacuuming heap' 892 WHEN 4 THEN 'cleaning up indexes' 893 WHEN 5 THEN 'truncating heap' 894 WHEN 6 THEN 'performing final cleanup' 895 END AS phase, 896 S.param2 AS heap_blks_total, S.param3 AS heap_blks_scanned, 897 S.param4 AS heap_blks_vacuumed, S.param5 AS index_vacuum_count, 898 S.param6 AS max_dead_tuples, S.param7 AS num_dead_tuples 899 FROM pg_stat_get_progress_info('VACUUM') AS S 900 LEFT JOIN pg_database D ON S.datid = D.oid; 901 902CREATE VIEW pg_user_mappings AS 903 SELECT 904 U.oid AS umid, 905 S.oid AS srvid, 906 S.srvname AS srvname, 907 U.umuser AS umuser, 908 CASE WHEN U.umuser = 0 THEN 909 'public' 910 ELSE 911 A.rolname 912 END AS usename, 913 CASE WHEN (U.umuser <> 0 AND A.rolname = current_user 914 AND (pg_has_role(S.srvowner, 'USAGE') 915 OR has_server_privilege(S.oid, 'USAGE'))) 916 OR (U.umuser = 0 AND pg_has_role(S.srvowner, 'USAGE')) 917 OR (SELECT rolsuper FROM pg_authid WHERE rolname = current_user) 918 THEN U.umoptions 919 ELSE NULL END AS umoptions 920 FROM pg_user_mapping U 921 JOIN pg_foreign_server S ON (U.umserver = S.oid) 922 LEFT JOIN pg_authid A ON (A.oid = U.umuser); 923 924REVOKE ALL on pg_user_mapping FROM public; 925 926CREATE VIEW pg_replication_origin_status AS 927 SELECT * 928 FROM pg_show_replication_origin_status(); 929 930REVOKE ALL ON pg_replication_origin_status FROM public; 931 932-- All columns of pg_subscription except subconninfo are readable. 933REVOKE ALL ON pg_subscription FROM public; 934GRANT SELECT (subdbid, subname, subowner, subenabled, subslotname, subpublications) 935 ON pg_subscription TO public; 936 937 938-- 939-- We have a few function definitions in here, too. 940-- At some point there might be enough to justify breaking them out into 941-- a separate "system_functions.sql" file. 942-- 943 944-- Tsearch debug function. Defined here because it'd be pretty unwieldy 945-- to put it into pg_proc.h 946 947CREATE FUNCTION ts_debug(IN config regconfig, IN document text, 948 OUT alias text, 949 OUT description text, 950 OUT token text, 951 OUT dictionaries regdictionary[], 952 OUT dictionary regdictionary, 953 OUT lexemes text[]) 954RETURNS SETOF record AS 955$$ 956SELECT 957 tt.alias AS alias, 958 tt.description AS description, 959 parse.token AS token, 960 ARRAY ( SELECT m.mapdict::pg_catalog.regdictionary 961 FROM pg_catalog.pg_ts_config_map AS m 962 WHERE m.mapcfg = $1 AND m.maptokentype = parse.tokid 963 ORDER BY m.mapseqno ) 964 AS dictionaries, 965 ( SELECT mapdict::pg_catalog.regdictionary 966 FROM pg_catalog.pg_ts_config_map AS m 967 WHERE m.mapcfg = $1 AND m.maptokentype = parse.tokid 968 ORDER BY pg_catalog.ts_lexize(mapdict, parse.token) IS NULL, m.mapseqno 969 LIMIT 1 970 ) AS dictionary, 971 ( SELECT pg_catalog.ts_lexize(mapdict, parse.token) 972 FROM pg_catalog.pg_ts_config_map AS m 973 WHERE m.mapcfg = $1 AND m.maptokentype = parse.tokid 974 ORDER BY pg_catalog.ts_lexize(mapdict, parse.token) IS NULL, m.mapseqno 975 LIMIT 1 976 ) AS lexemes 977FROM pg_catalog.ts_parse( 978 (SELECT cfgparser FROM pg_catalog.pg_ts_config WHERE oid = $1 ), $2 979 ) AS parse, 980 pg_catalog.ts_token_type( 981 (SELECT cfgparser FROM pg_catalog.pg_ts_config WHERE oid = $1 ) 982 ) AS tt 983WHERE tt.tokid = parse.tokid 984$$ 985LANGUAGE SQL STRICT STABLE PARALLEL SAFE; 986 987COMMENT ON FUNCTION ts_debug(regconfig,text) IS 988 'debug function for text search configuration'; 989 990CREATE FUNCTION ts_debug(IN document text, 991 OUT alias text, 992 OUT description text, 993 OUT token text, 994 OUT dictionaries regdictionary[], 995 OUT dictionary regdictionary, 996 OUT lexemes text[]) 997RETURNS SETOF record AS 998$$ 999 SELECT * FROM pg_catalog.ts_debug( pg_catalog.get_current_ts_config(), $1); 1000$$ 1001LANGUAGE SQL STRICT STABLE PARALLEL SAFE; 1002 1003COMMENT ON FUNCTION ts_debug(text) IS 1004 'debug function for current text search configuration'; 1005 1006-- 1007-- Redeclare built-in functions that need default values attached to their 1008-- arguments. It's impractical to set those up directly in pg_proc.h because 1009-- of the complexity and platform-dependency of the expression tree 1010-- representation. (Note that internal functions still have to have entries 1011-- in pg_proc.h; we are merely causing their proargnames and proargdefaults 1012-- to get filled in.) 1013-- 1014 1015CREATE OR REPLACE FUNCTION 1016 pg_start_backup(label text, fast boolean DEFAULT false, exclusive boolean DEFAULT true) 1017 RETURNS pg_lsn STRICT VOLATILE LANGUAGE internal AS 'pg_start_backup' 1018 PARALLEL RESTRICTED; 1019 1020CREATE OR REPLACE FUNCTION pg_stop_backup ( 1021 exclusive boolean, wait_for_archive boolean DEFAULT true, 1022 OUT lsn pg_lsn, OUT labelfile text, OUT spcmapfile text) 1023 RETURNS SETOF record STRICT VOLATILE LANGUAGE internal as 'pg_stop_backup_v2' 1024 PARALLEL RESTRICTED; 1025 1026-- legacy definition for compatibility with 9.3 1027CREATE OR REPLACE FUNCTION 1028 json_populate_record(base anyelement, from_json json, use_json_as_text boolean DEFAULT false) 1029 RETURNS anyelement LANGUAGE internal STABLE AS 'json_populate_record' PARALLEL SAFE; 1030 1031-- legacy definition for compatibility with 9.3 1032CREATE OR REPLACE FUNCTION 1033 json_populate_recordset(base anyelement, from_json json, use_json_as_text boolean DEFAULT false) 1034 RETURNS SETOF anyelement LANGUAGE internal STABLE ROWS 100 AS 'json_populate_recordset' PARALLEL SAFE; 1035 1036CREATE OR REPLACE FUNCTION pg_logical_slot_get_changes( 1037 IN slot_name name, IN upto_lsn pg_lsn, IN upto_nchanges int, VARIADIC options text[] DEFAULT '{}', 1038 OUT lsn pg_lsn, OUT xid xid, OUT data text) 1039RETURNS SETOF RECORD 1040LANGUAGE INTERNAL 1041VOLATILE ROWS 1000 COST 1000 1042AS 'pg_logical_slot_get_changes'; 1043 1044CREATE OR REPLACE FUNCTION pg_logical_slot_peek_changes( 1045 IN slot_name name, IN upto_lsn pg_lsn, IN upto_nchanges int, VARIADIC options text[] DEFAULT '{}', 1046 OUT lsn pg_lsn, OUT xid xid, OUT data text) 1047RETURNS SETOF RECORD 1048LANGUAGE INTERNAL 1049VOLATILE ROWS 1000 COST 1000 1050AS 'pg_logical_slot_peek_changes'; 1051 1052CREATE OR REPLACE FUNCTION pg_logical_slot_get_binary_changes( 1053 IN slot_name name, IN upto_lsn pg_lsn, IN upto_nchanges int, VARIADIC options text[] DEFAULT '{}', 1054 OUT lsn pg_lsn, OUT xid xid, OUT data bytea) 1055RETURNS SETOF RECORD 1056LANGUAGE INTERNAL 1057VOLATILE ROWS 1000 COST 1000 1058AS 'pg_logical_slot_get_binary_changes'; 1059 1060CREATE OR REPLACE FUNCTION pg_logical_slot_peek_binary_changes( 1061 IN slot_name name, IN upto_lsn pg_lsn, IN upto_nchanges int, VARIADIC options text[] DEFAULT '{}', 1062 OUT lsn pg_lsn, OUT xid xid, OUT data bytea) 1063RETURNS SETOF RECORD 1064LANGUAGE INTERNAL 1065VOLATILE ROWS 1000 COST 1000 1066AS 'pg_logical_slot_peek_binary_changes'; 1067 1068CREATE OR REPLACE FUNCTION pg_create_physical_replication_slot( 1069 IN slot_name name, IN immediately_reserve boolean DEFAULT false, 1070 IN temporary boolean DEFAULT false, 1071 OUT slot_name name, OUT lsn pg_lsn) 1072RETURNS RECORD 1073LANGUAGE INTERNAL 1074STRICT VOLATILE 1075AS 'pg_create_physical_replication_slot'; 1076 1077CREATE OR REPLACE FUNCTION pg_create_logical_replication_slot( 1078 IN slot_name name, IN plugin name, 1079 IN temporary boolean DEFAULT false, 1080 OUT slot_name text, OUT lsn pg_lsn) 1081RETURNS RECORD 1082LANGUAGE INTERNAL 1083STRICT VOLATILE 1084AS 'pg_create_logical_replication_slot'; 1085 1086CREATE OR REPLACE FUNCTION 1087 make_interval(years int4 DEFAULT 0, months int4 DEFAULT 0, weeks int4 DEFAULT 0, 1088 days int4 DEFAULT 0, hours int4 DEFAULT 0, mins int4 DEFAULT 0, 1089 secs double precision DEFAULT 0.0) 1090RETURNS interval 1091LANGUAGE INTERNAL 1092STRICT IMMUTABLE PARALLEL SAFE 1093AS 'make_interval'; 1094 1095CREATE OR REPLACE FUNCTION 1096 jsonb_set(jsonb_in jsonb, path text[] , replacement jsonb, 1097 create_if_missing boolean DEFAULT true) 1098RETURNS jsonb 1099LANGUAGE INTERNAL 1100STRICT IMMUTABLE PARALLEL SAFE 1101AS 'jsonb_set'; 1102 1103CREATE OR REPLACE FUNCTION 1104 parse_ident(str text, strict boolean DEFAULT true) 1105RETURNS text[] 1106LANGUAGE INTERNAL 1107STRICT IMMUTABLE PARALLEL SAFE 1108AS 'parse_ident'; 1109 1110CREATE OR REPLACE FUNCTION 1111 jsonb_insert(jsonb_in jsonb, path text[] , replacement jsonb, 1112 insert_after boolean DEFAULT false) 1113RETURNS jsonb 1114LANGUAGE INTERNAL 1115STRICT IMMUTABLE PARALLEL SAFE 1116AS 'jsonb_insert'; 1117 1118-- The default permissions for functions mean that anyone can execute them. 1119-- A number of functions shouldn't be executable by just anyone, but rather 1120-- than use explicit 'superuser()' checks in those functions, we use the GRANT 1121-- system to REVOKE access to those functions at initdb time. Administrators 1122-- can later change who can access these functions, or leave them as only 1123-- available to superuser / cluster owner, if they choose. 1124REVOKE EXECUTE ON FUNCTION pg_start_backup(text, boolean, boolean) FROM public; 1125REVOKE EXECUTE ON FUNCTION pg_stop_backup() FROM public; 1126REVOKE EXECUTE ON FUNCTION pg_stop_backup(boolean, boolean) FROM public; 1127REVOKE EXECUTE ON FUNCTION pg_create_restore_point(text) FROM public; 1128REVOKE EXECUTE ON FUNCTION pg_switch_wal() FROM public; 1129REVOKE EXECUTE ON FUNCTION pg_wal_replay_pause() FROM public; 1130REVOKE EXECUTE ON FUNCTION pg_wal_replay_resume() FROM public; 1131REVOKE EXECUTE ON FUNCTION pg_rotate_logfile() FROM public; 1132REVOKE EXECUTE ON FUNCTION pg_reload_conf() FROM public; 1133REVOKE EXECUTE ON FUNCTION pg_current_logfile() FROM public; 1134REVOKE EXECUTE ON FUNCTION pg_current_logfile(text) FROM public; 1135 1136REVOKE EXECUTE ON FUNCTION pg_stat_reset() FROM public; 1137REVOKE EXECUTE ON FUNCTION pg_stat_reset_shared(text) FROM public; 1138REVOKE EXECUTE ON FUNCTION pg_stat_reset_single_table_counters(oid) FROM public; 1139REVOKE EXECUTE ON FUNCTION pg_stat_reset_single_function_counters(oid) FROM public; 1140 1141REVOKE EXECUTE ON FUNCTION pg_ls_logdir() FROM public; 1142REVOKE EXECUTE ON FUNCTION pg_ls_waldir() FROM public; 1143GRANT EXECUTE ON FUNCTION pg_ls_logdir() TO pg_monitor; 1144GRANT EXECUTE ON FUNCTION pg_ls_waldir() TO pg_monitor; 1145 1146GRANT pg_read_all_settings TO pg_monitor; 1147GRANT pg_read_all_stats TO pg_monitor; 1148GRANT pg_stat_scan_tables TO pg_monitor; 1149