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

diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index cea4f79775..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-2009, PostgreSQL Global Development Group
+ * Copyright (c) 2003-2016, PostgreSQL Global Development Group
  *
- * $PostgreSQL: pgsql/src/backend/catalog/information_schema.sql,v 1.57 2009/07/13 20:25:57 petere Exp $
+ * 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.
  */
 
 /*
@@ -24,7 +32,7 @@
 
 CREATE SCHEMA information_schema;
 GRANT USAGE ON SCHEMA information_schema TO PUBLIC;
-SET search_path TO information_schema, public;
+SET search_path TO information_schema;
 
 
 /*
@@ -42,18 +50,7 @@ CREATE FUNCTION _pg_expandarray(IN anyarray, OUT x anyelement, OUT n int)
 
 CREATE FUNCTION _pg_keysequal(smallint[], smallint[]) RETURNS boolean
     LANGUAGE sql IMMUTABLE  -- intentionally not STRICT, to allow inlining
-    AS 'select $1 <@ $2 and $2 <@ $1';
-
-/* Get the OID of the unique index that an FK constraint depends on */
-CREATE FUNCTION _pg_underlying_index(oid) RETURNS oid
-    LANGUAGE sql STRICT STABLE
-    AS $$
-SELECT refobjid FROM pg_catalog.pg_depend
-  WHERE classid = 'pg_catalog.pg_constraint'::pg_catalog.regclass AND
-        objid = $1 AND
-        refclassid = 'pg_catalog.pg_class'::pg_catalog.regclass AND
-        refobjsubid = 0 AND deptype = 'n'
-$$;
+    AS 'select $1 operator(pg_catalog.<@) $2 and $2 operator(pg_catalog.<@) $1';
 
 /* Given an index's OID and an underlying-table column number, return the
  * column's position in the index (NULL if not there) */
@@ -169,7 +166,18 @@ $$SELECT
        WHEN $1 IN (1083, 1114, 1184, 1266) /* time, timestamp, same + tz */
            THEN CASE WHEN $2 < 0 THEN 6 ELSE $2 END
        WHEN $1 IN (1186) /* interval */
-           THEN CASE WHEN $2 < 0 THEN 6 ELSE $2 & 65535 END
+           THEN CASE WHEN $2 < 0 OR $2 & 65535 = 65535 THEN 6 ELSE $2 & 65535 END
+       ELSE null
+  END$$;
+
+CREATE FUNCTION _pg_interval_type(typid oid, mod int4) RETURNS text
+    LANGUAGE sql
+    IMMUTABLE
+    RETURNS NULL ON NULL INPUT
+    AS
+$$SELECT
+  CASE WHEN $1 IN (1186) /* interval */
+           THEN upper(substring(format_type($1, $2) from 'interval[()0-9]* #"%#"' for '#'))
        ELSE null
   END$$;
 
@@ -285,7 +293,7 @@ CREATE VIEW attributes AS
            CAST(pg_get_expr(ad.adbin, ad.adrelid) AS character_data) AS attribute_default,
            CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
              AS yes_or_no)
-             AS is_nullable,
+             AS is_nullable, -- This column was apparently removed between SQL:2003 and SQL:2008.
 
            CAST(
              CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
@@ -308,9 +316,9 @@ CREATE VIEW attributes AS
            CAST(null AS sql_identifier) AS character_set_schema,
            CAST(null AS sql_identifier) AS character_set_name,
 
-           CAST(null AS sql_identifier) AS collation_catalog,
-           CAST(null AS sql_identifier) AS collation_schema,
-           CAST(null AS sql_identifier) AS collation_name,
+           CAST(CASE WHEN nco.nspname IS NOT NULL THEN current_database() END AS sql_identifier) AS collation_catalog,
+           CAST(nco.nspname AS sql_identifier) AS collation_schema,
+           CAST(co.collname AS sql_identifier) AS collation_name,
 
            CAST(
              _pg_numeric_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
@@ -332,8 +340,11 @@ CREATE VIEW attributes AS
              AS cardinal_number)
              AS datetime_precision,
 
-           CAST(null AS character_data) AS interval_type, -- FIXME
-           CAST(null AS character_data) AS interval_precision, -- FIXME
+           CAST(
+             _pg_interval_type(_pg_truetypid(a, t), _pg_truetypmod(a, t))
+             AS character_data)
+             AS interval_type,
+           CAST(null AS cardinal_number) AS interval_precision,
 
            CAST(current_database() AS sql_identifier) AS attribute_udt_catalog,
            CAST(nt.nspname AS sql_identifier) AS attribute_udt_schema,
@@ -347,15 +358,16 @@ CREATE VIEW attributes AS
            CAST(a.attnum AS sql_identifier) AS dtd_identifier,
            CAST('NO' AS yes_or_no) AS is_derived_reference_attribute
 
-    FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum),
-         pg_class c, pg_namespace nc,
-         (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
+    FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum)
+         JOIN (pg_class c JOIN pg_namespace nc ON (c.relnamespace = nc.oid)) ON a.attrelid = c.oid
+         JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON a.atttypid = t.oid
+         LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid))
+           ON a.attcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default')
 
-    WHERE a.attrelid = c.oid
-          AND a.atttypid = t.oid
-          AND nc.oid = c.relnamespace
-          AND a.attnum > 0 AND NOT a.attisdropped
-          AND c.relkind in ('c');
+    WHERE a.attnum > 0 AND NOT a.attisdropped
+          AND c.relkind in ('c')
+          AND (pg_has_role(c.relowner, 'USAGE')
+               OR has_type_privilege(c.reltype, 'USAGE'));
 
 GRANT SELECT ON attributes TO PUBLIC;
 
@@ -365,7 +377,23 @@ GRANT SELECT ON attributes TO PUBLIC;
  * CHARACTER_SETS view
  */
 
--- feature not supported
+CREATE VIEW character_sets AS
+    SELECT CAST(null AS sql_identifier) AS character_set_catalog,
+           CAST(null AS sql_identifier) AS character_set_schema,
+           CAST(getdatabaseencoding() AS sql_identifier) AS character_set_name,
+           CAST(CASE WHEN getdatabaseencoding() = 'UTF8' THEN 'UCS' ELSE getdatabaseencoding() END AS sql_identifier) AS character_repertoire,
+           CAST(getdatabaseencoding() AS sql_identifier) AS form_of_use,
+           CAST(current_database() AS sql_identifier) AS default_collate_catalog,
+           CAST(nc.nspname AS sql_identifier) AS default_collate_schema,
+           CAST(c.collname AS sql_identifier) AS default_collate_name
+    FROM pg_database d
+         LEFT JOIN (pg_collation c JOIN pg_namespace nc ON (c.collnamespace = nc.oid))
+             ON (datcollate = collcollate AND datctype = collctype)
+    WHERE d.datname = current_database()
+    ORDER BY char_length(c.collname) DESC, c.collname ASC -- prefer full/canonical name
+    LIMIT 1;
+
+GRANT SELECT ON character_sets TO PUBLIC;
 
 
 /*
@@ -436,14 +464,35 @@ GRANT SELECT ON check_constraints TO PUBLIC;
  * COLLATIONS view
  */
 
--- feature not supported
+CREATE VIEW collations AS
+    SELECT CAST(current_database() AS sql_identifier) AS collation_catalog,
+           CAST(nc.nspname AS sql_identifier) AS collation_schema,
+           CAST(c.collname AS sql_identifier) AS collation_name,
+           CAST('NO PAD' AS character_data) AS pad_attribute
+    FROM pg_collation c, pg_namespace nc
+    WHERE c.collnamespace = nc.oid
+          AND collencoding IN (-1, (SELECT encoding FROM pg_database WHERE datname = current_database()));
+
+GRANT SELECT ON collations TO PUBLIC;
+
 
 /*
  * 5.16
  * COLLATION_CHARACTER_SET_APPLICABILITY view
  */
 
