util/X86MAC64/share/postgresql/information_schema.sql

   1 /*
   2  * SQL Information Schema
   3  * as defined in ISO/IEC 9075-11:2011
   4  *
   5  * Copyright (c) 2003-2014, 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 -- feature not supported
1932
1933
1934 /*
1935  * 5.65
1936  * TRANSLATIONS view
1937  */
1938
1939 -- feature not supported
1940
1941
1942 /*
1943  * 5.66
1944  * TRIGGERED_UPDATE_COLUMNS view
1945  */
1946
1947 CREATE VIEW triggered_update_columns AS
1948     SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1949            CAST(n.nspname AS sql_identifier) AS trigger_schema,
1950            CAST(t.tgname AS sql_identifier) AS trigger_name,
1951            CAST(current_database() AS sql_identifier) AS event_object_catalog,
1952            CAST(n.nspname AS sql_identifier) AS event_object_schema,
1953            CAST(c.relname AS sql_identifier) AS event_object_table,
1954            CAST(a.attname AS sql_identifier) AS event_object_column
1955
1956     FROM pg_namespace n, pg_class c, pg_trigger t,
1957          (SELECT tgoid, (ta0.tgat).x AS tgattnum, (ta0.tgat).n AS tgattpos
1958           FROM (SELECT oid AS tgoid, information_schema._pg_expandarray(tgattr) AS tgat FROM pg_trigger) AS ta0) AS ta,
1959          pg_attribute a
1960
1961     WHERE n.oid = c.relnamespace
1962           AND c.oid = t.tgrelid
1963           AND t.oid = ta.tgoid
1964           AND (a.attrelid, a.attnum) = (t.tgrelid, ta.tgattnum)
1965           AND NOT t.tgisinternal
1966           AND (NOT pg_is_other_temp_schema(n.oid))
1967           AND (pg_has_role(c.relowner, 'USAGE')
1968                -- SELECT privilege omitted, per SQL standard
1969                OR has_column_privilege(c.oid, a.attnum, 'INSERT, UPDATE, REFERENCES') );
1970
1971 GRANT SELECT ON triggered_update_columns TO PUBLIC;
1972
1973
1974 /*
1975  * 5.67
1976  * TRIGGER_COLUMN_USAGE view
1977  */
1978
1979 -- not tracked by PostgreSQL
1980
1981
1982 /*
1983  * 5.68
1984  * TRIGGER_PERIOD_USAGE view
1985  */
1986
1987 -- feature not supported
1988
1989
1990 /*
1991  * 5.69
1992  * TRIGGER_ROUTINE_USAGE view
1993  */
1994
1995 -- not tracked by PostgreSQL
1996
1997
1998 /*
1999  * 5.70
2000  * TRIGGER_SEQUENCE_USAGE view
2001  */
2002
2003 -- not tracked by PostgreSQL
2004
2005
2006 /*
2007  * 5.71
2008  * TRIGGER_TABLE_USAGE view
2009  */
2010
2011 -- not tracked by PostgreSQL
2012
2013
2014 /*
2015  * 5.72
2016  * TRIGGERS view
2017  */
2018
2019 CREATE VIEW triggers AS
2020     SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
2021            CAST(n.nspname AS sql_identifier) AS trigger_schema,
2022            CAST(t.tgname AS sql_identifier) AS trigger_name,
2023            CAST(em.text AS character_data) AS event_manipulation,
2024            CAST(current_database() AS sql_identifier) AS event_object_catalog,
2025            CAST(n.nspname AS sql_identifier) AS event_object_schema,
2026            CAST(c.relname AS sql_identifier) AS event_object_table,
2027            CAST(null AS cardinal_number) AS action_order,
2028            -- XXX strange hacks follow
2029            CAST(
2030              CASE WHEN pg_has_role(c.relowner, 'USAGE')
2031                THEN (SELECT m[1] FROM regexp_matches(pg_get_triggerdef(t.oid), E'.{35,} WHEN \\((.+)\\) EXECUTE PROCEDURE') AS rm(m) LIMIT 1)
2032                ELSE null END
2033              AS character_data) AS action_condition,
2034            CAST(
2035              substring(pg_get_triggerdef(t.oid) from
2036                        position('EXECUTE PROCEDURE' in substring(pg_get_triggerdef(t.oid) from 48)) + 47)
2037              AS character_data) AS action_statement,
2038            CAST(
2039              -- hard-wired reference to TRIGGER_TYPE_ROW
2040              CASE t.tgtype & 1 WHEN 1 THEN 'ROW' ELSE 'STATEMENT' END
2041              AS character_data) AS action_orientation,
2042            CAST(
2043              -- hard-wired refs to TRIGGER_TYPE_BEFORE, TRIGGER_TYPE_INSTEAD
2044              CASE t.tgtype & 66 WHEN 2 THEN 'BEFORE' WHEN 64 THEN 'INSTEAD OF' ELSE 'AFTER' END
2045              AS character_data) AS action_timing,
2046            CAST(null AS sql_identifier) AS action_reference_old_table,
2047            CAST(null AS sql_identifier) AS action_reference_new_table,
2048            CAST(null AS sql_identifier) AS action_reference_old_row,
2049            CAST(null AS sql_identifier) AS action_reference_new_row,
2050            CAST(null AS time_stamp) AS created
2051
2052     FROM pg_namespace n, pg_class c, pg_trigger t,
2053          -- hard-wired refs to TRIGGER_TYPE_INSERT, TRIGGER_TYPE_DELETE,
2054          -- TRIGGER_TYPE_UPDATE; we intentionally omit TRIGGER_TYPE_TRUNCATE
2055          (VALUES (4, 'INSERT'),
2056                  (8, 'DELETE'),
2057                  (16, 'UPDATE')) AS em (num, text)
2058
2059     WHERE n.oid = c.relnamespace
2060           AND c.oid = t.tgrelid
2061           AND t.tgtype & em.num <> 0
2062           AND NOT t.tgisinternal
2063           AND (NOT pg_is_other_temp_schema(n.oid))
2064           AND (pg_has_role(c.relowner, 'USAGE')
2065                -- SELECT privilege omitted, per SQL standard
2066                OR has_table_privilege(c.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
2067                OR has_any_column_privilege(c.oid, 'INSERT, UPDATE, REFERENCES') );
2068
2069 GRANT SELECT ON triggers TO PUBLIC;
2070
2071
2072 /*
2073  * 5.73
2074  * UDT_PRIVILEGES view
2075  */
2076
2077 CREATE VIEW udt_privileges AS
2078     SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2079            CAST(grantee.rolname AS sql_identifier) AS grantee,
2080            CAST(current_database() AS sql_identifier) AS udt_catalog,
2081            CAST(n.nspname AS sql_identifier) AS udt_schema,
2082            CAST(t.typname AS sql_identifier) AS udt_name,
2083            CAST('TYPE USAGE' AS character_data) AS privilege_type, -- sic
2084            CAST(
2085              CASE WHEN
2086                   -- object owner always has grant options
2087                   pg_has_role(grantee.oid, t.typowner, 'USAGE')
2088                   OR t.grantable
2089                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2090
2091     FROM (
2092             SELECT oid, typname, typnamespace, typtype, typowner, (aclexplode(coalesce(typacl, acldefault('T', typowner)))).* FROM pg_type
2093          ) AS t (oid, typname, typnamespace, typtype, typowner, grantor, grantee, prtype, grantable),
2094          pg_namespace n,
2095          pg_authid u_grantor,
2096          (
2097            SELECT oid, rolname FROM pg_authid
2098            UNION ALL
2099            SELECT 0::oid, 'PUBLIC'
2100          ) AS grantee (oid, rolname)
2101
2102     WHERE t.typnamespace = n.oid
2103           AND t.typtype = 'c'
2104           AND t.grantee = grantee.oid
2105           AND t.grantor = u_grantor.oid
2106           AND t.prtype IN ('USAGE')
2107           AND (pg_has_role(u_grantor.oid, 'USAGE')
2108                OR pg_has_role(grantee.oid, 'USAGE')
2109                OR grantee.rolname = 'PUBLIC');
2110
2111 GRANT SELECT ON udt_privileges TO PUBLIC;
2112
2113
2114 /*
2115  * 5.46
2116  * ROLE_UDT_GRANTS view
2117  */
2118
2119 CREATE VIEW role_udt_grants AS
2120     SELECT grantor,
2121            grantee,
2122            udt_catalog,
2123            udt_schema,
2124            udt_name,
2125            privilege_type,
2126            is_grantable
2127     FROM udt_privileges
2128     WHERE grantor IN (SELECT role_name FROM enabled_roles)
2129           OR grantee IN (SELECT role_name FROM enabled_roles);
2130
2131 GRANT SELECT ON role_udt_grants TO PUBLIC;
2132
2133
2134 /*
2135  * 5.74
2136  * USAGE_PRIVILEGES view
2137  */
2138
2139 CREATE VIEW usage_privileges AS
2140
2141     /* collations */
2142     -- Collations have no real privileges, so we represent all collations with implicit usage privilege here.
2143     SELECT CAST(u.rolname AS sql_identifier) AS grantor,
2144            CAST('PUBLIC' AS sql_identifier) AS grantee,
2145            CAST(current_database() AS sql_identifier) AS object_catalog,
2146            CAST(n.nspname AS sql_identifier) AS object_schema,
2147            CAST(c.collname AS sql_identifier) AS object_name,
2148            CAST('COLLATION' AS character_data) AS object_type,
2149            CAST('USAGE' AS character_data) AS privilege_type,
2150            CAST('NO' AS yes_or_no) AS is_grantable
2151
2152     FROM pg_authid u,
2153          pg_namespace n,
2154          pg_collation c
2155
2156     WHERE u.oid = c.collowner
2157           AND c.collnamespace = n.oid
2158           AND collencoding IN (-1, (SELECT encoding FROM pg_database WHERE datname = current_database()))
2159
2160     UNION ALL
2161
2162     /* domains */
2163     SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2164            CAST(grantee.rolname AS sql_identifier) AS grantee,
2165            CAST(current_database() AS sql_identifier) AS object_catalog,
2166            CAST(n.nspname AS sql_identifier) AS object_schema,
2167            CAST(t.typname AS sql_identifier) AS object_name,
2168            CAST('DOMAIN' AS character_data) AS object_type,
2169            CAST('USAGE' AS character_data) AS privilege_type,
2170            CAST(
2171              CASE WHEN
2172                   -- object owner always has grant options
2173                   pg_has_role(grantee.oid, t.typowner, 'USAGE')
2174                   OR t.grantable
2175                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2176
2177     FROM (
2178             SELECT oid, typname, typnamespace, typtype, typowner, (aclexplode(coalesce(typacl, acldefault('T', typowner)))).* FROM pg_type
2179          ) AS t (oid, typname, typnamespace, typtype, typowner, grantor, grantee, prtype, grantable),
2180          pg_namespace n,
2181          pg_authid u_grantor,
2182          (
2183            SELECT oid, rolname FROM pg_authid
2184            UNION ALL
2185            SELECT 0::oid, 'PUBLIC'
2186          ) AS grantee (oid, rolname)
2187
2188     WHERE t.typnamespace = n.oid
2189           AND t.typtype = 'd'
2190           AND t.grantee = grantee.oid
2191           AND t.grantor = u_grantor.oid
2192           AND t.prtype IN ('USAGE')
2193           AND (pg_has_role(u_grantor.oid, 'USAGE')
2194                OR pg_has_role(grantee.oid, 'USAGE')
2195                OR grantee.rolname = 'PUBLIC')
2196
2197     UNION ALL
2198
2199     /* foreign-data wrappers */
2200     SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2201            CAST(grantee.rolname AS sql_identifier) AS grantee,
2202            CAST(current_database() AS sql_identifier) AS object_catalog,
2203            CAST('' AS sql_identifier) AS object_schema,
2204            CAST(fdw.fdwname AS sql_identifier) AS object_name,
2205            CAST('FOREIGN DATA WRAPPER' AS character_data) AS object_type,
2206            CAST('USAGE' AS character_data) AS privilege_type,
2207            CAST(
2208              CASE WHEN
2209                   -- object owner always has grant options
2210                   pg_has_role(grantee.oid, fdw.fdwowner, 'USAGE')
2211                   OR fdw.grantable
2212                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2213
2214     FROM (
2215             SELECT fdwname, fdwowner, (aclexplode(coalesce(fdwacl, acldefault('F', fdwowner)))).* FROM pg_foreign_data_wrapper
2216          ) AS fdw (fdwname, fdwowner, grantor, grantee, prtype, grantable),
2217          pg_authid u_grantor,
2218          (
2219            SELECT oid, rolname FROM pg_authid
2220            UNION ALL
2221            SELECT 0::oid, 'PUBLIC'
2222          ) AS grantee (oid, rolname)
2223
2224     WHERE u_grantor.oid = fdw.grantor
2225           AND grantee.oid = fdw.grantee
2226           AND fdw.prtype IN ('USAGE')
2227           AND (pg_has_role(u_grantor.oid, 'USAGE')
2228                OR pg_has_role(grantee.oid, 'USAGE')
2229                OR grantee.rolname = 'PUBLIC')
2230
2231     UNION ALL
2232
2233     /* foreign servers */
2234     SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2235            CAST(grantee.rolname AS sql_identifier) AS grantee,
2236            CAST(current_database() AS sql_identifier) AS object_catalog,
2237            CAST('' AS sql_identifier) AS object_schema,
2238            CAST(srv.srvname AS sql_identifier) AS object_name,
2239            CAST('FOREIGN SERVER' AS character_data) AS object_type,
2240            CAST('USAGE' AS character_data) AS privilege_type,
2241            CAST(
2242              CASE WHEN
2243                   -- object owner always has grant options
2244                   pg_has_role(grantee.oid, srv.srvowner, 'USAGE')
2245                   OR srv.grantable
2246                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2247
2248     FROM (
2249             SELECT srvname, srvowner, (aclexplode(coalesce(srvacl, acldefault('S', srvowner)))).* FROM pg_foreign_server
2250          ) AS srv (srvname, srvowner, grantor, grantee, prtype, grantable),
2251          pg_authid u_grantor,
2252          (
2253            SELECT oid, rolname FROM pg_authid
2254            UNION ALL
2255            SELECT 0::oid, 'PUBLIC'
2256          ) AS grantee (oid, rolname)
2257
2258     WHERE u_grantor.oid = srv.grantor
2259           AND grantee.oid = srv.grantee
2260           AND srv.prtype IN ('USAGE')
2261           AND (pg_has_role(u_grantor.oid, 'USAGE')
2262                OR pg_has_role(grantee.oid, 'USAGE')
2263                OR grantee.rolname = 'PUBLIC')
2264
2265     UNION ALL
2266
2267     /* sequences */
2268     SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2269            CAST(grantee.rolname AS sql_identifier) AS grantee,
2270            CAST(current_database() AS sql_identifier) AS object_catalog,
2271            CAST(n.nspname AS sql_identifier) AS object_schema,
2272            CAST(c.relname AS sql_identifier) AS object_name,
2273            CAST('SEQUENCE' AS character_data) AS object_type,
2274            CAST('USAGE' AS character_data) AS privilege_type,
2275            CAST(
2276              CASE WHEN
2277                   -- object owner always has grant options
2278                   pg_has_role(grantee.oid, c.relowner, 'USAGE')
2279                   OR c.grantable
2280                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2281
2282     FROM (
2283             SELECT oid, relname, relnamespace, relkind, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).* FROM pg_class
2284          ) AS c (oid, relname, relnamespace, relkind, relowner, grantor, grantee, prtype, grantable),
2285          pg_namespace n,
2286          pg_authid u_grantor,
2287          (
2288            SELECT oid, rolname FROM pg_authid
2289            UNION ALL
2290            SELECT 0::oid, 'PUBLIC'
2291          ) AS grantee (oid, rolname)
2292
2293     WHERE c.relnamespace = n.oid
2294           AND c.relkind = 'S'
2295           AND c.grantee = grantee.oid
2296           AND c.grantor = u_grantor.oid
2297           AND c.prtype IN ('USAGE')
2298           AND (pg_has_role(u_grantor.oid, 'USAGE')
2299                OR pg_has_role(grantee.oid, 'USAGE')
2300                OR grantee.rolname = 'PUBLIC');
2301
2302 GRANT SELECT ON usage_privileges TO PUBLIC;
2303
2304
2305 /*
2306  * 5.45
2307  * ROLE_USAGE_GRANTS view
2308  */
2309
2310 CREATE VIEW role_usage_grants AS
2311     SELECT grantor,
2312            grantee,
2313            object_catalog,
2314            object_schema,
2315            object_name,
2316            object_type,
2317            privilege_type,
2318            is_grantable
2319     FROM usage_privileges
2320     WHERE grantor IN (SELECT role_name FROM enabled_roles)
2321           OR grantee IN (SELECT role_name FROM enabled_roles);
2322
2323 GRANT SELECT ON role_usage_grants TO PUBLIC;
2324
2325
2326 /*
2327  * 5.75
2328  * USER_DEFINED_TYPES view
2329  */
2330
2331 CREATE VIEW user_defined_types AS
2332     SELECT CAST(current_database() AS sql_identifier) AS user_defined_type_catalog,
2333            CAST(n.nspname AS sql_identifier) AS user_defined_type_schema,
2334            CAST(c.relname AS sql_identifier) AS user_defined_type_name,
2335            CAST('STRUCTURED' AS character_data) AS user_defined_type_category,
2336            CAST('YES' AS yes_or_no) AS is_instantiable,
2337            CAST(null AS yes_or_no) AS is_final,
2338            CAST(null AS character_data) AS ordering_form,
2339            CAST(null AS character_data) AS ordering_category,
2340            CAST(null AS sql_identifier) AS ordering_routine_catalog,
2341            CAST(null AS sql_identifier) AS ordering_routine_schema,
2342            CAST(null AS sql_identifier) AS ordering_routine_name,
2343            CAST(null AS character_data) AS reference_type,
2344            CAST(null AS character_data) AS data_type,
2345            CAST(null AS cardinal_number) AS character_maximum_length,
2346            CAST(null AS cardinal_number) AS character_octet_length,
2347            CAST(null AS sql_identifier) AS character_set_catalog,
2348            CAST(null AS sql_identifier) AS character_set_schema,
2349            CAST(null AS sql_identifier) AS character_set_name,
2350            CAST(null AS sql_identifier) AS collation_catalog,
2351            CAST(null AS sql_identifier) AS collation_schema,
2352            CAST(null AS sql_identifier) AS collation_name,
2353            CAST(null AS cardinal_number) AS numeric_precision,
2354            CAST(null AS cardinal_number) AS numeric_precision_radix,
2355            CAST(null AS cardinal_number) AS numeric_scale,
2356            CAST(null AS cardinal_number) AS datetime_precision,
2357            CAST(null AS character_data) AS interval_type,
2358            CAST(null AS cardinal_number) AS interval_precision,
2359            CAST(null AS sql_identifier) AS source_dtd_identifier,
2360            CAST(null AS sql_identifier) AS ref_dtd_identifier
2361
2362     FROM pg_namespace n, pg_class c, pg_type t
2363
2364     WHERE n.oid = c.relnamespace
2365           AND t.typrelid = c.oid
2366           AND c.relkind = 'c'
2367           AND (pg_has_role(t.typowner, 'USAGE')
2368                OR has_type_privilege(t.oid, 'USAGE'));
2369
2370 GRANT SELECT ON user_defined_types TO PUBLIC;
2371
2372
2373 /*
2374  * 5.76
2375  * VIEW_COLUMN_USAGE
2376  */
2377
2378 CREATE VIEW view_column_usage AS
2379     SELECT DISTINCT
2380            CAST(current_database() AS sql_identifier) AS view_catalog,
2381            CAST(nv.nspname AS sql_identifier) AS view_schema,
2382            CAST(v.relname AS sql_identifier) AS view_name,
2383            CAST(current_database() AS sql_identifier) AS table_catalog,
2384            CAST(nt.nspname AS sql_identifier) AS table_schema,
2385            CAST(t.relname AS sql_identifier) AS table_name,
2386            CAST(a.attname AS sql_identifier) AS column_name
2387
2388     FROM pg_namespace nv, pg_class v, pg_depend dv,
2389          pg_depend dt, pg_class t, pg_namespace nt,
2390          pg_attribute a
2391
2392     WHERE nv.oid = v.relnamespace
2393           AND v.relkind = 'v'
2394           AND v.oid = dv.refobjid
2395           AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2396           AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2397           AND dv.deptype = 'i'
2398           AND dv.objid = dt.objid
2399           AND dv.refobjid <> dt.refobjid
2400           AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2401           AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2402           AND dt.refobjid = t.oid
2403           AND t.relnamespace = nt.oid
2404           AND t.relkind IN ('r', 'v', 'f')
2405           AND t.oid = a.attrelid
2406           AND dt.refobjsubid = a.attnum
2407           AND pg_has_role(t.relowner, 'USAGE');
2408
2409 GRANT SELECT ON view_column_usage TO PUBLIC;
2410
2411
2412 /*
2413  * 5.77
2414  * VIEW_PERIOD_USAGE
2415  */
2416
2417 -- feature not supported
2418
2419
2420 /*
2421  * 5.78
2422  * VIEW_ROUTINE_USAGE
2423  */
2424
2425 CREATE VIEW view_routine_usage AS
2426     SELECT DISTINCT
2427            CAST(current_database() AS sql_identifier) AS table_catalog,
2428            CAST(nv.nspname AS sql_identifier) AS table_schema,
2429            CAST(v.relname AS sql_identifier) AS table_name,
2430            CAST(current_database() AS sql_identifier) AS specific_catalog,
2431            CAST(np.nspname AS sql_identifier) AS specific_schema,
2432            CAST(p.proname || '_' || CAST(p.oid AS text)  AS sql_identifier) AS specific_name
2433
2434     FROM pg_namespace nv, pg_class v, pg_depend dv,
2435          pg_depend dp, pg_proc p, pg_namespace np
2436
2437     WHERE nv.oid = v.relnamespace
2438           AND v.relkind = 'v'
2439           AND v.oid = dv.refobjid
2440           AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2441           AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2442           AND dv.deptype = 'i'
2443           AND dv.objid = dp.objid
2444           AND dp.classid = 'pg_catalog.pg_rewrite'::regclass
2445           AND dp.refclassid = 'pg_catalog.pg_proc'::regclass
2446           AND dp.refobjid = p.oid
2447           AND p.pronamespace = np.oid
2448           AND pg_has_role(p.proowner, 'USAGE');
2449
2450 GRANT SELECT ON view_routine_usage TO PUBLIC;
2451
2452
2453 /*
2454  * 5.79
2455  * VIEW_TABLE_USAGE
2456  */
2457
2458 CREATE VIEW view_table_usage AS
2459     SELECT DISTINCT
2460            CAST(current_database() AS sql_identifier) AS view_catalog,
2461            CAST(nv.nspname AS sql_identifier) AS view_schema,
2462            CAST(v.relname AS sql_identifier) AS view_name,
2463            CAST(current_database() AS sql_identifier) AS table_catalog,
2464            CAST(nt.nspname AS sql_identifier) AS table_schema,
2465            CAST(t.relname AS sql_identifier) AS table_name
2466
2467     FROM pg_namespace nv, pg_class v, pg_depend dv,
2468          pg_depend dt, pg_class t, pg_namespace nt
2469
2470     WHERE nv.oid = v.relnamespace
2471           AND v.relkind = 'v'
2472           AND v.oid = dv.refobjid
2473           AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2474           AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2475           AND dv.deptype = 'i'
2476           AND dv.objid = dt.objid
2477           AND dv.refobjid <> dt.refobjid
2478           AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2479           AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2480           AND dt.refobjid = t.oid
2481           AND t.relnamespace = nt.oid
2482           AND t.relkind IN ('r', 'v', 'f')
2483           AND pg_has_role(t.relowner, 'USAGE');
2484
2485 GRANT SELECT ON view_table_usage TO PUBLIC;
2486
2487
2488 /*
2489  * 5.80
2490  * VIEWS view
2491  */
2492
2493 CREATE VIEW views AS
2494     SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
2495            CAST(nc.nspname AS sql_identifier) AS table_schema,
2496            CAST(c.relname AS sql_identifier) AS table_name,
2497
2498            CAST(
2499              CASE WHEN pg_has_role(c.relowner, 'USAGE')
2500                   THEN pg_get_viewdef(c.oid)
2501                   ELSE null END
2502              AS character_data) AS view_definition,
2503
2504            CAST(
2505              CASE WHEN 'check_option=cascaded' = ANY (c.reloptions)
2506                   THEN 'CASCADED'
2507                   WHEN 'check_option=local' = ANY (c.reloptions)
2508                   THEN 'LOCAL'
2509                   ELSE 'NONE' END
2510              AS character_data) AS check_option,
2511
2512            CAST(
2513              -- (1 << CMD_UPDATE) + (1 << CMD_DELETE)
2514              CASE WHEN pg_relation_is_updatable(c.oid, false) & 20 = 20
2515                   THEN 'YES' ELSE 'NO' END
2516              AS yes_or_no) AS is_updatable,
2517
2518            CAST(
2519              -- 1 << CMD_INSERT
2520              CASE WHEN pg_relation_is_updatable(c.oid, false) & 8 = 8
2521                   THEN 'YES' ELSE 'NO' END
2522              AS yes_or_no) AS is_insertable_into,
2523
2524            CAST(
2525              -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_UPDATE
2526              CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 81 = 81)
2527                   THEN 'YES' ELSE 'NO' END
2528            AS yes_or_no) AS is_trigger_updatable,
2529
2530            CAST(
2531              -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_DELETE
2532              CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 73 = 73)
2533                   THEN 'YES' ELSE 'NO' END
2534            AS yes_or_no) AS is_trigger_deletable,
2535
2536            CAST(
2537              -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_INSERT
2538              CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 69 = 69)
2539                   THEN 'YES' ELSE 'NO' END
2540            AS yes_or_no) AS is_trigger_insertable_into
2541
2542     FROM pg_namespace nc, pg_class c
2543
2544     WHERE c.relnamespace = nc.oid
2545           AND c.relkind = 'v'
2546           AND (NOT pg_is_other_temp_schema(nc.oid))
2547           AND (pg_has_role(c.relowner, 'USAGE')
2548                OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
2549                OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES') );
2550
2551 GRANT SELECT ON views TO PUBLIC;
2552
2553
2554 -- The following views have dependencies that force them to appear out of order.
2555
2556 /*
2557  * 5.25
2558  * DATA_TYPE_PRIVILEGES view
2559  */
2560
2561 CREATE VIEW data_type_privileges AS
2562     SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2563            CAST(x.objschema AS sql_identifier) AS object_schema,
2564            CAST(x.objname AS sql_identifier) AS object_name,
2565            CAST(x.objtype AS character_data) AS object_type,
2566            CAST(x.objdtdid AS sql_identifier) AS dtd_identifier
2567
2568     FROM
2569       (
2570         SELECT udt_schema, udt_name, 'USER-DEFINED TYPE'::text, dtd_identifier FROM attributes
2571         UNION ALL
2572         SELECT table_schema, table_name, 'TABLE'::text, dtd_identifier FROM columns
2573         UNION ALL
2574         SELECT domain_schema, domain_name, 'DOMAIN'::text, dtd_identifier FROM domains
2575         UNION ALL
2576         SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM parameters
2577         UNION ALL
2578         SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM routines
2579       ) AS x (objschema, objname, objtype, objdtdid);
2580
2581 GRANT SELECT ON data_type_privileges TO PUBLIC;
2582
2583
2584 /*
2585  * 5.30
2586  * ELEMENT_TYPES view
2587  */
2588
2589 CREATE VIEW element_types AS
2590     SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2591            CAST(n.nspname AS sql_identifier) AS object_schema,
2592            CAST(x.objname AS sql_identifier) AS object_name,
2593            CAST(x.objtype AS character_data) AS object_type,
2594            CAST(x.objdtdid AS sql_identifier) AS collection_type_identifier,
2595            CAST(
2596              CASE WHEN nbt.nspname = 'pg_catalog' THEN format_type(bt.oid, null)
2597                   ELSE 'USER-DEFINED' END AS character_data) AS data_type,
2598
2599            CAST(null AS cardinal_number) AS character_maximum_length,
2600            CAST(null AS cardinal_number) AS character_octet_length,
2601            CAST(null AS sql_identifier) AS character_set_catalog,
2602            CAST(null AS sql_identifier) AS character_set_schema,
2603            CAST(null AS sql_identifier) AS character_set_name,
2604            CAST(CASE WHEN nco.nspname IS NOT NULL THEN current_database() END AS sql_identifier) AS collation_catalog,
2605            CAST(nco.nspname AS sql_identifier) AS collation_schema,
2606            CAST(co.collname AS sql_identifier) AS collation_name,
2607            CAST(null AS cardinal_number) AS numeric_precision,
2608            CAST(null AS cardinal_number) AS numeric_precision_radix,
2609            CAST(null AS cardinal_number) AS numeric_scale,
2610            CAST(null AS cardinal_number) AS datetime_precision,
2611            CAST(null AS character_data) AS interval_type,
2612            CAST(null AS cardinal_number) AS interval_precision,
2613
2614            CAST(null AS character_data) AS domain_default, -- XXX maybe a bug in the standard
2615
2616            CAST(current_database() AS sql_identifier) AS udt_catalog,
2617            CAST(nbt.nspname AS sql_identifier) AS udt_schema,
2618            CAST(bt.typname AS sql_identifier) AS udt_name,
2619
2620            CAST(null AS sql_identifier) AS scope_catalog,
2621            CAST(null AS sql_identifier) AS scope_schema,
2622            CAST(null AS sql_identifier) AS scope_name,
2623
2624            CAST(null AS cardinal_number) AS maximum_cardinality,
2625            CAST('a' || CAST(x.objdtdid AS text) AS sql_identifier) AS dtd_identifier
2626
2627     FROM pg_namespace n, pg_type at, pg_namespace nbt, pg_type bt,
2628          (
2629            /* columns, attributes */
2630            SELECT c.relnamespace, CAST(c.relname AS sql_identifier),
2631                   CASE WHEN c.relkind = 'c' THEN 'USER-DEFINED TYPE'::text ELSE 'TABLE'::text END,
2632                   a.attnum, a.atttypid, a.attcollation
2633            FROM pg_class c, pg_attribute a
2634            WHERE c.oid = a.attrelid
2635                  AND c.relkind IN ('r', 'v', 'f', 'c')
2636                  AND attnum > 0 AND NOT attisdropped
2637
2638            UNION ALL
2639
2640            /* domains */
2641            SELECT t.typnamespace, CAST(t.typname AS sql_identifier),
2642                   'DOMAIN'::text, 1, t.typbasetype, t.typcollation
2643            FROM pg_type t
2644            WHERE t.typtype = 'd'
2645
2646            UNION ALL
2647
2648            /* parameters */
2649            SELECT pronamespace, CAST(proname || '_' || CAST(oid AS text) AS sql_identifier),
2650                   'ROUTINE'::text, (ss.x).n, (ss.x).x, 0
2651            FROM (SELECT p.pronamespace, p.proname, p.oid,
2652                         _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
2653                  FROM pg_proc p) AS ss
2654
2655            UNION ALL
2656
2657            /* result types */
2658            SELECT p.pronamespace, CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier),
2659                   'ROUTINE'::text, 0, p.prorettype, 0
2660            FROM pg_proc p
2661
2662          ) AS x (objschema, objname, objtype, objdtdid, objtypeid, objcollation)
2663          LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid))
2664            ON x.objcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default')
2665
2666     WHERE n.oid = x.objschema
2667           AND at.oid = x.objtypeid
2668           AND (at.typelem <> 0 AND at.typlen = -1)
2669           AND at.typelem = bt.oid
2670           AND nbt.oid = bt.typnamespace
2671
2672           AND (n.nspname, x.objname, x.objtype, CAST(x.objdtdid AS sql_identifier)) IN
2673               ( SELECT object_schema, object_name, object_type, dtd_identifier
2674                     FROM data_type_privileges );
2675
2676 GRANT SELECT ON element_types TO PUBLIC;
2677
2678
2679 -- SQL/MED views; these use section numbers from part 9 of the standard.
2680 -- (still SQL:2008; there is no SQL:2011 SQL/MED)
2681
2682 /* Base view for foreign table columns */
2683 CREATE VIEW _pg_foreign_table_columns AS
2684     SELECT n.nspname,
2685            c.relname,
2686            a.attname,
2687            a.attfdwoptions
2688     FROM pg_foreign_table t, pg_authid u, pg_namespace n, pg_class c,
2689          pg_attribute a
2690     WHERE u.oid = c.relowner
2691           AND (pg_has_role(c.relowner, 'USAGE')
2692                OR has_column_privilege(c.oid, a.attnum, 'SELECT, INSERT, UPDATE, REFERENCES'))
2693           AND n.oid = c.relnamespace
2694           AND c.oid = t.ftrelid
2695           AND c.relkind = 'f'
2696           AND a.attrelid = c.oid
2697           AND a.attnum > 0;
2698
2699 /*
2700  * 24.2
2701  * COLUMN_OPTIONS view
2702  */
2703 CREATE VIEW column_options AS
2704     SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
2705            c.nspname AS table_schema,
2706            c.relname AS table_name,
2707            c.attname AS column_name,
2708            CAST((pg_options_to_table(c.attfdwoptions)).option_name AS sql_identifier) AS option_name,
2709            CAST((pg_options_to_table(c.attfdwoptions)).option_value AS character_data) AS option_value
2710     FROM _pg_foreign_table_columns c;
2711
2712 GRANT SELECT ON column_options TO PUBLIC;
2713
2714
2715 /* Base view for foreign-data wrappers */
2716 CREATE VIEW _pg_foreign_data_wrappers AS
2717     SELECT w.oid,
2718            w.fdwowner,
2719            w.fdwoptions,
2720            CAST(current_database() AS sql_identifier) AS foreign_data_wrapper_catalog,
2721            CAST(fdwname AS sql_identifier) AS foreign_data_wrapper_name,
2722            CAST(u.rolname AS sql_identifier) AS authorization_identifier,
2723            CAST('c' AS character_data) AS foreign_data_wrapper_language
2724     FROM pg_foreign_data_wrapper w, pg_authid u
2725     WHERE u.oid = w.fdwowner
2726           AND (pg_has_role(fdwowner, 'USAGE')
2727                OR has_foreign_data_wrapper_privilege(w.oid, 'USAGE'));
2728
2729
2730 /*
2731  * 24.4
2732  * FOREIGN_DATA_WRAPPER_OPTIONS view
2733  */
2734 CREATE VIEW foreign_data_wrapper_options AS
2735     SELECT foreign_data_wrapper_catalog,
2736            foreign_data_wrapper_name,
2737            CAST((pg_options_to_table(w.fdwoptions)).option_name AS sql_identifier) AS option_name,
2738            CAST((pg_options_to_table(w.fdwoptions)).option_value AS character_data) AS option_value
2739     FROM _pg_foreign_data_wrappers w;
2740
2741 GRANT SELECT ON foreign_data_wrapper_options TO PUBLIC;
2742
2743
2744 /*
2745  * 24.5
2746  * FOREIGN_DATA_WRAPPERS view
2747  */
2748 CREATE VIEW foreign_data_wrappers AS
2749     SELECT foreign_data_wrapper_catalog,
2750            foreign_data_wrapper_name,
2751            authorization_identifier,
2752            CAST(NULL AS character_data) AS library_name,
2753            foreign_data_wrapper_language
2754     FROM _pg_foreign_data_wrappers w;
2755
2756 GRANT SELECT ON foreign_data_wrappers TO PUBLIC;
2757
2758
2759 /* Base view for foreign servers */
2760 CREATE VIEW _pg_foreign_servers AS
2761     SELECT s.oid,
2762            s.srvoptions,
2763            CAST(current_database() AS sql_identifier) AS foreign_server_catalog,
2764            CAST(srvname AS sql_identifier) AS foreign_server_name,
2765            CAST(current_database() AS sql_identifier) AS foreign_data_wrapper_catalog,
2766            CAST(w.fdwname AS sql_identifier) AS foreign_data_wrapper_name,
2767            CAST(srvtype AS character_data) AS foreign_server_type,
2768            CAST(srvversion AS character_data) AS foreign_server_version,
2769            CAST(u.rolname AS sql_identifier) AS authorization_identifier
2770     FROM pg_foreign_server s, pg_foreign_data_wrapper w, pg_authid u
2771     WHERE w.oid = s.srvfdw
2772           AND u.oid = s.srvowner
2773           AND (pg_has_role(s.srvowner, 'USAGE')
2774                OR has_server_privilege(s.oid, 'USAGE'));
2775
2776
2777 /*
2778  * 24.6
2779  * FOREIGN_SERVER_OPTIONS view
2780  */
2781 CREATE VIEW foreign_server_options AS
2782     SELECT foreign_server_catalog,
2783            foreign_server_name,
2784            CAST((pg_options_to_table(s.srvoptions)).option_name AS sql_identifier) AS option_name,
2785            CAST((pg_options_to_table(s.srvoptions)).option_value AS character_data) AS option_value
2786     FROM _pg_foreign_servers s;
2787
2788 GRANT SELECT ON TABLE foreign_server_options TO PUBLIC;
2789
2790
2791 /*
2792  * 24.7
2793  * FOREIGN_SERVERS view
2794  */
2795 CREATE VIEW foreign_servers AS
2796     SELECT foreign_server_catalog,
2797            foreign_server_name,
2798            foreign_data_wrapper_catalog,
2799            foreign_data_wrapper_name,
2800            foreign_server_type,
2801            foreign_server_version,
2802            authorization_identifier
2803     FROM _pg_foreign_servers;
2804
2805 GRANT SELECT ON foreign_servers TO PUBLIC;
2806
2807
2808 /* Base view for foreign tables */
2809 CREATE VIEW _pg_foreign_tables AS
2810     SELECT
2811            CAST(current_database() AS sql_identifier) AS foreign_table_catalog,
2812            n.nspname AS foreign_table_schema,
2813            c.relname AS foreign_table_name,
2814            t.ftoptions AS ftoptions,
2815            CAST(current_database() AS sql_identifier) AS foreign_server_catalog,
2816            CAST(srvname AS sql_identifier) AS foreign_server_name,
2817            CAST(u.rolname AS sql_identifier) AS authorization_identifier
2818     FROM pg_foreign_table t, pg_foreign_server s, pg_foreign_data_wrapper w,
2819          pg_authid u, pg_namespace n, pg_class c
2820     WHERE w.oid = s.srvfdw
2821           AND u.oid = c.relowner
2822           AND (pg_has_role(c.relowner, 'USAGE')
2823                OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
2824                OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'))
2825           AND n.oid = c.relnamespace
2826           AND c.oid = t.ftrelid
2827           AND c.relkind = 'f'
2828           AND s.oid = t.ftserver;
2829
2830
2831 /*
2832  * 24.8
2833  * FOREIGN_TABLE_OPTIONS view
2834  */
2835 CREATE VIEW foreign_table_options AS
2836     SELECT foreign_table_catalog,
2837            foreign_table_schema,
2838            foreign_table_name,
2839            CAST((pg_options_to_table(t.ftoptions)).option_name AS sql_identifier) AS option_name,
2840            CAST((pg_options_to_table(t.ftoptions)).option_value AS character_data) AS option_value
2841     FROM _pg_foreign_tables t;
2842
2843 GRANT SELECT ON TABLE foreign_table_options TO PUBLIC;
2844
2845
2846 /*
2847  * 24.9
2848  * FOREIGN_TABLES view
2849  */
2850 CREATE VIEW foreign_tables AS
2851     SELECT foreign_table_catalog,
2852            foreign_table_schema,
2853            foreign_table_name,
2854            foreign_server_catalog,
2855            foreign_server_name
2856     FROM _pg_foreign_tables;
2857
2858 GRANT SELECT ON foreign_tables TO PUBLIC;
2859
2860
2861
2862 /* Base view for user mappings */
2863 CREATE VIEW _pg_user_mappings AS
2864     SELECT um.oid,
2865            um.umoptions,
2866            um.umuser,
2867            CAST(COALESCE(u.rolname,'PUBLIC') AS sql_identifier ) AS authorization_identifier,
2868            s.foreign_server_catalog,
2869            s.foreign_server_name,
2870            s.authorization_identifier AS srvowner
2871     FROM pg_user_mapping um LEFT JOIN pg_authid u ON (u.oid = um.umuser),
2872          _pg_foreign_servers s
2873     WHERE s.oid = um.umserver;
2874
2875
2876 /*
2877  * 24.12
2878  * USER_MAPPING_OPTIONS view
2879  */
2880 CREATE VIEW user_mapping_options AS
2881     SELECT authorization_identifier,
2882            foreign_server_catalog,
2883            foreign_server_name,
2884            CAST((pg_options_to_table(um.umoptions)).option_name AS sql_identifier) AS option_name,
2885            CAST(CASE WHEN (umuser <> 0 AND authorization_identifier = current_user)
2886                        OR (umuser = 0 AND pg_has_role(srvowner, 'USAGE'))
2887                        OR (SELECT rolsuper FROM pg_authid WHERE rolname = current_user) THEN (pg_options_to_table(um.umoptions)).option_value
2888                      ELSE NULL END AS character_data) AS option_value
2889     FROM _pg_user_mappings um;
2890
2891 GRANT SELECT ON user_mapping_options TO PUBLIC;
2892
2893
2894 /*
2895  * 24.13
2896  * USER_MAPPINGS view
2897  */
2898 CREATE VIEW user_mappings AS
2899     SELECT authorization_identifier,
2900            foreign_server_catalog,
2901            foreign_server_name
2902     FROM _pg_user_mappings;
2903
2904 GRANT SELECT ON user_mappings TO PUBLIC;