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-2008, PostgreSQL Global Development Group
   6  *
   7  * $PostgreSQL: pgsql/src/backend/catalog/information_schema.sql,v 1.46 2008/09/08 00:47:40 tgl 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 -- See USAGE_PRIVILEGES.
1248
1249 CREATE VIEW role_usage_grants AS
1250     SELECT CAST(null AS sql_identifier) AS grantor,
1251            CAST(null AS sql_identifier) AS grantee,
1252            CAST(current_database() AS sql_identifier) AS object_catalog,
1253            CAST(null AS sql_identifier) AS object_schema,
1254            CAST(null AS sql_identifier) AS object_name,
1255            CAST(null AS character_data) AS object_type,
1256            CAST('USAGE' AS character_data) AS privilege_type,
1257            CAST(null AS character_data) AS is_grantable
1258
1259     WHERE false;
1260
1261 GRANT SELECT ON role_usage_grants TO PUBLIC;
1262
1263
1264 /*
1265  * 5.42
1266  * ROLE_UDT_GRANTS view
1267  */
1268
1269 -- feature not supported
1270
1271
1272 /*
1273  * 5.43
1274  * ROUTINE_COLUMN_USAGE view
1275  */
1276
1277 -- not tracked by PostgreSQL
1278
1279
1280 /*
1281  * 5.44
1282  * ROUTINE_PRIVILEGES view
1283  */
1284
1285 CREATE VIEW routine_privileges AS
1286     SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1287            CAST(grantee.rolname AS sql_identifier) AS grantee,
1288            CAST(current_database() AS sql_identifier) AS specific_catalog,
1289            CAST(n.nspname AS sql_identifier) AS specific_schema,
1290            CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1291            CAST(current_database() AS sql_identifier) AS routine_catalog,
1292            CAST(n.nspname AS sql_identifier) AS routine_schema,
1293            CAST(p.proname AS sql_identifier) AS routine_name,
1294            CAST('EXECUTE' AS character_data) AS privilege_type,
1295            CAST(
1296              CASE WHEN aclcontains(p.proacl,
1297                                    makeaclitem(grantee.oid, u_grantor.oid, 'EXECUTE', true))
1298                   THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
1299
1300     FROM pg_proc p,
1301          pg_namespace n,
1302          pg_authid u_grantor,
1303          (
1304            SELECT oid, rolname FROM pg_authid
1305            UNION ALL
1306            SELECT 0::oid, 'PUBLIC'
1307          ) AS grantee (oid, rolname)
1308
1309     WHERE p.pronamespace = n.oid
1310           AND aclcontains(p.proacl,
1311                           makeaclitem(grantee.oid, u_grantor.oid, 'EXECUTE', false))
1312           AND (pg_has_role(u_grantor.oid, 'USAGE')
1313                OR pg_has_role(grantee.oid, 'USAGE')
1314                OR grantee.rolname = 'PUBLIC');
1315
1316 GRANT SELECT ON routine_privileges TO PUBLIC;
1317
1318
1319 /*
1320  * 5.45
1321  * ROUTINE_ROUTINE_USAGE view
1322  */
1323
1324 -- not tracked by PostgreSQL
1325
1326
1327 /*
1328  * 5.46
1329  * ROUTINE_SEQUENCE_USAGE view
1330  */
1331
1332 -- not tracked by PostgreSQL
1333
1334
1335 /*
1336  * 5.47
1337  * ROUTINE_TABLE_USAGE view
1338  */
1339
1340 -- not tracked by PostgreSQL
1341
1342
1343 /*
1344  * 5.48
1345  * ROUTINES view
1346  */
1347
1348 CREATE VIEW routines AS
1349     SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
1350            CAST(n.nspname AS sql_identifier) AS specific_schema,
1351            CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1352            CAST(current_database() AS sql_identifier) AS routine_catalog,
1353            CAST(n.nspname AS sql_identifier) AS routine_schema,
1354            CAST(p.proname AS sql_identifier) AS routine_name,
1355            CAST('FUNCTION' AS character_data) AS routine_type,
1356            CAST(null AS sql_identifier) AS module_catalog,
1357            CAST(null AS sql_identifier) AS module_schema,
1358            CAST(null AS sql_identifier) AS module_name,
1359            CAST(null AS sql_identifier) AS udt_catalog,
1360            CAST(null AS sql_identifier) AS udt_schema,
1361            CAST(null AS sql_identifier) AS udt_name,
1362
1363            CAST(
1364              CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
1365                   WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
1366                   ELSE 'USER-DEFINED' END AS character_data)
1367              AS data_type,
1368            CAST(null AS cardinal_number) AS character_maximum_length,
1369            CAST(null AS cardinal_number) AS character_octet_length,
1370            CAST(null AS sql_identifier) AS character_set_catalog,
1371            CAST(null AS sql_identifier) AS character_set_schema,
1372            CAST(null AS sql_identifier) AS character_set_name,
1373            CAST(null AS sql_identifier) AS collation_catalog,
1374            CAST(null AS sql_identifier) AS collation_schema,
1375            CAST(null AS sql_identifier) AS collation_name,
1376            CAST(null AS cardinal_number) AS numeric_precision,
1377            CAST(null AS cardinal_number) AS numeric_precision_radix,
1378            CAST(null AS cardinal_number) AS numeric_scale,
1379            CAST(null AS cardinal_number) AS datetime_precision,
1380            CAST(null AS character_data) AS interval_type,
1381            CAST(null AS character_data) AS interval_precision,
1382            CAST(current_database() AS sql_identifier) AS type_udt_catalog,
1383            CAST(nt.nspname AS sql_identifier) AS type_udt_schema,
1384            CAST(t.typname AS sql_identifier) AS type_udt_name,
1385            CAST(null AS sql_identifier) AS scope_catalog,
1386            CAST(null AS sql_identifier) AS scope_schema,
1387            CAST(null AS sql_identifier) AS scope_name,
1388            CAST(null AS cardinal_number) AS maximum_cardinality,
1389            CAST(0 AS sql_identifier) AS dtd_identifier,
1390
1391            CAST(CASE WHEN l.lanname = 'sql' THEN 'SQL' ELSE 'EXTERNAL' END AS character_data)
1392              AS routine_body,
1393            CAST(
1394              CASE WHEN pg_has_role(p.proowner, 'USAGE') THEN p.prosrc ELSE null END
1395              AS character_data) AS routine_definition,
1396            CAST(
1397              CASE WHEN l.lanname = 'c' THEN p.prosrc ELSE null END
1398              AS character_data) AS external_name,
1399            CAST(upper(l.lanname) AS character_data) AS external_language,
1400
1401            CAST('GENERAL' AS character_data) AS parameter_style,
1402            CAST(CASE WHEN p.provolatile = 'i' THEN 'YES' ELSE 'NO' END AS character_data) AS is_deterministic,
1403            CAST('MODIFIES' AS character_data) AS sql_data_access,
1404            CAST(CASE WHEN p.proisstrict THEN 'YES' ELSE 'NO' END AS character_data) AS is_null_call,
1405            CAST(null AS character_data) AS sql_path,
1406            CAST('YES' AS character_data) AS schema_level_routine,
1407            CAST(0 AS cardinal_number) AS max_dynamic_result_sets,
1408            CAST(null AS character_data) AS is_user_defined_cast,
1409            CAST(null AS character_data) AS is_implicitly_invocable,
1410            CAST(CASE WHEN p.prosecdef THEN 'DEFINER' ELSE 'INVOKER' END AS character_data) AS security_type,
1411            CAST(null AS sql_identifier) AS to_sql_specific_catalog,
1412            CAST(null AS sql_identifier) AS to_sql_specific_schema,
1413            CAST(null AS sql_identifier) AS to_sql_specific_name,
1414            CAST('NO' AS character_data) AS as_locator,
1415            CAST(null AS time_stamp) AS created,
1416            CAST(null AS time_stamp) AS last_altered,
1417            CAST(null AS character_data) AS new_savepoint_level,
1418            CAST('YES' AS character_data) AS is_udt_dependent, -- FIXME?
1419
1420            CAST(null AS character_data) AS result_cast_from_data_type,
1421            CAST(null AS character_data) AS result_cast_as_locator,
1422            CAST(null AS cardinal_number) AS result_cast_char_max_length,
1423            CAST(null AS cardinal_number) AS result_cast_char_octet_length,
1424            CAST(null AS sql_identifier) AS result_cast_char_set_catalog,
1425            CAST(null AS sql_identifier) AS result_cast_char_set_schema,
1426            CAST(null AS sql_identifier) AS result_cast_character_set_name,
1427            CAST(null AS sql_identifier) AS result_cast_collation_catalog,
1428            CAST(null AS sql_identifier) AS result_cast_collation_schema,
1429            CAST(null AS sql_identifier) AS result_cast_collation_name,
1430            CAST(null AS cardinal_number) AS result_cast_numeric_precision,
1431            CAST(null AS cardinal_number) AS result_cast_numeric_precision_radix,
1432            CAST(null AS cardinal_number) AS result_cast_numeric_scale,
1433            CAST(null AS cardinal_number) AS result_cast_datetime_precision,
1434            CAST(null AS character_data) AS result_cast_interval_type,
1435            CAST(null AS character_data) AS result_cast_interval_precision,
1436            CAST(null AS sql_identifier) AS result_cast_type_udt_catalog,
1437            CAST(null AS sql_identifier) AS result_cast_type_udt_schema,
1438            CAST(null AS sql_identifier) AS result_cast_type_udt_name,
1439            CAST(null AS sql_identifier) AS result_cast_scope_catalog,
1440            CAST(null AS sql_identifier) AS result_cast_scope_schema,
1441            CAST(null AS sql_identifier) AS result_cast_scope_name,
1442            CAST(null AS cardinal_number) AS result_cast_maximum_cardinality,
1443            CAST(null AS sql_identifier) AS result_cast_dtd_identifier
1444
1445     FROM pg_namespace n, pg_proc p, pg_language l,
1446          pg_type t, pg_namespace nt
1447
1448     WHERE n.oid = p.pronamespace AND p.prolang = l.oid
1449           AND p.prorettype = t.oid AND t.typnamespace = nt.oid
1450           AND (pg_has_role(p.proowner, 'USAGE')
1451                OR has_function_privilege(p.oid, 'EXECUTE'));
1452
1453 GRANT SELECT ON routines TO PUBLIC;
1454
1455
1456 /*
1457  * 5.49
1458  * SCHEMATA view
1459  */
1460
1461 CREATE VIEW schemata AS
1462     SELECT CAST(current_database() AS sql_identifier) AS catalog_name,
1463            CAST(n.nspname AS sql_identifier) AS schema_name,
1464            CAST(u.rolname AS sql_identifier) AS schema_owner,
1465            CAST(null AS sql_identifier) AS default_character_set_catalog,
1466            CAST(null AS sql_identifier) AS default_character_set_schema,
1467            CAST(null AS sql_identifier) AS default_character_set_name,
1468            CAST(null AS character_data) AS sql_path
1469     FROM pg_namespace n, pg_authid u
1470     WHERE n.nspowner = u.oid AND pg_has_role(n.nspowner, 'USAGE');
1471
1472 GRANT SELECT ON schemata TO PUBLIC;
1473
1474
1475 /*
1476  * 5.50
1477  * SEQUENCES view
1478  */
1479
1480 CREATE VIEW sequences AS
1481     SELECT CAST(current_database() AS sql_identifier) AS sequence_catalog,
1482            CAST(nc.nspname AS sql_identifier) AS sequence_schema,
1483            CAST(c.relname AS sql_identifier) AS sequence_name,
1484            CAST('bigint' AS character_data) AS data_type,
1485            CAST(64 AS cardinal_number) AS numeric_precision,
1486            CAST(2 AS cardinal_number) AS numeric_precision_radix,
1487            CAST(0 AS cardinal_number) AS numeric_scale,
1488            CAST(null AS cardinal_number) AS maximum_value, -- FIXME
1489            CAST(null AS cardinal_number) AS minimum_value, -- FIXME
1490            CAST(null AS cardinal_number) AS increment,     -- FIXME
1491            CAST(null AS character_data) AS cycle_option    -- FIXME
1492     FROM pg_namespace nc, pg_class c
1493     WHERE c.relnamespace = nc.oid
1494           AND c.relkind = 'S'
1495           AND (NOT pg_is_other_temp_schema(nc.oid))
1496           AND (pg_has_role(c.relowner, 'USAGE')
1497                OR has_table_privilege(c.oid, 'SELECT')
1498                OR has_table_privilege(c.oid, 'UPDATE') );
1499
1500 GRANT SELECT ON sequences TO PUBLIC;
1501
1502
1503 /*
1504  * 5.51
1505  * SQL_FEATURES table
1506  */
1507
1508 CREATE TABLE sql_features (
1509     feature_id          character_data,
1510     feature_name        character_data,
1511     sub_feature_id      character_data,
1512     sub_feature_name    character_data,
1513     is_supported        character_data,
1514     is_verified_by      character_data,
1515     comments            character_data
1516 ) WITHOUT OIDS;
1517
1518 -- Will be filled with external data by initdb.
1519
1520 GRANT SELECT ON sql_features TO PUBLIC;
1521
1522
1523 /*
1524  * 5.52
1525  * SQL_IMPLEMENTATION_INFO table
1526  */
1527
1528 -- Note: Implementation information items are defined in ISO/IEC 9075-3:2003,
1529 -- clause 7.1.
1530
1531 CREATE TABLE sql_implementation_info (
1532     implementation_info_id      character_data,
1533     implementation_info_name    character_data,
1534     integer_value               cardinal_number,
1535     character_value             character_data,
1536     comments                    character_data
1537 ) WITHOUT OIDS;
1538
1539 INSERT INTO sql_implementation_info VALUES ('10003', 'CATALOG NAME', NULL, 'Y', NULL);
1540 INSERT INTO sql_implementation_info VALUES ('10004', 'COLLATING SEQUENCE', NULL, '', 'not supported');
1541 INSERT INTO sql_implementation_info VALUES ('23',    'CURSOR COMMIT BEHAVIOR', 1, NULL, 'close cursors and retain prepared statements');
1542 INSERT INTO sql_implementation_info VALUES ('2',     'DATA SOURCE NAME', NULL, '', NULL);
1543 INSERT INTO sql_implementation_info VALUES ('17',    'DBMS NAME', NULL, (select trim(trailing ' ' from substring(version() from '^[^0-9]*'))), NULL);
1544 INSERT INTO sql_implementation_info VALUES ('18',    'DBMS VERSION', NULL, '???', NULL); -- filled by initdb
1545 INSERT INTO sql_implementation_info VALUES ('26',    'DEFAULT TRANSACTION ISOLATION', 2, NULL, 'READ COMMITTED; user-settable');
1546 INSERT INTO sql_implementation_info VALUES ('28',    'IDENTIFIER CASE', 3, NULL, 'stored in mixed case - case sensitive');
1547 INSERT INTO sql_implementation_info VALUES ('85',    'NULL COLLATION', 0, NULL, 'nulls higher than non-nulls');
1548 INSERT INTO sql_implementation_info VALUES ('13',    'SERVER NAME', NULL, '', NULL);
1549 INSERT INTO sql_implementation_info VALUES ('94',    'SPECIAL CHARACTERS', NULL, '', 'all non-ASCII characters allowed');
1550 INSERT INTO sql_implementation_info VALUES ('46',    'TRANSACTION CAPABLE', 2, NULL, 'both DML and DDL');
1551
1552 GRANT SELECT ON sql_implementation_info TO PUBLIC;
1553
1554
1555 /*
1556  * 5.53
1557  * SQL_LANGUAGES table
1558  */
1559
1560 CREATE TABLE sql_languages (
1561     sql_language_source         character_data,
1562     sql_language_year           character_data,
1563     sql_language_conformance    character_data,
1564     sql_language_integrity      character_data,
1565     sql_language_implementation character_data,
1566     sql_language_binding_style  character_data,
1567     sql_language_programming_language character_data
1568 ) WITHOUT OIDS;
1569
1570 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'DIRECT', NULL);
1571 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
1572 INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'DIRECT', NULL);
1573 INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
1574
1575 GRANT SELECT ON sql_languages TO PUBLIC;
1576
1577
1578 /*
1579  * 5.54
1580  * SQL_PACKAGES table
1581  */
1582
1583 CREATE TABLE sql_packages (
1584     feature_id      character_data,
1585     feature_name    character_data,
1586     is_supported    character_data,
1587     is_verified_by  character_data,
1588     comments        character_data
1589 ) WITHOUT OIDS;
1590
1591 INSERT INTO sql_packages VALUES ('PKG000', 'Core', 'NO', NULL, '');
1592 INSERT INTO sql_packages VALUES ('PKG001', 'Enhanced datetime facilities', 'YES', NULL, '');
1593 INSERT INTO sql_packages VALUES ('PKG002', 'Enhanced integrity management', 'NO', NULL, '');
1594 INSERT INTO sql_packages VALUES ('PKG003', 'OLAP facilities', 'NO', NULL, '');
1595 INSERT INTO sql_packages VALUES ('PKG004', 'PSM', 'NO', NULL, 'PL/pgSQL is similar.');
1596 INSERT INTO sql_packages VALUES ('PKG005', 'CLI', 'NO', NULL, 'ODBC is similar.');
1597 INSERT INTO sql_packages VALUES ('PKG006', 'Basic object support', 'NO', NULL, '');
1598 INSERT INTO sql_packages VALUES ('PKG007', 'Enhanced object support', 'NO', NULL, '');
1599 INSERT INTO sql_packages VALUES ('PKG008', 'Active database', 'NO', NULL, '');
1600 INSERT INTO sql_packages VALUES ('PKG010', 'OLAP', 'NO', NULL, 'NO');
1601
1602 GRANT SELECT ON sql_packages TO PUBLIC;
1603
1604
1605 /*
1606  * 5.55
1607  * SQL_PARTS table
1608  */
1609
1610 CREATE TABLE sql_parts (
1611     feature_id      character_data,
1612     feature_name    character_data,
1613     is_supported    character_data,
1614     is_verified_by  character_data,
1615     comments        character_data
1616 ) WITHOUT OIDS;
1617
1618 INSERT INTO sql_parts VALUES ('1', 'Framework (SQL/Framework)', 'NO', NULL, '');
1619 INSERT INTO sql_parts VALUES ('2', 'Foundation (SQL/Foundation)', 'NO', NULL, '');
1620 INSERT INTO sql_parts VALUES ('3', 'Call-Level Interface (SQL/CLI)', 'NO', NULL, '');
1621 INSERT INTO sql_parts VALUES ('4', 'Persistent Stored Modules (SQL/PSM)', 'NO', NULL, '');
1622 INSERT INTO sql_parts VALUES ('9', 'Management of External Data (SQL/MED)', 'NO', NULL, '');
1623 INSERT INTO sql_parts VALUES ('10', 'Object Language Bindings (SQL/OLB)', 'NO', NULL, '');
1624 INSERT INTO sql_parts VALUES ('11', 'Information and Definition Schema (SQL/Schemata)', 'NO', NULL, '');
1625 INSERT INTO sql_parts VALUES ('13', 'Routines and Types Using the Java Programming Language (SQL/JRT)', 'NO', NULL, '');
1626 INSERT INTO sql_parts VALUES ('14', 'XML-Related Specifications (SQL/XML)', 'YES', NULL, '');
1627
1628
1629 /*
1630  * 5.56
1631  * SQL_SIZING table
1632  */
1633
1634 -- Note: Sizing items are defined in ISO/IEC 9075-3:2003, clause 7.2.
1635
1636 CREATE TABLE sql_sizing (
1637     sizing_id       cardinal_number,
1638     sizing_name     character_data,
1639     supported_value cardinal_number,
1640     comments        character_data
1641 ) WITHOUT OIDS;
1642
1643 INSERT INTO sql_sizing VALUES (34,    'MAXIMUM CATALOG NAME LENGTH', 63, NULL);
1644 INSERT INTO sql_sizing VALUES (30,    'MAXIMUM COLUMN NAME LENGTH', 63, NULL);
1645 INSERT INTO sql_sizing VALUES (97,    'MAXIMUM COLUMNS IN GROUP BY', 0, NULL);
1646 INSERT INTO sql_sizing VALUES (99,    'MAXIMUM COLUMNS IN ORDER BY', 0, NULL);
1647 INSERT INTO sql_sizing VALUES (100,   'MAXIMUM COLUMNS IN SELECT', 1664, NULL); -- match MaxTupleAttributeNumber
1648 INSERT INTO sql_sizing VALUES (101,   'MAXIMUM COLUMNS IN TABLE', 1600, NULL); -- match MaxHeapAttributeNumber
1649 INSERT INTO sql_sizing VALUES (1,     'MAXIMUM CONCURRENT ACTIVITIES', 0, NULL);
1650 INSERT INTO sql_sizing VALUES (31,    'MAXIMUM CURSOR NAME LENGTH', 63, NULL);
1651 INSERT INTO sql_sizing VALUES (0,     'MAXIMUM DRIVER CONNECTIONS', NULL, NULL);
1652 INSERT INTO sql_sizing VALUES (10005, 'MAXIMUM IDENTIFIER LENGTH', 63, NULL);
1653 INSERT INTO sql_sizing VALUES (32,    'MAXIMUM SCHEMA NAME LENGTH', 63, NULL);
1654 INSERT INTO sql_sizing VALUES (20000, 'MAXIMUM STATEMENT OCTETS', 0, NULL);
1655 INSERT INTO sql_sizing VALUES (20001, 'MAXIMUM STATEMENT OCTETS DATA', 0, NULL);
1656 INSERT INTO sql_sizing VALUES (20002, 'MAXIMUM STATEMENT OCTETS SCHEMA', 0, NULL);
1657 INSERT INTO sql_sizing VALUES (35,    'MAXIMUM TABLE NAME LENGTH', 63, NULL);
1658 INSERT INTO sql_sizing VALUES (106,   'MAXIMUM TABLES IN SELECT', 0, NULL);
1659 INSERT INTO sql_sizing VALUES (107,   'MAXIMUM USER NAME LENGTH', 63, NULL);
1660 INSERT INTO sql_sizing VALUES (25000, 'MAXIMUM CURRENT DEFAULT TRANSFORM GROUP LENGTH', NULL, NULL);
1661 INSERT INTO sql_sizing VALUES (25001, 'MAXIMUM CURRENT TRANSFORM GROUP LENGTH', NULL, NULL);
1662 INSERT INTO sql_sizing VALUES (25002, 'MAXIMUM CURRENT PATH LENGTH', 0, NULL);
1663 INSERT INTO sql_sizing VALUES (25003, 'MAXIMUM CURRENT ROLE LENGTH', NULL, NULL);
1664 INSERT INTO sql_sizing VALUES (25004, 'MAXIMUM SESSION USER LENGTH', 63, NULL);
1665 INSERT INTO sql_sizing VALUES (25005, 'MAXIMUM SYSTEM USER LENGTH', 63, NULL);
1666
1667 UPDATE sql_sizing
1668     SET supported_value = (SELECT typlen-1 FROM pg_catalog.pg_type WHERE typname = 'name'),
1669         comments = 'Might be less, depending on character set.'
1670     WHERE supported_value = 63;
1671
1672 GRANT SELECT ON sql_sizing TO PUBLIC;
1673
1674
1675 /*
1676  * 5.57
1677  * SQL_SIZING_PROFILES table
1678  */
1679
1680 -- The data in this table are defined by various profiles of SQL.
1681 -- Since we don't have any information about such profiles, we provide
1682 -- an empty table.
1683
1684 CREATE TABLE sql_sizing_profiles (
1685     sizing_id       cardinal_number,
1686     sizing_name     character_data,
1687     profile_id      character_data,
1688     required_value  cardinal_number,
1689     comments        character_data
1690 ) WITHOUT OIDS;
1691
1692 GRANT SELECT ON sql_sizing_profiles TO PUBLIC;
1693
1694
1695 /*
1696  * 5.58
1697  * TABLE_CONSTRAINTS view
1698  */
1699
1700 CREATE VIEW table_constraints AS
1701     SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1702            CAST(nc.nspname AS sql_identifier) AS constraint_schema,
1703            CAST(c.conname AS sql_identifier) AS constraint_name,
1704            CAST(current_database() AS sql_identifier) AS table_catalog,
1705            CAST(nr.nspname AS sql_identifier) AS table_schema,
1706            CAST(r.relname AS sql_identifier) AS table_name,
1707            CAST(
1708              CASE c.contype WHEN 'c' THEN 'CHECK'
1709                             WHEN 'f' THEN 'FOREIGN KEY'
1710                             WHEN 'p' THEN 'PRIMARY KEY'
1711                             WHEN 'u' THEN 'UNIQUE' END
1712              AS character_data) AS constraint_type,
1713            CAST(CASE WHEN c.condeferrable THEN 'YES' ELSE 'NO' END AS character_data)
1714              AS is_deferrable,
1715            CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS character_data)
1716              AS initially_deferred
1717
1718     FROM pg_namespace nc,
1719          pg_namespace nr,
1720          pg_constraint c,
1721          pg_class r
1722
1723     WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace
1724           AND c.conrelid = r.oid
1725           AND r.relkind = 'r'
1726           AND (NOT pg_is_other_temp_schema(nr.oid))
1727           AND (pg_has_role(r.relowner, 'USAGE')
1728                -- SELECT privilege omitted, per SQL standard
1729                OR has_table_privilege(r.oid, 'INSERT')
1730                OR has_table_privilege(r.oid, 'UPDATE')
1731                OR has_table_privilege(r.oid, 'DELETE')
1732                OR has_table_privilege(r.oid, 'TRUNCATE')
1733                OR has_table_privilege(r.oid, 'REFERENCES')
1734                OR has_table_privilege(r.oid, 'TRIGGER') )
1735
1736     UNION
1737
1738     -- not-null constraints
1739
1740     SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1741            CAST(nr.nspname AS sql_identifier) AS constraint_schema,
1742            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
1743            CAST(current_database() AS sql_identifier) AS table_catalog,
1744            CAST(nr.nspname AS sql_identifier) AS table_schema,
1745            CAST(r.relname AS sql_identifier) AS table_name,
1746            CAST('CHECK' AS character_data) AS constraint_type,
1747            CAST('NO' AS character_data) AS is_deferrable,
1748            CAST('NO' AS character_data) AS initially_deferred
1749
1750     FROM pg_namespace nr,
1751          pg_class r,
1752          pg_attribute a
1753
1754     WHERE nr.oid = r.relnamespace
1755           AND r.oid = a.attrelid
1756           AND a.attnotnull
1757           AND a.attnum > 0
1758           AND NOT a.attisdropped
1759           AND r.relkind = 'r'
1760           AND (NOT pg_is_other_temp_schema(nr.oid))
1761           AND (pg_has_role(r.relowner, 'USAGE')
1762                OR has_table_privilege(r.oid, 'SELECT')
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 GRANT SELECT ON table_constraints TO PUBLIC;
1771
1772
1773 /*
1774  * 5.59
1775  * TABLE_METHOD_PRIVILEGES view
1776  */
1777
1778 -- feature not supported
1779
1780
1781 /*
1782  * 5.60
1783  * TABLE_PRIVILEGES view
1784  */
1785
1786 CREATE VIEW table_privileges AS
1787     SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1788            CAST(grantee.rolname AS sql_identifier) AS grantee,
1789            CAST(current_database() AS sql_identifier) AS table_catalog,
1790            CAST(nc.nspname AS sql_identifier) AS table_schema,
1791            CAST(c.relname AS sql_identifier) AS table_name,
1792            CAST(pr.type AS character_data) AS privilege_type,
1793            CAST(
1794              CASE WHEN aclcontains(c.relacl,
1795                                    makeaclitem(grantee.oid, u_grantor.oid, pr.type, true))
1796                   THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable,
1797            CAST('NO' AS character_data) AS with_hierarchy
1798
1799     FROM pg_class c,
1800          pg_namespace nc,
1801          pg_authid u_grantor,
1802          (
1803            SELECT oid, rolname FROM pg_authid
1804            UNION ALL
1805            SELECT 0::oid, 'PUBLIC'
1806          ) AS grantee (oid, rolname),
1807          (SELECT 'SELECT' UNION ALL
1808           SELECT 'INSERT' UNION ALL
1809           SELECT 'UPDATE' UNION ALL
1810           SELECT 'DELETE' UNION ALL
1811           SELECT 'TRUNCATE' UNION ALL
1812           SELECT 'REFERENCES' UNION ALL
1813           SELECT 'TRIGGER') AS pr (type)
1814
1815     WHERE c.relnamespace = nc.oid
1816           AND c.relkind IN ('r', 'v')
1817           AND aclcontains(c.relacl,
1818                           makeaclitem(grantee.oid, u_grantor.oid, pr.type, false))
1819           AND (pg_has_role(u_grantor.oid, 'USAGE')
1820                OR pg_has_role(grantee.oid, 'USAGE')
1821                OR grantee.rolname = 'PUBLIC');
1822
1823 GRANT SELECT ON table_privileges TO PUBLIC;
1824
1825
1826 /*
1827  * 5.61
1828  * TABLES view
1829  */
1830
1831 CREATE VIEW tables AS
1832     SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
1833            CAST(nc.nspname AS sql_identifier) AS table_schema,
1834            CAST(c.relname AS sql_identifier) AS table_name,
1835
1836            CAST(
1837              CASE WHEN nc.oid = pg_my_temp_schema() THEN 'LOCAL TEMPORARY'
1838                   WHEN c.relkind = 'r' THEN 'BASE TABLE'
1839                   WHEN c.relkind = 'v' THEN 'VIEW'
1840                   ELSE null END
1841              AS character_data) AS table_type,
1842
1843            CAST(null AS sql_identifier) AS self_referencing_column_name,
1844            CAST(null AS character_data) AS reference_generation,
1845
1846            CAST(null AS sql_identifier) AS user_defined_type_catalog,
1847            CAST(null AS sql_identifier) AS user_defined_type_schema,
1848            CAST(null AS sql_identifier) AS user_defined_type_name,
1849
1850            CAST(CASE WHEN c.relkind = 'r'
1851                 THEN 'YES' ELSE 'NO' END AS character_data) AS is_insertable_into,
1852            CAST('NO' AS character_data) AS is_typed,
1853            CAST(
1854              CASE WHEN nc.oid = pg_my_temp_schema() THEN 'PRESERVE' -- FIXME
1855                   ELSE null END
1856              AS character_data) AS commit_action
1857
1858     FROM pg_namespace nc, pg_class c
1859
1860     WHERE c.relnamespace = nc.oid
1861           AND c.relkind IN ('r', 'v')
1862           AND (NOT pg_is_other_temp_schema(nc.oid))
1863           AND (pg_has_role(c.relowner, 'USAGE')
1864                OR has_table_privilege(c.oid, 'SELECT')
1865                OR has_table_privilege(c.oid, 'INSERT')
1866                OR has_table_privilege(c.oid, 'UPDATE')
1867                OR has_table_privilege(c.oid, 'DELETE')
1868                OR has_table_privilege(c.oid, 'TRUNCATE')
1869                OR has_table_privilege(c.oid, 'REFERENCES')
1870                OR has_table_privilege(c.oid, 'TRIGGER') );
1871
1872 GRANT SELECT ON tables TO PUBLIC;
1873
1874
1875 /*
1876  * 5.62
1877  * TRANSFORMS view
1878  */
1879
1880 -- feature not supported
1881
1882
1883 /*
1884  * 5.63
1885  * TRANSLATIONS view
1886  */
1887
1888 -- feature not supported
1889
1890
1891 /*
1892  * 5.64
1893  * TRIGGERED_UPDATE_COLUMNS view
1894  */
1895
1896 -- PostgreSQL doesn't allow the specification of individual triggered
1897 -- update columns, so this view is empty.
1898
1899 CREATE VIEW triggered_update_columns AS
1900     SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1901            CAST(null AS sql_identifier) AS trigger_schema,
1902            CAST(null AS sql_identifier) AS trigger_name,
1903            CAST(current_database() AS sql_identifier) AS event_object_catalog,
1904            CAST(null AS sql_identifier) AS event_object_schema,
1905            CAST(null AS sql_identifier) AS event_object_table,
1906            CAST(null AS sql_identifier) AS event_object_column
1907     WHERE false;
1908
1909 GRANT SELECT ON triggered_update_columns TO PUBLIC;
1910
1911
1912 /*
1913  * 5.65
1914  * TRIGGER_COLUMN_USAGE view
1915  */
1916
1917 -- not tracked by PostgreSQL
1918
1919
1920 /*
1921  * 5.66
1922  * TRIGGER_ROUTINE_USAGE view
1923  */
1924
1925 -- not tracked by PostgreSQL
1926
1927
1928 /*
1929  * 5.67
1930  * TRIGGER_SEQUENCE_USAGE view
1931  */
1932
1933 -- not tracked by PostgreSQL
1934
1935
1936 /*
1937  * 5.68
1938  * TRIGGER_TABLE_USAGE view
1939  */
1940
1941 -- not tracked by PostgreSQL
1942
1943
1944 /*
1945  * 5.69
1946  * TRIGGERS view
1947  */
1948
1949 CREATE VIEW triggers AS
1950     SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1951            CAST(n.nspname AS sql_identifier) AS trigger_schema,
1952            CAST(t.tgname AS sql_identifier) AS trigger_name,
1953            CAST(em.text AS character_data) AS event_manipulation,
1954            CAST(current_database() AS sql_identifier) AS event_object_catalog,
1955            CAST(n.nspname AS sql_identifier) AS event_object_schema,
1956            CAST(c.relname AS sql_identifier) AS event_object_table,
1957            CAST(null AS cardinal_number) AS action_order,
1958            CAST(null AS character_data) AS action_condition,
1959            CAST(
1960              substring(pg_get_triggerdef(t.oid) from
1961                        position('EXECUTE PROCEDURE' in substring(pg_get_triggerdef(t.oid) from 48)) + 47)
1962              AS character_data) AS action_statement,
1963            CAST(
1964              CASE WHEN t.tgtype & 1 = 1 THEN 'ROW' ELSE 'STATEMENT' END
1965              AS character_data) AS action_orientation,
1966            CAST(
1967              CASE WHEN t.tgtype & 2 = 2 THEN 'BEFORE' ELSE 'AFTER' END
1968              AS character_data) AS condition_timing,
1969            CAST(null AS sql_identifier) AS condition_reference_old_table,
1970            CAST(null AS sql_identifier) AS condition_reference_new_table,
1971            CAST(null AS sql_identifier) AS condition_reference_old_row,
1972            CAST(null AS sql_identifier) AS condition_reference_new_row,
1973            CAST(null AS time_stamp) AS created
1974
1975     FROM pg_namespace n, pg_class c, pg_trigger t,
1976          (SELECT 4, 'INSERT' UNION ALL
1977           SELECT 8, 'DELETE' UNION ALL
1978           SELECT 16, 'UPDATE') AS em (num, text)
1979
1980     WHERE n.oid = c.relnamespace
1981           AND c.oid = t.tgrelid
1982           AND t.tgtype & em.num <> 0
1983           AND NOT t.tgisconstraint
1984           AND (NOT pg_is_other_temp_schema(n.oid))
1985           AND (pg_has_role(c.relowner, 'USAGE')
1986                -- SELECT privilege omitted, per SQL standard
1987                OR has_table_privilege(c.oid, 'INSERT')
1988                OR has_table_privilege(c.oid, 'UPDATE')
1989                OR has_table_privilege(c.oid, 'DELETE')
1990                OR has_table_privilege(c.oid, 'TRUNCATE')
1991                OR has_table_privilege(c.oid, 'REFERENCES')
1992                OR has_table_privilege(c.oid, 'TRIGGER') );
1993
1994 GRANT SELECT ON triggers TO PUBLIC;
1995
1996
1997 /*
1998  * 5.70
1999  * UDT_PRIVILEGES view
2000  */
2001
2002 -- feature not supported
2003
2004
2005 /*
2006  * 5.71
2007  * USAGE_PRIVILEGES view
2008  */
2009
2010 -- Of the things currently implemented in PostgreSQL, usage privileges
2011 -- apply only to domains.  Since domains have no real privileges, we
2012 -- represent all domains with implicit usage privilege here.
2013
2014 CREATE VIEW usage_privileges AS
2015     SELECT CAST(u.rolname AS sql_identifier) AS grantor,
2016            CAST('PUBLIC' AS sql_identifier) AS grantee,
2017            CAST(current_database() AS sql_identifier) AS object_catalog,
2018            CAST(n.nspname AS sql_identifier) AS object_schema,
2019            CAST(t.typname AS sql_identifier) AS object_name,
2020            CAST('DOMAIN' AS character_data) AS object_type,
2021            CAST('USAGE' AS character_data) AS privilege_type,
2022            CAST('NO' AS character_data) AS is_grantable
2023
2024     FROM pg_authid u,
2025          pg_namespace n,
2026          pg_type t
2027
2028     WHERE u.oid = t.typowner
2029           AND t.typnamespace = n.oid
2030           AND t.typtype = 'd';
2031
2032 GRANT SELECT ON usage_privileges TO PUBLIC;
2033
2034
2035 /*
2036  * 5.72
2037  * USER_DEFINED_TYPES view
2038  */
2039
2040 -- feature not supported
2041
2042
2043 /*
2044  * 5.73
2045  * VIEW_COLUMN_USAGE
2046  */
2047
2048 CREATE VIEW view_column_usage AS
2049     SELECT DISTINCT
2050            CAST(current_database() AS sql_identifier) AS view_catalog,
2051            CAST(nv.nspname AS sql_identifier) AS view_schema,
2052            CAST(v.relname AS sql_identifier) AS view_name,
2053            CAST(current_database() AS sql_identifier) AS table_catalog,
2054            CAST(nt.nspname AS sql_identifier) AS table_schema,
2055            CAST(t.relname AS sql_identifier) AS table_name,
2056            CAST(a.attname AS sql_identifier) AS column_name
2057
2058     FROM pg_namespace nv, pg_class v, pg_depend dv,
2059          pg_depend dt, pg_class t, pg_namespace nt,
2060          pg_attribute a
2061
2062     WHERE nv.oid = v.relnamespace
2063           AND v.relkind = 'v'
2064           AND v.oid = dv.refobjid
2065           AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2066           AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2067           AND dv.deptype = 'i'
2068           AND dv.objid = dt.objid
2069           AND dv.refobjid <> dt.refobjid
2070           AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2071           AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2072           AND dt.refobjid = t.oid
2073           AND t.relnamespace = nt.oid
2074           AND t.relkind IN ('r', 'v')
2075           AND t.oid = a.attrelid
2076           AND dt.refobjsubid = a.attnum
2077           AND pg_has_role(t.relowner, 'USAGE');
2078
2079 GRANT SELECT ON view_column_usage TO PUBLIC;
2080
2081
2082 /*
2083  * 5.74
2084  * VIEW_ROUTINE_USAGE
2085  */
2086
2087 CREATE VIEW view_routine_usage AS
2088     SELECT DISTINCT
2089            CAST(current_database() AS sql_identifier) AS table_catalog,
2090            CAST(nv.nspname AS sql_identifier) AS table_schema,
2091            CAST(v.relname AS sql_identifier) AS table_name,
2092            CAST(current_database() AS sql_identifier) AS specific_catalog,
2093            CAST(np.nspname AS sql_identifier) AS specific_schema,
2094            CAST(p.proname || '_' || CAST(p.oid AS text)  AS sql_identifier) AS specific_name
2095
2096     FROM pg_namespace nv, pg_class v, pg_depend dv,
2097          pg_depend dp, pg_proc p, pg_namespace np
2098
2099     WHERE nv.oid = v.relnamespace
2100           AND v.relkind = 'v'
2101           AND v.oid = dv.refobjid
2102           AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2103           AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2104           AND dv.deptype = 'i'
2105           AND dv.objid = dp.objid
2106           AND dp.classid = 'pg_catalog.pg_rewrite'::regclass
2107           AND dp.refclassid = 'pg_catalog.pg_proc'::regclass
2108           AND dp.refobjid = p.oid
2109           AND p.pronamespace = np.oid
2110           AND pg_has_role(p.proowner, 'USAGE');
2111
2112 GRANT SELECT ON view_routine_usage TO PUBLIC;
2113
2114
2115 /*
2116  * 5.75
2117  * VIEW_TABLE_USAGE
2118  */
2119
2120 CREATE VIEW view_table_usage AS
2121     SELECT DISTINCT
2122            CAST(current_database() AS sql_identifier) AS view_catalog,
2123            CAST(nv.nspname AS sql_identifier) AS view_schema,
2124            CAST(v.relname AS sql_identifier) AS view_name,
2125            CAST(current_database() AS sql_identifier) AS table_catalog,
2126            CAST(nt.nspname AS sql_identifier) AS table_schema,
2127            CAST(t.relname AS sql_identifier) AS table_name
2128
2129     FROM pg_namespace nv, pg_class v, pg_depend dv,
2130          pg_depend dt, pg_class t, pg_namespace nt
2131
2132     WHERE nv.oid = v.relnamespace
2133           AND v.relkind = 'v'
2134           AND v.oid = dv.refobjid
2135           AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2136           AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2137           AND dv.deptype = 'i'
2138           AND dv.objid = dt.objid
2139           AND dv.refobjid <> dt.refobjid
2140           AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2141           AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2142           AND dt.refobjid = t.oid
2143           AND t.relnamespace = nt.oid
2144           AND t.relkind IN ('r', 'v')
2145           AND pg_has_role(t.relowner, 'USAGE');
2146
2147 GRANT SELECT ON view_table_usage TO PUBLIC;
2148
2149
2150 /*
2151  * 5.76
2152  * VIEWS view
2153  */
2154
2155 CREATE VIEW views AS
2156     SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
2157            CAST(nc.nspname AS sql_identifier) AS table_schema,
2158            CAST(c.relname AS sql_identifier) AS table_name,
2159
2160            CAST(
2161              CASE WHEN pg_has_role(c.relowner, 'USAGE')
2162                   THEN pg_get_viewdef(c.oid)
2163                   ELSE null END
2164              AS character_data) AS view_definition,
2165
2166            CAST('NONE' AS character_data) AS check_option,
2167
2168            CAST(
2169              CASE WHEN EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '2' AND is_instead)
2170                    AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '4' AND is_instead)
2171                   THEN 'YES' ELSE 'NO' END
2172              AS character_data) AS is_updatable,
2173
2174            CAST(
2175              CASE WHEN EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '3' AND is_instead)
2176                   THEN 'YES' ELSE 'NO' END
2177              AS character_data) AS is_insertable_into
2178
2179     FROM pg_namespace nc, pg_class c
2180
2181     WHERE c.relnamespace = nc.oid
2182           AND c.relkind = 'v'
2183           AND (NOT pg_is_other_temp_schema(nc.oid))
2184           AND (pg_has_role(c.relowner, 'USAGE')
2185                OR has_table_privilege(c.oid, 'SELECT')
2186                OR has_table_privilege(c.oid, 'INSERT')
2187                OR has_table_privilege(c.oid, 'UPDATE')
2188                OR has_table_privilege(c.oid, 'DELETE')
2189                OR has_table_privilege(c.oid, 'TRUNCATE')
2190                OR has_table_privilege(c.oid, 'REFERENCES')
2191                OR has_table_privilege(c.oid, 'TRIGGER') );
2192
2193 GRANT SELECT ON views TO PUBLIC;
2194
2195
2196 -- The following views have dependencies that force them to appear out of order.
2197
2198 /*
2199  * 5.23
2200  * DATA_TYPE_PRIVILEGES view
2201  */
2202
2203 CREATE VIEW data_type_privileges AS
2204     SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2205            CAST(x.objschema AS sql_identifier) AS object_schema,
2206            CAST(x.objname AS sql_identifier) AS object_name,
2207            CAST(x.objtype AS character_data) AS object_type,
2208            CAST(x.objdtdid AS sql_identifier) AS dtd_identifier
2209
2210     FROM
2211       (
2212         SELECT udt_schema, udt_name, 'USER-DEFINED TYPE'::text, dtd_identifier FROM attributes
2213         UNION ALL
2214         SELECT table_schema, table_name, 'TABLE'::text, dtd_identifier FROM columns
2215         UNION ALL
2216         SELECT domain_schema, domain_name, 'DOMAIN'::text, dtd_identifier FROM domains
2217         UNION ALL
2218         SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM parameters
2219         UNION ALL
2220         SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM routines
2221       ) AS x (objschema, objname, objtype, objdtdid);
2222
2223 GRANT SELECT ON data_type_privileges TO PUBLIC;
2224
2225
2226 /*
2227  * 5.28
2228  * ELEMENT_TYPES view
2229  */
2230
2231 CREATE VIEW element_types AS
2232     SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2233            CAST(n.nspname AS sql_identifier) AS object_schema,
2234            CAST(x.objname AS sql_identifier) AS object_name,
2235            CAST(x.objtype AS character_data) AS object_type,
2236            CAST(x.objdtdid AS sql_identifier) AS collection_type_identifier,
2237            CAST(
2238              CASE WHEN nbt.nspname = 'pg_catalog' THEN format_type(bt.oid, null)
2239                   ELSE 'USER-DEFINED' END AS character_data) AS data_type,
2240
2241            CAST(null AS cardinal_number) AS character_maximum_length,
2242            CAST(null AS cardinal_number) AS character_octet_length,
2243            CAST(null AS sql_identifier) AS character_set_catalog,
2244            CAST(null AS sql_identifier) AS character_set_schema,
2245            CAST(null AS sql_identifier) AS character_set_name,
2246            CAST(null AS sql_identifier) AS collation_catalog,
2247            CAST(null AS sql_identifier) AS collation_schema,
2248            CAST(null AS sql_identifier) AS collation_name,
2249            CAST(null AS cardinal_number) AS numeric_precision,
2250            CAST(null AS cardinal_number) AS numeric_precision_radix,
2251            CAST(null AS cardinal_number) AS numeric_scale,
2252            CAST(null AS cardinal_number) AS datetime_precision,
2253            CAST(null AS character_data) AS interval_type,
2254            CAST(null AS character_data) AS interval_precision,
2255
2256            CAST(null AS character_data) AS domain_default, -- XXX maybe a bug in the standard
2257
2258            CAST(current_database() AS sql_identifier) AS udt_catalog,
2259            CAST(nbt.nspname AS sql_identifier) AS udt_schema,
2260            CAST(bt.typname AS sql_identifier) AS udt_name,
2261
2262            CAST(null AS sql_identifier) AS scope_catalog,
2263            CAST(null AS sql_identifier) AS scope_schema,
2264            CAST(null AS sql_identifier) AS scope_name,
2265
2266            CAST(null AS cardinal_number) AS maximum_cardinality,
2267            CAST('a' || CAST(x.objdtdid AS text) AS sql_identifier) AS dtd_identifier
2268
2269     FROM pg_namespace n, pg_type at, pg_namespace nbt, pg_type bt,
2270          (
2271            /* columns */
2272            SELECT c.relnamespace, CAST(c.relname AS sql_identifier),
2273                   'TABLE'::text, a.attnum, a.atttypid
2274            FROM pg_class c, pg_attribute a
2275            WHERE c.oid = a.attrelid
2276                  AND c.relkind IN ('r', 'v')
2277                  AND attnum > 0 AND NOT attisdropped
2278
2279            UNION ALL
2280
2281            /* domains */
2282            SELECT t.typnamespace, CAST(t.typname AS sql_identifier),
2283                   'DOMAIN'::text, 1, t.typbasetype
2284            FROM pg_type t
2285            WHERE t.typtype = 'd'
2286
2287            UNION ALL
2288
2289            /* parameters */
2290            SELECT pronamespace, CAST(proname || '_' || CAST(oid AS text) AS sql_identifier),
2291                   'ROUTINE'::text, (ss.x).n, (ss.x).x
2292            FROM (SELECT p.pronamespace, p.proname, p.oid,
2293                         _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
2294                  FROM pg_proc p) AS ss
2295
2296            UNION ALL
2297
2298            /* result types */
2299            SELECT p.pronamespace, CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier),
2300                   'ROUTINE'::text, 0, p.prorettype
2301            FROM pg_proc p
2302
2303          ) AS x (objschema, objname, objtype, objdtdid, objtypeid)
2304
2305     WHERE n.oid = x.objschema
2306           AND at.oid = x.objtypeid
2307           AND (at.typelem <> 0 AND at.typlen = -1)
2308           AND at.typelem = bt.oid
2309           AND nbt.oid = bt.typnamespace
2310
2311           AND (n.nspname, x.objname, x.objtype, CAST(x.objdtdid AS sql_identifier)) IN
2312               ( SELECT object_schema, object_name, object_type, dtd_identifier
2313                     FROM data_type_privileges );
2314
2315 GRANT SELECT ON element_types TO PUBLIC;