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

   1 /*
   2  * SQL Information Schema
   3  * as defined in ISO/IEC 9075-11:2011
   4  *
   5  * Copyright (c) 2003-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_PERIOD_USAGE view
 813  */
 814
 815 -- feature not supported
 816
 817
 818 /*
 819  * 5.24
 820  * CONSTRAINT_TABLE_USAGE view
 821  */
 822
 823 CREATE VIEW constraint_table_usage AS
 824     SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
 825            CAST(nr.nspname AS sql_identifier) AS table_schema,
 826            CAST(r.relname AS sql_identifier) AS table_name,
 827            CAST(current_database() AS sql_identifier) AS constraint_catalog,
 828            CAST(nc.nspname AS sql_identifier) AS constraint_schema,
 829            CAST(c.conname AS sql_identifier) AS constraint_name
 830
 831     FROM pg_constraint c, pg_namespace nc,
 832          pg_class r, pg_namespace nr
 833
 834     WHERE c.connamespace = nc.oid AND r.relnamespace = nr.oid
 835           AND ( (c.contype = 'f' AND c.confrelid = r.oid)
 836              OR (c.contype IN ('p', 'u') AND c.conrelid = r.oid) )
 837           AND r.relkind = 'r'
 838           AND pg_has_role(r.relowner, 'USAGE');
 839
 840 GRANT SELECT ON constraint_table_usage TO PUBLIC;
 841
 842
 843 -- 5.25 DATA_TYPE_PRIVILEGES view appears later.
 844
 845
 846 /*
 847  * 5.26
 848  * DIRECT_SUPERTABLES view
 849  */
 850
 851 -- feature not supported
 852
 853
 854 /*
 855  * 5.27
 856  * DIRECT_SUPERTYPES view
 857  */
 858
 859 -- feature not supported
 860
 861
 862 /*
 863  * 5.28
 864  * DOMAIN_CONSTRAINTS view
 865  */
 866
 867 CREATE VIEW domain_constraints AS
 868     SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
 869            CAST(rs.nspname AS sql_identifier) AS constraint_schema,
 870            CAST(con.conname AS sql_identifier) AS constraint_name,
 871            CAST(current_database() AS sql_identifier) AS domain_catalog,
 872            CAST(n.nspname AS sql_identifier) AS domain_schema,
 873            CAST(t.typname AS sql_identifier) AS domain_name,
 874            CAST(CASE WHEN condeferrable THEN 'YES' ELSE 'NO' END
 875              AS yes_or_no) AS is_deferrable,
 876            CAST(CASE WHEN condeferred THEN 'YES' ELSE 'NO' END
 877              AS yes_or_no) AS initially_deferred
 878     FROM pg_namespace rs, pg_namespace n, pg_constraint con, pg_type t
 879     WHERE rs.oid = con.connamespace
 880           AND n.oid = t.typnamespace
 881           AND t.oid = con.contypid
 882           AND (pg_has_role(t.typowner, 'USAGE')
 883                OR has_type_privilege(t.oid, 'USAGE'));
 884
 885 GRANT SELECT ON domain_constraints TO PUBLIC;
 886
 887
 888 /*
 889  * DOMAIN_UDT_USAGE view
 890  * apparently removed in SQL:2003
 891  */
 892
 893 CREATE VIEW domain_udt_usage AS
 894     SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
 895            CAST(nbt.nspname AS sql_identifier) AS udt_schema,
 896            CAST(bt.typname AS sql_identifier) AS udt_name,
 897            CAST(current_database() AS sql_identifier) AS domain_catalog,
 898            CAST(nt.nspname AS sql_identifier) AS domain_schema,
 899            CAST(t.typname AS sql_identifier) AS domain_name
 900
 901     FROM pg_type t, pg_namespace nt,
 902          pg_type bt, pg_namespace nbt
 903
 904     WHERE t.typnamespace = nt.oid
 905           AND t.typbasetype = bt.oid
 906           AND bt.typnamespace = nbt.oid
 907           AND t.typtype = 'd'
 908           AND pg_has_role(bt.typowner, 'USAGE');
 909
 910 GRANT SELECT ON domain_udt_usage TO PUBLIC;
 911
 912
 913 /*
 914  * 5.29
 915  * DOMAINS view
 916  */
 917
 918 CREATE VIEW domains AS
 919     SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
 920            CAST(nt.nspname AS sql_identifier) AS domain_schema,
 921            CAST(t.typname AS sql_identifier) AS domain_name,
 922
 923            CAST(
 924              CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
 925                   WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
 926                   ELSE 'USER-DEFINED' END
 927              AS character_data)
 928              AS data_type,
 929
 930            CAST(
 931              _pg_char_max_length(t.typbasetype, t.typtypmod)
 932              AS cardinal_number)
 933              AS character_maximum_length,
 934
 935            CAST(
 936              _pg_char_octet_length(t.typbasetype, t.typtypmod)
 937              AS cardinal_number)
 938              AS character_octet_length,
 939
 940            CAST(null AS sql_identifier) AS character_set_catalog,
 941            CAST(null AS sql_identifier) AS character_set_schema,
 942            CAST(null AS sql_identifier) AS character_set_name,
 943
 944            CAST(CASE WHEN nco.nspname IS NOT NULL THEN current_database() END AS sql_identifier) AS collation_catalog,
 945            CAST(nco.nspname AS sql_identifier) AS collation_schema,
 946            CAST(co.collname AS sql_identifier) AS collation_name,
 947
 948            CAST(
 949              _pg_numeric_precision(t.typbasetype, t.typtypmod)
 950              AS cardinal_number)
 951              AS numeric_precision,
 952
 953            CAST(
 954              _pg_numeric_precision_radix(t.typbasetype, t.typtypmod)
 955              AS cardinal_number)
 956              AS numeric_precision_radix,
 957
 958            CAST(
 959              _pg_numeric_scale(t.typbasetype, t.typtypmod)
 960              AS cardinal_number)
 961              AS numeric_scale,
 962
 963            CAST(
 964              _pg_datetime_precision(t.typbasetype, t.typtypmod)
 965              AS cardinal_number)
 966              AS datetime_precision,
 967
 968            CAST(
 969              _pg_interval_type(t.typbasetype, t.typtypmod)
 970              AS character_data)
 971              AS interval_type,
 972            CAST(null AS cardinal_number) AS interval_precision,
 973
 974            CAST(t.typdefault AS character_data) AS domain_default,
 975
 976            CAST(current_database() AS sql_identifier) AS udt_catalog,
 977            CAST(nbt.nspname AS sql_identifier) AS udt_schema,
 978            CAST(bt.typname AS sql_identifier) AS udt_name,
 979
 980            CAST(null AS sql_identifier) AS scope_catalog,
 981            CAST(null AS sql_identifier) AS scope_schema,
 982            CAST(null AS sql_identifier) AS scope_name,
 983
 984            CAST(null AS cardinal_number) AS maximum_cardinality,
 985            CAST(1 AS sql_identifier) AS dtd_identifier
 986
 987     FROM (pg_type t JOIN pg_namespace nt ON t.typnamespace = nt.oid)
 988          JOIN (pg_type bt JOIN pg_namespace nbt ON bt.typnamespace = nbt.oid)
 989            ON (t.typbasetype = bt.oid AND t.typtype = 'd')
 990          LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid))
 991            ON t.typcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default')
 992
 993     WHERE (pg_has_role(t.typowner, 'USAGE')
 994            OR has_type_privilege(t.oid, 'USAGE'));
 995
 996 GRANT SELECT ON domains TO PUBLIC;
 997
 998
 999 -- 5.30 ELEMENT_TYPES view appears later.
