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