X-Git-Url: https://granicus.if.org/sourcecode?a=blobdiff_plain;f=src%2Fbackend%2Fcatalog%2Finformation_schema.sql;h=659d7cb5a49b465eb68c7c034fe067014f3fdadf;hb=ee943004466418595363d567f18c053bae407792;hp=818582833d08e202b802443b15a66b772b8920a4;hpb=e126958c2efdfc2d60b978d1fc7a780ff647e8ad;p=postgresql

diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index 818582833d..659d7cb5a4 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -1,10 +1,18 @@
 /*
  * SQL Information Schema
- * as defined in ISO/IEC 9075-11:2008
+ * as defined in ISO/IEC 9075-11:2011
  *
- * Copyright (c) 2003-2012, PostgreSQL Global Development Group
+ * Copyright (c) 2003-2016, PostgreSQL Global Development Group
  *
  * src/backend/catalog/information_schema.sql
+ *
+ * Note: this file is read in single-user -j mode, which means that the
+ * command terminator is semicolon-newline-newline; whenever the backend
+ * sees that, it stops and executes what it's got.  If you write a lot of
+ * statements without empty lines between, they'll all get quoted to you
+ * in any error message about one of them, so don't do that.  Also, you
+ * cannot write a semicolon immediately followed by an empty line in a
+ * string literal (including a function body!) or a multiline comment.
  */
 
 /*
@@ -554,7 +562,7 @@ CREATE VIEW column_privileges AS
                   pr_c.prtype,
                   pr_c.grantable,
                   pr_c.relowner
-           FROM (SELECT oid, relname, relnamespace, relowner, (aclexplode(relacl)).*
+           FROM (SELECT oid, relname, relnamespace, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).*
                  FROM pg_class
                  WHERE relkind IN ('r', 'v', 'f')
                 ) pr_c (oid, relname, relnamespace, relowner, grantor, grantee, prtype, grantable),
@@ -571,8 +579,8 @@ CREATE VIEW column_privileges AS
                   pr_a.prtype,
                   pr_a.grantable,
                   c.relowner
-           FROM (SELECT attrelid, attname, (aclexplode(attacl)).*
-                 FROM pg_attribute
+           FROM (SELECT attrelid, attname, (aclexplode(coalesce(attacl, acldefault('c', relowner)))).*
+                 FROM pg_attribute a JOIN pg_class cc ON (a.attrelid = cc.oid)
                  WHERE attnum > 0
                        AND NOT attisdropped
                 ) pr_a (attrelid, attname, grantor, grantee, prtype, grantable),
@@ -730,10 +738,9 @@ CREATE VIEW columns AS
            CAST('NEVER' AS character_data) AS is_generated,
            CAST(null AS character_data) AS generation_expression,
 
-           CAST(CASE WHEN c.relkind = 'r'
-                          OR (c.relkind = 'v'
-                              AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '2' AND is_instead)
-                              AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '4' AND is_instead))
+           CAST(CASE WHEN c.relkind = 'r' OR
+                          (c.relkind IN ('v', 'f') AND
+                           pg_column_is_updatable(c.oid, a.attnum, false))
                 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_updatable
 
     FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum)
@@ -809,6 +816,14 @@ GRANT SELECT ON constraint_column_usage TO PUBLIC;
 
 /*
  * 5.23
+ * CONSTRAINT_PERIOD_USAGE view
+ */
+
+-- feature not supported
+
+
+/*
+ * 5.24
  * CONSTRAINT_TABLE_USAGE view
  */
 
@@ -832,11 +847,11 @@ CREATE VIEW constraint_table_usage AS
 GRANT SELECT ON constraint_table_usage TO PUBLIC;
 
 
--- 5.24 DATA_TYPE_PRIVILEGES view appears later.
+-- 5.25 DATA_TYPE_PRIVILEGES view appears later.
 
 
 /*
- * 5.25
+ * 5.26
  * DIRECT_SUPERTABLES view
  */
 
@@ -844,7 +859,7 @@ GRANT SELECT ON constraint_table_usage TO PUBLIC;
 
 
 /*
- * 5.26
+ * 5.27
  * DIRECT_SUPERTYPES view
  */
 
@@ -852,7 +867,7 @@ GRANT SELECT ON constraint_table_usage TO PUBLIC;
 
 
 /*
- * 5.27
+ * 5.28
  * DOMAIN_CONSTRAINTS view
  */
 
