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-2011, 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', 'f')
 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', 'f')
 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', 'f')
 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', 'f')
 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', 'f')
 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            -- XXX: The following could be improved if we had LATERAL.
1434            CAST((pg_sequence_parameters(c.oid)).start_value AS character_data) AS start_value,
1435            CAST((pg_sequence_parameters(c.oid)).minimum_value AS character_data) AS minimum_value,
1436            CAST((pg_sequence_parameters(c.oid)).maximum_value AS character_data) AS maximum_value,
1437            CAST((pg_sequence_parameters(c.oid)).increment AS character_data) AS increment,
1438            CAST(CASE WHEN (pg_sequence_parameters(c.oid)).cycle_option THEN 'YES' ELSE 'NO' END AS yes_or_no) AS cycle_option
1439     FROM pg_namespace nc, pg_class c
1440     WHERE c.relnamespace = nc.oid
1441           AND c.relkind = 'S'
1442           AND (NOT pg_is_other_temp_schema(nc.oid))
1443           AND (pg_has_role(c.relowner, 'USAGE')
1444                OR has_sequence_privilege(c.oid, 'SELECT, UPDATE, USAGE') );
1445
1446 GRANT SELECT ON sequences TO PUBLIC;
1447
1448
1449 /*
1450  * 5.52
1451  * SQL_FEATURES table
1452  */
1453
1454 CREATE TABLE sql_features (
1455     feature_id          character_data,
1456     feature_name        character_data,
1457     sub_feature_id      character_data,
1458     sub_feature_name    character_data,
1459     is_supported        yes_or_no,
1460     is_verified_by      character_data,
1461     comments            character_data
1462 ) WITHOUT OIDS;
1463
1464 -- Will be filled with external data by initdb.
1465
1466 GRANT SELECT ON sql_features TO PUBLIC;
1467
1468
1469 /*
1470  * 5.53
1471  * SQL_IMPLEMENTATION_INFO table
1472  */
1473
1474 -- Note: Implementation information items are defined in ISO/IEC 9075-3:2008,
1475 -- clause 9.1.
1476
1477 CREATE TABLE sql_implementation_info (
1478     implementation_info_id      character_data,
1479     implementation_info_name    character_data,
1480     integer_value               cardinal_number,
1481     character_value             character_data,
1482     comments                    character_data
1483 ) WITHOUT OIDS;
1484
1485 INSERT INTO sql_implementation_info VALUES ('10003', 'CATALOG NAME', NULL, 'Y', NULL);
1486 INSERT INTO sql_implementation_info VALUES ('10004', 'COLLATING SEQUENCE', NULL, '', 'not supported');
1487 INSERT INTO sql_implementation_info VALUES ('23',    'CURSOR COMMIT BEHAVIOR', 1, NULL, 'close cursors and retain prepared statements');
1488 INSERT INTO sql_implementation_info VALUES ('2',     'DATA SOURCE NAME', NULL, '', NULL);
1489 INSERT INTO sql_implementation_info VALUES ('17',    'DBMS NAME', NULL, (select trim(trailing ' ' from substring(version() from '^[^0-9]*'))), NULL);
1490 INSERT INTO sql_implementation_info VALUES ('18',    'DBMS VERSION', NULL, '???', NULL); -- filled by initdb
1491 INSERT INTO sql_implementation_info VALUES ('26',    'DEFAULT TRANSACTION ISOLATION', 2, NULL, 'READ COMMITTED; user-settable');
1492 INSERT INTO sql_implementation_info VALUES ('28',    'IDENTIFIER CASE', 3, NULL, 'stored in mixed case - case sensitive');
1493 INSERT INTO sql_implementation_info VALUES ('85',    'NULL COLLATION', 0, NULL, 'nulls higher than non-nulls');
1494 INSERT INTO sql_implementation_info VALUES ('13',    'SERVER NAME', NULL, '', NULL);
1495 INSERT INTO sql_implementation_info VALUES ('94',    'SPECIAL CHARACTERS', NULL, '', 'all non-ASCII characters allowed');
1496 INSERT INTO sql_implementation_info VALUES ('46',    'TRANSACTION CAPABLE', 2, NULL, 'both DML and DDL');
1497
1498 GRANT SELECT ON sql_implementation_info TO PUBLIC;
1499
1500
1501 /*
1502  * SQL_LANGUAGES table
1503  * apparently removed in SQL:2008
1504  */
1505
1506 CREATE TABLE sql_languages (
1507     sql_language_source         character_data,
1508     sql_language_year           character_data,
1509     sql_language_conformance    character_data,
1510     sql_language_integrity      character_data,
1511     sql_language_implementation character_data,
1512     sql_language_binding_style  character_data,
1513     sql_language_programming_language character_data
1514 ) WITHOUT OIDS;
1515
1516 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'DIRECT', NULL);
1517 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
1518 INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'DIRECT', NULL);
1519 INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
1520
1521 GRANT SELECT ON sql_languages TO PUBLIC;
1522
1523
1524 /*
1525  * 5.54
1526  * SQL_PACKAGES table
1527  */
1528
1529 CREATE TABLE sql_packages (
1530     feature_id      character_data,
1531     feature_name    character_data,
1532     is_supported    yes_or_no,
1533     is_verified_by  character_data,
1534     comments        character_data
1535 ) WITHOUT OIDS;
1536
1537 INSERT INTO sql_packages VALUES ('PKG000', 'Core', 'NO', NULL, '');
1538 INSERT INTO sql_packages VALUES ('PKG001', 'Enhanced datetime facilities', 'YES', NULL, '');
1539 INSERT INTO sql_packages VALUES ('PKG002', 'Enhanced integrity management', 'NO', NULL, '');
1540 INSERT INTO sql_packages VALUES ('PKG003', 'OLAP facilities', 'NO', NULL, '');
1541 INSERT INTO sql_packages VALUES ('PKG004', 'PSM', 'NO', NULL, 'PL/pgSQL is similar.');
1542 INSERT INTO sql_packages VALUES ('PKG005', 'CLI', 'NO', NULL, 'ODBC is similar.');
1543 INSERT INTO sql_packages VALUES ('PKG006', 'Basic object support', 'NO', NULL, '');
1544 INSERT INTO sql_packages VALUES ('PKG007', 'Enhanced object support', 'NO', NULL, '');
1545 INSERT INTO sql_packages VALUES ('PKG008', 'Active database', 'NO', NULL, '');
1546 INSERT INTO sql_packages VALUES ('PKG010', 'OLAP', 'NO', NULL, 'NO');
1547
1548 GRANT SELECT ON sql_packages TO PUBLIC;
1549
1550
1551 /*
1552  * 5.55
1553  * SQL_PARTS table
1554  */
1555
1556 CREATE TABLE sql_parts (
1557     feature_id      character_data,
1558     feature_name    character_data,
1559     is_supported    yes_or_no,
1560     is_verified_by  character_data,
1561     comments        character_data
1562 ) WITHOUT OIDS;
1563
1564 INSERT INTO sql_parts VALUES ('1', 'Framework (SQL/Framework)', 'NO', NULL, '');
1565 INSERT INTO sql_parts VALUES ('2', 'Foundation (SQL/Foundation)', 'NO', NULL, '');
1566 INSERT INTO sql_parts VALUES ('3', 'Call-Level Interface (SQL/CLI)', 'NO', NULL, '');
1567 INSERT INTO sql_parts VALUES ('4', 'Persistent Stored Modules (SQL/PSM)', 'NO', NULL, '');
1568 INSERT INTO sql_parts VALUES ('9', 'Management of External Data (SQL/MED)', 'NO', NULL, '');
1569 INSERT INTO sql_parts VALUES ('10', 'Object Language Bindings (SQL/OLB)', 'NO', NULL, '');
1570 INSERT INTO sql_parts VALUES ('11', 'Information and Definition Schema (SQL/Schemata)', 'NO', NULL, '');
1571 INSERT INTO sql_parts VALUES ('13', 'Routines and Types Using the Java Programming Language (SQL/JRT)', 'NO', NULL, '');
1572 INSERT INTO sql_parts VALUES ('14', 'XML-Related Specifications (SQL/XML)', 'YES', NULL, '');
1573
1574
1575 /*
1576  * 5.56
1577  * SQL_SIZING table
1578  */
1579
1580 -- Note: Sizing items are defined in ISO/IEC 9075-3:2008, clause 9.2.
1581
1582 CREATE TABLE sql_sizing (
1583     sizing_id       cardinal_number,
1584     sizing_name     character_data,
1585     supported_value cardinal_number,
1586     comments        character_data
1587 ) WITHOUT OIDS;
1588
1589 INSERT INTO sql_sizing VALUES (34,    'MAXIMUM CATALOG NAME LENGTH', 63, NULL);
1590 INSERT INTO sql_sizing VALUES (30,    'MAXIMUM COLUMN NAME LENGTH', 63, NULL);
1591 INSERT INTO sql_sizing VALUES (97,    'MAXIMUM COLUMNS IN GROUP BY', 0, NULL);
1592 INSERT INTO sql_sizing VALUES (99,    'MAXIMUM COLUMNS IN ORDER BY', 0, NULL);
1593 INSERT INTO sql_sizing VALUES (100,   'MAXIMUM COLUMNS IN SELECT', 1664, NULL); -- match MaxTupleAttributeNumber
1594 INSERT INTO sql_sizing VALUES (101,   'MAXIMUM COLUMNS IN TABLE', 1600, NULL); -- match MaxHeapAttributeNumber
1595 INSERT INTO sql_sizing VALUES (1,     'MAXIMUM CONCURRENT ACTIVITIES', 0, NULL);
1596 INSERT INTO sql_sizing VALUES (31,    'MAXIMUM CURSOR NAME LENGTH', 63, NULL);
1597 INSERT INTO sql_sizing VALUES (0,     'MAXIMUM DRIVER CONNECTIONS', NULL, NULL);
1598 INSERT INTO sql_sizing VALUES (10005, 'MAXIMUM IDENTIFIER LENGTH', 63, NULL);
1599 INSERT INTO sql_sizing VALUES (32,    'MAXIMUM SCHEMA NAME LENGTH', 63, NULL);
1600 INSERT INTO sql_sizing VALUES (20000, 'MAXIMUM STATEMENT OCTETS', 0, NULL);
1601 INSERT INTO sql_sizing VALUES (20001, 'MAXIMUM STATEMENT OCTETS DATA', 0, NULL);
1602 INSERT INTO sql_sizing VALUES (20002, 'MAXIMUM STATEMENT OCTETS SCHEMA', 0, NULL);
1603 INSERT INTO sql_sizing VALUES (35,    'MAXIMUM TABLE NAME LENGTH', 63, NULL);
1604 INSERT INTO sql_sizing VALUES (106,   'MAXIMUM TABLES IN SELECT', 0, NULL);
1605 INSERT INTO sql_sizing VALUES (107,   'MAXIMUM USER NAME LENGTH', 63, NULL);
1606 INSERT INTO sql_sizing VALUES (25000, 'MAXIMUM CURRENT DEFAULT TRANSFORM GROUP LENGTH', NULL, NULL);
1607 INSERT INTO sql_sizing VALUES (25001, 'MAXIMUM CURRENT TRANSFORM GROUP LENGTH', NULL, NULL);
1608 INSERT INTO sql_sizing VALUES (25002, 'MAXIMUM CURRENT PATH LENGTH', 0, NULL);
1609 INSERT INTO sql_sizing VALUES (25003, 'MAXIMUM CURRENT ROLE LENGTH', NULL, NULL);
1610 INSERT INTO sql_sizing VALUES (25004, 'MAXIMUM SESSION USER LENGTH', 63, NULL);
1611 INSERT INTO sql_sizing VALUES (25005, 'MAXIMUM SYSTEM USER LENGTH', 63, NULL);
1612
1613 UPDATE sql_sizing
1614     SET supported_value = (SELECT typlen-1 FROM pg_catalog.pg_type WHERE typname = 'name'),
1615         comments = 'Might be less, depending on character set.'
1616     WHERE supported_value = 63;
1617
1618 GRANT SELECT ON sql_sizing TO PUBLIC;
1619
1620
1621 /*
1622  * 5.57
1623  * SQL_SIZING_PROFILES table
1624  */
1625
1626 -- The data in this table are defined by various profiles of SQL.
1627 -- Since we don't have any information about such profiles, we provide
1628 -- an empty table.
1629
1630 CREATE TABLE sql_sizing_profiles (
1631     sizing_id       cardinal_number,
1632     sizing_name     character_data,
1633     profile_id      character_data,
1634     required_value  cardinal_number,
1635     comments        character_data
1636 ) WITHOUT OIDS;
1637
1638 GRANT SELECT ON sql_sizing_profiles TO PUBLIC;
1639
1640
1641 /*
1642  * 5.58
1643  * TABLE_CONSTRAINTS view
1644  */
1645
1646 CREATE VIEW table_constraints AS
1647     SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1648            CAST(nc.nspname AS sql_identifier) AS constraint_schema,
1649            CAST(c.conname AS sql_identifier) AS constraint_name,
1650            CAST(current_database() AS sql_identifier) AS table_catalog,
1651            CAST(nr.nspname AS sql_identifier) AS table_schema,
1652            CAST(r.relname AS sql_identifier) AS table_name,
1653            CAST(
1654              CASE c.contype WHEN 'c' THEN 'CHECK'
1655                             WHEN 'f' THEN 'FOREIGN KEY'
1656                             WHEN 'p' THEN 'PRIMARY KEY'
1657                             WHEN 'u' THEN 'UNIQUE' END
1658              AS character_data) AS constraint_type,
1659            CAST(CASE WHEN c.condeferrable THEN 'YES' ELSE 'NO' END AS yes_or_no)
1660              AS is_deferrable,
1661            CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS yes_or_no)
1662              AS initially_deferred
1663
1664     FROM pg_namespace nc,
1665          pg_namespace nr,
1666          pg_constraint c,
1667          pg_class r
1668
1669     WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace
1670           AND c.conrelid = r.oid
1671           AND c.contype NOT IN ('t', 'x')  -- ignore nonstandard constraints
1672           AND r.relkind = 'r'
1673           AND (NOT pg_is_other_temp_schema(nr.oid))
1674           AND (pg_has_role(r.relowner, 'USAGE')
1675                -- SELECT privilege omitted, per SQL standard
1676                OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1677                OR has_any_column_privilege(r.oid, 'INSERT, UPDATE, REFERENCES') )
1678
1679     UNION ALL
1680
1681     -- not-null constraints
1682
1683     SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1684            CAST(nr.nspname AS sql_identifier) AS constraint_schema,
1685            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
1686            CAST(current_database() AS sql_identifier) AS table_catalog,
1687            CAST(nr.nspname AS sql_identifier) AS table_schema,
1688            CAST(r.relname AS sql_identifier) AS table_name,
1689            CAST('CHECK' AS character_data) AS constraint_type,
1690            CAST('NO' AS yes_or_no) AS is_deferrable,
1691            CAST('NO' AS yes_or_no) AS initially_deferred
1692
1693     FROM pg_namespace nr,
1694          pg_class r,
1695          pg_attribute a
1696
1697     WHERE nr.oid = r.relnamespace
1698           AND r.oid = a.attrelid
1699           AND a.attnotnull
1700           AND a.attnum > 0
1701           AND NOT a.attisdropped
1702           AND r.relkind = 'r'
1703           AND (NOT pg_is_other_temp_schema(nr.oid))
1704           AND (pg_has_role(r.relowner, 'USAGE')
1705                -- SELECT privilege omitted, per SQL standard
1706                OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1707                OR has_any_column_privilege(r.oid, 'INSERT, UPDATE, REFERENCES') );
1708
1709 GRANT SELECT ON table_constraints TO PUBLIC;
1710
1711
1712 /*
1713  * 5.59
1714  * TABLE_METHOD_PRIVILEGES view
1715  */
1716
1717 -- feature not supported
1718
1719
1720 /*
1721  * 5.60
1722  * TABLE_PRIVILEGES view
1723  */
1724
1725 CREATE VIEW table_privileges AS
1726     SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1727            CAST(grantee.rolname AS sql_identifier) AS grantee,
1728            CAST(current_database() AS sql_identifier) AS table_catalog,
1729            CAST(nc.nspname AS sql_identifier) AS table_schema,
1730            CAST(c.relname AS sql_identifier) AS table_name,
1731            CAST(c.prtype AS character_data) AS privilege_type,
1732            CAST(
1733              CASE WHEN
1734                   -- object owner always has grant options
1735                   pg_has_role(grantee.oid, c.relowner, 'USAGE')
1736                   OR c.grantable
1737                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable,
1738            CAST('NO' AS yes_or_no) AS with_hierarchy
1739
1740     FROM (
1741             SELECT oid, relname, relnamespace, relkind, relowner, (aclexplode(relacl)).* FROM pg_class
1742          ) AS c (oid, relname, relnamespace, relkind, relowner, grantor, grantee, prtype, grantable),
1743          pg_namespace nc,
1744          pg_authid u_grantor,
1745          (
1746            SELECT oid, rolname FROM pg_authid
1747            UNION ALL
1748            SELECT 0::oid, 'PUBLIC'
1749          ) AS grantee (oid, rolname)
1750
1751     WHERE c.relnamespace = nc.oid
1752           AND c.relkind IN ('r', 'v')
1753           AND c.grantee = grantee.oid
1754           AND c.grantor = u_grantor.oid
1755           AND c.prtype IN ('INSERT', 'SELECT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER')
1756           AND (pg_has_role(u_grantor.oid, 'USAGE')
1757                OR pg_has_role(grantee.oid, 'USAGE')
1758                OR grantee.rolname = 'PUBLIC');
1759
1760 GRANT SELECT ON table_privileges TO PUBLIC;
1761
1762
1763 /*
1764  * 5.40
1765  * ROLE_TABLE_GRANTS view
1766  */
1767
1768 CREATE VIEW role_table_grants AS
1769     SELECT grantor,
1770            grantee,
1771            table_catalog,
1772            table_schema,
1773            table_name,
1774            privilege_type,
1775            is_grantable,
1776            with_hierarchy
1777     FROM table_privileges
1778     WHERE grantor IN (SELECT role_name FROM enabled_roles)
1779           OR grantee IN (SELECT role_name FROM enabled_roles);
1780
1781 GRANT SELECT ON role_table_grants TO PUBLIC;
1782
1783
1784 /*
1785  * 5.61
1786  * TABLES view
1787  */
1788
1789 CREATE VIEW tables AS
1790     SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
1791            CAST(nc.nspname AS sql_identifier) AS table_schema,
1792            CAST(c.relname AS sql_identifier) AS table_name,
1793
1794            CAST(
1795              CASE WHEN nc.oid = pg_my_temp_schema() THEN 'LOCAL TEMPORARY'
1796                   WHEN c.relkind = 'r' THEN 'BASE TABLE'
1797                   WHEN c.relkind = 'v' THEN 'VIEW'
1798                   WHEN c.relkind = 'f' THEN 'FOREIGN TABLE'
1799                   ELSE null END
1800              AS character_data) AS table_type,
1801
1802            CAST(null AS sql_identifier) AS self_referencing_column_name,
1803            CAST(null AS character_data) AS reference_generation,
1804
1805            CAST(CASE WHEN t.typname IS NOT NULL THEN current_database() ELSE null END AS sql_identifier) AS user_defined_type_catalog,
1806            CAST(nt.nspname AS sql_identifier) AS user_defined_type_schema,
1807            CAST(t.typname AS sql_identifier) AS user_defined_type_name,
1808
1809            CAST(CASE WHEN c.relkind = 'r'
1810                           OR (c.relkind = 'v'
1811                               AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '3' AND is_instead))
1812                 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_insertable_into,
1813
1814            CAST(CASE WHEN t.typname IS NOT NULL THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_typed,
1815            CAST(
1816              CASE WHEN nc.oid = pg_my_temp_schema() THEN 'PRESERVE' -- FIXME
1817                   ELSE null END
1818              AS character_data) AS commit_action
1819
1820     FROM pg_namespace nc JOIN pg_class c ON (nc.oid = c.relnamespace)
1821            LEFT JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON (c.reloftype = t.oid)
1822
1823     WHERE c.relkind IN ('r', 'v', 'f')
1824           AND (NOT pg_is_other_temp_schema(nc.oid))
1825           AND (pg_has_role(c.relowner, 'USAGE')
1826                OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1827                OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES') );
1828
1829 GRANT SELECT ON tables TO PUBLIC;
1830
1831
1832 /*
1833  * 5.62
1834  * TRANSFORMS view
1835  */
1836
1837 -- feature not supported
1838
1839
1840 /*
1841  * 5.63
1842  * TRANSLATIONS view
1843  */
1844
1845 -- feature not supported
1846
1847
1848 /*
1849  * 5.64
1850  * TRIGGERED_UPDATE_COLUMNS view
1851  */
1852
1853 CREATE VIEW triggered_update_columns AS
1854     SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1855            CAST(n.nspname AS sql_identifier) AS trigger_schema,
1856            CAST(t.tgname AS sql_identifier) AS trigger_name,
1857            CAST(current_database() AS sql_identifier) AS event_object_catalog,
1858            CAST(n.nspname AS sql_identifier) AS event_object_schema,
1859            CAST(c.relname AS sql_identifier) AS event_object_table,
1860            CAST(a.attname AS sql_identifier) AS event_object_column
1861
1862     FROM pg_namespace n, pg_class c, pg_trigger t,
1863          (SELECT tgoid, (ta0.tgat).x AS tgattnum, (ta0.tgat).n AS tgattpos
1864           FROM (SELECT oid AS tgoid, information_schema._pg_expandarray(tgattr) AS tgat FROM pg_trigger) AS ta0) AS ta,
1865          pg_attribute a
1866
1867     WHERE n.oid = c.relnamespace
1868           AND c.oid = t.tgrelid
1869           AND t.oid = ta.tgoid
1870           AND (a.attrelid, a.attnum) = (t.tgrelid, ta.tgattnum)
1871           AND NOT t.tgisinternal
1872           AND (NOT pg_is_other_temp_schema(n.oid))
1873           AND (pg_has_role(c.relowner, 'USAGE')
1874                -- SELECT privilege omitted, per SQL standard
1875                OR has_column_privilege(c.oid, a.attnum, 'INSERT, UPDATE, REFERENCES') );
1876
1877 GRANT SELECT ON triggered_update_columns TO PUBLIC;
1878
1879
1880 /*
1881  * 5.65
1882  * TRIGGER_COLUMN_USAGE view
1883  */
1884
1885 -- not tracked by PostgreSQL
1886
1887
1888 /*
1889  * 5.66
1890  * TRIGGER_ROUTINE_USAGE view
1891  */
1892
1893 -- not tracked by PostgreSQL
1894
1895
1896 /*
1897  * 5.67
1898  * TRIGGER_SEQUENCE_USAGE view
1899  */
1900
1901 -- not tracked by PostgreSQL
1902
1903
1904 /*
1905  * 5.68
1906  * TRIGGER_TABLE_USAGE view
1907  */
1908
1909 -- not tracked by PostgreSQL
1910
1911
1912 /*
1913  * 5.69
1914  * TRIGGERS view
1915  */
1916
1917 CREATE VIEW triggers AS
1918     SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1919            CAST(n.nspname AS sql_identifier) AS trigger_schema,
1920            CAST(t.tgname AS sql_identifier) AS trigger_name,
1921            CAST(em.text AS character_data) AS event_manipulation,
1922            CAST(current_database() AS sql_identifier) AS event_object_catalog,
1923            CAST(n.nspname AS sql_identifier) AS event_object_schema,
1924            CAST(c.relname AS sql_identifier) AS event_object_table,
1925            CAST(null AS cardinal_number) AS action_order,
1926            -- XXX strange hacks follow
1927            CAST(
1928              CASE WHEN pg_has_role(c.relowner, 'USAGE')
1929                THEN (SELECT m[1] FROM regexp_matches(pg_get_triggerdef(t.oid), E'.{35,} WHEN \\((.+)\\) EXECUTE PROCEDURE') AS rm(m) LIMIT 1)
1930                ELSE null END
1931              AS character_data) AS action_condition,
1932            CAST(
1933              substring(pg_get_triggerdef(t.oid) from
1934                        position('EXECUTE PROCEDURE' in substring(pg_get_triggerdef(t.oid) from 48)) + 47)
1935              AS character_data) AS action_statement,
1936            CAST(
1937              -- hard-wired reference to TRIGGER_TYPE_ROW
1938              CASE t.tgtype & 1 WHEN 1 THEN 'ROW' ELSE 'STATEMENT' END
1939              AS character_data) AS action_orientation,
1940            CAST(
1941              -- hard-wired refs to TRIGGER_TYPE_BEFORE, TRIGGER_TYPE_INSTEAD
1942              CASE t.tgtype & 66 WHEN 2 THEN 'BEFORE' WHEN 64 THEN 'INSTEAD OF' ELSE 'AFTER' END
1943              AS character_data) AS action_timing,
1944            CAST(null AS sql_identifier) AS action_reference_old_table,
1945            CAST(null AS sql_identifier) AS action_reference_new_table,
1946            CAST(null AS sql_identifier) AS action_reference_old_row,
1947            CAST(null AS sql_identifier) AS action_reference_new_row,
1948            CAST(null AS time_stamp) AS created
1949
1950     FROM pg_namespace n, pg_class c, pg_trigger t,
1951          -- hard-wired refs to TRIGGER_TYPE_INSERT, TRIGGER_TYPE_DELETE,
1952          -- TRIGGER_TYPE_UPDATE; we intentionally omit TRIGGER_TYPE_TRUNCATE
1953          (VALUES (4, 'INSERT'),
1954                  (8, 'DELETE'),
1955                  (16, 'UPDATE')) AS em (num, text)
1956
1957     WHERE n.oid = c.relnamespace
1958           AND c.oid = t.tgrelid
1959           AND t.tgtype & em.num <> 0
1960           AND NOT t.tgisinternal
1961           AND (NOT pg_is_other_temp_schema(n.oid))
1962           AND (pg_has_role(c.relowner, 'USAGE')
1963                -- SELECT privilege omitted, per SQL standard
1964                OR has_table_privilege(c.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1965                OR has_any_column_privilege(c.oid, 'INSERT, UPDATE, REFERENCES') );
1966
1967 GRANT SELECT ON triggers TO PUBLIC;
1968
1969
1970 /*
1971  * 5.70
1972  * UDT_PRIVILEGES view
1973  */
1974
1975 -- feature not supported
1976
1977
1978 /*
1979  * 5.71
1980  * USAGE_PRIVILEGES view
1981  */
1982
1983 CREATE VIEW usage_privileges AS
1984
1985     /* domains */
1986     -- Domains have no real privileges, so we represent all domains with implicit usage privilege here.
1987     SELECT CAST(u.rolname AS sql_identifier) AS grantor,
1988            CAST('PUBLIC' AS sql_identifier) AS grantee,
1989            CAST(current_database() AS sql_identifier) AS object_catalog,
1990            CAST(n.nspname AS sql_identifier) AS object_schema,
1991            CAST(t.typname AS sql_identifier) AS object_name,
1992            CAST('DOMAIN' AS character_data) AS object_type,
1993            CAST('USAGE' AS character_data) AS privilege_type,
1994            CAST('NO' AS yes_or_no) AS is_grantable
1995
1996     FROM pg_authid u,
1997          pg_namespace n,
1998          pg_type t
1999
2000     WHERE u.oid = t.typowner
2001           AND t.typnamespace = n.oid
2002           AND t.typtype = 'd'
2003
2004     UNION ALL
2005
2006     /* foreign-data wrappers */
2007     SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2008            CAST(grantee.rolname AS sql_identifier) AS grantee,
2009            CAST(current_database() AS sql_identifier) AS object_catalog,
2010            CAST('' AS sql_identifier) AS object_schema,
2011            CAST(fdw.fdwname AS sql_identifier) AS object_name,
2012            CAST('FOREIGN DATA WRAPPER' AS character_data) AS object_type,
2013            CAST('USAGE' AS character_data) AS privilege_type,
2014            CAST(
2015              CASE WHEN
2016                   -- object owner always has grant options
2017                   pg_has_role(grantee.oid, fdw.fdwowner, 'USAGE')
2018                   OR fdw.grantable
2019                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2020
2021     FROM (
2022             SELECT fdwname, fdwowner, (aclexplode(fdwacl)).* FROM pg_foreign_data_wrapper
2023          ) AS fdw (fdwname, fdwowner, grantor, grantee, prtype, grantable),
2024          pg_authid u_grantor,
2025          (
2026            SELECT oid, rolname FROM pg_authid
2027            UNION ALL
2028            SELECT 0::oid, 'PUBLIC'
2029          ) AS grantee (oid, rolname)
2030
2031     WHERE u_grantor.oid = fdw.grantor
2032           AND grantee.oid = fdw.grantee
2033           AND fdw.prtype IN ('USAGE')
2034           AND (pg_has_role(u_grantor.oid, 'USAGE')
2035                OR pg_has_role(grantee.oid, 'USAGE')
2036                OR grantee.rolname = 'PUBLIC')
2037
2038     UNION ALL
2039
2040     /* foreign servers */
2041     SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2042            CAST(grantee.rolname AS sql_identifier) AS grantee,
2043            CAST(current_database() AS sql_identifier) AS object_catalog,
2044            CAST('' AS sql_identifier) AS object_schema,
2045            CAST(srv.srvname AS sql_identifier) AS object_name,
2046            CAST('FOREIGN SERVER' AS character_data) AS object_type,
2047            CAST('USAGE' AS character_data) AS privilege_type,
2048            CAST(
2049              CASE WHEN
2050                   -- object owner always has grant options
2051                   pg_has_role(grantee.oid, srv.srvowner, 'USAGE')
2052                   OR srv.grantable
2053                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2054
2055     FROM (
2056             SELECT srvname, srvowner, (aclexplode(srvacl)).* FROM pg_foreign_server
2057          ) AS srv (srvname, srvowner, grantor, grantee, prtype, grantable),
2058          pg_authid u_grantor,
2059          (
2060            SELECT oid, rolname FROM pg_authid
2061            UNION ALL
2062            SELECT 0::oid, 'PUBLIC'
2063          ) AS grantee (oid, rolname)
2064
2065     WHERE u_grantor.oid = srv.grantor
2066           AND grantee.oid = srv.grantee
2067           AND srv.prtype IN ('USAGE')
2068           AND (pg_has_role(u_grantor.oid, 'USAGE')
2069                OR pg_has_role(grantee.oid, 'USAGE')
2070                OR grantee.rolname = 'PUBLIC');
2071
2072 GRANT SELECT ON usage_privileges TO PUBLIC;
2073
2074
2075 /*
2076  * 5.42
2077  * ROLE_USAGE_GRANTS view
2078  */
2079
2080 CREATE VIEW role_usage_grants AS
2081     SELECT grantor,
2082            grantee,
2083            object_catalog,
2084            object_schema,
2085            object_name,
2086            object_type,
2087            privilege_type,
2088            is_grantable
2089     FROM usage_privileges
2090     WHERE grantor IN (SELECT role_name FROM enabled_roles)
2091           OR grantee IN (SELECT role_name FROM enabled_roles);
2092
2093 GRANT SELECT ON role_usage_grants TO PUBLIC;
2094
2095
2096 /*
2097  * 5.72
2098  * USER_DEFINED_TYPES view
2099  */
2100
2101 -- feature not supported
2102
2103
2104 /*
2105  * 5.73
2106  * VIEW_COLUMN_USAGE
2107  */
2108
2109 CREATE VIEW view_column_usage AS
2110     SELECT DISTINCT
2111            CAST(current_database() AS sql_identifier) AS view_catalog,
2112            CAST(nv.nspname AS sql_identifier) AS view_schema,
2113            CAST(v.relname AS sql_identifier) AS view_name,
2114            CAST(current_database() AS sql_identifier) AS table_catalog,
2115            CAST(nt.nspname AS sql_identifier) AS table_schema,
2116            CAST(t.relname AS sql_identifier) AS table_name,
2117            CAST(a.attname AS sql_identifier) AS column_name
2118
2119     FROM pg_namespace nv, pg_class v, pg_depend dv,
2120          pg_depend dt, pg_class t, pg_namespace nt,
2121          pg_attribute a
2122
2123     WHERE nv.oid = v.relnamespace
2124           AND v.relkind = 'v'
2125           AND v.oid = dv.refobjid
2126           AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2127           AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2128           AND dv.deptype = 'i'
2129           AND dv.objid = dt.objid
2130           AND dv.refobjid <> dt.refobjid
2131           AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2132           AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2133           AND dt.refobjid = t.oid
2134           AND t.relnamespace = nt.oid
2135           AND t.relkind IN ('r', 'v', 'f')
2136           AND t.oid = a.attrelid
2137           AND dt.refobjsubid = a.attnum
2138           AND pg_has_role(t.relowner, 'USAGE');
2139
2140 GRANT SELECT ON view_column_usage TO PUBLIC;
2141
2142
2143 /*
2144  * 5.74
2145  * VIEW_ROUTINE_USAGE
2146  */
2147
2148 CREATE VIEW view_routine_usage AS
2149     SELECT DISTINCT
2150            CAST(current_database() AS sql_identifier) AS table_catalog,
2151            CAST(nv.nspname AS sql_identifier) AS table_schema,
2152            CAST(v.relname AS sql_identifier) AS table_name,
2153            CAST(current_database() AS sql_identifier) AS specific_catalog,
2154            CAST(np.nspname AS sql_identifier) AS specific_schema,
2155            CAST(p.proname || '_' || CAST(p.oid AS text)  AS sql_identifier) AS specific_name
2156
2157     FROM pg_namespace nv, pg_class v, pg_depend dv,
2158          pg_depend dp, pg_proc p, pg_namespace np
2159
2160     WHERE nv.oid = v.relnamespace
2161           AND v.relkind = 'v'
2162           AND v.oid = dv.refobjid
2163           AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2164           AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2165           AND dv.deptype = 'i'
2166           AND dv.objid = dp.objid
2167           AND dp.classid = 'pg_catalog.pg_rewrite'::regclass
2168           AND dp.refclassid = 'pg_catalog.pg_proc'::regclass
2169           AND dp.refobjid = p.oid
2170           AND p.pronamespace = np.oid
2171           AND pg_has_role(p.proowner, 'USAGE');
2172
2173 GRANT SELECT ON view_routine_usage TO PUBLIC;
2174
2175
2176 /*
2177  * 5.75
2178  * VIEW_TABLE_USAGE
2179  */
2180
2181 CREATE VIEW view_table_usage AS
2182     SELECT DISTINCT
2183            CAST(current_database() AS sql_identifier) AS view_catalog,
2184            CAST(nv.nspname AS sql_identifier) AS view_schema,
2185            CAST(v.relname AS sql_identifier) AS view_name,
2186            CAST(current_database() AS sql_identifier) AS table_catalog,
2187            CAST(nt.nspname AS sql_identifier) AS table_schema,
2188            CAST(t.relname AS sql_identifier) AS table_name
2189
2190     FROM pg_namespace nv, pg_class v, pg_depend dv,
2191          pg_depend dt, pg_class t, pg_namespace nt
2192
2193     WHERE nv.oid = v.relnamespace
2194           AND v.relkind = 'v'
2195           AND v.oid = dv.refobjid
2196           AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2197           AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2198           AND dv.deptype = 'i'
2199           AND dv.objid = dt.objid
2200           AND dv.refobjid <> dt.refobjid
2201           AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2202           AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2203           AND dt.refobjid = t.oid
2204           AND t.relnamespace = nt.oid
2205           AND t.relkind IN ('r', 'v', 'f')
2206           AND pg_has_role(t.relowner, 'USAGE');
2207
2208 GRANT SELECT ON view_table_usage TO PUBLIC;
2209
2210
2211 /*
2212  * 5.76
2213  * VIEWS view
2214  */
2215
2216 CREATE VIEW views AS
2217     SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
2218            CAST(nc.nspname AS sql_identifier) AS table_schema,
2219            CAST(c.relname AS sql_identifier) AS table_name,
2220
2221            CAST(
2222              CASE WHEN pg_has_role(c.relowner, 'USAGE')
2223                   THEN pg_get_viewdef(c.oid)
2224                   ELSE null END
2225              AS character_data) AS view_definition,
2226
2227            CAST('NONE' AS character_data) AS check_option,
2228
2229            CAST(
2230              CASE WHEN EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '2' AND is_instead)
2231                    AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '4' AND is_instead)
2232                   THEN 'YES' ELSE 'NO' END
2233              AS yes_or_no) AS is_updatable,
2234
2235            CAST(
2236              CASE WHEN EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '3' AND is_instead)
2237                   THEN 'YES' ELSE 'NO' END
2238              AS yes_or_no) AS is_insertable_into,
2239
2240            CAST(
2241              -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_UPDATE
2242              CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 81 = 81)
2243                   THEN 'YES' ELSE 'NO' END
2244            AS yes_or_no) AS is_trigger_updatable,
2245
2246            CAST(
2247              -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_DELETE
2248              CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 73 = 73)
2249                   THEN 'YES' ELSE 'NO' END
2250            AS yes_or_no) AS is_trigger_deletable,
2251
2252            CAST(
2253              -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_INSERT
2254              CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 69 = 69)
2255                   THEN 'YES' ELSE 'NO' END
2256            AS yes_or_no) AS is_trigger_insertable_into
2257
2258     FROM pg_namespace nc, pg_class c
2259
2260     WHERE c.relnamespace = nc.oid
2261           AND c.relkind = 'v'
2262           AND (NOT pg_is_other_temp_schema(nc.oid))
2263           AND (pg_has_role(c.relowner, 'USAGE')
2264                OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
2265                OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES') );
2266
2267 GRANT SELECT ON views TO PUBLIC;
2268
2269
2270 -- The following views have dependencies that force them to appear out of order.
2271
2272 /*
2273  * 5.24
2274  * DATA_TYPE_PRIVILEGES view
2275  */
2276
2277 CREATE VIEW data_type_privileges AS
2278     SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2279            CAST(x.objschema AS sql_identifier) AS object_schema,
2280            CAST(x.objname AS sql_identifier) AS object_name,
2281            CAST(x.objtype AS character_data) AS object_type,
2282            CAST(x.objdtdid AS sql_identifier) AS dtd_identifier
2283
2284     FROM
2285       (
2286         SELECT udt_schema, udt_name, 'USER-DEFINED TYPE'::text, dtd_identifier FROM attributes
2287         UNION ALL
2288         SELECT table_schema, table_name, 'TABLE'::text, dtd_identifier FROM columns
2289         UNION ALL
2290         SELECT domain_schema, domain_name, 'DOMAIN'::text, dtd_identifier FROM domains
2291         UNION ALL
2292         SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM parameters
2293         UNION ALL
2294         SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM routines
2295       ) AS x (objschema, objname, objtype, objdtdid);
2296
2297 GRANT SELECT ON data_type_privileges TO PUBLIC;
2298
2299
2300 /*
2301  * 5.29
2302  * ELEMENT_TYPES view
2303  */
2304
2305 CREATE VIEW element_types AS
2306     SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2307            CAST(n.nspname AS sql_identifier) AS object_schema,
2308            CAST(x.objname AS sql_identifier) AS object_name,
2309            CAST(x.objtype AS character_data) AS object_type,
2310            CAST(x.objdtdid AS sql_identifier) AS collection_type_identifier,
2311            CAST(
2312              CASE WHEN nbt.nspname = 'pg_catalog' THEN format_type(bt.oid, null)
2313                   ELSE 'USER-DEFINED' END AS character_data) AS data_type,
2314
2315            CAST(null AS cardinal_number) AS character_maximum_length,
2316            CAST(null AS cardinal_number) AS character_octet_length,
2317            CAST(null AS sql_identifier) AS character_set_catalog,
2318            CAST(null AS sql_identifier) AS character_set_schema,
2319            CAST(null AS sql_identifier) AS character_set_name,
2320            CAST(null AS sql_identifier) AS collation_catalog,
2321            CAST(null AS sql_identifier) AS collation_schema,
2322            CAST(null AS sql_identifier) AS collation_name,
2323            CAST(null AS cardinal_number) AS numeric_precision,
2324            CAST(null AS cardinal_number) AS numeric_precision_radix,
2325            CAST(null AS cardinal_number) AS numeric_scale,
2326            CAST(null AS cardinal_number) AS datetime_precision,
2327            CAST(null AS character_data) AS interval_type,
2328            CAST(null AS character_data) AS interval_precision,
2329
2330            CAST(null AS character_data) AS domain_default, -- XXX maybe a bug in the standard
2331
2332            CAST(current_database() AS sql_identifier) AS udt_catalog,
2333            CAST(nbt.nspname AS sql_identifier) AS udt_schema,
2334            CAST(bt.typname AS sql_identifier) AS udt_name,
2335
2336            CAST(null AS sql_identifier) AS scope_catalog,
2337            CAST(null AS sql_identifier) AS scope_schema,
2338            CAST(null AS sql_identifier) AS scope_name,
2339
2340            CAST(null AS cardinal_number) AS maximum_cardinality,
2341            CAST('a' || CAST(x.objdtdid AS text) AS sql_identifier) AS dtd_identifier
2342
2343     FROM pg_namespace n, pg_type at, pg_namespace nbt, pg_type bt,
2344          (
2345            /* columns */
2346            SELECT c.relnamespace, CAST(c.relname AS sql_identifier),
2347                   'TABLE'::text, a.attnum, a.atttypid
2348            FROM pg_class c, pg_attribute a
2349            WHERE c.oid = a.attrelid
2350                  AND c.relkind IN ('r', 'v', 'f')
2351                  AND attnum > 0 AND NOT attisdropped
2352
2353            UNION ALL
2354
2355            /* domains */
2356            SELECT t.typnamespace, CAST(t.typname AS sql_identifier),
2357                   'DOMAIN'::text, 1, t.typbasetype
2358            FROM pg_type t
2359            WHERE t.typtype = 'd'
2360
2361            UNION ALL
2362
2363            /* parameters */
2364            SELECT pronamespace, CAST(proname || '_' || CAST(oid AS text) AS sql_identifier),
2365                   'ROUTINE'::text, (ss.x).n, (ss.x).x
2366            FROM (SELECT p.pronamespace, p.proname, p.oid,
2367                         _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
2368                  FROM pg_proc p) AS ss
2369
2370            UNION ALL
2371
2372            /* result types */
2373            SELECT p.pronamespace, CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier),
2374                   'ROUTINE'::text, 0, p.prorettype
2375            FROM pg_proc p
2376
2377          ) AS x (objschema, objname, objtype, objdtdid, objtypeid)
2378
2379     WHERE n.oid = x.objschema
2380           AND at.oid = x.objtypeid
2381           AND (at.typelem <> 0 AND at.typlen = -1)
2382           AND at.typelem = bt.oid
2383           AND nbt.oid = bt.typnamespace
2384
2385           AND (n.nspname, x.objname, x.objtype, CAST(x.objdtdid AS sql_identifier)) IN
2386               ( SELECT object_schema, object_name, object_type, dtd_identifier
2387                     FROM data_type_privileges );
2388
2389 GRANT SELECT ON element_types TO PUBLIC;
2390
2391
2392 -- SQL/MED views; these use section numbers from part 9 of the standard.
2393
2394 /* Base view for foreign-data wrappers */
2395 CREATE VIEW _pg_foreign_data_wrappers AS
2396     SELECT w.oid,
2397            w.fdwowner,
2398            w.fdwoptions,
2399            CAST(current_database() AS sql_identifier) AS foreign_data_wrapper_catalog,
2400            CAST(fdwname AS sql_identifier) AS foreign_data_wrapper_name,
2401            CAST(u.rolname AS sql_identifier) AS authorization_identifier,
2402            CAST('c' AS character_data) AS foreign_data_wrapper_language
2403     FROM pg_foreign_data_wrapper w, pg_authid u
2404     WHERE u.oid = w.fdwowner
2405           AND (pg_has_role(fdwowner, 'USAGE')
2406                OR has_foreign_data_wrapper_privilege(w.oid, 'USAGE'));
2407
2408
2409 /*
2410  * 24.4
2411  * FOREIGN_DATA_WRAPPER_OPTIONS view
2412  */
2413 CREATE VIEW foreign_data_wrapper_options AS
2414     SELECT foreign_data_wrapper_catalog,
2415            foreign_data_wrapper_name,
2416            CAST((pg_options_to_table(w.fdwoptions)).option_name AS sql_identifier) AS option_name,
2417            CAST((pg_options_to_table(w.fdwoptions)).option_value AS character_data) AS option_value
2418     FROM _pg_foreign_data_wrappers w;
2419
2420 GRANT SELECT ON foreign_data_wrapper_options TO PUBLIC;
2421
2422
2423 /*
2424  * 24.5
2425  * FOREIGN_DATA_WRAPPERS view
2426  */
2427 CREATE VIEW foreign_data_wrappers AS
2428     SELECT foreign_data_wrapper_catalog,
2429            foreign_data_wrapper_name,
2430            authorization_identifier,
2431            CAST(NULL AS character_data) AS library_name,
2432            foreign_data_wrapper_language
2433     FROM _pg_foreign_data_wrappers w;
2434
2435 GRANT SELECT ON foreign_data_wrappers TO PUBLIC;
2436
2437
2438 /* Base view for foreign servers */
2439 CREATE VIEW _pg_foreign_servers AS
2440     SELECT s.oid,
2441            s.srvoptions,
2442            CAST(current_database() AS sql_identifier) AS foreign_server_catalog,
2443            CAST(srvname AS sql_identifier) AS foreign_server_name,
2444            CAST(current_database() AS sql_identifier) AS foreign_data_wrapper_catalog,
2445            CAST(w.fdwname AS sql_identifier) AS foreign_data_wrapper_name,
2446            CAST(srvtype AS character_data) AS foreign_server_type,
2447            CAST(srvversion AS character_data) AS foreign_server_version,
2448            CAST(u.rolname AS sql_identifier) AS authorization_identifier
2449     FROM pg_foreign_server s, pg_foreign_data_wrapper w, pg_authid u
2450     WHERE w.oid = s.srvfdw
2451           AND u.oid = s.srvowner
2452           AND (pg_has_role(s.srvowner, 'USAGE')
2453                OR has_server_privilege(s.oid, 'USAGE'));
2454
2455
2456 /*
2457  * 24.6
2458  * FOREIGN_SERVER_OPTIONS view
2459  */
2460 CREATE VIEW foreign_server_options AS
2461     SELECT foreign_server_catalog,
2462            foreign_server_name,
2463            CAST((pg_options_to_table(s.srvoptions)).option_name AS sql_identifier) AS option_name,
2464            CAST((pg_options_to_table(s.srvoptions)).option_value AS character_data) AS option_value
2465     FROM _pg_foreign_servers s;
2466
2467 GRANT SELECT ON TABLE foreign_server_options TO PUBLIC;
2468
2469
2470 /*
2471  * 24.7
2472  * FOREIGN_SERVERS view
2473  */
2474 CREATE VIEW foreign_servers AS
2475     SELECT foreign_server_catalog,
2476            foreign_server_name,
2477            foreign_data_wrapper_catalog,
2478            foreign_data_wrapper_name,
2479            foreign_server_type,
2480            foreign_server_version,
2481            authorization_identifier
2482     FROM _pg_foreign_servers;
2483
2484 GRANT SELECT ON foreign_servers TO PUBLIC;
2485
2486
2487 /* Base view for foreign tables */
2488 CREATE VIEW _pg_foreign_tables AS
2489     SELECT
2490            CAST(current_database() AS sql_identifier) AS foreign_table_catalog,
2491            n.nspname AS foreign_table_schema,
2492            c.relname AS foreign_table_name,
2493            t.ftoptions AS ftoptions,
2494            CAST(current_database() AS sql_identifier) AS foreign_server_catalog,
2495            CAST(srvname AS sql_identifier) AS foreign_server_name,
2496            CAST(u.rolname AS sql_identifier) AS authorization_identifier
2497     FROM pg_foreign_table t, pg_foreign_server s, pg_foreign_data_wrapper w,
2498          pg_authid u, pg_namespace n, pg_class c
2499     WHERE w.oid = s.srvfdw
2500           AND u.oid = c.relowner
2501           AND (pg_has_role(c.relowner, 'USAGE')
2502                OR has_table_privilege(c.oid, 'SELECT')
2503                OR has_any_column_privilege(c.oid, 'SELECT'))
2504           AND n.oid = c.relnamespace
2505           AND c.oid = t.ftrelid
2506           AND c.relkind = 'f'
2507           AND s.oid = t.ftserver;
2508
2509
2510 /*
2511  * 24.8
2512  * FOREIGN_TABLE_OPTIONS view
2513  */
2514 CREATE VIEW foreign_table_options AS
2515     SELECT foreign_table_catalog,
2516            foreign_table_schema,
2517            foreign_table_name,
2518            CAST((pg_options_to_table(t.ftoptions)).option_name AS sql_identifier) AS option_name,
2519            CAST((pg_options_to_table(t.ftoptions)).option_value AS character_data) AS option_value
2520     FROM _pg_foreign_tables t;
2521
2522 GRANT SELECT ON TABLE foreign_table_options TO PUBLIC;
2523
2524
2525 /*
2526  * 24.9
2527  * FOREIGN_TABLES view
2528  */
2529 CREATE VIEW foreign_tables AS
2530     SELECT foreign_table_catalog,
2531            foreign_table_schema,
2532            foreign_table_name,
2533            foreign_server_catalog,
2534            foreign_server_name
2535     FROM _pg_foreign_tables;
2536
2537 GRANT SELECT ON foreign_tables TO PUBLIC;
2538
2539
2540
2541 /* Base view for user mappings */
2542 CREATE VIEW _pg_user_mappings AS
2543     SELECT um.oid,
2544            um.umoptions,
2545            um.umuser,
2546            CAST(COALESCE(u.rolname,'PUBLIC') AS sql_identifier ) AS authorization_identifier,
2547            s.foreign_server_catalog,
2548            s.foreign_server_name,
2549            s.authorization_identifier AS srvowner
2550     FROM pg_user_mapping um LEFT JOIN pg_authid u ON (u.oid = um.umuser),
2551          _pg_foreign_servers s
2552     WHERE s.oid = um.umserver;
2553
2554
2555 /*
2556  * 24.12
2557  * USER_MAPPING_OPTIONS view
2558  */
2559 CREATE VIEW user_mapping_options AS
2560     SELECT authorization_identifier,
2561            foreign_server_catalog,
2562            foreign_server_name,
2563            CAST((pg_options_to_table(um.umoptions)).option_name AS sql_identifier) AS option_name,
2564            CAST(CASE WHEN (umuser <> 0 AND authorization_identifier = current_user)
2565                        OR (umuser = 0 AND pg_has_role(srvowner, 'USAGE'))
2566                        OR (SELECT rolsuper FROM pg_authid WHERE rolname = current_user) THEN (pg_options_to_table(um.umoptions)).option_value
2567                      ELSE NULL END AS character_data) AS option_value
2568     FROM _pg_user_mappings um;
2569
2570 GRANT SELECT ON user_mapping_options TO PUBLIC;
2571
2572
2573 /*
2574  * 24.13
2575  * USER_MAPPINGS view
2576  */
2577 CREATE VIEW user_mappings AS
2578     SELECT authorization_identifier,
2579            foreign_server_catalog,
2580            foreign_server_name
2581     FROM _pg_user_mappings;
2582
2583 GRANT SELECT ON user_mappings TO PUBLIC;