--- feature not supported
+CREATE VIEW collation_character_set_applicability AS
+    SELECT CAST(current_database() AS sql_identifier) AS collation_catalog,
+           CAST(nc.nspname AS sql_identifier) AS collation_schema,
+           CAST(c.collname AS sql_identifier) AS collation_name,
+           CAST(null AS sql_identifier) AS character_set_catalog,
+           CAST(null AS sql_identifier) AS character_set_schema,
+           CAST(getdatabaseencoding() AS sql_identifier) AS character_set_name
+    FROM pg_collation c, pg_namespace nc
+    WHERE c.collnamespace = nc.oid
+          AND collencoding IN (-1, (SELECT encoding FROM pg_database WHERE datname = current_database()));
+
+GRANT SELECT ON collation_character_set_applicability TO PUBLIC;
 
 
 /*
@@ -476,7 +525,7 @@ CREATE VIEW column_domain_usage AS
           AND a.attrelid = c.oid
           AND a.atttypid = t.oid
           AND t.typtype = 'd'
-          AND c.relkind IN ('r', 'v')
+          AND c.relkind IN ('r', 'v', 'f')
           AND a.attnum > 0
           AND NOT a.attisdropped
           AND pg_has_role(t.typowner, 'USAGE');
@@ -494,42 +543,63 @@ CREATE VIEW column_privileges AS
            CAST(grantee.rolname AS sql_identifier) AS grantee,
            CAST(current_database() AS sql_identifier) AS table_catalog,
            CAST(nc.nspname AS sql_identifier) AS table_schema,
-           CAST(c.relname AS sql_identifier) AS table_name,
-           CAST(a.attname AS sql_identifier) AS column_name,
-           CAST(pr.type AS character_data) AS privilege_type,
+           CAST(x.relname AS sql_identifier) AS table_name,
+           CAST(x.attname AS sql_identifier) AS column_name,
+           CAST(x.prtype AS character_data) AS privilege_type,
            CAST(
              CASE WHEN
                   -- object owner always has grant options
-                  pg_has_role(grantee.oid, c.relowner, 'USAGE')
-                  OR aclcontains(c.relacl,
-                                 makeaclitem(grantee.oid, u_grantor.oid, pr.type, true))
-                  OR aclcontains(a.attacl,
-                                 makeaclitem(grantee.oid, u_grantor.oid, pr.type, true))
+                  pg_has_role(x.grantee, x.relowner, 'USAGE')
+                  OR x.grantable
                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
 
-    FROM pg_attribute a,
-         pg_class c,
+    FROM (
+           SELECT pr_c.grantor,
+                  pr_c.grantee,
+                  attname,
+                  relname,
+                  relnamespace,
+                  pr_c.prtype,
+                  pr_c.grantable,
+                  pr_c.relowner
+           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),
+                pg_attribute a
+           WHERE a.attrelid = pr_c.oid
+                 AND a.attnum > 0
+                 AND NOT a.attisdropped
+           UNION
+           SELECT pr_a.grantor,
+                  pr_a.grantee,
+                  attname,
+                  relname,
+                  relnamespace,
+                  pr_a.prtype,
+                  pr_a.grantable,
+                  c.relowner
+           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),
+                pg_class c
+           WHERE pr_a.attrelid = c.oid
+                 AND relkind IN ('r', 'v', 'f')
+         ) x,
          pg_namespace nc,
          pg_authid u_grantor,
          (
            SELECT oid, rolname FROM pg_authid
            UNION ALL
            SELECT 0::oid, 'PUBLIC'
-         ) AS grantee (oid, rolname),
-         (VALUES ('SELECT'),
-                 ('INSERT'),
-                 ('UPDATE'),
-                 ('REFERENCES')) AS pr (type)
+         ) AS grantee (oid, rolname)
 
-    WHERE a.attrelid = c.oid
-          AND c.relnamespace = nc.oid
-          AND a.attnum > 0
-          AND NOT a.attisdropped
-          AND c.relkind IN ('r', 'v')
-          AND (aclcontains(c.relacl,
-                           makeaclitem(grantee.oid, u_grantor.oid, pr.type, false))
-               OR aclcontains(a.attacl,
-                              makeaclitem(grantee.oid, u_grantor.oid, pr.type, false)))
+    WHERE x.relnamespace = nc.oid
+          AND x.grantee = grantee.oid
+          AND x.grantor = u_grantor.oid
+          AND x.prtype IN ('INSERT', 'SELECT', 'UPDATE', 'REFERENCES')
           AND (pg_has_role(u_grantor.oid, 'USAGE')
                OR pg_has_role(grantee.oid, 'USAGE')
                OR grantee.rolname = 'PUBLIC');
@@ -559,7 +629,7 @@ CREATE VIEW column_udt_usage AS
     WHERE a.attrelid = c.oid
           AND a.atttypid = t.oid
           AND nc.oid = c.relnamespace
-          AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
+          AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v', 'f')
           AND pg_has_role(coalesce(bt.typowner, t.typowner), 'USAGE');
 
 GRANT SELECT ON column_udt_usage TO PUBLIC;
@@ -624,16 +694,19 @@ CREATE VIEW columns AS
              AS cardinal_number)
              AS datetime_precision,
 
-           CAST(null AS character_data) AS interval_type, -- FIXME
-           CAST(null AS character_data) AS interval_precision, -- FIXME
+           CAST(
+             _pg_interval_type(_pg_truetypid(a, t), _pg_truetypmod(a, t))
+             AS character_data)
+             AS interval_type,
+           CAST(null AS cardinal_number) AS interval_precision,
 
            CAST(null AS sql_identifier) AS character_set_catalog,
            CAST(null AS sql_identifier) AS character_set_schema,
            CAST(null AS sql_identifier) AS character_set_name,
 
-           CAST(null AS sql_identifier) AS collation_catalog,
-           CAST(null AS sql_identifier) AS collation_schema,
-           CAST(null AS sql_identifier) AS collation_name,
+           CAST(CASE WHEN nco.nspname IS NOT NULL THEN current_database() END AS sql_identifier) AS collation_catalog,
+           CAST(nco.nspname AS sql_identifier) AS collation_schema,
+           CAST(co.collname AS sql_identifier) AS collation_name,
 
            CAST(CASE WHEN t.typtype = 'd' THEN current_database() ELSE null END
              AS sql_identifier) AS domain_catalog,
@@ -665,24 +738,22 @@ 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),
-         pg_class c, pg_namespace nc,
-         (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
-           LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
+    FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum)
+         JOIN (pg_class c JOIN pg_namespace nc ON (c.relnamespace = nc.oid)) ON a.attrelid = c.oid
+         JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON a.atttypid = t.oid
+         LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
            ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
+         LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid))
+           ON a.attcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default')
 
-    WHERE a.attrelid = c.oid
-          AND a.atttypid = t.oid
-          AND nc.oid = c.relnamespace
-          AND (NOT pg_is_other_temp_schema(nc.oid))
+    WHERE (NOT pg_is_other_temp_schema(nc.oid))
 
-          AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
+          AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v', 'f')
 
           AND (pg_has_role(c.relowner, 'USAGE')
                OR has_column_privilege(c.oid, a.attnum,
@@ -745,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
  */
 
@@ -768,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
  */
 
@@ -780,7 +859,7 @@ GRANT SELECT ON constraint_table_usage TO PUBLIC;
 
 
 /*
- * 5.26
+ * 5.27
  * DIRECT_SUPERTYPES view
  */
 
@@ -788,7 +867,7 @@ GRANT SELECT ON constraint_table_usage TO PUBLIC;
 
 
 /*
- * 5.27
+ * 5.28
  * DOMAIN_CONSTRAINTS view
  */
 
@@ -806,7 +885,9 @@ CREATE VIEW domain_constraints AS
     FROM pg_namespace rs, pg_namespace n, pg_constraint con, pg_type t
     WHERE rs.oid = con.connamespace
           AND n.oid = t.typnamespace
-          AND t.oid = con.contypid;
+          AND t.oid = con.contypid
+          AND (pg_has_role(t.typowner, 'USAGE')
+               OR has_type_privilege(t.oid, 'USAGE'));
 
 GRANT SELECT ON domain_constraints TO PUBLIC;
 
@@ -837,7 +918,7 @@ GRANT SELECT ON domain_udt_usage TO PUBLIC;
 
 
 /*
- * 5.28
+ * 5.29
  * DOMAINS view
  */
 
@@ -867,9 +948,9 @@ CREATE VIEW domains AS
            CAST(null AS sql_identifier) AS character_set_schema,
            CAST(null AS sql_identifier) AS character_set_name,
 
-           CAST(null AS sql_identifier) AS collation_catalog,
-           CAST(null AS sql_identifier) AS collation_schema,
-           CAST(null AS sql_identifier) AS collation_name,
+           CAST(CASE WHEN nco.nspname IS NOT NULL THEN current_database() END AS sql_identifier) AS collation_catalog,
+           CAST(nco.nspname AS sql_identifier) AS collation_schema,
+           CAST(co.collname AS sql_identifier) AS collation_name,
 
            CAST(
              _pg_numeric_precision(t.typbasetype, t.typtypmod)
@@ -891,8 +972,11 @@ CREATE VIEW domains AS
              AS cardinal_number)
              AS datetime_precision,
 
-           CAST(null AS character_data) AS interval_type, -- FIXME
-           CAST(null AS character_data) AS interval_precision, -- FIXME
+           CAST(
+             _pg_interval_type(t.typbasetype, t.typtypmod)
+             AS character_data)
+             AS interval_type,
+           CAST(null AS cardinal_number) AS interval_precision,
 
            CAST(t.typdefault AS character_data) AS domain_default,
 
@@ -907,22 +991,23 @@ CREATE VIEW domains AS
            CAST(null AS cardinal_number) AS maximum_cardinality,
            CAST(1 AS sql_identifier) AS dtd_identifier
 
