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