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('YES' AS yes_or_no) AS is_udt_dependent, -- FIXME?
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(
1866              CASE WHEN nc.oid = pg_my_temp_schema() THEN 'PRESERVE' -- FIXME
1867                   ELSE null END
1868              AS character_data) AS commit_action
1869
1870     FROM pg_namespace nc JOIN pg_class c ON (nc.oid = c.relnamespace)
1871            LEFT JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON (c.reloftype = t.oid)
1872
1873     WHERE c.relkind IN ('r', 'v', 'f')
1874           AND (NOT pg_is_other_temp_schema(nc.oid))
1875           AND (pg_has_role(c.relowner, 'USAGE')
1876                OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1877                OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES') );
1878
1879 GRANT SELECT ON tables TO PUBLIC;
1880
1881
1882 /*
1883  * 5.62
1884  * TRANSFORMS view
1885  */
1886
1887 -- feature not supported
1888
1889
1890 /*
1891  * 5.63
1892  * TRANSLATIONS view
1893  */
1894
1895 -- feature not supported
1896
1897
1898 /*
1899  * 5.64
1900  * TRIGGERED_UPDATE_COLUMNS view
1901  */
1902
1903 CREATE VIEW triggered_update_columns AS
1904     SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1905            CAST(n.nspname AS sql_identifier) AS trigger_schema,
1906            CAST(t.tgname AS sql_identifier) AS trigger_name,
1907            CAST(current_database() AS sql_identifier) AS event_object_catalog,
1908            CAST(n.nspname AS sql_identifier) AS event_object_schema,
1909            CAST(c.relname AS sql_identifier) AS event_object_table,
1910            CAST(a.attname AS sql_identifier) AS event_object_column
1911
1912     FROM pg_namespace n, pg_class c, pg_trigger t,
1913          (SELECT tgoid, (ta0.tgat).x AS tgattnum, (ta0.tgat).n AS tgattpos
1914           FROM (SELECT oid AS tgoid, information_schema._pg_expandarray(tgattr) AS tgat FROM pg_trigger) AS ta0) AS ta,
1915          pg_attribute a
1916
1917     WHERE n.oid = c.relnamespace
1918           AND c.oid = t.tgrelid
1919           AND t.oid = ta.tgoid
1920           AND (a.attrelid, a.attnum) = (t.tgrelid, ta.tgattnum)
1921           AND NOT t.tgisinternal
1922           AND (NOT pg_is_other_temp_schema(n.oid))
1923           AND (pg_has_role(c.relowner, 'USAGE')
1924                -- SELECT privilege omitted, per SQL standard
1925                OR has_column_privilege(c.oid, a.attnum, 'INSERT, UPDATE, REFERENCES') );
1926
1927 GRANT SELECT ON triggered_update_columns TO PUBLIC;
1928
1929
1930 /*
1931  * 5.65
1932  * TRIGGER_COLUMN_USAGE view
1933  */
1934
1935 -- not tracked by PostgreSQL
1936
1937
1938 /*
1939  * 5.66
1940  * TRIGGER_ROUTINE_USAGE view
1941  */
1942
1943 -- not tracked by PostgreSQL
1944
1945
1946 /*
1947  * 5.67
1948  * TRIGGER_SEQUENCE_USAGE view
1949  */
1950
1951 -- not tracked by PostgreSQL
1952
1953
1954 /*
1955  * 5.68
1956  * TRIGGER_TABLE_USAGE view
1957  */
1958
1959 -- not tracked by PostgreSQL
1960
1961
1962 /*
1963  * 5.69
1964  * TRIGGERS view
1965  */
1966
1967 CREATE VIEW triggers AS
1968     SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1969            CAST(n.nspname AS sql_identifier) AS trigger_schema,
1970            CAST(t.tgname AS sql_identifier) AS trigger_name,
1971            CAST(em.text AS character_data) AS event_manipulation,
1972            CAST(current_database() AS sql_identifier) AS event_object_catalog,
1973            CAST(n.nspname AS sql_identifier) AS event_object_schema,
1974            CAST(c.relname AS sql_identifier) AS event_object_table,
1975            CAST(null AS cardinal_number) AS action_order,
1976            -- XXX strange hacks follow
1977            CAST(
1978              CASE WHEN pg_has_role(c.relowner, 'USAGE')
1979                THEN (SELECT m[1] FROM regexp_matches(pg_get_triggerdef(t.oid), E'.{35,} WHEN \\((.+)\\) EXECUTE PROCEDURE') AS rm(m) LIMIT 1)
1980                ELSE null END
1981              AS character_data) AS action_condition,
1982            CAST(
1983              substring(pg_get_triggerdef(t.oid) from
1984                        position('EXECUTE PROCEDURE' in substring(pg_get_triggerdef(t.oid) from 48)) + 47)
1985              AS character_data) AS action_statement,
1986            CAST(
1987              -- hard-wired reference to TRIGGER_TYPE_ROW
1988              CASE t.tgtype & 1 WHEN 1 THEN 'ROW' ELSE 'STATEMENT' END
1989              AS character_data) AS action_orientation,
1990            CAST(
1991              -- hard-wired refs to TRIGGER_TYPE_BEFORE, TRIGGER_TYPE_INSTEAD
1992              CASE t.tgtype & 66 WHEN 2 THEN 'BEFORE' WHEN 64 THEN 'INSTEAD OF' ELSE 'AFTER' END
1993              AS character_data) AS action_timing,
1994            CAST(null AS sql_identifier) AS action_reference_old_table,
1995            CAST(null AS sql_identifier) AS action_reference_new_table,
1996            CAST(null AS sql_identifier) AS action_reference_old_row,
1997            CAST(null AS sql_identifier) AS action_reference_new_row,
1998            CAST(null AS time_stamp) AS created
1999
2000     FROM pg_namespace n, pg_class c, pg_trigger t,
2001          -- hard-wired refs to TRIGGER_TYPE_INSERT, TRIGGER_TYPE_DELETE,
2002          -- TRIGGER_TYPE_UPDATE; we intentionally omit TRIGGER_TYPE_TRUNCATE
2003          (VALUES (4, 'INSERT'),
2004                  (8, 'DELETE'),
2005                  (16, 'UPDATE')) AS em (num, text)
2006
2007     WHERE n.oid = c.relnamespace
2008           AND c.oid = t.tgrelid
2009           AND t.tgtype & em.num <> 0
2010           AND NOT t.tgisinternal
2011           AND (NOT pg_is_other_temp_schema(n.oid))
2012           AND (pg_has_role(c.relowner, 'USAGE')
2013                -- SELECT privilege omitted, per SQL standard
2014                OR has_table_privilege(c.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
2015                OR has_any_column_privilege(c.oid, 'INSERT, UPDATE, REFERENCES') );
2016
2017 GRANT SELECT ON triggers TO PUBLIC;
2018
2019
2020 /*
2021  * 5.70
2022  * UDT_PRIVILEGES view
2023  */
2024
2025 CREATE VIEW udt_privileges AS
2026     SELECT CAST(null AS sql_identifier) AS grantor,
2027            CAST('PUBLIC' AS sql_identifier) AS grantee,
2028            CAST(current_database() AS sql_identifier) AS udt_catalog,
2029            CAST(n.nspname AS sql_identifier) AS udt_schema,
2030            CAST(t.typname AS sql_identifier) AS udt_name,
2031            CAST('TYPE USAGE' AS character_data) AS privilege_type, -- sic
2032            CAST('NO' AS yes_or_no) AS is_grantable
2033
2034     FROM pg_authid u, pg_namespace n, pg_type t
2035
2036     WHERE u.oid = t.typowner
2037           AND n.oid = t.typnamespace
2038           AND t.typtype <> 'd'
2039           AND NOT (t.typelem <> 0 AND t.typlen = -1);
2040
2041 GRANT SELECT ON udt_privileges TO PUBLIC;
2042
2043
2044 /*
2045  * 5.43
2046  * ROLE_UDT_GRANTS view
2047  */
2048
2049 CREATE VIEW role_udt_grants AS
2050     SELECT grantor,
2051            grantee,
2052            udt_catalog,
2053            udt_schema,
2054            udt_name,
2055            privilege_type,
2056            is_grantable
2057     FROM udt_privileges
2058     WHERE grantor IN (SELECT role_name FROM enabled_roles)
2059           OR grantee IN (SELECT role_name FROM enabled_roles);
2060
2061 GRANT SELECT ON role_udt_grants TO PUBLIC;
2062
2063
2064 /*
2065  * 5.71
2066  * USAGE_PRIVILEGES view
2067  */
2068
2069 CREATE VIEW usage_privileges AS
2070
2071     /* collations */
2072     -- Collations have no real privileges, so we represent all collations with implicit usage privilege here.
2073     SELECT CAST(u.rolname AS sql_identifier) AS grantor,
2074            CAST('PUBLIC' AS sql_identifier) AS grantee,
2075            CAST(current_database() AS sql_identifier) AS object_catalog,
2076            CAST(n.nspname AS sql_identifier) AS object_schema,
2077            CAST(c.collname AS sql_identifier) AS object_name,
2078            CAST('COLLATION' AS character_data) AS object_type,
2079            CAST('USAGE' AS character_data) AS privilege_type,
2080            CAST('NO' AS yes_or_no) AS is_grantable
2081
2082     FROM pg_authid u,
2083          pg_namespace n,
2084          pg_collation c
2085
2086     WHERE u.oid = c.collowner
2087           AND c.collnamespace = n.oid
2088           AND collencoding IN (-1, (SELECT encoding FROM pg_database WHERE datname = current_database()))
2089
2090     UNION ALL
2091
2092     /* domains */
2093     -- Domains have no real privileges, so we represent all domains with implicit usage privilege here.
2094     SELECT CAST(u.rolname AS sql_identifier) AS grantor,
2095            CAST('PUBLIC' AS sql_identifier) AS grantee,
2096            CAST(current_database() AS sql_identifier) AS object_catalog,
2097            CAST(n.nspname AS sql_identifier) AS object_schema,
2098            CAST(t.typname AS sql_identifier) AS object_name,
2099            CAST('DOMAIN' AS character_data) AS object_type,
2100            CAST('USAGE' AS character_data) AS privilege_type,
2101            CAST('NO' AS yes_or_no) AS is_grantable
2102
2103     FROM pg_authid u,
2104          pg_namespace n,
2105          pg_type t
2106
2107     WHERE u.oid = t.typowner
2108           AND t.typnamespace = n.oid
2109           AND t.typtype = 'd'
2110
2111     UNION ALL
2112
2113     /* foreign-data wrappers */
2114     SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2115            CAST(grantee.rolname AS sql_identifier) AS grantee,
2116            CAST(current_database() AS sql_identifier) AS object_catalog,
2117            CAST('' AS sql_identifier) AS object_schema,
2118            CAST(fdw.fdwname AS sql_identifier) AS object_name,
2119            CAST('FOREIGN DATA WRAPPER' AS character_data) AS object_type,
2120            CAST('USAGE' AS character_data) AS privilege_type,
2121            CAST(
2122              CASE WHEN
2123                   -- object owner always has grant options
2124                   pg_has_role(grantee.oid, fdw.fdwowner, 'USAGE')
2125                   OR fdw.grantable
2126                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2127
2128     FROM (
2129             SELECT fdwname, fdwowner, (aclexplode(fdwacl)).* FROM pg_foreign_data_wrapper
2130          ) AS fdw (fdwname, fdwowner, grantor, grantee, prtype, grantable),
2131          pg_authid u_grantor,
2132          (
2133            SELECT oid, rolname FROM pg_authid
2134            UNION ALL
2135            SELECT 0::oid, 'PUBLIC'
2136          ) AS grantee (oid, rolname)
2137
2138     WHERE u_grantor.oid = fdw.grantor
2139           AND grantee.oid = fdw.grantee
2140           AND fdw.prtype IN ('USAGE')
2141           AND (pg_has_role(u_grantor.oid, 'USAGE')
2142                OR pg_has_role(grantee.oid, 'USAGE')
2143                OR grantee.rolname = 'PUBLIC')
2144
2145     UNION ALL
2146
2147     /* foreign servers */
2148     SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2149            CAST(grantee.rolname AS sql_identifier) AS grantee,
2150            CAST(current_database() AS sql_identifier) AS object_catalog,
2151            CAST('' AS sql_identifier) AS object_schema,
2152            CAST(srv.srvname AS sql_identifier) AS object_name,
2153            CAST('FOREIGN SERVER' AS character_data) AS object_type,
2154            CAST('USAGE' AS character_data) AS privilege_type,
2155            CAST(
2156              CASE WHEN
2157                   -- object owner always has grant options
2158                   pg_has_role(grantee.oid, srv.srvowner, 'USAGE')
2159                   OR srv.grantable
2160                   THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2161
2162     FROM (
2163             SELECT srvname, srvowner, (aclexplode(srvacl)).* FROM pg_foreign_server
2164          ) AS srv (srvname, srvowner, grantor, grantee, prtype, grantable),
2165          pg_authid u_grantor,
2166          (
2167            SELECT oid, rolname FROM pg_authid
2168            UNION ALL
2169            SELECT 0::oid, 'PUBLIC'
2170          ) AS grantee (oid, rolname)
2171
2172     WHERE u_grantor.oid = srv.grantor
2173           AND grantee.oid = srv.grantee
2174           AND srv.prtype IN ('USAGE')
2175           AND (pg_has_role(u_grantor.oid, 'USAGE')
2176                OR pg_has_role(grantee.oid, 'USAGE')
2177                OR grantee.rolname = 'PUBLIC');
2178
2179 GRANT SELECT ON usage_privileges TO PUBLIC;
2180
2181
2182 /*
2183  * 5.42
2184  * ROLE_USAGE_GRANTS view
2185  */
2186
2187 CREATE VIEW role_usage_grants AS
2188     SELECT grantor,
2189            grantee,
2190            object_catalog,
2191            object_schema,
2192            object_name,
2193            object_type,
2194            privilege_type,
2195            is_grantable
2196     FROM usage_privileges
2197     WHERE grantor IN (SELECT role_name FROM enabled_roles)
2198           OR grantee IN (SELECT role_name FROM enabled_roles);
2199
2200 GRANT SELECT ON role_usage_grants TO PUBLIC;
2201
2202
2203 /*
2204  * 5.72
2205  * USER_DEFINED_TYPES view
2206  */
2207
2208 CREATE VIEW user_defined_types AS
2209     SELECT CAST(current_database() AS sql_identifier) AS user_defined_type_catalog,
2210            CAST(n.nspname AS sql_identifier) AS user_defined_type_schema,
2211            CAST(c.relname AS sql_identifier) AS user_defined_type_name,
2212            CAST('STRUCTURED' AS character_data) AS user_defined_type_category,
2213            CAST('YES' AS yes_or_no) AS is_instantiable,
2214            CAST(null AS yes_or_no) AS is_final,
2215            CAST(null AS character_data) AS ordering_form,
2216            CAST(null AS character_data) AS ordering_category,
2217            CAST(null AS sql_identifier) AS ordering_routine_catalog,
2218            CAST(null AS sql_identifier) AS ordering_routine_schema,
2219            CAST(null AS sql_identifier) AS ordering_routine_name,
2220            CAST(null AS character_data) AS reference_type,
2221            CAST(null AS character_data) AS data_type,
2222            CAST(null AS cardinal_number) AS character_maximum_length,
2223            CAST(null AS cardinal_number) AS character_octet_length,
2224            CAST(null AS sql_identifier) AS character_set_catalog,
2225            CAST(null AS sql_identifier) AS character_set_schema,
2226            CAST(null AS sql_identifier) AS character_set_name,
2227            CAST(null AS sql_identifier) AS collation_catalog,
2228            CAST(null AS sql_identifier) AS collation_schema,
2229            CAST(null AS sql_identifier) AS collation_name,
2230            CAST(null AS cardinal_number) AS numeric_precision,
2231            CAST(null AS cardinal_number) AS numeric_precision_radix,
2232            CAST(null AS cardinal_number) AS numeric_scale,
2233            CAST(null AS cardinal_number) AS datetime_precision,
2234            CAST(null AS character_data) AS interval_type,
2235            CAST(null AS cardinal_number) AS interval_precision,
2236            CAST(null AS sql_identifier) AS source_dtd_identifier,
2237            CAST(null AS sql_identifier) AS ref_dtd_identifier
2238
2239     FROM pg_namespace n, pg_class c
2240
2241     WHERE n.oid = c.relnamespace
2242           AND c.relkind = 'c';
2243
2244 GRANT SELECT ON user_defined_types TO PUBLIC;
2245
2246
2247 /*
2248  * 5.73
2249  * VIEW_COLUMN_USAGE
2250  */
2251
2252 CREATE VIEW view_column_usage AS
2253     SELECT DISTINCT
2254            CAST(current_database() AS sql_identifier) AS view_catalog,
2255            CAST(nv.nspname AS sql_identifier) AS view_schema,
2256            CAST(v.relname AS sql_identifier) AS view_name,
2257            CAST(current_database() AS sql_identifier) AS table_catalog,
2258            CAST(nt.nspname AS sql_identifier) AS table_schema,
2259            CAST(t.relname AS sql_identifier) AS table_name,
2260            CAST(a.attname AS sql_identifier) AS column_name
2261
2262     FROM pg_namespace nv, pg_class v, pg_depend dv,
2263          pg_depend dt, pg_class t, pg_namespace nt,
2264          pg_attribute a
2265
2266     WHERE nv.oid = v.relnamespace
2267           AND v.relkind = 'v'
2268           AND v.oid = dv.refobjid
2269           AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2270           AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2271           AND dv.deptype = 'i'
2272           AND dv.objid = dt.objid
2273           AND dv.refobjid <> dt.refobjid
2274           AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2275           AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2276           AND dt.refobjid = t.oid
2277           AND t.relnamespace = nt.oid
2278           AND t.relkind IN ('r', 'v', 'f')
2279           AND t.oid = a.attrelid
2280           AND dt.refobjsubid = a.attnum
2281           AND pg_has_role(t.relowner, 'USAGE');
2282
2283 GRANT SELECT ON view_column_usage TO PUBLIC;
2284
2285
2286 /*
2287  * 5.74
2288  * VIEW_ROUTINE_USAGE
2289  */
2290
2291 CREATE VIEW view_routine_usage AS
2292     SELECT DISTINCT
2293            CAST(current_database() AS sql_identifier) AS table_catalog,
2294            CAST(nv.nspname AS sql_identifier) AS table_schema,
2295            CAST(v.relname AS sql_identifier) AS table_name,
2296            CAST(current_database() AS sql_identifier) AS specific_catalog,
2297            CAST(np.nspname AS sql_identifier) AS specific_schema,
2298            CAST(p.proname || '_' || CAST(p.oid AS text)  AS sql_identifier) AS specific_name
2299
2300     FROM pg_namespace nv, pg_class v, pg_depend dv,
2301          pg_depend dp, pg_proc p, pg_namespace np
2302
2303     WHERE nv.oid = v.relnamespace
2304           AND v.relkind = 'v'
2305           AND v.oid = dv.refobjid
2306           AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2307           AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2308           AND dv.deptype = 'i'
2309           AND dv.objid = dp.objid
2310           AND dp.classid = 'pg_catalog.pg_rewrite'::regclass
2311           AND dp.refclassid = 'pg_catalog.pg_proc'::regclass
2312           AND dp.refobjid = p.oid
2313           AND p.pronamespace = np.oid
2314           AND pg_has_role(p.proowner, 'USAGE');
2315
2316 GRANT SELECT ON view_routine_usage TO PUBLIC;
2317
2318
2319 /*
2320  * 5.75
2321  * VIEW_TABLE_USAGE
2322  */
2323
2324 CREATE VIEW view_table_usage AS
2325     SELECT DISTINCT
2326            CAST(current_database() AS sql_identifier) AS view_catalog,
2327            CAST(nv.nspname AS sql_identifier) AS view_schema,
2328            CAST(v.relname AS sql_identifier) AS view_name,
2329            CAST(current_database() AS sql_identifier) AS table_catalog,
2330            CAST(nt.nspname AS sql_identifier) AS table_schema,
2331            CAST(t.relname AS sql_identifier) AS table_name
2332
2333     FROM pg_namespace nv, pg_class v, pg_depend dv,
2334          pg_depend dt, pg_class t, pg_namespace nt
2335
2336     WHERE nv.oid = v.relnamespace
2337           AND v.relkind = 'v'
2338           AND v.oid = dv.refobjid
2339           AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2340           AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2341           AND dv.deptype = 'i'
2342           AND dv.objid = dt.objid
2343           AND dv.refobjid <> dt.refobjid
2344           AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2345           AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2346           AND dt.refobjid = t.oid
2347           AND t.relnamespace = nt.oid
2348           AND t.relkind IN ('r', 'v', 'f')
2349           AND pg_has_role(t.relowner, 'USAGE');
2350
2351 GRANT SELECT ON view_table_usage TO PUBLIC;
2352
2353
2354 /*
2355  * 5.76
2356  * VIEWS view
2357  */
2358
2359 CREATE VIEW views AS
2360     SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
2361            CAST(nc.nspname AS sql_identifier) AS table_schema,
2362            CAST(c.relname AS sql_identifier) AS table_name,
2363
2364            CAST(
2365              CASE WHEN pg_has_role(c.relowner, 'USAGE')
2366                   THEN pg_get_viewdef(c.oid)
2367                   ELSE null END
2368              AS character_data) AS view_definition,
2369
2370            CAST('NONE' AS character_data) AS check_option,
2371
2372            CAST(
2373              CASE WHEN EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '2' AND is_instead)
2374                    AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '4' AND is_instead)
2375                   THEN 'YES' ELSE 'NO' END
2376              AS yes_or_no) AS is_updatable,
2377
2378            CAST(
2379              CASE WHEN EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '3' AND is_instead)
2380                   THEN 'YES' ELSE 'NO' END
2381              AS yes_or_no) AS is_insertable_into,
2382
2383            CAST(
2384              -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_UPDATE
2385              CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 81 = 81)
2386                   THEN 'YES' ELSE 'NO' END
2387            AS yes_or_no) AS is_trigger_updatable,
2388
2389            CAST(
2390              -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_DELETE
2391              CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 73 = 73)
2392                   THEN 'YES' ELSE 'NO' END
2393            AS yes_or_no) AS is_trigger_deletable,
2394
2395            CAST(
2396              -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_INSERT
2397              CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 69 = 69)
2398                   THEN 'YES' ELSE 'NO' END
2399            AS yes_or_no) AS is_trigger_insertable_into
2400
2401     FROM pg_namespace nc, pg_class c
2402
2403     WHERE c.relnamespace = nc.oid
2404           AND c.relkind = 'v'
2405           AND (NOT pg_is_other_temp_schema(nc.oid))
2406           AND (pg_has_role(c.relowner, 'USAGE')
2407                OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
2408                OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES') );
2409
2410 GRANT SELECT ON views TO PUBLIC;
2411
2412
2413 -- The following views have dependencies that force them to appear out of order.
2414
2415 /*
2416  * 5.24
2417  * DATA_TYPE_PRIVILEGES view
2418  */
2419
2420 CREATE VIEW data_type_privileges AS
2421     SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2422            CAST(x.objschema AS sql_identifier) AS object_schema,
2423            CAST(x.objname AS sql_identifier) AS object_name,
2424            CAST(x.objtype AS character_data) AS object_type,
2425            CAST(x.objdtdid AS sql_identifier) AS dtd_identifier
2426
2427     FROM
2428       (
2429         SELECT udt_schema, udt_name, 'USER-DEFINED TYPE'::text, dtd_identifier FROM attributes
2430         UNION ALL
2431         SELECT table_schema, table_name, 'TABLE'::text, dtd_identifier FROM columns
2432         UNION ALL
2433         SELECT domain_schema, domain_name, 'DOMAIN'::text, dtd_identifier FROM domains
2434         UNION ALL
2435         SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM parameters
2436         UNION ALL
2437         SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM routines
2438       ) AS x (objschema, objname, objtype, objdtdid);
2439
2440 GRANT SELECT ON data_type_privileges TO PUBLIC;
2441
2442
2443 /*
2444  * 5.29
2445  * ELEMENT_TYPES view
2446  */
2447
2448 CREATE VIEW element_types AS
2449     SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2450            CAST(n.nspname AS sql_identifier) AS object_schema,
2451            CAST(x.objname AS sql_identifier) AS object_name,
2452            CAST(x.objtype AS character_data) AS object_type,
2453            CAST(x.objdtdid AS sql_identifier) AS collection_type_identifier,
2454            CAST(
2455              CASE WHEN nbt.nspname = 'pg_catalog' THEN format_type(bt.oid, null)
2456                   ELSE 'USER-DEFINED' END AS character_data) AS data_type,
2457
2458            CAST(null AS cardinal_number) AS character_maximum_length,
2459            CAST(null AS cardinal_number) AS character_octet_length,
2460            CAST(null AS sql_identifier) AS character_set_catalog,
2461            CAST(null AS sql_identifier) AS character_set_schema,
2462            CAST(null AS sql_identifier) AS character_set_name,
2463            CAST(CASE WHEN nco.nspname IS NOT NULL THEN current_database() END AS sql_identifier) AS collation_catalog,
2464            CAST(nco.nspname AS sql_identifier) AS collation_schema,
2465            CAST(co.collname AS sql_identifier) AS collation_name,
2466            CAST(null AS cardinal_number) AS numeric_precision,
2467            CAST(null AS cardinal_number) AS numeric_precision_radix,
2468            CAST(null AS cardinal_number) AS numeric_scale,
2469            CAST(null AS cardinal_number) AS datetime_precision,
2470            CAST(null AS character_data) AS interval_type,
2471            CAST(null AS cardinal_number) AS interval_precision,
2472
2473            CAST(null AS character_data) AS domain_default, -- XXX maybe a bug in the standard
2474
2475            CAST(current_database() AS sql_identifier) AS udt_catalog,
2476            CAST(nbt.nspname AS sql_identifier) AS udt_schema,
2477            CAST(bt.typname AS sql_identifier) AS udt_name,
2478
2479            CAST(null AS sql_identifier) AS scope_catalog,
2480            CAST(null AS sql_identifier) AS scope_schema,
2481            CAST(null AS sql_identifier) AS scope_name,
2482
2483            CAST(null AS cardinal_number) AS maximum_cardinality,
2484            CAST('a' || CAST(x.objdtdid AS text) AS sql_identifier) AS dtd_identifier
2485
2486     FROM pg_namespace n, pg_type at, pg_namespace nbt, pg_type bt,
2487          (
2488            /* columns, attributes */
2489            SELECT c.relnamespace, CAST(c.relname AS sql_identifier),
2490                   CASE WHEN c.relkind = 'c' THEN 'USER-DEFINED TYPE'::text ELSE 'TABLE'::text END,
2491                   a.attnum, a.atttypid, a.attcollation
2492            FROM pg_class c, pg_attribute a
2493            WHERE c.oid = a.attrelid
2494                  AND c.relkind IN ('r', 'v', 'f', 'c')
2495                  AND attnum > 0 AND NOT attisdropped
2496
2497            UNION ALL
2498
2499            /* domains */
2500            SELECT t.typnamespace, CAST(t.typname AS sql_identifier),
2501                   'DOMAIN'::text, 1, t.typbasetype, t.typcollation
2502            FROM pg_type t
2503            WHERE t.typtype = 'd'
2504
2505            UNION ALL
2506
2507            /* parameters */
2508            SELECT pronamespace, CAST(proname || '_' || CAST(oid AS text) AS sql_identifier),
2509                   'ROUTINE'::text, (ss.x).n, (ss.x).x, 0
2510            FROM (SELECT p.pronamespace, p.proname, p.oid,
2511                         _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
2512                  FROM pg_proc p) AS ss
2513
2514            UNION ALL
2515
2516            /* result types */
2517            SELECT p.pronamespace, CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier),
2518                   'ROUTINE'::text, 0, p.prorettype, 0
2519            FROM pg_proc p
2520
2521          ) AS x (objschema, objname, objtype, objdtdid, objtypeid, objcollation)
2522          LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid))
2523            ON x.objcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default')
2524
2525     WHERE n.oid = x.objschema
2526           AND at.oid = x.objtypeid
2527           AND (at.typelem <> 0 AND at.typlen = -1)
2528           AND at.typelem = bt.oid
2529           AND nbt.oid = bt.typnamespace
2530
2531           AND (n.nspname, x.objname, x.objtype, CAST(x.objdtdid AS sql_identifier)) IN
2532               ( SELECT object_schema, object_name, object_type, dtd_identifier
2533                     FROM data_type_privileges );
2534
2535 GRANT SELECT ON element_types TO PUBLIC;
2536
2537
2538 -- SQL/MED views; these use section numbers from part 9 of the standard.
2539
2540 /* Base view for foreign-data wrappers */
2541 CREATE VIEW _pg_foreign_data_wrappers AS
2542     SELECT w.oid,
2543            w.fdwowner,
2544            w.fdwoptions,
2545            CAST(current_database() AS sql_identifier) AS foreign_data_wrapper_catalog,
2546            CAST(fdwname AS sql_identifier) AS foreign_data_wrapper_name,
2547            CAST(u.rolname AS sql_identifier) AS authorization_identifier,
2548            CAST('c' AS character_data) AS foreign_data_wrapper_language
2549     FROM pg_foreign_data_wrapper w, pg_authid u
2550     WHERE u.oid = w.fdwowner
2551           AND (pg_has_role(fdwowner, 'USAGE')
2552                OR has_foreign_data_wrapper_privilege(w.oid, 'USAGE'));
2553
2554
2555 /*
2556  * 24.4
2557  * FOREIGN_DATA_WRAPPER_OPTIONS view
2558  */
2559 CREATE VIEW foreign_data_wrapper_options AS
2560     SELECT foreign_data_wrapper_catalog,
2561            foreign_data_wrapper_name,
2562            CAST((pg_options_to_table(w.fdwoptions)).option_name AS sql_identifier) AS option_name,
2563            CAST((pg_options_to_table(w.fdwoptions)).option_value AS character_data) AS option_value
2564     FROM _pg_foreign_data_wrappers w;
2565
2566 GRANT SELECT ON foreign_data_wrapper_options TO PUBLIC;
2567
2568
2569 /*
2570  * 24.5
2571  * FOREIGN_DATA_WRAPPERS view
2572  */
2573 CREATE VIEW foreign_data_wrappers AS
2574     SELECT foreign_data_wrapper_catalog,
2575            foreign_data_wrapper_name,
2576            authorization_identifier,
2577            CAST(NULL AS character_data) AS library_name,
2578            foreign_data_wrapper_language
2579     FROM _pg_foreign_data_wrappers w;
2580
2581 GRANT SELECT ON foreign_data_wrappers TO PUBLIC;
2582
2583
2584 /* Base view for foreign servers */
2585 CREATE VIEW _pg_foreign_servers AS
2586     SELECT s.oid,
2587            s.srvoptions,
2588            CAST(current_database() AS sql_identifier) AS foreign_server_catalog,
2589            CAST(srvname AS sql_identifier) AS foreign_server_name,
2590            CAST(current_database() AS sql_identifier) AS foreign_data_wrapper_catalog,
2591            CAST(w.fdwname AS sql_identifier) AS foreign_data_wrapper_name,
2592            CAST(srvtype AS character_data) AS foreign_server_type,
2593            CAST(srvversion AS character_data) AS foreign_server_version,
2594            CAST(u.rolname AS sql_identifier) AS authorization_identifier
2595     FROM pg_foreign_server s, pg_foreign_data_wrapper w, pg_authid u
2596     WHERE w.oid = s.srvfdw
2597           AND u.oid = s.srvowner
2598           AND (pg_has_role(s.srvowner, 'USAGE')
2599                OR has_server_privilege(s.oid, 'USAGE'));
2600
2601
2602 /*
2603  * 24.6
2604  * FOREIGN_SERVER_OPTIONS view
2605  */
2606 CREATE VIEW foreign_server_options AS
2607     SELECT foreign_server_catalog,
2608            foreign_server_name,
2609            CAST((pg_options_to_table(s.srvoptions)).option_name AS sql_identifier) AS option_name,
2610            CAST((pg_options_to_table(s.srvoptions)).option_value AS character_data) AS option_value
2611     FROM _pg_foreign_servers s;
2612
2613 GRANT SELECT ON TABLE foreign_server_options TO PUBLIC;
2614
2615
2616 /*
2617  * 24.7
2618  * FOREIGN_SERVERS view
2619  */
2620 CREATE VIEW foreign_servers AS
2621     SELECT foreign_server_catalog,
2622            foreign_server_name,
2623            foreign_data_wrapper_catalog,
2624            foreign_data_wrapper_name,
2625            foreign_server_type,
2626            foreign_server_version,
2627            authorization_identifier
2628     FROM _pg_foreign_servers;
2629
2630 GRANT SELECT ON foreign_servers TO PUBLIC;
2631
2632
2633 /* Base view for foreign tables */
2634 CREATE VIEW _pg_foreign_tables AS
2635     SELECT
2636            CAST(current_database() AS sql_identifier) AS foreign_table_catalog,
2637            n.nspname AS foreign_table_schema,
2638            c.relname AS foreign_table_name,
2639            t.ftoptions AS ftoptions,
2640            CAST(current_database() AS sql_identifier) AS foreign_server_catalog,
2641            CAST(srvname AS sql_identifier) AS foreign_server_name,
2642            CAST(u.rolname AS sql_identifier) AS authorization_identifier
2643     FROM pg_foreign_table t, pg_foreign_server s, pg_foreign_data_wrapper w,
2644          pg_authid u, pg_namespace n, pg_class c
2645     WHERE w.oid = s.srvfdw
2646           AND u.oid = c.relowner
2647           AND (pg_has_role(c.relowner, 'USAGE')
2648                OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
2649                OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'))
2650           AND n.oid = c.relnamespace
2651           AND c.oid = t.ftrelid
2652           AND c.relkind = 'f'
2653           AND s.oid = t.ftserver;
2654
2655
2656 /*
2657  * 24.8
2658  * FOREIGN_TABLE_OPTIONS view
2659  */
2660 CREATE VIEW foreign_table_options AS
2661     SELECT foreign_table_catalog,
2662            foreign_table_schema,
2663            foreign_table_name,
2664            CAST((pg_options_to_table(t.ftoptions)).option_name AS sql_identifier) AS option_name,
2665            CAST((pg_options_to_table(t.ftoptions)).option_value AS character_data) AS option_value
2666     FROM _pg_foreign_tables t;
2667
2668 GRANT SELECT ON TABLE foreign_table_options TO PUBLIC;
2669
2670
2671 /*
2672  * 24.9
2673  * FOREIGN_TABLES view
2674  */
2675 CREATE VIEW foreign_tables AS
2676     SELECT foreign_table_catalog,
2677            foreign_table_schema,
2678            foreign_table_name,
2679            foreign_server_catalog,
2680            foreign_server_name
2681     FROM _pg_foreign_tables;
2682
2683 GRANT SELECT ON foreign_tables TO PUBLIC;
2684
2685
2686
2687 /* Base view for user mappings */
2688 CREATE VIEW _pg_user_mappings AS
2689     SELECT um.oid,
2690            um.umoptions,
2691            um.umuser,
2692            CAST(COALESCE(u.rolname,'PUBLIC') AS sql_identifier ) AS authorization_identifier,
2693            s.foreign_server_catalog,
2694            s.foreign_server_name,
2695            s.authorization_identifier AS srvowner
2696     FROM pg_user_mapping um LEFT JOIN pg_authid u ON (u.oid = um.umuser),
2697          _pg_foreign_servers s
2698     WHERE s.oid = um.umserver;
2699
2700
2701 /*
2702  * 24.12
2703  * USER_MAPPING_OPTIONS view
2704  */
2705 CREATE VIEW user_mapping_options AS
2706     SELECT authorization_identifier,
2707            foreign_server_catalog,
2708            foreign_server_name,
2709            CAST((pg_options_to_table(um.umoptions)).option_name AS sql_identifier) AS option_name,
2710            CAST(CASE WHEN (umuser <> 0 AND authorization_identifier = current_user)
2711                        OR (umuser = 0 AND pg_has_role(srvowner, 'USAGE'))
2712                        OR (SELECT rolsuper FROM pg_authid WHERE rolname = current_user) THEN (pg_options_to_table(um.umoptions)).option_value
2713                      ELSE NULL END AS character_data) AS option_value
2714     FROM _pg_user_mappings um;
2715
2716 GRANT SELECT ON user_mapping_options TO PUBLIC;
2717
2718
2719 /*
2720  * 24.13
2721  * USER_MAPPINGS view
2722  */
2723 CREATE VIEW user_mappings AS
2724     SELECT authorization_identifier,
2725            foreign_server_catalog,
2726            foreign_server_name
2727     FROM _pg_user_mappings;
2728
2729 GRANT SELECT ON user_mappings TO PUBLIC;