granicus.if.org Git - postgresql/blob - src/backend/catalog/information_schema.sql

   1 /*
   2  * SQL Information Schema
   3  * as defined in ISO/IEC 9075-11:2011
   4  *
   5  * Copyright (c) 2003-2015, PostgreSQL Global Development Group
   6  *
   7  * src/backend/catalog/information_schema.sql
   8  *
   9  * Note: this file is read in single-user -j mode, which means that the
  10  * command terminator is semicolon-newline-newline; whenever the backend
  11  * sees that, it stops and executes what it's got.  If you write a lot of
  12  * statements without empty lines between, they'll all get quoted to you
  13  * in any error message about one of them, so don't do that.  Also, you
  14  * cannot write a semicolon immediately followed by an empty line in a
  15  * string literal (including a function body!) or a multiline comment.
  16  */
  17
  18 /*
  19  * Note: Generally, the definitions in this file should be ordered
  20  * according to the clause numbers in the SQL standard, which is also the
  21  * alphabetical order.  In some cases it is convenient or necessary to
  22  * define one information schema view by using another one; in that case,
  23  * put the referencing view at the very end and leave a note where it
  24  * should have been put.
  25  */
  26
  27
  28 /*
  29  * 5.1
  30  * INFORMATION_SCHEMA schema
  31  */
  32
  33 CREATE SCHEMA information_schema;
  34 GRANT USAGE ON SCHEMA information_schema TO PUBLIC;
  35 SET search_path TO information_schema;
  36
  37
  38 /*
  39  * A few supporting functions first ...
  40  */
  41
  42 /* Expand any 1-D array into a set with integers 1..N */
  43 CREATE FUNCTION _pg_expandarray(IN anyarray, OUT x anyelement, OUT n int)
  44     RETURNS SETOF RECORD
  45     LANGUAGE sql STRICT IMMUTABLE
  46     AS 'select $1[s], s - pg_catalog.array_lower($1,1) + 1
  47         from pg_catalog.generate_series(pg_catalog.array_lower($1,1),
  48                                         pg_catalog.array_upper($1,1),
  49                                         1) as g(s)';
  50
  51 CREATE FUNCTION _pg_keysequal(smallint[], smallint[]) RETURNS boolean
  52     LANGUAGE sql IMMUTABLE  -- intentionally not STRICT, to allow inlining
  53     AS 'select $1 operator(pg_catalog.<@) $2 and $2 operator(pg_catalog.<@) $1';
  54
  55 /* Given an index's OID and an underlying-table column number, return the
  56  * column's position in the index (NULL if not there) */
  57 CREATE FUNCTION _pg_index_position(oid, smallint) RETURNS int
  58     LANGUAGE sql STRICT STABLE
  59     AS $$
  60 SELECT (ss.a).n FROM
  61   (SELECT information_schema._pg_expandarray(indkey) AS a
  62    FROM pg_catalog.pg_index WHERE indexrelid = $1) ss
  63   WHERE (ss.a).x = $2;
  64 $$;
  65
  66 CREATE FUNCTION _pg_truetypid(pg_attribute, pg_type) RETURNS oid
  67     LANGUAGE sql
  68     IMMUTABLE
  69     RETURNS NULL ON NULL INPUT
  70     AS
  71 $$SELECT CASE WHEN $2.typtype = 'd' THEN $2.typbasetype ELSE $1.atttypid END$$;
  72
  73 CREATE FUNCTION _pg_truetypmod(pg_attribute, pg_type) RETURNS int4
  74     LANGUAGE sql
  75     IMMUTABLE
  76     RETURNS NULL ON NULL INPUT
  77     AS
  78 $$SELECT CASE WHEN $2.typtype = 'd' THEN $2.typtypmod ELSE $1.atttypmod END$$;
  79
  80 -- these functions encapsulate knowledge about the encoding of typmod:
  81
  82 CREATE FUNCTION _pg_char_max_length(typid oid, typmod int4) RETURNS integer
  83     LANGUAGE sql
  84     IMMUTABLE
  85     RETURNS NULL ON NULL INPUT
  86     AS
  87 $$SELECT
  88   CASE WHEN $2 = -1 /* default typmod */
  89        THEN null
  90        WHEN $1 IN (1042, 1043) /* char, varchar */
  91        THEN $2 - 4
  92        WHEN $1 IN (1560, 1562) /* bit, varbit */
  93        THEN $2
  94        ELSE null
  95   END$$;
  96
  97 CREATE FUNCTION _pg_char_octet_length(typid oid, typmod int4) RETURNS integer
  98     LANGUAGE sql
  99     IMMUTABLE
 100     RETURNS NULL ON NULL INPUT
 101     AS
 102 $$SELECT
 103   CASE WHEN $1 IN (25, 1042, 1043) /* text, char, varchar */
 104        THEN CASE WHEN $2 = -1 /* default typmod */
 105                  THEN CAST(2^30 AS integer)
 106                  ELSE information_schema._pg_char_max_length($1, $2) *
 107                       pg_catalog.pg_encoding_max_length((SELECT encoding FROM pg_catalog.pg_database WHERE datname = pg_catalog.current_database()))
 108             END
 109        ELSE null
 110   END$$;
 111
 112 CREATE FUNCTION _pg_numeric_precision(typid oid, typmod int4) RETURNS integer
 113     LANGUAGE sql
 114     IMMUTABLE
 115     RETURNS NULL ON NULL INPUT
 116     AS
 117 $$SELECT
 118   CASE $1
 119          WHEN 21 /*int2*/ THEN 16
 120          WHEN 23 /*int4*/ THEN 32
 121          WHEN 20 /*int8*/ THEN 64
 122          WHEN 1700 /*numeric*/ THEN
 123               CASE WHEN $2 = -1
 124                    THEN null
 125                    ELSE (($2 - 4) >> 16) & 65535
 126                    END
 127          WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/
 128          WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/
 129          ELSE null
 130   END$$;
 131
 132 CREATE FUNCTION _pg_numeric_precision_radix(typid oid, typmod int4) RETURNS integer
 133     LANGUAGE sql
 134     IMMUTABLE
 135     RETURNS NULL ON NULL INPUT
 136     AS
 137 $$SELECT
 138   CASE WHEN $1 IN (21, 23, 20, 700, 701) THEN 2
 139        WHEN $1 IN (1700) THEN 10
 140        ELSE null
 141   END$$;
 142
 143 CREATE FUNCTION _pg_numeric_scale(typid oid, typmod int4) RETURNS integer
 144     LANGUAGE sql
 145     IMMUTABLE
 146     RETURNS NULL ON NULL INPUT
 147     AS
 148 $$SELECT
 149   CASE WHEN $1 IN (21, 23, 20) THEN 0
 150        WHEN $1 IN (1700) THEN
 151             CASE WHEN $2 = -1
 152                  THEN null
 153                  ELSE ($2 - 4) & 65535
 154                  END
 155        ELSE null
 156   END$$;
 157
 158 CREATE FUNCTION _pg_datetime_precision(typid oid, typmod int4) RETURNS integer
 159     LANGUAGE sql
 160     IMMUTABLE
 161     RETURNS NULL ON NULL INPUT
 162     AS
 163 $$SELECT
 164   CASE WHEN $1 IN (1082) /* date */
 165            THEN 0
 166        WHEN $1 IN (1083, 1114, 1184, 1266) /* time, timestamp, same + tz */
 167            THEN CASE WHEN $2 < 0 THEN 6 ELSE $2 END
 168        WHEN $1 IN (1186) /* interval */
 169            THEN CASE WHEN $2 < 0 OR $2 & 65535 = 65535 THEN 6 ELSE $2 & 65535 END
 170        ELSE null
 171   END$$;
 172
 173 CREATE FUNCTION _pg_interval_type(typid oid, mod int4) RETURNS text
 174     LANGUAGE sql
 175     IMMUTABLE
 176     RETURNS NULL ON NULL INPUT
 177     AS
 178 $$SELECT
 179   CASE WHEN $1 IN (1186) /* interval */
 180            THEN upper(substring(format_type($1, $2) from 'interval[()0-9]* #"%#"' for '#'))
 181        ELSE null
 182   END$$;
 183
 184
 185 -- 5.2 INFORMATION_SCHEMA_CATALOG_NAME view appears later.
 186
 187
 188 /*
 189  * 5.3
 190  * CARDINAL_NUMBER domain
 191  */
 192
 193 CREATE DOMAIN cardinal_number AS integer
 194     CONSTRAINT cardinal_number_domain_check CHECK (value >= 0);
 195
 196
 197 /*
 198  * 5.4
 199  * CHARACTER_DATA domain
 200  */
 201
 202 CREATE DOMAIN character_data AS character varying;
 203
 204
 205 /*
 206  * 5.5
 207  * SQL_IDENTIFIER domain
 208  */
 209
 210 CREATE DOMAIN sql_identifier AS character varying;
 211
 212
 213 /*
 214  * 5.2
 215  * INFORMATION_SCHEMA_CATALOG_NAME view
 216  */
 217
 218 CREATE VIEW information_schema_catalog_name AS
 219     SELECT CAST(current_database() AS sql_identifier) AS catalog_name;
 220
 221 GRANT SELECT ON information_schema_catalog_name TO PUBLIC;
 222
 223
 224 /*
 225  * 5.6
 226  * TIME_STAMP domain
 227  */
 228
 229 CREATE DOMAIN time_stamp AS timestamp(2) with time zone
 230     DEFAULT current_timestamp(2);
 231
 232 /*
 233  * 5.7
 234  * YES_OR_NO domain
 235  */
 236
 237 CREATE DOMAIN yes_or_no AS character varying(3)
 238     CONSTRAINT yes_or_no_check CHECK (value IN ('YES', 'NO'));
 239
 240
 241 -- 5.8 ADMINISTRABLE_ROLE_AUTHORIZATIONS view appears later.
 242
 243
 244 /*
 245  * 5.9
 246  * APPLICABLE_ROLES view
 247  */
 248
 249 CREATE VIEW applicable_roles AS
 250     SELECT CAST(a.rolname AS sql_identifier) AS grantee,
 251            CAST(b.rolname AS sql_identifier) AS role_name,
 252            CAST(CASE WHEN m.admin_option THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
 253     FROM pg_auth_members m
 254          JOIN pg_authid a ON (m.member = a.oid)
 255          JOIN pg_authid b ON (m.roleid = b.oid)
 256     WHERE pg_has_role(a.oid, 'USAGE');
 257
 258 GRANT SELECT ON applicable_roles TO PUBLIC;
 259
 260
 261 /*
 262  * 5.8
 263  * ADMINISTRABLE_ROLE_AUTHORIZATIONS view
 264  */
 265
 266 CREATE VIEW administrable_role_authorizations AS
 267     SELECT *
 268     FROM applicable_roles
 269     WHERE is_grantable = 'YES';
 270
 271 GRANT SELECT ON administrable_role_authorizations TO PUBLIC;
 272
 273
 274 /*
 275  * 5.10
 276  * ASSERTIONS view
 277  */
 278
 279 -- feature not supported
 280
 281
 282 /*
 283  * 5.11
 284  * ATTRIBUTES view
 285  */
 286
 287 CREATE VIEW attributes AS
 288     SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
 289            CAST(nc.nspname AS sql_identifier) AS udt_schema,
 290            CAST(c.relname AS sql_identifier) AS udt_name,
 291            CAST(a.attname AS sql_identifier) AS attribute_name,
 292            CAST(a.attnum AS cardinal_number) AS ordinal_position,
 293            CAST(pg_get_expr(ad.adbin, ad.adrelid) AS character_data) AS attribute_default,
 294            CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
 295              AS yes_or_no)
 296              AS is_nullable, -- This column was apparently removed between SQL:2003 and SQL:2008.
 297
 298            CAST(
 299              CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
 300                   WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, null)
 301                   ELSE 'USER-DEFINED' END
 302              AS character_data)
 303              AS data_type,
 304
 305            CAST(
 306              _pg_char_max_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
 307              AS cardinal_number)
 308              AS character_maximum_length,
 309
 310            CAST(
 311              _pg_char_octet_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
 312              AS cardinal_number)
 313              AS character_octet_length,
 314
 315            CAST(null AS sql_identifier) AS character_set_catalog,
 316            CAST(null AS sql_identifier) AS character_set_schema,
 317            CAST(null AS sql_identifier) AS character_set_name,
 318
 319            CAST(CASE WHEN nco.nspname IS NOT NULL THEN current_database() END AS sql_identifier) AS collation_catalog,
 320            CAST(nco.nspname AS sql_identifier) AS collation_schema,
 321            CAST(co.collname AS sql_identifier) AS collation_name,
 322
 323            CAST(
 324              _pg_numeric_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
 325              AS cardinal_number)
 326              AS numeric_precision,
 327
 328            CAST(
 329              _pg_numeric_precision_radix(_pg_truetypid(a, t), _pg_truetypmod(a, t))
 330              AS cardinal_number)
 331              AS numeric_precision_radix,
 332
 333            CAST(
 334              _pg_numeric_scale(_pg_truetypid(a, t), _pg_truetypmod(a, t))
 335              AS cardinal_number)
 336              AS numeric_scale,
 337
 338            CAST(
 339              _pg_datetime_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
 340              AS cardinal_number)
 341              AS datetime_precision,
 342
 343            CAST(
 344              _pg_interval_type(_pg_truetypid(a, t), _pg_truetypmod(a, t))
 345              AS character_data)
 346              AS interval_type,
 347            CAST(null AS cardinal_number) AS interval_precision,
 348
 349            CAST(current_database() AS sql_identifier) AS attribute_udt_catalog,
 350            CAST(nt.nspname AS sql_identifier) AS attribute_udt_schema,
 351            CAST(t.typname AS sql_identifier) AS attribute_udt_name,
 352
 353            CAST(null AS sql_identifier) AS scope_catalog,
 354            CAST(null AS sql_identifier) AS scope_schema,
 355            CAST(null AS sql_identifier) AS scope_name,
 356
 357            CAST(null AS cardinal_number) AS maximum_cardinality,
 358            CAST(a.attnum AS sql_identifier) AS dtd_identifier,
 359            CAST('NO' AS yes_or_no) AS is_derived_reference_attribute
 360
 361     FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum)
 362          JOIN (pg_class c JOIN pg_namespace nc ON (c.relnamespace = nc.oid)) ON a.attrelid = c.oid
 363          JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON a.atttypid = t.oid
 364          LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid))
 365            ON a.attcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default')
 366
 367     WHERE a.attnum > 0 AND NOT a.attisdropped
 368           AND c.relkind in ('c')
 369           AND (pg_has_role(c.relowner, 'USAGE')
 370                OR has_type_privilege(c.reltype, 'USAGE'));
 371
 372 GRANT SELECT ON attributes TO PUBLIC;
 373
 374
 375 /*
 376  * 5.12
 377  * CHARACTER_SETS view
 378  */
 379
 380 CREATE VIEW character_sets AS
 381     SELECT CAST(null AS sql_identifier) AS character_set_catalog,
 382            CAST(null AS sql_identifier) AS character_set_schema,
 383            CAST(getdatabaseencoding() AS sql_identifier) AS character_set_name,
 384            CAST(CASE WHEN getdatabaseencoding() = 'UTF8' THEN 'UCS' ELSE getdatabaseencoding() END AS sql_identifier) AS character_repertoire,
 385            CAST(getdatabaseencoding() AS sql_identifier) AS form_of_use,
 386            CAST(current_database() AS sql_identifier) AS default_collate_catalog,
 387            CAST(nc.nspname AS sql_identifier) AS default_collate_schema,
 388            CAST(c.collname AS sql_identifier) AS default_collate_name
 389     FROM pg_database d
 390          LEFT JOIN (pg_collation c JOIN pg_namespace nc ON (c.collnamespace = nc.oid))
 391              ON (datcollate = collcollate AND datctype = collctype)
 392     WHERE d.datname = current_database()
 393     ORDER BY char_length(c.collname) DESC, c.collname ASC -- prefer full/canonical name
 394     LIMIT 1;
 395
 396 GRANT SELECT ON character_sets TO PUBLIC;
 397
 398
 399 /*
 400  * 5.13
 401  * CHECK_CONSTRAINT_ROUTINE_USAGE view
 402  */
 403
 404 CREATE VIEW check_constraint_routine_usage AS
 405     SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
 406            CAST(nc.nspname AS sql_identifier) AS constraint_schema,
 407            CAST(c.conname AS sql_identifier) AS constraint_name,
 408            CAST(current_database() AS sql_identifier) AS specific_catalog,
 409            CAST(np.nspname AS sql_identifier) AS specific_schema,
 410            CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name
 411     FROM pg_namespace nc, pg_constraint c, pg_depend d, pg_proc p, pg_namespace np
 412     WHERE nc.oid = c.connamespace
 413       AND c.contype = 'c'
 414       AND c.oid = d.objid
 415       AND d.classid = 'pg_catalog.pg_constraint'::regclass
 416       AND d.refobjid = p.oid
 417       AND d.refclassid = 'pg_catalog.pg_proc'::regclass
 418       AND p.pronamespace = np.oid
 419       AND pg_has_role(p.proowner, 'USAGE');
 420
 421 GRANT SELECT ON check_constraint_routine_usage TO PUBLIC;
 422
 423
 424 /*
 425  * 5.14
 426  * CHECK_CONSTRAINTS view
 427  */
 428
 429 CREATE VIEW check_constraints AS
 430     SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
 431            CAST(rs.nspname AS sql_identifier) AS constraint_schema,
 432            CAST(con.conname AS sql_identifier) AS constraint_name,
 433            CAST(substring(pg_get_constraintdef(con.oid) from 7) AS character_data)
 434              AS check_clause
 435     FROM pg_constraint con
 436            LEFT OUTER JOIN pg_namespace rs ON (rs.oid = con.connamespace)
 437            LEFT OUTER JOIN pg_class c ON (c.oid = con.conrelid)
 438            LEFT OUTER JOIN pg_type t ON (t.oid = con.contypid)
 439     WHERE pg_has_role(coalesce(c.relowner, t.typowner), 'USAGE')
 440       AND con.contype = 'c'
 441
 442     UNION
 443     -- not-null constraints
 444
 445     SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
 446            CAST(n.nspname AS sql_identifier) AS constraint_schema,
 447            CAST(CAST(n.oid AS text) || '_' || CAST(r.oid AS text) || '_' || CAST(a.attnum AS text) || '_not_null' AS sql_identifier) AS constraint_name, -- XXX
 448            CAST(a.attname || ' IS NOT NULL' AS character_data)
 449              AS check_clause
 450     FROM pg_namespace n, pg_class r, pg_attribute a
 451     WHERE n.oid = r.relnamespace
 452       AND r.oid = a.attrelid
 453       AND a.attnum > 0
 454       AND NOT a.attisdropped
 455       AND a.attnotnull
 456       AND r.relkind = 'r'
 457       AND pg_has_role(r.relowner, 'USAGE');
 458
 459 GRANT SELECT ON check_constraints TO PUBLIC;
 460
 461
 462 /*
 463  * 5.15
 464  * COLLATIONS view
 465  */
 466
 467 CREATE VIEW collations AS
 468     SELECT CAST(current_database() AS sql_identifier) AS collation_catalog,
 469            CAST(nc.nspname AS sql_identifier) AS collation_schema,
 470            CAST(c.collname AS sql_identifier) AS collation_name,
 471            CAST('NO PAD' AS character_data) AS pad_attribute
 472     FROM pg_collation c, pg_namespace nc
 473     WHERE c.collnamespace = nc.oid
 474           AND collencoding IN (-1, (SELECT encoding FROM pg_database WHERE datname = current_database()));
 475
 476 GRANT SELECT ON collations TO PUBLIC;
 477
 478
 479 /*
 480  * 5.16
 481  * COLLATION_CHARACTER_SET_APPLICABILITY view
 482  */
 483
 484 CREATE VIEW collation_character_set_applicability AS
 485     SELECT CAST(current_database() AS sql_identifier) AS collation_catalog,
 486            CAST(nc.nspname AS sql_identifier) AS collation_schema,
 487            CAST(c.collname AS sql_identifier) AS collation_name,
 488            CAST(null AS sql_identifier) AS character_set_catalog,
 489            CAST(null AS sql_identifier) AS character_set_schema,
 490            CAST(getdatabaseencoding() AS sql_identifier) AS character_set_name
 491     FROM pg_collation c, pg_namespace nc
 492     WHERE c.collnamespace = nc.oid
 493           AND collencoding IN (-1, (SELECT encoding FROM pg_database WHERE datname = current_database()));
 494
 495 GRANT SELECT ON collation_character_set_applicability TO PUBLIC;
 496
 497
 498 /*
 499  * 5.17
 500  * COLUMN_COLUMN_USAGE view
 501  */
 502
 503 -- feature not supported
 504
 505
 506 /*
 507  * 5.18
 508  * COLUMN_DOMAIN_USAGE view
 509  */
 510
 511 CREATE VIEW column_domain_usage AS
 512     SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
 513            CAST(nt.nspname AS sql_identifier) AS domain_schema,
 514            CAST(t.typname AS sql_identifier) AS domain_name,
 515            CAST(current_database() AS sql_identifier) AS table_catalog,
 516            CAST(nc.nspname AS sql_identifier) AS table_schema,
 517            CAST(c.relname AS sql_identifier) AS table_name,
 518            CAST(a.attname AS sql_identifier) AS column_name
 519
 520     FROM pg_type t, pg_namespace nt, pg_class c, pg_namespace nc,
 521          pg_attribute a
 522
 523     WHERE t.typnamespace = nt.oid
 524           AND c.relnamespace = nc.oid
 525           AND a.attrelid = c.oid
 526           AND a.atttypid = t.oid
 527           AND t.typtype = 'd'
 528           AND c.relkind IN ('r', 'v', 'f')
 529           AND a.attnum > 0
 530           AND NOT a.attisdropped
 531           AND pg_has_role(t.typowner, 'USAGE');
 532
 533 GRANT SELECT ON column_domain_usage TO PUBLIC;
 534
 535
 536 /*
 537  * 5.19
 538  * COLUMN_PRIVILEGES
 539  */
 540
 541 CREATE VIEW column_privileges AS
 542     SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
 543            CAST(grantee.rolname AS sql_identifier) AS grantee,
 544            CAST(current_database() AS sql_identifier) AS table_catalog,
 545            CAST(nc.nspname AS sql_identifier) AS table_schema,
 546            CAST(x.relname AS sql_identifier) AS table_name,
 547            CAST(x.attname AS sql_identifier) AS column_name,
 548            CAST(x.prtype AS character_data) AS privilege_type,
 549            CAST(
 550              CASE WHEN
 551                   -- object owner always has grant options
 552                   pg_has_role(x.grantee, x.relowner, 'USAGE')
 553                   OR x.grantable
 554                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
 555
 556     FROM (
 557            SELECT pr_c.grantor,
 558                   pr_c.grantee,
 559                   attname,
 560                   relname,
 561                   relnamespace,
 562                   pr_c.prtype,
 563                   pr_c.grantable,
 564                   pr_c.relowner
 565            FROM (SELECT oid, relname, relnamespace, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).*
 566                  FROM pg_class
 567                  WHERE relkind IN ('r', 'v', 'f')
 568                 ) pr_c (oid, relname, relnamespace, relowner, grantor, grantee, prtype, grantable),
 569                 pg_attribute a
 570            WHERE a.attrelid = pr_c.oid
 571                  AND a.attnum > 0
 572                  AND NOT a.attisdropped
 573            UNION
 574            SELECT pr_a.grantor,
 575                   pr_a.grantee,
 576                   attname,
 577                   relname,
 578                   relnamespace,
 579                   pr_a.prtype,
 580                   pr_a.grantable,
 581                   c.relowner
 582            FROM (SELECT attrelid, attname, (aclexplode(coalesce(attacl, acldefault('c', relowner)))).*
 583                  FROM pg_attribute a JOIN pg_class cc ON (a.attrelid = cc.oid)
 584                  WHERE attnum > 0
 585                        AND NOT attisdropped
 586                 ) pr_a (attrelid, attname, grantor, grantee, prtype, grantable),
 587                 pg_class c
 588            WHERE pr_a.attrelid = c.oid
 589                  AND relkind IN ('r', 'v', 'f')
 590          ) x,
 591          pg_namespace nc,
 592          pg_authid u_grantor,
 593          (
 594            SELECT oid, rolname FROM pg_authid
 595            UNION ALL
 596            SELECT 0::oid, 'PUBLIC'
 597          ) AS grantee (oid, rolname)
 598
 599     WHERE x.relnamespace = nc.oid
 600           AND x.grantee = grantee.oid
 601           AND x.grantor = u_grantor.oid
 602           AND x.prtype IN ('INSERT', 'SELECT', 'UPDATE', 'REFERENCES')
 603           AND (pg_has_role(u_grantor.oid, 'USAGE')
 604                OR pg_has_role(grantee.oid, 'USAGE')
 605                OR grantee.rolname = 'PUBLIC');
 606
 607 GRANT SELECT ON column_privileges TO PUBLIC;
 608
 609
 610 /*
 611  * 5.20
 612  * COLUMN_UDT_USAGE view
 613  */
 614
 615 CREATE VIEW column_udt_usage AS
 616     SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
 617            CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,
 618            CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,
 619            CAST(current_database() AS sql_identifier) AS table_catalog,
 620            CAST(nc.nspname AS sql_identifier) AS table_schema,
 621            CAST(c.relname AS sql_identifier) AS table_name,
 622            CAST(a.attname AS sql_identifier) AS column_name
 623
 624     FROM pg_attribute a, pg_class c, pg_namespace nc,
 625          (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
 626            LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
 627            ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
 628
 629     WHERE a.attrelid = c.oid
 630           AND a.atttypid = t.oid
 631           AND nc.oid = c.relnamespace
 632           AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v', 'f')
 633           AND pg_has_role(coalesce(bt.typowner, t.typowner), 'USAGE');
 634
 635 GRANT SELECT ON column_udt_usage TO PUBLIC;
 636
 637
 638 /*
 639  * 5.21
 640  * COLUMNS view
 641  */
 642
 643 CREATE VIEW columns AS
 644     SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
 645            CAST(nc.nspname AS sql_identifier) AS table_schema,
 646            CAST(c.relname AS sql_identifier) AS table_name,
 647            CAST(a.attname AS sql_identifier) AS column_name,
 648            CAST(a.attnum AS cardinal_number) AS ordinal_position,
 649            CAST(pg_get_expr(ad.adbin, ad.adrelid) AS character_data) AS column_default,
 650            CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
 651              AS yes_or_no)
 652              AS is_nullable,
 653
 654            CAST(
 655              CASE WHEN t.typtype = 'd' THEN
 656                CASE WHEN bt.typelem <> 0 AND bt.typlen = -1 THEN 'ARRAY'
 657                     WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
 658                     ELSE 'USER-DEFINED' END
 659              ELSE
 660                CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
 661                     WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, null)
 662                     ELSE 'USER-DEFINED' END
 663              END
 664              AS character_data)
 665              AS data_type,
 666
 667            CAST(
 668              _pg_char_max_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
 669              AS cardinal_number)
 670              AS character_maximum_length,
 671
 672            CAST(
 673              _pg_char_octet_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
 674              AS cardinal_number)
 675              AS character_octet_length,
 676
 677            CAST(
 678              _pg_numeric_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
 679              AS cardinal_number)
 680              AS numeric_precision,
 681
 682            CAST(
 683              _pg_numeric_precision_radix(_pg_truetypid(a, t), _pg_truetypmod(a, t))
 684              AS cardinal_number)
 685              AS numeric_precision_radix,
 686
 687            CAST(
 688              _pg_numeric_scale(_pg_truetypid(a, t), _pg_truetypmod(a, t))
 689              AS cardinal_number)
 690              AS numeric_scale,
 691
 692            CAST(
 693              _pg_datetime_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
 694              AS cardinal_number)
 695              AS datetime_precision,
 696
 697            CAST(
 698              _pg_interval_type(_pg_truetypid(a, t), _pg_truetypmod(a, t))
 699              AS character_data)
 700              AS interval_type,
 701            CAST(null AS cardinal_number) AS interval_precision,
 702
 703            CAST(null AS sql_identifier) AS character_set_catalog,
 704            CAST(null AS sql_identifier) AS character_set_schema,
 705            CAST(null AS sql_identifier) AS character_set_name,
 706
 707            CAST(CASE WHEN nco.nspname IS NOT NULL THEN current_database() END AS sql_identifier) AS collation_catalog,
 708            CAST(nco.nspname AS sql_identifier) AS collation_schema,
 709            CAST(co.collname AS sql_identifier) AS collation_name,
 710
 711            CAST(CASE WHEN t.typtype = 'd' THEN current_database() ELSE null END
 712              AS sql_identifier) AS domain_catalog,
 713            CAST(CASE WHEN t.typtype = 'd' THEN nt.nspname ELSE null END
 714              AS sql_identifier) AS domain_schema,
 715            CAST(CASE WHEN t.typtype = 'd' THEN t.typname ELSE null END
 716              AS sql_identifier) AS domain_name,
 717
 718            CAST(current_database() AS sql_identifier) AS udt_catalog,
 719            CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,
 720            CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,
 721
 722            CAST(null AS sql_identifier) AS scope_catalog,
 723            CAST(null AS sql_identifier) AS scope_schema,
 724            CAST(null AS sql_identifier) AS scope_name,
 725
 726            CAST(null AS cardinal_number) AS maximum_cardinality,
 727            CAST(a.attnum AS sql_identifier) AS dtd_identifier,
 728            CAST('NO' AS yes_or_no) AS is_self_referencing,
 729
 730            CAST('NO' AS yes_or_no) AS is_identity,
 731            CAST(null AS character_data) AS identity_generation,
 732            CAST(null AS character_data) AS identity_start,
 733            CAST(null AS character_data) AS identity_increment,
 734            CAST(null AS character_data) AS identity_maximum,
 735            CAST(null AS character_data) AS identity_minimum,
 736            CAST(null AS yes_or_no) AS identity_cycle,
 737
 738            CAST('NEVER' AS character_data) AS is_generated,
 739            CAST(null AS character_data) AS generation_expression,
 740
 741            CAST(CASE WHEN c.relkind = 'r' OR
 742                           (c.relkind IN ('v', 'f') AND
 743                            pg_column_is_updatable(c.oid, a.attnum, false))
 744                 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_updatable
 745
 746     FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum)
 747          JOIN (pg_class c JOIN pg_namespace nc ON (c.relnamespace = nc.oid)) ON a.attrelid = c.oid
 748          JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON a.atttypid = t.oid
 749          LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
 750            ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
 751          LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid))
 752            ON a.attcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default')
 753
 754     WHERE (NOT pg_is_other_temp_schema(nc.oid))
 755
 756           AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v', 'f')
 757
 758           AND (pg_has_role(c.relowner, 'USAGE')
 759                OR has_column_privilege(c.oid, a.attnum,
 760                                        'SELECT, INSERT, UPDATE, REFERENCES'));
 761
 762 GRANT SELECT ON columns TO PUBLIC;
 763
 764
 765 /*
 766  * 5.22
 767  * CONSTRAINT_COLUMN_USAGE view
 768  */
 769
 770 CREATE VIEW constraint_column_usage AS
 771     SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
 772            CAST(tblschema AS sql_identifier) AS table_schema,
 773            CAST(tblname AS sql_identifier) AS table_name,
 774            CAST(colname AS sql_identifier) AS column_name,
 775            CAST(current_database() AS sql_identifier) AS constraint_catalog,
 776            CAST(cstrschema AS sql_identifier) AS constraint_schema,
 777            CAST(cstrname AS sql_identifier) AS constraint_name
 778
 779     FROM (
 780         /* check constraints */
 781         SELECT DISTINCT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
 782           FROM pg_namespace nr, pg_class r, pg_attribute a, pg_depend d, pg_namespace nc, pg_constraint c
 783           WHERE nr.oid = r.relnamespace
 784             AND r.oid = a.attrelid
 785             AND d.refclassid = 'pg_catalog.pg_class'::regclass
 786             AND d.refobjid = r.oid
 787             AND d.refobjsubid = a.attnum
 788             AND d.classid = 'pg_catalog.pg_constraint'::regclass
 789             AND d.objid = c.oid
 790             AND c.connamespace = nc.oid
 791             AND c.contype = 'c'
 792             AND r.relkind = 'r'
 793             AND NOT a.attisdropped
 794
 795         UNION ALL
 796
 797         /* unique/primary key/foreign key constraints */
 798         SELECT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
 799           FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc,
 800                pg_constraint c
 801           WHERE nr.oid = r.relnamespace
 802             AND r.oid = a.attrelid
 803             AND nc.oid = c.connamespace
 804             AND (CASE WHEN c.contype = 'f' THEN r.oid = c.confrelid AND a.attnum = ANY (c.confkey)
 805                       ELSE r.oid = c.conrelid AND a.attnum = ANY (c.conkey) END)
 806             AND NOT a.attisdropped
 807             AND c.contype IN ('p', 'u', 'f')
 808             AND r.relkind = 'r'
 809
 810       ) AS x (tblschema, tblname, tblowner, colname, cstrschema, cstrname)
 811
 812     WHERE pg_has_role(x.tblowner, 'USAGE');
 813
 814 GRANT SELECT ON constraint_column_usage TO PUBLIC;
 815
 816
 817 /*
 818  * 5.23
 819  * CONSTRAINT_PERIOD_USAGE view
 820  */
 821
 822 -- feature not supported
 823
 824
 825 /*
 826  * 5.24
 827  * CONSTRAINT_TABLE_USAGE view
 828  */
 829
 830 CREATE VIEW constraint_table_usage AS
 831     SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
 832            CAST(nr.nspname AS sql_identifier) AS table_schema,
 833            CAST(r.relname AS sql_identifier) AS table_name,
 834            CAST(current_database() AS sql_identifier) AS constraint_catalog,
 835            CAST(nc.nspname AS sql_identifier) AS constraint_schema,
 836            CAST(c.conname AS sql_identifier) AS constraint_name
 837
 838     FROM pg_constraint c, pg_namespace nc,
 839          pg_class r, pg_namespace nr
 840
 841     WHERE c.connamespace = nc.oid AND r.relnamespace = nr.oid
 842           AND ( (c.contype = 'f' AND c.confrelid = r.oid)
 843              OR (c.contype IN ('p', 'u') AND c.conrelid = r.oid) )
 844           AND r.relkind = 'r'
 845           AND pg_has_role(r.relowner, 'USAGE');
 846
 847 GRANT SELECT ON constraint_table_usage TO PUBLIC;
 848
 849
 850 -- 5.25 DATA_TYPE_PRIVILEGES view appears later.
 851
 852
 853 /*
 854  * 5.26
 855  * DIRECT_SUPERTABLES view
 856  */
 857
 858 -- feature not supported
 859
 860
 861 /*
 862  * 5.27
 863  * DIRECT_SUPERTYPES view
 864  */
 865
 866 -- feature not supported
 867
 868
 869 /*
 870  * 5.28
 871  * DOMAIN_CONSTRAINTS view
 872  */
 873
 874 CREATE VIEW domain_constraints AS
 875     SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
 876            CAST(rs.nspname AS sql_identifier) AS constraint_schema,
 877            CAST(con.conname AS sql_identifier) AS constraint_name,
 878            CAST(current_database() AS sql_identifier) AS domain_catalog,
 879            CAST(n.nspname AS sql_identifier) AS domain_schema,
 880            CAST(t.typname AS sql_identifier) AS domain_name,
 881            CAST(CASE WHEN condeferrable THEN 'YES' ELSE 'NO' END
 882              AS yes_or_no) AS is_deferrable,
 883            CAST(CASE WHEN condeferred THEN 'YES' ELSE 'NO' END
 884              AS yes_or_no) AS initially_deferred
 885     FROM pg_namespace rs, pg_namespace n, pg_constraint con, pg_type t
 886     WHERE rs.oid = con.connamespace
 887           AND n.oid = t.typnamespace
 888           AND t.oid = con.contypid
 889           AND (pg_has_role(t.typowner, 'USAGE')
 890                OR has_type_privilege(t.oid, 'USAGE'));
 891
 892 GRANT SELECT ON domain_constraints TO PUBLIC;
 893
 894
 895 /*
 896  * DOMAIN_UDT_USAGE view
 897  * apparently removed in SQL:2003
 898  */
 899
 900 CREATE VIEW domain_udt_usage AS
 901     SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
 902            CAST(nbt.nspname AS sql_identifier) AS udt_schema,
 903            CAST(bt.typname AS sql_identifier) AS udt_name,
 904            CAST(current_database() AS sql_identifier) AS domain_catalog,
 905            CAST(nt.nspname AS sql_identifier) AS domain_schema,
 906            CAST(t.typname AS sql_identifier) AS domain_name
 907
 908     FROM pg_type t, pg_namespace nt,
 909          pg_type bt, pg_namespace nbt
 910
 911     WHERE t.typnamespace = nt.oid
 912           AND t.typbasetype = bt.oid
 913           AND bt.typnamespace = nbt.oid
 914           AND t.typtype = 'd'
 915           AND pg_has_role(bt.typowner, 'USAGE');
 916
 917 GRANT SELECT ON domain_udt_usage TO PUBLIC;
 918
 919
 920 /*
 921  * 5.29
 922  * DOMAINS view
 923  */
 924
 925 CREATE VIEW domains AS
 926     SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
 927            CAST(nt.nspname AS sql_identifier) AS domain_schema,
 928            CAST(t.typname AS sql_identifier) AS domain_name,
 929
 930            CAST(
 931              CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
 932                   WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
 933                   ELSE 'USER-DEFINED' END
 934              AS character_data)
 935              AS data_type,
 936
 937            CAST(
 938              _pg_char_max_length(t.typbasetype, t.typtypmod)
 939              AS cardinal_number)
 940              AS character_maximum_length,
 941
 942            CAST(
 943              _pg_char_octet_length(t.typbasetype, t.typtypmod)
 944              AS cardinal_number)
 945              AS character_octet_length,
 946
 947            CAST(null AS sql_identifier) AS character_set_catalog,
 948            CAST(null AS sql_identifier) AS character_set_schema,
 949            CAST(null AS sql_identifier) AS character_set_name,
 950
 951            CAST(CASE WHEN nco.nspname IS NOT NULL THEN current_database() END AS sql_identifier) AS collation_catalog,
 952            CAST(nco.nspname AS sql_identifier) AS collation_schema,
 953            CAST(co.collname AS sql_identifier) AS collation_name,
 954
 955            CAST(
 956              _pg_numeric_precision(t.typbasetype, t.typtypmod)
 957              AS cardinal_number)
 958              AS numeric_precision,
 959
 960            CAST(
 961              _pg_numeric_precision_radix(t.typbasetype, t.typtypmod)
 962              AS cardinal_number)
 963              AS numeric_precision_radix,
 964
 965            CAST(
 966              _pg_numeric_scale(t.typbasetype, t.typtypmod)
 967              AS cardinal_number)
 968              AS numeric_scale,
 969
 970            CAST(
 971              _pg_datetime_precision(t.typbasetype, t.typtypmod)
 972              AS cardinal_number)
 973              AS datetime_precision,
 974
 975            CAST(
 976              _pg_interval_type(t.typbasetype, t.typtypmod)
 977              AS character_data)
 978              AS interval_type,
 979            CAST(null AS cardinal_number) AS interval_precision,
 980
 981            CAST(t.typdefault AS character_data) AS domain_default,
 982
 983            CAST(current_database() AS sql_identifier) AS udt_catalog,
 984            CAST(nbt.nspname AS sql_identifier) AS udt_schema,
 985            CAST(bt.typname AS sql_identifier) AS udt_name,
 986
 987            CAST(null AS sql_identifier) AS scope_catalog,
 988            CAST(null AS sql_identifier) AS scope_schema,
 989            CAST(null AS sql_identifier) AS scope_name,
 990
 991            CAST(null AS cardinal_number) AS maximum_cardinality,
 992            CAST(1 AS sql_identifier) AS dtd_identifier
 993
 994     FROM (pg_type t JOIN pg_namespace nt ON t.typnamespace = nt.oid)
 995          JOIN (pg_type bt JOIN pg_namespace nbt ON bt.typnamespace = nbt.oid)
 996            ON (t.typbasetype = bt.oid AND t.typtype = 'd')
 997          LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid))
 998            ON t.typcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default')
 999
