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

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