src/backend/catalog/information_schema.sql

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