src/backend/catalog/information_schema.sql

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