-    FROM pg_type t, pg_namespace nt,
-         pg_type bt, pg_namespace nbt
+    FROM (pg_type t JOIN pg_namespace nt ON t.typnamespace = nt.oid)
+         JOIN (pg_type bt JOIN pg_namespace nbt ON bt.typnamespace = nbt.oid)
+           ON (t.typbasetype = bt.oid AND t.typtype = 'd')
+         LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid))
+           ON t.typcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default')
 
-    WHERE t.typnamespace = nt.oid
-          AND t.typbasetype = bt.oid
-          AND bt.typnamespace = nbt.oid
-          AND t.typtype = 'd';
+    WHERE (pg_has_role(t.typowner, 'USAGE')
+           OR has_type_privilege(t.oid, 'USAGE'));
 
 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
  */
 
@@ -935,7 +1020,7 @@ GRANT SELECT ON enabled_roles TO PUBLIC;
 
 
 /*
- * 5.31
+ * 5.32
  * FIELDS view
  */
 
@@ -943,7 +1028,7 @@ GRANT SELECT ON enabled_roles TO PUBLIC;
 
 
 /*
- * 5.32
+ * 5.33
  * KEY_COLUMN_USAGE view
  */
 
@@ -957,15 +1042,15 @@ CREATE VIEW key_column_usage AS
            CAST(a.attname AS sql_identifier) AS column_name,
            CAST((ss.x).n AS cardinal_number) AS ordinal_position,
            CAST(CASE WHEN contype = 'f' THEN
-                  _pg_index_position(_pg_underlying_index(ss.coid),
-                                     ss.confkey[(ss.x).n])
+                       _pg_index_position(ss.conindid, ss.confkey[(ss.x).n])
                      ELSE NULL
                 END AS cardinal_number)
              AS position_in_unique_constraint
     FROM pg_attribute a,
          (SELECT r.oid AS roid, r.relname, r.relowner,
                  nc.nspname AS nc_nspname, nr.nspname AS nr_nspname,
-                 c.oid AS coid, c.conname, c.contype, c.confkey, c.confrelid,
+                 c.oid AS coid, c.conname, c.contype, c.conindid,
+                 c.confkey, c.confrelid,
                  _pg_expandarray(c.conkey) AS x
           FROM pg_namespace nr, pg_class r, pg_namespace nc,
                pg_constraint c
@@ -986,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
  */
 
@@ -994,7 +1087,7 @@ GRANT SELECT ON key_column_usage TO PUBLIC;
 
 
 /*
- * 5.34
+ * 5.36
  * METHOD_SPECIFICATIONS view
  */
 
@@ -1002,7 +1095,7 @@ GRANT SELECT ON key_column_usage TO PUBLIC;
 
 
 /*
- * 5.35
+ * 5.37
  * PARAMETERS view
  */
 
@@ -1040,7 +1133,7 @@ CREATE VIEW parameters AS
            CAST(null AS cardinal_number) AS numeric_scale,
            CAST(null AS cardinal_number) AS datetime_precision,
            CAST(null AS character_data) AS interval_type,
-           CAST(null AS character_data) AS interval_precision,
+           CAST(null AS cardinal_number) AS interval_precision,
            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,
@@ -1048,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
@@ -1064,7 +1162,15 @@ GRANT SELECT ON parameters TO PUBLIC;
 
 
 /*
- * 5.36
+ * 5.38
+ * PERIODS view
+ */
+
+-- feature not supported
+
+
+/*
+ * 5.39
  * REFERENCED_TYPES view
  */
 
@@ -1072,7 +1178,7 @@ GRANT SELECT ON parameters TO PUBLIC;
 
 
 /*
- * 5.37
+ * 5.40
  * REFERENTIAL_CONSTRAINTS view
  */
 
@@ -1090,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(
@@ -1111,240 +1217,88 @@ CREATE VIEW referential_constraints AS
 
     FROM (pg_namespace ncon
           INNER JOIN pg_constraint con ON ncon.oid = con.connamespace
-          INNER JOIN pg_class c ON con.conrelid = c.oid)
-         LEFT JOIN
-         (pg_constraint pkc
-          INNER JOIN pg_namespace npkc ON pkc.connamespace = npkc.oid)
-         ON con.confrelid = pkc.conrelid
-            AND _pg_keysequal(con.confkey, pkc.conkey)
-
-    WHERE c.relkind = 'r'
-          AND con.contype = 'f'
-          AND (pkc.contype IN ('p', 'u') OR pkc.contype IS NULL)
-          AND (pg_has_role(c.relowner, 'USAGE')
-               -- SELECT privilege omitted, per SQL standard
-               OR has_table_privilege(c.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
-               OR has_any_column_privilege(c.oid, 'INSERT, UPDATE, REFERENCES') );
+          INNER JOIN pg_class c ON con.conrelid = c.oid AND con.contype = 'f')
+         LEFT JOIN pg_depend d1  -- find constraint's dependency on an index
+          ON d1.objid = con.oid AND d1.classid = 'pg_constraint'::regclass
+             AND d1.refclassid = 'pg_class'::regclass AND d1.refobjsubid = 0
+         LEFT JOIN pg_depend d2  -- find pkey/unique constraint for that index
+          ON d2.refclassid = 'pg_constraint'::regclass
+             AND d2.classid = 'pg_class'::regclass
+             AND d2.objid = d1.refobjid AND d2.objsubid = 0
+             AND d2.deptype = 'i'
+         LEFT JOIN pg_constraint pkc ON pkc.oid = d2.refobjid
+            AND pkc.contype IN ('p', 'u')
+            AND pkc.conrelid = con.confrelid
+         LEFT JOIN pg_namespace npkc ON pkc.connamespace = npkc.oid
+
+    WHERE pg_has_role(c.relowner, 'USAGE')
+          -- SELECT privilege omitted, per SQL standard
+          OR has_table_privilege(c.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
+          OR has_any_column_privilege(c.oid, 'INSERT, UPDATE, REFERENCES') ;
 
 GRANT SELECT ON referential_constraints TO PUBLIC;
 
 
 /*
- * 5.38
+ * 5.41
  * ROLE_COLUMN_GRANTS view
  */
 
 CREATE VIEW role_column_grants AS
-    SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
-           CAST(g_grantee.rolname AS sql_identifier) AS grantee,
-           CAST(current_database() AS sql_identifier) AS table_catalog,
-           CAST(nc.nspname AS sql_identifier) AS table_schema,
-           CAST(c.relname AS sql_identifier) AS table_name,
-           CAST(a.attname AS sql_identifier) AS column_name,
-           CAST(pr.type AS character_data) AS privilege_type,
-           CAST(
-             CASE WHEN
-                  -- object owner always has grant options
-                  pg_has_role(g_grantee.oid, c.relowner, 'USAGE')
-                  OR aclcontains(c.relacl,
-                                 makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, true))
-                  OR aclcontains(a.attacl,
-                                 makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, true))
-                  THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
-
-    FROM pg_attribute a,
-         pg_class c,
-         pg_namespace nc,
-         pg_authid u_grantor,
-         pg_authid g_grantee,
-         (VALUES ('SELECT'),
-                 ('INSERT'),
-                 ('UPDATE'),
-                 ('REFERENCES')) AS pr (type)
-
-    WHERE a.attrelid = c.oid
-          AND c.relnamespace = nc.oid
-          AND a.attnum > 0
-          AND NOT a.attisdropped
-          AND c.relkind IN ('r', 'v')
-          AND (aclcontains(c.relacl,
-                           makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, false))
-               OR aclcontains(a.attacl,
-                              makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, false)))
-          AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
-               OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));
+    SELECT grantor,
+           grantee,
+           table_catalog,
+           table_schema,
+           table_name,
+           column_name,
+           privilege_type,
+           is_grantable
+    FROM column_privileges
+    WHERE grantor IN (SELECT role_name FROM enabled_roles)
+          OR grantee IN (SELECT role_name FROM enabled_roles);
 
 GRANT SELECT ON role_column_grants TO PUBLIC;
 
 
