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

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