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