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 CREATE VIEW triggered_update_columns AS
1851     SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1852            CAST(n.nspname AS sql_identifier) AS trigger_schema,
1853            CAST(t.tgname AS sql_identifier) AS trigger_name,
1854            CAST(current_database() AS sql_identifier) AS event_object_catalog,
1855            CAST(n.nspname AS sql_identifier) AS event_object_schema,
1856            CAST(c.relname AS sql_identifier) AS event_object_table,
1857            CAST(a.attname AS sql_identifier) AS event_object_column
1858
1859     FROM pg_namespace n, pg_class c, pg_trigger t,
1860          (SELECT tgoid, (ta0.tgat).x AS tgattnum, (ta0.tgat).n AS tgattpos
1861           FROM (SELECT oid AS tgoid, information_schema._pg_expandarray(tgattr) AS tgat FROM pg_trigger) AS ta0) AS ta,
1862          pg_attribute a
1863
1864     WHERE n.oid = c.relnamespace
1865           AND c.oid = t.tgrelid
1866           AND t.oid = ta.tgoid
1867           AND (a.attrelid, a.attnum) = (t.tgrelid, ta.tgattnum)
1868           AND NOT t.tgisinternal
1869           AND (NOT pg_is_other_temp_schema(n.oid))
1870           AND (pg_has_role(c.relowner, 'USAGE')
1871                -- SELECT privilege omitted, per SQL standard
1872                OR has_column_privilege(c.oid, a.attnum, 'INSERT, UPDATE, REFERENCES') );
1873
1874 GRANT SELECT ON triggered_update_columns TO PUBLIC;
1875
1876
1877 /*
1878  * 5.65
1879  * TRIGGER_COLUMN_USAGE view
1880  */
1881
1882 -- not tracked by PostgreSQL
1883
1884
1885 /*
1886  * 5.66
1887  * TRIGGER_ROUTINE_USAGE view
1888  */
1889
1890 -- not tracked by PostgreSQL
1891
1892
1893 /*
1894  * 5.67
1895  * TRIGGER_SEQUENCE_USAGE view
1896  */
1897
1898 -- not tracked by PostgreSQL
1899
1900
1901 /*
1902  * 5.68
1903  * TRIGGER_TABLE_USAGE view
1904  */
1905
1906 -- not tracked by PostgreSQL
1907
1908
1909 /*
1910  * 5.69
1911  * TRIGGERS view
1912  */
1913
1914 CREATE VIEW triggers AS
1915     SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1916            CAST(n.nspname AS sql_identifier) AS trigger_schema,
1917            CAST(t.tgname AS sql_identifier) AS trigger_name,
1918            CAST(em.text AS character_data) AS event_manipulation,
1919            CAST(current_database() AS sql_identifier) AS event_object_catalog,
1920            CAST(n.nspname AS sql_identifier) AS event_object_schema,
1921            CAST(c.relname AS sql_identifier) AS event_object_table,
1922            CAST(null AS cardinal_number) AS action_order,
1923            -- XXX strange hacks follow
1924            CAST(
1925              CASE WHEN pg_has_role(c.relowner, 'USAGE')
1926                THEN (SELECT m[1] FROM regexp_matches(pg_get_triggerdef(t.oid), E'.{35,} WHEN \\((.+)\\) EXECUTE PROCEDURE') AS rm(m) LIMIT 1)
1927                ELSE null END
1928              AS character_data) AS action_condition,
1929            CAST(
1930              substring(pg_get_triggerdef(t.oid) from
1931                        position('EXECUTE PROCEDURE' in substring(pg_get_triggerdef(t.oid) from 48)) + 47)
1932              AS character_data) AS action_statement,
1933            CAST(
1934              -- hard-wired reference to TRIGGER_TYPE_ROW
1935              CASE t.tgtype & 1 WHEN 1 THEN 'ROW' ELSE 'STATEMENT' END
1936              AS character_data) AS action_orientation,
1937            CAST(
1938              -- hard-wired refs to TRIGGER_TYPE_BEFORE, TRIGGER_TYPE_INSTEAD
1939              CASE t.tgtype & 66 WHEN 2 THEN 'BEFORE' WHEN 64 THEN 'INSTEAD OF' ELSE 'AFTER' END
1940              AS character_data) AS action_timing,
1941            CAST(null AS sql_identifier) AS action_reference_old_table,
1942            CAST(null AS sql_identifier) AS action_reference_new_table,
1943            CAST(null AS sql_identifier) AS action_reference_old_row,
1944            CAST(null AS sql_identifier) AS action_reference_new_row,
1945            CAST(null AS time_stamp) AS created
1946
1947     FROM pg_namespace n, pg_class c, pg_trigger t,
1948          -- hard-wired refs to TRIGGER_TYPE_INSERT, TRIGGER_TYPE_DELETE,
1949          -- TRIGGER_TYPE_UPDATE; we intentionally omit TRIGGER_TYPE_TRUNCATE
1950          (VALUES (4, 'INSERT'),
1951                  (8, 'DELETE'),
1952                  (16, 'UPDATE')) AS em (num, text)
1953
1954     WHERE n.oid = c.relnamespace
1955           AND c.oid = t.tgrelid
1956           AND t.tgtype & em.num <> 0
1957           AND NOT t.tgisinternal
1958           AND (NOT pg_is_other_temp_schema(n.oid))
1959           AND (pg_has_role(c.relowner, 'USAGE')
1960                -- SELECT privilege omitted, per SQL standard
1961                OR has_table_privilege(c.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1962                OR has_any_column_privilege(c.oid, 'INSERT, UPDATE, REFERENCES') );
1963
1964 GRANT SELECT ON triggers TO PUBLIC;
1965
1966
1967 /*
1968  * 5.70
1969  * UDT_PRIVILEGES view
1970  */
1971
1972 -- feature not supported
1973
1974
1975 /*
1976  * 5.71
1977  * USAGE_PRIVILEGES view
1978  */
1979
1980 CREATE VIEW usage_privileges AS
1981
1982     /* domains */
1983     -- Domains have no real privileges, so we represent all domains with implicit usage privilege here.
1984     SELECT CAST(u.rolname AS sql_identifier) AS grantor,
1985            CAST('PUBLIC' AS sql_identifier) AS grantee,
1986            CAST(current_database() AS sql_identifier) AS object_catalog,
1987            CAST(n.nspname AS sql_identifier) AS object_schema,
1988            CAST(t.typname AS sql_identifier) AS object_name,
1989            CAST('DOMAIN' AS character_data) AS object_type,
1990            CAST('USAGE' AS character_data) AS privilege_type,
1991            CAST('NO' AS yes_or_no) AS is_grantable
1992
1993     FROM pg_authid u,
1994          pg_namespace n,
1995          pg_type t
1996
1997     WHERE u.oid = t.typowner
1998           AND t.typnamespace = n.oid
1999           AND t.typtype = 'd'
2000
2001     UNION ALL
2002
2003     /* foreign-data wrappers */
2004     SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2005            CAST(grantee.rolname AS sql_identifier) AS grantee,
2006            CAST(current_database() AS sql_identifier) AS object_catalog,
2007            CAST('' AS sql_identifier) AS object_schema,
2008            CAST(fdw.fdwname AS sql_identifier) AS object_name,
2009            CAST('FOREIGN DATA WRAPPER' AS character_data) AS object_type,
2010            CAST('USAGE' AS character_data) AS privilege_type,
2011            CAST(
2012              CASE WHEN
2013                   -- object owner always has grant options
2014                   pg_has_role(grantee.oid, fdw.fdwowner, 'USAGE')
2015                   OR fdw.grantable
2016                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2017
2018     FROM (
2019             SELECT fdwname, fdwowner, (aclexplode(fdwacl)).* FROM pg_foreign_data_wrapper
2020          ) AS fdw (fdwname, fdwowner, grantor, grantee, prtype, grantable),
2021          pg_authid u_grantor,
2022          (
2023            SELECT oid, rolname FROM pg_authid
2024            UNION ALL
2025            SELECT 0::oid, 'PUBLIC'
2026          ) AS grantee (oid, rolname)
2027
2028     WHERE u_grantor.oid = fdw.grantor
2029           AND grantee.oid = fdw.grantee
2030           AND fdw.prtype IN ('USAGE')
2031           AND (pg_has_role(u_grantor.oid, 'USAGE')
2032                OR pg_has_role(grantee.oid, 'USAGE')
2033                OR grantee.rolname = 'PUBLIC')
2034
2035     UNION ALL
2036
2037     /* foreign servers */
2038     SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2039            CAST(grantee.rolname AS sql_identifier) AS grantee,
2040            CAST(current_database() AS sql_identifier) AS object_catalog,
2041            CAST('' AS sql_identifier) AS object_schema,
2042            CAST(srv.srvname AS sql_identifier) AS object_name,
2043            CAST('FOREIGN SERVER' AS character_data) AS object_type,
2044            CAST('USAGE' AS character_data) AS privilege_type,
2045            CAST(
2046              CASE WHEN
2047                   -- object owner always has grant options
2048                   pg_has_role(grantee.oid, srv.srvowner, 'USAGE')
2049                   OR srv.grantable
2050                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2051
2052     FROM (
2053             SELECT srvname, srvowner, (aclexplode(srvacl)).* FROM pg_foreign_server
2054          ) AS srv (srvname, srvowner, grantor, grantee, prtype, grantable),
2055          pg_authid u_grantor,
2056          (
2057            SELECT oid, rolname FROM pg_authid
2058            UNION ALL
2059            SELECT 0::oid, 'PUBLIC'
2060          ) AS grantee (oid, rolname)
2061
2062     WHERE u_grantor.oid = srv.grantor
2063           AND grantee.oid = srv.grantee
2064           AND srv.prtype IN ('USAGE')
2065           AND (pg_has_role(u_grantor.oid, 'USAGE')
2066                OR pg_has_role(grantee.oid, 'USAGE')
2067                OR grantee.rolname = 'PUBLIC');
2068
2069 GRANT SELECT ON usage_privileges TO PUBLIC;
2070
2071
2072 /*
2073  * 5.42
2074  * ROLE_USAGE_GRANTS view
2075  */
2076
2077 CREATE VIEW role_usage_grants AS
2078     SELECT grantor,
2079            grantee,
2080            object_catalog,
2081            object_schema,
2082            object_name,
2083            object_type,
2084            privilege_type,
2085            is_grantable
2086     FROM usage_privileges
2087     WHERE grantor IN (SELECT role_name FROM enabled_roles)
2088           OR grantee IN (SELECT role_name FROM enabled_roles);
2089
2090 GRANT SELECT ON role_usage_grants TO PUBLIC;
2091
2092
2093 /*
2094  * 5.72
2095  * USER_DEFINED_TYPES view
2096  */
2097
2098 -- feature not supported
2099
2100
2101 /*
2102  * 5.73
2103  * VIEW_COLUMN_USAGE
2104  */
2105
2106 CREATE VIEW view_column_usage AS
2107     SELECT DISTINCT
2108            CAST(current_database() AS sql_identifier) AS view_catalog,
2109            CAST(nv.nspname AS sql_identifier) AS view_schema,
2110            CAST(v.relname AS sql_identifier) AS view_name,
2111            CAST(current_database() AS sql_identifier) AS table_catalog,
2112            CAST(nt.nspname AS sql_identifier) AS table_schema,
2113            CAST(t.relname AS sql_identifier) AS table_name,
2114            CAST(a.attname AS sql_identifier) AS column_name
2115
2116     FROM pg_namespace nv, pg_class v, pg_depend dv,
2117          pg_depend dt, pg_class t, pg_namespace nt,
2118          pg_attribute a
2119
2120     WHERE nv.oid = v.relnamespace
2121           AND v.relkind = 'v'
2122           AND v.oid = dv.refobjid
2123           AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2124           AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2125           AND dv.deptype = 'i'
2126           AND dv.objid = dt.objid
2127           AND dv.refobjid <> dt.refobjid
2128           AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2129           AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2130           AND dt.refobjid = t.oid
2131           AND t.relnamespace = nt.oid
2132           AND t.relkind IN ('r', 'v')
2133           AND t.oid = a.attrelid
2134           AND dt.refobjsubid = a.attnum
2135           AND pg_has_role(t.relowner, 'USAGE');
2136
2137 GRANT SELECT ON view_column_usage TO PUBLIC;
2138
2139
2140 /*
2141  * 5.74
2142  * VIEW_ROUTINE_USAGE
2143  */
2144
2145 CREATE VIEW view_routine_usage AS
2146     SELECT DISTINCT
2147            CAST(current_database() AS sql_identifier) AS table_catalog,
2148            CAST(nv.nspname AS sql_identifier) AS table_schema,
2149            CAST(v.relname AS sql_identifier) AS table_name,
2150            CAST(current_database() AS sql_identifier) AS specific_catalog,
2151            CAST(np.nspname AS sql_identifier) AS specific_schema,
2152            CAST(p.proname || '_' || CAST(p.oid AS text)  AS sql_identifier) AS specific_name
2153
2154     FROM pg_namespace nv, pg_class v, pg_depend dv,
2155          pg_depend dp, pg_proc p, pg_namespace np
2156
2157     WHERE nv.oid = v.relnamespace
2158           AND v.relkind = 'v'
2159           AND v.oid = dv.refobjid
2160           AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2161           AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2162           AND dv.deptype = 'i'
2163           AND dv.objid = dp.objid
2164           AND dp.classid = 'pg_catalog.pg_rewrite'::regclass
2165           AND dp.refclassid = 'pg_catalog.pg_proc'::regclass
2166           AND dp.refobjid = p.oid
2167           AND p.pronamespace = np.oid
2168           AND pg_has_role(p.proowner, 'USAGE');
2169
2170 GRANT SELECT ON view_routine_usage TO PUBLIC;
2171
2172
2173 /*
2174  * 5.75
2175  * VIEW_TABLE_USAGE
2176  */
2177
2178 CREATE VIEW view_table_usage AS
2179     SELECT DISTINCT
2180            CAST(current_database() AS sql_identifier) AS view_catalog,
2181            CAST(nv.nspname AS sql_identifier) AS view_schema,
2182            CAST(v.relname AS sql_identifier) AS view_name,
2183            CAST(current_database() AS sql_identifier) AS table_catalog,
2184            CAST(nt.nspname AS sql_identifier) AS table_schema,
2185            CAST(t.relname AS sql_identifier) AS table_name
2186
2187     FROM pg_namespace nv, pg_class v, pg_depend dv,
2188          pg_depend dt, pg_class t, pg_namespace nt
2189
2190     WHERE nv.oid = v.relnamespace
2191           AND v.relkind = 'v'
2192           AND v.oid = dv.refobjid
2193           AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2194           AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2195           AND dv.deptype = 'i'
2196           AND dv.objid = dt.objid
2197           AND dv.refobjid <> dt.refobjid
2198           AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2199           AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2200           AND dt.refobjid = t.oid
2201           AND t.relnamespace = nt.oid
2202           AND t.relkind IN ('r', 'v')
2203           AND pg_has_role(t.relowner, 'USAGE');
2204
2205 GRANT SELECT ON view_table_usage TO PUBLIC;
2206
2207
2208 /*
2209  * 5.76
2210  * VIEWS view
2211  */
2212
2213 CREATE VIEW views AS
2214     SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
2215            CAST(nc.nspname AS sql_identifier) AS table_schema,
2216            CAST(c.relname AS sql_identifier) AS table_name,
2217
2218            CAST(
2219              CASE WHEN pg_has_role(c.relowner, 'USAGE')
2220                   THEN pg_get_viewdef(c.oid)
2221                   ELSE null END
2222              AS character_data) AS view_definition,
2223
2224            CAST('NONE' AS character_data) AS check_option,
2225
2226            CAST(
2227              CASE WHEN EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '2' AND is_instead)
2228                    AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '4' AND is_instead)
2229                   THEN 'YES' ELSE 'NO' END
2230              AS yes_or_no) AS is_updatable,
2231
2232            CAST(
2233              CASE WHEN EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '3' AND is_instead)
2234                   THEN 'YES' ELSE 'NO' END
2235              AS yes_or_no) AS is_insertable_into,
2236
2237            CAST(
2238              -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_UPDATE
2239              CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 81 = 81)
2240                   THEN 'YES' ELSE 'NO' END
2241            AS yes_or_no) AS is_trigger_updatable,
2242
2243            CAST(
2244              -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_DELETE
2245              CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 73 = 73)
2246                   THEN 'YES' ELSE 'NO' END
2247            AS yes_or_no) AS is_trigger_deletable,
2248
2249            CAST(
2250              -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_INSERT
2251              CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 69 = 69)
2252                   THEN 'YES' ELSE 'NO' END
2253            AS yes_or_no) AS is_trigger_insertable_into
2254
2255     FROM pg_namespace nc, pg_class c
2256
2257     WHERE c.relnamespace = nc.oid
2258           AND c.relkind = 'v'
2259           AND (NOT pg_is_other_temp_schema(nc.oid))
2260           AND (pg_has_role(c.relowner, 'USAGE')
2261                OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
2262                OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES') );
2263
2264 GRANT SELECT ON views TO PUBLIC;
2265
2266
2267 -- The following views have dependencies that force them to appear out of order.
2268
2269 /*
2270  * 5.24
2271  * DATA_TYPE_PRIVILEGES view
2272  */
2273
2274 CREATE VIEW data_type_privileges AS
2275     SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2276            CAST(x.objschema AS sql_identifier) AS object_schema,
2277            CAST(x.objname AS sql_identifier) AS object_name,
2278            CAST(x.objtype AS character_data) AS object_type,
2279            CAST(x.objdtdid AS sql_identifier) AS dtd_identifier
2280
2281     FROM
2282       (
2283         SELECT udt_schema, udt_name, 'USER-DEFINED TYPE'::text, dtd_identifier FROM attributes
2284         UNION ALL
2285         SELECT table_schema, table_name, 'TABLE'::text, dtd_identifier FROM columns
2286         UNION ALL
2287         SELECT domain_schema, domain_name, 'DOMAIN'::text, dtd_identifier FROM domains
2288         UNION ALL
2289         SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM parameters
2290         UNION ALL
2291         SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM routines
2292       ) AS x (objschema, objname, objtype, objdtdid);
2293
2294 GRANT SELECT ON data_type_privileges TO PUBLIC;
2295
2296
2297 /*
2298  * 5.29
2299  * ELEMENT_TYPES view
2300  */
2301
2302 CREATE VIEW element_types AS
2303     SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2304            CAST(n.nspname AS sql_identifier) AS object_schema,
2305            CAST(x.objname AS sql_identifier) AS object_name,
2306            CAST(x.objtype AS character_data) AS object_type,
2307            CAST(x.objdtdid AS sql_identifier) AS collection_type_identifier,
2308            CAST(
2309              CASE WHEN nbt.nspname = 'pg_catalog' THEN format_type(bt.oid, null)
2310                   ELSE 'USER-DEFINED' END AS character_data) AS data_type,
2311
2312            CAST(null AS cardinal_number) AS character_maximum_length,
2313            CAST(null AS cardinal_number) AS character_octet_length,
2314            CAST(null AS sql_identifier) AS character_set_catalog,
2315            CAST(null AS sql_identifier) AS character_set_schema,
2316            CAST(null AS sql_identifier) AS character_set_name,
2317            CAST(null AS sql_identifier) AS collation_catalog,
2318            CAST(null AS sql_identifier) AS collation_schema,
2319            CAST(null AS sql_identifier) AS collation_name,
2320            CAST(null AS cardinal_number) AS numeric_precision,
2321            CAST(null AS cardinal_number) AS numeric_precision_radix,
2322            CAST(null AS cardinal_number) AS numeric_scale,
2323            CAST(null AS cardinal_number) AS datetime_precision,
2324            CAST(null AS character_data) AS interval_type,
2325            CAST(null AS character_data) AS interval_precision,
2326
2327            CAST(null AS character_data) AS domain_default, -- XXX maybe a bug in the standard
2328
2329            CAST(current_database() AS sql_identifier) AS udt_catalog,
2330            CAST(nbt.nspname AS sql_identifier) AS udt_schema,
2331            CAST(bt.typname AS sql_identifier) AS udt_name,
2332
2333            CAST(null AS sql_identifier) AS scope_catalog,
2334            CAST(null AS sql_identifier) AS scope_schema,
2335            CAST(null AS sql_identifier) AS scope_name,
2336
2337            CAST(null AS cardinal_number) AS maximum_cardinality,
2338            CAST('a' || CAST(x.objdtdid AS text) AS sql_identifier) AS dtd_identifier
2339
2340     FROM pg_namespace n, pg_type at, pg_namespace nbt, pg_type bt,
2341          (
2342            /* columns */
2343            SELECT c.relnamespace, CAST(c.relname AS sql_identifier),
2344                   'TABLE'::text, a.attnum, a.atttypid
2345            FROM pg_class c, pg_attribute a
2346            WHERE c.oid = a.attrelid
2347                  AND c.relkind IN ('r', 'v')
2348                  AND attnum > 0 AND NOT attisdropped
2349
2350            UNION ALL
2351
2352            /* domains */
2353            SELECT t.typnamespace, CAST(t.typname AS sql_identifier),
2354                   'DOMAIN'::text, 1, t.typbasetype
2355            FROM pg_type t
2356            WHERE t.typtype = 'd'
2357
2358            UNION ALL
2359
2360            /* parameters */
2361            SELECT pronamespace, CAST(proname || '_' || CAST(oid AS text) AS sql_identifier),
2362                   'ROUTINE'::text, (ss.x).n, (ss.x).x
2363            FROM (SELECT p.pronamespace, p.proname, p.oid,
2364                         _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
2365                  FROM pg_proc p) AS ss
2366
2367            UNION ALL
2368
2369            /* result types */
2370            SELECT p.pronamespace, CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier),
2371                   'ROUTINE'::text, 0, p.prorettype
2372            FROM pg_proc p
2373
2374          ) AS x (objschema, objname, objtype, objdtdid, objtypeid)
2375
2376     WHERE n.oid = x.objschema
2377           AND at.oid = x.objtypeid
2378           AND (at.typelem <> 0 AND at.typlen = -1)
2379           AND at.typelem = bt.oid
2380           AND nbt.oid = bt.typnamespace
2381
2382           AND (n.nspname, x.objname, x.objtype, CAST(x.objdtdid AS sql_identifier)) IN
2383               ( SELECT object_schema, object_name, object_type, dtd_identifier
2384                     FROM data_type_privileges );
2385
2386 GRANT SELECT ON element_types TO PUBLIC;
2387
2388
2389 -- SQL/MED views; these use section numbers from part 9 of the standard.
2390
2391 /* Base view for foreign-data wrappers */
2392 CREATE VIEW _pg_foreign_data_wrappers AS
2393     SELECT w.oid,
2394            w.fdwowner,
2395            w.fdwoptions,
2396            CAST(current_database() AS sql_identifier) AS foreign_data_wrapper_catalog,
2397            CAST(fdwname AS sql_identifier) AS foreign_data_wrapper_name,
2398            CAST(u.rolname AS sql_identifier) AS authorization_identifier,
2399            CAST('c' AS character_data) AS foreign_data_wrapper_language
2400     FROM pg_foreign_data_wrapper w, pg_authid u
2401     WHERE u.oid = w.fdwowner
2402           AND (pg_has_role(fdwowner, 'USAGE')
2403                OR has_foreign_data_wrapper_privilege(w.oid, 'USAGE'));
2404
2405
2406 /*
2407  * 24.4
2408  * FOREIGN_DATA_WRAPPER_OPTIONS view
2409  */
2410 CREATE VIEW foreign_data_wrapper_options AS
2411     SELECT foreign_data_wrapper_catalog,
2412            foreign_data_wrapper_name,
2413            CAST((pg_options_to_table(w.fdwoptions)).option_name AS sql_identifier) AS option_name,
2414            CAST((pg_options_to_table(w.fdwoptions)).option_value AS character_data) AS option_value
2415     FROM _pg_foreign_data_wrappers w;
2416
2417 GRANT SELECT ON foreign_data_wrapper_options TO PUBLIC;
2418
2419
2420 /*
2421  * 24.5
2422  * FOREIGN_DATA_WRAPPERS view
2423  */
2424 CREATE VIEW foreign_data_wrappers AS
2425     SELECT foreign_data_wrapper_catalog,
2426            foreign_data_wrapper_name,
2427            authorization_identifier,
2428            CAST(NULL AS character_data) AS library_name,
2429            foreign_data_wrapper_language
2430     FROM _pg_foreign_data_wrappers w;
2431
2432 GRANT SELECT ON foreign_data_wrappers TO PUBLIC;
2433
2434
2435 /* Base view for foreign servers */
2436 CREATE VIEW _pg_foreign_servers AS
2437     SELECT s.oid,
2438            s.srvoptions,
2439            CAST(current_database() AS sql_identifier) AS foreign_server_catalog,
2440            CAST(srvname AS sql_identifier) AS foreign_server_name,
2441            CAST(current_database() AS sql_identifier) AS foreign_data_wrapper_catalog,
2442            CAST(w.fdwname AS sql_identifier) AS foreign_data_wrapper_name,
2443            CAST(srvtype AS character_data) AS foreign_server_type,
2444            CAST(srvversion AS character_data) AS foreign_server_version,
2445            CAST(u.rolname AS sql_identifier) AS authorization_identifier
2446     FROM pg_foreign_server s, pg_foreign_data_wrapper w, pg_authid u
2447     WHERE w.oid = s.srvfdw
2448           AND u.oid = s.srvowner
2449           AND (pg_has_role(s.srvowner, 'USAGE')
2450                OR has_server_privilege(s.oid, 'USAGE'));
2451
2452
2453 /*
2454  * 24.6
2455  * FOREIGN_SERVER_OPTIONS view
2456  */
2457 CREATE VIEW foreign_server_options AS
2458     SELECT foreign_server_catalog,
2459            foreign_server_name,
2460            CAST((pg_options_to_table(s.srvoptions)).option_name AS sql_identifier) AS option_name,
2461            CAST((pg_options_to_table(s.srvoptions)).option_value AS character_data) AS option_value
2462     FROM _pg_foreign_servers s;
2463
2464 GRANT SELECT ON TABLE foreign_server_options TO PUBLIC;
2465
2466
2467 /*
2468  * 24.7
2469  * FOREIGN_SERVERS view
2470  */
2471 CREATE VIEW foreign_servers AS
2472     SELECT foreign_server_catalog,
2473            foreign_server_name,
2474            foreign_data_wrapper_catalog,
2475            foreign_data_wrapper_name,
2476            foreign_server_type,
2477            foreign_server_version,
2478            authorization_identifier
2479     FROM _pg_foreign_servers;
2480
2481 GRANT SELECT ON foreign_servers TO PUBLIC;
2482
2483
2484 /* Base view for user mappings */
2485 CREATE VIEW _pg_user_mappings AS
2486     SELECT um.oid,
2487            um.umoptions,
2488            um.umuser,
2489            CAST(COALESCE(u.rolname,'PUBLIC') AS sql_identifier ) AS authorization_identifier,
2490            s.foreign_server_catalog,
2491            s.foreign_server_name,
2492            s.authorization_identifier AS srvowner
2493     FROM pg_user_mapping um LEFT JOIN pg_authid u ON (u.oid = um.umuser),
2494          _pg_foreign_servers s
2495     WHERE s.oid = um.umserver;
2496
2497
2498 /*
2499  * 24.12
2500  * USER_MAPPING_OPTIONS view
2501  */
2502 CREATE VIEW user_mapping_options AS
2503     SELECT authorization_identifier,
2504            foreign_server_catalog,
2505            foreign_server_name,
2506            CAST((pg_options_to_table(um.umoptions)).option_name AS sql_identifier) AS option_name,
2507            CAST(CASE WHEN (umuser <> 0 AND authorization_identifier = current_user)
2508                        OR (umuser = 0 AND pg_has_role(srvowner, 'USAGE'))
2509                        OR (SELECT rolsuper FROM pg_authid WHERE rolname = current_user) THEN (pg_options_to_table(um.umoptions)).option_value
2510                      ELSE NULL END AS character_data) AS option_value
2511     FROM _pg_user_mappings um;
2512
2513 GRANT SELECT ON user_mapping_options TO PUBLIC;
2514
2515
2516 /*
2517  * 24.13
2518  * USER_MAPPINGS view
2519  */
2520 CREATE VIEW user_mappings AS
2521     SELECT authorization_identifier,
2522            foreign_server_catalog,
2523            foreign_server_name
2524     FROM _pg_user_mappings;
2525
2526 GRANT SELECT ON user_mappings TO PUBLIC;