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