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

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