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

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