@@ -903,7 +918,7 @@ GRANT SELECT ON domain_udt_usage TO PUBLIC;
 
 
 /*
- * 5.28
+ * 5.29
  * DOMAINS view
  */
 
@@ -988,11 +1003,11 @@ CREATE VIEW domains AS
 GRANT SELECT ON domains TO PUBLIC;
 
 
--- 5.29 ELEMENT_TYPES view appears later.
+-- 5.30 ELEMENT_TYPES view appears later.
 
 
 /*
- * 5.30
+ * 5.31
  * ENABLED_ROLES view
  */
 
@@ -1005,7 +1020,7 @@ GRANT SELECT ON enabled_roles TO PUBLIC;
 
 
 /*
- * 5.31
+ * 5.32
  * FIELDS view
  */
 
@@ -1013,7 +1028,7 @@ GRANT SELECT ON enabled_roles TO PUBLIC;
 
 
 /*
- * 5.32
+ * 5.33
  * KEY_COLUMN_USAGE view
  */
 
@@ -1056,7 +1071,15 @@ GRANT SELECT ON key_column_usage TO PUBLIC;
 
 
 /*
- * 5.33
+ * 5.34
+ * KEY_PERIOD_USAGE view
+ */
+
+-- feature not supported
+
+
+/*
+ * 5.35
  * METHOD_SPECIFICATION_PARAMETERS view
  */
 
@@ -1064,7 +1087,7 @@ GRANT SELECT ON key_column_usage TO PUBLIC;
 
 
 /*
- * 5.34
+ * 5.36
  * METHOD_SPECIFICATIONS view
  */
 
@@ -1072,7 +1095,7 @@ GRANT SELECT ON key_column_usage TO PUBLIC;
 
 
 /*
- * 5.35
+ * 5.37
  * PARAMETERS view
  */
 
@@ -1118,10 +1141,15 @@ CREATE VIEW parameters AS
            CAST(null AS sql_identifier) AS scope_schema,
            CAST(null AS sql_identifier) AS scope_name,
            CAST(null AS cardinal_number) AS maximum_cardinality,
-           CAST((ss.x).n AS sql_identifier) AS dtd_identifier
+           CAST((ss.x).n AS sql_identifier) AS dtd_identifier,
+           CAST(
+             CASE WHEN pg_has_role(proowner, 'USAGE')
+                  THEN pg_get_function_arg_default(p_oid, (ss.x).n)
+                  ELSE NULL END
+             AS character_data) AS parameter_default
 
     FROM pg_type t, pg_namespace nt,