1000
1001
1002 /*
1003  * 5.31
1004  * ENABLED_ROLES view
1005  */
1006
1007 CREATE VIEW enabled_roles AS
1008     SELECT CAST(a.rolname AS sql_identifier) AS role_name
1009     FROM pg_authid a
1010     WHERE pg_has_role(a.oid, 'USAGE');
1011
1012 GRANT SELECT ON enabled_roles TO PUBLIC;
1013
1014
1015 /*
1016  * 5.32
1017  * FIELDS view
1018  */
1019
1020 -- feature not supported
1021
1022
1023 /*
1024  * 5.33
1025  * KEY_COLUMN_USAGE view
1026  */
1027
1028 CREATE VIEW key_column_usage AS
1029     SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1030            CAST(nc_nspname AS sql_identifier) AS constraint_schema,
1031            CAST(conname AS sql_identifier) AS constraint_name,
1032            CAST(current_database() AS sql_identifier) AS table_catalog,
1033            CAST(nr_nspname AS sql_identifier) AS table_schema,
1034            CAST(relname AS sql_identifier) AS table_name,
1035            CAST(a.attname AS sql_identifier) AS column_name,
1036            CAST((ss.x).n AS cardinal_number) AS ordinal_position,
1037            CAST(CASE WHEN contype = 'f' THEN
1038                        _pg_index_position(ss.conindid, ss.confkey[(ss.x).n])
1039                      ELSE NULL
1040                 END AS cardinal_number)
1041              AS position_in_unique_constraint
1042     FROM pg_attribute a,
1043          (SELECT r.oid AS roid, r.relname, r.relowner,
1044                  nc.nspname AS nc_nspname, nr.nspname AS nr_nspname,
1045                  c.oid AS coid, c.conname, c.contype, c.conindid,
1046                  c.confkey, c.confrelid,
1047                  _pg_expandarray(c.conkey) AS x
1048           FROM pg_namespace nr, pg_class r, pg_namespace nc,
1049                pg_constraint c
1050           WHERE nr.oid = r.relnamespace
1051                 AND r.oid = c.conrelid
1052                 AND nc.oid = c.connamespace
1053                 AND c.contype IN ('p', 'u', 'f')
1054                 AND r.relkind = 'r'
1055                 AND (NOT pg_is_other_temp_schema(nr.oid)) ) AS ss
1056     WHERE ss.roid = a.attrelid
1057           AND a.attnum = (ss.x).x
1058           AND NOT a.attisdropped
1059           AND (pg_has_role(relowner, 'USAGE')
1060                OR has_column_privilege(roid, a.attnum,
1061                                        'SELECT, INSERT, UPDATE, REFERENCES'));
1062
1063 GRANT SELECT ON key_column_usage TO PUBLIC;
1064
1065
1066 /*
1067  * 5.34
1068  * KEY_PERIOD_USAGE view
1069  */
1070
1071 -- feature not supported
1072
1073
1074 /*
1075  * 5.35
1076  * METHOD_SPECIFICATION_PARAMETERS view
1077  */
1078
1079 -- feature not supported
1080
1081
1082 /*
1083  * 5.36
1084  * METHOD_SPECIFICATIONS view
1085  */
1086
1087 -- feature not supported
1088
1089
1090 /*
1091  * 5.37
1092  * PARAMETERS view
1093  */
1094
1095 CREATE VIEW parameters AS
1096     SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
1097            CAST(n_nspname AS sql_identifier) AS specific_schema,
1098            CAST(proname || '_' || CAST(p_oid AS text) AS sql_identifier) AS specific_name,
1099            CAST((ss.x).n AS cardinal_number) AS ordinal_position,
1100            CAST(
1101              CASE WHEN proargmodes IS NULL THEN 'IN'
1102                 WHEN proargmodes[(ss.x).n] = 'i' THEN 'IN'
1103                 WHEN proargmodes[(ss.x).n] = 'o' THEN 'OUT'
1104                 WHEN proargmodes[(ss.x).n] = 'b' THEN 'INOUT'
1105                 WHEN proargmodes[(ss.x).n] = 'v' THEN 'IN'
1106                 WHEN proargmodes[(ss.x).n] = 't' THEN 'OUT'
1107              END AS character_data) AS parameter_mode,
1108            CAST('NO' AS yes_or_no) AS is_result,
1109            CAST('NO' AS yes_or_no) AS as_locator,
1110            CAST(NULLIF(proargnames[(ss.x).n], '') AS sql_identifier) AS parameter_name,
1111            CAST(
1112              CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
1113                   WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
1114                   ELSE 'USER-DEFINED' END AS character_data)
1115              AS data_type,
1116            CAST(null AS cardinal_number) AS character_maximum_length,
1117            CAST(null AS cardinal_number) AS character_octet_length,
1118            CAST(null AS sql_identifier) AS character_set_catalog,
1119            CAST(null AS sql_identifier) AS character_set_schema,
1120            CAST(null AS sql_identifier) AS character_set_name,
1121            CAST(null AS sql_identifier) AS collation_catalog,
1122            CAST(null AS sql_identifier) AS collation_schema,
1123            CAST(null AS sql_identifier) AS collation_name,
1124            CAST(null AS cardinal_number) AS numeric_precision,
1125            CAST(null AS cardinal_number) AS numeric_precision_radix,
1126            CAST(null AS cardinal_number) AS numeric_scale,
1127            CAST(null AS cardinal_number) AS datetime_precision,
1128            CAST(null AS character_data) AS interval_type,
1129            CAST(null AS cardinal_number) AS interval_precision,
1130            CAST(current_database() AS sql_identifier) AS udt_catalog,
1131            CAST(nt.nspname AS sql_identifier) AS udt_schema,
1132            CAST(t.typname AS sql_identifier) AS udt_name,
1133            CAST(null AS sql_identifier) AS scope_catalog,
1134            CAST(null AS sql_identifier) AS scope_schema,
1135            CAST(null AS sql_identifier) AS scope_name,
1136            CAST(null AS cardinal_number) AS maximum_cardinality,
1137            CAST((ss.x).n AS sql_identifier) AS dtd_identifier
1138
1139     FROM pg_type t, pg_namespace nt,
1140          (SELECT n.nspname AS n_nspname, p.proname, p.oid AS p_oid,
1141                  p.proargnames, p.proargmodes,
1142                  _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
1143           FROM pg_namespace n, pg_proc p
1144           WHERE n.oid = p.pronamespace
1145                 AND (pg_has_role(p.proowner, 'USAGE') OR
1146                      has_function_privilege(p.oid, 'EXECUTE'))) AS ss
1147     WHERE t.oid = (ss.x).x AND t.typnamespace = nt.oid;
1148
1149 GRANT SELECT ON parameters TO PUBLIC;
1150
1151
1152 /*
1153  * 5.38
1154  * PERIODS view
1155  */
1156
1157 -- feature not supported
1158
1159
1160 /*
1161  * 5.39
1162  * REFERENCED_TYPES view
1163  */
1164
1165 -- feature not supported
1166
1167
1168 /*
1169  * 5.40
1170  * REFERENTIAL_CONSTRAINTS view
1171  */
1172
1173 CREATE VIEW referential_constraints AS
1174     SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1175            CAST(ncon.nspname AS sql_identifier) AS constraint_schema,
1176            CAST(con.conname AS sql_identifier) AS constraint_name,
1177            CAST(
1178              CASE WHEN npkc.nspname IS NULL THEN NULL
1179                   ELSE current_database() END
1180              AS sql_identifier) AS unique_constraint_catalog,
1181            CAST(npkc.nspname AS sql_identifier) AS unique_constraint_schema,
1182            CAST(pkc.conname AS sql_identifier) AS unique_constraint_name,
1183
1184            CAST(
1185              CASE con.confmatchtype WHEN 'f' THEN 'FULL'
1186                                     WHEN 'p' THEN 'PARTIAL'
1187                                     WHEN 's' THEN 'NONE' END
1188              AS character_data) AS match_option,
1189
1190            CAST(
1191              CASE con.confupdtype WHEN 'c' THEN 'CASCADE'
1192                                   WHEN 'n' THEN 'SET NULL'
1193                                   WHEN 'd' THEN 'SET DEFAULT'
1194                                   WHEN 'r' THEN 'RESTRICT'
1195                                   WHEN 'a' THEN 'NO ACTION' END
1196              AS character_data) AS update_rule,
1197
1198            CAST(
1199              CASE con.confdeltype WHEN 'c' THEN 'CASCADE'
1200                                   WHEN 'n' THEN 'SET NULL'
1201                                   WHEN 'd' THEN 'SET DEFAULT'
1202                                   WHEN 'r' THEN 'RESTRICT'
1203                                   WHEN 'a' THEN 'NO ACTION' END
1204              AS character_data) AS delete_rule
1205
1206     FROM (pg_namespace ncon
1207           INNER JOIN pg_constraint con ON ncon.oid = con.connamespace
1208           INNER JOIN pg_class c ON con.conrelid = c.oid AND con.contype = 'f')
1209          LEFT JOIN pg_depend d1  -- find constraint's dependency on an index
1210           ON d1.objid = con.oid AND d1.classid = 'pg_constraint'::regclass
1211              AND d1.refclassid = 'pg_class'::regclass AND d1.refobjsubid = 0
1212          LEFT JOIN pg_depend d2  -- find pkey/unique constraint for that index
1213           ON d2.refclassid = 'pg_constraint'::regclass
1214              AND d2.classid = 'pg_class'::regclass
1215              AND d2.objid = d1.refobjid AND d2.objsubid = 0
1216              AND d2.deptype = 'i'
1217          LEFT JOIN pg_constraint pkc ON pkc.oid = d2.refobjid
1218             AND pkc.contype IN ('p', 'u')
1219             AND pkc.conrelid = con.confrelid
1220          LEFT JOIN pg_namespace npkc ON pkc.connamespace = npkc.oid
1221
1222     WHERE pg_has_role(c.relowner, 'USAGE')
1223           -- SELECT privilege omitted, per SQL standard
1224           OR has_table_privilege(c.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1225           OR has_any_column_privilege(c.oid, 'INSERT, UPDATE, REFERENCES') ;
1226
1227 GRANT SELECT ON referential_constraints TO PUBLIC;
1228
1229
1230 /*
1231  * 5.41
1232  * ROLE_COLUMN_GRANTS view
1233  */
1234
1235 CREATE VIEW role_column_grants AS
1236     SELECT grantor,
1237            grantee,
1238            table_catalog,
1239            table_schema,
1240            table_name,
1241            column_name,
1242            privilege_type,
1243            is_grantable
1244     FROM column_privileges
1245     WHERE grantor IN (SELECT role_name FROM enabled_roles)
1246           OR grantee IN (SELECT role_name FROM enabled_roles);
1247
1248 GRANT SELECT ON role_column_grants TO PUBLIC;
1249
1250
1251 -- 5.42 ROLE_ROUTINE_GRANTS view is based on 5.49 ROUTINE_PRIVILEGES and is defined there instead.
1252
1253
1254 -- 5.43 ROLE_TABLE_GRANTS view is based on 5.62 TABLE_PRIVILEGES and is defined there instead.
1255
1256
1257 /*
1258  * 5.44
1259  * ROLE_TABLE_METHOD_GRANTS view
1260  */
1261
1262 -- feature not supported
1263
1264
1265
1266 -- 5.45 ROLE_USAGE_GRANTS view is based on 5.74 USAGE_PRIVILEGES and is defined there instead.
1267
1268
1269 -- 5.46 ROLE_UDT_GRANTS view is based on 5.73 UDT_PRIVILEGES and is defined there instead.
1270
1271
1272 /*
1273  * 5.47
1274  * ROUTINE_COLUMN_USAGE view
1275  */
1276
1277 -- not tracked by PostgreSQL
1278
1279
1280 /*
1281  * 5.48
1282  * ROUTINE_PERIOD_USAGE view
1283  */
1284
1285 -- feature not supported
1286
1287
1288 /*
1289  * 5.49
1290  * ROUTINE_PRIVILEGES view
1291  */
1292
1293 CREATE VIEW routine_privileges AS
1294     SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1295            CAST(grantee.rolname AS sql_identifier) AS grantee,
1296            CAST(current_database() AS sql_identifier) AS specific_catalog,
1297            CAST(n.nspname AS sql_identifier) AS specific_schema,
1298            CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1299            CAST(current_database() AS sql_identifier) AS routine_catalog,
1300            CAST(n.nspname AS sql_identifier) AS routine_schema,
1301            CAST(p.proname AS sql_identifier) AS routine_name,
1302            CAST('EXECUTE' AS character_data) AS privilege_type,
1303            CAST(
1304              CASE WHEN
1305                   -- object owner always has grant options
1306                   pg_has_role(grantee.oid, p.proowner, 'USAGE')
1307                   OR p.grantable
1308                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
1309
1310     FROM (
1311             SELECT oid, proname, proowner, pronamespace, (aclexplode(coalesce(proacl, acldefault('f', proowner)))).* FROM pg_proc
1312          ) p (oid, proname, proowner, pronamespace, grantor, grantee, prtype, grantable),
1313          pg_namespace n,
1314          pg_authid u_grantor,
1315          (
1316            SELECT oid, rolname FROM pg_authid
1317            UNION ALL
1318            SELECT 0::oid, 'PUBLIC'
1319          ) AS grantee (oid, rolname)
1320
1321     WHERE p.pronamespace = n.oid
1322           AND grantee.oid = p.grantee
1323           AND u_grantor.oid = p.grantor
1324           AND p.prtype IN ('EXECUTE')
1325           AND (pg_has_role(u_grantor.oid, 'USAGE')
1326                OR pg_has_role(grantee.oid, 'USAGE')
1327                OR grantee.rolname = 'PUBLIC');
1328
1329 GRANT SELECT ON routine_privileges TO PUBLIC;
1330
1331
1332 /*
1333  * 5.42
1334  * ROLE_ROUTINE_GRANTS view
1335  */
1336
1337 CREATE VIEW role_routine_grants AS
1338     SELECT grantor,
1339            grantee,
1340            specific_catalog,
1341            specific_schema,
1342            specific_name,
1343            routine_catalog,
1344            routine_schema,
1345            routine_name,
1346            privilege_type,
1347            is_grantable
1348     FROM routine_privileges
1349     WHERE grantor IN (SELECT role_name FROM enabled_roles)
1350           OR grantee IN (SELECT role_name FROM enabled_roles);
1351
1352 GRANT SELECT ON role_routine_grants TO PUBLIC;
1353
1354
1355 /*
1356  * 5.50
1357  * ROUTINE_ROUTINE_USAGE view
1358  */
1359
1360 -- not tracked by PostgreSQL
1361
1362
1363 /*
1364  * 5.51
1365  * ROUTINE_SEQUENCE_USAGE view
1366  */
1367
1368 -- not tracked by PostgreSQL
1369
1370
1371 /*
1372  * 5.52
1373  * ROUTINE_TABLE_USAGE view
1374  */
1375
1376 -- not tracked by PostgreSQL
1377
1378
1379 /*
1380  * 5.53
1381  * ROUTINES view
1382  */
1383
1384 CREATE VIEW routines AS
1385     SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
1386            CAST(n.nspname AS sql_identifier) AS specific_schema,
1387            CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1388            CAST(current_database() AS sql_identifier) AS routine_catalog,
1389            CAST(n.nspname AS sql_identifier) AS routine_schema,
1390            CAST(p.proname AS sql_identifier) AS routine_name,
1391            CAST('FUNCTION' AS character_data) AS routine_type,
1392            CAST(null AS sql_identifier) AS module_catalog,
1393            CAST(null AS sql_identifier) AS module_schema,
1394            CAST(null AS sql_identifier) AS module_name,
1395            CAST(null AS sql_identifier) AS udt_catalog,
1396            CAST(null AS sql_identifier) AS udt_schema,
1397            CAST(null AS sql_identifier) AS udt_name,
1398
1399            CAST(
1400              CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
1401                   WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
1402                   ELSE 'USER-DEFINED' END AS character_data)
1403              AS data_type,
1404            CAST(null AS cardinal_number) AS character_maximum_length,
1405            CAST(null AS cardinal_number) AS character_octet_length,
1406            CAST(null AS sql_identifier) AS character_set_catalog,
1407            CAST(null AS sql_identifier) AS character_set_schema,
1408            CAST(null AS sql_identifier) AS character_set_name,
1409            CAST(null AS sql_identifier) AS collation_catalog,
1410            CAST(null AS sql_identifier) AS collation_schema,
1411            CAST(null AS sql_identifier) AS collation_name,
1412            CAST(null AS cardinal_number) AS numeric_precision,
1413            CAST(null AS cardinal_number) AS numeric_precision_radix,
1414            CAST(null AS cardinal_number) AS numeric_scale,
1415            CAST(null AS cardinal_number) AS datetime_precision,
1416            CAST(null AS character_data) AS interval_type,
1417            CAST(null AS cardinal_number) AS interval_precision,
1418            CAST(current_database() AS sql_identifier) AS type_udt_catalog,
1419            CAST(nt.nspname AS sql_identifier) AS type_udt_schema,
1420            CAST(t.typname AS sql_identifier) AS type_udt_name,
1421            CAST(null AS sql_identifier) AS scope_catalog,
1422            CAST(null AS sql_identifier) AS scope_schema,
1423            CAST(null AS sql_identifier) AS scope_name,
1424            CAST(null AS cardinal_number) AS maximum_cardinality,
1425            CAST(0 AS sql_identifier) AS dtd_identifier,
1426
1427            CAST(CASE WHEN l.lanname = 'sql' THEN 'SQL' ELSE 'EXTERNAL' END AS character_data)
1428              AS routine_body,
1429            CAST(
1430              CASE WHEN pg_has_role(p.proowner, 'USAGE') THEN p.prosrc ELSE null END
1431              AS character_data) AS routine_definition,
1432            CAST(
1433              CASE WHEN l.lanname = 'c' THEN p.prosrc ELSE null END
1434              AS character_data) AS external_name,
1435            CAST(upper(l.lanname) AS character_data) AS external_language,
1436
1437            CAST('GENERAL' AS character_data) AS parameter_style,
1438            CAST(CASE WHEN p.provolatile = 'i' THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_deterministic,
1439            CAST('MODIFIES' AS character_data) AS sql_data_access,
1440            CAST(CASE WHEN p.proisstrict THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_null_call,
1441            CAST(null AS character_data) AS sql_path,
1442            CAST('YES' AS yes_or_no) AS schema_level_routine,
1443            CAST(0 AS cardinal_number) AS max_dynamic_result_sets,
1444            CAST(null AS yes_or_no) AS is_user_defined_cast,
1445            CAST(null AS yes_or_no) AS is_implicitly_invocable,
1446            CAST(CASE WHEN p.prosecdef THEN 'DEFINER' ELSE 'INVOKER' END AS character_data) AS security_type,
1447            CAST(null AS sql_identifier) AS to_sql_specific_catalog,
1448            CAST(null AS sql_identifier) AS to_sql_specific_schema,
1449            CAST(null AS sql_identifier) AS to_sql_specific_name,
1450            CAST('NO' AS yes_or_no) AS as_locator,
1451            CAST(null AS time_stamp) AS created,
1452            CAST(null AS time_stamp) AS last_altered,
1453            CAST(null AS yes_or_no) AS new_savepoint_level,
1454            CAST('NO' AS yes_or_no) AS is_udt_dependent,
1455
1456            CAST(null AS character_data) AS result_cast_from_data_type,
1457            CAST(null AS yes_or_no) AS result_cast_as_locator,
1458            CAST(null AS cardinal_number) AS result_cast_char_max_length,
1459            CAST(null AS cardinal_number) AS result_cast_char_octet_length,
1460            CAST(null AS sql_identifier) AS result_cast_char_set_catalog,
1461            CAST(null AS sql_identifier) AS result_cast_char_set_schema,
1462            CAST(null AS sql_identifier) AS result_cast_character_set_name,
1463            CAST(null AS sql_identifier) AS result_cast_collation_catalog,
1464            CAST(null AS sql_identifier) AS result_cast_collation_schema,
1465            CAST(null AS sql_identifier) AS result_cast_collation_name,
1466            CAST(null AS cardinal_number) AS result_cast_numeric_precision,
1467            CAST(null AS cardinal_number) AS result_cast_numeric_precision_radix,
1468            CAST(null AS cardinal_number) AS result_cast_numeric_scale,
1469            CAST(null AS cardinal_number) AS result_cast_datetime_precision,
1470            CAST(null AS character_data) AS result_cast_interval_type,
1471            CAST(null AS cardinal_number) AS result_cast_interval_precision,
1472            CAST(null AS sql_identifier) AS result_cast_type_udt_catalog,
1473            CAST(null AS sql_identifier) AS result_cast_type_udt_schema,
1474            CAST(null AS sql_identifier) AS result_cast_type_udt_name,
1475            CAST(null AS sql_identifier) AS result_cast_scope_catalog,
1476            CAST(null AS sql_identifier) AS result_cast_scope_schema,
1477            CAST(null AS sql_identifier) AS result_cast_scope_name,
1478            CAST(null AS cardinal_number) AS result_cast_maximum_cardinality,
1479            CAST(null AS sql_identifier) AS result_cast_dtd_identifier
1480
1481     FROM pg_namespace n, pg_proc p, pg_language l,
1482          pg_type t, pg_namespace nt
1483
1484     WHERE n.oid = p.pronamespace AND p.prolang = l.oid
1485           AND p.prorettype = t.oid AND t.typnamespace = nt.oid
1486           AND (pg_has_role(p.proowner, 'USAGE')
1487                OR has_function_privilege(p.oid, 'EXECUTE'));
1488
1489 GRANT SELECT ON routines TO PUBLIC;
1490
1491
1492 /*
1493  * 5.54
1494  * SCHEMATA view
1495  */
1496
1497 CREATE VIEW schemata AS
1498     SELECT CAST(current_database() AS sql_identifier) AS catalog_name,
1499            CAST(n.nspname AS sql_identifier) AS schema_name,
1500            CAST(u.rolname AS sql_identifier) AS schema_owner,
1501            CAST(null AS sql_identifier) AS default_character_set_catalog,
1502            CAST(null AS sql_identifier) AS default_character_set_schema,
1503            CAST(null AS sql_identifier) AS default_character_set_name,
1504            CAST(null AS character_data) AS sql_path
1505     FROM pg_namespace n, pg_authid u
1506     WHERE n.nspowner = u.oid AND pg_has_role(n.nspowner, 'USAGE');
1507
1508 GRANT SELECT ON schemata TO PUBLIC;
1509
1510
1511 /*
1512  * 5.55
1513  * SEQUENCES view
1514  */
1515
1516 CREATE VIEW sequences AS
1517     SELECT CAST(current_database() AS sql_identifier) AS sequence_catalog,
1518            CAST(nc.nspname AS sql_identifier) AS sequence_schema,
1519            CAST(c.relname AS sql_identifier) AS sequence_name,
1520            CAST('bigint' AS character_data) AS data_type,
1521            CAST(64 AS cardinal_number) AS numeric_precision,
1522            CAST(2 AS cardinal_number) AS numeric_precision_radix,
1523            CAST(0 AS cardinal_number) AS numeric_scale,
1524            -- XXX: The following could be improved if we had LATERAL.
1525            CAST((pg_sequence_parameters(c.oid)).start_value AS character_data) AS start_value,
1526            CAST((pg_sequence_parameters(c.oid)).minimum_value AS character_data) AS minimum_value,
1527            CAST((pg_sequence_parameters(c.oid)).maximum_value AS character_data) AS maximum_value,
1528            CAST((pg_sequence_parameters(c.oid)).increment AS character_data) AS increment,
1529            CAST(CASE WHEN (pg_sequence_parameters(c.oid)).cycle_option THEN 'YES' ELSE 'NO' END AS yes_or_no) AS cycle_option
1530     FROM pg_namespace nc, pg_class c
1531     WHERE c.relnamespace = nc.oid
1532           AND c.relkind = 'S'
1533           AND (NOT pg_is_other_temp_schema(nc.oid))
1534           AND (pg_has_role(c.relowner, 'USAGE')
1535                OR has_sequence_privilege(c.oid, 'SELECT, UPDATE, USAGE') );
1536
1537 GRANT SELECT ON sequences TO PUBLIC;
1538
1539
1540 /*
1541  * 5.56
1542  * SQL_FEATURES table
1543  */
1544
1545 CREATE TABLE sql_features (
1546     feature_id          character_data,
1547     feature_name        character_data,
1548     sub_feature_id      character_data,
1549     sub_feature_name    character_data,
1550     is_supported        yes_or_no,
1551     is_verified_by      character_data,
1552     comments            character_data
1553 ) WITHOUT OIDS;
1554
1555 -- Will be filled with external data by initdb.
1556
1557 GRANT SELECT ON sql_features TO PUBLIC;
1558
1559
1560 /*
1561  * 5.57
1562  * SQL_IMPLEMENTATION_INFO table
1563  */
1564
1565 -- Note: Implementation information items are defined in ISO/IEC 9075-3:2008,
1566 -- clause 9.1.
1567
1568 CREATE TABLE sql_implementation_info (
1569     implementation_info_id      character_data,
1570     implementation_info_name    character_data,
1571     integer_value               cardinal_number,
1572     character_value             character_data,
1573     comments                    character_data
1574 ) WITHOUT OIDS;
1575
1576 INSERT INTO sql_implementation_info VALUES ('10003', 'CATALOG NAME', NULL, 'Y', NULL);
1577 INSERT INTO sql_implementation_info VALUES ('10004', 'COLLATING SEQUENCE', NULL, (SELECT default_collate_name FROM character_sets), NULL);
1578 INSERT INTO sql_implementation_info VALUES ('23',    'CURSOR COMMIT BEHAVIOR', 1, NULL, 'close cursors and retain prepared statements');
1579 INSERT INTO sql_implementation_info VALUES ('2',     'DATA SOURCE NAME', NULL, '', NULL);
1580 INSERT INTO sql_implementation_info VALUES ('17',    'DBMS NAME', NULL, (select trim(trailing ' ' from substring(version() from '^[^0-9]*'))), NULL);
1581 INSERT INTO sql_implementation_info VALUES ('18',    'DBMS VERSION', NULL, '???', NULL); -- filled by initdb
1582 INSERT INTO sql_implementation_info VALUES ('26',    'DEFAULT TRANSACTION ISOLATION', 2, NULL, 'READ COMMITTED; user-settable');
1583 INSERT INTO sql_implementation_info VALUES ('28',    'IDENTIFIER CASE', 3, NULL, 'stored in mixed case - case sensitive');
1584 INSERT INTO sql_implementation_info VALUES ('85',    'NULL COLLATION', 0, NULL, 'nulls higher than non-nulls');
1585 INSERT INTO sql_implementation_info VALUES ('13',    'SERVER NAME', NULL, '', NULL);
1586 INSERT INTO sql_implementation_info VALUES ('94',    'SPECIAL CHARACTERS', NULL, '', 'all non-ASCII characters allowed');
1587 INSERT INTO sql_implementation_info VALUES ('46',    'TRANSACTION CAPABLE', 2, NULL, 'both DML and DDL');
1588
1589 GRANT SELECT ON sql_implementation_info TO PUBLIC;
1590
1591
1592 /*
1593  * SQL_LANGUAGES table
1594  * apparently removed in SQL:2008
1595  */
1596
1597 CREATE TABLE sql_languages (
1598     sql_language_source         character_data,
1599     sql_language_year           character_data,
1600     sql_language_conformance    character_data,
1601     sql_language_integrity      character_data,
1602     sql_language_implementation character_data,
1603     sql_language_binding_style  character_data,
1604     sql_language_programming_language character_data
1605 ) WITHOUT OIDS;
1606
1607 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'DIRECT', NULL);
1608 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
1609 INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'DIRECT', NULL);
1610 INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
1611
1612 GRANT SELECT ON sql_languages TO PUBLIC;
1613
1614
1615 /*
1616  * SQL_PACKAGES table
1617  * removed in SQL:2011
1618  */
1619
1620 CREATE TABLE sql_packages (
1621     feature_id      character_data,
1622     feature_name    character_data,
1623     is_supported    yes_or_no,
1624     is_verified_by  character_data,
1625     comments        character_data
1626 ) WITHOUT OIDS;
1627
1628 INSERT INTO sql_packages VALUES ('PKG000', 'Core', 'NO', NULL, '');
1629 INSERT INTO sql_packages VALUES ('PKG001', 'Enhanced datetime facilities', 'YES', NULL, '');
1630 INSERT INTO sql_packages VALUES ('PKG002', 'Enhanced integrity management', 'NO', NULL, '');
1631 INSERT INTO sql_packages VALUES ('PKG003', 'OLAP facilities', 'NO', NULL, '');
1632 INSERT INTO sql_packages VALUES ('PKG004', 'PSM', 'NO', NULL, 'PL/pgSQL is similar.');
1633 INSERT INTO sql_packages VALUES ('PKG005', 'CLI', 'NO', NULL, 'ODBC is similar.');
1634 INSERT INTO sql_packages VALUES ('PKG006', 'Basic object support', 'NO', NULL, '');
1635 INSERT INTO sql_packages VALUES ('PKG007', 'Enhanced object support', 'NO', NULL, '');
1636 INSERT INTO sql_packages VALUES ('PKG008', 'Active database', 'NO', NULL, '');
1637 INSERT INTO sql_packages VALUES ('PKG010', 'OLAP', 'NO', NULL, 'NO');
1638
1639 GRANT SELECT ON sql_packages TO PUBLIC;
1640
1641
1642 /*
1643  * 5.58
1644  * SQL_PARTS table
1645  */
1646
1647 CREATE TABLE sql_parts (
1648     feature_id      character_data,
1649     feature_name    character_data,
1650     is_supported    yes_or_no,
1651     is_verified_by  character_data,
1652     comments        character_data
1653 ) WITHOUT OIDS;
1654
1655 INSERT INTO sql_parts VALUES ('1', 'Framework (SQL/Framework)', 'NO', NULL, '');
1656 INSERT INTO sql_parts VALUES ('2', 'Foundation (SQL/Foundation)', 'NO', NULL, '');
1657 INSERT INTO sql_parts VALUES ('3', 'Call-Level Interface (SQL/CLI)', 'NO', NULL, '');
1658 INSERT INTO sql_parts VALUES ('4', 'Persistent Stored Modules (SQL/PSM)', 'NO', NULL, '');
1659 INSERT INTO sql_parts VALUES ('9', 'Management of External Data (SQL/MED)', 'NO', NULL, '');
1660 INSERT INTO sql_parts VALUES ('10', 'Object Language Bindings (SQL/OLB)', 'NO', NULL, '');
1661 INSERT INTO sql_parts VALUES ('11', 'Information and Definition Schema (SQL/Schemata)', 'NO', NULL, '');
1662 INSERT INTO sql_parts VALUES ('13', 'Routines and Types Using the Java Programming Language (SQL/JRT)', 'NO', NULL, '');
1663 INSERT INTO sql_parts VALUES ('14', 'XML-Related Specifications (SQL/XML)', 'YES', NULL, '');
1664
1665
1666 /*
1667  * 5.59
1668  * SQL_SIZING table
1669  */
1670
1671 -- Note: Sizing items are defined in ISO/IEC 9075-3:2008, clause 9.2.
1672
1673 CREATE TABLE sql_sizing (
1674     sizing_id       cardinal_number,
1675     sizing_name     character_data,
1676     supported_value cardinal_number,
1677     comments        character_data
1678 ) WITHOUT OIDS;
1679
1680 INSERT INTO sql_sizing VALUES (34,    'MAXIMUM CATALOG NAME LENGTH', 63, NULL);
1681 INSERT INTO sql_sizing VALUES (30,    'MAXIMUM COLUMN NAME LENGTH', 63, NULL);
1682 INSERT INTO sql_sizing VALUES (97,    'MAXIMUM COLUMNS IN GROUP BY', 0, NULL);
1683 INSERT INTO sql_sizing VALUES (99,    'MAXIMUM COLUMNS IN ORDER BY', 0, NULL);
1684 INSERT INTO sql_sizing VALUES (100,   'MAXIMUM COLUMNS IN SELECT', 1664, NULL); -- match MaxTupleAttributeNumber
1685 INSERT INTO sql_sizing VALUES (101,   'MAXIMUM COLUMNS IN TABLE', 1600, NULL); -- match MaxHeapAttributeNumber
1686 INSERT INTO sql_sizing VALUES (1,     'MAXIMUM CONCURRENT ACTIVITIES', 0, NULL);
1687 INSERT INTO sql_sizing VALUES (31,    'MAXIMUM CURSOR NAME LENGTH', 63, NULL);
1688 INSERT INTO sql_sizing VALUES (0,     'MAXIMUM DRIVER CONNECTIONS', NULL, NULL);
1689 INSERT INTO sql_sizing VALUES (10005, 'MAXIMUM IDENTIFIER LENGTH', 63, NULL);
1690 INSERT INTO sql_sizing VALUES (32,    'MAXIMUM SCHEMA NAME LENGTH', 63, NULL);
1691 INSERT INTO sql_sizing VALUES (20000, 'MAXIMUM STATEMENT OCTETS', 0, NULL);
1692 INSERT INTO sql_sizing VALUES (20001, 'MAXIMUM STATEMENT OCTETS DATA', 0, NULL);
1693 INSERT INTO sql_sizing VALUES (20002, 'MAXIMUM STATEMENT OCTETS SCHEMA', 0, NULL);
1694 INSERT INTO sql_sizing VALUES (35,    'MAXIMUM TABLE NAME LENGTH', 63, NULL);
1695 INSERT INTO sql_sizing VALUES (106,   'MAXIMUM TABLES IN SELECT', 0, NULL);
1696 INSERT INTO sql_sizing VALUES (107,   'MAXIMUM USER NAME LENGTH', 63, NULL);
1697 INSERT INTO sql_sizing VALUES (25000, 'MAXIMUM CURRENT DEFAULT TRANSFORM GROUP LENGTH', NULL, NULL);
1698 INSERT INTO sql_sizing VALUES (25001, 'MAXIMUM CURRENT TRANSFORM GROUP LENGTH', NULL, NULL);
1699 INSERT INTO sql_sizing VALUES (25002, 'MAXIMUM CURRENT PATH LENGTH', 0, NULL);
1700 INSERT INTO sql_sizing VALUES (25003, 'MAXIMUM CURRENT ROLE LENGTH', NULL, NULL);
1701 INSERT INTO sql_sizing VALUES (25004, 'MAXIMUM SESSION USER LENGTH', 63, NULL);
1702 INSERT INTO sql_sizing VALUES (25005, 'MAXIMUM SYSTEM USER LENGTH', 63, NULL);
1703
1704 UPDATE sql_sizing
1705     SET supported_value = (SELECT typlen-1 FROM pg_catalog.pg_type WHERE typname = 'name'),
1706         comments = 'Might be less, depending on character set.'
1707     WHERE supported_value = 63;
1708
1709 GRANT SELECT ON sql_sizing TO PUBLIC;
1710
1711
1712 /*
1713  * SQL_SIZING_PROFILES table
1714  * removed in SQL:2011
1715  */
1716
1717 -- The data in this table are defined by various profiles of SQL.
1718 -- Since we don't have any information about such profiles, we provide
1719 -- an empty table.
1720
1721 CREATE TABLE sql_sizing_profiles (
1722     sizing_id       cardinal_number,
1723     sizing_name     character_data,
1724     profile_id      character_data,
1725     required_value  cardinal_number,
1726     comments        character_data
1727 ) WITHOUT OIDS;
1728
1729 GRANT SELECT ON sql_sizing_profiles TO PUBLIC;
1730
1731
1732 /*
1733  * 5.60
1734  * TABLE_CONSTRAINTS view
1735  */
1736
1737 CREATE VIEW table_constraints AS
1738     SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1739            CAST(nc.nspname AS sql_identifier) AS constraint_schema,
1740            CAST(c.conname AS sql_identifier) AS constraint_name,
1741            CAST(current_database() AS sql_identifier) AS table_catalog,
1742            CAST(nr.nspname AS sql_identifier) AS table_schema,
1743            CAST(r.relname AS sql_identifier) AS table_name,
1744            CAST(
1745              CASE c.contype WHEN 'c' THEN 'CHECK'
1746                             WHEN 'f' THEN 'FOREIGN KEY'
1747                             WHEN 'p' THEN 'PRIMARY KEY'
1748                             WHEN 'u' THEN 'UNIQUE' END
1749              AS character_data) AS constraint_type,
1750            CAST(CASE WHEN c.condeferrable THEN 'YES' ELSE 'NO' END AS yes_or_no)
1751              AS is_deferrable,
1752            CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS yes_or_no)
1753              AS initially_deferred
1754
1755     FROM pg_namespace nc,
1756          pg_namespace nr,
1757          pg_constraint c,
1758          pg_class r
1759
1760     WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace
1761           AND c.conrelid = r.oid
1762           AND c.contype NOT IN ('t', 'x')  -- ignore nonstandard constraints
1763           AND r.relkind = 'r'
1764           AND (NOT pg_is_other_temp_schema(nr.oid))
1765           AND (pg_has_role(r.relowner, 'USAGE')
1766                -- SELECT privilege omitted, per SQL standard
1767                OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1768                OR has_any_column_privilege(r.oid, 'INSERT, UPDATE, REFERENCES') )
1769
1770     UNION ALL
1771
1772     -- not-null constraints
1773
1774     SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1775            CAST(nr.nspname AS sql_identifier) AS constraint_schema,
1776            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
1777            CAST(current_database() AS sql_identifier) AS table_catalog,
1778            CAST(nr.nspname AS sql_identifier) AS table_schema,
1779            CAST(r.relname AS sql_identifier) AS table_name,
1780            CAST('CHECK' AS character_data) AS constraint_type,
1781            CAST('NO' AS yes_or_no) AS is_deferrable,
1782            CAST('NO' AS yes_or_no) AS initially_deferred
1783
1784     FROM pg_namespace nr,
1785          pg_class r,
1786          pg_attribute a
1787
1788     WHERE nr.oid = r.relnamespace
1789           AND r.oid = a.attrelid
1790           AND a.attnotnull
1791           AND a.attnum > 0
1792           AND NOT a.attisdropped
1793           AND r.relkind = 'r'
1794           AND (NOT pg_is_other_temp_schema(nr.oid))
1795           AND (pg_has_role(r.relowner, 'USAGE')
1796                -- SELECT privilege omitted, per SQL standard
1797                OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1798                OR has_any_column_privilege(r.oid, 'INSERT, UPDATE, REFERENCES') );
1799
1800 GRANT SELECT ON table_constraints TO PUBLIC;
1801
1802
1803 /*
1804  * 5.61
1805  * TABLE_METHOD_PRIVILEGES view
1806  */
1807
1808 -- feature not supported
1809
1810
1811 /*
1812  * 5.62
1813  * TABLE_PRIVILEGES view
1814  */
1815
1816 CREATE VIEW table_privileges AS
1817     SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1818            CAST(grantee.rolname AS sql_identifier) AS grantee,
1819            CAST(current_database() AS sql_identifier) AS table_catalog,
1820            CAST(nc.nspname AS sql_identifier) AS table_schema,
1821            CAST(c.relname AS sql_identifier) AS table_name,
1822            CAST(c.prtype AS character_data) AS privilege_type,
1823            CAST(
1824              CASE WHEN
1825                   -- object owner always has grant options
1826                   pg_has_role(grantee.oid, c.relowner, 'USAGE')
1827                   OR c.grantable
1828                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable,
1829            CAST(CASE WHEN c.prtype = 'SELECT' THEN 'YES' ELSE 'NO' END AS yes_or_no) AS with_hierarchy
1830
1831     FROM (
1832             SELECT oid, relname, relnamespace, relkind, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).* FROM pg_class
1833          ) AS c (oid, relname, relnamespace, relkind, relowner, grantor, grantee, prtype, grantable),
1834          pg_namespace nc,
1835          pg_authid u_grantor,
1836          (
1837            SELECT oid, rolname FROM pg_authid
1838            UNION ALL
1839            SELECT 0::oid, 'PUBLIC'
1840          ) AS grantee (oid, rolname)
1841
1842     WHERE c.relnamespace = nc.oid
1843           AND c.relkind IN ('r', 'v')
1844           AND c.grantee = grantee.oid
1845           AND c.grantor = u_grantor.oid
1846           AND c.prtype IN ('INSERT', 'SELECT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER')
1847           AND (pg_has_role(u_grantor.oid, 'USAGE')
1848                OR pg_has_role(grantee.oid, 'USAGE')
1849                OR grantee.rolname = 'PUBLIC');
1850
1851 GRANT SELECT ON table_privileges TO PUBLIC;
1852
1853
1854 /*
1855  * 5.43
1856  * ROLE_TABLE_GRANTS view
1857  */
1858
1859 CREATE VIEW role_table_grants AS
1860     SELECT grantor,
1861            grantee,
1862            table_catalog,
1863            table_schema,
1864            table_name,
1865            privilege_type,
1866            is_grantable,
1867            with_hierarchy
1868     FROM table_privileges
1869     WHERE grantor IN (SELECT role_name FROM enabled_roles)
1870           OR grantee IN (SELECT role_name FROM enabled_roles);
1871
1872 GRANT SELECT ON role_table_grants TO PUBLIC;
1873
1874
1875 /*
1876  * 5.63
1877  * TABLES view
1878  */
1879
1880 CREATE VIEW tables AS
1881     SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
1882            CAST(nc.nspname AS sql_identifier) AS table_schema,
1883            CAST(c.relname AS sql_identifier) AS table_name,
1884
1885            CAST(
1886              CASE WHEN nc.oid = pg_my_temp_schema() THEN 'LOCAL TEMPORARY'
1887                   WHEN c.relkind = 'r' THEN 'BASE TABLE'
1888                   WHEN c.relkind = 'v' THEN 'VIEW'
1889                   WHEN c.relkind = 'f' THEN 'FOREIGN TABLE'
1890                   ELSE null END
1891              AS character_data) AS table_type,
1892
1893            CAST(null AS sql_identifier) AS self_referencing_column_name,
1894            CAST(null AS character_data) AS reference_generation,
1895
1896            CAST(CASE WHEN t.typname IS NOT NULL THEN current_database() ELSE null END AS sql_identifier) AS user_defined_type_catalog,
1897            CAST(nt.nspname AS sql_identifier) AS user_defined_type_schema,
1898            CAST(t.typname AS sql_identifier) AS user_defined_type_name,
1899
1900            CAST(CASE WHEN c.relkind = 'r'
1901                           OR (c.relkind = 'v'
1902                               AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '3' AND is_instead))
1903                 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_insertable_into,
1904
1905            CAST(CASE WHEN t.typname IS NOT NULL THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_typed,
1906            CAST(null AS character_data) AS commit_action
1907
1908     FROM pg_namespace nc JOIN pg_class c ON (nc.oid = c.relnamespace)
1909            LEFT JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON (c.reloftype = t.oid)
1910
1911     WHERE c.relkind IN ('r', 'v', 'f')
1912           AND (NOT pg_is_other_temp_schema(nc.oid))
1913           AND (pg_has_role(c.relowner, 'USAGE')
1914                OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1915                OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES') );
1916
1917 GRANT SELECT ON tables TO PUBLIC;
1918
1919
1920 /*
1921  * 5.64
1922  * TRANSFORMS view
1923  */
1924
1925 -- feature not supported
1926
1927
1928 /*
1929  * 5.65
1930  * TRANSLATIONS view
1931  */
1932
1933 -- feature not supported
1934
1935
1936 /*
1937  * 5.66
1938  * TRIGGERED_UPDATE_COLUMNS view
1939  */
1940
1941 CREATE VIEW triggered_update_columns AS
1942     SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1943            CAST(n.nspname AS sql_identifier) AS trigger_schema,
1944            CAST(t.tgname AS sql_identifier) AS trigger_name,
1945            CAST(current_database() AS sql_identifier) AS event_object_catalog,
1946            CAST(n.nspname AS sql_identifier) AS event_object_schema,
1947            CAST(c.relname AS sql_identifier) AS event_object_table,
1948            CAST(a.attname AS sql_identifier) AS event_object_column
1949
1950     FROM pg_namespace n, pg_class c, pg_trigger t,
1951          (SELECT tgoid, (ta0.tgat).x AS tgattnum, (ta0.tgat).n AS tgattpos
1952           FROM (SELECT oid AS tgoid, information_schema._pg_expandarray(tgattr) AS tgat FROM pg_trigger) AS ta0) AS ta,
1953          pg_attribute a
1954
1955     WHERE n.oid = c.relnamespace
1956           AND c.oid = t.tgrelid
1957           AND t.oid = ta.tgoid
1958           AND (a.attrelid, a.attnum) = (t.tgrelid, ta.tgattnum)
1959           AND NOT t.tgisinternal
1960           AND (NOT pg_is_other_temp_schema(n.oid))
1961           AND (pg_has_role(c.relowner, 'USAGE')
1962                -- SELECT privilege omitted, per SQL standard
1963                OR has_column_privilege(c.oid, a.attnum, 'INSERT, UPDATE, REFERENCES') );
1964
1965 GRANT SELECT ON triggered_update_columns TO PUBLIC;
1966
1967
1968 /*
1969  * 5.67
1970  * TRIGGER_COLUMN_USAGE view
1971  */
1972
1973 -- not tracked by PostgreSQL
1974
1975
1976 /*
1977  * 5.68
1978  * TRIGGER_PERIOD_USAGE view
1979  */
1980
1981 -- feature not supported
1982
1983
1984 /*
1985  * 5.69
1986  * TRIGGER_ROUTINE_USAGE view
1987  */
1988
1989 -- not tracked by PostgreSQL
1990
1991
1992 /*
1993  * 5.70
1994  * TRIGGER_SEQUENCE_USAGE view
1995  */
1996
1997 -- not tracked by PostgreSQL
1998
1999
2000 /*
2001  * 5.71
2002  * TRIGGER_TABLE_USAGE view
2003  */
2004
2005 -- not tracked by PostgreSQL
2006
2007
2008 /*
2009  * 5.72
2010  * TRIGGERS view
2011  */
2012
2013 CREATE VIEW triggers AS
2014     SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
2015            CAST(n.nspname AS sql_identifier) AS trigger_schema,
2016            CAST(t.tgname AS sql_identifier) AS trigger_name,
2017            CAST(em.text AS character_data) AS event_manipulation,
2018            CAST(current_database() AS sql_identifier) AS event_object_catalog,
2019            CAST(n.nspname AS sql_identifier) AS event_object_schema,
2020            CAST(c.relname AS sql_identifier) AS event_object_table,
2021            CAST(null AS cardinal_number) AS action_order,
2022            -- XXX strange hacks follow
2023            CAST(
2024              CASE WHEN pg_has_role(c.relowner, 'USAGE')
2025                THEN (SELECT m[1] FROM regexp_matches(pg_get_triggerdef(t.oid), E'.{35,} WHEN \\((.+)\\) EXECUTE PROCEDURE') AS rm(m) LIMIT 1)
2026                ELSE null END
2027              AS character_data) AS action_condition,
2028            CAST(
2029              substring(pg_get_triggerdef(t.oid) from
2030                        position('EXECUTE PROCEDURE' in substring(pg_get_triggerdef(t.oid) from 48)) + 47)
2031              AS character_data) AS action_statement,
2032            CAST(
2033              -- hard-wired reference to TRIGGER_TYPE_ROW
2034              CASE t.tgtype & 1 WHEN 1 THEN 'ROW' ELSE 'STATEMENT' END
2035              AS character_data) AS action_orientation,
2036            CAST(
2037              -- hard-wired refs to TRIGGER_TYPE_BEFORE, TRIGGER_TYPE_INSTEAD
2038              CASE t.tgtype & 66 WHEN 2 THEN 'BEFORE' WHEN 64 THEN 'INSTEAD OF' ELSE 'AFTER' END
2039              AS character_data) AS action_timing,
2040            CAST(null AS sql_identifier) AS action_reference_old_table,
2041            CAST(null AS sql_identifier) AS action_reference_new_table,
2042            CAST(null AS sql_identifier) AS action_reference_old_row,
2043            CAST(null AS sql_identifier) AS action_reference_new_row,
2044            CAST(null AS time_stamp) AS created
2045
2046     FROM pg_namespace n, pg_class c, pg_trigger t,
2047          -- hard-wired refs to TRIGGER_TYPE_INSERT, TRIGGER_TYPE_DELETE,
2048          -- TRIGGER_TYPE_UPDATE; we intentionally omit TRIGGER_TYPE_TRUNCATE
2049          (VALUES (4, 'INSERT'),
2050                  (8, 'DELETE'),
2051                  (16, 'UPDATE')) AS em (num, text)
2052
2053     WHERE n.oid = c.relnamespace
2054           AND c.oid = t.tgrelid
2055           AND t.tgtype & em.num <> 0
2056           AND NOT t.tgisinternal
2057           AND (NOT pg_is_other_temp_schema(n.oid))
2058           AND (pg_has_role(c.relowner, 'USAGE')
2059                -- SELECT privilege omitted, per SQL standard
2060                OR has_table_privilege(c.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
2061                OR has_any_column_privilege(c.oid, 'INSERT, UPDATE, REFERENCES') );
2062
2063 GRANT SELECT ON triggers TO PUBLIC;
2064
2065
2066 /*
2067  * 5.73
2068  * UDT_PRIVILEGES view
2069  */
2070
2071 CREATE VIEW udt_privileges AS
2072     SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2073            CAST(grantee.rolname AS sql_identifier) AS grantee,
2074            CAST(current_database() AS sql_identifier) AS udt_catalog,
2075            CAST(n.nspname AS sql_identifier) AS udt_schema,
2076            CAST(t.typname AS sql_identifier) AS udt_name,
2077            CAST('TYPE USAGE' AS character_data) AS privilege_type, -- sic
2078            CAST(
2079              CASE WHEN
2080                   -- object owner always has grant options
2081                   pg_has_role(grantee.oid, t.typowner, 'USAGE')
2082                   OR t.grantable
2083                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2084
2085     FROM (
2086             SELECT oid, typname, typnamespace, typtype, typowner, (aclexplode(coalesce(typacl, acldefault('T', typowner)))).* FROM pg_type
2087          ) AS t (oid, typname, typnamespace, typtype, typowner, grantor, grantee, prtype, grantable),
2088          pg_namespace n,
2089          pg_authid u_grantor,
2090          (
2091            SELECT oid, rolname FROM pg_authid
2092            UNION ALL
2093            SELECT 0::oid, 'PUBLIC'
2094          ) AS grantee (oid, rolname)
2095
2096     WHERE t.typnamespace = n.oid
2097           AND t.typtype = 'c'
2098           AND t.grantee = grantee.oid
2099           AND t.grantor = u_grantor.oid
2100           AND t.prtype IN ('USAGE')
2101           AND (pg_has_role(u_grantor.oid, 'USAGE')
2102                OR pg_has_role(grantee.oid, 'USAGE')
2103                OR grantee.rolname = 'PUBLIC');
2104
2105 GRANT SELECT ON udt_privileges TO PUBLIC;
2106
2107
2108 /*
2109  * 5.46
2110  * ROLE_UDT_GRANTS view
2111  */
2112
2113 CREATE VIEW role_udt_grants AS
2114     SELECT grantor,
2115            grantee,
2116            udt_catalog,
2117            udt_schema,
2118            udt_name,
2119            privilege_type,
2120            is_grantable
2121     FROM udt_privileges
2122     WHERE grantor IN (SELECT role_name FROM enabled_roles)
2123           OR grantee IN (SELECT role_name FROM enabled_roles);
2124
2125 GRANT SELECT ON role_udt_grants TO PUBLIC;
2126
2127
2128 /*
2129  * 5.74
2130  * USAGE_PRIVILEGES view
2131  */
2132
2133 CREATE VIEW usage_privileges AS
2134
2135     /* collations */
2136     -- Collations have no real privileges, so we represent all collations with implicit usage privilege here.
2137     SELECT CAST(u.rolname AS sql_identifier) AS grantor,
2138            CAST('PUBLIC' AS sql_identifier) AS grantee,
2139            CAST(current_database() AS sql_identifier) AS object_catalog,
2140            CAST(n.nspname AS sql_identifier) AS object_schema,
2141            CAST(c.collname AS sql_identifier) AS object_name,
2142            CAST('COLLATION' AS character_data) AS object_type,
2143            CAST('USAGE' AS character_data) AS privilege_type,
2144            CAST('NO' AS yes_or_no) AS is_grantable
2145
2146     FROM pg_authid u,
2147          pg_namespace n,
2148          pg_collation c
2149
2150     WHERE u.oid = c.collowner
2151           AND c.collnamespace = n.oid
2152           AND collencoding IN (-1, (SELECT encoding FROM pg_database WHERE datname = current_database()))
2153
2154     UNION ALL
2155
2156     /* domains */
2157     SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2158            CAST(grantee.rolname AS sql_identifier) AS grantee,
2159            CAST(current_database() AS sql_identifier) AS object_catalog,
2160            CAST(n.nspname AS sql_identifier) AS object_schema,
2161            CAST(t.typname AS sql_identifier) AS object_name,
2162            CAST('DOMAIN' AS character_data) AS object_type,
2163            CAST('USAGE' AS character_data) AS privilege_type,
2164            CAST(
2165              CASE WHEN
2166                   -- object owner always has grant options
2167                   pg_has_role(grantee.oid, t.typowner, 'USAGE')
2168                   OR t.grantable
2169                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2170
2171     FROM (
2172             SELECT oid, typname, typnamespace, typtype, typowner, (aclexplode(coalesce(typacl, acldefault('T', typowner)))).* FROM pg_type
2173          ) AS t (oid, typname, typnamespace, typtype, typowner, grantor, grantee, prtype, grantable),
2174          pg_namespace n,
2175          pg_authid u_grantor,
2176          (
2177            SELECT oid, rolname FROM pg_authid
2178            UNION ALL
2179            SELECT 0::oid, 'PUBLIC'
2180          ) AS grantee (oid, rolname)
2181
2182     WHERE t.typnamespace = n.oid
2183           AND t.typtype = 'd'
2184           AND t.grantee = grantee.oid
2185           AND t.grantor = u_grantor.oid
2186           AND t.prtype IN ('USAGE')
2187           AND (pg_has_role(u_grantor.oid, 'USAGE')
2188                OR pg_has_role(grantee.oid, 'USAGE')
2189                OR grantee.rolname = 'PUBLIC')
2190
2191     UNION ALL
2192
2193     /* foreign-data wrappers */
2194     SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2195            CAST(grantee.rolname AS sql_identifier) AS grantee,
2196            CAST(current_database() AS sql_identifier) AS object_catalog,
2197            CAST('' AS sql_identifier) AS object_schema,
2198            CAST(fdw.fdwname AS sql_identifier) AS object_name,
2199            CAST('FOREIGN DATA WRAPPER' AS character_data) AS object_type,
2200            CAST('USAGE' AS character_data) AS privilege_type,
2201            CAST(
2202              CASE WHEN
2203                   -- object owner always has grant options
2204                   pg_has_role(grantee.oid, fdw.fdwowner, 'USAGE')
2205                   OR fdw.grantable
2206                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2207
2208     FROM (
2209             SELECT fdwname, fdwowner, (aclexplode(coalesce(fdwacl, acldefault('F', fdwowner)))).* FROM pg_foreign_data_wrapper
2210          ) AS fdw (fdwname, fdwowner, grantor, grantee, prtype, grantable),
2211          pg_authid u_grantor,
2212          (
2213            SELECT oid, rolname FROM pg_authid
2214            UNION ALL
2215            SELECT 0::oid, 'PUBLIC'
2216          ) AS grantee (oid, rolname)
2217
2218     WHERE u_grantor.oid = fdw.grantor
2219           AND grantee.oid = fdw.grantee
2220           AND fdw.prtype IN ('USAGE')
2221           AND (pg_has_role(u_grantor.oid, 'USAGE')
2222                OR pg_has_role(grantee.oid, 'USAGE')
2223                OR grantee.rolname = 'PUBLIC')
2224
2225     UNION ALL
2226
2227     /* foreign servers */
2228     SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2229            CAST(grantee.rolname AS sql_identifier) AS grantee,
2230            CAST(current_database() AS sql_identifier) AS object_catalog,
2231            CAST('' AS sql_identifier) AS object_schema,
2232            CAST(srv.srvname AS sql_identifier) AS object_name,
2233            CAST('FOREIGN SERVER' AS character_data) AS object_type,
2234            CAST('USAGE' AS character_data) AS privilege_type,
2235            CAST(
2236              CASE WHEN
2237                   -- object owner always has grant options
2238                   pg_has_role(grantee.oid, srv.srvowner, 'USAGE')
2239                   OR srv.grantable
2240                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2241
2242     FROM (
2243             SELECT srvname, srvowner, (aclexplode(coalesce(srvacl, acldefault('S', srvowner)))).* FROM pg_foreign_server
2244          ) AS srv (srvname, srvowner, grantor, grantee, prtype, grantable),
2245          pg_authid u_grantor,
2246          (
2247            SELECT oid, rolname FROM pg_authid
2248            UNION ALL
2249            SELECT 0::oid, 'PUBLIC'
2250          ) AS grantee (oid, rolname)
2251
2252     WHERE u_grantor.oid = srv.grantor
2253           AND grantee.oid = srv.grantee
2254           AND srv.prtype IN ('USAGE')
2255           AND (pg_has_role(u_grantor.oid, 'USAGE')
2256                OR pg_has_role(grantee.oid, 'USAGE')
2257                OR grantee.rolname = 'PUBLIC')
2258
2259     UNION ALL
2260
2261     /* sequences */
2262     SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2263            CAST(grantee.rolname AS sql_identifier) AS grantee,
2264            CAST(current_database() AS sql_identifier) AS object_catalog,
2265            CAST(n.nspname AS sql_identifier) AS object_schema,
2266            CAST(c.relname AS sql_identifier) AS object_name,
2267            CAST('SEQUENCE' AS character_data) AS object_type,
2268            CAST('USAGE' AS character_data) AS privilege_type,
2269            CAST(
2270              CASE WHEN
2271                   -- object owner always has grant options
2272                   pg_has_role(grantee.oid, c.relowner, 'USAGE')
2273                   OR c.grantable
2274                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2275
2276     FROM (
2277             SELECT oid, relname, relnamespace, relkind, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).* FROM pg_class
2278          ) AS c (oid, relname, relnamespace, relkind, relowner, grantor, grantee, prtype, grantable),
2279          pg_namespace n,
2280          pg_authid u_grantor,
2281          (
2282            SELECT oid, rolname FROM pg_authid
2283            UNION ALL
2284            SELECT 0::oid, 'PUBLIC'
2285          ) AS grantee (oid, rolname)
2286
2287     WHERE c.relnamespace = n.oid
2288           AND c.relkind = 'S'
2289           AND c.grantee = grantee.oid
2290           AND c.grantor = u_grantor.oid
2291           AND c.prtype IN ('USAGE')
2292           AND (pg_has_role(u_grantor.oid, 'USAGE')
2293                OR pg_has_role(grantee.oid, 'USAGE')
2294                OR grantee.rolname = 'PUBLIC');
2295
2296 GRANT SELECT ON usage_privileges TO PUBLIC;
2297
2298
2299 /*
2300  * 5.45
2301  * ROLE_USAGE_GRANTS view
2302  */
2303
2304 CREATE VIEW role_usage_grants AS
2305     SELECT grantor,
2306            grantee,
2307            object_catalog,
2308            object_schema,
2309            object_name,
2310            object_type,
2311            privilege_type,
2312            is_grantable
2313     FROM usage_privileges
2314     WHERE grantor IN (SELECT role_name FROM enabled_roles)
2315           OR grantee IN (SELECT role_name FROM enabled_roles);
2316
2317 GRANT SELECT ON role_usage_grants TO PUBLIC;
2318
2319
2320 /*
2321  * 5.75
2322  * USER_DEFINED_TYPES view
2323  */
2324
2325 CREATE VIEW user_defined_types AS
2326     SELECT CAST(current_database() AS sql_identifier) AS user_defined_type_catalog,
2327            CAST(n.nspname AS sql_identifier) AS user_defined_type_schema,
2328            CAST(c.relname AS sql_identifier) AS user_defined_type_name,
2329            CAST('STRUCTURED' AS character_data) AS user_defined_type_category,
2330            CAST('YES' AS yes_or_no) AS is_instantiable,
2331            CAST(null AS yes_or_no) AS is_final,
2332            CAST(null AS character_data) AS ordering_form,
2333            CAST(null AS character_data) AS ordering_category,
2334            CAST(null AS sql_identifier) AS ordering_routine_catalog,
2335            CAST(null AS sql_identifier) AS ordering_routine_schema,
2336            CAST(null AS sql_identifier) AS ordering_routine_name,
2337            CAST(null AS character_data) AS reference_type,
2338            CAST(null AS character_data) AS data_type,
2339            CAST(null AS cardinal_number) AS character_maximum_length,
2340            CAST(null AS cardinal_number) AS character_octet_length,
2341            CAST(null AS sql_identifier) AS character_set_catalog,
2342            CAST(null AS sql_identifier) AS character_set_schema,
2343            CAST(null AS sql_identifier) AS character_set_name,
2344            CAST(null AS sql_identifier) AS collation_catalog,
2345            CAST(null AS sql_identifier) AS collation_schema,
2346            CAST(null AS sql_identifier) AS collation_name,
2347            CAST(null AS cardinal_number) AS numeric_precision,
2348            CAST(null AS cardinal_number) AS numeric_precision_radix,
2349            CAST(null AS cardinal_number) AS numeric_scale,
2350            CAST(null AS cardinal_number) AS datetime_precision,
2351            CAST(null AS character_data) AS interval_type,
2352            CAST(null AS cardinal_number) AS interval_precision,
2353            CAST(null AS sql_identifier) AS source_dtd_identifier,
2354            CAST(null AS sql_identifier) AS ref_dtd_identifier
2355
2356     FROM pg_namespace n, pg_class c, pg_type t
2357
2358     WHERE n.oid = c.relnamespace
2359           AND t.typrelid = c.oid
2360           AND c.relkind = 'c'
2361           AND (pg_has_role(t.typowner, 'USAGE')
2362                OR has_type_privilege(t.oid, 'USAGE'));
2363
2364 GRANT SELECT ON user_defined_types TO PUBLIC;
2365
2366
2367 /*
2368  * 5.76
2369  * VIEW_COLUMN_USAGE
2370  */
2371
2372 CREATE VIEW view_column_usage AS
2373     SELECT DISTINCT
2374            CAST(current_database() AS sql_identifier) AS view_catalog,
2375            CAST(nv.nspname AS sql_identifier) AS view_schema,
2376            CAST(v.relname AS sql_identifier) AS view_name,
2377            CAST(current_database() AS sql_identifier) AS table_catalog,
2378            CAST(nt.nspname AS sql_identifier) AS table_schema,
2379            CAST(t.relname AS sql_identifier) AS table_name,
2380            CAST(a.attname AS sql_identifier) AS column_name
2381
2382     FROM pg_namespace nv, pg_class v, pg_depend dv,
2383          pg_depend dt, pg_class t, pg_namespace nt,
2384          pg_attribute a
2385
2386     WHERE nv.oid = v.relnamespace
2387           AND v.relkind = 'v'
2388           AND v.oid = dv.refobjid
2389           AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2390           AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2391           AND dv.deptype = 'i'
2392           AND dv.objid = dt.objid
2393           AND dv.refobjid <> dt.refobjid
2394           AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2395           AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2396           AND dt.refobjid = t.oid
2397           AND t.relnamespace = nt.oid
2398           AND t.relkind IN ('r', 'v', 'f')
2399           AND t.oid = a.attrelid
2400           AND dt.refobjsubid = a.attnum
2401           AND pg_has_role(t.relowner, 'USAGE');
2402
2403 GRANT SELECT ON view_column_usage TO PUBLIC;
2404
2405
2406 /*
2407  * 5.77
2408  * VIEW_PERIOD_USAGE
2409  */
2410
2411 -- feature not supported
2412
2413
2414 /*
2415  * 5.78
2416  * VIEW_ROUTINE_USAGE
2417  */
2418
2419 CREATE VIEW view_routine_usage AS
2420     SELECT DISTINCT
2421            CAST(current_database() AS sql_identifier) AS table_catalog,
2422            CAST(nv.nspname AS sql_identifier) AS table_schema,
2423            CAST(v.relname AS sql_identifier) AS table_name,
2424            CAST(current_database() AS sql_identifier) AS specific_catalog,
2425            CAST(np.nspname AS sql_identifier) AS specific_schema,
2426            CAST(p.proname || '_' || CAST(p.oid AS text)  AS sql_identifier) AS specific_name
2427
2428     FROM pg_namespace nv, pg_class v, pg_depend dv,
2429          pg_depend dp, pg_proc p, pg_namespace np
2430
2431     WHERE nv.oid = v.relnamespace
2432           AND v.relkind = 'v'
2433           AND v.oid = dv.refobjid
2434           AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2435           AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2436           AND dv.deptype = 'i'
2437           AND dv.objid = dp.objid
2438           AND dp.classid = 'pg_catalog.pg_rewrite'::regclass
2439           AND dp.refclassid = 'pg_catalog.pg_proc'::regclass
2440           AND dp.refobjid = p.oid
2441           AND p.pronamespace = np.oid
2442           AND pg_has_role(p.proowner, 'USAGE');
2443
2444 GRANT SELECT ON view_routine_usage TO PUBLIC;
2445
2446
2447 /*
2448  * 5.79
2449  * VIEW_TABLE_USAGE
2450  */
2451
2452 CREATE VIEW view_table_usage AS
2453     SELECT DISTINCT
2454            CAST(current_database() AS sql_identifier) AS view_catalog,
2455            CAST(nv.nspname AS sql_identifier) AS view_schema,
2456            CAST(v.relname AS sql_identifier) AS view_name,
2457            CAST(current_database() AS sql_identifier) AS table_catalog,
2458            CAST(nt.nspname AS sql_identifier) AS table_schema,
2459            CAST(t.relname AS sql_identifier) AS table_name
2460
2461     FROM pg_namespace nv, pg_class v, pg_depend dv,
2462          pg_depend dt, pg_class t, pg_namespace nt
2463
2464     WHERE nv.oid = v.relnamespace
2465           AND v.relkind = 'v'
2466           AND v.oid = dv.refobjid
2467           AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2468           AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2469           AND dv.deptype = 'i'
2470           AND dv.objid = dt.objid
2471           AND dv.refobjid <> dt.refobjid
2472           AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2473           AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2474           AND dt.refobjid = t.oid
2475           AND t.relnamespace = nt.oid
2476           AND t.relkind IN ('r', 'v', 'f')
2477           AND pg_has_role(t.relowner, 'USAGE');
2478
2479 GRANT SELECT ON view_table_usage TO PUBLIC;
2480
2481
2482 /*
2483  * 5.80
2484  * VIEWS view
2485  */
2486
2487 CREATE VIEW views AS
2488     SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
2489            CAST(nc.nspname AS sql_identifier) AS table_schema,
2490            CAST(c.relname AS sql_identifier) AS table_name,
2491
2492            CAST(
2493              CASE WHEN pg_has_role(c.relowner, 'USAGE')
2494                   THEN pg_get_viewdef(c.oid)
2495                   ELSE null END
2496              AS character_data) AS view_definition,
2497
2498            CAST('NONE' AS character_data) AS check_option,
2499
2500            CAST(
2501              CASE WHEN EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '2' AND is_instead)
2502                    AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '4' AND is_instead)
2503                   THEN 'YES' ELSE 'NO' END
2504              AS yes_or_no) AS is_updatable,
2505
2506            CAST(
2507              CASE WHEN EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '3' AND is_instead)
2508                   THEN 'YES' ELSE 'NO' END
2509              AS yes_or_no) AS is_insertable_into,
2510
2511            CAST(
2512              -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_UPDATE
2513              CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 81 = 81)
2514                   THEN 'YES' ELSE 'NO' END
2515            AS yes_or_no) AS is_trigger_updatable,
2516
2517            CAST(
2518              -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_DELETE
2519              CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 73 = 73)
2520                   THEN 'YES' ELSE 'NO' END
2521            AS yes_or_no) AS is_trigger_deletable,
2522
2523            CAST(
2524              -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_INSERT
2525              CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 69 = 69)
2526                   THEN 'YES' ELSE 'NO' END
2527            AS yes_or_no) AS is_trigger_insertable_into
2528
2529     FROM pg_namespace nc, pg_class c
2530
2531     WHERE c.relnamespace = nc.oid
2532           AND c.relkind = 'v'
2533           AND (NOT pg_is_other_temp_schema(nc.oid))
2534           AND (pg_has_role(c.relowner, 'USAGE')
2535                OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
2536                OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES') );
2537
2538 GRANT SELECT ON views TO PUBLIC;
2539
2540
2541 -- The following views have dependencies that force them to appear out of order.
2542
2543 /*
2544  * 5.25
2545  * DATA_TYPE_PRIVILEGES view
2546  */
2547
2548 CREATE VIEW data_type_privileges AS
2549     SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2550            CAST(x.objschema AS sql_identifier) AS object_schema,
2551            CAST(x.objname AS sql_identifier) AS object_name,
2552            CAST(x.objtype AS character_data) AS object_type,
2553            CAST(x.objdtdid AS sql_identifier) AS dtd_identifier
2554
2555     FROM
2556       (
2557         SELECT udt_schema, udt_name, 'USER-DEFINED TYPE'::text, dtd_identifier FROM attributes
2558         UNION ALL
2559         SELECT table_schema, table_name, 'TABLE'::text, dtd_identifier FROM columns
2560         UNION ALL
2561         SELECT domain_schema, domain_name, 'DOMAIN'::text, dtd_identifier FROM domains
2562         UNION ALL
2563         SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM parameters
2564         UNION ALL
2565         SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM routines
2566       ) AS x (objschema, objname, objtype, objdtdid);
2567
2568 GRANT SELECT ON data_type_privileges TO PUBLIC;
2569
2570
2571 /*
2572  * 5.30
2573  * ELEMENT_TYPES view
2574  */
2575
2576 CREATE VIEW element_types AS
2577     SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2578            CAST(n.nspname AS sql_identifier) AS object_schema,
2579            CAST(x.objname AS sql_identifier) AS object_name,
2580            CAST(x.objtype AS character_data) AS object_type,
2581            CAST(x.objdtdid AS sql_identifier) AS collection_type_identifier,
2582            CAST(
2583              CASE WHEN nbt.nspname = 'pg_catalog' THEN format_type(bt.oid, null)
2584                   ELSE 'USER-DEFINED' END AS character_data) AS data_type,
2585
2586            CAST(null AS cardinal_number) AS character_maximum_length,
2587            CAST(null AS cardinal_number) AS character_octet_length,
2588            CAST(null AS sql_identifier) AS character_set_catalog,
2589            CAST(null AS sql_identifier) AS character_set_schema,
2590            CAST(null AS sql_identifier) AS character_set_name,
2591            CAST(CASE WHEN nco.nspname IS NOT NULL THEN current_database() END AS sql_identifier) AS collation_catalog,
2592            CAST(nco.nspname AS sql_identifier) AS collation_schema,
2593            CAST(co.collname AS sql_identifier) AS collation_name,
2594            CAST(null AS cardinal_number) AS numeric_precision,
2595            CAST(null AS cardinal_number) AS numeric_precision_radix,
2596            CAST(null AS cardinal_number) AS numeric_scale,
2597            CAST(null AS cardinal_number) AS datetime_precision,
2598            CAST(null AS character_data) AS interval_type,
2599            CAST(null AS cardinal_number) AS interval_precision,
2600
2601            CAST(null AS character_data) AS domain_default, -- XXX maybe a bug in the standard
2602
2603            CAST(current_database() AS sql_identifier) AS udt_catalog,
2604            CAST(nbt.nspname AS sql_identifier) AS udt_schema,
2605            CAST(bt.typname AS sql_identifier) AS udt_name,
2606
2607            CAST(null AS sql_identifier) AS scope_catalog,
2608            CAST(null AS sql_identifier) AS scope_schema,
2609            CAST(null AS sql_identifier) AS scope_name,
2610
2611            CAST(null AS cardinal_number) AS maximum_cardinality,
2612            CAST('a' || CAST(x.objdtdid AS text) AS sql_identifier) AS dtd_identifier
2613
2614     FROM pg_namespace n, pg_type at, pg_namespace nbt, pg_type bt,
2615          (
2616            /* columns, attributes */
2617            SELECT c.relnamespace, CAST(c.relname AS sql_identifier),
2618                   CASE WHEN c.relkind = 'c' THEN 'USER-DEFINED TYPE'::text ELSE 'TABLE'::text END,
2619                   a.attnum, a.atttypid, a.attcollation
2620            FROM pg_class c, pg_attribute a
2621            WHERE c.oid = a.attrelid
2622                  AND c.relkind IN ('r', 'v', 'f', 'c')
2623                  AND attnum > 0 AND NOT attisdropped
2624
2625            UNION ALL
2626
2627            /* domains */
2628            SELECT t.typnamespace, CAST(t.typname AS sql_identifier),
2629                   'DOMAIN'::text, 1, t.typbasetype, t.typcollation
2630            FROM pg_type t
2631            WHERE t.typtype = 'd'
2632
2633            UNION ALL
2634
2635            /* parameters */
2636            SELECT pronamespace, CAST(proname || '_' || CAST(oid AS text) AS sql_identifier),
2637                   'ROUTINE'::text, (ss.x).n, (ss.x).x, 0
2638            FROM (SELECT p.pronamespace, p.proname, p.oid,
2639                         _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
2640                  FROM pg_proc p) AS ss
2641
2642            UNION ALL
2643
2644            /* result types */
2645            SELECT p.pronamespace, CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier),
2646                   'ROUTINE'::text, 0, p.prorettype, 0
2647            FROM pg_proc p
2648
2649          ) AS x (objschema, objname, objtype, objdtdid, objtypeid, objcollation)
2650          LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid))
2651            ON x.objcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default')
2652
2653     WHERE n.oid = x.objschema
2654           AND at.oid = x.objtypeid
2655           AND (at.typelem <> 0 AND at.typlen = -1)
2656           AND at.typelem = bt.oid
2657           AND nbt.oid = bt.typnamespace
2658
2659           AND (n.nspname, x.objname, x.objtype, CAST(x.objdtdid AS sql_identifier)) IN
2660               ( SELECT object_schema, object_name, object_type, dtd_identifier
2661                     FROM data_type_privileges );
2662
2663 GRANT SELECT ON element_types TO PUBLIC;
2664
2665
2666 -- SQL/MED views; these use section numbers from part 9 of the standard.
2667 -- (still SQL:2008; there is no SQL:2011 SQL/MED)
2668
2669 /* Base view for foreign table columns */
2670 CREATE VIEW _pg_foreign_table_columns AS
2671     SELECT n.nspname,
2672            c.relname,
2673            a.attname,
2674            a.attfdwoptions
2675     FROM pg_foreign_table t, pg_authid u, pg_namespace n, pg_class c,
2676          pg_attribute a
2677     WHERE u.oid = c.relowner
2678           AND (pg_has_role(c.relowner, 'USAGE')
2679                OR has_column_privilege(c.oid, a.attnum, 'SELECT, INSERT, UPDATE, REFERENCES'))
2680           AND n.oid = c.relnamespace
2681           AND c.oid = t.ftrelid
2682           AND c.relkind = 'f'
2683           AND a.attrelid = c.oid
2684           AND a.attnum > 0;
2685
2686 /*
2687  * 24.2
2688  * COLUMN_OPTIONS view
2689  */
2690 CREATE VIEW column_options AS
2691     SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
2692            c.nspname AS table_schema,
2693            c.relname AS table_name,
2694            c.attname AS column_name,
2695            CAST((pg_options_to_table(c.attfdwoptions)).option_name AS sql_identifier) AS option_name,
2696            CAST((pg_options_to_table(c.attfdwoptions)).option_value AS character_data) AS option_value
2697     FROM _pg_foreign_table_columns c;
2698
2699 GRANT SELECT ON column_options TO PUBLIC;
2700
2701
2702 /* Base view for foreign-data wrappers */
2703 CREATE VIEW _pg_foreign_data_wrappers AS
2704     SELECT w.oid,
2705            w.fdwowner,
2706            w.fdwoptions,
2707            CAST(current_database() AS sql_identifier) AS foreign_data_wrapper_catalog,
2708            CAST(fdwname AS sql_identifier) AS foreign_data_wrapper_name,
2709            CAST(u.rolname AS sql_identifier) AS authorization_identifier,
2710            CAST('c' AS character_data) AS foreign_data_wrapper_language
2711     FROM pg_foreign_data_wrapper w, pg_authid u
2712     WHERE u.oid = w.fdwowner
2713           AND (pg_has_role(fdwowner, 'USAGE')
2714                OR has_foreign_data_wrapper_privilege(w.oid, 'USAGE'));
2715
2716
2717 /*
2718  * 24.4
2719  * FOREIGN_DATA_WRAPPER_OPTIONS view
2720  */
2721 CREATE VIEW foreign_data_wrapper_options AS
2722     SELECT foreign_data_wrapper_catalog,
2723            foreign_data_wrapper_name,
2724            CAST((pg_options_to_table(w.fdwoptions)).option_name AS sql_identifier) AS option_name,
2725            CAST((pg_options_to_table(w.fdwoptions)).option_value AS character_data) AS option_value
2726     FROM _pg_foreign_data_wrappers w;
2727
2728 GRANT SELECT ON foreign_data_wrapper_options TO PUBLIC;
2729
2730
2731 /*
2732  * 24.5
2733  * FOREIGN_DATA_WRAPPERS view
2734  */
2735 CREATE VIEW foreign_data_wrappers AS
2736     SELECT foreign_data_wrapper_catalog,
2737            foreign_data_wrapper_name,
2738            authorization_identifier,
2739            CAST(NULL AS character_data) AS library_name,
2740            foreign_data_wrapper_language
2741     FROM _pg_foreign_data_wrappers w;
2742
2743 GRANT SELECT ON foreign_data_wrappers TO PUBLIC;
2744
2745
2746 /* Base view for foreign servers */
2747 CREATE VIEW _pg_foreign_servers AS
2748     SELECT s.oid,
2749            s.srvoptions,
2750            CAST(current_database() AS sql_identifier) AS foreign_server_catalog,
2751            CAST(srvname AS sql_identifier) AS foreign_server_name,
2752            CAST(current_database() AS sql_identifier) AS foreign_data_wrapper_catalog,
2753            CAST(w.fdwname AS sql_identifier) AS foreign_data_wrapper_name,
2754            CAST(srvtype AS character_data) AS foreign_server_type,
2755            CAST(srvversion AS character_data) AS foreign_server_version,
2756            CAST(u.rolname AS sql_identifier) AS authorization_identifier
2757     FROM pg_foreign_server s, pg_foreign_data_wrapper w, pg_authid u
2758     WHERE w.oid = s.srvfdw
2759           AND u.oid = s.srvowner
2760           AND (pg_has_role(s.srvowner, 'USAGE')
2761                OR has_server_privilege(s.oid, 'USAGE'));
2762
2763
2764 /*
2765  * 24.6
2766  * FOREIGN_SERVER_OPTIONS view
2767  */
2768 CREATE VIEW foreign_server_options AS
2769     SELECT foreign_server_catalog,
2770            foreign_server_name,
2771            CAST((pg_options_to_table(s.srvoptions)).option_name AS sql_identifier) AS option_name,
2772            CAST((pg_options_to_table(s.srvoptions)).option_value AS character_data) AS option_value
2773     FROM _pg_foreign_servers s;
2774
2775 GRANT SELECT ON TABLE foreign_server_options TO PUBLIC;
2776
2777
2778 /*
2779  * 24.7
2780  * FOREIGN_SERVERS view
2781  */
2782 CREATE VIEW foreign_servers AS
2783     SELECT foreign_server_catalog,
2784            foreign_server_name,
2785            foreign_data_wrapper_catalog,
2786            foreign_data_wrapper_name,
2787            foreign_server_type,
2788            foreign_server_version,
2789            authorization_identifier
2790     FROM _pg_foreign_servers;
2791
2792 GRANT SELECT ON foreign_servers TO PUBLIC;
2793
2794
2795 /* Base view for foreign tables */
2796 CREATE VIEW _pg_foreign_tables AS
2797     SELECT
2798            CAST(current_database() AS sql_identifier) AS foreign_table_catalog,
2799            n.nspname AS foreign_table_schema,
2800            c.relname AS foreign_table_name,
2801            t.ftoptions AS ftoptions,
2802            CAST(current_database() AS sql_identifier) AS foreign_server_catalog,
2803            CAST(srvname AS sql_identifier) AS foreign_server_name,
2804            CAST(u.rolname AS sql_identifier) AS authorization_identifier
2805     FROM pg_foreign_table t, pg_foreign_server s, pg_foreign_data_wrapper w,
2806          pg_authid u, pg_namespace n, pg_class c
2807     WHERE w.oid = s.srvfdw
2808           AND u.oid = c.relowner
2809           AND (pg_has_role(c.relowner, 'USAGE')
2810                OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
2811                OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'))
2812           AND n.oid = c.relnamespace
2813           AND c.oid = t.ftrelid
2814           AND c.relkind = 'f'
2815           AND s.oid = t.ftserver;
2816
2817
2818 /*
2819  * 24.8
2820  * FOREIGN_TABLE_OPTIONS view
2821  */
2822 CREATE VIEW foreign_table_options AS
2823     SELECT foreign_table_catalog,
2824            foreign_table_schema,
2825            foreign_table_name,
2826            CAST((pg_options_to_table(t.ftoptions)).option_name AS sql_identifier) AS option_name,
2827            CAST((pg_options_to_table(t.ftoptions)).option_value AS character_data) AS option_value
2828     FROM _pg_foreign_tables t;
2829
2830 GRANT SELECT ON TABLE foreign_table_options TO PUBLIC;
2831
2832
2833 /*
2834  * 24.9
2835  * FOREIGN_TABLES view
2836  */
2837 CREATE VIEW foreign_tables AS
2838     SELECT foreign_table_catalog,
2839            foreign_table_schema,
2840            foreign_table_name,
2841            foreign_server_catalog,
2842            foreign_server_name
2843     FROM _pg_foreign_tables;
2844
2845 GRANT SELECT ON foreign_tables TO PUBLIC;
2846
2847
2848
2849 /* Base view for user mappings */
2850 CREATE VIEW _pg_user_mappings AS
2851     SELECT um.oid,
2852            um.umoptions,
2853            um.umuser,
2854            CAST(COALESCE(u.rolname,'PUBLIC') AS sql_identifier ) AS authorization_identifier,
2855            s.foreign_server_catalog,
2856            s.foreign_server_name,
2857            s.authorization_identifier AS srvowner
2858     FROM pg_user_mapping um LEFT JOIN pg_authid u ON (u.oid = um.umuser),
2859          _pg_foreign_servers s
2860     WHERE s.oid = um.umserver;
2861
2862
2863 /*
2864  * 24.12
2865  * USER_MAPPING_OPTIONS view
2866  */
2867 CREATE VIEW user_mapping_options AS
2868     SELECT authorization_identifier,
2869            foreign_server_catalog,
2870            foreign_server_name,
2871            CAST((pg_options_to_table(um.umoptions)).option_name AS sql_identifier) AS option_name,
2872            CAST(CASE WHEN (umuser <> 0 AND authorization_identifier = current_user)
2873                        OR (umuser = 0 AND pg_has_role(srvowner, 'USAGE'))
2874                        OR (SELECT rolsuper FROM pg_authid WHERE rolname = current_user) THEN (pg_options_to_table(um.umoptions)).option_value
2875                      ELSE NULL END AS character_data) AS option_value
2876     FROM _pg_user_mappings um;
2877
2878 GRANT SELECT ON user_mapping_options TO PUBLIC;
2879
2880
2881 /*
2882  * 24.13
2883  * USER_MAPPINGS view
2884  */
2885 CREATE VIEW user_mappings AS
2886     SELECT authorization_identifier,
2887            foreign_server_catalog,
2888            foreign_server_name
2889     FROM _pg_user_mappings;
2890
2891 GRANT SELECT ON user_mappings TO PUBLIC;