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

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