-/*
- * 5.39
- * ROLE_ROUTINE_GRANTS view
- */
-
-CREATE VIEW role_routine_grants AS
-    SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
-           CAST(g_grantee.rolname AS sql_identifier) AS grantee,
-           CAST(current_database() AS sql_identifier) AS specific_catalog,
-           CAST(n.nspname AS sql_identifier) AS specific_schema,
-           CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
-           CAST(current_database() AS sql_identifier) AS routine_catalog,
-           CAST(n.nspname AS sql_identifier) AS routine_schema,
-           CAST(p.proname AS sql_identifier) AS routine_name,
-           CAST('EXECUTE' AS character_data) AS privilege_type,
-           CAST(
-             CASE WHEN
-                  -- object owner always has grant options
-                  pg_has_role(g_grantee.oid, p.proowner, 'USAGE')
-                  OR aclcontains(p.proacl,
-                                 makeaclitem(g_grantee.oid, u_grantor.oid, 'EXECUTE', true))
-                  THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
-
-    FROM pg_proc p,
-         pg_namespace n,
-         pg_authid u_grantor,
-         pg_authid g_grantee
-
-    WHERE p.pronamespace = n.oid
-          AND aclcontains(p.proacl,
-                          makeaclitem(g_grantee.oid, u_grantor.oid, 'EXECUTE', false))
-          AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
-               OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));
-
-GRANT SELECT ON role_routine_grants TO PUBLIC;
-
-
-/*
- * 5.40
- * ROLE_TABLE_GRANTS view
- */
-
-CREATE VIEW role_table_grants AS
-    SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
-           CAST(g_grantee.rolname AS sql_identifier) AS grantee,
-           CAST(current_database() AS sql_identifier) AS table_catalog,
-           CAST(nc.nspname AS sql_identifier) AS table_schema,
-           CAST(c.relname AS sql_identifier) AS table_name,
-           CAST(pr.type AS character_data) AS privilege_type,
-           CAST(
-             CASE WHEN
-                  -- object owner always has grant options
-                  pg_has_role(g_grantee.oid, c.relowner, 'USAGE')
-                  OR aclcontains(c.relacl,
-                                 makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, true))
-                  THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable,
-           CAST('NO' AS yes_or_no) AS with_hierarchy
-
-    FROM pg_class c,
-         pg_namespace nc,
-         pg_authid u_grantor,
-         pg_authid g_grantee,
-         (VALUES ('SELECT'),
-                 ('INSERT'),
-                 ('UPDATE'),
-                 ('DELETE'),
-                 ('TRUNCATE'),
-                 ('REFERENCES'),
-                 ('TRIGGER')) AS pr (type)
+-- 5.42 ROLE_ROUTINE_GRANTS view is based on 5.49 ROUTINE_PRIVILEGES and is defined there instead.
 
-    WHERE c.relnamespace = nc.oid
-          AND c.relkind IN ('r', 'v')
-          AND aclcontains(c.relacl,
-                          makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, false))
-          AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
-               OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));
 
-GRANT SELECT ON role_table_grants TO PUBLIC;
+-- 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
  */
 
 -- feature not supported
 
 
-/*
- * 5.42
- * ROLE_USAGE_GRANTS view
- */
-
-CREATE VIEW role_usage_grants AS
-
-    /* foreign-data wrappers */
-    SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
-           CAST(g_grantee.rolname AS sql_identifier) AS grantee,
-           CAST(current_database() AS sql_identifier) AS object_catalog,
-           CAST('' AS sql_identifier) AS object_schema,
-           CAST(fdw.fdwname AS sql_identifier) AS object_name,
-           CAST('FOREIGN DATA WRAPPER' 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(g_grantee.oid, fdw.fdwowner, 'USAGE')
-                  OR aclcontains(fdw.fdwacl,
-                                 makeaclitem(g_grantee.oid, u_grantor.oid, 'USAGE', true))
-                  THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
-
-    FROM pg_foreign_data_wrapper fdw,
-         pg_authid u_grantor,
-         pg_authid g_grantee
-
-    WHERE aclcontains(fdw.fdwacl,
-                          makeaclitem(g_grantee.oid, u_grantor.oid, 'USAGE', false))
-          AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
-               OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles))
-
-    UNION ALL
-
-    /* foreign server */
-    SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
-           CAST(g_grantee.rolname AS sql_identifier) AS grantee,
-           CAST(current_database() AS sql_identifier) AS object_catalog,
-           CAST('' AS sql_identifier) AS object_schema,
-           CAST(srv.srvname AS sql_identifier) AS object_name,
-           CAST('FOREIGN SERVER' 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(g_grantee.oid, srv.srvowner, 'USAGE')
-                  OR aclcontains(srv.srvacl,
-                                 makeaclitem(g_grantee.oid, u_grantor.oid, 'USAGE', true))
-                  THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
 
-    FROM pg_foreign_server srv,
-         pg_authid u_grantor,
-         pg_authid g_grantee
+-- 5.45 ROLE_USAGE_GRANTS view is based on 5.74 USAGE_PRIVILEGES and is defined there instead.
 
-    WHERE aclcontains(srv.srvacl,
-                          makeaclitem(g_grantee.oid, u_grantor.oid, 'USAGE', false))
-          AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
-               OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));
 
-GRANT SELECT ON role_usage_grants TO PUBLIC;
+-- 5.46 ROLE_UDT_GRANTS view is based on 5.73 UDT_PRIVILEGES and is defined there instead.
 
 
 /*
- * 5.43
- * ROLE_UDT_GRANTS view
+ * 5.47
+ * ROUTINE_COLUMN_USAGE view
  */
 
--- feature not supported
+-- not tracked by PostgreSQL
 
 
 /*
- * 5.44
- * ROUTINE_COLUMN_USAGE view
+ * 5.48
+ * ROUTINE_PERIOD_USAGE view
  */
 
--- not tracked by PostgreSQL
+-- feature not supported
 
 
 /*
- * 5.45
+ * 5.49
  * ROUTINE_PRIVILEGES view
  */
 
@@ -1362,11 +1316,12 @@ CREATE VIEW routine_privileges AS
              CASE WHEN
                   -- object owner always has grant options
                   pg_has_role(grantee.oid, p.proowner, 'USAGE')
-                  OR aclcontains(p.proacl,
-                                 makeaclitem(grantee.oid, u_grantor.oid, 'EXECUTE', true))
+                  OR p.grantable
                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
 
-    FROM pg_proc p,
+    FROM (
+            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,
          (
@@ -1376,8 +1331,9 @@ CREATE VIEW routine_privileges AS
          ) AS grantee (oid, rolname)
 
     WHERE p.pronamespace = n.oid
-          AND aclcontains(p.proacl,
-                          makeaclitem(grantee.oid, u_grantor.oid, 'EXECUTE', false))
+          AND grantee.oid = p.grantee
+          AND u_grantor.oid = p.grantor
+          AND p.prtype IN ('EXECUTE')
           AND (pg_has_role(u_grantor.oid, 'USAGE')
                OR pg_has_role(grantee.oid, 'USAGE')
                OR grantee.rolname = 'PUBLIC');
@@ -1386,15 +1342,38 @@ GRANT SELECT ON routine_privileges TO PUBLIC;
 
 
 /*
- * 5.46
+ * 5.42
+ * ROLE_ROUTINE_GRANTS view
+ */
+
+CREATE VIEW role_routine_grants AS
+    SELECT grantor,
+           grantee,
+           specific_catalog,
+           specific_schema,
+           specific_name,
+           routine_catalog,
+           routine_schema,
+           routine_name,
+           privilege_type,
+           is_grantable
+    FROM routine_privileges
+    WHERE grantor IN (SELECT role_name FROM enabled_roles)
+          OR grantee IN (SELECT role_name FROM enabled_roles);
+
+GRANT SELECT ON role_routine_grants TO PUBLIC;
+
+
+/*
+ * 5.50
  * ROUTINE_ROUTINE_USAGE view
  */
 
 -- not tracked by PostgreSQL
 
 
-/* 
- * 5.47
+/*
+ * 5.51
  * ROUTINE_SEQUENCE_USAGE view
  */
 
@@ -1402,7 +1381,7 @@ GRANT SELECT ON routine_privileges TO PUBLIC;
 
 
 /*
- * 5.48
+ * 5.52
  * ROUTINE_TABLE_USAGE view
  */
 
@@ -1410,7 +1389,7 @@ GRANT SELECT ON routine_privileges TO PUBLIC;
 
 
 /*
- * 5.49
+ * 5.53
  * ROUTINES view
  */
 
@@ -1447,7 +1426,7 @@ CREATE VIEW routines AS
            CAST(null AS cardinal_number) AS numeric_scale,
            CAST(null AS cardinal_number) AS datetime_precision,
            CAST(null AS character_data) AS interval_type,
-           CAST(null AS character_data) AS interval_precision,
+           CAST(null AS cardinal_number) AS interval_precision,
            CAST(current_database() AS sql_identifier) AS type_udt_catalog,
            CAST(nt.nspname AS sql_identifier) AS type_udt_schema,
            CAST(t.typname AS sql_identifier) AS type_udt_name,
@@ -1484,7 +1463,7 @@ CREATE VIEW routines AS
            CAST(null AS time_stamp) AS created,
            CAST(null AS time_stamp) AS last_altered,
            CAST(null AS yes_or_no) AS new_savepoint_level,
-           CAST('YES' AS yes_or_no) AS is_udt_dependent, -- FIXME?
+           CAST('NO' AS yes_or_no) AS is_udt_dependent,
 
            CAST(null AS character_data) AS result_cast_from_data_type,
            CAST(null AS yes_or_no) AS result_cast_as_locator,
@@ -1501,7 +1480,7 @@ CREATE VIEW routines AS
            CAST(null AS cardinal_number) AS result_cast_numeric_scale,
            CAST(null AS cardinal_number) AS result_cast_datetime_precision,
            CAST(null AS character_data) AS result_cast_interval_type,
-           CAST(null AS character_data) AS result_cast_interval_precision,
+           CAST(null AS cardinal_number) AS result_cast_interval_precision,
            CAST(null AS sql_identifier) AS result_cast_type_udt_catalog,
            CAST(null AS sql_identifier) AS result_cast_type_udt_schema,
            CAST(null AS sql_identifier) AS result_cast_type_udt_name,
@@ -1509,7 +1488,7 @@ CREATE VIEW routines AS
            CAST(null AS sql_identifier) AS result_cast_scope_schema,
            CAST(null AS sql_identifier) AS result_cast_scope_name,
            CAST(null AS cardinal_number) AS result_cast_maximum_cardinality,
-           CAST(null AS sql_identifier) AS result_cast_dtd_identifier           
+           CAST(null AS sql_identifier) AS result_cast_dtd_identifier
 
     FROM pg_namespace n, pg_proc p, pg_language l,
          pg_type t, pg_namespace nt
@@ -1523,7 +1502,7 @@ GRANT SELECT ON routines TO PUBLIC;
 
 
 /*
- * 5.50
+ * 5.54
  * SCHEMATA view
  */
 
@@ -1536,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
  */
 
@@ -1554,22 +1535,23 @@ 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,
-           CAST(null AS cardinal_number) AS maximum_value, -- FIXME
-           CAST(null AS cardinal_number) AS minimum_value, -- FIXME
-           CAST(null AS cardinal_number) AS increment,     -- FIXME
-           CAST(null AS yes_or_no) AS cycle_option    -- FIXME
-    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))
           AND (pg_has_role(c.relowner, 'USAGE')
-               OR has_table_privilege(c.oid, 'SELECT, UPDATE') );
+               OR has_sequence_privilege(c.oid, 'SELECT, UPDATE, USAGE') );
 
 GRANT SELECT ON sequences TO PUBLIC;
 
 
 /*
- * 5.52
+ * 5.56
  * SQL_FEATURES table
  */
 
