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