src/backend/catalog/information_schema.sql

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