-         (SELECT n.nspname AS n_nspname, p.proname, p.oid AS p_oid,
+         (SELECT n.nspname AS n_nspname, p.proname, p.oid AS p_oid, p.proowner,
                  p.proargnames, p.proargmodes,
                  _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
           FROM pg_namespace n, pg_proc p
@@ -1134,7 +1162,15 @@ GRANT SELECT ON parameters TO PUBLIC;
 
 
 /*
- * 5.36
+ * 5.38
+ * PERIODS view
+ */
+
+-- feature not supported
+
+
+/*
+ * 5.39
  * REFERENCED_TYPES view
  */
 
@@ -1142,7 +1178,7 @@ GRANT SELECT ON parameters TO PUBLIC;
 
 
 /*
- * 5.37
+ * 5.40
  * REFERENTIAL_CONSTRAINTS view
  */
 
@@ -1160,7 +1196,7 @@ CREATE VIEW referential_constraints AS
            CAST(
              CASE con.confmatchtype WHEN 'f' THEN 'FULL'
                                     WHEN 'p' THEN 'PARTIAL'
-                                    WHEN 'u' THEN 'NONE' END
+                                    WHEN 's' THEN 'NONE' END
              AS character_data) AS match_option,
 
            CAST(
@@ -1204,7 +1240,7 @@ GRANT SELECT ON referential_constraints TO PUBLIC;
 
 
 /*
- * 5.38
+ * 5.41
  * ROLE_COLUMN_GRANTS view
  */
 
@@ -1224,14 +1260,14 @@ CREATE VIEW role_column_grants AS
 GRANT SELECT ON role_column_grants TO PUBLIC;
 
 
--- 5.39 ROLE_ROUTINE_GRANTS view is based on 5.45 ROUTINE_PRIVILEGES and is defined there instead.
+-- 5.42 ROLE_ROUTINE_GRANTS view is based on 5.49 ROUTINE_PRIVILEGES and is defined there instead.
 
 
--- 5.40 ROLE_TABLE_GRANTS view is based on 5.60 TABLE_PRIVILEGES and is defined there instead.
+-- 5.43 ROLE_TABLE_GRANTS view is based on 5.62 TABLE_PRIVILEGES and is defined there instead.
 
 
 /*
- * 5.41
+ * 5.44
  * ROLE_TABLE_METHOD_GRANTS view
  */
 
@@ -1239,14 +1275,14 @@ GRANT SELECT ON role_column_grants TO PUBLIC;
 
 
 
--- 5.42 ROLE_USAGE_GRANTS view is based on 5.71 USAGE_PRIVILEGES and is defined there instead.
+-- 5.45 ROLE_USAGE_GRANTS view is based on 5.74 USAGE_PRIVILEGES and is defined there instead.
 
 
--- 5.43 ROLE_UDT_GRANTS view is based on 5.70 UDT_PRIVILEGES and is defined there instead.
+-- 5.46 ROLE_UDT_GRANTS view is based on 5.73 UDT_PRIVILEGES and is defined there instead.
 
 
 /*
- * 5.44
+ * 5.47
  * ROUTINE_COLUMN_USAGE view
  */
 
@@ -1254,7 +1290,15 @@ GRANT SELECT ON role_column_grants TO PUBLIC;
 
 
 /*
- * 5.45
+ * 5.48
+ * ROUTINE_PERIOD_USAGE view
+ */
+
+-- feature not supported
+
+
+/*
+ * 5.49
  * ROUTINE_PRIVILEGES view
  */
 
@@ -1276,7 +1320,7 @@ CREATE VIEW routine_privileges AS
                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
 
     FROM (
-            SELECT oid, proname, proowner, pronamespace, (aclexplode(proacl)).* FROM pg_proc
+            SELECT oid, proname, proowner, pronamespace, (aclexplode(coalesce(proacl, acldefault('f', proowner)))).* FROM pg_proc
          ) p (oid, proname, proowner, pronamespace, grantor, grantee, prtype, grantable),
          pg_namespace n,
          pg_authid u_grantor,
@@ -1298,7 +1342,7 @@ GRANT SELECT ON routine_privileges TO PUBLIC;
 
 
 /*
- * 5.39
+ * 5.42
  * ROLE_ROUTINE_GRANTS view
  */
 
@@ -1321,7 +1365,7 @@ GRANT SELECT ON role_routine_grants TO PUBLIC;
 
 
 /*
- * 5.46
+ * 5.50
  * ROUTINE_ROUTINE_USAGE view
  */
 
@@ -1329,7 +1373,7 @@ GRANT SELECT ON role_routine_grants TO PUBLIC;
 
 
 /*
- * 5.47
+ * 5.51
  * ROUTINE_SEQUENCE_USAGE view
  */
 
@@ -1337,7 +1381,7 @@ GRANT SELECT ON role_routine_grants TO PUBLIC;
 
 
 /*
- * 5.48
+ * 5.52
  * ROUTINE_TABLE_USAGE view
  */
 
@@ -1345,7 +1389,7 @@ GRANT SELECT ON role_routine_grants TO PUBLIC;
 
 
 /*
- * 5.49
+ * 5.53
  * ROUTINES view
  */
 
@@ -1458,7 +1502,7 @@ GRANT SELECT ON routines TO PUBLIC;
 
 
 /*
- * 5.50
+ * 5.54
  * SCHEMATA view
  */
 
@@ -1471,13 +1515,15 @@ CREATE VIEW schemata AS
            CAST(null AS sql_identifier) AS default_character_set_name,
            CAST(null AS character_data) AS sql_path
     FROM pg_namespace n, pg_authid u
-    WHERE n.nspowner = u.oid AND pg_has_role(n.nspowner, 'USAGE');
+    WHERE n.nspowner = u.oid
+          AND (pg_has_role(n.nspowner, 'USAGE')
+               OR has_schema_privilege(n.oid, 'CREATE, USAGE'));
 
 GRANT SELECT ON schemata TO PUBLIC;
 
 
 /*
- * 5.51
+ * 5.55
  * SEQUENCES view
  */
 
@@ -1489,13 +1535,12 @@ CREATE VIEW sequences AS
            CAST(64 AS cardinal_number) AS numeric_precision,
            CAST(2 AS cardinal_number) AS numeric_precision_radix,
            CAST(0 AS cardinal_number) AS numeric_scale,
-           -- XXX: The following could be improved if we had LATERAL.
-           CAST((pg_sequence_parameters(c.oid)).start_value AS character_data) AS start_value,
-           CAST((pg_sequence_parameters(c.oid)).minimum_value AS character_data) AS minimum_value,
-           CAST((pg_sequence_parameters(c.oid)).maximum_value AS character_data) AS maximum_value,
-           CAST((pg_sequence_parameters(c.oid)).increment AS character_data) AS increment,
-           CAST(CASE WHEN (pg_sequence_parameters(c.oid)).cycle_option THEN 'YES' ELSE 'NO' END AS yes_or_no) AS cycle_option
-    FROM pg_namespace nc, pg_class c
+           CAST(p.start_value AS character_data) AS start_value,
+           CAST(p.minimum_value AS character_data) AS minimum_value,
+           CAST(p.maximum_value AS character_data) AS maximum_value,
+           CAST(p.increment AS character_data) AS increment,
+           CAST(CASE WHEN p.cycle_option THEN 'YES' ELSE 'NO' END AS yes_or_no) AS cycle_option
+    FROM pg_namespace nc, pg_class c, LATERAL pg_sequence_parameters(c.oid) p
     WHERE c.relnamespace = nc.oid
           AND c.relkind = 'S'
           AND (NOT pg_is_other_temp_schema(nc.oid))
@@ -1506,7 +1551,7 @@ GRANT SELECT ON sequences TO PUBLIC;
 
 
 /*
- * 5.52
+ * 5.56
  * SQL_FEATURES table
  */
 
@@ -1526,7 +1571,7 @@ GRANT SELECT ON sql_features TO PUBLIC;
 
 
 /*
- * 5.53
+ * 5.57
  * SQL_IMPLEMENTATION_INFO table
  */
 
@@ -1581,8 +1626,8 @@ GRANT SELECT ON sql_languages TO PUBLIC;
 
 
 /*
- * 5.54
  * SQL_PACKAGES table
+ * removed in SQL:2011
  */
 
 CREATE TABLE sql_packages (
@@ -1608,7 +1653,7 @@ GRANT SELECT ON sql_packages TO PUBLIC;
 
 
 /*
- * 5.55
+ * 5.58
  * SQL_PARTS table
  */
 
@@ -1632,7 +1677,7 @@ INSERT INTO sql_parts VALUES ('14', 'XML-Related Specifications (SQL/XML)', 'YES
 
 
 /*
- * 5.56
+ * 5.59
  * SQL_SIZING table
  */
 
@@ -1678,8 +1723,8 @@ GRANT SELECT ON sql_sizing TO PUBLIC;
 
 
 /*
- * 5.57
  * SQL_SIZING_PROFILES table
+ * removed in SQL:2011
  */
 
 -- The data in this table are defined by various profiles of SQL.
@@ -1698,7 +1743,7 @@ GRANT SELECT ON sql_sizing_profiles TO PUBLIC;
 
 
 /*
- * 5.58
+ * 5.60
  * TABLE_CONSTRAINTS view
  */
 
@@ -1769,7 +1814,7 @@ GRANT SELECT ON table_constraints TO PUBLIC;
 
 
 /*
- * 5.59
+ * 5.61
  * TABLE_METHOD_PRIVILEGES view
  */
 
@@ -1777,7 +1822,7 @@ GRANT SELECT ON table_constraints TO PUBLIC;
 
 
 /*
- * 5.60
+ * 5.62
  * TABLE_PRIVILEGES view
  */
 
@@ -1797,7 +1842,7 @@ CREATE VIEW table_privileges AS
            CAST(CASE WHEN c.prtype = 'SELECT' THEN 'YES' ELSE 'NO' END AS yes_or_no) AS with_hierarchy
 
     FROM (
-            SELECT oid, relname, relnamespace, relkind, relowner, (aclexplode(relacl)).* FROM pg_class
+            SELECT oid, relname, relnamespace, relkind, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).* FROM pg_class
          ) AS c (oid, relname, relnamespace, relkind, relowner, grantor, grantee, prtype, grantable),
          pg_namespace nc,
          pg_authid u_grantor,
@@ -1820,7 +1865,7 @@ GRANT SELECT ON table_privileges TO PUBLIC;
 
 
 /*
- * 5.40
+ * 5.43
  * ROLE_TABLE_GRANTS view
  */
 
@@ -1841,7 +1886,7 @@ GRANT SELECT ON role_table_grants TO PUBLIC;
 
 
 /*
- * 5.61
+ * 5.63
  * TABLES view
  */
 
@@ -1865,9 +1910,10 @@ CREATE VIEW tables AS
            CAST(nt.nspname AS sql_identifier) AS user_defined_type_schema,
            CAST(t.typname AS sql_identifier) AS user_defined_type_name,
 
-           CAST(CASE WHEN c.relkind = 'r'
-                          OR (c.relkind = 'v'
-                              AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '3' AND is_instead))
+           CAST(CASE WHEN c.relkind = 'r' OR
+                          (c.relkind IN ('v', 'f') AND
+                           -- 1 << CMD_INSERT
+                           pg_relation_is_updatable(c.oid, false) & 8 = 8)
                 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_insertable_into,
 
            CAST(CASE WHEN t.typname IS NOT NULL THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_typed,
@@ -1886,15 +1932,47 @@ GRANT SELECT ON tables TO PUBLIC;
 
 
 /*
- * 5.62
+ * 5.64
  * TRANSFORMS view
  */
 
--- feature not supported
+CREATE VIEW transforms AS
+    SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
+           CAST(nt.nspname AS sql_identifier) AS udt_schema,
+           CAST(t.typname AS sql_identifier) AS udt_name,
+           CAST(current_database() AS sql_identifier) AS specific_catalog,
+           CAST(np.nspname AS sql_identifier) AS specific_schema,
+           CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
+           CAST(l.lanname AS sql_identifier) AS group_name,
+           CAST('FROM SQL' AS character_data) AS transform_type
+    FROM pg_type t JOIN pg_transform x ON t.oid = x.trftype
+         JOIN pg_language l ON x.trflang = l.oid
+         JOIN pg_proc p ON x.trffromsql = p.oid
+         JOIN pg_namespace nt ON t.typnamespace = nt.oid
+         JOIN pg_namespace np ON p.pronamespace = np.oid
+
+  UNION
+
+    SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
+           CAST(nt.nspname AS sql_identifier) AS udt_schema,
+           CAST(t.typname AS sql_identifier) AS udt_name,
+           CAST(current_database() AS sql_identifier) AS specific_catalog,
+           CAST(np.nspname AS sql_identifier) AS specific_schema,
+           CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
+           CAST(l.lanname AS sql_identifier) AS group_name,
+           CAST('TO SQL' AS character_data) AS transform_type
+    FROM pg_type t JOIN pg_transform x ON t.oid = x.trftype
+         JOIN pg_language l ON x.trflang = l.oid
+         JOIN pg_proc p ON x.trftosql = p.oid
+         JOIN pg_namespace nt ON t.typnamespace = nt.oid
+         JOIN pg_namespace np ON p.pronamespace = np.oid
+
+  ORDER BY udt_catalog, udt_schema, udt_name, group_name, transform_type  -- some sensible grouping for interactive use
+;
 
 
 /*
- * 5.63
+ * 5.65
  * TRANSLATIONS view
  */
 
@@ -1902,7 +1980,7 @@ GRANT SELECT ON tables TO PUBLIC;
 
 
 /*
- * 5.64
+ * 5.66
  * TRIGGERED_UPDATE_COLUMNS view
  */
 
@@ -1934,7 +2012,7 @@ GRANT SELECT ON triggered_update_columns TO PUBLIC;
 
 
 /*
- * 5.65
+ * 5.67
  * TRIGGER_COLUMN_USAGE view
  */
 
@@ -1942,7 +2020,15 @@ GRANT SELECT ON triggered_update_columns TO PUBLIC;
 
 
 /*
- * 5.66
+ * 5.68
+ * TRIGGER_PERIOD_USAGE view
+ */
+
+-- feature not supported
+
+
+/*
+ * 5.69
  * TRIGGER_ROUTINE_USAGE view
  */
 
@@ -1950,7 +2036,7 @@ GRANT SELECT ON triggered_update_columns TO PUBLIC;
 
 
 /*
- * 5.67
+ * 5.70
  * TRIGGER_SEQUENCE_USAGE view
  */
 
@@ -1958,7 +2044,7 @@ GRANT SELECT ON triggered_update_columns TO PUBLIC;
 
 
 /*
- * 5.68
+ * 5.71
  * TRIGGER_TABLE_USAGE view
  */
 
@@ -1966,7 +2052,7 @@ GRANT SELECT ON triggered_update_columns TO PUBLIC;
 
 
 /*
- * 5.69
+ * 5.72
  * TRIGGERS view
  */
 
@@ -2024,7 +2110,7 @@ GRANT SELECT ON triggers TO PUBLIC;
 
 
 /*
- * 5.70
+ * 5.73
  * UDT_PRIVILEGES view
  */
 
@@ -2043,7 +2129,7 @@ CREATE VIEW udt_privileges AS
                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
 
     FROM (
-            SELECT oid, typname, typnamespace, typtype, typowner, (aclexplode(typacl)).* FROM pg_type
+            SELECT oid, typname, typnamespace, typtype, typowner, (aclexplode(coalesce(typacl, acldefault('T', typowner)))).* FROM pg_type
          ) AS t (oid, typname, typnamespace, typtype, typowner, grantor, grantee, prtype, grantable),
          pg_namespace n,
          pg_authid u_grantor,
@@ -2066,7 +2152,7 @@ GRANT SELECT ON udt_privileges TO PUBLIC;
 
 
 /*
- * 5.43
+ * 5.46
  * ROLE_UDT_GRANTS view
  */
 
@@ -2086,7 +2172,7 @@ GRANT SELECT ON role_udt_grants TO PUBLIC;
 
 
 /*
- * 5.71
+ * 5.74
  * USAGE_PRIVILEGES view
  */
 
@@ -2129,7 +2215,7 @@ CREATE VIEW usage_privileges AS
                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
 
     FROM (
-            SELECT oid, typname, typnamespace, typtype, typowner, (aclexplode(typacl)).* FROM pg_type
+            SELECT oid, typname, typnamespace, typtype, typowner, (aclexplode(coalesce(typacl, acldefault('T', typowner)))).* FROM pg_type
          ) AS t (oid, typname, typnamespace, typtype, typowner, grantor, grantee, prtype, grantable),
          pg_namespace n,
          pg_authid u_grantor,
@@ -2166,7 +2252,7 @@ CREATE VIEW usage_privileges AS
                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
 
     FROM (
-            SELECT fdwname, fdwowner, (aclexplode(fdwacl)).* FROM pg_foreign_data_wrapper
+            SELECT fdwname, fdwowner, (aclexplode(coalesce(fdwacl, acldefault('F', fdwowner)))).* FROM pg_foreign_data_wrapper
          ) AS fdw (fdwname, fdwowner, grantor, grantee, prtype, grantable),
          pg_authid u_grantor,
          (
@@ -2200,7 +2286,7 @@ CREATE VIEW usage_privileges AS
                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
 
     FROM (
-            SELECT srvname, srvowner, (aclexplode(srvacl)).* FROM pg_foreign_server
+            SELECT srvname, srvowner, (aclexplode(coalesce(srvacl, acldefault('S', srvowner)))).* FROM pg_foreign_server
          ) AS srv (srvname, srvowner, grantor, grantee, prtype, grantable),
          pg_authid u_grantor,
          (
@@ -2212,6 +2298,43 @@ CREATE VIEW usage_privileges AS
     WHERE u_grantor.oid = srv.grantor
           AND grantee.oid = srv.grantee
           AND srv.prtype IN ('USAGE')
+          AND (pg_has_role(u_grantor.oid, 'USAGE')
+               OR pg_has_role(grantee.oid, 'USAGE')
+               OR grantee.rolname = 'PUBLIC')
+
+    UNION ALL
+
+    /* sequences */
+    SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
+           CAST(grantee.rolname AS sql_identifier) AS grantee,
+           CAST(current_database() AS sql_identifier) AS object_catalog,
+           CAST(n.nspname AS sql_identifier) AS object_schema,
+           CAST(c.relname AS sql_identifier) AS object_name,
+           CAST('SEQUENCE' AS character_data) AS object_type,
+           CAST('USAGE' AS character_data) AS privilege_type,
+           CAST(
+             CASE WHEN
+                  -- object owner always has grant options
+                  pg_has_role(grantee.oid, c.relowner, 'USAGE')
+                  OR c.grantable
+                  THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
+
+    FROM (
+            SELECT oid, relname, relnamespace, relkind, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).* FROM pg_class
+         ) AS c (oid, relname, relnamespace, relkind, relowner, grantor, grantee, prtype, grantable),
+         pg_namespace n,
+         pg_authid u_grantor,
+         (
+           SELECT oid, rolname FROM pg_authid
+           UNION ALL
+           SELECT 0::oid, 'PUBLIC'
+         ) AS grantee (oid, rolname)
+
+    WHERE c.relnamespace = n.oid
+          AND c.relkind = 'S'
+          AND c.grantee = grantee.oid
+          AND c.grantor = u_grantor.oid
+          AND c.prtype IN ('USAGE')
           AND (pg_has_role(u_grantor.oid, 'USAGE')
                OR pg_has_role(grantee.oid, 'USAGE')
                OR grantee.rolname = 'PUBLIC');
@@ -2220,7 +2343,7 @@ GRANT SELECT ON usage_privileges TO PUBLIC;
 
 
 /*
- * 5.42
+ * 5.45
  * ROLE_USAGE_GRANTS view
  */
 
@@ -2241,7 +2364,7 @@ GRANT SELECT ON role_usage_grants TO PUBLIC;
 
 
 /*
- * 5.72
+ * 5.75
  * USER_DEFINED_TYPES view
  */
 
@@ -2288,7 +2411,7 @@ GRANT SELECT ON user_defined_types TO PUBLIC;
 
 
 /*
- * 5.73
+ * 5.76
  * VIEW_COLUMN_USAGE
  */
 
@@ -2327,7 +2450,15 @@ GRANT SELECT ON view_column_usage TO PUBLIC;
 
 
 /*
- * 5.74
+ * 5.77
+ * VIEW_PERIOD_USAGE
+ */
+
+-- feature not supported
+
+
+/*
+ * 5.78
  * VIEW_ROUTINE_USAGE
  */
 
@@ -2360,7 +2491,7 @@ GRANT SELECT ON view_routine_usage TO PUBLIC;
 
 
 /*
- * 5.75
+ * 5.79
  * VIEW_TABLE_USAGE
  */
 
@@ -2395,7 +2526,7 @@ GRANT SELECT ON view_table_usage TO PUBLIC;
 
 
 /*
- * 5.76
+ * 5.80
  * VIEWS view
  */
 
@@ -2410,16 +2541,23 @@ CREATE VIEW views AS
                   ELSE null END
              AS character_data) AS view_definition,
 
-           CAST('NONE' AS character_data) AS check_option,
+           CAST(
+             CASE WHEN 'check_option=cascaded' = ANY (c.reloptions)
+                  THEN 'CASCADED'
+                  WHEN 'check_option=local' = ANY (c.reloptions)
+                  THEN 'LOCAL'
+                  ELSE 'NONE' END
+             AS character_data) AS check_option,
 
            CAST(
-             CASE WHEN EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '2' AND is_instead)
-                   AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '4' AND is_instead)
+             -- (1 << CMD_UPDATE) + (1 << CMD_DELETE)
+             CASE WHEN pg_relation_is_updatable(c.oid, false) & 20 = 20
                   THEN 'YES' ELSE 'NO' END
              AS yes_or_no) AS is_updatable,
 
            CAST(
-             CASE WHEN EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '3' AND is_instead)
+             -- 1 << CMD_INSERT
+             CASE WHEN pg_relation_is_updatable(c.oid, false) & 8 = 8
                   THEN 'YES' ELSE 'NO' END
              AS yes_or_no) AS is_insertable_into,
 
@@ -2456,7 +2594,7 @@ GRANT SELECT ON views TO PUBLIC;
 -- The following views have dependencies that force them to appear out of order.
 
 /*
- * 5.24
+ * 5.25
  * DATA_TYPE_PRIVILEGES view
  */
 
@@ -2484,7 +2622,7 @@ GRANT SELECT ON data_type_privileges TO PUBLIC;
 
 
 /*
- * 5.29
+ * 5.30
  * ELEMENT_TYPES view
  */
 
@@ -2579,6 +2717,7 @@ GRANT SELECT ON element_types TO PUBLIC;
 
 
 -- SQL/MED views; these use section numbers from part 9 of the standard.
+-- (still SQL:2008; there is no SQL:2011 SQL/MED)
 
 /* Base view for foreign table columns */
 CREATE VIEW _pg_foreign_table_columns AS