granicus.if.org Git - postgresql/blob - src/backend/catalog/information_schema.sql

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