@@ -1589,7 +1571,7 @@ GRANT SELECT ON sql_features TO PUBLIC;
 
 
 /*
- * 5.53
+ * 5.57
  * SQL_IMPLEMENTATION_INFO table
  */
 
@@ -1605,7 +1587,7 @@ CREATE TABLE sql_implementation_info (
 ) WITHOUT OIDS;
 
 INSERT INTO sql_implementation_info VALUES ('10003', 'CATALOG NAME', NULL, 'Y', NULL);
-INSERT INTO sql_implementation_info VALUES ('10004', 'COLLATING SEQUENCE', NULL, '', 'not supported');
+INSERT INTO sql_implementation_info VALUES ('10004', 'COLLATING SEQUENCE', NULL, (SELECT default_collate_name FROM character_sets), NULL);
 INSERT INTO sql_implementation_info VALUES ('23',    'CURSOR COMMIT BEHAVIOR', 1, NULL, 'close cursors and retain prepared statements');
 INSERT INTO sql_implementation_info VALUES ('2',     'DATA SOURCE NAME', NULL, '', NULL);
 INSERT INTO sql_implementation_info VALUES ('17',    'DBMS NAME', NULL, (select trim(trailing ' ' from substring(version() from '^[^0-9]*'))), NULL);
@@ -1644,8 +1626,8 @@ GRANT SELECT ON sql_languages TO PUBLIC;
 
 
 /*
- * 5.54
  * SQL_PACKAGES table
+ * removed in SQL:2011
  */
 
 CREATE TABLE sql_packages (
@@ -1671,7 +1653,7 @@ GRANT SELECT ON sql_packages TO PUBLIC;
 
 
 /*
- * 5.55
+ * 5.58
  * SQL_PARTS table
  */
 
@@ -1695,7 +1677,7 @@ INSERT INTO sql_parts VALUES ('14', 'XML-Related Specifications (SQL/XML)', 'YES
 
 
 /*
- * 5.56
+ * 5.59
  * SQL_SIZING table
  */
 
@@ -1741,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.
@@ -1761,7 +1743,7 @@ GRANT SELECT ON sql_sizing_profiles TO PUBLIC;
 
 
 /*
- * 5.58
+ * 5.60
  * TABLE_CONSTRAINTS view
  */
 
@@ -1790,6 +1772,7 @@ CREATE VIEW table_constraints AS
 
     WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace
           AND c.conrelid = r.oid
+          AND c.contype NOT IN ('t', 'x')  -- ignore nonstandard constraints
           AND r.relkind = 'r'
           AND (NOT pg_is_other_temp_schema(nr.oid))
           AND (pg_has_role(r.relowner, 'USAGE')
@@ -1831,7 +1814,7 @@ GRANT SELECT ON table_constraints TO PUBLIC;
 
 
 /*
- * 5.59
+ * 5.61
  * TABLE_METHOD_PRIVILEGES view
  */
 
@@ -1839,7 +1822,7 @@ GRANT SELECT ON table_constraints TO PUBLIC;
 
 
 /*
- * 5.60
+ * 5.62
  * TABLE_PRIVILEGES view
  */
 
@@ -1849,36 +1832,31 @@ CREATE VIEW table_privileges AS
            CAST(current_database() AS sql_identifier) AS table_catalog,
            CAST(nc.nspname AS sql_identifier) AS table_schema,
            CAST(c.relname AS sql_identifier) AS table_name,
-           CAST(pr.type AS character_data) AS privilege_type,
+           CAST(c.prtype AS character_data) AS privilege_type,
            CAST(
              CASE WHEN
                   -- object owner always has grant options
                   pg_has_role(grantee.oid, c.relowner, 'USAGE')
-                  OR aclcontains(c.relacl,
-                                 makeaclitem(grantee.oid, u_grantor.oid, pr.type, true))
+                  OR c.grantable
                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable,
-           CAST('NO' AS yes_or_no) AS with_hierarchy
+           CAST(CASE WHEN c.prtype = 'SELECT' THEN 'YES' ELSE 'NO' END AS yes_or_no) AS with_hierarchy
 
-    FROM pg_class c,
+    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 nc,
          pg_authid u_grantor,
          (
            SELECT oid, rolname FROM pg_authid
            UNION ALL
            SELECT 0::oid, 'PUBLIC'
-         ) AS grantee (oid, rolname),
-         (VALUES ('SELECT'),
-                 ('INSERT'),
-                 ('UPDATE'),
-                 ('DELETE'),
-                 ('TRUNCATE'),
-                 ('REFERENCES'),
-                 ('TRIGGER')) AS pr (type)
+         ) AS grantee (oid, rolname)
 
     WHERE c.relnamespace = nc.oid
           AND c.relkind IN ('r', 'v')
-          AND aclcontains(c.relacl,
-                          makeaclitem(grantee.oid, u_grantor.oid, pr.type, false))
+          AND c.grantee = grantee.oid
+          AND c.grantor = u_grantor.oid
+          AND c.prtype IN ('INSERT', 'SELECT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER')
           AND (pg_has_role(u_grantor.oid, 'USAGE')
                OR pg_has_role(grantee.oid, 'USAGE')
                OR grantee.rolname = 'PUBLIC');
@@ -1887,7 +1865,28 @@ GRANT SELECT ON table_privileges TO PUBLIC;
 
 
 /*
- * 5.61
+ * 5.43
+ * ROLE_TABLE_GRANTS view
+ */
+
+CREATE VIEW role_table_grants AS
+    SELECT grantor,
+           grantee,
+           table_catalog,
+           table_schema,
+           table_name,
+           privilege_type,
+           is_grantable,
+           with_hierarchy
+    FROM table_privileges
+    WHERE grantor IN (SELECT role_name FROM enabled_roles)
+          OR grantee IN (SELECT role_name FROM enabled_roles);
+
+GRANT SELECT ON role_table_grants TO PUBLIC;
+
+
+/*
+ * 5.63
  * TABLES view
  */
 
@@ -1900,31 +1899,30 @@ CREATE VIEW tables AS
              CASE WHEN nc.oid = pg_my_temp_schema() THEN 'LOCAL TEMPORARY'
                   WHEN c.relkind = 'r' THEN 'BASE TABLE'
                   WHEN c.relkind = 'v' THEN 'VIEW'
+                  WHEN c.relkind = 'f' THEN 'FOREIGN TABLE'
                   ELSE null END
              AS character_data) AS table_type,
 
            CAST(null AS sql_identifier) AS self_referencing_column_name,
            CAST(null AS character_data) AS reference_generation,
 
-           CAST(null AS sql_identifier) AS user_defined_type_catalog,
-           CAST(null AS sql_identifier) AS user_defined_type_schema,
-           CAST(null AS sql_identifier) AS user_defined_type_name,
+           CAST(CASE WHEN t.typname IS NOT NULL THEN current_database() ELSE null END AS sql_identifier) AS user_defined_type_catalog,
+           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('NO' AS yes_or_no) AS is_typed,
-           CAST(
-             CASE WHEN nc.oid = pg_my_temp_schema() THEN 'PRESERVE' -- FIXME
-                  ELSE null END
-             AS character_data) AS commit_action
+           CAST(CASE WHEN t.typname IS NOT NULL THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_typed,
+           CAST(null AS character_data) AS commit_action
 
-    FROM pg_namespace nc, pg_class c
+    FROM pg_namespace nc JOIN pg_class c ON (nc.oid = c.relnamespace)
+           LEFT JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON (c.reloftype = t.oid)
 
-    WHERE c.relnamespace = nc.oid
-          AND c.relkind IN ('r', 'v')
+    WHERE c.relkind IN ('r', 'v', 'f')
           AND (NOT pg_is_other_temp_schema(nc.oid))
           AND (pg_has_role(c.relowner, 'USAGE')
                OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
@@ -1934,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
  */
 
@@ -1950,28 +1980,39 @@ GRANT SELECT ON tables TO PUBLIC;
 
 
 /*
- * 5.64
+ * 5.66
  * TRIGGERED_UPDATE_COLUMNS view
  */
 
--- PostgreSQL doesn't allow the specification of individual triggered
--- update columns, so this view is empty.
-
 CREATE VIEW triggered_update_columns AS
     SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
-           CAST(null AS sql_identifier) AS trigger_schema,
-           CAST(null AS sql_identifier) AS trigger_name,
+           CAST(n.nspname AS sql_identifier) AS trigger_schema,
+           CAST(t.tgname AS sql_identifier) AS trigger_name,
            CAST(current_database() AS sql_identifier) AS event_object_catalog,
-           CAST(null AS sql_identifier) AS event_object_schema,
-           CAST(null AS sql_identifier) AS event_object_table,
-           CAST(null AS sql_identifier) AS event_object_column
-    WHERE false;
+           CAST(n.nspname AS sql_identifier) AS event_object_schema,
+           CAST(c.relname AS sql_identifier) AS event_object_table,
+           CAST(a.attname AS sql_identifier) AS event_object_column
+
+    FROM pg_namespace n, pg_class c, pg_trigger t,
+         (SELECT tgoid, (ta0.tgat).x AS tgattnum, (ta0.tgat).n AS tgattpos
+          FROM (SELECT oid AS tgoid, information_schema._pg_expandarray(tgattr) AS tgat FROM pg_trigger) AS ta0) AS ta,
+         pg_attribute a
+
+    WHERE n.oid = c.relnamespace
+          AND c.oid = t.tgrelid
+          AND t.oid = ta.tgoid
+          AND (a.attrelid, a.attnum) = (t.tgrelid, ta.tgattnum)
+          AND NOT t.tgisinternal
+          AND (NOT pg_is_other_temp_schema(n.oid))
+          AND (pg_has_role(c.relowner, 'USAGE')
+               -- SELECT privilege omitted, per SQL standard
+               OR has_column_privilege(c.oid, a.attnum, 'INSERT, UPDATE, REFERENCES') );
 
 GRANT SELECT ON triggered_update_columns TO PUBLIC;
 
 
 /*
- * 5.65
+ * 5.67
  * TRIGGER_COLUMN_USAGE view
  */
 
@@ -1979,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
  */
 
@@ -1987,7 +2036,7 @@ GRANT SELECT ON triggered_update_columns TO PUBLIC;
 
 
 /*
- * 5.67
+ * 5.70
  * TRIGGER_SEQUENCE_USAGE view
  */
 
@@ -1995,7 +2044,7 @@ GRANT SELECT ON triggered_update_columns TO PUBLIC;
 
 
 /*
- * 5.68
+ * 5.71
  * TRIGGER_TABLE_USAGE view
  */
 
@@ -2003,7 +2052,7 @@ GRANT SELECT ON triggered_update_columns TO PUBLIC;
 
 
 /*
- * 5.69
+ * 5.72
  * TRIGGERS view
  */
 
@@ -2016,24 +2065,33 @@ CREATE VIEW triggers AS
            CAST(n.nspname AS sql_identifier) AS event_object_schema,
            CAST(c.relname AS sql_identifier) AS event_object_table,
            CAST(null AS cardinal_number) AS action_order,
-           CAST(null AS character_data) AS action_condition,
+           -- XXX strange hacks follow
+           CAST(
+             CASE WHEN pg_has_role(c.relowner, 'USAGE')
+               THEN (SELECT m[1] FROM regexp_matches(pg_get_triggerdef(t.oid), E'.{35,} WHEN \\((.+)\\) EXECUTE PROCEDURE') AS rm(m) LIMIT 1)
+               ELSE null END
+             AS character_data) AS action_condition,
            CAST(
              substring(pg_get_triggerdef(t.oid) from
                        position('EXECUTE PROCEDURE' in substring(pg_get_triggerdef(t.oid) from 48)) + 47)
              AS character_data) AS action_statement,
            CAST(
-             CASE WHEN t.tgtype & 1 = 1 THEN 'ROW' ELSE 'STATEMENT' END
+             -- hard-wired reference to TRIGGER_TYPE_ROW
+             CASE t.tgtype & 1 WHEN 1 THEN 'ROW' ELSE 'STATEMENT' END
              AS character_data) AS action_orientation,
            CAST(
-             CASE WHEN t.tgtype & 2 = 2 THEN 'BEFORE' ELSE 'AFTER' END
-             AS character_data) AS condition_timing,
-           CAST(null AS sql_identifier) AS condition_reference_old_table,
-           CAST(null AS sql_identifier) AS condition_reference_new_table,
-           CAST(null AS sql_identifier) AS condition_reference_old_row,
-           CAST(null AS sql_identifier) AS condition_reference_new_row,
+             -- hard-wired refs to TRIGGER_TYPE_BEFORE, TRIGGER_TYPE_INSTEAD
+             CASE t.tgtype & 66 WHEN 2 THEN 'BEFORE' WHEN 64 THEN 'INSTEAD OF' ELSE 'AFTER' END
+             AS character_data) AS action_timing,
+           CAST(null AS sql_identifier) AS action_reference_old_table,
+           CAST(null AS sql_identifier) AS action_reference_new_table,
+           CAST(null AS sql_identifier) AS action_reference_old_row,
+           CAST(null AS sql_identifier) AS action_reference_new_row,
            CAST(null AS time_stamp) AS created
 
     FROM pg_namespace n, pg_class c, pg_trigger t,
+         -- hard-wired refs to TRIGGER_TYPE_INSERT, TRIGGER_TYPE_DELETE,
+         -- TRIGGER_TYPE_UPDATE; we intentionally omit TRIGGER_TYPE_TRUNCATE
          (VALUES (4, 'INSERT'),
                  (8, 'DELETE'),
                  (16, 'UPDATE')) AS em (num, text)
@@ -2041,7 +2099,7 @@ CREATE VIEW triggers AS
     WHERE n.oid = c.relnamespace
           AND c.oid = t.tgrelid
           AND t.tgtype & em.num <> 0
-          AND NOT t.tgisconstraint
+          AND NOT t.tgisinternal
           AND (NOT pg_is_other_temp_schema(n.oid))
           AND (pg_has_role(c.relowner, 'USAGE')
                -- SELECT privilege omitted, per SQL standard
@@ -2052,38 +2110,129 @@ GRANT SELECT ON triggers TO PUBLIC;
 
 
 /*
- * 5.70
+ * 5.73
  * UDT_PRIVILEGES view
  */
 
--- feature not supported
+CREATE VIEW udt_privileges AS
+    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 udt_catalog,
+           CAST(n.nspname AS sql_identifier) AS udt_schema,
+           CAST(t.typname AS sql_identifier) AS udt_name,
+           CAST('TYPE USAGE' AS character_data) AS privilege_type, -- sic
+           CAST(
+             CASE WHEN
+                  -- object owner always has grant options
+                  pg_has_role(grantee.oid, t.typowner, 'USAGE')
+                  OR t.grantable
+                  THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
+
+    FROM (
+            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,
+         (
+           SELECT oid, rolname FROM pg_authid
+           UNION ALL
+           SELECT 0::oid, 'PUBLIC'
+         ) AS grantee (oid, rolname)
+
+    WHERE t.typnamespace = n.oid
+          AND t.typtype = 'c'
+          AND t.grantee = grantee.oid
+          AND t.grantor = u_grantor.oid
+          AND t.prtype IN ('USAGE')
+          AND (pg_has_role(u_grantor.oid, 'USAGE')
+               OR pg_has_role(grantee.oid, 'USAGE')
+               OR grantee.rolname = 'PUBLIC');
+
+GRANT SELECT ON udt_privileges TO PUBLIC;
 
 
 /*
- * 5.71
+ * 5.46
+ * ROLE_UDT_GRANTS view
+ */
+
+CREATE VIEW role_udt_grants AS
+    SELECT grantor,
+           grantee,
+           udt_catalog,
+           udt_schema,
+           udt_name,
+           privilege_type,
+           is_grantable
+    FROM udt_privileges
+    WHERE grantor IN (SELECT role_name FROM enabled_roles)
+          OR grantee IN (SELECT role_name FROM enabled_roles);
+
+GRANT SELECT ON role_udt_grants TO PUBLIC;
+
+
+/*
+ * 5.74
  * USAGE_PRIVILEGES view
  */
 
 CREATE VIEW usage_privileges AS
 
-    /* domains */
-    -- Domains have no real privileges, so we represent all domains with implicit usage privilege here.
+    /* collations */
+    -- Collations have no real privileges, so we represent all collations with implicit usage privilege here.
     SELECT CAST(u.rolname AS sql_identifier) AS grantor,
            CAST('PUBLIC' AS sql_identifier) AS grantee,
            CAST(current_database() AS sql_identifier) AS object_catalog,
            CAST(n.nspname AS sql_identifier) AS object_schema,
-           CAST(t.typname AS sql_identifier) AS object_name,
-           CAST('DOMAIN' AS character_data) AS object_type,
+           CAST(c.collname AS sql_identifier) AS object_name,
+           CAST('COLLATION' AS character_data) AS object_type,
            CAST('USAGE' AS character_data) AS privilege_type,
            CAST('NO' AS yes_or_no) AS is_grantable
 
     FROM pg_authid u,
          pg_namespace n,
-         pg_type t
+         pg_collation c
+
+    WHERE u.oid = c.collowner
+          AND c.collnamespace = n.oid
+          AND collencoding IN (-1, (SELECT encoding FROM pg_database WHERE datname = current_database()))
+
+    UNION ALL
+
+    /* domains */
+    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(t.typname AS sql_identifier) AS object_name,
+           CAST('DOMAIN' 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, t.typowner, 'USAGE')
+                  OR t.grantable
+                  THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
+
+    FROM (
+            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,
+         (
+           SELECT oid, rolname FROM pg_authid
+           UNION ALL
+           SELECT 0::oid, 'PUBLIC'
+         ) AS grantee (oid, rolname)
 
-    WHERE u.oid = t.typowner
-          AND t.typnamespace = n.oid
+    WHERE t.typnamespace = n.oid
           AND t.typtype = 'd'
+          AND t.grantee = grantee.oid
+          AND t.grantor = u_grantor.oid
+          AND t.prtype IN ('USAGE')
+          AND (pg_has_role(u_grantor.oid, 'USAGE')
+               OR pg_has_role(grantee.oid, 'USAGE')
+               OR grantee.rolname = 'PUBLIC')
 
     UNION ALL
 
@@ -2099,11 +2248,12 @@ CREATE VIEW usage_privileges AS
              CASE WHEN
                   -- object owner always has grant options
                   pg_has_role(grantee.oid, fdw.fdwowner, 'USAGE')
-                  OR aclcontains(fdw.fdwacl,
-                                 makeaclitem(grantee.oid, u_grantor.oid, 'USAGE', true))
+                  OR fdw.grantable
                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
 
-    FROM pg_foreign_data_wrapper fdw,
+    FROM (
+            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,
          (
            SELECT oid, rolname FROM pg_authid
@@ -2111,8 +2261,9 @@ CREATE VIEW usage_privileges AS
            SELECT 0::oid, 'PUBLIC'
          ) AS grantee (oid, rolname)
 
-    WHERE aclcontains(fdw.fdwacl,
-                      makeaclitem(grantee.oid, u_grantor.oid, 'USAGE', false))
+    WHERE u_grantor.oid = fdw.grantor
+          AND grantee.oid = fdw.grantee
+          AND fdw.prtype IN ('USAGE')
           AND (pg_has_role(u_grantor.oid, 'USAGE')
                OR pg_has_role(grantee.oid, 'USAGE')
                OR grantee.rolname = 'PUBLIC')
@@ -2131,11 +2282,47 @@ CREATE VIEW usage_privileges AS
              CASE WHEN
                   -- object owner always has grant options
                   pg_has_role(grantee.oid, srv.srvowner, 'USAGE')
-                  OR aclcontains(srv.srvacl,
-                                 makeaclitem(grantee.oid, u_grantor.oid, 'USAGE', true))
+                  OR srv.grantable
+                  THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
+
+    FROM (
+            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,
+         (
+           SELECT oid, rolname FROM pg_authid
+           UNION ALL
+           SELECT 0::oid, 'PUBLIC'
+         ) AS grantee (oid, rolname)
+
+    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 pg_foreign_server srv,
+    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
@@ -2143,8 +2330,11 @@ CREATE VIEW usage_privileges AS
            SELECT 0::oid, 'PUBLIC'
          ) AS grantee (oid, rolname)
 
-    WHERE aclcontains(srv.srvacl,
-                      makeaclitem(grantee.oid, u_grantor.oid, 'USAGE', false))
+    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');
@@ -2153,15 +2343,75 @@ GRANT SELECT ON usage_privileges TO PUBLIC;
 
 
 /*
- * 5.72
+ * 5.45
+ * ROLE_USAGE_GRANTS view
+ */
+
+CREATE VIEW role_usage_grants AS
+    SELECT grantor,
+           grantee,
+           object_catalog,
+           object_schema,
+           object_name,
+           object_type,
+           privilege_type,
+           is_grantable
+    FROM usage_privileges
+    WHERE grantor IN (SELECT role_name FROM enabled_roles)
+          OR grantee IN (SELECT role_name FROM enabled_roles);
+
+GRANT SELECT ON role_usage_grants TO PUBLIC;
+
+
+/*
+ * 5.75
  * USER_DEFINED_TYPES view
  */
 
--- feature not supported
+CREATE VIEW user_defined_types AS
+    SELECT CAST(current_database() AS sql_identifier) AS user_defined_type_catalog,
+           CAST(n.nspname AS sql_identifier) AS user_defined_type_schema,
+           CAST(c.relname AS sql_identifier) AS user_defined_type_name,
+           CAST('STRUCTURED' AS character_data) AS user_defined_type_category,
+           CAST('YES' AS yes_or_no) AS is_instantiable,
+           CAST(null AS yes_or_no) AS is_final,
+           CAST(null AS character_data) AS ordering_form,
+           CAST(null AS character_data) AS ordering_category,
+           CAST(null AS sql_identifier) AS ordering_routine_catalog,
+           CAST(null AS sql_identifier) AS ordering_routine_schema,
+           CAST(null AS sql_identifier) AS ordering_routine_name,
+           CAST(null AS character_data) AS reference_type,
+           CAST(null AS character_data) AS data_type,
+           CAST(null AS cardinal_number) AS character_maximum_length,
+           CAST(null AS cardinal_number) AS character_octet_length,
+           CAST(null AS sql_identifier) AS character_set_catalog,
+           CAST(null AS sql_identifier) AS character_set_schema,
+           CAST(null AS sql_identifier) AS character_set_name,
+           CAST(null AS sql_identifier) AS collation_catalog,
+           CAST(null AS sql_identifier) AS collation_schema,
+           CAST(null AS sql_identifier) AS collation_name,
+           CAST(null AS cardinal_number) AS numeric_precision,
+           CAST(null AS cardinal_number) AS numeric_precision_radix,
+           CAST(null AS cardinal_number) AS numeric_scale,
+           CAST(null AS cardinal_number) AS datetime_precision,
+           CAST(null AS character_data) AS interval_type,
+           CAST(null AS cardinal_number) AS interval_precision,
+           CAST(null AS sql_identifier) AS source_dtd_identifier,
+           CAST(null AS sql_identifier) AS ref_dtd_identifier
+
+    FROM pg_namespace n, pg_class c, pg_type t
+
+    WHERE n.oid = c.relnamespace
+          AND t.typrelid = c.oid
+          AND c.relkind = 'c'
+          AND (pg_has_role(t.typowner, 'USAGE')
+               OR has_type_privilege(t.oid, 'USAGE'));
+
+GRANT SELECT ON user_defined_types TO PUBLIC;
 
 
 /*
- * 5.73
+ * 5.76
  * VIEW_COLUMN_USAGE
  */
 
@@ -2191,7 +2441,7 @@ CREATE VIEW view_column_usage AS
           AND dt.refclassid = 'pg_catalog.pg_class'::regclass
           AND dt.refobjid = t.oid
           AND t.relnamespace = nt.oid
-          AND t.relkind IN ('r', 'v')
+          AND t.relkind IN ('r', 'v', 'f')
           AND t.oid = a.attrelid
           AND dt.refobjsubid = a.attnum
           AND pg_has_role(t.relowner, 'USAGE');
@@ -2200,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
  */
 
@@ -2233,7 +2491,7 @@ GRANT SELECT ON view_routine_usage TO PUBLIC;
 
 
 /*
- * 5.75
+ * 5.79
  * VIEW_TABLE_USAGE
  */
 
@@ -2261,14 +2519,14 @@ CREATE VIEW view_table_usage AS
           AND dt.refclassid = 'pg_catalog.pg_class'::regclass
           AND dt.refobjid = t.oid
           AND t.relnamespace = nt.oid
-          AND t.relkind IN ('r', 'v')
+          AND t.relkind IN ('r', 'v', 'f')
           AND pg_has_role(t.relowner, 'USAGE');
 
 GRANT SELECT ON view_table_usage TO PUBLIC;
 
 
 /*
- * 5.76
+ * 5.80
  * VIEWS view
  */
 
@@ -2283,22 +2541,43 @@ 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,
 
-           CAST('NO' AS yes_or_no) AS is_trigger_updatable,
-           CAST('NO' AS yes_or_no) AS is_trigger_deletable,
-           CAST('NO' AS yes_or_no) AS is_trigger_insertable_into
+           CAST(
+             -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_UPDATE
+             CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 81 = 81)
+                  THEN 'YES' ELSE 'NO' END
+           AS yes_or_no) AS is_trigger_updatable,
+
+           CAST(
+             -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_DELETE
+             CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 73 = 73)
+                  THEN 'YES' ELSE 'NO' END
+           AS yes_or_no) AS is_trigger_deletable,
+
+           CAST(
+             -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_INSERT
+             CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 69 = 69)
+                  THEN 'YES' ELSE 'NO' END
+           AS yes_or_no) AS is_trigger_insertable_into
 
     FROM pg_namespace nc, pg_class c
 
@@ -2315,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
  */
 
@@ -2343,7 +2622,7 @@ GRANT SELECT ON data_type_privileges TO PUBLIC;
 
 
 /*
- * 5.29
+ * 5.30
  * ELEMENT_TYPES view
  */
 
@@ -2362,16 +2641,16 @@ CREATE VIEW element_types AS
            CAST(null AS sql_identifier) AS character_set_catalog,
            CAST(null AS sql_identifier) AS character_set_schema,
            CAST(null AS sql_identifier) AS character_set_name,
-           CAST(null AS sql_identifier) AS collation_catalog,
-           CAST(null AS sql_identifier) AS collation_schema,
-           CAST(null AS sql_identifier) AS collation_name,
+           CAST(CASE WHEN nco.nspname IS NOT NULL THEN current_database() END AS sql_identifier) AS collation_catalog,
+           CAST(nco.nspname AS sql_identifier) AS collation_schema,
+           CAST(co.collname AS sql_identifier) AS collation_name,
            CAST(null AS cardinal_number) AS numeric_precision,
            CAST(null AS cardinal_number) AS numeric_precision_radix,
            CAST(null AS cardinal_number) AS numeric_scale,
            CAST(null AS cardinal_number) AS datetime_precision,
            CAST(null AS character_data) AS interval_type,
-           CAST(null AS character_data) AS interval_precision,
-           
+           CAST(null AS cardinal_number) AS interval_precision,
+
            CAST(null AS character_data) AS domain_default, -- XXX maybe a bug in the standard
 
            CAST(current_database() AS sql_identifier) AS udt_catalog,
@@ -2387,19 +2666,20 @@ CREATE VIEW element_types AS
 
     FROM pg_namespace n, pg_type at, pg_namespace nbt, pg_type bt,
          (
-           /* columns */
+           /* columns, attributes */
            SELECT c.relnamespace, CAST(c.relname AS sql_identifier),
-                  'TABLE'::text, a.attnum, a.atttypid
+                  CASE WHEN c.relkind = 'c' THEN 'USER-DEFINED TYPE'::text ELSE 'TABLE'::text END,
+                  a.attnum, a.atttypid, a.attcollation
            FROM pg_class c, pg_attribute a
            WHERE c.oid = a.attrelid
-                 AND c.relkind IN ('r', 'v')
+                 AND c.relkind IN ('r', 'v', 'f', 'c')
                  AND attnum > 0 AND NOT attisdropped
 
            UNION ALL
 
            /* domains */
            SELECT t.typnamespace, CAST(t.typname AS sql_identifier),
-                  'DOMAIN'::text, 1, t.typbasetype
+                  'DOMAIN'::text, 1, t.typbasetype, t.typcollation
            FROM pg_type t
            WHERE t.typtype = 'd'
 
@@ -2407,7 +2687,7 @@ CREATE VIEW element_types AS
 
            /* parameters */
            SELECT pronamespace, CAST(proname || '_' || CAST(oid AS text) AS sql_identifier),
-                  'ROUTINE'::text, (ss.x).n, (ss.x).x
+                  'ROUTINE'::text, (ss.x).n, (ss.x).x, 0
            FROM (SELECT p.pronamespace, p.proname, p.oid,
                         _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
                  FROM pg_proc p) AS ss
@@ -2416,10 +2696,12 @@ CREATE VIEW element_types AS
 
            /* result types */
            SELECT p.pronamespace, CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier),
-                  'ROUTINE'::text, 0, p.prorettype
+                  'ROUTINE'::text, 0, p.prorettype, 0
            FROM pg_proc p
 
-         ) AS x (objschema, objname, objtype, objdtdid, objtypeid)
+         ) AS x (objschema, objname, objtype, objdtdid, objtypeid, objcollation)
+         LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid))
+           ON x.objcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default')
 
     WHERE n.oid = x.objschema
           AND at.oid = x.objtypeid
@@ -2435,6 +2717,40 @@ 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
+    SELECT n.nspname,
+           c.relname,
+           a.attname,
+           a.attfdwoptions
+    FROM pg_foreign_table t, pg_authid u, pg_namespace n, pg_class c,
+         pg_attribute a
+    WHERE u.oid = c.relowner
+          AND (pg_has_role(c.relowner, 'USAGE')
+               OR has_column_privilege(c.oid, a.attnum, 'SELECT, INSERT, UPDATE, REFERENCES'))
+          AND n.oid = c.relnamespace
+          AND c.oid = t.ftrelid
+          AND c.relkind = 'f'
+          AND a.attrelid = c.oid
+          AND a.attnum > 0;
+
+/*
+ * 24.2
+ * COLUMN_OPTIONS view
+ */
+CREATE VIEW column_options AS
+    SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
+           c.nspname AS table_schema,
+           c.relname AS table_name,
+           c.attname AS column_name,
+           CAST((pg_options_to_table(c.attfdwoptions)).option_name AS sql_identifier) AS option_name,
+           CAST((pg_options_to_table(c.attfdwoptions)).option_value AS character_data) AS option_value
+    FROM _pg_foreign_table_columns c;
+
+GRANT SELECT ON column_options TO PUBLIC;
+
 
 /* Base view for foreign-data wrappers */
 CREATE VIEW _pg_foreign_data_wrappers AS
@@ -2529,6 +2845,60 @@ CREATE VIEW foreign_servers AS
 GRANT SELECT ON foreign_servers TO PUBLIC;
 
 
+/* Base view for foreign tables */
+CREATE VIEW _pg_foreign_tables AS
+    SELECT
+           CAST(current_database() AS sql_identifier) AS foreign_table_catalog,
+           n.nspname AS foreign_table_schema,
+           c.relname AS foreign_table_name,
+           t.ftoptions AS ftoptions,
+           CAST(current_database() AS sql_identifier) AS foreign_server_catalog,
+           CAST(srvname AS sql_identifier) AS foreign_server_name,
+           CAST(u.rolname AS sql_identifier) AS authorization_identifier
+    FROM pg_foreign_table t, pg_foreign_server s, pg_foreign_data_wrapper w,
+         pg_authid u, pg_namespace n, pg_class c
+    WHERE w.oid = s.srvfdw
+          AND u.oid = c.relowner
+          AND (pg_has_role(c.relowner, 'USAGE')
+               OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
+               OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'))
+          AND n.oid = c.relnamespace
+          AND c.oid = t.ftrelid
+          AND c.relkind = 'f'
+          AND s.oid = t.ftserver;
+
+
+/*
+ * 24.8
+ * FOREIGN_TABLE_OPTIONS view
+ */
+CREATE VIEW foreign_table_options AS
+    SELECT foreign_table_catalog,
+           foreign_table_schema,
+           foreign_table_name,
+           CAST((pg_options_to_table(t.ftoptions)).option_name AS sql_identifier) AS option_name,
+           CAST((pg_options_to_table(t.ftoptions)).option_value AS character_data) AS option_value
+    FROM _pg_foreign_tables t;
+
+GRANT SELECT ON TABLE foreign_table_options TO PUBLIC;
+
+
+/*
+ * 24.9
+ * FOREIGN_TABLES view
+ */
+CREATE VIEW foreign_tables AS
+    SELECT foreign_table_catalog,
+           foreign_table_schema,
+           foreign_table_name,
+           foreign_server_catalog,
+           foreign_server_name
+    FROM _pg_foreign_tables;
+
+GRANT SELECT ON foreign_tables TO PUBLIC;
+
+
+
 /* Base view for user mappings */
 CREATE VIEW _pg_user_mappings AS
     SELECT um.oid,