| blob | 987026c8358c65aa536be67647023eea468cdc3c |
1 /*
2 * PostgreSQL System Views
3 *
4 * Copyright (c) 1996-2011, PostgreSQL Global Development Group
5 *
6 * src/backend/catalog/system_views.sql
7 */
9 CREATE VIEW pg_roles AS
10 SELECT
11 rolname,
12 rolsuper,
13 rolinherit,
14 rolcreaterole,
15 rolcreatedb,
16 rolcatupdate,
17 rolcanlogin,
18 rolreplication,
19 rolconnlimit,
20 '********'::text as rolpassword,
21 rolvaliduntil,
22 setconfig as rolconfig,
23 pg_authid.oid
24 FROM pg_authid LEFT JOIN pg_db_role_setting s
25 ON (pg_authid.oid = setrole AND setdatabase = 0);
27 CREATE VIEW pg_shadow AS
28 SELECT
29 rolname AS usename,
30 pg_authid.oid AS usesysid,
31 rolcreatedb AS usecreatedb,
32 rolsuper AS usesuper,
33 rolcatupdate AS usecatupd,
34 rolreplication AS userepl,
35 rolpassword AS passwd,
36 rolvaliduntil::abstime AS valuntil,
37 setconfig AS useconfig
38 FROM pg_authid LEFT JOIN pg_db_role_setting s
39 ON (pg_authid.oid = setrole AND setdatabase = 0)
40 WHERE rolcanlogin;
42 REVOKE ALL on pg_shadow FROM public;
44 CREATE VIEW pg_group AS
45 SELECT
46 rolname AS groname,
47 oid AS grosysid,
48 ARRAY(SELECT member FROM pg_auth_members WHERE roleid = oid) AS grolist
49 FROM pg_authid
50 WHERE NOT rolcanlogin;
52 CREATE VIEW pg_user AS
53 SELECT
54 usename,
55 usesysid,
56 usecreatedb,
57 usesuper,
58 usecatupd,
59 userepl,
60 '********'::text as passwd,
61 valuntil,
62 useconfig
63 FROM pg_shadow;
65 CREATE VIEW pg_rules AS
66 SELECT
67 N.nspname AS schemaname,
68 C.relname AS tablename,
69 R.rulename AS rulename,
70 pg_get_ruledef(R.oid) AS definition
71 FROM (pg_rewrite R JOIN pg_class C ON (C.oid = R.ev_class))
72 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
73 WHERE R.rulename != '_RETURN';
75 CREATE VIEW pg_views AS
76 SELECT
77 N.nspname AS schemaname,
78 C.relname AS viewname,
79 pg_get_userbyid(C.relowner) AS viewowner,
80 pg_get_viewdef(C.oid) AS definition
81 FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
82 WHERE C.relkind = 'v';
84 CREATE VIEW pg_tables AS
85 SELECT
86 N.nspname AS schemaname,
87 C.relname AS tablename,
88 pg_get_userbyid(C.relowner) AS tableowner,
89 T.spcname AS tablespace,
90 C.relhasindex AS hasindexes,
91 C.relhasrules AS hasrules,
92 C.relhastriggers AS hastriggers
93 FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
94 LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace)
95 WHERE C.relkind = 'r';
97 CREATE VIEW pg_indexes AS
98 SELECT
99 N.nspname AS schemaname,
100 C.relname AS tablename,
101 I.relname AS indexname,
102 T.spcname AS tablespace,
103 pg_get_indexdef(I.oid) AS indexdef
104 FROM pg_index X JOIN pg_class C ON (C.oid = X.indrelid)
105 JOIN pg_class I ON (I.oid = X.indexrelid)
106 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
107 LEFT JOIN pg_tablespace T ON (T.oid = I.reltablespace)
108 WHERE C.relkind = 'r' AND I.relkind = 'i';
110 CREATE VIEW pg_stats AS
111 SELECT
112 nspname AS schemaname,
113 relname AS tablename,
114 attname AS attname,
115 stainherit AS inherited,
116 stanullfrac AS null_frac,
117 stawidth AS avg_width,
118 stadistinct AS n_distinct,
119 CASE
120 WHEN stakind1 IN (1, 4) THEN stavalues1
121 WHEN stakind2 IN (1, 4) THEN stavalues2
122 WHEN stakind3 IN (1, 4) THEN stavalues3
123 WHEN stakind4 IN (1, 4) THEN stavalues4
124 END AS most_common_vals,
125 CASE
126 WHEN stakind1 IN (1, 4) THEN stanumbers1
127 WHEN stakind2 IN (1, 4) THEN stanumbers2
128 WHEN stakind3 IN (1, 4) THEN stanumbers3
129 WHEN stakind4 IN (1, 4) THEN stanumbers4
130 END AS most_common_freqs,
131 CASE
132 WHEN stakind1 = 2 THEN stavalues1
133 WHEN stakind2 = 2 THEN stavalues2
134 WHEN stakind3 = 2 THEN stavalues3
135 WHEN stakind4 = 2 THEN stavalues4
136 END AS histogram_bounds,
137 CASE
138 WHEN stakind1 = 3 THEN stanumbers1[1]
139 WHEN stakind2 = 3 THEN stanumbers2[1]
140 WHEN stakind3 = 3 THEN stanumbers3[1]
141 WHEN stakind4 = 3 THEN stanumbers4[1]
142 END AS correlation
143 FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid)
144 JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum)
145 LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
146 WHERE NOT attisdropped AND has_column_privilege(c.oid, a.attnum, 'select');
148 REVOKE ALL on pg_statistic FROM public;
150 CREATE VIEW pg_locks AS
151 SELECT * FROM pg_lock_status() AS L;
153 CREATE VIEW pg_cursors AS
154 SELECT * FROM pg_cursor() AS C;
156 CREATE VIEW pg_available_extensions AS
157 SELECT E.name, E.version, X.extversion AS installed,
158 N.nspname AS schema, E.relocatable, E.comment
159 FROM pg_available_extensions() AS E
160 LEFT JOIN pg_extension AS X ON E.name = X.extname
161 LEFT JOIN pg_namespace AS N on N.oid = X.extnamespace;
163 CREATE VIEW pg_prepared_xacts AS
164 SELECT P.transaction, P.gid, P.prepared,
165 U.rolname AS owner, D.datname AS database
166 FROM pg_prepared_xact() AS P
167 LEFT JOIN pg_authid U ON P.ownerid = U.oid
168 LEFT JOIN pg_database D ON P.dbid = D.oid;
170 CREATE VIEW pg_prepared_statements AS
171 SELECT * FROM pg_prepared_statement() AS P;
173 CREATE VIEW pg_seclabels AS
174 SELECT
175 l.objoid, l.classoid, l.objsubid,
176 CASE WHEN rel.relkind = 'r' THEN 'table'::text
177 WHEN rel.relkind = 'v' THEN 'view'::text
178 WHEN rel.relkind = 'S' THEN 'sequence'::text
179 WHEN rel.relkind = 'f' THEN 'foreign table'::text END AS objtype,
180 rel.relnamespace AS objnamespace,
181 CASE WHEN pg_table_is_visible(rel.oid)
182 THEN quote_ident(rel.relname)
183 ELSE quote_ident(nsp.nspname) || '.' || quote_ident(rel.relname)
184 END AS objname,
185 l.provider, l.label
186 FROM
187 pg_seclabel l
188 JOIN pg_class rel ON l.classoid = rel.tableoid AND l.objoid = rel.oid
189 JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid
190 WHERE
191 l.objsubid = 0
192 UNION ALL
193 SELECT
194 l.objoid, l.classoid, l.objsubid,
195 'column'::text AS objtype,
196 rel.relnamespace AS objnamespace,
197 CASE WHEN pg_table_is_visible(rel.oid)
198 THEN quote_ident(rel.relname)
199 ELSE quote_ident(nsp.nspname) || '.' || quote_ident(rel.relname)
200 END || '.' || att.attname AS objname,
201 l.provider, l.label
202 FROM
203 pg_seclabel l
204 JOIN pg_class rel ON l.classoid = rel.tableoid AND l.objoid = rel.oid
205 JOIN pg_attribute att
206 ON rel.oid = att.attrelid AND l.objsubid = att.attnum
207 JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid
208 WHERE
209 l.objsubid != 0
210 UNION ALL
211 SELECT
212 l.objoid, l.classoid, l.objsubid,
213 CASE WHEN pro.proisagg = true THEN 'aggregate'::text
214 WHEN pro.proisagg = false THEN 'function'::text
215 END AS objtype,
216 pro.pronamespace AS objnamespace,
217 CASE WHEN pg_function_is_visible(pro.oid)
218 THEN quote_ident(pro.proname)
219 ELSE quote_ident(nsp.nspname) || '.' || quote_ident(pro.proname)
220 END || '(' || pg_catalog.pg_get_function_arguments(pro.oid) || ')' AS objname,
221 l.provider, l.label
222 FROM
223 pg_seclabel l
224 JOIN pg_proc pro ON l.classoid = pro.tableoid AND l.objoid = pro.oid
225 JOIN pg_namespace nsp ON pro.pronamespace = nsp.oid
226 WHERE
227 l.objsubid = 0
228 UNION ALL
229 SELECT
230 l.objoid, l.classoid, l.objsubid,
231 CASE WHEN typ.typtype = 'd' THEN 'domain'::text
232 ELSE 'type'::text END AS objtype,
233 typ.typnamespace AS objnamespace,
234 CASE WHEN pg_type_is_visible(typ.oid)
235 THEN quote_ident(typ.typname)
236 ELSE quote_ident(nsp.nspname) || '.' || quote_ident(typ.typname)
237 END AS objname,
238 l.provider, l.label
239 FROM
240 pg_seclabel l
241 JOIN pg_type typ ON l.classoid = typ.tableoid AND l.objoid = typ.oid
242 JOIN pg_namespace nsp ON typ.typnamespace = nsp.oid
243 WHERE
244 l.objsubid = 0
245 UNION ALL
246 SELECT
247 l.objoid, l.classoid, l.objsubid,
248 'large object'::text AS objtype,
249 NULL::oid AS objnamespace,
250 l.objoid::text AS objname,
251 l.provider, l.label
252 FROM
253 pg_seclabel l
254 JOIN pg_largeobject_metadata lom ON l.objoid = lom.oid
255 WHERE
256 l.classoid = 'pg_catalog.pg_largeobject'::regclass AND l.objsubid = 0
257 UNION ALL
258 SELECT
259 l.objoid, l.classoid, l.objsubid,
260 'language'::text AS objtype,
261 NULL::oid AS objnamespace,
262 quote_ident(lan.lanname) AS objname,
263 l.provider, l.label
264 FROM
265 pg_seclabel l
266 JOIN pg_language lan ON l.classoid = lan.tableoid AND l.objoid = lan.oid
267 WHERE
268 l.objsubid = 0
269 UNION ALL
270 SELECT
271 l.objoid, l.classoid, l.objsubid,
272 'schema'::text AS objtype,
273 nsp.oid AS objnamespace,
274 quote_ident(nsp.nspname) AS objname,
275 l.provider, l.label
276 FROM
277 pg_seclabel l
278 JOIN pg_namespace nsp ON l.classoid = nsp.tableoid AND l.objoid = nsp.oid
279 WHERE
280 l.objsubid = 0;
282 CREATE VIEW pg_settings AS
283 SELECT * FROM pg_show_all_settings() AS A;
285 CREATE RULE pg_settings_u AS
286 ON UPDATE TO pg_settings
287 WHERE new.name = old.name DO
288 SELECT set_config(old.name, new.setting, 'f');
290 CREATE RULE pg_settings_n AS
291 ON UPDATE TO pg_settings
292 DO INSTEAD NOTHING;
294 GRANT SELECT, UPDATE ON pg_settings TO PUBLIC;
296 CREATE VIEW pg_timezone_abbrevs AS
297 SELECT * FROM pg_timezone_abbrevs();
299 CREATE VIEW pg_timezone_names AS
300 SELECT * FROM pg_timezone_names();
302 -- Statistics views
304 CREATE VIEW pg_stat_all_tables AS
305 SELECT
306 C.oid AS relid,
307 N.nspname AS schemaname,
308 C.relname AS relname,
309 pg_stat_get_numscans(C.oid) AS seq_scan,
310 pg_stat_get_tuples_returned(C.oid) AS seq_tup_read,
311 sum(pg_stat_get_numscans(I.indexrelid))::bigint AS idx_scan,
312 sum(pg_stat_get_tuples_fetched(I.indexrelid))::bigint +
313 pg_stat_get_tuples_fetched(C.oid) AS idx_tup_fetch,
314 pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins,
315 pg_stat_get_tuples_updated(C.oid) AS n_tup_upd,
316 pg_stat_get_tuples_deleted(C.oid) AS n_tup_del,
317 pg_stat_get_tuples_hot_updated(C.oid) AS n_tup_hot_upd,
318 pg_stat_get_live_tuples(C.oid) AS n_live_tup,
319 pg_stat_get_dead_tuples(C.oid) AS n_dead_tup,
320 pg_stat_get_last_vacuum_time(C.oid) as last_vacuum,
321 pg_stat_get_last_autovacuum_time(C.oid) as last_autovacuum,
322 pg_stat_get_last_analyze_time(C.oid) as last_analyze,
323 pg_stat_get_last_autoanalyze_time(C.oid) as last_autoanalyze,
324 pg_stat_get_vacuum_count(C.oid) AS vacuum_count,
325 pg_stat_get_autovacuum_count(C.oid) AS autovacuum_count,
326 pg_stat_get_analyze_count(C.oid) AS analyze_count,
327 pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count
328 FROM pg_class C LEFT JOIN
329 pg_index I ON C.oid = I.indrelid
330 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
331 WHERE C.relkind IN ('r', 't')
332 GROUP BY C.oid, N.nspname, C.relname;
334 CREATE VIEW pg_stat_xact_all_tables AS
335 SELECT
336 C.oid AS relid,
337 N.nspname AS schemaname,
338 C.relname AS relname,
339 pg_stat_get_xact_numscans(C.oid) AS seq_scan,
340 pg_stat_get_xact_tuples_returned(C.oid) AS seq_tup_read,
341 sum(pg_stat_get_xact_numscans(I.indexrelid))::bigint AS idx_scan,
342 sum(pg_stat_get_xact_tuples_fetched(I.indexrelid))::bigint +
343 pg_stat_get_xact_tuples_fetched(C.oid) AS idx_tup_fetch,
344 pg_stat_get_xact_tuples_inserted(C.oid) AS n_tup_ins,
345 pg_stat_get_xact_tuples_updated(C.oid) AS n_tup_upd,
346 pg_stat_get_xact_tuples_deleted(C.oid) AS n_tup_del,
347 pg_stat_get_xact_tuples_hot_updated(C.oid) AS n_tup_hot_upd
348 FROM pg_class C LEFT JOIN
349 pg_index I ON C.oid = I.indrelid
350 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
351 WHERE C.relkind IN ('r', 't')
352 GROUP BY C.oid, N.nspname, C.relname;
354 CREATE VIEW pg_stat_sys_tables AS
355 SELECT * FROM pg_stat_all_tables
356 WHERE schemaname IN ('pg_catalog', 'information_schema') OR
357 schemaname ~ '^pg_toast';
359 CREATE VIEW pg_stat_xact_sys_tables AS
360 SELECT * FROM pg_stat_xact_all_tables
361 WHERE schemaname IN ('pg_catalog', 'information_schema') OR
362 schemaname ~ '^pg_toast';
364 CREATE VIEW pg_stat_user_tables AS
365 SELECT * FROM pg_stat_all_tables
366 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
367 schemaname !~ '^pg_toast';
369 CREATE VIEW pg_stat_xact_user_tables AS
370 SELECT * FROM pg_stat_xact_all_tables
371 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
372 schemaname !~ '^pg_toast';
374 CREATE VIEW pg_statio_all_tables AS
375 SELECT
376 C.oid AS relid,
377 N.nspname AS schemaname,
378 C.relname AS relname,
379 pg_stat_get_blocks_fetched(C.oid) -
380 pg_stat_get_blocks_hit(C.oid) AS heap_blks_read,
381 pg_stat_get_blocks_hit(C.oid) AS heap_blks_hit,
382 sum(pg_stat_get_blocks_fetched(I.indexrelid) -
383 pg_stat_get_blocks_hit(I.indexrelid))::bigint AS idx_blks_read,
384 sum(pg_stat_get_blocks_hit(I.indexrelid))::bigint AS idx_blks_hit,
385 pg_stat_get_blocks_fetched(T.oid) -
386 pg_stat_get_blocks_hit(T.oid) AS toast_blks_read,
387 pg_stat_get_blocks_hit(T.oid) AS toast_blks_hit,
388 pg_stat_get_blocks_fetched(X.oid) -
389 pg_stat_get_blocks_hit(X.oid) AS tidx_blks_read,
390 pg_stat_get_blocks_hit(X.oid) AS tidx_blks_hit
391 FROM pg_class C LEFT JOIN
392 pg_index I ON C.oid = I.indrelid LEFT JOIN
393 pg_class T ON C.reltoastrelid = T.oid LEFT JOIN
394 pg_class X ON T.reltoastidxid = X.oid
395 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
396 WHERE C.relkind IN ('r', 't')
397 GROUP BY C.oid, N.nspname, C.relname, T.oid, X.oid;
399 CREATE VIEW pg_statio_sys_tables AS
400 SELECT * FROM pg_statio_all_tables
401 WHERE schemaname IN ('pg_catalog', 'information_schema') OR
402 schemaname ~ '^pg_toast';
404 CREATE VIEW pg_statio_user_tables AS
405 SELECT * FROM pg_statio_all_tables
406 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
407 schemaname !~ '^pg_toast';
409 CREATE VIEW pg_stat_all_indexes AS
410 SELECT
411 C.oid AS relid,
412 I.oid AS indexrelid,
413 N.nspname AS schemaname,
414 C.relname AS relname,
415 I.relname AS indexrelname,
416 pg_stat_get_numscans(I.oid) AS idx_scan,
417 pg_stat_get_tuples_returned(I.oid) AS idx_tup_read,
418 pg_stat_get_tuples_fetched(I.oid) AS idx_tup_fetch
419 FROM pg_class C JOIN
420 pg_index X ON C.oid = X.indrelid JOIN
421 pg_class I ON I.oid = X.indexrelid
422 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
423 WHERE C.relkind IN ('r', 't');
425 CREATE VIEW pg_stat_sys_indexes AS
426 SELECT * FROM pg_stat_all_indexes
427 WHERE schemaname IN ('pg_catalog', 'information_schema') OR
428 schemaname ~ '^pg_toast';
430 CREATE VIEW pg_stat_user_indexes AS
431 SELECT * FROM pg_stat_all_indexes
432 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
433 schemaname !~ '^pg_toast';
435 CREATE VIEW pg_statio_all_indexes AS
436 SELECT
437 C.oid AS relid,
438 I.oid AS indexrelid,
439 N.nspname AS schemaname,
440 C.relname AS relname,
441 I.relname AS indexrelname,
442 pg_stat_get_blocks_fetched(I.oid) -
443 pg_stat_get_blocks_hit(I.oid) AS idx_blks_read,
444 pg_stat_get_blocks_hit(I.oid) AS idx_blks_hit
445 FROM pg_class C JOIN
446 pg_index X ON C.oid = X.indrelid JOIN
447 pg_class I ON I.oid = X.indexrelid
448 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
449 WHERE C.relkind IN ('r', 't');
451 CREATE VIEW pg_statio_sys_indexes AS
452 SELECT * FROM pg_statio_all_indexes
453 WHERE schemaname IN ('pg_catalog', 'information_schema') OR
454 schemaname ~ '^pg_toast';
456 CREATE VIEW pg_statio_user_indexes AS
457 SELECT * FROM pg_statio_all_indexes
458 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
459 schemaname !~ '^pg_toast';
461 CREATE VIEW pg_statio_all_sequences AS
462 SELECT
463 C.oid AS relid,
464 N.nspname AS schemaname,
465 C.relname AS relname,
466 pg_stat_get_blocks_fetched(C.oid) -
467 pg_stat_get_blocks_hit(C.oid) AS blks_read,
468 pg_stat_get_blocks_hit(C.oid) AS blks_hit
469 FROM pg_class C
470 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
471 WHERE C.relkind = 'S';
473 CREATE VIEW pg_statio_sys_sequences AS
474 SELECT * FROM pg_statio_all_sequences
475 WHERE schemaname IN ('pg_catalog', 'information_schema') OR
476 schemaname ~ '^pg_toast';
478 CREATE VIEW pg_statio_user_sequences AS
479 SELECT * FROM pg_statio_all_sequences
480 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
481 schemaname !~ '^pg_toast';
483 CREATE VIEW pg_stat_activity AS
484 SELECT
485 S.datid AS datid,
486 D.datname AS datname,
487 S.procpid,
488 S.usesysid,
489 U.rolname AS usename,
490 S.application_name,
491 S.client_addr,
492 S.client_port,
493 S.backend_start,
494 S.xact_start,
495 S.query_start,
496 S.waiting,
497 S.current_query
498 FROM pg_database D, pg_stat_get_activity(NULL) AS S, pg_authid U
499 WHERE S.datid = D.oid AND
500 S.usesysid = U.oid;
502 CREATE VIEW pg_stat_replication AS
503 SELECT
504 S.procpid,
505 S.usesysid,
506 U.rolname AS usename,
507 S.application_name,
508 S.client_addr,
509 S.client_port,
510 S.backend_start,
511 W.state,
512 W.sent_location
513 FROM pg_stat_get_activity(NULL) AS S, pg_authid U,
514 pg_stat_get_wal_senders() AS W
515 WHERE S.usesysid = U.oid AND
516 S.procpid = W.procpid;
518 CREATE VIEW pg_stat_database AS
519 SELECT
520 D.oid AS datid,
521 D.datname AS datname,
522 pg_stat_get_db_numbackends(D.oid) AS numbackends,
523 pg_stat_get_db_xact_commit(D.oid) AS xact_commit,
524 pg_stat_get_db_xact_rollback(D.oid) AS xact_rollback,
525 pg_stat_get_db_blocks_fetched(D.oid) -
526 pg_stat_get_db_blocks_hit(D.oid) AS blks_read,
527 pg_stat_get_db_blocks_hit(D.oid) AS blks_hit,
528 pg_stat_get_db_tuples_returned(D.oid) AS tup_returned,
529 pg_stat_get_db_tuples_fetched(D.oid) AS tup_fetched,
530 pg_stat_get_db_tuples_inserted(D.oid) AS tup_inserted,
531 pg_stat_get_db_tuples_updated(D.oid) AS tup_updated,
532 pg_stat_get_db_tuples_deleted(D.oid) AS tup_deleted,
533 pg_stat_get_db_conflict_all(D.oid) AS conflicts
534 FROM pg_database D;
536 CREATE VIEW pg_stat_database_conflicts AS
537 SELECT
538 D.oid AS datid,
539 D.datname AS datname,
540 pg_stat_get_db_conflict_tablespace(D.oid) AS confl_tablespace,
541 pg_stat_get_db_conflict_lock(D.oid) AS confl_lock,
542 pg_stat_get_db_conflict_snapshot(D.oid) AS confl_snapshot,
543 pg_stat_get_db_conflict_bufferpin(D.oid) AS confl_bufferpin,
544 pg_stat_get_db_conflict_startup_deadlock(D.oid) AS confl_deadlock
545 FROM pg_database D;
547 CREATE VIEW pg_stat_user_functions AS
548 SELECT
549 P.oid AS funcid,
550 N.nspname AS schemaname,
551 P.proname AS funcname,
552 pg_stat_get_function_calls(P.oid) AS calls,
553 pg_stat_get_function_time(P.oid) / 1000 AS total_time,
554 pg_stat_get_function_self_time(P.oid) / 1000 AS self_time
555 FROM pg_proc P LEFT JOIN pg_namespace N ON (N.oid = P.pronamespace)
556 WHERE P.prolang != 12 -- fast check to eliminate built-in functions
557 AND pg_stat_get_function_calls(P.oid) IS NOT NULL;
559 CREATE VIEW pg_stat_xact_user_functions AS
560 SELECT
561 P.oid AS funcid,
562 N.nspname AS schemaname,
563 P.proname AS funcname,
564 pg_stat_get_xact_function_calls(P.oid) AS calls,
565 pg_stat_get_xact_function_time(P.oid) / 1000 AS total_time,
566 pg_stat_get_xact_function_self_time(P.oid) / 1000 AS self_time
567 FROM pg_proc P LEFT JOIN pg_namespace N ON (N.oid = P.pronamespace)
568 WHERE P.prolang != 12 -- fast check to eliminate built-in functions
569 AND pg_stat_get_xact_function_calls(P.oid) IS NOT NULL;
571 CREATE VIEW pg_stat_bgwriter AS
572 SELECT
573 pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed,
574 pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req,
575 pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint,
576 pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean,
577 pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,
578 pg_stat_get_buf_written_backend() AS buffers_backend,
579 pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync,
580 pg_stat_get_buf_alloc() AS buffers_alloc;
582 CREATE VIEW pg_user_mappings AS
583 SELECT
584 U.oid AS umid,
585 S.oid AS srvid,
586 S.srvname AS srvname,
587 U.umuser AS umuser,
588 CASE WHEN U.umuser = 0 THEN
589 'public'
590 ELSE
591 A.rolname
592 END AS usename,
593 CASE WHEN pg_has_role(S.srvowner, 'USAGE') OR has_server_privilege(S.oid, 'USAGE') THEN
594 U.umoptions
595 ELSE
596 NULL
597 END AS umoptions
598 FROM pg_user_mapping U
599 LEFT JOIN pg_authid A ON (A.oid = U.umuser) JOIN
600 pg_foreign_server S ON (U.umserver = S.oid);
602 REVOKE ALL on pg_user_mapping FROM public;
604 --
605 -- We have a few function definitions in here, too.
606 -- At some point there might be enough to justify breaking them out into
607 -- a separate "system_functions.sql" file.
608 --
610 -- Tsearch debug function. Defined here because it'd be pretty unwieldy
611 -- to put it into pg_proc.h
613 CREATE FUNCTION ts_debug(IN config regconfig, IN document text,
614 OUT alias text,
615 OUT description text,
616 OUT token text,
617 OUT dictionaries regdictionary[],
618 OUT dictionary regdictionary,
619 OUT lexemes text[])
620 RETURNS SETOF record AS
621 $$
622 SELECT
623 tt.alias AS alias,
624 tt.description AS description,
625 parse.token AS token,
626 ARRAY ( SELECT m.mapdict::pg_catalog.regdictionary
627 FROM pg_catalog.pg_ts_config_map AS m
628 WHERE m.mapcfg = $1 AND m.maptokentype = parse.tokid
629 ORDER BY m.mapseqno )
630 AS dictionaries,
631 ( SELECT mapdict::pg_catalog.regdictionary
632 FROM pg_catalog.pg_ts_config_map AS m
633 WHERE m.mapcfg = $1 AND m.maptokentype = parse.tokid
634 ORDER BY pg_catalog.ts_lexize(mapdict, parse.token) IS NULL, m.mapseqno
635 LIMIT 1
636 ) AS dictionary,
637 ( SELECT pg_catalog.ts_lexize(mapdict, parse.token)
638 FROM pg_catalog.pg_ts_config_map AS m
639 WHERE m.mapcfg = $1 AND m.maptokentype = parse.tokid
640 ORDER BY pg_catalog.ts_lexize(mapdict, parse.token) IS NULL, m.mapseqno
641 LIMIT 1
642 ) AS lexemes
643 FROM pg_catalog.ts_parse(
644 (SELECT cfgparser FROM pg_catalog.pg_ts_config WHERE oid = $1 ), $2
645 ) AS parse,
646 pg_catalog.ts_token_type(
647 (SELECT cfgparser FROM pg_catalog.pg_ts_config WHERE oid = $1 )
648 ) AS tt
649 WHERE tt.tokid = parse.tokid
650 $$
651 LANGUAGE SQL STRICT STABLE;
653 COMMENT ON FUNCTION ts_debug(regconfig,text) IS
654 'debug function for text search configuration';
656 CREATE FUNCTION ts_debug(IN document text,
657 OUT alias text,
658 OUT description text,
659 OUT token text,
660 OUT dictionaries regdictionary[],
661 OUT dictionary regdictionary,
662 OUT lexemes text[])
663 RETURNS SETOF record AS
664 $$
665 SELECT * FROM pg_catalog.ts_debug( pg_catalog.get_current_ts_config(), $1);
666 $$
667 LANGUAGE SQL STRICT STABLE;
669 COMMENT ON FUNCTION ts_debug(text) IS
670 'debug function for current text search configuration';
672 --
673 -- Redeclare built-in functions that need default values attached to their
674 -- arguments. It's impractical to set those up directly in pg_proc.h because
675 -- of the complexity and platform-dependency of the expression tree
676 -- representation. (Note that internal functions still have to have entries
677 -- in pg_proc.h; we are merely causing their proargnames and proargdefaults
678 -- to get filled in.)
679 --
681 CREATE OR REPLACE FUNCTION
682 format_type(oid, int DEFAULT NULL, oid DEFAULT NULL)
683 RETURNS text STABLE LANGUAGE internal AS 'format_type';
685 CREATE OR REPLACE FUNCTION
686 pg_start_backup(label text, fast boolean DEFAULT false)
687 RETURNS text STRICT VOLATILE LANGUAGE internal AS 'pg_start_backup';