1000     WHERE (pg_has_role(t.typowner, 'USAGE')
1001            OR has_type_privilege(t.oid, 'USAGE'));
1002
1003 GRANT SELECT ON domains TO PUBLIC;
1004
1005
1006 -- 5.30 ELEMENT_TYPES view appears later.
1007
1008
1009 /*
1010  * 5.31
1011  * ENABLED_ROLES view
1012  */
1013
1014 CREATE VIEW enabled_roles AS
1015     SELECT CAST(a.rolname AS sql_identifier) AS role_name
1016     FROM pg_authid a
1017     WHERE pg_has_role(a.oid, 'USAGE');
1018
1019 GRANT SELECT ON enabled_roles TO PUBLIC;
1020
1021
1022 /*
1023  * 5.32
1024  * FIELDS view
1025  */
1026
1027 -- feature not supported
1028
1029
1030 /*
1031  * 5.33
1032  * KEY_COLUMN_USAGE view
1033  */
1034
1035 CREATE VIEW key_column_usage AS
1036     SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1037            CAST(nc_nspname AS sql_identifier) AS constraint_schema,
1038            CAST(conname AS sql_identifier) AS constraint_name,
1039            CAST(current_database() AS sql_identifier) AS table_catalog,
1040            CAST(nr_nspname AS sql_identifier) AS table_schema,
1041            CAST(relname AS sql_identifier) AS table_name,
1042            CAST(a.attname AS sql_identifier) AS column_name,
1043            CAST((ss.x).n AS cardinal_number) AS ordinal_position,
1044            CAST(CASE WHEN contype = 'f' THEN
1045                        _pg_index_position(ss.conindid, ss.confkey[(ss.x).n])
1046                      ELSE NULL
1047                 END AS cardinal_number)
1048              AS position_in_unique_constraint
1049     FROM pg_attribute a,
1050          (SELECT r.oid AS roid, r.relname, r.relowner,
1051                  nc.nspname AS nc_nspname, nr.nspname AS nr_nspname,
1052                  c.oid AS coid, c.conname, c.contype, c.conindid,
1053                  c.confkey, c.confrelid,
1054                  _pg_expandarray(c.conkey) AS x
1055           FROM pg_namespace nr, pg_class r, pg_namespace nc,
1056                pg_constraint c
1057           WHERE nr.oid = r.relnamespace
1058                 AND r.oid = c.conrelid
1059                 AND nc.oid = c.connamespace
1060                 AND c.contype IN ('p', 'u', 'f')
1061                 AND r.relkind = 'r'
1062                 AND (NOT pg_is_other_temp_schema(nr.oid)) ) AS ss
1063     WHERE ss.roid = a.attrelid
1064           AND a.attnum = (ss.x).x
1065           AND NOT a.attisdropped
1066           AND (pg_has_role(relowner, 'USAGE')
1067                OR has_column_privilege(roid, a.attnum,
1068                                        'SELECT, INSERT, UPDATE, REFERENCES'));
1069
1070 GRANT SELECT ON key_column_usage TO PUBLIC;
1071
1072
1073 /*
1074  * 5.34
1075  * KEY_PERIOD_USAGE view
1076  */
1077
1078 -- feature not supported
1079
1080
1081 /*
1082  * 5.35
1083  * METHOD_SPECIFICATION_PARAMETERS view
1084  */
1085
1086 -- feature not supported
1087
1088
1089 /*
1090  * 5.36
1091  * METHOD_SPECIFICATIONS view
1092  */
1093
1094 -- feature not supported
1095
1096
1097 /*
1098  * 5.37
1099  * PARAMETERS view
1100  */
1101
1102 CREATE VIEW parameters AS
1103     SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
1104            CAST(n_nspname AS sql_identifier) AS specific_schema,
1105            CAST(proname || '_' || CAST(p_oid AS text) AS sql_identifier) AS specific_name,
1106            CAST((ss.x).n AS cardinal_number) AS ordinal_position,
1107            CAST(
1108              CASE WHEN proargmodes IS NULL THEN 'IN'
1109                 WHEN proargmodes[(ss.x).n] = 'i' THEN 'IN'
1110                 WHEN proargmodes[(ss.x).n] = 'o' THEN 'OUT'
1111                 WHEN proargmodes[(ss.x).n] = 'b' THEN 'INOUT'
1112                 WHEN proargmodes[(ss.x).n] = 'v' THEN 'IN'
1113                 WHEN proargmodes[(ss.x).n] = 't' THEN 'OUT'
1114              END AS character_data) AS parameter_mode,
1115            CAST('NO' AS yes_or_no) AS is_result,
1116            CAST('NO' AS yes_or_no) AS as_locator,
1117            CAST(NULLIF(proargnames[(ss.x).n], '') AS sql_identifier) AS parameter_name,
1118            CAST(
1119              CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
1120                   WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
1121                   ELSE 'USER-DEFINED' END AS character_data)
1122              AS data_type,
1123            CAST(null AS cardinal_number) AS character_maximum_length,
1124            CAST(null AS cardinal_number) AS character_octet_length,
1125            CAST(null AS sql_identifier) AS character_set_catalog,
1126            CAST(null AS sql_identifier) AS character_set_schema,
1127            CAST(null AS sql_identifier) AS character_set_name,
1128            CAST(null AS sql_identifier) AS collation_catalog,
1129            CAST(null AS sql_identifier) AS collation_schema,
1130            CAST(null AS sql_identifier) AS collation_name,
1131            CAST(null AS cardinal_number) AS numeric_precision,
1132            CAST(null AS cardinal_number) AS numeric_precision_radix,
1133            CAST(null AS cardinal_number) AS numeric_scale,
1134            CAST(null AS cardinal_number) AS datetime_precision,
1135            CAST(null AS character_data) AS interval_type,
1136            CAST(null AS cardinal_number) AS interval_precision,
1137            CAST(current_database() AS sql_identifier) AS udt_catalog,
1138            CAST(nt.nspname AS sql_identifier) AS udt_schema,
1139            CAST(t.typname AS sql_identifier) AS udt_name,
1140            CAST(null AS sql_identifier) AS scope_catalog,
1141            CAST(null AS sql_identifier) AS scope_schema,
1142            CAST(null AS sql_identifier) AS scope_name,
1143            CAST(null AS cardinal_number) AS maximum_cardinality,
1144            CAST((ss.x).n AS sql_identifier) AS dtd_identifier,
1145            CAST(
1146              CASE WHEN pg_has_role(proowner, 'USAGE')
1147                   THEN pg_get_function_arg_default(p_oid, (ss.x).n)
1148                   ELSE NULL END
1149              AS character_data) AS parameter_default
1150
1151     FROM pg_type t, pg_namespace nt,
1152          (SELECT n.nspname AS n_nspname, p.proname, p.oid AS p_oid, p.proowner,
1153                  p.proargnames, p.proargmodes,
1154                  _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
1155           FROM pg_namespace n, pg_proc p
1156           WHERE n.oid = p.pronamespace
1157                 AND (pg_has_role(p.proowner, 'USAGE') OR
1158                      has_function_privilege(p.oid, 'EXECUTE'))) AS ss
1159     WHERE t.oid = (ss.x).x AND t.typnamespace = nt.oid;
1160
1161 GRANT SELECT ON parameters TO PUBLIC;
1162
1163
1164 /*
1165  * 5.38
1166  * PERIODS view
1167  */
1168
1169 -- feature not supported
1170
1171
1172 /*
1173  * 5.39
1174  * REFERENCED_TYPES view
1175  */
1176
1177 -- feature not supported
1178
1179
1180 /*
1181  * 5.40
1182  * REFERENTIAL_CONSTRAINTS view
1183  */
1184
1185 CREATE VIEW referential_constraints AS
1186     SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1187            CAST(ncon.nspname AS sql_identifier) AS constraint_schema,
1188            CAST(con.conname AS sql_identifier) AS constraint_name,
1189            CAST(
1190              CASE WHEN npkc.nspname IS NULL THEN NULL
1191                   ELSE current_database() END
1192              AS sql_identifier) AS unique_constraint_catalog,
1193            CAST(npkc.nspname AS sql_identifier) AS unique_constraint_schema,
1194            CAST(pkc.conname AS sql_identifier) AS unique_constraint_name,
1195
1196            CAST(
1197              CASE con.confmatchtype WHEN 'f' THEN 'FULL'
1198                                     WHEN 'p' THEN 'PARTIAL'
1199                                     WHEN 's' THEN 'NONE' END
1200              AS character_data) AS match_option,
1201
1202            CAST(
1203              CASE con.confupdtype WHEN 'c' THEN 'CASCADE'
1204                                   WHEN 'n' THEN 'SET NULL'
1205                                   WHEN 'd' THEN 'SET DEFAULT'
1206                                   WHEN 'r' THEN 'RESTRICT'
1207                                   WHEN 'a' THEN 'NO ACTION' END
1208              AS character_data) AS update_rule,
1209
1210            CAST(
1211              CASE con.confdeltype WHEN 'c' THEN 'CASCADE'
1212                                   WHEN 'n' THEN 'SET NULL'
1213                                   WHEN 'd' THEN 'SET DEFAULT'
1214                                   WHEN 'r' THEN 'RESTRICT'
1215                                   WHEN 'a' THEN 'NO ACTION' END
1216              AS character_data) AS delete_rule
1217
1218     FROM (pg_namespace ncon
1219           INNER JOIN pg_constraint con ON ncon.oid = con.connamespace
1220           INNER JOIN pg_class c ON con.conrelid = c.oid AND con.contype = 'f')
1221          LEFT JOIN pg_depend d1  -- find constraint's dependency on an index
1222           ON d1.objid = con.oid AND d1.classid = 'pg_constraint'::regclass
1223              AND d1.refclassid = 'pg_class'::regclass AND d1.refobjsubid = 0
1224          LEFT JOIN pg_depend d2  -- find pkey/unique constraint for that index
1225           ON d2.refclassid = 'pg_constraint'::regclass
1226              AND d2.classid = 'pg_class'::regclass
1227              AND d2.objid = d1.refobjid AND d2.objsubid = 0
1228              AND d2.deptype = 'i'
1229          LEFT JOIN pg_constraint pkc ON pkc.oid = d2.refobjid
1230             AND pkc.contype IN ('p', 'u')
1231             AND pkc.conrelid = con.confrelid
1232          LEFT JOIN pg_namespace npkc ON pkc.connamespace = npkc.oid
1233
1234     WHERE pg_has_role(c.relowner, 'USAGE')
1235           -- SELECT privilege omitted, per SQL standard
1236           OR has_table_privilege(c.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1237           OR has_any_column_privilege(c.oid, 'INSERT, UPDATE, REFERENCES') ;
1238
1239 GRANT SELECT ON referential_constraints TO PUBLIC;
1240
1241
1242 /*
1243  * 5.41
1244  * ROLE_COLUMN_GRANTS view
1245  */
1246
1247 CREATE VIEW role_column_grants AS
1248     SELECT grantor,
1249            grantee,
1250            table_catalog,
1251            table_schema,
1252            table_name,
1253            column_name,
1254            privilege_type,
1255            is_grantable
1256     FROM column_privileges
1257     WHERE grantor IN (SELECT role_name FROM enabled_roles)
1258           OR grantee IN (SELECT role_name FROM enabled_roles);
1259
1260 GRANT SELECT ON role_column_grants TO PUBLIC;
1261
1262
1263 -- 5.42 ROLE_ROUTINE_GRANTS view is based on 5.49 ROUTINE_PRIVILEGES and is defined there instead.
1264
1265
1266 -- 5.43 ROLE_TABLE_GRANTS view is based on 5.62 TABLE_PRIVILEGES and is defined there instead.
1267
1268
1269 /*
1270  * 5.44
1271  * ROLE_TABLE_METHOD_GRANTS view
1272  */
1273
1274 -- feature not supported
1275
1276
1277
1278 -- 5.45 ROLE_USAGE_GRANTS view is based on 5.74 USAGE_PRIVILEGES and is defined there instead.
1279
1280
1281 -- 5.46 ROLE_UDT_GRANTS view is based on 5.73 UDT_PRIVILEGES and is defined there instead.
1282
1283
1284 /*
1285  * 5.47
1286  * ROUTINE_COLUMN_USAGE view
1287  */
1288
1289 -- not tracked by PostgreSQL
1290
1291
1292 /*
1293  * 5.48
1294  * ROUTINE_PERIOD_USAGE view
1295  */
1296
1297 -- feature not supported
1298
1299
1300 /*
1301  * 5.49
1302  * ROUTINE_PRIVILEGES view
1303  */
1304
1305 CREATE VIEW routine_privileges AS
1306     SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1307            CAST(grantee.rolname AS sql_identifier) AS grantee,
1308            CAST(current_database() AS sql_identifier) AS specific_catalog,
1309            CAST(n.nspname AS sql_identifier) AS specific_schema,
1310            CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1311            CAST(current_database() AS sql_identifier) AS routine_catalog,
1312            CAST(n.nspname AS sql_identifier) AS routine_schema,
1313            CAST(p.proname AS sql_identifier) AS routine_name,
1314            CAST('EXECUTE' AS character_data) AS privilege_type,
1315            CAST(
1316              CASE WHEN
1317                   -- object owner always has grant options
1318                   pg_has_role(grantee.oid, p.proowner, 'USAGE')
1319                   OR p.grantable
1320                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
1321
1322     FROM (
1323             SELECT oid, proname, proowner, pronamespace, (aclexplode(coalesce(proacl, acldefault('f', proowner)))).* FROM pg_proc
1324          ) p (oid, proname, proowner, pronamespace, grantor, grantee, prtype, grantable),
1325          pg_namespace n,
1326          pg_authid u_grantor,
1327          (
1328            SELECT oid, rolname FROM pg_authid
1329            UNION ALL
1330            SELECT 0::oid, 'PUBLIC'
1331          ) AS grantee (oid, rolname)
1332
1333     WHERE p.pronamespace = n.oid
1334           AND grantee.oid = p.grantee
1335           AND u_grantor.oid = p.grantor
1336           AND p.prtype IN ('EXECUTE')
1337           AND (pg_has_role(u_grantor.oid, 'USAGE')
1338                OR pg_has_role(grantee.oid, 'USAGE')
1339                OR grantee.rolname = 'PUBLIC');
1340
1341 GRANT SELECT ON routine_privileges TO PUBLIC;
1342
1343
1344 /*
1345  * 5.42
1346  * ROLE_ROUTINE_GRANTS view
1347  */
1348
1349 CREATE VIEW role_routine_grants AS
1350     SELECT grantor,
1351            grantee,
1352            specific_catalog,
1353            specific_schema,
1354            specific_name,
1355            routine_catalog,
1356            routine_schema,
1357            routine_name,
1358            privilege_type,
1359            is_grantable
1360     FROM routine_privileges
1361     WHERE grantor IN (SELECT role_name FROM enabled_roles)
1362           OR grantee IN (SELECT role_name FROM enabled_roles);
1363
1364 GRANT SELECT ON role_routine_grants TO PUBLIC;
1365
1366
1367 /*
1368  * 5.50
1369  * ROUTINE_ROUTINE_USAGE view
1370  */
1371
1372 -- not tracked by PostgreSQL
1373
1374
1375 /*
1376  * 5.51
1377  * ROUTINE_SEQUENCE_USAGE view
1378  */
1379
1380 -- not tracked by PostgreSQL
1381
1382
1383 /*
1384  * 5.52
1385  * ROUTINE_TABLE_USAGE view
1386  */
1387
1388 -- not tracked by PostgreSQL
1389
1390
1391 /*
1392  * 5.53
1393  * ROUTINES view
1394  */
1395
1396 CREATE VIEW routines AS
1397     SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
1398            CAST(n.nspname AS sql_identifier) AS specific_schema,
1399            CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1400            CAST(current_database() AS sql_identifier) AS routine_catalog,
1401            CAST(n.nspname AS sql_identifier) AS routine_schema,
1402            CAST(p.proname AS sql_identifier) AS routine_name,
1403            CAST('FUNCTION' AS character_data) AS routine_type,
1404            CAST(null AS sql_identifier) AS module_catalog,
1405            CAST(null AS sql_identifier) AS module_schema,
1406            CAST(null AS sql_identifier) AS module_name,
1407            CAST(null AS sql_identifier) AS udt_catalog,
1408            CAST(null AS sql_identifier) AS udt_schema,
1409            CAST(null AS sql_identifier) AS udt_name,
1410
1411            CAST(
1412              CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
1413                   WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
1414                   ELSE 'USER-DEFINED' END AS character_data)
1415              AS data_type,
1416            CAST(null AS cardinal_number) AS character_maximum_length,
1417            CAST(null AS cardinal_number) AS character_octet_length,
1418            CAST(null AS sql_identifier) AS character_set_catalog,
1419            CAST(null AS sql_identifier) AS character_set_schema,
1420            CAST(null AS sql_identifier) AS character_set_name,
1421            CAST(null AS sql_identifier) AS collation_catalog,
1422            CAST(null AS sql_identifier) AS collation_schema,
1423            CAST(null AS sql_identifier) AS collation_name,
1424            CAST(null AS cardinal_number) AS numeric_precision,
1425            CAST(null AS cardinal_number) AS numeric_precision_radix,
1426            CAST(null AS cardinal_number) AS numeric_scale,
1427            CAST(null AS cardinal_number) AS datetime_precision,
1428            CAST(null AS character_data) AS interval_type,
1429            CAST(null AS cardinal_number) AS interval_precision,
1430            CAST(current_database() AS sql_identifier) AS type_udt_catalog,
1431            CAST(nt.nspname AS sql_identifier) AS type_udt_schema,
1432            CAST(t.typname AS sql_identifier) AS type_udt_name,
1433            CAST(null AS sql_identifier) AS scope_catalog,
1434            CAST(null AS sql_identifier) AS scope_schema,
1435            CAST(null AS sql_identifier) AS scope_name,
1436            CAST(null AS cardinal_number) AS maximum_cardinality,
1437            CAST(0 AS sql_identifier) AS dtd_identifier,
1438
1439            CAST(CASE WHEN l.lanname = 'sql' THEN 'SQL' ELSE 'EXTERNAL' END AS character_data)
1440              AS routine_body,
1441            CAST(
1442              CASE WHEN pg_has_role(p.proowner, 'USAGE') THEN p.prosrc ELSE null END
1443              AS character_data) AS routine_definition,
1444            CAST(
1445              CASE WHEN l.lanname = 'c' THEN p.prosrc ELSE null END
1446              AS character_data) AS external_name,
1447            CAST(upper(l.lanname) AS character_data) AS external_language,
1448
1449            CAST('GENERAL' AS character_data) AS parameter_style,
1450            CAST(CASE WHEN p.provolatile = 'i' THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_deterministic,
1451            CAST('MODIFIES' AS character_data) AS sql_data_access,
1452            CAST(CASE WHEN p.proisstrict THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_null_call,
1453            CAST(null AS character_data) AS sql_path,
1454            CAST('YES' AS yes_or_no) AS schema_level_routine,
1455            CAST(0 AS cardinal_number) AS max_dynamic_result_sets,
1456            CAST(null AS yes_or_no) AS is_user_defined_cast,
1457            CAST(null AS yes_or_no) AS is_implicitly_invocable,
1458            CAST(CASE WHEN p.prosecdef THEN 'DEFINER' ELSE 'INVOKER' END AS character_data) AS security_type,
1459            CAST(null AS sql_identifier) AS to_sql_specific_catalog,
1460            CAST(null AS sql_identifier) AS to_sql_specific_schema,
1461            CAST(null AS sql_identifier) AS to_sql_specific_name,
1462            CAST('NO' AS yes_or_no) AS as_locator,
1463            CAST(null AS time_stamp) AS created,
1464            CAST(null AS time_stamp) AS last_altered,
1465            CAST(null AS yes_or_no) AS new_savepoint_level,
1466            CAST('NO' AS yes_or_no) AS is_udt_dependent,
1467
1468            CAST(null AS character_data) AS result_cast_from_data_type,
1469            CAST(null AS yes_or_no) AS result_cast_as_locator,
1470            CAST(null AS cardinal_number) AS result_cast_char_max_length,
1471            CAST(null AS cardinal_number) AS result_cast_char_octet_length,
1472            CAST(null AS sql_identifier) AS result_cast_char_set_catalog,
1473            CAST(null AS sql_identifier) AS result_cast_char_set_schema,
1474            CAST(null AS sql_identifier) AS result_cast_character_set_name,
1475            CAST(null AS sql_identifier) AS result_cast_collation_catalog,
1476            CAST(null AS sql_identifier) AS result_cast_collation_schema,
1477            CAST(null AS sql_identifier) AS result_cast_collation_name,
1478            CAST(null AS cardinal_number) AS result_cast_numeric_precision,
1479            CAST(null AS cardinal_number) AS result_cast_numeric_precision_radix,
1480            CAST(null AS cardinal_number) AS result_cast_numeric_scale,
1481            CAST(null AS cardinal_number) AS result_cast_datetime_precision,
1482            CAST(null AS character_data) AS result_cast_interval_type,
1483            CAST(null AS cardinal_number) AS result_cast_interval_precision,
1484            CAST(null AS sql_identifier) AS result_cast_type_udt_catalog,
1485            CAST(null AS sql_identifier) AS result_cast_type_udt_schema,
1486            CAST(null AS sql_identifier) AS result_cast_type_udt_name,
1487            CAST(null AS sql_identifier) AS result_cast_scope_catalog,
1488            CAST(null AS sql_identifier) AS result_cast_scope_schema,
1489            CAST(null AS sql_identifier) AS result_cast_scope_name,
1490            CAST(null AS cardinal_number) AS result_cast_maximum_cardinality,
1491            CAST(null AS sql_identifier) AS result_cast_dtd_identifier
1492
1493     FROM pg_namespace n, pg_proc p, pg_language l,
1494          pg_type t, pg_namespace nt
1495
1496     WHERE n.oid = p.pronamespace AND p.prolang = l.oid
1497           AND p.prorettype = t.oid AND t.typnamespace = nt.oid
1498           AND (pg_has_role(p.proowner, 'USAGE')
1499                OR has_function_privilege(p.oid, 'EXECUTE'));
1500
1501 GRANT SELECT ON routines TO PUBLIC;
1502
1503
1504 /*
1505  * 5.54
1506  * SCHEMATA view
1507  */
1508
1509 CREATE VIEW schemata AS
1510     SELECT CAST(current_database() AS sql_identifier) AS catalog_name,
1511            CAST(n.nspname AS sql_identifier) AS schema_name,
1512            CAST(u.rolname AS sql_identifier) AS schema_owner,
1513            CAST(null AS sql_identifier) AS default_character_set_catalog,
1514            CAST(null AS sql_identifier) AS default_character_set_schema,
1515            CAST(null AS sql_identifier) AS default_character_set_name,
1516            CAST(null AS character_data) AS sql_path
1517     FROM pg_namespace n, pg_authid u
1518     WHERE n.nspowner = u.oid
1519           AND (pg_has_role(n.nspowner, 'USAGE')
1520                OR has_schema_privilege(n.oid, 'CREATE, USAGE'));
1521
1522 GRANT SELECT ON schemata TO PUBLIC;
1523
1524
1525 /*
1526  * 5.55
1527  * SEQUENCES view
1528  */
1529
1530 CREATE VIEW sequences AS
1531     SELECT CAST(current_database() AS sql_identifier) AS sequence_catalog,
1532            CAST(nc.nspname AS sql_identifier) AS sequence_schema,
1533            CAST(c.relname AS sql_identifier) AS sequence_name,
1534            CAST('bigint' AS character_data) AS data_type,
1535            CAST(64 AS cardinal_number) AS numeric_precision,
1536            CAST(2 AS cardinal_number) AS numeric_precision_radix,
1537            CAST(0 AS cardinal_number) AS numeric_scale,
1538            CAST(p.start_value AS character_data) AS start_value,
1539            CAST(p.minimum_value AS character_data) AS minimum_value,
1540            CAST(p.maximum_value AS character_data) AS maximum_value,
1541            CAST(p.increment AS character_data) AS increment,
1542            CAST(CASE WHEN p.cycle_option THEN 'YES' ELSE 'NO' END AS yes_or_no) AS cycle_option
1543     FROM pg_namespace nc, pg_class c, LATERAL pg_sequence_parameters(c.oid) p
1544     WHERE c.relnamespace = nc.oid
1545           AND c.relkind = 'S'
1546           AND (NOT pg_is_other_temp_schema(nc.oid))
1547           AND (pg_has_role(c.relowner, 'USAGE')
1548                OR has_sequence_privilege(c.oid, 'SELECT, UPDATE, USAGE') );
1549
1550 GRANT SELECT ON sequences TO PUBLIC;
1551
1552
1553 /*
1554  * 5.56
1555  * SQL_FEATURES table
1556  */
1557
1558 CREATE TABLE sql_features (
1559     feature_id          character_data,
1560     feature_name        character_data,
1561     sub_feature_id      character_data,
1562     sub_feature_name    character_data,
1563     is_supported        yes_or_no,
1564     is_verified_by      character_data,
1565     comments            character_data
1566 ) WITHOUT OIDS;
1567
1568 -- Will be filled with external data by initdb.
1569
1570 GRANT SELECT ON sql_features TO PUBLIC;
1571
1572
1573 /*
1574  * 5.57
1575  * SQL_IMPLEMENTATION_INFO table
1576  */
1577
1578 -- Note: Implementation information items are defined in ISO/IEC 9075-3:2008,
1579 -- clause 9.1.
1580
1581 CREATE TABLE sql_implementation_info (
1582     implementation_info_id      character_data,
1583     implementation_info_name    character_data,
1584     integer_value               cardinal_number,
1585     character_value             character_data,
1586     comments                    character_data
1587 ) WITHOUT OIDS;
1588
1589 INSERT INTO sql_implementation_info VALUES ('10003', 'CATALOG NAME', NULL, 'Y', NULL);
1590 INSERT INTO sql_implementation_info VALUES ('10004', 'COLLATING SEQUENCE', NULL, (SELECT default_collate_name FROM character_sets), NULL);
1591 INSERT INTO sql_implementation_info VALUES ('23',    'CURSOR COMMIT BEHAVIOR', 1, NULL, 'close cursors and retain prepared statements');
1592 INSERT INTO sql_implementation_info VALUES ('2',     'DATA SOURCE NAME', NULL, '', NULL);
1593 INSERT INTO sql_implementation_info VALUES ('17',    'DBMS NAME', NULL, (select trim(trailing ' ' from substring(version() from '^[^0-9]*'))), NULL);
1594 INSERT INTO sql_implementation_info VALUES ('18',    'DBMS VERSION', NULL, '???', NULL); -- filled by initdb
1595 INSERT INTO sql_implementation_info VALUES ('26',    'DEFAULT TRANSACTION ISOLATION', 2, NULL, 'READ COMMITTED; user-settable');
1596 INSERT INTO sql_implementation_info VALUES ('28',    'IDENTIFIER CASE', 3, NULL, 'stored in mixed case - case sensitive');
1597 INSERT INTO sql_implementation_info VALUES ('85',    'NULL COLLATION', 0, NULL, 'nulls higher than non-nulls');
1598 INSERT INTO sql_implementation_info VALUES ('13',    'SERVER NAME', NULL, '', NULL);
1599 INSERT INTO sql_implementation_info VALUES ('94',    'SPECIAL CHARACTERS', NULL, '', 'all non-ASCII characters allowed');
1600 INSERT INTO sql_implementation_info VALUES ('46',    'TRANSACTION CAPABLE', 2, NULL, 'both DML and DDL');
1601
1602 GRANT SELECT ON sql_implementation_info TO PUBLIC;
1603
1604
1605 /*
1606  * SQL_LANGUAGES table
1607  * apparently removed in SQL:2008
1608  */
1609
1610 CREATE TABLE sql_languages (
1611     sql_language_source         character_data,
1612     sql_language_year           character_data,
1613     sql_language_conformance    character_data,
1614     sql_language_integrity      character_data,
1615     sql_language_implementation character_data,
1616     sql_language_binding_style  character_data,
1617     sql_language_programming_language character_data
1618 ) WITHOUT OIDS;
1619
1620 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'DIRECT', NULL);
1621 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
1622 INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'DIRECT', NULL);
1623 INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
1624
1625 GRANT SELECT ON sql_languages TO PUBLIC;
1626
1627
1628 /*
1629  * SQL_PACKAGES table
1630  * removed in SQL:2011
1631  */
1632
1633 CREATE TABLE sql_packages (
1634     feature_id      character_data,
1635     feature_name    character_data,
1636     is_supported    yes_or_no,
1637     is_verified_by  character_data,
1638     comments        character_data
1639 ) WITHOUT OIDS;
1640
1641 INSERT INTO sql_packages VALUES ('PKG000', 'Core', 'NO', NULL, '');
1642 INSERT INTO sql_packages VALUES ('PKG001', 'Enhanced datetime facilities', 'YES', NULL, '');
1643 INSERT INTO sql_packages VALUES ('PKG002', 'Enhanced integrity management', 'NO', NULL, '');
1644 INSERT INTO sql_packages VALUES ('PKG003', 'OLAP facilities', 'NO', NULL, '');
1645 INSERT INTO sql_packages VALUES ('PKG004', 'PSM', 'NO', NULL, 'PL/pgSQL is similar.');
1646 INSERT INTO sql_packages VALUES ('PKG005', 'CLI', 'NO', NULL, 'ODBC is similar.');
1647 INSERT INTO sql_packages VALUES ('PKG006', 'Basic object support', 'NO', NULL, '');
1648 INSERT INTO sql_packages VALUES ('PKG007', 'Enhanced object support', 'NO', NULL, '');
1649 INSERT INTO sql_packages VALUES ('PKG008', 'Active database', 'NO', NULL, '');
1650 INSERT INTO sql_packages VALUES ('PKG010', 'OLAP', 'NO', NULL, 'NO');
1651
1652 GRANT SELECT ON sql_packages TO PUBLIC;
1653
1654
1655 /*
1656  * 5.58
1657  * SQL_PARTS table
1658  */
1659
1660 CREATE TABLE sql_parts (
1661     feature_id      character_data,
1662     feature_name    character_data,
1663     is_supported    yes_or_no,
1664     is_verified_by  character_data,
1665     comments        character_data
1666 ) WITHOUT OIDS;
1667
1668 INSERT INTO sql_parts VALUES ('1', 'Framework (SQL/Framework)', 'NO', NULL, '');
1669 INSERT INTO sql_parts VALUES ('2', 'Foundation (SQL/Foundation)', 'NO', NULL, '');
1670 INSERT INTO sql_parts VALUES ('3', 'Call-Level Interface (SQL/CLI)', 'NO', NULL, '');
1671 INSERT INTO sql_parts VALUES ('4', 'Persistent Stored Modules (SQL/PSM)', 'NO', NULL, '');
1672 INSERT INTO sql_parts VALUES ('9', 'Management of External Data (SQL/MED)', 'NO', NULL, '');
1673 INSERT INTO sql_parts VALUES ('10', 'Object Language Bindings (SQL/OLB)', 'NO', NULL, '');
1674 INSERT INTO sql_parts VALUES ('11', 'Information and Definition Schema (SQL/Schemata)', 'NO', NULL, '');
1675 INSERT INTO sql_parts VALUES ('13', 'Routines and Types Using the Java Programming Language (SQL/JRT)', 'NO', NULL, '');
1676 INSERT INTO sql_parts VALUES ('14', 'XML-Related Specifications (SQL/XML)', 'YES', NULL, '');
1677
1678
1679 /*
1680  * 5.59
1681  * SQL_SIZING table
1682  */
1683
1684 -- Note: Sizing items are defined in ISO/IEC 9075-3:2008, clause 9.2.
1685
1686 CREATE TABLE sql_sizing (
1687     sizing_id       cardinal_number,
1688     sizing_name     character_data,
1689     supported_value cardinal_number,
1690     comments        character_data
1691 ) WITHOUT OIDS;
1692
1693 INSERT INTO sql_sizing VALUES (34,    'MAXIMUM CATALOG NAME LENGTH', 63, NULL);
1694 INSERT INTO sql_sizing VALUES (30,    'MAXIMUM COLUMN NAME LENGTH', 63, NULL);
1695 INSERT INTO sql_sizing VALUES (97,    'MAXIMUM COLUMNS IN GROUP BY', 0, NULL);
1696 INSERT INTO sql_sizing VALUES (99,    'MAXIMUM COLUMNS IN ORDER BY', 0, NULL);
1697 INSERT INTO sql_sizing VALUES (100,   'MAXIMUM COLUMNS IN SELECT', 1664, NULL); -- match MaxTupleAttributeNumber
1698 INSERT INTO sql_sizing VALUES (101,   'MAXIMUM COLUMNS IN TABLE', 1600, NULL); -- match MaxHeapAttributeNumber
1699 INSERT INTO sql_sizing VALUES (1,     'MAXIMUM CONCURRENT ACTIVITIES', 0, NULL);
1700 INSERT INTO sql_sizing VALUES (31,    'MAXIMUM CURSOR NAME LENGTH', 63, NULL);
1701 INSERT INTO sql_sizing VALUES (0,     'MAXIMUM DRIVER CONNECTIONS', NULL, NULL);
1702 INSERT INTO sql_sizing VALUES (10005, 'MAXIMUM IDENTIFIER LENGTH', 63, NULL);
1703 INSERT INTO sql_sizing VALUES (32,    'MAXIMUM SCHEMA NAME LENGTH', 63, NULL);
1704 INSERT INTO sql_sizing VALUES (20000, 'MAXIMUM STATEMENT OCTETS', 0, NULL);
1705 INSERT INTO sql_sizing VALUES (20001, 'MAXIMUM STATEMENT OCTETS DATA', 0, NULL);
1706 INSERT INTO sql_sizing VALUES (20002, 'MAXIMUM STATEMENT OCTETS SCHEMA', 0, NULL);
1707 INSERT INTO sql_sizing VALUES (35,    'MAXIMUM TABLE NAME LENGTH', 63, NULL);
1708 INSERT INTO sql_sizing VALUES (106,   'MAXIMUM TABLES IN SELECT', 0, NULL);
1709 INSERT INTO sql_sizing VALUES (107,   'MAXIMUM USER NAME LENGTH', 63, NULL);
1710 INSERT INTO sql_sizing VALUES (25000, 'MAXIMUM CURRENT DEFAULT TRANSFORM GROUP LENGTH', NULL, NULL);
1711 INSERT INTO sql_sizing VALUES (25001, 'MAXIMUM CURRENT TRANSFORM GROUP LENGTH', NULL, NULL);
1712 INSERT INTO sql_sizing VALUES (25002, 'MAXIMUM CURRENT PATH LENGTH', 0, NULL);
1713 INSERT INTO sql_sizing VALUES (25003, 'MAXIMUM CURRENT ROLE LENGTH', NULL, NULL);
1714 INSERT INTO sql_sizing VALUES (25004, 'MAXIMUM SESSION USER LENGTH', 63, NULL);
1715 INSERT INTO sql_sizing VALUES (25005, 'MAXIMUM SYSTEM USER LENGTH', 63, NULL);
1716
1717 UPDATE sql_sizing
1718     SET supported_value = (SELECT typlen-1 FROM pg_catalog.pg_type WHERE typname = 'name'),
1719         comments = 'Might be less, depending on character set.'
1720     WHERE supported_value = 63;
1721
1722 GRANT SELECT ON sql_sizing TO PUBLIC;
1723
1724
1725 /*
1726  * SQL_SIZING_PROFILES table
1727  * removed in SQL:2011
1728  */
1729
1730 -- The data in this table are defined by various profiles of SQL.
1731 -- Since we don't have any information about such profiles, we provide
1732 -- an empty table.
1733
1734 CREATE TABLE sql_sizing_profiles (
1735     sizing_id       cardinal_number,
1736     sizing_name     character_data,
1737     profile_id      character_data,
1738     required_value  cardinal_number,
1739     comments        character_data
1740 ) WITHOUT OIDS;
1741
1742 GRANT SELECT ON sql_sizing_profiles TO PUBLIC;
1743
1744
1745 /*
1746  * 5.60
1747  * TABLE_CONSTRAINTS view
1748  */
1749
1750 CREATE VIEW table_constraints AS
1751     SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1752            CAST(nc.nspname AS sql_identifier) AS constraint_schema,
1753            CAST(c.conname AS sql_identifier) AS constraint_name,
1754            CAST(current_database() AS sql_identifier) AS table_catalog,
1755            CAST(nr.nspname AS sql_identifier) AS table_schema,
1756            CAST(r.relname AS sql_identifier) AS table_name,
1757            CAST(
1758              CASE c.contype WHEN 'c' THEN 'CHECK'
1759                             WHEN 'f' THEN 'FOREIGN KEY'
1760                             WHEN 'p' THEN 'PRIMARY KEY'
1761                             WHEN 'u' THEN 'UNIQUE' END
1762              AS character_data) AS constraint_type,
1763            CAST(CASE WHEN c.condeferrable THEN 'YES' ELSE 'NO' END AS yes_or_no)
1764              AS is_deferrable,
1765            CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS yes_or_no)
1766              AS initially_deferred
1767
1768     FROM pg_namespace nc,
1769          pg_namespace nr,
1770          pg_constraint c,
1771          pg_class r
1772
1773     WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace
1774           AND c.conrelid = r.oid
1775           AND c.contype NOT IN ('t', 'x')  -- ignore nonstandard constraints
1776           AND r.relkind = 'r'
1777           AND (NOT pg_is_other_temp_schema(nr.oid))
1778           AND (pg_has_role(r.relowner, 'USAGE')
1779                -- SELECT privilege omitted, per SQL standard
1780                OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1781                OR has_any_column_privilege(r.oid, 'INSERT, UPDATE, REFERENCES') )
1782
1783     UNION ALL
1784
1785     -- not-null constraints
1786
1787     SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1788            CAST(nr.nspname AS sql_identifier) AS constraint_schema,
1789            CAST(CAST(nr.oid AS text) || '_' || CAST(r.oid AS text) || '_' || CAST(a.attnum AS text) || '_not_null' AS sql_identifier) AS constraint_name, -- XXX
1790            CAST(current_database() AS sql_identifier) AS table_catalog,
1791            CAST(nr.nspname AS sql_identifier) AS table_schema,
1792            CAST(r.relname AS sql_identifier) AS table_name,
1793            CAST('CHECK' AS character_data) AS constraint_type,
1794            CAST('NO' AS yes_or_no) AS is_deferrable,
1795            CAST('NO' AS yes_or_no) AS initially_deferred
1796
1797     FROM pg_namespace nr,
1798          pg_class r,
1799          pg_attribute a
1800
1801     WHERE nr.oid = r.relnamespace
1802           AND r.oid = a.attrelid
1803           AND a.attnotnull
1804           AND a.attnum > 0
1805           AND NOT a.attisdropped
1806           AND r.relkind = 'r'
1807           AND (NOT pg_is_other_temp_schema(nr.oid))
1808           AND (pg_has_role(r.relowner, 'USAGE')
1809                -- SELECT privilege omitted, per SQL standard
1810                OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1811                OR has_any_column_privilege(r.oid, 'INSERT, UPDATE, REFERENCES') );
1812
1813 GRANT SELECT ON table_constraints TO PUBLIC;
1814
1815
1816 /*
1817  * 5.61
1818  * TABLE_METHOD_PRIVILEGES view
1819  */
1820
1821 -- feature not supported
1822
1823
1824 /*
1825  * 5.62
1826  * TABLE_PRIVILEGES view
1827  */
1828
1829 CREATE VIEW table_privileges AS
1830     SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1831            CAST(grantee.rolname AS sql_identifier) AS grantee,
1832            CAST(current_database() AS sql_identifier) AS table_catalog,
1833            CAST(nc.nspname AS sql_identifier) AS table_schema,
1834            CAST(c.relname AS sql_identifier) AS table_name,
1835            CAST(c.prtype AS character_data) AS privilege_type,
1836            CAST(
1837              CASE WHEN
1838                   -- object owner always has grant options
1839                   pg_has_role(grantee.oid, c.relowner, 'USAGE')
1840                   OR c.grantable
1841                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable,
1842            CAST(CASE WHEN c.prtype = 'SELECT' THEN 'YES' ELSE 'NO' END AS yes_or_no) AS with_hierarchy
1843
1844     FROM (
1845             SELECT oid, relname, relnamespace, relkind, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).* FROM pg_class
1846          ) AS c (oid, relname, relnamespace, relkind, relowner, grantor, grantee, prtype, grantable),
1847          pg_namespace nc,
1848          pg_authid u_grantor,
1849          (
1850            SELECT oid, rolname FROM pg_authid
1851            UNION ALL
1852            SELECT 0::oid, 'PUBLIC'
1853          ) AS grantee (oid, rolname)
1854
1855     WHERE c.relnamespace = nc.oid
1856           AND c.relkind IN ('r', 'v')
1857           AND c.grantee = grantee.oid
1858           AND c.grantor = u_grantor.oid
1859           AND c.prtype IN ('INSERT', 'SELECT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER')
1860           AND (pg_has_role(u_grantor.oid, 'USAGE')
1861                OR pg_has_role(grantee.oid, 'USAGE')
1862                OR grantee.rolname = 'PUBLIC');
1863
1864 GRANT SELECT ON table_privileges TO PUBLIC;
1865
1866
1867 /*
1868  * 5.43
1869  * ROLE_TABLE_GRANTS view
1870  */
1871
1872 CREATE VIEW role_table_grants AS
1873     SELECT grantor,
1874            grantee,
1875            table_catalog,
1876            table_schema,
1877            table_name,
1878            privilege_type,
1879            is_grantable,
1880            with_hierarchy
1881     FROM table_privileges
1882     WHERE grantor IN (SELECT role_name FROM enabled_roles)
1883           OR grantee IN (SELECT role_name FROM enabled_roles);
1884
1885 GRANT SELECT ON role_table_grants TO PUBLIC;
1886
1887
1888 /*
1889  * 5.63
1890  * TABLES view
1891  */
1892
1893 CREATE VIEW tables AS
1894     SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
1895            CAST(nc.nspname AS sql_identifier) AS table_schema,
1896            CAST(c.relname AS sql_identifier) AS table_name,
1897
1898            CAST(
1899              CASE WHEN nc.oid = pg_my_temp_schema() THEN 'LOCAL TEMPORARY'
1900                   WHEN c.relkind = 'r' THEN 'BASE TABLE'
1901                   WHEN c.relkind = 'v' THEN 'VIEW'
1902                   WHEN c.relkind = 'f' THEN 'FOREIGN TABLE'
1903                   ELSE null END
1904              AS character_data) AS table_type,
1905
1906            CAST(null AS sql_identifier) AS self_referencing_column_name,
1907            CAST(null AS character_data) AS reference_generation,
1908
1909            CAST(CASE WHEN t.typname IS NOT NULL THEN current_database() ELSE null END AS sql_identifier) AS user_defined_type_catalog,
1910            CAST(nt.nspname AS sql_identifier) AS user_defined_type_schema,
1911            CAST(t.typname AS sql_identifier) AS user_defined_type_name,
1912
1913            CAST(CASE WHEN c.relkind = 'r' OR
1914                           (c.relkind IN ('v', 'f') AND
1915                            -- 1 << CMD_INSERT
1916                            pg_relation_is_updatable(c.oid, false) & 8 = 8)
1917                 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_insertable_into,
1918
1919            CAST(CASE WHEN t.typname IS NOT NULL THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_typed,
1920            CAST(null AS character_data) AS commit_action
1921
1922     FROM pg_namespace nc JOIN pg_class c ON (nc.oid = c.relnamespace)
1923            LEFT JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON (c.reloftype = t.oid)
1924
1925     WHERE c.relkind IN ('r', 'v', 'f')
1926           AND (NOT pg_is_other_temp_schema(nc.oid))
1927           AND (pg_has_role(c.relowner, 'USAGE')
1928                OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1929                OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES') );
1930
1931 GRANT SELECT ON tables TO PUBLIC;
1932
1933
1934 /*
1935  * 5.64
1936  * TRANSFORMS view
1937  */
1938
1939 CREATE VIEW transforms AS
1940     SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
1941            CAST(nt.nspname AS sql_identifier) AS udt_schema,
1942            CAST(t.typname AS sql_identifier) AS udt_name,
1943            CAST(current_database() AS sql_identifier) AS specific_catalog,
1944            CAST(np.nspname AS sql_identifier) AS specific_schema,
1945            CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1946            CAST(l.lanname AS sql_identifier) AS group_name,
1947            CAST('FROM SQL' AS character_data) AS transform_type
1948     FROM pg_type t JOIN pg_transform x ON t.oid = x.trftype
1949          JOIN pg_language l ON x.trflang = l.oid
1950          JOIN pg_proc p ON x.trffromsql = p.oid
1951          JOIN pg_namespace nt ON t.typnamespace = nt.oid
1952          JOIN pg_namespace np ON p.pronamespace = np.oid
1953
1954   UNION
1955
1956     SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
1957            CAST(nt.nspname AS sql_identifier) AS udt_schema,
1958            CAST(t.typname AS sql_identifier) AS udt_name,
1959            CAST(current_database() AS sql_identifier) AS specific_catalog,
1960            CAST(np.nspname AS sql_identifier) AS specific_schema,
1961            CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1962            CAST(l.lanname AS sql_identifier) AS group_name,
1963            CAST('TO SQL' AS character_data) AS transform_type
1964     FROM pg_type t JOIN pg_transform x ON t.oid = x.trftype
1965          JOIN pg_language l ON x.trflang = l.oid
1966          JOIN pg_proc p ON x.trftosql = p.oid
1967          JOIN pg_namespace nt ON t.typnamespace = nt.oid
1968          JOIN pg_namespace np ON p.pronamespace = np.oid
1969
1970   ORDER BY udt_catalog, udt_schema, udt_name, group_name, transform_type  -- some sensible grouping for interactive use
1971 ;
1972
1973
1974 /*
1975  * 5.65
1976  * TRANSLATIONS view
1977  */
1978
1979 -- feature not supported
1980
1981
1982 /*
1983  * 5.66
1984  * TRIGGERED_UPDATE_COLUMNS view
1985  */
1986
1987 CREATE VIEW triggered_update_columns AS
1988     SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1989            CAST(n.nspname AS sql_identifier) AS trigger_schema,
1990            CAST(t.tgname AS sql_identifier) AS trigger_name,
1991            CAST(current_database() AS sql_identifier) AS event_object_catalog,
1992            CAST(n.nspname AS sql_identifier) AS event_object_schema,
1993            CAST(c.relname AS sql_identifier) AS event_object_table,
1994            CAST(a.attname AS sql_identifier) AS event_object_column
1995
1996     FROM pg_namespace n, pg_class c, pg_trigger t,
1997          (SELECT tgoid, (ta0.tgat).x AS tgattnum, (ta0.tgat).n AS tgattpos
1998           FROM (SELECT oid AS tgoid, information_schema._pg_expandarray(tgattr) AS tgat FROM pg_trigger) AS ta0) AS ta,
1999          pg_attribute a
2000
2001     WHERE n.oid = c.relnamespace
2002           AND c.oid = t.tgrelid
2003           AND t.oid = ta.tgoid
2004           AND (a.attrelid, a.attnum) = (t.tgrelid, ta.tgattnum)
2005           AND NOT t.tgisinternal
2006           AND (NOT pg_is_other_temp_schema(n.oid))
2007           AND (pg_has_role(c.relowner, 'USAGE')
2008                -- SELECT privilege omitted, per SQL standard
2009                OR has_column_privilege(c.oid, a.attnum, 'INSERT, UPDATE, REFERENCES') );
2010
2011 GRANT SELECT ON triggered_update_columns TO PUBLIC;
2012
2013
2014 /*
2015  * 5.67
2016  * TRIGGER_COLUMN_USAGE view
2017  */
2018
2019 -- not tracked by PostgreSQL
2020
2021
2022 /*
2023  * 5.68
2024  * TRIGGER_PERIOD_USAGE view
2025  */
2026
2027 -- feature not supported
2028
2029
2030 /*
2031  * 5.69
2032  * TRIGGER_ROUTINE_USAGE view
2033  */
2034
2035 -- not tracked by PostgreSQL
2036
2037
2038 /*
2039  * 5.70
2040  * TRIGGER_SEQUENCE_USAGE view
2041  */
2042
2043 -- not tracked by PostgreSQL
2044
2045
2046 /*
2047  * 5.71
2048  * TRIGGER_TABLE_USAGE view
2049  */
2050
2051 -- not tracked by PostgreSQL
2052
2053
2054 /*
2055  * 5.72
2056  * TRIGGERS view
2057  */
2058
2059 CREATE VIEW triggers AS
2060     SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
2061            CAST(n.nspname AS sql_identifier) AS trigger_schema,
2062            CAST(t.tgname AS sql_identifier) AS trigger_name,
2063            CAST(em.text AS character_data) AS event_manipulation,
2064            CAST(current_database() AS sql_identifier) AS event_object_catalog,
2065            CAST(n.nspname AS sql_identifier) AS event_object_schema,
2066            CAST(c.relname AS sql_identifier) AS event_object_table,
2067            CAST(null AS cardinal_number) AS action_order,
2068            -- XXX strange hacks follow
2069            CAST(
2070              CASE WHEN pg_has_role(c.relowner, 'USAGE')
2071                THEN (SELECT m[1] FROM regexp_matches(pg_get_triggerdef(t.oid), E'.{35,} WHEN \\((.+)\\) EXECUTE PROCEDURE') AS rm(m) LIMIT 1)
2072                ELSE null END
2073              AS character_data) AS action_condition,
2074            CAST(
2075              substring(pg_get_triggerdef(t.oid) from
2076                        position('EXECUTE PROCEDURE' in substring(pg_get_triggerdef(t.oid) from 48)) + 47)
2077              AS character_data) AS action_statement,
2078            CAST(
2079              -- hard-wired reference to TRIGGER_TYPE_ROW
2080              CASE t.tgtype & 1 WHEN 1 THEN 'ROW' ELSE 'STATEMENT' END
2081              AS character_data) AS action_orientation,
2082            CAST(
2083              -- hard-wired refs to TRIGGER_TYPE_BEFORE, TRIGGER_TYPE_INSTEAD
2084              CASE t.tgtype & 66 WHEN 2 THEN 'BEFORE' WHEN 64 THEN 'INSTEAD OF' ELSE 'AFTER' END
2085              AS character_data) AS action_timing,
2086            CAST(null AS sql_identifier) AS action_reference_old_table,
2087            CAST(null AS sql_identifier) AS action_reference_new_table,
2088            CAST(null AS sql_identifier) AS action_reference_old_row,
2089            CAST(null AS sql_identifier) AS action_reference_new_row,
2090            CAST(null AS time_stamp) AS created
2091
2092     FROM pg_namespace n, pg_class c, pg_trigger t,
2093          -- hard-wired refs to TRIGGER_TYPE_INSERT, TRIGGER_TYPE_DELETE,
2094          -- TRIGGER_TYPE_UPDATE; we intentionally omit TRIGGER_TYPE_TRUNCATE
2095          (VALUES (4, 'INSERT'),
2096                  (8, 'DELETE'),
2097                  (16, 'UPDATE')) AS em (num, text)
2098
2099     WHERE n.oid = c.relnamespace
2100           AND c.oid = t.tgrelid
2101           AND t.tgtype & em.num <> 0
2102           AND NOT t.tgisinternal
2103           AND (NOT pg_is_other_temp_schema(n.oid))
2104           AND (pg_has_role(c.relowner, 'USAGE')
2105                -- SELECT privilege omitted, per SQL standard
2106                OR has_table_privilege(c.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
2107                OR has_any_column_privilege(c.oid, 'INSERT, UPDATE, REFERENCES') );
2108
2109 GRANT SELECT ON triggers TO PUBLIC;
2110
2111
2112 /*
2113  * 5.73
2114  * UDT_PRIVILEGES view
2115  */
2116
2117 CREATE VIEW udt_privileges AS
2118     SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2119            CAST(grantee.rolname AS sql_identifier) AS grantee,
2120            CAST(current_database() AS sql_identifier) AS udt_catalog,
2121            CAST(n.nspname AS sql_identifier) AS udt_schema,
2122            CAST(t.typname AS sql_identifier) AS udt_name,
2123            CAST('TYPE USAGE' AS character_data) AS privilege_type, -- sic
2124            CAST(
2125              CASE WHEN
2126                   -- object owner always has grant options
2127                   pg_has_role(grantee.oid, t.typowner, 'USAGE')
2128                   OR t.grantable
2129                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2130
2131     FROM (
2132             SELECT oid, typname, typnamespace, typtype, typowner, (aclexplode(coalesce(typacl, acldefault('T', typowner)))).* FROM pg_type
2133          ) AS t (oid, typname, typnamespace, typtype, typowner, grantor, grantee, prtype, grantable),
2134          pg_namespace n,
2135          pg_authid u_grantor,
2136          (
2137            SELECT oid, rolname FROM pg_authid
2138            UNION ALL
2139            SELECT 0::oid, 'PUBLIC'
2140          ) AS grantee (oid, rolname)
2141
2142     WHERE t.typnamespace = n.oid
2143           AND t.typtype = 'c'
2144           AND t.grantee = grantee.oid
2145           AND t.grantor = u_grantor.oid
2146           AND t.prtype IN ('USAGE')
2147           AND (pg_has_role(u_grantor.oid, 'USAGE')
2148                OR pg_has_role(grantee.oid, 'USAGE')
2149                OR grantee.rolname = 'PUBLIC');
2150
2151 GRANT SELECT ON udt_privileges TO PUBLIC;
2152
2153
2154 /*
2155  * 5.46
2156  * ROLE_UDT_GRANTS view
2157  */
2158
2159 CREATE VIEW role_udt_grants AS
2160     SELECT grantor,
2161            grantee,
2162            udt_catalog,
2163            udt_schema,
2164            udt_name,
2165            privilege_type,
2166            is_grantable
2167     FROM udt_privileges
2168     WHERE grantor IN (SELECT role_name FROM enabled_roles)
2169           OR grantee IN (SELECT role_name FROM enabled_roles);
2170
2171 GRANT SELECT ON role_udt_grants TO PUBLIC;
2172
2173
2174 /*
2175  * 5.74
2176  * USAGE_PRIVILEGES view
2177  */
2178
2179 CREATE VIEW usage_privileges AS
2180
2181     /* collations */
2182     -- Collations have no real privileges, so we represent all collations with implicit usage privilege here.
2183     SELECT CAST(u.rolname AS sql_identifier) AS grantor,
2184            CAST('PUBLIC' AS sql_identifier) AS grantee,
2185            CAST(current_database() AS sql_identifier) AS object_catalog,
2186            CAST(n.nspname AS sql_identifier) AS object_schema,
2187            CAST(c.collname AS sql_identifier) AS object_name,
2188            CAST('COLLATION' AS character_data) AS object_type,
2189            CAST('USAGE' AS character_data) AS privilege_type,
2190            CAST('NO' AS yes_or_no) AS is_grantable
2191
2192     FROM pg_authid u,
2193          pg_namespace n,
2194          pg_collation c
2195
2196     WHERE u.oid = c.collowner
2197           AND c.collnamespace = n.oid
2198           AND collencoding IN (-1, (SELECT encoding FROM pg_database WHERE datname = current_database()))
2199
2200     UNION ALL
2201
2202     /* domains */
2203     SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2204            CAST(grantee.rolname AS sql_identifier) AS grantee,
2205            CAST(current_database() AS sql_identifier) AS object_catalog,
2206            CAST(n.nspname AS sql_identifier) AS object_schema,
2207            CAST(t.typname AS sql_identifier) AS object_name,
2208            CAST('DOMAIN' AS character_data) AS object_type,
2209            CAST('USAGE' AS character_data) AS privilege_type,
2210            CAST(
2211              CASE WHEN
2212                   -- object owner always has grant options
2213                   pg_has_role(grantee.oid, t.typowner, 'USAGE')
2214                   OR t.grantable
2215                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2216
2217     FROM (
2218             SELECT oid, typname, typnamespace, typtype, typowner, (aclexplode(coalesce(typacl, acldefault('T', typowner)))).* FROM pg_type
2219          ) AS t (oid, typname, typnamespace, typtype, typowner, grantor, grantee, prtype, grantable),
2220          pg_namespace n,
2221          pg_authid u_grantor,
2222          (
2223            SELECT oid, rolname FROM pg_authid
2224            UNION ALL
2225            SELECT 0::oid, 'PUBLIC'
2226          ) AS grantee (oid, rolname)
2227
2228     WHERE t.typnamespace = n.oid
2229           AND t.typtype = 'd'
2230           AND t.grantee = grantee.oid
2231           AND t.grantor = u_grantor.oid
2232           AND t.prtype IN ('USAGE')
2233           AND (pg_has_role(u_grantor.oid, 'USAGE')
2234                OR pg_has_role(grantee.oid, 'USAGE')
2235                OR grantee.rolname = 'PUBLIC')
2236
2237     UNION ALL
2238
2239     /* foreign-data wrappers */
2240     SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2241            CAST(grantee.rolname AS sql_identifier) AS grantee,
2242            CAST(current_database() AS sql_identifier) AS object_catalog,
2243            CAST('' AS sql_identifier) AS object_schema,
2244            CAST(fdw.fdwname AS sql_identifier) AS object_name,
2245            CAST('FOREIGN DATA WRAPPER' AS character_data) AS object_type,
2246            CAST('USAGE' AS character_data) AS privilege_type,
2247            CAST(
2248              CASE WHEN
2249                   -- object owner always has grant options
2250                   pg_has_role(grantee.oid, fdw.fdwowner, 'USAGE')
2251                   OR fdw.grantable
2252                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2253
2254     FROM (
2255             SELECT fdwname, fdwowner, (aclexplode(coalesce(fdwacl, acldefault('F', fdwowner)))).* FROM pg_foreign_data_wrapper
2256          ) AS fdw (fdwname, fdwowner, grantor, grantee, prtype, grantable),
2257          pg_authid u_grantor,
2258          (
2259            SELECT oid, rolname FROM pg_authid
2260            UNION ALL
2261            SELECT 0::oid, 'PUBLIC'
2262          ) AS grantee (oid, rolname)
2263
2264     WHERE u_grantor.oid = fdw.grantor
2265           AND grantee.oid = fdw.grantee
2266           AND fdw.prtype IN ('USAGE')
2267           AND (pg_has_role(u_grantor.oid, 'USAGE')
2268                OR pg_has_role(grantee.oid, 'USAGE')
2269                OR grantee.rolname = 'PUBLIC')
2270
2271     UNION ALL
2272
2273     /* foreign servers */
2274     SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2275            CAST(grantee.rolname AS sql_identifier) AS grantee,
2276            CAST(current_database() AS sql_identifier) AS object_catalog,
2277            CAST('' AS sql_identifier) AS object_schema,
2278            CAST(srv.srvname AS sql_identifier) AS object_name,
2279            CAST('FOREIGN SERVER' AS character_data) AS object_type,
2280            CAST('USAGE' AS character_data) AS privilege_type,
2281            CAST(
2282              CASE WHEN
2283                   -- object owner always has grant options
2284                   pg_has_role(grantee.oid, srv.srvowner, 'USAGE')
2285                   OR srv.grantable
2286                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2287
2288     FROM (
2289             SELECT srvname, srvowner, (aclexplode(coalesce(srvacl, acldefault('S', srvowner)))).* FROM pg_foreign_server
2290          ) AS srv (srvname, srvowner, grantor, grantee, prtype, grantable),
2291          pg_authid u_grantor,
2292          (
2293            SELECT oid, rolname FROM pg_authid
2294            UNION ALL
2295            SELECT 0::oid, 'PUBLIC'
2296          ) AS grantee (oid, rolname)
2297
2298     WHERE u_grantor.oid = srv.grantor
2299           AND grantee.oid = srv.grantee
2300           AND srv.prtype IN ('USAGE')
2301           AND (pg_has_role(u_grantor.oid, 'USAGE')
2302                OR pg_has_role(grantee.oid, 'USAGE')
2303                OR grantee.rolname = 'PUBLIC')
2304
2305     UNION ALL
2306
2307     /* sequences */
2308     SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2309            CAST(grantee.rolname AS sql_identifier) AS grantee,
2310            CAST(current_database() AS sql_identifier) AS object_catalog,
2311            CAST(n.nspname AS sql_identifier) AS object_schema,
2312            CAST(c.relname AS sql_identifier) AS object_name,
2313            CAST('SEQUENCE' AS character_data) AS object_type,
2314            CAST('USAGE' AS character_data) AS privilege_type,
2315            CAST(
2316              CASE WHEN
2317                   -- object owner always has grant options
2318                   pg_has_role(grantee.oid, c.relowner, 'USAGE')
2319                   OR c.grantable
2320                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2321
2322     FROM (
2323             SELECT oid, relname, relnamespace, relkind, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).* FROM pg_class
2324          ) AS c (oid, relname, relnamespace, relkind, relowner, grantor, grantee, prtype, grantable),
2325          pg_namespace n,
2326          pg_authid u_grantor,
2327          (
2328            SELECT oid, rolname FROM pg_authid
2329            UNION ALL
2330            SELECT 0::oid, 'PUBLIC'
2331          ) AS grantee (oid, rolname)
2332
2333     WHERE c.relnamespace = n.oid
2334           AND c.relkind = 'S'
2335           AND c.grantee = grantee.oid
2336           AND c.grantor = u_grantor.oid
2337           AND c.prtype IN ('USAGE')
2338           AND (pg_has_role(u_grantor.oid, 'USAGE')
2339                OR pg_has_role(grantee.oid, 'USAGE')
2340                OR grantee.rolname = 'PUBLIC');
2341
2342 GRANT SELECT ON usage_privileges TO PUBLIC;
2343
2344
2345 /*
2346  * 5.45
2347  * ROLE_USAGE_GRANTS view
2348  */
2349
2350 CREATE VIEW role_usage_grants AS
2351     SELECT grantor,
2352            grantee,
2353            object_catalog,
2354            object_schema,
2355            object_name,
2356            object_type,
2357            privilege_type,
2358            is_grantable
2359     FROM usage_privileges
2360     WHERE grantor IN (SELECT role_name FROM enabled_roles)
2361           OR grantee IN (SELECT role_name FROM enabled_roles);
2362
2363 GRANT SELECT ON role_usage_grants TO PUBLIC;
2364
2365
2366 /*
2367  * 5.75
2368  * USER_DEFINED_TYPES view
2369  */
2370
2371 CREATE VIEW user_defined_types AS
2372     SELECT CAST(current_database() AS sql_identifier) AS user_defined_type_catalog,
2373            CAST(n.nspname AS sql_identifier) AS user_defined_type_schema,
2374            CAST(c.relname AS sql_identifier) AS user_defined_type_name,
2375            CAST('STRUCTURED' AS character_data) AS user_defined_type_category,
2376            CAST('YES' AS yes_or_no) AS is_instantiable,
2377            CAST(null AS yes_or_no) AS is_final,
2378            CAST(null AS character_data) AS ordering_form,
2379            CAST(null AS character_data) AS ordering_category,
2380            CAST(null AS sql_identifier) AS ordering_routine_catalog,
2381            CAST(null AS sql_identifier) AS ordering_routine_schema,
2382            CAST(null AS sql_identifier) AS ordering_routine_name,
2383            CAST(null AS character_data) AS reference_type,
2384            CAST(null AS character_data) AS data_type,
2385            CAST(null AS cardinal_number) AS character_maximum_length,
2386            CAST(null AS cardinal_number) AS character_octet_length,
2387            CAST(null AS sql_identifier) AS character_set_catalog,
2388            CAST(null AS sql_identifier) AS character_set_schema,
2389            CAST(null AS sql_identifier) AS character_set_name,
2390            CAST(null AS sql_identifier) AS collation_catalog,
2391            CAST(null AS sql_identifier) AS collation_schema,
2392            CAST(null AS sql_identifier) AS collation_name,
2393            CAST(null AS cardinal_number) AS numeric_precision,
2394            CAST(null AS cardinal_number) AS numeric_precision_radix,
2395            CAST(null AS cardinal_number) AS numeric_scale,
2396            CAST(null AS cardinal_number) AS datetime_precision,
2397            CAST(null AS character_data) AS interval_type,
2398            CAST(null AS cardinal_number) AS interval_precision,
2399            CAST(null AS sql_identifier) AS source_dtd_identifier,
2400            CAST(null AS sql_identifier) AS ref_dtd_identifier
2401
2402     FROM pg_namespace n, pg_class c, pg_type t
2403
2404     WHERE n.oid = c.relnamespace
2405           AND t.typrelid = c.oid
2406           AND c.relkind = 'c'
2407           AND (pg_has_role(t.typowner, 'USAGE')
2408                OR has_type_privilege(t.oid, 'USAGE'));
2409
2410 GRANT SELECT ON user_defined_types TO PUBLIC;
2411
2412
2413 /*
2414  * 5.76
2415  * VIEW_COLUMN_USAGE
2416  */
2417
2418 CREATE VIEW view_column_usage AS
2419     SELECT DISTINCT
2420            CAST(current_database() AS sql_identifier) AS view_catalog,
2421            CAST(nv.nspname AS sql_identifier) AS view_schema,
2422            CAST(v.relname AS sql_identifier) AS view_name,
2423            CAST(current_database() AS sql_identifier) AS table_catalog,
2424            CAST(nt.nspname AS sql_identifier) AS table_schema,
2425            CAST(t.relname AS sql_identifier) AS table_name,
2426            CAST(a.attname AS sql_identifier) AS column_name
2427
2428     FROM pg_namespace nv, pg_class v, pg_depend dv,
2429          pg_depend dt, pg_class t, pg_namespace nt,
2430          pg_attribute a
2431
2432     WHERE nv.oid = v.relnamespace
2433           AND v.relkind = 'v'
2434           AND v.oid = dv.refobjid
2435           AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2436           AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2437           AND dv.deptype = 'i'
2438           AND dv.objid = dt.objid
2439           AND dv.refobjid <> dt.refobjid
2440           AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2441           AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2442           AND dt.refobjid = t.oid
2443           AND t.relnamespace = nt.oid
2444           AND t.relkind IN ('r', 'v', 'f')
2445           AND t.oid = a.attrelid
2446           AND dt.refobjsubid = a.attnum
2447           AND pg_has_role(t.relowner, 'USAGE');
2448
2449 GRANT SELECT ON view_column_usage TO PUBLIC;
2450
2451
2452 /*
2453  * 5.77
2454  * VIEW_PERIOD_USAGE
2455  */
2456
2457 -- feature not supported
2458
2459
2460 /*
2461  * 5.78
2462  * VIEW_ROUTINE_USAGE
2463  */
2464
2465 CREATE VIEW view_routine_usage AS
2466     SELECT DISTINCT
2467            CAST(current_database() AS sql_identifier) AS table_catalog,
2468            CAST(nv.nspname AS sql_identifier) AS table_schema,
2469            CAST(v.relname AS sql_identifier) AS table_name,
2470            CAST(current_database() AS sql_identifier) AS specific_catalog,
2471            CAST(np.nspname AS sql_identifier) AS specific_schema,
2472            CAST(p.proname || '_' || CAST(p.oid AS text)  AS sql_identifier) AS specific_name
2473
2474     FROM pg_namespace nv, pg_class v, pg_depend dv,
2475          pg_depend dp, pg_proc p, pg_namespace np
2476
2477     WHERE nv.oid = v.relnamespace
2478           AND v.relkind = 'v'
2479           AND v.oid = dv.refobjid
2480           AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2481           AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2482           AND dv.deptype = 'i'
2483           AND dv.objid = dp.objid
2484           AND dp.classid = 'pg_catalog.pg_rewrite'::regclass
2485           AND dp.refclassid = 'pg_catalog.pg_proc'::regclass
2486           AND dp.refobjid = p.oid
2487           AND p.pronamespace = np.oid
2488           AND pg_has_role(p.proowner, 'USAGE');
2489
2490 GRANT SELECT ON view_routine_usage TO PUBLIC;
2491
2492
2493 /*
2494  * 5.79
2495  * VIEW_TABLE_USAGE
2496  */
2497
2498 CREATE VIEW view_table_usage AS
2499     SELECT DISTINCT
2500            CAST(current_database() AS sql_identifier) AS view_catalog,
2501            CAST(nv.nspname AS sql_identifier) AS view_schema,
2502            CAST(v.relname AS sql_identifier) AS view_name,
2503            CAST(current_database() AS sql_identifier) AS table_catalog,
2504            CAST(nt.nspname AS sql_identifier) AS table_schema,
2505            CAST(t.relname AS sql_identifier) AS table_name
2506
2507     FROM pg_namespace nv, pg_class v, pg_depend dv,
2508          pg_depend dt, pg_class t, pg_namespace nt
2509
2510     WHERE nv.oid = v.relnamespace
2511           AND v.relkind = 'v'
2512           AND v.oid = dv.refobjid
2513           AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2514           AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2515           AND dv.deptype = 'i'
2516           AND dv.objid = dt.objid
2517           AND dv.refobjid <> dt.refobjid
2518           AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2519           AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2520           AND dt.refobjid = t.oid
2521           AND t.relnamespace = nt.oid
2522           AND t.relkind IN ('r', 'v', 'f')
2523           AND pg_has_role(t.relowner, 'USAGE');
2524
2525 GRANT SELECT ON view_table_usage TO PUBLIC;
2526
2527
2528 /*
2529  * 5.80
2530  * VIEWS view
2531  */
2532
2533 CREATE VIEW views AS
2534     SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
2535            CAST(nc.nspname AS sql_identifier) AS table_schema,
2536            CAST(c.relname AS sql_identifier) AS table_name,
2537
2538            CAST(
2539              CASE WHEN pg_has_role(c.relowner, 'USAGE')
2540                   THEN pg_get_viewdef(c.oid)
2541                   ELSE null END
2542              AS character_data) AS view_definition,
2543
2544            CAST(
2545              CASE WHEN 'check_option=cascaded' = ANY (c.reloptions)
2546                   THEN 'CASCADED'
2547                   WHEN 'check_option=local' = ANY (c.reloptions)
2548                   THEN 'LOCAL'
2549                   ELSE 'NONE' END
2550              AS character_data) AS check_option,
2551
2552            CAST(
2553              -- (1 << CMD_UPDATE) + (1 << CMD_DELETE)
2554              CASE WHEN pg_relation_is_updatable(c.oid, false) & 20 = 20
2555                   THEN 'YES' ELSE 'NO' END
2556              AS yes_or_no) AS is_updatable,
2557
2558            CAST(
2559              -- 1 << CMD_INSERT
2560              CASE WHEN pg_relation_is_updatable(c.oid, false) & 8 = 8
2561                   THEN 'YES' ELSE 'NO' END
2562              AS yes_or_no) AS is_insertable_into,
2563
2564            CAST(
2565              -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_UPDATE
2566              CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 81 = 81)
2567                   THEN 'YES' ELSE 'NO' END
2568            AS yes_or_no) AS is_trigger_updatable,
2569
2570            CAST(
2571              -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_DELETE
2572              CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 73 = 73)
2573                   THEN 'YES' ELSE 'NO' END
2574            AS yes_or_no) AS is_trigger_deletable,
2575
2576            CAST(
2577              -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_INSERT
2578              CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 69 = 69)
2579                   THEN 'YES' ELSE 'NO' END
2580            AS yes_or_no) AS is_trigger_insertable_into
2581
2582     FROM pg_namespace nc, pg_class c
2583
2584     WHERE c.relnamespace = nc.oid
2585           AND c.relkind = 'v'
2586           AND (NOT pg_is_other_temp_schema(nc.oid))
2587           AND (pg_has_role(c.relowner, 'USAGE')
2588                OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
2589                OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES') );
2590
2591 GRANT SELECT ON views TO PUBLIC;
2592
2593
2594 -- The following views have dependencies that force them to appear out of order.
2595
2596 /*
2597  * 5.25
2598  * DATA_TYPE_PRIVILEGES view
2599  */
2600
2601 CREATE VIEW data_type_privileges AS
2602     SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2603            CAST(x.objschema AS sql_identifier) AS object_schema,
2604            CAST(x.objname AS sql_identifier) AS object_name,
2605            CAST(x.objtype AS character_data) AS object_type,
2606            CAST(x.objdtdid AS sql_identifier) AS dtd_identifier
2607
2608     FROM
2609       (
2610         SELECT udt_schema, udt_name, 'USER-DEFINED TYPE'::text, dtd_identifier FROM attributes
2611         UNION ALL
2612         SELECT table_schema, table_name, 'TABLE'::text, dtd_identifier FROM columns
2613         UNION ALL
2614         SELECT domain_schema, domain_name, 'DOMAIN'::text, dtd_identifier FROM domains
2615         UNION ALL
2616         SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM parameters
2617         UNION ALL
2618         SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM routines
2619       ) AS x (objschema, objname, objtype, objdtdid);
2620
2621 GRANT SELECT ON data_type_privileges TO PUBLIC;
2622
2623
2624 /*
2625  * 5.30
2626  * ELEMENT_TYPES view
2627  */
2628
2629 CREATE VIEW element_types AS
2630     SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2631            CAST(n.nspname AS sql_identifier) AS object_schema,
2632            CAST(x.objname AS sql_identifier) AS object_name,
2633            CAST(x.objtype AS character_data) AS object_type,
2634            CAST(x.objdtdid AS sql_identifier) AS collection_type_identifier,
2635            CAST(
2636              CASE WHEN nbt.nspname = 'pg_catalog' THEN format_type(bt.oid, null)
2637                   ELSE 'USER-DEFINED' END AS character_data) AS data_type,
2638
2639            CAST(null AS cardinal_number) AS character_maximum_length,
2640            CAST(null AS cardinal_number) AS character_octet_length,
2641            CAST(null AS sql_identifier) AS character_set_catalog,
2642            CAST(null AS sql_identifier) AS character_set_schema,
2643            CAST(null AS sql_identifier) AS character_set_name,
2644            CAST(CASE WHEN nco.nspname IS NOT NULL THEN current_database() END AS sql_identifier) AS collation_catalog,
2645            CAST(nco.nspname AS sql_identifier) AS collation_schema,
2646            CAST(co.collname AS sql_identifier) AS collation_name,
2647            CAST(null AS cardinal_number) AS numeric_precision,
2648            CAST(null AS cardinal_number) AS numeric_precision_radix,
2649            CAST(null AS cardinal_number) AS numeric_scale,
2650            CAST(null AS cardinal_number) AS datetime_precision,
2651            CAST(null AS character_data) AS interval_type,
2652            CAST(null AS cardinal_number) AS interval_precision,
2653
2654            CAST(null AS character_data) AS domain_default, -- XXX maybe a bug in the standard
2655
2656            CAST(current_database() AS sql_identifier) AS udt_catalog,
2657            CAST(nbt.nspname AS sql_identifier) AS udt_schema,
2658            CAST(bt.typname AS sql_identifier) AS udt_name,
2659
2660            CAST(null AS sql_identifier) AS scope_catalog,
2661            CAST(null AS sql_identifier) AS scope_schema,
2662            CAST(null AS sql_identifier) AS scope_name,
2663
2664            CAST(null AS cardinal_number) AS maximum_cardinality,
2665            CAST('a' || CAST(x.objdtdid AS text) AS sql_identifier) AS dtd_identifier
2666
2667     FROM pg_namespace n, pg_type at, pg_namespace nbt, pg_type bt,
2668          (
2669            /* columns, attributes */
2670            SELECT c.relnamespace, CAST(c.relname AS sql_identifier),
2671                   CASE WHEN c.relkind = 'c' THEN 'USER-DEFINED TYPE'::text ELSE 'TABLE'::text END,
2672                   a.attnum, a.atttypid, a.attcollation
2673            FROM pg_class c, pg_attribute a
2674            WHERE c.oid = a.attrelid
2675                  AND c.relkind IN ('r', 'v', 'f', 'c')
2676                  AND attnum > 0 AND NOT attisdropped
2677
2678            UNION ALL
2679
2680            /* domains */
2681            SELECT t.typnamespace, CAST(t.typname AS sql_identifier),
2682                   'DOMAIN'::text, 1, t.typbasetype, t.typcollation
2683            FROM pg_type t
2684            WHERE t.typtype = 'd'
2685
2686            UNION ALL
2687
2688            /* parameters */
2689            SELECT pronamespace, CAST(proname || '_' || CAST(oid AS text) AS sql_identifier),
2690                   'ROUTINE'::text, (ss.x).n, (ss.x).x, 0
2691            FROM (SELECT p.pronamespace, p.proname, p.oid,
2692                         _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
2693                  FROM pg_proc p) AS ss
2694
2695            UNION ALL
2696
2697            /* result types */
2698            SELECT p.pronamespace, CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier),
2699                   'ROUTINE'::text, 0, p.prorettype, 0
2700            FROM pg_proc p
2701
2702          ) AS x (objschema, objname, objtype, objdtdid, objtypeid, objcollation)
2703          LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid))
2704            ON x.objcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default')
2705
2706     WHERE n.oid = x.objschema
2707           AND at.oid = x.objtypeid
2708           AND (at.typelem <> 0 AND at.typlen = -1)
2709           AND at.typelem = bt.oid
2710           AND nbt.oid = bt.typnamespace
2711
2712           AND (n.nspname, x.objname, x.objtype, CAST(x.objdtdid AS sql_identifier)) IN
2713               ( SELECT object_schema, object_name, object_type, dtd_identifier
2714                     FROM data_type_privileges );
2715
2716 GRANT SELECT ON element_types TO PUBLIC;
2717
2718
2719 -- SQL/MED views; these use section numbers from part 9 of the standard.
2720 -- (still SQL:2008; there is no SQL:2011 SQL/MED)
2721
2722 /* Base view for foreign table columns */
2723 CREATE VIEW _pg_foreign_table_columns AS
2724     SELECT n.nspname,
2725            c.relname,
2726            a.attname,
2727            a.attfdwoptions
2728     FROM pg_foreign_table t, pg_authid u, pg_namespace n, pg_class c,
2729          pg_attribute a
2730     WHERE u.oid = c.relowner
2731           AND (pg_has_role(c.relowner, 'USAGE')
2732                OR has_column_privilege(c.oid, a.attnum, 'SELECT, INSERT, UPDATE, REFERENCES'))
2733           AND n.oid = c.relnamespace
2734           AND c.oid = t.ftrelid
2735           AND c.relkind = 'f'
2736           AND a.attrelid = c.oid
2737           AND a.attnum > 0;
2738
2739 /*
2740  * 24.2
2741  * COLUMN_OPTIONS view
2742  */
2743 CREATE VIEW column_options AS
2744     SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
2745            c.nspname AS table_schema,
2746            c.relname AS table_name,
2747            c.attname AS column_name,
2748            CAST((pg_options_to_table(c.attfdwoptions)).option_name AS sql_identifier) AS option_name,
2749            CAST((pg_options_to_table(c.attfdwoptions)).option_value AS character_data) AS option_value
2750     FROM _pg_foreign_table_columns c;
2751
2752 GRANT SELECT ON column_options TO PUBLIC;
2753
2754
2755 /* Base view for foreign-data wrappers */
2756 CREATE VIEW _pg_foreign_data_wrappers AS
2757     SELECT w.oid,
2758            w.fdwowner,
2759            w.fdwoptions,
2760            CAST(current_database() AS sql_identifier) AS foreign_data_wrapper_catalog,
2761            CAST(fdwname AS sql_identifier) AS foreign_data_wrapper_name,
2762            CAST(u.rolname AS sql_identifier) AS authorization_identifier,
2763            CAST('c' AS character_data) AS foreign_data_wrapper_language
2764     FROM pg_foreign_data_wrapper w, pg_authid u
2765     WHERE u.oid = w.fdwowner
2766           AND (pg_has_role(fdwowner, 'USAGE')
2767                OR has_foreign_data_wrapper_privilege(w.oid, 'USAGE'));
2768
2769
2770 /*
2771  * 24.4
2772  * FOREIGN_DATA_WRAPPER_OPTIONS view
2773  */
2774 CREATE VIEW foreign_data_wrapper_options AS
2775     SELECT foreign_data_wrapper_catalog,
2776            foreign_data_wrapper_name,
2777            CAST((pg_options_to_table(w.fdwoptions)).option_name AS sql_identifier) AS option_name,
2778            CAST((pg_options_to_table(w.fdwoptions)).option_value AS character_data) AS option_value
2779     FROM _pg_foreign_data_wrappers w;
2780
2781 GRANT SELECT ON foreign_data_wrapper_options TO PUBLIC;
2782
2783
2784 /*
2785  * 24.5
2786  * FOREIGN_DATA_WRAPPERS view
2787  */
2788 CREATE VIEW foreign_data_wrappers AS
2789     SELECT foreign_data_wrapper_catalog,
2790            foreign_data_wrapper_name,
2791            authorization_identifier,
2792            CAST(NULL AS character_data) AS library_name,
2793            foreign_data_wrapper_language
2794     FROM _pg_foreign_data_wrappers w;
2795
2796 GRANT SELECT ON foreign_data_wrappers TO PUBLIC;
2797
2798
2799 /* Base view for foreign servers */
2800 CREATE VIEW _pg_foreign_servers AS
2801     SELECT s.oid,
2802            s.srvoptions,
2803            CAST(current_database() AS sql_identifier) AS foreign_server_catalog,
2804            CAST(srvname AS sql_identifier) AS foreign_server_name,
2805            CAST(current_database() AS sql_identifier) AS foreign_data_wrapper_catalog,
2806            CAST(w.fdwname AS sql_identifier) AS foreign_data_wrapper_name,
2807            CAST(srvtype AS character_data) AS foreign_server_type,
2808            CAST(srvversion AS character_data) AS foreign_server_version,
2809            CAST(u.rolname AS sql_identifier) AS authorization_identifier
2810     FROM pg_foreign_server s, pg_foreign_data_wrapper w, pg_authid u
2811     WHERE w.oid = s.srvfdw
2812           AND u.oid = s.srvowner
2813           AND (pg_has_role(s.srvowner, 'USAGE')
2814                OR has_server_privilege(s.oid, 'USAGE'));
2815
2816
2817 /*
2818  * 24.6
2819  * FOREIGN_SERVER_OPTIONS view
2820  */
2821 CREATE VIEW foreign_server_options AS
2822     SELECT foreign_server_catalog,
2823            foreign_server_name,
2824            CAST((pg_options_to_table(s.srvoptions)).option_name AS sql_identifier) AS option_name,
2825            CAST((pg_options_to_table(s.srvoptions)).option_value AS character_data) AS option_value
2826     FROM _pg_foreign_servers s;
2827
2828 GRANT SELECT ON TABLE foreign_server_options TO PUBLIC;
2829
2830
2831 /*
2832  * 24.7
2833  * FOREIGN_SERVERS view
2834  */
2835 CREATE VIEW foreign_servers AS
2836     SELECT foreign_server_catalog,
2837            foreign_server_name,
2838            foreign_data_wrapper_catalog,
2839            foreign_data_wrapper_name,
2840            foreign_server_type,
2841            foreign_server_version,
2842            authorization_identifier
2843     FROM _pg_foreign_servers;
2844
2845 GRANT SELECT ON foreign_servers TO PUBLIC;
2846
2847
2848 /* Base view for foreign tables */
2849 CREATE VIEW _pg_foreign_tables AS
2850     SELECT
2851            CAST(current_database() AS sql_identifier) AS foreign_table_catalog,
2852            n.nspname AS foreign_table_schema,
2853            c.relname AS foreign_table_name,
2854            t.ftoptions AS ftoptions,
2855            CAST(current_database() AS sql_identifier) AS foreign_server_catalog,
2856            CAST(srvname AS sql_identifier) AS foreign_server_name,
2857            CAST(u.rolname AS sql_identifier) AS authorization_identifier
2858     FROM pg_foreign_table t, pg_foreign_server s, pg_foreign_data_wrapper w,
2859          pg_authid u, pg_namespace n, pg_class c
2860     WHERE w.oid = s.srvfdw
2861           AND u.oid = c.relowner
2862           AND (pg_has_role(c.relowner, 'USAGE')
2863                OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
2864                OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'))
2865           AND n.oid = c.relnamespace
2866           AND c.oid = t.ftrelid
2867           AND c.relkind = 'f'
2868           AND s.oid = t.ftserver;
2869
2870
2871 /*
2872  * 24.8
2873  * FOREIGN_TABLE_OPTIONS view
2874  */
2875 CREATE VIEW foreign_table_options AS
2876     SELECT foreign_table_catalog,
2877            foreign_table_schema,
2878            foreign_table_name,
2879            CAST((pg_options_to_table(t.ftoptions)).option_name AS sql_identifier) AS option_name,
2880            CAST((pg_options_to_table(t.ftoptions)).option_value AS character_data) AS option_value
2881     FROM _pg_foreign_tables t;
2882
2883 GRANT SELECT ON TABLE foreign_table_options TO PUBLIC;
2884
2885
2886 /*
2887  * 24.9
2888  * FOREIGN_TABLES view
2889  */
2890 CREATE VIEW foreign_tables AS
2891     SELECT foreign_table_catalog,
2892            foreign_table_schema,
2893            foreign_table_name,
2894            foreign_server_catalog,
2895            foreign_server_name
2896     FROM _pg_foreign_tables;
2897
2898 GRANT SELECT ON foreign_tables TO PUBLIC;
2899
2900
2901
2902 /* Base view for user mappings */
2903 CREATE VIEW _pg_user_mappings AS
2904     SELECT um.oid,
2905            um.umoptions,
2906            um.umuser,
2907            CAST(COALESCE(u.rolname,'PUBLIC') AS sql_identifier ) AS authorization_identifier,
2908            s.foreign_server_catalog,
2909            s.foreign_server_name,
2910            s.authorization_identifier AS srvowner
2911     FROM pg_user_mapping um LEFT JOIN pg_authid u ON (u.oid = um.umuser),
2912          _pg_foreign_servers s
2913     WHERE s.oid = um.umserver;
2914
2915
2916 /*
2917  * 24.12
2918  * USER_MAPPING_OPTIONS view
2919  */
2920 CREATE VIEW user_mapping_options AS
2921     SELECT authorization_identifier,
2922            foreign_server_catalog,
2923            foreign_server_name,
2924            CAST((pg_options_to_table(um.umoptions)).option_name AS sql_identifier) AS option_name,
2925            CAST(CASE WHEN (umuser <> 0 AND authorization_identifier = current_user)
2926                        OR (umuser = 0 AND pg_has_role(srvowner, 'USAGE'))
2927                        OR (SELECT rolsuper FROM pg_authid WHERE rolname = current_user) THEN (pg_options_to_table(um.umoptions)).option_value
2928                      ELSE NULL END AS character_data) AS option_value
2929     FROM _pg_user_mappings um;
2930
2931 GRANT SELECT ON user_mapping_options TO PUBLIC;
2932
2933
2934 /*
2935  * 24.13
2936  * USER_MAPPINGS view
2937  */
2938 CREATE VIEW user_mappings AS
2939     SELECT authorization_identifier,
2940            foreign_server_catalog,
2941            foreign_server_name
2942     FROM _pg_user_mappings;
2943
2944 GRANT SELECT ON user_mappings TO PUBLIC;