src/backend/catalog/information_schema.sql

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