From fbbd150a25676076b5ed0e68f77adafcf46c1f4d Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 11 Nov 2013 13:36:42 -0500 Subject: [PATCH] Fix ruleutils pretty-printing to not generate trailing whitespace. The pretty-printing logic in ruleutils.c operates by inserting a newline and some indentation whitespace into strings that are already valid SQL. This naturally results in leaving some trailing whitespace before the newline in many cases; which can be annoying when processing the output with other tools, as complained of by Joe Abbate. We can fix that in a pretty localized fashion by deleting any trailing whitespace before we append a pretty-printing newline. In addition, we have to modify the code inserted by commit 2f582f76b1945929ff07116cd4639747ce9bb8a1 so that we also delete trailing whitespace when transposing items from temporary buffers into the main result string, when a temporary item starts with a newline. This results in rather voluminous changes to the regression test results, but it's easily verified that they are only removal of trailing whitespace. Back-patch to 9.3, because the aforementioned commit resulted in many more cases of trailing whitespace than had occurred in earlier branches. --- src/backend/utils/adt/ruleutils.c | 129 +- src/test/regress/expected/aggregates.out | 24 +- src/test/regress/expected/create_view.out | 96 +- src/test/regress/expected/matview.out | 18 +- src/test/regress/expected/polymorphism.out | 6 +- src/test/regress/expected/rules.out | 1672 ++++++++++---------- src/test/regress/expected/triggers.out | 2 +- src/test/regress/expected/with.out | 2 +- 8 files changed, 990 insertions(+), 959 deletions(-) diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index df3d896264..fbade83746 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -366,6 +366,7 @@ static const char *get_simple_binary_op_name(OpExpr *expr); static bool isSimpleNode(Node *node, Node *parentNode, int prettyFlags); static void appendContextKeyword(deparse_context *context, const char *str, int indentBefore, int indentAfter, int indentPlus); +static void removeStringInfoSpaces(StringInfo str); static void get_rule_expr(Node *node, deparse_context *context, bool showimplicit); static void get_oper_expr(OpExpr *expr, deparse_context *context); @@ -4494,42 +4495,42 @@ get_target_list(List *targetList, deparse_context *context, /* Consider line-wrapping if enabled */ if (PRETTY_INDENT(context) && context->wrapColumn >= 0) { - int leading_nl_pos = -1; - char *trailing_nl; - int pos; + int leading_nl_pos; - /* Does the new field start with whitespace plus a new line? */ - for (pos = 0; pos < targetbuf.len; pos++) + /* Does the new field start with a new line? */ + if (targetbuf.len > 0 && targetbuf.data[0] == '\n') + leading_nl_pos = 0; + else + leading_nl_pos = -1; + + /* If so, we shouldn't add anything */ + if (leading_nl_pos >= 0) { - if (targetbuf.data[pos] == '\n') - { - leading_nl_pos = pos; - break; - } - if (targetbuf.data[pos] != ' ') - break; + /* instead, remove any trailing spaces currently in buf */ + removeStringInfoSpaces(buf); } - - /* Locate the start of the current line in the output buffer */ - trailing_nl = strrchr(buf->data, '\n'); - if (trailing_nl == NULL) - trailing_nl = buf->data; else - trailing_nl++; + { + char *trailing_nl; - /* - * If the field we're adding is the first in the list, or it - * already has a leading newline, don't add anything. Otherwise, - * add a newline, plus some indentation, if either the new field - * would cause an overflow or the last field used more than one - * line. - */ - if (colno > 1 && - leading_nl_pos == -1 && - ((strlen(trailing_nl) + strlen(targetbuf.data) > context->wrapColumn) || - last_was_multiline)) - appendContextKeyword(context, "", -PRETTYINDENT_STD, - PRETTYINDENT_STD, PRETTYINDENT_VAR); + /* Locate the start of the current line in the output buffer */ + trailing_nl = strrchr(buf->data, '\n'); + if (trailing_nl == NULL) + trailing_nl = buf->data; + else + trailing_nl++; + + /* + * Add a newline, plus some indentation, if the new field is + * not the first and either the new field would cause an + * overflow or the last field used more than one line. + */ + if (colno > 1 && + ((strlen(trailing_nl) + targetbuf.len > context->wrapColumn) || + last_was_multiline)) + appendContextKeyword(context, "", -PRETTYINDENT_STD, + PRETTYINDENT_STD, PRETTYINDENT_VAR); + } /* Remember this field's multiline status for next iteration */ last_was_multiline = @@ -6251,23 +6252,42 @@ static void appendContextKeyword(deparse_context *context, const char *str, int indentBefore, int indentAfter, int indentPlus) { + StringInfo buf = context->buf; + if (PRETTY_INDENT(context)) { context->indentLevel += indentBefore; - appendStringInfoChar(context->buf, '\n'); - appendStringInfoSpaces(context->buf, + /* remove any trailing spaces currently in the buffer ... */ + removeStringInfoSpaces(buf); + /* ... then add a newline and some spaces */ + appendStringInfoChar(buf, '\n'); + appendStringInfoSpaces(buf, Max(context->indentLevel, 0) + indentPlus); - appendStringInfoString(context->buf, str); + + appendStringInfoString(buf, str); context->indentLevel += indentAfter; if (context->indentLevel < 0) context->indentLevel = 0; } else - appendStringInfoString(context->buf, str); + appendStringInfoString(buf, str); } +/* + * removeStringInfoSpaces - delete trailing spaces from a buffer. + * + * Possibly this should move to stringinfo.c at some point. + */ +static void +removeStringInfoSpaces(StringInfo str) +{ + while (str->len > 0 && str->data[str->len - 1] == ' ') + str->data[--(str->len)] = '\0'; +} + + /* * get_rule_expr_paren - deparse expr using get_rule_expr, * embracing the string with parentheses if necessary for prettyPrint. @@ -7929,22 +7949,33 @@ get_from_clause(Query *query, const char *prefix, deparse_context *context) /* Consider line-wrapping if enabled */ if (PRETTY_INDENT(context) && context->wrapColumn >= 0) { - char *trailing_nl; - - /* Locate the start of the current line in the buffer */ - trailing_nl = strrchr(buf->data, '\n'); - if (trailing_nl == NULL) - trailing_nl = buf->data; + /* Does the new item start with a new line? */ + if (itembuf.len > 0 && itembuf.data[0] == '\n') + { + /* If so, we shouldn't add anything */ + /* instead, remove any trailing spaces currently in buf */ + removeStringInfoSpaces(buf); + } else - trailing_nl++; + { + char *trailing_nl; - /* - * Add a newline, plus some indentation, if the new item would - * cause an overflow. - */ - if (strlen(trailing_nl) + strlen(itembuf.data) > context->wrapColumn) - appendContextKeyword(context, "", -PRETTYINDENT_STD, - PRETTYINDENT_STD, PRETTYINDENT_VAR); + /* Locate the start of the current line in the buffer */ + trailing_nl = strrchr(buf->data, '\n'); + if (trailing_nl == NULL) + trailing_nl = buf->data; + else + trailing_nl++; + + /* + * Add a newline, plus some indentation, if the new item + * would cause an overflow. + */ + if (strlen(trailing_nl) + itembuf.len > context->wrapColumn) + appendContextKeyword(context, "", -PRETTYINDENT_STD, + PRETTYINDENT_STD, + PRETTYINDENT_VAR); + } } /* Add the new item */ diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out index d379c0d759..953a45fbf0 100644 --- a/src/test/regress/expected/aggregates.out +++ b/src/test/regress/expected/aggregates.out @@ -960,10 +960,10 @@ select * from agg_view1; (1 row) select pg_get_viewdef('agg_view1'::regclass); - pg_get_viewdef ----------------------------------------------------------------------------------------------------------------------- - SELECT aggfns(DISTINCT v.a, v.b, v.c) AS aggfns + - FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c), + + pg_get_viewdef +--------------------------------------------------------------------------------------------------------------------- + SELECT aggfns(DISTINCT v.a, v.b, v.c) AS aggfns + + FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c),+ generate_series(1, 3) i(i); (1 row) @@ -978,10 +978,10 @@ select * from agg_view1; (1 row) select pg_get_viewdef('agg_view1'::regclass); - pg_get_viewdef ----------------------------------------------------------------------------------------------------------------------- - SELECT aggfns(DISTINCT v.a, v.b, v.c ORDER BY v.b) AS aggfns + - FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c), + + pg_get_viewdef +--------------------------------------------------------------------------------------------------------------------- + SELECT aggfns(DISTINCT v.a, v.b, v.c ORDER BY v.b) AS aggfns + + FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c),+ generate_series(1, 3) i(i); (1 row) @@ -1044,10 +1044,10 @@ select * from agg_view1; (1 row) select pg_get_viewdef('agg_view1'::regclass); - pg_get_viewdef ----------------------------------------------------------------------------------------------------------------------- - SELECT aggfns(DISTINCT v.a, v.b, v.c ORDER BY v.a, v.c USING ~<~ NULLS LAST, v.b) AS aggfns + - FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c), + + pg_get_viewdef +--------------------------------------------------------------------------------------------------------------------- + SELECT aggfns(DISTINCT v.a, v.b, v.c ORDER BY v.a, v.c USING ~<~ NULLS LAST, v.b) AS aggfns + + FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c),+ generate_series(1, 2) i(i); (1 row) diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out index f1fdd50d6a..f6db582afd 100644 --- a/src/test/regress/expected/create_view.out +++ b/src/test/regress/expected/create_view.out @@ -312,8 +312,8 @@ CREATE VIEW aliased_view_4 AS f2 | integer | | plain | f3 | text | | extended | View definition: - SELECT tt1.f1, - tt1.f2, + SELECT tt1.f1, + tt1.f2, tt1.f3 FROM tt1 WHERE (EXISTS ( SELECT 1 @@ -328,8 +328,8 @@ View definition: f2 | integer | | plain | f3 | text | | extended | View definition: - SELECT a1.f1, - a1.f2, + SELECT a1.f1, + a1.f2, a1.f3 FROM tt1 a1 WHERE (EXISTS ( SELECT 1 @@ -344,8 +344,8 @@ View definition: f2 | integer | | plain | f3 | text | | extended | View definition: - SELECT tt1.f1, - tt1.f2, + SELECT tt1.f1, + tt1.f2, tt1.f3 FROM tt1 WHERE (EXISTS ( SELECT 1 @@ -360,8 +360,8 @@ View definition: f2 | integer | | plain | f3 | text | | extended | View definition: - SELECT tt1.y1, - tt1.f2, + SELECT tt1.y1, + tt1.f2, tt1.f3 FROM temp_view_test.tt1 WHERE (EXISTS ( SELECT 1 @@ -377,8 +377,8 @@ ALTER TABLE tx1 RENAME TO a1; f2 | integer | | plain | f3 | text | | extended | View definition: - SELECT tt1.f1, - tt1.f2, + SELECT tt1.f1, + tt1.f2, tt1.f3 FROM tt1 WHERE (EXISTS ( SELECT 1 @@ -393,8 +393,8 @@ View definition: f2 | integer | | plain | f3 | text | | extended | View definition: - SELECT a1.f1, - a1.f2, + SELECT a1.f1, + a1.f2, a1.f3 FROM tt1 a1 WHERE (EXISTS ( SELECT 1 @@ -409,8 +409,8 @@ View definition: f2 | integer | | plain | f3 | text | | extended | View definition: - SELECT tt1.f1, - tt1.f2, + SELECT tt1.f1, + tt1.f2, tt1.f3 FROM tt1 WHERE (EXISTS ( SELECT 1 @@ -425,8 +425,8 @@ View definition: f2 | integer | | plain | f3 | text | | extended | View definition: - SELECT tt1.y1, - tt1.f2, + SELECT tt1.y1, + tt1.f2, tt1.f3 FROM temp_view_test.tt1 WHERE (EXISTS ( SELECT 1 @@ -442,8 +442,8 @@ ALTER TABLE tt1 RENAME TO a2; f2 | integer | | plain | f3 | text | | extended | View definition: - SELECT a2.f1, - a2.f2, + SELECT a2.f1, + a2.f2, a2.f3 FROM a2 WHERE (EXISTS ( SELECT 1 @@ -458,8 +458,8 @@ View definition: f2 | integer | | plain | f3 | text | | extended | View definition: - SELECT a1.f1, - a1.f2, + SELECT a1.f1, + a1.f2, a1.f3 FROM a2 a1 WHERE (EXISTS ( SELECT 1 @@ -474,8 +474,8 @@ View definition: f2 | integer | | plain | f3 | text | | extended | View definition: - SELECT a2.f1, - a2.f2, + SELECT a2.f1, + a2.f2, a2.f3 FROM a2 WHERE (EXISTS ( SELECT 1 @@ -490,8 +490,8 @@ View definition: f2 | integer | | plain | f3 | text | | extended | View definition: - SELECT tt1.y1, - tt1.f2, + SELECT tt1.y1, + tt1.f2, tt1.f3 FROM temp_view_test.tt1 WHERE (EXISTS ( SELECT 1 @@ -507,8 +507,8 @@ ALTER TABLE a1 RENAME TO tt1; f2 | integer | | plain | f3 | text | | extended | View definition: - SELECT a2.f1, - a2.f2, + SELECT a2.f1, + a2.f2, a2.f3 FROM a2 WHERE (EXISTS ( SELECT 1 @@ -523,8 +523,8 @@ View definition: f2 | integer | | plain | f3 | text | | extended | View definition: - SELECT a1.f1, - a1.f2, + SELECT a1.f1, + a1.f2, a1.f3 FROM a2 a1 WHERE (EXISTS ( SELECT 1 @@ -539,8 +539,8 @@ View definition: f2 | integer | | plain | f3 | text | | extended | View definition: - SELECT a2.f1, - a2.f2, + SELECT a2.f1, + a2.f2, a2.f3 FROM a2 WHERE (EXISTS ( SELECT 1 @@ -555,8 +555,8 @@ View definition: f2 | integer | | plain | f3 | text | | extended | View definition: - SELECT tt1.y1, - tt1.f2, + SELECT tt1.y1, + tt1.f2, tt1.f3 FROM temp_view_test.tt1 WHERE (EXISTS ( SELECT 1 @@ -573,8 +573,8 @@ ALTER TABLE tx1 SET SCHEMA temp_view_test; f2 | integer | | plain | f3 | text | | extended | View definition: - SELECT tx1.f1, - tx1.f2, + SELECT tx1.f1, + tx1.f2, tx1.f3 FROM temp_view_test.tx1 WHERE (EXISTS ( SELECT 1 @@ -589,8 +589,8 @@ View definition: f2 | integer | | plain | f3 | text | | extended | View definition: - SELECT a1.f1, - a1.f2, + SELECT a1.f1, + a1.f2, a1.f3 FROM temp_view_test.tx1 a1 WHERE (EXISTS ( SELECT 1 @@ -605,8 +605,8 @@ View definition: f2 | integer | | plain | f3 | text | | extended | View definition: - SELECT tx1.f1, - tx1.f2, + SELECT tx1.f1, + tx1.f2, tx1.f3 FROM temp_view_test.tx1 WHERE (EXISTS ( SELECT 1 @@ -621,8 +621,8 @@ View definition: f2 | integer | | plain | f3 | text | | extended | View definition: - SELECT tt1.y1, - tt1.f2, + SELECT tt1.y1, + tt1.f2, tt1.f3 FROM temp_view_test.tt1 WHERE (EXISTS ( SELECT 1 @@ -640,8 +640,8 @@ ALTER TABLE tmp1 RENAME TO tx1; f2 | integer | | plain | f3 | text | | extended | View definition: - SELECT tx1.f1, - tx1.f2, + SELECT tx1.f1, + tx1.f2, tx1.f3 FROM temp_view_test.tx1 WHERE (EXISTS ( SELECT 1 @@ -656,8 +656,8 @@ View definition: f2 | integer | | plain | f3 | text | | extended | View definition: - SELECT a1.f1, - a1.f2, + SELECT a1.f1, + a1.f2, a1.f3 FROM temp_view_test.tx1 a1 WHERE (EXISTS ( SELECT 1 @@ -672,8 +672,8 @@ View definition: f2 | integer | | plain | f3 | text | | extended | View definition: - SELECT tx1.f1, - tx1.f2, + SELECT tx1.f1, + tx1.f2, tx1.f3 FROM temp_view_test.tx1 WHERE (EXISTS ( SELECT 1 @@ -688,8 +688,8 @@ View definition: f2 | integer | | plain | f3 | text | | extended | View definition: - SELECT tx1.y1, - tx1.f2, + SELECT tx1.y1, + tx1.f2, tx1.f3 FROM tx1 WHERE (EXISTS ( SELECT 1 diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out index c93a17415d..65a6bfdada 100644 --- a/src/test/regress/expected/matview.out +++ b/src/test/regress/expected/matview.out @@ -95,7 +95,7 @@ CREATE INDEX aa ON bb (grandtot); type | text | | extended | | totamt | numeric | | main | | View definition: - SELECT tv.type, + SELECT tv.type, tv.totamt FROM tv ORDER BY tv.type; @@ -107,7 +107,7 @@ View definition: type | text | | extended | | totamt | numeric | | main | | View definition: - SELECT tv.type, + SELECT tv.type, tv.totamt FROM tv ORDER BY tv.type; @@ -153,7 +153,7 @@ SET search_path = mvschema, public; type | text | | extended | | totamt | numeric | | main | | View definition: - SELECT tv.type, + SELECT tv.type, tv.totamt FROM tv ORDER BY tv.type; @@ -329,11 +329,11 @@ CREATE VIEW v_test2 AS SELECT moo, 2*moo FROM v_test1 UNION ALL SELECT moo, 3*mo moo | integer | | plain | ?column? | integer | | plain | View definition: - SELECT v_test1.moo, + SELECT v_test1.moo, 2 * v_test1.moo FROM v_test1 -UNION ALL - SELECT v_test1.moo, +UNION ALL + SELECT v_test1.moo, 3 * v_test1.moo FROM v_test1; @@ -345,11 +345,11 @@ CREATE MATERIALIZED VIEW mv_test2 AS SELECT moo, 2*moo FROM v_test2 UNION ALL SE moo | integer | | plain | | ?column? | integer | | plain | | View definition: - SELECT v_test2.moo, + SELECT v_test2.moo, 2 * v_test2.moo FROM v_test2 -UNION ALL - SELECT v_test2.moo, +UNION ALL + SELECT v_test2.moo, 3 * v_test2.moo FROM v_test2; diff --git a/src/test/regress/expected/polymorphism.out b/src/test/regress/expected/polymorphism.out index b967f7c6f8..27b28790e0 100644 --- a/src/test/regress/expected/polymorphism.out +++ b/src/test/regress/expected/polymorphism.out @@ -1381,9 +1381,9 @@ select * from dfview; c3 | bigint | | plain | c4 | bigint | | plain | View definition: - SELECT int8_tbl.q1, - int8_tbl.q2, - dfunc(int8_tbl.q1, int8_tbl.q2, flag := int8_tbl.q1 > int8_tbl.q2) AS c3, + SELECT int8_tbl.q1, + int8_tbl.q2, + dfunc(int8_tbl.q1, int8_tbl.q2, flag := int8_tbl.q1 > int8_tbl.q2) AS c3, dfunc(int8_tbl.q1, flag := int8_tbl.q1 < int8_tbl.q2, b := int8_tbl.q2) AS c4 FROM int8_tbl; diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 57ae8427ec..3df99a532c 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1277,867 +1277,867 @@ drop table cchild; -- Check that ruleutils are working -- SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schema' ORDER BY viewname; - viewname | definition ----------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - iexit | SELECT ih.name, + - | ih.thepath, + - | interpt_pp(ih.thepath, r.thepath) AS exit + - | FROM ihighway ih, + - | ramp r + + viewname | definition +---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + iexit | SELECT ih.name, + + | ih.thepath, + + | interpt_pp(ih.thepath, r.thepath) AS exit + + | FROM ihighway ih, + + | ramp r + | WHERE (ih.thepath ## r.thepath); - pg_available_extension_versions | SELECT e.name, + - | e.version, + - | (x.extname IS NOT NULL) AS installed, + - | e.superuser, + - | e.relocatable, + - | e.schema, + - | e.requires, + - | e.comment + - | FROM (pg_available_extension_versions() e(name, version, superuser, relocatable, schema, requires, comment) + + pg_available_extension_versions | SELECT e.name, + + | e.version, + + | (x.extname IS NOT NULL) AS installed, + + | e.superuser, + + | e.relocatable, + + | e.schema, + + | e.requires, + + | e.comment + + | FROM (pg_available_extension_versions() e(name, version, superuser, relocatable, schema, requires, comment) + | LEFT JOIN pg_extension x ON (((e.name = x.extname) AND (e.version = x.extversion)))); - pg_available_extensions | SELECT e.name, + - | e.default_version, + - | x.extversion AS installed_version, + - | e.comment + - | FROM (pg_available_extensions() e(name, default_version, comment) + + pg_available_extensions | SELECT e.name, + + | e.default_version, + + | x.extversion AS installed_version, + + | e.comment + + | FROM (pg_available_extensions() e(name, default_version, comment) + | LEFT JOIN pg_extension x ON ((e.name = x.extname))); - pg_cursors | SELECT c.name, + - | c.statement, + - | c.is_holdable, + - | c.is_binary, + - | c.is_scrollable, + - | c.creation_time + + pg_cursors | SELECT c.name, + + | c.statement, + + | c.is_holdable, + + | c.is_binary, + + | c.is_scrollable, + + | c.creation_time + | FROM pg_cursor() c(name, statement, is_holdable, is_binary, is_scrollable, creation_time); - pg_group | SELECT pg_authid.rolname AS groname, + - | pg_authid.oid AS grosysid, + - | ARRAY( SELECT pg_auth_members.member + - | FROM pg_auth_members + - | WHERE (pg_auth_members.roleid = pg_authid.oid)) AS grolist + - | FROM pg_authid + + pg_group | SELECT pg_authid.rolname AS groname, + + | pg_authid.oid AS grosysid, + + | ARRAY( SELECT pg_auth_members.member + + | FROM pg_auth_members + + | WHERE (pg_auth_members.roleid = pg_authid.oid)) AS grolist + + | FROM pg_authid + | WHERE (NOT pg_authid.rolcanlogin); - pg_indexes | SELECT n.nspname AS schemaname, + - | c.relname AS tablename, + - | i.relname AS indexname, + - | t.spcname AS tablespace, + - | pg_get_indexdef(i.oid) AS indexdef + - | FROM ((((pg_index x + - | JOIN pg_class c ON ((c.oid = x.indrelid))) + - | JOIN pg_class i ON ((i.oid = x.indexrelid))) + - | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + - | LEFT JOIN pg_tablespace t ON ((t.oid = i.reltablespace))) + + pg_indexes | SELECT n.nspname AS schemaname, + + | c.relname AS tablename, + + | i.relname AS indexname, + + | t.spcname AS tablespace, + + | pg_get_indexdef(i.oid) AS indexdef + + | FROM ((((pg_index x + + | JOIN pg_class c ON ((c.oid = x.indrelid))) + + | JOIN pg_class i ON ((i.oid = x.indexrelid))) + + | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + + | LEFT JOIN pg_tablespace t ON ((t.oid = i.reltablespace))) + | WHERE ((c.relkind = ANY (ARRAY['r'::"char", 'm'::"char"])) AND (i.relkind = 'i'::"char")); - pg_locks | SELECT l.locktype, + - | l.database, + - | l.relation, + - | l.page, + - | l.tuple, + - | l.virtualxid, + - | l.transactionid, + - | l.classid, + - | l.objid, + - | l.objsubid, + - | l.virtualtransaction, + - | l.pid, + - | l.mode, + - | l.granted, + - | l.fastpath + + pg_locks | SELECT l.locktype, + + | l.database, + + | l.relation, + + | l.page, + + | l.tuple, + + | l.virtualxid, + + | l.transactionid, + + | l.classid, + + | l.objid, + + | l.objsubid, + + | l.virtualtransaction, + + | l.pid, + + | l.mode, + + | l.granted, + + | l.fastpath + | FROM pg_lock_status() l(locktype, database, relation, page, tuple, virtualxid, transactionid, classid, objid, objsubid, virtualtransaction, pid, mode, granted, fastpath); - pg_matviews | SELECT n.nspname AS schemaname, + - | c.relname AS matviewname, + - | pg_get_userbyid(c.relowner) AS matviewowner, + - | t.spcname AS tablespace, + - | c.relhasindex AS hasindexes, + - | c.relispopulated AS ispopulated, + - | pg_get_viewdef(c.oid) AS definition + - | FROM ((pg_class c + - | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + - | LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace))) + + pg_matviews | SELECT n.nspname AS schemaname, + + | c.relname AS matviewname, + + | pg_get_userbyid(c.relowner) AS matviewowner, + + | t.spcname AS tablespace, + + | c.relhasindex AS hasindexes, + + | c.relispopulated AS ispopulated, + + | pg_get_viewdef(c.oid) AS definition + + | FROM ((pg_class c + + | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + + | LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace))) + | WHERE (c.relkind = 'm'::"char"); - pg_prepared_statements | SELECT p.name, + - | p.statement, + - | p.prepare_time, + - | p.parameter_types, + - | p.from_sql + + pg_prepared_statements | SELECT p.name, + + | p.statement, + + | p.prepare_time, + + | p.parameter_types, + + | p.from_sql + | FROM pg_prepared_statement() p(name, statement, prepare_time, parameter_types, from_sql); - pg_prepared_xacts | SELECT p.transaction, + - | p.gid, + - | p.prepared, + - | u.rolname AS owner, + - | d.datname AS database + - | FROM ((pg_prepared_xact() p(transaction, gid, prepared, ownerid, dbid) + - | LEFT JOIN pg_authid u ON ((p.ownerid = u.oid))) + + pg_prepared_xacts | SELECT p.transaction, + + | p.gid, + + | p.prepared, + + | u.rolname AS owner, + + | d.datname AS database + + | FROM ((pg_prepared_xact() p(transaction, gid, prepared, ownerid, dbid) + + | LEFT JOIN pg_authid u ON ((p.ownerid = u.oid))) + | LEFT JOIN pg_database d ON ((p.dbid = d.oid))); - pg_roles | SELECT pg_authid.rolname, + - | pg_authid.rolsuper, + - | pg_authid.rolinherit, + - | pg_authid.rolcreaterole, + - | pg_authid.rolcreatedb, + - | pg_authid.rolcatupdate, + - | pg_authid.rolcanlogin, + - | pg_authid.rolreplication, + - | pg_authid.rolconnlimit, + - | '********'::text AS rolpassword, + - | pg_authid.rolvaliduntil, + - | s.setconfig AS rolconfig, + - | pg_authid.oid + - | FROM (pg_authid + + pg_roles | SELECT pg_authid.rolname, + + | pg_authid.rolsuper, + + | pg_authid.rolinherit, + + | pg_authid.rolcreaterole, + + | pg_authid.rolcreatedb, + + | pg_authid.rolcatupdate, + + | pg_authid.rolcanlogin, + + | pg_authid.rolreplication, + + | pg_authid.rolconnlimit, + + | '********'::text AS rolpassword, + + | pg_authid.rolvaliduntil, + + | s.setconfig AS rolconfig, + + | pg_authid.oid + + | FROM (pg_authid + | LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid)))); - pg_rules | SELECT n.nspname AS schemaname, + - | c.relname AS tablename, + - | r.rulename, + - | pg_get_ruledef(r.oid) AS definition + - | FROM ((pg_rewrite r + - | JOIN pg_class c ON ((c.oid = r.ev_class))) + - | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + + pg_rules | SELECT n.nspname AS schemaname, + + | c.relname AS tablename, + + | r.rulename, + + | pg_get_ruledef(r.oid) AS definition + + | FROM ((pg_rewrite r + + | JOIN pg_class c ON ((c.oid = r.ev_class))) + + | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + | WHERE (r.rulename <> '_RETURN'::name); - pg_seclabels | ( ( ( ( ( ( ( ( ( SELECT l.objoid, + - | l.classoid, + - | l.objsubid, + - | CASE + - | WHEN (rel.relkind = 'r'::"char") THEN 'table'::text + - | WHEN (rel.relkind = 'v'::"char") THEN 'view'::text + - | WHEN (rel.relkind = 'm'::"char") THEN 'materialized view'::text + - | WHEN (rel.relkind = 'S'::"char") THEN 'sequence'::text + - | WHEN (rel.relkind = 'f'::"char") THEN 'foreign table'::text + - | ELSE NULL::text + - | END AS objtype, + - | rel.relnamespace AS objnamespace, + - | CASE + - | WHEN pg_table_is_visible(rel.oid) THEN quote_ident((rel.relname)::text) + - | ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((rel.relname)::text)) + - | END AS objname, + - | l.provider, + - | l.label + - | FROM ((pg_seclabel l + - | JOIN pg_class rel ON (((l.classoid = rel.tableoid) AND (l.objoid = rel.oid)))) + - | JOIN pg_namespace nsp ON ((rel.relnamespace = nsp.oid))) + - | WHERE (l.objsubid = 0) + - | UNION ALL + - | SELECT l.objoid, + - | l.classoid, + - | l.objsubid, + - | 'column'::text AS objtype, + - | rel.relnamespace AS objnamespace, + - | (( + - | CASE + - | WHEN pg_table_is_visible(rel.oid) THEN quote_ident((rel.relname)::text) + - | ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((rel.relname)::text)) + - | END || '.'::text) || (att.attname)::text) AS objname, + - | l.provider, + - | l.label + - | FROM (((pg_seclabel l + - | JOIN pg_class rel ON (((l.classoid = rel.tableoid) AND (l.objoid = rel.oid)))) + - | JOIN pg_attribute att ON (((rel.oid = att.attrelid) AND (l.objsubid = att.attnum)))) + - | JOIN pg_namespace nsp ON ((rel.relnamespace = nsp.oid))) + - | WHERE (l.objsubid <> 0)) + - | UNION ALL + - | SELECT l.objoid, + - | l.classoid, + - | l.objsubid, + - | CASE + - | WHEN (pro.proisagg = true) THEN 'aggregate'::text + - | WHEN (pro.proisagg = false) THEN 'function'::text + - | ELSE NULL::text + - | END AS objtype, + - | pro.pronamespace AS objnamespace, + - | ((( + - | CASE + - | WHEN pg_function_is_visible(pro.oid) THEN quote_ident((pro.proname)::text) + - | ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((pro.proname)::text)) + - | END || '('::text) || pg_get_function_arguments(pro.oid)) || ')'::text) AS objname, + - | l.provider, + - | l.label + - | FROM ((pg_seclabel l + - | JOIN pg_proc pro ON (((l.classoid = pro.tableoid) AND (l.objoid = pro.oid)))) + - | JOIN pg_namespace nsp ON ((pro.pronamespace = nsp.oid))) + - | WHERE (l.objsubid = 0)) + - | UNION ALL + - | SELECT l.objoid, + - | l.classoid, + - | l.objsubid, + - | CASE + - | WHEN (typ.typtype = 'd'::"char") THEN 'domain'::text + - | ELSE 'type'::text + - | END AS objtype, + - | typ.typnamespace AS objnamespace, + - | CASE + - | WHEN pg_type_is_visible(typ.oid) THEN quote_ident((typ.typname)::text) + - | ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((typ.typname)::text)) + - | END AS objname, + - | l.provider, + - | l.label + - | FROM ((pg_seclabel l + - | JOIN pg_type typ ON (((l.classoid = typ.tableoid) AND (l.objoid = typ.oid)))) + - | JOIN pg_namespace nsp ON ((typ.typnamespace = nsp.oid))) + - | WHERE (l.objsubid = 0)) + - | UNION ALL + - | SELECT l.objoid, + - | l.classoid, + - | l.objsubid, + - | 'large object'::text AS objtype, + - | NULL::oid AS objnamespace, + - | (l.objoid)::text AS objname, + - | l.provider, + - | l.label + - | FROM (pg_seclabel l + - | JOIN pg_largeobject_metadata lom ON ((l.objoid = lom.oid))) + - | WHERE ((l.classoid = ('pg_largeobject'::regclass)::oid) AND (l.objsubid = 0))) + - | UNION ALL + - | SELECT l.objoid, + - | l.classoid, + - | l.objsubid, + - | 'language'::text AS objtype, + - | NULL::oid AS objnamespace, + - | quote_ident((lan.lanname)::text) AS objname, + - | l.provider, + - | l.label + - | FROM (pg_seclabel l + - | JOIN pg_language lan ON (((l.classoid = lan.tableoid) AND (l.objoid = lan.oid)))) + - | WHERE (l.objsubid = 0)) + - | UNION ALL + - | SELECT l.objoid, + - | l.classoid, + - | l.objsubid, + - | 'schema'::text AS objtype, + - | nsp.oid AS objnamespace, + - | quote_ident((nsp.nspname)::text) AS objname, + - | l.provider, + - | l.label + - | FROM (pg_seclabel l + - | JOIN pg_namespace nsp ON (((l.classoid = nsp.tableoid) AND (l.objoid = nsp.oid)))) + - | WHERE (l.objsubid = 0)) + - | UNION ALL + - | SELECT l.objoid, + - | l.classoid, + - | l.objsubid, + - | 'event trigger'::text AS objtype, + - | NULL::oid AS objnamespace, + - | quote_ident((evt.evtname)::text) AS objname, + - | l.provider, + - | l.label + - | FROM (pg_seclabel l + - | JOIN pg_event_trigger evt ON (((l.classoid = evt.tableoid) AND (l.objoid = evt.oid)))) + - | WHERE (l.objsubid = 0)) + - | UNION ALL + - | SELECT l.objoid, + - | l.classoid, + - | 0 AS objsubid, + - | 'database'::text AS objtype, + - | NULL::oid AS objnamespace, + - | quote_ident((dat.datname)::text) AS objname, + - | l.provider, + - | l.label + - | FROM (pg_shseclabel l + - | JOIN pg_database dat ON (((l.classoid = dat.tableoid) AND (l.objoid = dat.oid))))) + - | UNION ALL + - | SELECT l.objoid, + - | l.classoid, + - | 0 AS objsubid, + - | 'tablespace'::text AS objtype, + - | NULL::oid AS objnamespace, + - | quote_ident((spc.spcname)::text) AS objname, + - | l.provider, + - | l.label + - | FROM (pg_shseclabel l + - | JOIN pg_tablespace spc ON (((l.classoid = spc.tableoid) AND (l.objoid = spc.oid))))) + - | UNION ALL + - | SELECT l.objoid, + - | l.classoid, + - | 0 AS objsubid, + - | 'role'::text AS objtype, + - | NULL::oid AS objnamespace, + - | quote_ident((rol.rolname)::text) AS objname, + - | l.provider, + - | l.label + - | FROM (pg_shseclabel l + + pg_seclabels | ( ( ( ( ( ( ( ( ( SELECT l.objoid, + + | l.classoid, + + | l.objsubid, + + | CASE + + | WHEN (rel.relkind = 'r'::"char") THEN 'table'::text + + | WHEN (rel.relkind = 'v'::"char") THEN 'view'::text + + | WHEN (rel.relkind = 'm'::"char") THEN 'materialized view'::text + + | WHEN (rel.relkind = 'S'::"char") THEN 'sequence'::text + + | WHEN (rel.relkind = 'f'::"char") THEN 'foreign table'::text + + | ELSE NULL::text + + | END AS objtype, + + | rel.relnamespace AS objnamespace, + + | CASE + + | WHEN pg_table_is_visible(rel.oid) THEN quote_ident((rel.relname)::text) + + | ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((rel.relname)::text)) + + | END AS objname, + + | l.provider, + + | l.label + + | FROM ((pg_seclabel l + + | JOIN pg_class rel ON (((l.classoid = rel.tableoid) AND (l.objoid = rel.oid)))) + + | JOIN pg_namespace nsp ON ((rel.relnamespace = nsp.oid))) + + | WHERE (l.objsubid = 0) + + | UNION ALL + + | SELECT l.objoid, + + | l.classoid, + + | l.objsubid, + + | 'column'::text AS objtype, + + | rel.relnamespace AS objnamespace, + + | (( + + | CASE + + | WHEN pg_table_is_visible(rel.oid) THEN quote_ident((rel.relname)::text) + + | ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((rel.relname)::text)) + + | END || '.'::text) || (att.attname)::text) AS objname, + + | l.provider, + + | l.label + + | FROM (((pg_seclabel l + + | JOIN pg_class rel ON (((l.classoid = rel.tableoid) AND (l.objoid = rel.oid)))) + + | JOIN pg_attribute att ON (((rel.oid = att.attrelid) AND (l.objsubid = att.attnum)))) + + | JOIN pg_namespace nsp ON ((rel.relnamespace = nsp.oid))) + + | WHERE (l.objsubid <> 0)) + + | UNION ALL + + | SELECT l.objoid, + + | l.classoid, + + | l.objsubid, + + | CASE + + | WHEN (pro.proisagg = true) THEN 'aggregate'::text + + | WHEN (pro.proisagg = false) THEN 'function'::text + + | ELSE NULL::text + + | END AS objtype, + + | pro.pronamespace AS objnamespace, + + | ((( + + | CASE + + | WHEN pg_function_is_visible(pro.oid) THEN quote_ident((pro.proname)::text) + + | ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((pro.proname)::text)) + + | END || '('::text) || pg_get_function_arguments(pro.oid)) || ')'::text) AS objname, + + | l.provider, + + | l.label + + | FROM ((pg_seclabel l + + | JOIN pg_proc pro ON (((l.classoid = pro.tableoid) AND (l.objoid = pro.oid)))) + + | JOIN pg_namespace nsp ON ((pro.pronamespace = nsp.oid))) + + | WHERE (l.objsubid = 0)) + + | UNION ALL + + | SELECT l.objoid, + + | l.classoid, + + | l.objsubid, + + | CASE + + | WHEN (typ.typtype = 'd'::"char") THEN 'domain'::text + + | ELSE 'type'::text + + | END AS objtype, + + | typ.typnamespace AS objnamespace, + + | CASE + + | WHEN pg_type_is_visible(typ.oid) THEN quote_ident((typ.typname)::text) + + | ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((typ.typname)::text)) + + | END AS objname, + + | l.provider, + + | l.label + + | FROM ((pg_seclabel l + + | JOIN pg_type typ ON (((l.classoid = typ.tableoid) AND (l.objoid = typ.oid)))) + + | JOIN pg_namespace nsp ON ((typ.typnamespace = nsp.oid))) + + | WHERE (l.objsubid = 0)) + + | UNION ALL + + | SELECT l.objoid, + + | l.classoid, + + | l.objsubid, + + | 'large object'::text AS objtype, + + | NULL::oid AS objnamespace, + + | (l.objoid)::text AS objname, + + | l.provider, + + | l.label + + | FROM (pg_seclabel l + + | JOIN pg_largeobject_metadata lom ON ((l.objoid = lom.oid))) + + | WHERE ((l.classoid = ('pg_largeobject'::regclass)::oid) AND (l.objsubid = 0))) + + | UNION ALL + + | SELECT l.objoid, + + | l.classoid, + + | l.objsubid, + + | 'language'::text AS objtype, + + | NULL::oid AS objnamespace, + + | quote_ident((lan.lanname)::text) AS objname, + + | l.provider, + + | l.label + + | FROM (pg_seclabel l + + | JOIN pg_language lan ON (((l.classoid = lan.tableoid) AND (l.objoid = lan.oid)))) + + | WHERE (l.objsubid = 0)) + + | UNION ALL + + | SELECT l.objoid, + + | l.classoid, + + | l.objsubid, + + | 'schema'::text AS objtype, + + | nsp.oid AS objnamespace, + + | quote_ident((nsp.nspname)::text) AS objname, + + | l.provider, + + | l.label + + | FROM (pg_seclabel l + + | JOIN pg_namespace nsp ON (((l.classoid = nsp.tableoid) AND (l.objoid = nsp.oid)))) + + | WHERE (l.objsubid = 0)) + + | UNION ALL + + | SELECT l.objoid, + + | l.classoid, + + | l.objsubid, + + | 'event trigger'::text AS objtype, + + | NULL::oid AS objnamespace, + + | quote_ident((evt.evtname)::text) AS objname, + + | l.provider, + + | l.label + + | FROM (pg_seclabel l + + | JOIN pg_event_trigger evt ON (((l.classoid = evt.tableoid) AND (l.objoid = evt.oid)))) + + | WHERE (l.objsubid = 0)) + + | UNION ALL + + | SELECT l.objoid, + + | l.classoid, + + | 0 AS objsubid, + + | 'database'::text AS objtype, + + | NULL::oid AS objnamespace, + + | quote_ident((dat.datname)::text) AS objname, + + | l.provider, + + | l.label + + | FROM (pg_shseclabel l + + | JOIN pg_database dat ON (((l.classoid = dat.tableoid) AND (l.objoid = dat.oid))))) + + | UNION ALL + + | SELECT l.objoid, + + | l.classoid, + + | 0 AS objsubid, + + | 'tablespace'::text AS objtype, + + | NULL::oid AS objnamespace, + + | quote_ident((spc.spcname)::text) AS objname, + + | l.provider, + + | l.label + + | FROM (pg_shseclabel l + + | JOIN pg_tablespace spc ON (((l.classoid = spc.tableoid) AND (l.objoid = spc.oid))))) + + | UNION ALL + + | SELECT l.objoid, + + | l.classoid, + + | 0 AS objsubid, + + | 'role'::text AS objtype, + + | NULL::oid AS objnamespace, + + | quote_ident((rol.rolname)::text) AS objname, + + | l.provider, + + | l.label + + | FROM (pg_shseclabel l + | JOIN pg_authid rol ON (((l.classoid = rol.tableoid) AND (l.objoid = rol.oid)))); - pg_settings | SELECT a.name, + - | a.setting, + - | a.unit, + - | a.category, + - | a.short_desc, + - | a.extra_desc, + - | a.context, + - | a.vartype, + - | a.source, + - | a.min_val, + - | a.max_val, + - | a.enumvals, + - | a.boot_val, + - | a.reset_val, + - | a.sourcefile, + - | a.sourceline + + pg_settings | SELECT a.name, + + | a.setting, + + | a.unit, + + | a.category, + + | a.short_desc, + + | a.extra_desc, + + | a.context, + + | a.vartype, + + | a.source, + + | a.min_val, + + | a.max_val, + + | a.enumvals, + + | a.boot_val, + + | a.reset_val, + + | a.sourcefile, + + | a.sourceline + | FROM pg_show_all_settings() a(name, setting, unit, category, short_desc, extra_desc, context, vartype, source, min_val, max_val, enumvals, boot_val, reset_val, sourcefile, sourceline); - pg_shadow | SELECT pg_authid.rolname AS usename, + - | pg_authid.oid AS usesysid, + - | pg_authid.rolcreatedb AS usecreatedb, + - | pg_authid.rolsuper AS usesuper, + - | pg_authid.rolcatupdate AS usecatupd, + - | pg_authid.rolreplication AS userepl, + - | pg_authid.rolpassword AS passwd, + - | (pg_authid.rolvaliduntil)::abstime AS valuntil, + - | s.setconfig AS useconfig + - | FROM (pg_authid + - | LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid)))) + + pg_shadow | SELECT pg_authid.rolname AS usename, + + | pg_authid.oid AS usesysid, + + | pg_authid.rolcreatedb AS usecreatedb, + + | pg_authid.rolsuper AS usesuper, + + | pg_authid.rolcatupdate AS usecatupd, + + | pg_authid.rolreplication AS userepl, + + | pg_authid.rolpassword AS passwd, + + | (pg_authid.rolvaliduntil)::abstime AS valuntil, + + | s.setconfig AS useconfig + + | FROM (pg_authid + + | LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid)))) + | WHERE pg_authid.rolcanlogin; - pg_stat_activity | SELECT s.datid, + - | d.datname, + - | s.pid, + - | s.usesysid, + - | u.rolname AS usename, + - | s.application_name, + - | s.client_addr, + - | s.client_hostname, + - | s.client_port, + - | s.backend_start, + - | s.xact_start, + - | s.query_start, + - | s.state_change, + - | s.waiting, + - | s.state, + - | s.query + - | FROM pg_database d, + - | pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port), + - | pg_authid u + + pg_stat_activity | SELECT s.datid, + + | d.datname, + + | s.pid, + + | s.usesysid, + + | u.rolname AS usename, + + | s.application_name, + + | s.client_addr, + + | s.client_hostname, + + | s.client_port, + + | s.backend_start, + + | s.xact_start, + + | s.query_start, + + | s.state_change, + + | s.waiting, + + | s.state, + + | s.query + + | FROM pg_database d, + + | pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port), + + | pg_authid u + | WHERE ((s.datid = d.oid) AND (s.usesysid = u.oid)); - pg_stat_all_indexes | SELECT c.oid AS relid, + - | i.oid AS indexrelid, + - | n.nspname AS schemaname, + - | c.relname, + - | i.relname AS indexrelname, + - | pg_stat_get_numscans(i.oid) AS idx_scan, + - | pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, + - | pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch + - | FROM (((pg_class c + - | JOIN pg_index x ON ((c.oid = x.indrelid))) + - | JOIN pg_class i ON ((i.oid = x.indexrelid))) + - | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + + pg_stat_all_indexes | SELECT c.oid AS relid, + + | i.oid AS indexrelid, + + | n.nspname AS schemaname, + + | c.relname, + + | i.relname AS indexrelname, + + | pg_stat_get_numscans(i.oid) AS idx_scan, + + | pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, + + | pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch + + | FROM (((pg_class c + + | JOIN pg_index x ON ((c.oid = x.indrelid))) + + | JOIN pg_class i ON ((i.oid = x.indexrelid))) + + | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + | WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"])); - pg_stat_all_tables | SELECT c.oid AS relid, + - | n.nspname AS schemaname, + - | c.relname, + - | pg_stat_get_numscans(c.oid) AS seq_scan, + - | pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, + - | (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan, + - | ((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch, + - | pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, + - | pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, + - | pg_stat_get_tuples_deleted(c.oid) AS n_tup_del, + - | pg_stat_get_tuples_hot_updated(c.oid) AS n_tup_hot_upd, + - | pg_stat_get_live_tuples(c.oid) AS n_live_tup, + - | pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, + - | pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum, + - | pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, + - | pg_stat_get_last_analyze_time(c.oid) AS last_analyze, + - | pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze, + - | pg_stat_get_vacuum_count(c.oid) AS vacuum_count, + - | pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count, + - | pg_stat_get_analyze_count(c.oid) AS analyze_count, + - | pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count + - | FROM ((pg_class c + - | LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) + - | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + - | WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"])) + + pg_stat_all_tables | SELECT c.oid AS relid, + + | n.nspname AS schemaname, + + | c.relname, + + | pg_stat_get_numscans(c.oid) AS seq_scan, + + | pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, + + | (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan, + + | ((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch, + + | pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, + + | pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, + + | pg_stat_get_tuples_deleted(c.oid) AS n_tup_del, + + | pg_stat_get_tuples_hot_updated(c.oid) AS n_tup_hot_upd, + + | pg_stat_get_live_tuples(c.oid) AS n_live_tup, + + | pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, + + | pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum, + + | pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, + + | pg_stat_get_last_analyze_time(c.oid) AS last_analyze, + + | pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze, + + | pg_stat_get_vacuum_count(c.oid) AS vacuum_count, + + | pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count, + + | pg_stat_get_analyze_count(c.oid) AS analyze_count, + + | pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count + + | FROM ((pg_class c + + | LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) + + | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + + | WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"])) + | GROUP BY c.oid, n.nspname, c.relname; - pg_stat_bgwriter | SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed, + - | pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req, + - | pg_stat_get_checkpoint_write_time() AS checkpoint_write_time, + - | pg_stat_get_checkpoint_sync_time() AS checkpoint_sync_time, + - | pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint, + - | pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean, + - | pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean, + - | pg_stat_get_buf_written_backend() AS buffers_backend, + - | pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync, + - | pg_stat_get_buf_alloc() AS buffers_alloc, + + pg_stat_bgwriter | SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed, + + | pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req, + + | pg_stat_get_checkpoint_write_time() AS checkpoint_write_time, + + | pg_stat_get_checkpoint_sync_time() AS checkpoint_sync_time, + + | pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint, + + | pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean, + + | pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean, + + | pg_stat_get_buf_written_backend() AS buffers_backend, + + | pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync, + + | pg_stat_get_buf_alloc() AS buffers_alloc, + | pg_stat_get_bgwriter_stat_reset_time() AS stats_reset; - pg_stat_database | SELECT d.oid AS datid, + - | d.datname, + - | pg_stat_get_db_numbackends(d.oid) AS numbackends, + - | pg_stat_get_db_xact_commit(d.oid) AS xact_commit, + - | pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback, + - | (pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid)) AS blks_read, + - | pg_stat_get_db_blocks_hit(d.oid) AS blks_hit, + - | pg_stat_get_db_tuples_returned(d.oid) AS tup_returned, + - | pg_stat_get_db_tuples_fetched(d.oid) AS tup_fetched, + - | pg_stat_get_db_tuples_inserted(d.oid) AS tup_inserted, + - | pg_stat_get_db_tuples_updated(d.oid) AS tup_updated, + - | pg_stat_get_db_tuples_deleted(d.oid) AS tup_deleted, + - | pg_stat_get_db_conflict_all(d.oid) AS conflicts, + - | pg_stat_get_db_temp_files(d.oid) AS temp_files, + - | pg_stat_get_db_temp_bytes(d.oid) AS temp_bytes, + - | pg_stat_get_db_deadlocks(d.oid) AS deadlocks, + - | pg_stat_get_db_blk_read_time(d.oid) AS blk_read_time, + - | pg_stat_get_db_blk_write_time(d.oid) AS blk_write_time, + - | pg_stat_get_db_stat_reset_time(d.oid) AS stats_reset + + pg_stat_database | SELECT d.oid AS datid, + + | d.datname, + + | pg_stat_get_db_numbackends(d.oid) AS numbackends, + + | pg_stat_get_db_xact_commit(d.oid) AS xact_commit, + + | pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback, + + | (pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid)) AS blks_read, + + | pg_stat_get_db_blocks_hit(d.oid) AS blks_hit, + + | pg_stat_get_db_tuples_returned(d.oid) AS tup_returned, + + | pg_stat_get_db_tuples_fetched(d.oid) AS tup_fetched, + + | pg_stat_get_db_tuples_inserted(d.oid) AS tup_inserted, + + | pg_stat_get_db_tuples_updated(d.oid) AS tup_updated, + + | pg_stat_get_db_tuples_deleted(d.oid) AS tup_deleted, + + | pg_stat_get_db_conflict_all(d.oid) AS conflicts, + + | pg_stat_get_db_temp_files(d.oid) AS temp_files, + + | pg_stat_get_db_temp_bytes(d.oid) AS temp_bytes, + + | pg_stat_get_db_deadlocks(d.oid) AS deadlocks, + + | pg_stat_get_db_blk_read_time(d.oid) AS blk_read_time, + + | pg_stat_get_db_blk_write_time(d.oid) AS blk_write_time, + + | pg_stat_get_db_stat_reset_time(d.oid) AS stats_reset + | FROM pg_database d; - pg_stat_database_conflicts | SELECT d.oid AS datid, + - | d.datname, + - | pg_stat_get_db_conflict_tablespace(d.oid) AS confl_tablespace, + - | pg_stat_get_db_conflict_lock(d.oid) AS confl_lock, + - | pg_stat_get_db_conflict_snapshot(d.oid) AS confl_snapshot, + - | pg_stat_get_db_conflict_bufferpin(d.oid) AS confl_bufferpin, + - | pg_stat_get_db_conflict_startup_deadlock(d.oid) AS confl_deadlock + + pg_stat_database_conflicts | SELECT d.oid AS datid, + + | d.datname, + + | pg_stat_get_db_conflict_tablespace(d.oid) AS confl_tablespace, + + | pg_stat_get_db_conflict_lock(d.oid) AS confl_lock, + + | pg_stat_get_db_conflict_snapshot(d.oid) AS confl_snapshot, + + | pg_stat_get_db_conflict_bufferpin(d.oid) AS confl_bufferpin, + + | pg_stat_get_db_conflict_startup_deadlock(d.oid) AS confl_deadlock + | FROM pg_database d; - pg_stat_replication | SELECT s.pid, + - | s.usesysid, + - | u.rolname AS usename, + - | s.application_name, + - | s.client_addr, + - | s.client_hostname, + - | s.client_port, + - | s.backend_start, + - | w.state, + - | w.sent_location, + - | w.write_location, + - | w.flush_location, + - | w.replay_location, + - | w.sync_priority, + - | w.sync_state + - | FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port), + - | pg_authid u, + - | pg_stat_get_wal_senders() w(pid, state, sent_location, write_location, flush_location, replay_location, sync_priority, sync_state) + + pg_stat_replication | SELECT s.pid, + + | s.usesysid, + + | u.rolname AS usename, + + | s.application_name, + + | s.client_addr, + + | s.client_hostname, + + | s.client_port, + + | s.backend_start, + + | w.state, + + | w.sent_location, + + | w.write_location, + + | w.flush_location, + + | w.replay_location, + + | w.sync_priority, + + | w.sync_state + + | FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port),+ + | pg_authid u, + + | pg_stat_get_wal_senders() w(pid, state, sent_location, write_location, flush_location, replay_location, sync_priority, sync_state) + | WHERE ((s.usesysid = u.oid) AND (s.pid = w.pid)); - pg_stat_sys_indexes | SELECT pg_stat_all_indexes.relid, + - | pg_stat_all_indexes.indexrelid, + - | pg_stat_all_indexes.schemaname, + - | pg_stat_all_indexes.relname, + - | pg_stat_all_indexes.indexrelname, + - | pg_stat_all_indexes.idx_scan, + - | pg_stat_all_indexes.idx_tup_read, + - | pg_stat_all_indexes.idx_tup_fetch + - | FROM pg_stat_all_indexes + + pg_stat_sys_indexes | SELECT pg_stat_all_indexes.relid, + + | pg_stat_all_indexes.indexrelid, + + | pg_stat_all_indexes.schemaname, + + | pg_stat_all_indexes.relname, + + | pg_stat_all_indexes.indexrelname, + + | pg_stat_all_indexes.idx_scan, + + | pg_stat_all_indexes.idx_tup_read, + + | pg_stat_all_indexes.idx_tup_fetch + + | FROM pg_stat_all_indexes + | WHERE ((pg_stat_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_indexes.schemaname ~ '^pg_toast'::text)); - pg_stat_sys_tables | SELECT pg_stat_all_tables.relid, + - | pg_stat_all_tables.schemaname, + - | pg_stat_all_tables.relname, + - | pg_stat_all_tables.seq_scan, + - | pg_stat_all_tables.seq_tup_read, + - | pg_stat_all_tables.idx_scan, + - | pg_stat_all_tables.idx_tup_fetch, + - | pg_stat_all_tables.n_tup_ins, + - | pg_stat_all_tables.n_tup_upd, + - | pg_stat_all_tables.n_tup_del, + - | pg_stat_all_tables.n_tup_hot_upd, + - | pg_stat_all_tables.n_live_tup, + - | pg_stat_all_tables.n_dead_tup, + - | pg_stat_all_tables.last_vacuum, + - | pg_stat_all_tables.last_autovacuum, + - | pg_stat_all_tables.last_analyze, + - | pg_stat_all_tables.last_autoanalyze, + - | pg_stat_all_tables.vacuum_count, + - | pg_stat_all_tables.autovacuum_count, + - | pg_stat_all_tables.analyze_count, + - | pg_stat_all_tables.autoanalyze_count + - | FROM pg_stat_all_tables + + pg_stat_sys_tables | SELECT pg_stat_all_tables.relid, + + | pg_stat_all_tables.schemaname, + + | pg_stat_all_tables.relname, + + | pg_stat_all_tables.seq_scan, + + | pg_stat_all_tables.seq_tup_read, + + | pg_stat_all_tables.idx_scan, + + | pg_stat_all_tables.idx_tup_fetch, + + | pg_stat_all_tables.n_tup_ins, + + | pg_stat_all_tables.n_tup_upd, + + | pg_stat_all_tables.n_tup_del, + + | pg_stat_all_tables.n_tup_hot_upd, + + | pg_stat_all_tables.n_live_tup, + + | pg_stat_all_tables.n_dead_tup, + + | pg_stat_all_tables.last_vacuum, + + | pg_stat_all_tables.last_autovacuum, + + | pg_stat_all_tables.last_analyze, + + | pg_stat_all_tables.last_autoanalyze, + + | pg_stat_all_tables.vacuum_count, + + | pg_stat_all_tables.autovacuum_count, + + | pg_stat_all_tables.analyze_count, + + | pg_stat_all_tables.autoanalyze_count + + | FROM pg_stat_all_tables + | WHERE ((pg_stat_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_tables.schemaname ~ '^pg_toast'::text)); - pg_stat_user_functions | SELECT p.oid AS funcid, + - | n.nspname AS schemaname, + - | p.proname AS funcname, + - | pg_stat_get_function_calls(p.oid) AS calls, + - | pg_stat_get_function_total_time(p.oid) AS total_time, + - | pg_stat_get_function_self_time(p.oid) AS self_time + - | FROM (pg_proc p + - | LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace))) + + pg_stat_user_functions | SELECT p.oid AS funcid, + + | n.nspname AS schemaname, + + | p.proname AS funcname, + + | pg_stat_get_function_calls(p.oid) AS calls, + + | pg_stat_get_function_total_time(p.oid) AS total_time, + + | pg_stat_get_function_self_time(p.oid) AS self_time + + | FROM (pg_proc p + + | LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace))) + | WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_function_calls(p.oid) IS NOT NULL)); - pg_stat_user_indexes | SELECT pg_stat_all_indexes.relid, + - | pg_stat_all_indexes.indexrelid, + - | pg_stat_all_indexes.schemaname, + - | pg_stat_all_indexes.relname, + - | pg_stat_all_indexes.indexrelname, + - | pg_stat_all_indexes.idx_scan, + - | pg_stat_all_indexes.idx_tup_read, + - | pg_stat_all_indexes.idx_tup_fetch + - | FROM pg_stat_all_indexes + + pg_stat_user_indexes | SELECT pg_stat_all_indexes.relid, + + | pg_stat_all_indexes.indexrelid, + + | pg_stat_all_indexes.schemaname, + + | pg_stat_all_indexes.relname, + + | pg_stat_all_indexes.indexrelname, + + | pg_stat_all_indexes.idx_scan, + + | pg_stat_all_indexes.idx_tup_read, + + | pg_stat_all_indexes.idx_tup_fetch + + | FROM pg_stat_all_indexes + | WHERE ((pg_stat_all_indexes.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_indexes.schemaname !~ '^pg_toast'::text)); - pg_stat_user_tables | SELECT pg_stat_all_tables.relid, + - | pg_stat_all_tables.schemaname, + - | pg_stat_all_tables.relname, + - | pg_stat_all_tables.seq_scan, + - | pg_stat_all_tables.seq_tup_read, + - | pg_stat_all_tables.idx_scan, + - | pg_stat_all_tables.idx_tup_fetch, + - | pg_stat_all_tables.n_tup_ins, + - | pg_stat_all_tables.n_tup_upd, + - | pg_stat_all_tables.n_tup_del, + - | pg_stat_all_tables.n_tup_hot_upd, + - | pg_stat_all_tables.n_live_tup, + - | pg_stat_all_tables.n_dead_tup, + - | pg_stat_all_tables.last_vacuum, + - | pg_stat_all_tables.last_autovacuum, + - | pg_stat_all_tables.last_analyze, + - | pg_stat_all_tables.last_autoanalyze, + - | pg_stat_all_tables.vacuum_count, + - | pg_stat_all_tables.autovacuum_count, + - | pg_stat_all_tables.analyze_count, + - | pg_stat_all_tables.autoanalyze_count + - | FROM pg_stat_all_tables + + pg_stat_user_tables | SELECT pg_stat_all_tables.relid, + + | pg_stat_all_tables.schemaname, + + | pg_stat_all_tables.relname, + + | pg_stat_all_tables.seq_scan, + + | pg_stat_all_tables.seq_tup_read, + + | pg_stat_all_tables.idx_scan, + + | pg_stat_all_tables.idx_tup_fetch, + + | pg_stat_all_tables.n_tup_ins, + + | pg_stat_all_tables.n_tup_upd, + + | pg_stat_all_tables.n_tup_del, + + | pg_stat_all_tables.n_tup_hot_upd, + + | pg_stat_all_tables.n_live_tup, + + | pg_stat_all_tables.n_dead_tup, + + | pg_stat_all_tables.last_vacuum, + + | pg_stat_all_tables.last_autovacuum, + + | pg_stat_all_tables.last_analyze, + + | pg_stat_all_tables.last_autoanalyze, + + | pg_stat_all_tables.vacuum_count, + + | pg_stat_all_tables.autovacuum_count, + + | pg_stat_all_tables.analyze_count, + + | pg_stat_all_tables.autoanalyze_count + + | FROM pg_stat_all_tables + | WHERE ((pg_stat_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_tables.schemaname !~ '^pg_toast'::text)); - pg_stat_xact_all_tables | SELECT c.oid AS relid, + - | n.nspname AS schemaname, + - | c.relname, + - | pg_stat_get_xact_numscans(c.oid) AS seq_scan, + - | pg_stat_get_xact_tuples_returned(c.oid) AS seq_tup_read, + - | (sum(pg_stat_get_xact_numscans(i.indexrelid)))::bigint AS idx_scan, + - | ((sum(pg_stat_get_xact_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_xact_tuples_fetched(c.oid)) AS idx_tup_fetch, + - | pg_stat_get_xact_tuples_inserted(c.oid) AS n_tup_ins, + - | pg_stat_get_xact_tuples_updated(c.oid) AS n_tup_upd, + - | pg_stat_get_xact_tuples_deleted(c.oid) AS n_tup_del, + - | pg_stat_get_xact_tuples_hot_updated(c.oid) AS n_tup_hot_upd + - | FROM ((pg_class c + - | LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) + - | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + - | WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"])) + + pg_stat_xact_all_tables | SELECT c.oid AS relid, + + | n.nspname AS schemaname, + + | c.relname, + + | pg_stat_get_xact_numscans(c.oid) AS seq_scan, + + | pg_stat_get_xact_tuples_returned(c.oid) AS seq_tup_read, + + | (sum(pg_stat_get_xact_numscans(i.indexrelid)))::bigint AS idx_scan, + + | ((sum(pg_stat_get_xact_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_xact_tuples_fetched(c.oid)) AS idx_tup_fetch, + + | pg_stat_get_xact_tuples_inserted(c.oid) AS n_tup_ins, + + | pg_stat_get_xact_tuples_updated(c.oid) AS n_tup_upd, + + | pg_stat_get_xact_tuples_deleted(c.oid) AS n_tup_del, + + | pg_stat_get_xact_tuples_hot_updated(c.oid) AS n_tup_hot_upd + + | FROM ((pg_class c + + | LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) + + | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + + | WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"])) + | GROUP BY c.oid, n.nspname, c.relname; - pg_stat_xact_sys_tables | SELECT pg_stat_xact_all_tables.relid, + - | pg_stat_xact_all_tables.schemaname, + - | pg_stat_xact_all_tables.relname, + - | pg_stat_xact_all_tables.seq_scan, + - | pg_stat_xact_all_tables.seq_tup_read, + - | pg_stat_xact_all_tables.idx_scan, + - | pg_stat_xact_all_tables.idx_tup_fetch, + - | pg_stat_xact_all_tables.n_tup_ins, + - | pg_stat_xact_all_tables.n_tup_upd, + - | pg_stat_xact_all_tables.n_tup_del, + - | pg_stat_xact_all_tables.n_tup_hot_upd + - | FROM pg_stat_xact_all_tables + + pg_stat_xact_sys_tables | SELECT pg_stat_xact_all_tables.relid, + + | pg_stat_xact_all_tables.schemaname, + + | pg_stat_xact_all_tables.relname, + + | pg_stat_xact_all_tables.seq_scan, + + | pg_stat_xact_all_tables.seq_tup_read, + + | pg_stat_xact_all_tables.idx_scan, + + | pg_stat_xact_all_tables.idx_tup_fetch, + + | pg_stat_xact_all_tables.n_tup_ins, + + | pg_stat_xact_all_tables.n_tup_upd, + + | pg_stat_xact_all_tables.n_tup_del, + + | pg_stat_xact_all_tables.n_tup_hot_upd + + | FROM pg_stat_xact_all_tables + | WHERE ((pg_stat_xact_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_xact_all_tables.schemaname ~ '^pg_toast'::text)); - pg_stat_xact_user_functions | SELECT p.oid AS funcid, + - | n.nspname AS schemaname, + - | p.proname AS funcname, + - | pg_stat_get_xact_function_calls(p.oid) AS calls, + - | pg_stat_get_xact_function_total_time(p.oid) AS total_time, + - | pg_stat_get_xact_function_self_time(p.oid) AS self_time + - | FROM (pg_proc p + - | LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace))) + + pg_stat_xact_user_functions | SELECT p.oid AS funcid, + + | n.nspname AS schemaname, + + | p.proname AS funcname, + + | pg_stat_get_xact_function_calls(p.oid) AS calls, + + | pg_stat_get_xact_function_total_time(p.oid) AS total_time, + + | pg_stat_get_xact_function_self_time(p.oid) AS self_time + + | FROM (pg_proc p + + | LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace))) + | WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_xact_function_calls(p.oid) IS NOT NULL)); - pg_stat_xact_user_tables | SELECT pg_stat_xact_all_tables.relid, + - | pg_stat_xact_all_tables.schemaname, + - | pg_stat_xact_all_tables.relname, + - | pg_stat_xact_all_tables.seq_scan, + - | pg_stat_xact_all_tables.seq_tup_read, + - | pg_stat_xact_all_tables.idx_scan, + - | pg_stat_xact_all_tables.idx_tup_fetch, + - | pg_stat_xact_all_tables.n_tup_ins, + - | pg_stat_xact_all_tables.n_tup_upd, + - | pg_stat_xact_all_tables.n_tup_del, + - | pg_stat_xact_all_tables.n_tup_hot_upd + - | FROM pg_stat_xact_all_tables + + pg_stat_xact_user_tables | SELECT pg_stat_xact_all_tables.relid, + + | pg_stat_xact_all_tables.schemaname, + + | pg_stat_xact_all_tables.relname, + + | pg_stat_xact_all_tables.seq_scan, + + | pg_stat_xact_all_tables.seq_tup_read, + + | pg_stat_xact_all_tables.idx_scan, + + | pg_stat_xact_all_tables.idx_tup_fetch, + + | pg_stat_xact_all_tables.n_tup_ins, + + | pg_stat_xact_all_tables.n_tup_upd, + + | pg_stat_xact_all_tables.n_tup_del, + + | pg_stat_xact_all_tables.n_tup_hot_upd + + | FROM pg_stat_xact_all_tables + | WHERE ((pg_stat_xact_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_xact_all_tables.schemaname !~ '^pg_toast'::text)); - pg_statio_all_indexes | SELECT c.oid AS relid, + - | i.oid AS indexrelid, + - | n.nspname AS schemaname, + - | c.relname, + - | i.relname AS indexrelname, + - | (pg_stat_get_blocks_fetched(i.oid) - pg_stat_get_blocks_hit(i.oid)) AS idx_blks_read, + - | pg_stat_get_blocks_hit(i.oid) AS idx_blks_hit + - | FROM (((pg_class c + - | JOIN pg_index x ON ((c.oid = x.indrelid))) + - | JOIN pg_class i ON ((i.oid = x.indexrelid))) + - | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + + pg_statio_all_indexes | SELECT c.oid AS relid, + + | i.oid AS indexrelid, + + | n.nspname AS schemaname, + + | c.relname, + + | i.relname AS indexrelname, + + | (pg_stat_get_blocks_fetched(i.oid) - pg_stat_get_blocks_hit(i.oid)) AS idx_blks_read, + + | pg_stat_get_blocks_hit(i.oid) AS idx_blks_hit + + | FROM (((pg_class c + + | JOIN pg_index x ON ((c.oid = x.indrelid))) + + | JOIN pg_class i ON ((i.oid = x.indexrelid))) + + | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + | WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"])); - pg_statio_all_sequences | SELECT c.oid AS relid, + - | n.nspname AS schemaname, + - | c.relname, + - | (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS blks_read, + - | pg_stat_get_blocks_hit(c.oid) AS blks_hit + - | FROM (pg_class c + - | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + + pg_statio_all_sequences | SELECT c.oid AS relid, + + | n.nspname AS schemaname, + + | c.relname, + + | (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS blks_read, + + | pg_stat_get_blocks_hit(c.oid) AS blks_hit + + | FROM (pg_class c + + | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + | WHERE (c.relkind = 'S'::"char"); - pg_statio_all_tables | SELECT c.oid AS relid, + - | n.nspname AS schemaname, + - | c.relname, + - | (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS heap_blks_read, + - | pg_stat_get_blocks_hit(c.oid) AS heap_blks_hit, + - | (sum((pg_stat_get_blocks_fetched(i.indexrelid) - pg_stat_get_blocks_hit(i.indexrelid))))::bigint AS idx_blks_read, + - | (sum(pg_stat_get_blocks_hit(i.indexrelid)))::bigint AS idx_blks_hit, + - | (pg_stat_get_blocks_fetched(t.oid) - pg_stat_get_blocks_hit(t.oid)) AS toast_blks_read, + - | pg_stat_get_blocks_hit(t.oid) AS toast_blks_hit, + - | (pg_stat_get_blocks_fetched(x.oid) - pg_stat_get_blocks_hit(x.oid)) AS tidx_blks_read, + - | pg_stat_get_blocks_hit(x.oid) AS tidx_blks_hit + - | FROM ((((pg_class c + - | LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) + - | LEFT JOIN pg_class t ON ((c.reltoastrelid = t.oid))) + - | LEFT JOIN pg_class x ON ((t.reltoastidxid = x.oid))) + - | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + - | WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"])) + + pg_statio_all_tables | SELECT c.oid AS relid, + + | n.nspname AS schemaname, + + | c.relname, + + | (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS heap_blks_read, + + | pg_stat_get_blocks_hit(c.oid) AS heap_blks_hit, + + | (sum((pg_stat_get_blocks_fetched(i.indexrelid) - pg_stat_get_blocks_hit(i.indexrelid))))::bigint AS idx_blks_read, + + | (sum(pg_stat_get_blocks_hit(i.indexrelid)))::bigint AS idx_blks_hit, + + | (pg_stat_get_blocks_fetched(t.oid) - pg_stat_get_blocks_hit(t.oid)) AS toast_blks_read, + + | pg_stat_get_blocks_hit(t.oid) AS toast_blks_hit, + + | (pg_stat_get_blocks_fetched(x.oid) - pg_stat_get_blocks_hit(x.oid)) AS tidx_blks_read, + + | pg_stat_get_blocks_hit(x.oid) AS tidx_blks_hit + + | FROM ((((pg_class c + + | LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) + + | LEFT JOIN pg_class t ON ((c.reltoastrelid = t.oid))) + + | LEFT JOIN pg_class x ON ((t.reltoastidxid = x.oid))) + + | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + + | WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"])) + | GROUP BY c.oid, n.nspname, c.relname, t.oid, x.oid; - pg_statio_sys_indexes | SELECT pg_statio_all_indexes.relid, + - | pg_statio_all_indexes.indexrelid, + - | pg_statio_all_indexes.schemaname, + - | pg_statio_all_indexes.relname, + - | pg_statio_all_indexes.indexrelname, + - | pg_statio_all_indexes.idx_blks_read, + - | pg_statio_all_indexes.idx_blks_hit + - | FROM pg_statio_all_indexes + + pg_statio_sys_indexes | SELECT pg_statio_all_indexes.relid, + + | pg_statio_all_indexes.indexrelid, + + | pg_statio_all_indexes.schemaname, + + | pg_statio_all_indexes.relname, + + | pg_statio_all_indexes.indexrelname, + + | pg_statio_all_indexes.idx_blks_read, + + | pg_statio_all_indexes.idx_blks_hit + + | FROM pg_statio_all_indexes + | WHERE ((pg_statio_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_indexes.schemaname ~ '^pg_toast'::text)); - pg_statio_sys_sequences | SELECT pg_statio_all_sequences.relid, + - | pg_statio_all_sequences.schemaname, + - | pg_statio_all_sequences.relname, + - | pg_statio_all_sequences.blks_read, + - | pg_statio_all_sequences.blks_hit + - | FROM pg_statio_all_sequences + + pg_statio_sys_sequences | SELECT pg_statio_all_sequences.relid, + + | pg_statio_all_sequences.schemaname, + + | pg_statio_all_sequences.relname, + + | pg_statio_all_sequences.blks_read, + + | pg_statio_all_sequences.blks_hit + + | FROM pg_statio_all_sequences + | WHERE ((pg_statio_all_sequences.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_sequences.schemaname ~ '^pg_toast'::text)); - pg_statio_sys_tables | SELECT pg_statio_all_tables.relid, + - | pg_statio_all_tables.schemaname, + - | pg_statio_all_tables.relname, + - | pg_statio_all_tables.heap_blks_read, + - | pg_statio_all_tables.heap_blks_hit, + - | pg_statio_all_tables.idx_blks_read, + - | pg_statio_all_tables.idx_blks_hit, + - | pg_statio_all_tables.toast_blks_read, + - | pg_statio_all_tables.toast_blks_hit, + - | pg_statio_all_tables.tidx_blks_read, + - | pg_statio_all_tables.tidx_blks_hit + - | FROM pg_statio_all_tables + + pg_statio_sys_tables | SELECT pg_statio_all_tables.relid, + + | pg_statio_all_tables.schemaname, + + | pg_statio_all_tables.relname, + + | pg_statio_all_tables.heap_blks_read, + + | pg_statio_all_tables.heap_blks_hit, + + | pg_statio_all_tables.idx_blks_read, + + | pg_statio_all_tables.idx_blks_hit, + + | pg_statio_all_tables.toast_blks_read, + + | pg_statio_all_tables.toast_blks_hit, + + | pg_statio_all_tables.tidx_blks_read, + + | pg_statio_all_tables.tidx_blks_hit + + | FROM pg_statio_all_tables + | WHERE ((pg_statio_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_tables.schemaname ~ '^pg_toast'::text)); - pg_statio_user_indexes | SELECT pg_statio_all_indexes.relid, + - | pg_statio_all_indexes.indexrelid, + - | pg_statio_all_indexes.schemaname, + - | pg_statio_all_indexes.relname, + - | pg_statio_all_indexes.indexrelname, + - | pg_statio_all_indexes.idx_blks_read, + - | pg_statio_all_indexes.idx_blks_hit + - | FROM pg_statio_all_indexes + + pg_statio_user_indexes | SELECT pg_statio_all_indexes.relid, + + | pg_statio_all_indexes.indexrelid, + + | pg_statio_all_indexes.schemaname, + + | pg_statio_all_indexes.relname, + + | pg_statio_all_indexes.indexrelname, + + | pg_statio_all_indexes.idx_blks_read, + + | pg_statio_all_indexes.idx_blks_hit + + | FROM pg_statio_all_indexes + | WHERE ((pg_statio_all_indexes.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_indexes.schemaname !~ '^pg_toast'::text)); - pg_statio_user_sequences | SELECT pg_statio_all_sequences.relid, + - | pg_statio_all_sequences.schemaname, + - | pg_statio_all_sequences.relname, + - | pg_statio_all_sequences.blks_read, + - | pg_statio_all_sequences.blks_hit + - | FROM pg_statio_all_sequences + + pg_statio_user_sequences | SELECT pg_statio_all_sequences.relid, + + | pg_statio_all_sequences.schemaname, + + | pg_statio_all_sequences.relname, + + | pg_statio_all_sequences.blks_read, + + | pg_statio_all_sequences.blks_hit + + | FROM pg_statio_all_sequences + | WHERE ((pg_statio_all_sequences.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_sequences.schemaname !~ '^pg_toast'::text)); - pg_statio_user_tables | SELECT pg_statio_all_tables.relid, + - | pg_statio_all_tables.schemaname, + - | pg_statio_all_tables.relname, + - | pg_statio_all_tables.heap_blks_read, + - | pg_statio_all_tables.heap_blks_hit, + - | pg_statio_all_tables.idx_blks_read, + - | pg_statio_all_tables.idx_blks_hit, + - | pg_statio_all_tables.toast_blks_read, + - | pg_statio_all_tables.toast_blks_hit, + - | pg_statio_all_tables.tidx_blks_read, + - | pg_statio_all_tables.tidx_blks_hit + - | FROM pg_statio_all_tables + + pg_statio_user_tables | SELECT pg_statio_all_tables.relid, + + | pg_statio_all_tables.schemaname, + + | pg_statio_all_tables.relname, + + | pg_statio_all_tables.heap_blks_read, + + | pg_statio_all_tables.heap_blks_hit, + + | pg_statio_all_tables.idx_blks_read, + + | pg_statio_all_tables.idx_blks_hit, + + | pg_statio_all_tables.toast_blks_read, + + | pg_statio_all_tables.toast_blks_hit, + + | pg_statio_all_tables.tidx_blks_read, + + | pg_statio_all_tables.tidx_blks_hit + + | FROM pg_statio_all_tables + | WHERE ((pg_statio_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_tables.schemaname !~ '^pg_toast'::text)); - pg_stats | SELECT n.nspname AS schemaname, + - | c.relname AS tablename, + - | a.attname, + - | s.stainherit AS inherited, + - | s.stanullfrac AS null_frac, + - | s.stawidth AS avg_width, + - | s.stadistinct AS n_distinct, + - | CASE + - | WHEN (s.stakind1 = 1) THEN s.stavalues1 + - | WHEN (s.stakind2 = 1) THEN s.stavalues2 + - | WHEN (s.stakind3 = 1) THEN s.stavalues3 + - | WHEN (s.stakind4 = 1) THEN s.stavalues4 + - | WHEN (s.stakind5 = 1) THEN s.stavalues5 + - | ELSE NULL::anyarray + - | END AS most_common_vals, + - | CASE + - | WHEN (s.stakind1 = 1) THEN s.stanumbers1 + - | WHEN (s.stakind2 = 1) THEN s.stanumbers2 + - | WHEN (s.stakind3 = 1) THEN s.stanumbers3 + - | WHEN (s.stakind4 = 1) THEN s.stanumbers4 + - | WHEN (s.stakind5 = 1) THEN s.stanumbers5 + - | ELSE NULL::real[] + - | END AS most_common_freqs, + - | CASE + - | WHEN (s.stakind1 = 2) THEN s.stavalues1 + - | WHEN (s.stakind2 = 2) THEN s.stavalues2 + - | WHEN (s.stakind3 = 2) THEN s.stavalues3 + - | WHEN (s.stakind4 = 2) THEN s.stavalues4 + - | WHEN (s.stakind5 = 2) THEN s.stavalues5 + - | ELSE NULL::anyarray + - | END AS histogram_bounds, + - | CASE + - | WHEN (s.stakind1 = 3) THEN s.stanumbers1[1] + - | WHEN (s.stakind2 = 3) THEN s.stanumbers2[1] + - | WHEN (s.stakind3 = 3) THEN s.stanumbers3[1] + - | WHEN (s.stakind4 = 3) THEN s.stanumbers4[1] + - | WHEN (s.stakind5 = 3) THEN s.stanumbers5[1] + - | ELSE NULL::real + - | END AS correlation, + - | CASE + - | WHEN (s.stakind1 = 4) THEN s.stavalues1 + - | WHEN (s.stakind2 = 4) THEN s.stavalues2 + - | WHEN (s.stakind3 = 4) THEN s.stavalues3 + - | WHEN (s.stakind4 = 4) THEN s.stavalues4 + - | WHEN (s.stakind5 = 4) THEN s.stavalues5 + - | ELSE NULL::anyarray + - | END AS most_common_elems, + - | CASE + - | WHEN (s.stakind1 = 4) THEN s.stanumbers1 + - | WHEN (s.stakind2 = 4) THEN s.stanumbers2 + - | WHEN (s.stakind3 = 4) THEN s.stanumbers3 + - | WHEN (s.stakind4 = 4) THEN s.stanumbers4 + - | WHEN (s.stakind5 = 4) THEN s.stanumbers5 + - | ELSE NULL::real[] + - | END AS most_common_elem_freqs, + - | CASE + - | WHEN (s.stakind1 = 5) THEN s.stanumbers1 + - | WHEN (s.stakind2 = 5) THEN s.stanumbers2 + - | WHEN (s.stakind3 = 5) THEN s.stanumbers3 + - | WHEN (s.stakind4 = 5) THEN s.stanumbers4 + - | WHEN (s.stakind5 = 5) THEN s.stanumbers5 + - | ELSE NULL::real[] + - | END AS elem_count_histogram + - | FROM (((pg_statistic s + - | JOIN pg_class c ON ((c.oid = s.starelid))) + - | JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum)))) + - | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + + pg_stats | SELECT n.nspname AS schemaname, + + | c.relname AS tablename, + + | a.attname, + + | s.stainherit AS inherited, + + | s.stanullfrac AS null_frac, + + | s.stawidth AS avg_width, + + | s.stadistinct AS n_distinct, + + | CASE + + | WHEN (s.stakind1 = 1) THEN s.stavalues1 + + | WHEN (s.stakind2 = 1) THEN s.stavalues2 + + | WHEN (s.stakind3 = 1) THEN s.stavalues3 + + | WHEN (s.stakind4 = 1) THEN s.stavalues4 + + | WHEN (s.stakind5 = 1) THEN s.stavalues5 + + | ELSE NULL::anyarray + + | END AS most_common_vals, + + | CASE + + | WHEN (s.stakind1 = 1) THEN s.stanumbers1 + + | WHEN (s.stakind2 = 1) THEN s.stanumbers2 + + | WHEN (s.stakind3 = 1) THEN s.stanumbers3 + + | WHEN (s.stakind4 = 1) THEN s.stanumbers4 + + | WHEN (s.stakind5 = 1) THEN s.stanumbers5 + + | ELSE NULL::real[] + + | END AS most_common_freqs, + + | CASE + + | WHEN (s.stakind1 = 2) THEN s.stavalues1 + + | WHEN (s.stakind2 = 2) THEN s.stavalues2 + + | WHEN (s.stakind3 = 2) THEN s.stavalues3 + + | WHEN (s.stakind4 = 2) THEN s.stavalues4 + + | WHEN (s.stakind5 = 2) THEN s.stavalues5 + + | ELSE NULL::anyarray + + | END AS histogram_bounds, + + | CASE + + | WHEN (s.stakind1 = 3) THEN s.stanumbers1[1] + + | WHEN (s.stakind2 = 3) THEN s.stanumbers2[1] + + | WHEN (s.stakind3 = 3) THEN s.stanumbers3[1] + + | WHEN (s.stakind4 = 3) THEN s.stanumbers4[1] + + | WHEN (s.stakind5 = 3) THEN s.stanumbers5[1] + + | ELSE NULL::real + + | END AS correlation, + + | CASE + + | WHEN (s.stakind1 = 4) THEN s.stavalues1 + + | WHEN (s.stakind2 = 4) THEN s.stavalues2 + + | WHEN (s.stakind3 = 4) THEN s.stavalues3 + + | WHEN (s.stakind4 = 4) THEN s.stavalues4 + + | WHEN (s.stakind5 = 4) THEN s.stavalues5 + + | ELSE NULL::anyarray + + | END AS most_common_elems, + + | CASE + + | WHEN (s.stakind1 = 4) THEN s.stanumbers1 + + | WHEN (s.stakind2 = 4) THEN s.stanumbers2 + + | WHEN (s.stakind3 = 4) THEN s.stanumbers3 + + | WHEN (s.stakind4 = 4) THEN s.stanumbers4 + + | WHEN (s.stakind5 = 4) THEN s.stanumbers5 + + | ELSE NULL::real[] + + | END AS most_common_elem_freqs, + + | CASE + + | WHEN (s.stakind1 = 5) THEN s.stanumbers1 + + | WHEN (s.stakind2 = 5) THEN s.stanumbers2 + + | WHEN (s.stakind3 = 5) THEN s.stanumbers3 + + | WHEN (s.stakind4 = 5) THEN s.stanumbers4 + + | WHEN (s.stakind5 = 5) THEN s.stanumbers5 + + | ELSE NULL::real[] + + | END AS elem_count_histogram + + | FROM (((pg_statistic s + + | JOIN pg_class c ON ((c.oid = s.starelid))) + + | JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum)))) + + | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + | WHERE ((NOT a.attisdropped) AND has_column_privilege(c.oid, a.attnum, 'select'::text)); - pg_tables | SELECT n.nspname AS schemaname, + - | c.relname AS tablename, + - | pg_get_userbyid(c.relowner) AS tableowner, + - | t.spcname AS tablespace, + - | c.relhasindex AS hasindexes, + - | c.relhasrules AS hasrules, + - | c.relhastriggers AS hastriggers + - | FROM ((pg_class c + - | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + - | LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace))) + + pg_tables | SELECT n.nspname AS schemaname, + + | c.relname AS tablename, + + | pg_get_userbyid(c.relowner) AS tableowner, + + | t.spcname AS tablespace, + + | c.relhasindex AS hasindexes, + + | c.relhasrules AS hasrules, + + | c.relhastriggers AS hastriggers + + | FROM ((pg_class c + + | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + + | LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace))) + | WHERE (c.relkind = 'r'::"char"); - pg_timezone_abbrevs | SELECT pg_timezone_abbrevs.abbrev, + - | pg_timezone_abbrevs.utc_offset, + - | pg_timezone_abbrevs.is_dst + + pg_timezone_abbrevs | SELECT pg_timezone_abbrevs.abbrev, + + | pg_timezone_abbrevs.utc_offset, + + | pg_timezone_abbrevs.is_dst + | FROM pg_timezone_abbrevs() pg_timezone_abbrevs(abbrev, utc_offset, is_dst); - pg_timezone_names | SELECT pg_timezone_names.name, + - | pg_timezone_names.abbrev, + - | pg_timezone_names.utc_offset, + - | pg_timezone_names.is_dst + + pg_timezone_names | SELECT pg_timezone_names.name, + + | pg_timezone_names.abbrev, + + | pg_timezone_names.utc_offset, + + | pg_timezone_names.is_dst + | FROM pg_timezone_names() pg_timezone_names(name, abbrev, utc_offset, is_dst); - pg_user | SELECT pg_shadow.usename, + - | pg_shadow.usesysid, + - | pg_shadow.usecreatedb, + - | pg_shadow.usesuper, + - | pg_shadow.usecatupd, + - | pg_shadow.userepl, + - | '********'::text AS passwd, + - | pg_shadow.valuntil, + - | pg_shadow.useconfig + + pg_user | SELECT pg_shadow.usename, + + | pg_shadow.usesysid, + + | pg_shadow.usecreatedb, + + | pg_shadow.usesuper, + + | pg_shadow.usecatupd, + + | pg_shadow.userepl, + + | '********'::text AS passwd, + + | pg_shadow.valuntil, + + | pg_shadow.useconfig + | FROM pg_shadow; - pg_user_mappings | SELECT u.oid AS umid, + - | s.oid AS srvid, + - | s.srvname, + - | u.umuser, + - | CASE + - | WHEN (u.umuser = (0)::oid) THEN 'public'::name + - | ELSE a.rolname + - | END AS usename, + - | CASE + - | WHEN (pg_has_role(s.srvowner, 'USAGE'::text) OR has_server_privilege(s.oid, 'USAGE'::text)) THEN u.umoptions + - | ELSE NULL::text[] + - | END AS umoptions + - | FROM ((pg_user_mapping u + - | LEFT JOIN pg_authid a ON ((a.oid = u.umuser))) + + pg_user_mappings | SELECT u.oid AS umid, + + | s.oid AS srvid, + + | s.srvname, + + | u.umuser, + + | CASE + + | WHEN (u.umuser = (0)::oid) THEN 'public'::name + + | ELSE a.rolname + + | END AS usename, + + | CASE + + | WHEN (pg_has_role(s.srvowner, 'USAGE'::text) OR has_server_privilege(s.oid, 'USAGE'::text)) THEN u.umoptions + + | ELSE NULL::text[] + + | END AS umoptions + + | FROM ((pg_user_mapping u + + | LEFT JOIN pg_authid a ON ((a.oid = u.umuser))) + | JOIN pg_foreign_server s ON ((u.umserver = s.oid))); - pg_views | SELECT n.nspname AS schemaname, + - | c.relname AS viewname, + - | pg_get_userbyid(c.relowner) AS viewowner, + - | pg_get_viewdef(c.oid) AS definition + - | FROM (pg_class c + - | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + + pg_views | SELECT n.nspname AS schemaname, + + | c.relname AS viewname, + + | pg_get_userbyid(c.relowner) AS viewowner, + + | pg_get_viewdef(c.oid) AS definition + + | FROM (pg_class c + + | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + | WHERE (c.relkind = 'v'::"char"); - rtest_v1 | SELECT rtest_t1.a, + - | rtest_t1.b + + rtest_v1 | SELECT rtest_t1.a, + + | rtest_t1.b + | FROM rtest_t1; - rtest_vcomp | SELECT x.part, + - | (x.size * y.factor) AS size_in_cm + - | FROM rtest_comp x, + - | rtest_unitfact y + + rtest_vcomp | SELECT x.part, + + | (x.size * y.factor) AS size_in_cm + + | FROM rtest_comp x, + + | rtest_unitfact y + | WHERE (x.unit = y.unit); - rtest_vview1 | SELECT x.a, + - | x.b + - | FROM rtest_view1 x + - | WHERE (0 < ( SELECT count(*) AS count + - | FROM rtest_view2 y + + rtest_vview1 | SELECT x.a, + + | x.b + + | FROM rtest_view1 x + + | WHERE (0 < ( SELECT count(*) AS count + + | FROM rtest_view2 y + | WHERE (y.a = x.a))); - rtest_vview2 | SELECT rtest_view1.a, + - | rtest_view1.b + - | FROM rtest_view1 + + rtest_vview2 | SELECT rtest_view1.a, + + | rtest_view1.b + + | FROM rtest_view1 + | WHERE rtest_view1.v; - rtest_vview3 | SELECT x.a, + - | x.b + - | FROM rtest_vview2 x + - | WHERE (0 < ( SELECT count(*) AS count + - | FROM rtest_view2 y + + rtest_vview3 | SELECT x.a, + + | x.b + + | FROM rtest_vview2 x + + | WHERE (0 < ( SELECT count(*) AS count + + | FROM rtest_view2 y + | WHERE (y.a = x.a))); - rtest_vview4 | SELECT x.a, + - | x.b, + - | count(y.a) AS refcount + - | FROM rtest_view1 x, + - | rtest_view2 y + - | WHERE (x.a = y.a) + + rtest_vview4 | SELECT x.a, + + | x.b, + + | count(y.a) AS refcount + + | FROM rtest_view1 x, + + | rtest_view2 y + + | WHERE (x.a = y.a) + | GROUP BY x.a, x.b; - rtest_vview5 | SELECT rtest_view1.a, + - | rtest_view1.b, + - | rtest_viewfunc1(rtest_view1.a) AS refcount + + rtest_vview5 | SELECT rtest_view1.a, + + | rtest_view1.b, + + | rtest_viewfunc1(rtest_view1.a) AS refcount + | FROM rtest_view1; - shoe | SELECT sh.shoename, + - | sh.sh_avail, + - | sh.slcolor, + - | sh.slminlen, + - | (sh.slminlen * un.un_fact) AS slminlen_cm, + - | sh.slmaxlen, + - | (sh.slmaxlen * un.un_fact) AS slmaxlen_cm, + - | sh.slunit + - | FROM shoe_data sh, + - | unit un + + shoe | SELECT sh.shoename, + + | sh.sh_avail, + + | sh.slcolor, + + | sh.slminlen, + + | (sh.slminlen * un.un_fact) AS slminlen_cm, + + | sh.slmaxlen, + + | (sh.slmaxlen * un.un_fact) AS slmaxlen_cm, + + | sh.slunit + + | FROM shoe_data sh, + + | unit un + | WHERE (sh.slunit = un.un_name); - shoe_ready | SELECT rsh.shoename, + - | rsh.sh_avail, + - | rsl.sl_name, + - | rsl.sl_avail, + - | int4smaller(rsh.sh_avail, rsl.sl_avail) AS total_avail + - | FROM shoe rsh, + - | shoelace rsl + + shoe_ready | SELECT rsh.shoename, + + | rsh.sh_avail, + + | rsl.sl_name, + + | rsl.sl_avail, + + | int4smaller(rsh.sh_avail, rsl.sl_avail) AS total_avail + + | FROM shoe rsh, + + | shoelace rsl + | WHERE (((rsl.sl_color = rsh.slcolor) AND (rsl.sl_len_cm >= rsh.slminlen_cm)) AND (rsl.sl_len_cm <= rsh.slmaxlen_cm)); - shoelace | SELECT s.sl_name, + - | s.sl_avail, + - | s.sl_color, + - | s.sl_len, + - | s.sl_unit, + - | (s.sl_len * u.un_fact) AS sl_len_cm + - | FROM shoelace_data s, + - | unit u + + shoelace | SELECT s.sl_name, + + | s.sl_avail, + + | s.sl_color, + + | s.sl_len, + + | s.sl_unit, + + | (s.sl_len * u.un_fact) AS sl_len_cm + + | FROM shoelace_data s, + + | unit u + | WHERE (s.sl_unit = u.un_name); - shoelace_candelete | SELECT shoelace_obsolete.sl_name, + - | shoelace_obsolete.sl_avail, + - | shoelace_obsolete.sl_color, + - | shoelace_obsolete.sl_len, + - | shoelace_obsolete.sl_unit, + - | shoelace_obsolete.sl_len_cm + - | FROM shoelace_obsolete + + shoelace_candelete | SELECT shoelace_obsolete.sl_name, + + | shoelace_obsolete.sl_avail, + + | shoelace_obsolete.sl_color, + + | shoelace_obsolete.sl_len, + + | shoelace_obsolete.sl_unit, + + | shoelace_obsolete.sl_len_cm + + | FROM shoelace_obsolete + | WHERE (shoelace_obsolete.sl_avail = 0); - shoelace_obsolete | SELECT shoelace.sl_name, + - | shoelace.sl_avail, + - | shoelace.sl_color, + - | shoelace.sl_len, + - | shoelace.sl_unit, + - | shoelace.sl_len_cm + - | FROM shoelace + - | WHERE (NOT (EXISTS ( SELECT shoe.shoename + - | FROM shoe + + shoelace_obsolete | SELECT shoelace.sl_name, + + | shoelace.sl_avail, + + | shoelace.sl_color, + + | shoelace.sl_len, + + | shoelace.sl_unit, + + | shoelace.sl_len_cm + + | FROM shoelace + + | WHERE (NOT (EXISTS ( SELECT shoe.shoename + + | FROM shoe + | WHERE (shoe.slcolor = shoelace.sl_color)))); - street | SELECT r.name, + - | r.thepath, + - | c.cname + - | FROM ONLY road r, + - | real_city c + + street | SELECT r.name, + + | r.thepath, + + | c.cname + + | FROM ONLY road r, + + | real_city c + | WHERE (c.outline ## r.thepath); - toyemp | SELECT emp.name, + - | emp.age, + - | emp.location, + - | (12 * emp.salary) AS annualsal + + toyemp | SELECT emp.name, + + | emp.age, + + | emp.location, + + | (12 * emp.salary) AS annualsal + | FROM emp; - tv | SELECT t.type, + - | sum(t.amt) AS totamt + - | FROM t + + tv | SELECT t.type, + + | sum(t.amt) AS totamt + + | FROM t + | GROUP BY t.type; - tvv | SELECT sum(tv.totamt) AS grandtot + + tvv | SELECT sum(tv.totamt) AS grandtot + | FROM tv; - tvvmv | SELECT tvvm.grandtot + + tvvmv | SELECT tvvm.grandtot + | FROM tvvm; (64 rows) @@ -2461,50 +2461,50 @@ select * from only t1_2; -- test various flavors of pg_get_viewdef() select pg_get_viewdef('shoe'::regclass) as unpretty; - unpretty -------------------------------------------------- - SELECT sh.shoename, + - sh.sh_avail, + - sh.slcolor, + - sh.slminlen, + - (sh.slminlen * un.un_fact) AS slminlen_cm, + - sh.slmaxlen, + - (sh.slmaxlen * un.un_fact) AS slmaxlen_cm, + - sh.slunit + - FROM shoe_data sh, + - unit un + + unpretty +------------------------------------------------ + SELECT sh.shoename, + + sh.sh_avail, + + sh.slcolor, + + sh.slminlen, + + (sh.slminlen * un.un_fact) AS slminlen_cm,+ + sh.slmaxlen, + + (sh.slmaxlen * un.un_fact) AS slmaxlen_cm,+ + sh.slunit + + FROM shoe_data sh, + + unit un + WHERE (sh.slunit = un.un_name); (1 row) select pg_get_viewdef('shoe'::regclass,true) as pretty; - pretty ------------------------------------------------ - SELECT sh.shoename, + - sh.sh_avail, + - sh.slcolor, + - sh.slminlen, + - sh.slminlen * un.un_fact AS slminlen_cm, + - sh.slmaxlen, + - sh.slmaxlen * un.un_fact AS slmaxlen_cm, + - sh.slunit + - FROM shoe_data sh, + - unit un + + pretty +---------------------------------------------- + SELECT sh.shoename, + + sh.sh_avail, + + sh.slcolor, + + sh.slminlen, + + sh.slminlen * un.un_fact AS slminlen_cm,+ + sh.slmaxlen, + + sh.slmaxlen * un.un_fact AS slmaxlen_cm,+ + sh.slunit + + FROM shoe_data sh, + + unit un + WHERE sh.slunit = un.un_name; (1 row) select pg_get_viewdef('shoe'::regclass,0) as prettier; - prettier ------------------------------------------------ - SELECT sh.shoename, + - sh.sh_avail, + - sh.slcolor, + - sh.slminlen, + - sh.slminlen * un.un_fact AS slminlen_cm, + - sh.slmaxlen, + - sh.slmaxlen * un.un_fact AS slmaxlen_cm, + - sh.slunit + - FROM shoe_data sh, + - unit un + + prettier +---------------------------------------------- + SELECT sh.shoename, + + sh.sh_avail, + + sh.slcolor, + + sh.slminlen, + + sh.slminlen * un.un_fact AS slminlen_cm,+ + sh.slmaxlen, + + sh.slmaxlen * un.un_fact AS slmaxlen_cm,+ + sh.slunit + + FROM shoe_data sh, + + unit un + WHERE sh.slunit = un.un_name; (1 row) @@ -2586,7 +2586,7 @@ Rules: r2 AS ON UPDATE TO rules_src DO VALUES (old.f1,old.f2,'old'::text), (new.f1,new.f2,'new'::text) r3 AS - ON DELETE TO rules_src DO + ON DELETE TO rules_src DO NOTIFY rules_src_deletion Has OIDs: no @@ -2617,7 +2617,7 @@ View definition: FROM rule_t1; Rules: newinsertrule AS - ON INSERT TO rule_v1 DO INSTEAD INSERT INTO rule_t1 (a) + ON INSERT TO rule_v1 DO INSTEAD INSERT INTO rule_t1 (a) VALUES (new.a) -- diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out index 6a6ecf7e25..f1a5fde107 100644 --- a/src/test/regress/expected/triggers.out +++ b/src/test/regress/expected/triggers.out @@ -1119,7 +1119,7 @@ DROP TRIGGER instead_of_delete_trig ON main_view; a | integer | | plain | b | integer | | plain | View definition: - SELECT main_table.a, + SELECT main_table.a, main_table.b FROM main_table; Triggers: diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out index c381715c56..d76ef4e03f 100644 --- a/src/test/regress/expected/with.out +++ b/src/test/regress/expected/with.out @@ -361,7 +361,7 @@ SELECT sum(n) FROM t; View definition: WITH RECURSIVE t(n) AS ( VALUES (1) - UNION ALL + UNION ALL SELECT t_1.n + 1 FROM t t_1 WHERE t_1.n < 100 -- 2.40.0