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

   1 /*
   2  * SQL Information Schema
   3  * as defined in ISO 9075-2:1999 chapter 20
   4  *
   5  * Copyright 2002, PostgreSQL Global Development Group
   6  *
   7  * $Id: information_schema.sql,v 1.2 2003/01/14 23:19:34 petere Exp $
   8  */
   9
  10
  11 /*
  12  * 20.2
  13  * INFORMATION_SCHEMA schema
  14  */
  15
  16 CREATE SCHEMA information_schema;
  17 GRANT USAGE ON SCHEMA information_schema TO PUBLIC;
  18 SET search_path TO information_schema, public;
  19
  20
  21 -- Note: 20.3 follows later.  Some genius screwed up the order in the standard.
  22
  23
  24 /*
  25  * 20.4
  26  * CARDINAL_NUMBER domain
  27  */
  28
  29 CREATE DOMAIN cardinal_number AS integer
  30     CONSTRAINT cardinal_number_domain_check CHECK (value >= 0);
  31
  32
  33 /*
  34  * 20.5
  35  * CHARACTER_DATA domain
  36  */
  37
  38 CREATE DOMAIN character_data AS character varying;
  39
  40
  41 /*
  42  * 20.6
  43  * SQL_IDENTIFIER domain
  44  */
  45
  46 CREATE DOMAIN sql_identifier AS character varying;
  47
  48
  49 /*
  50  * 20.3
  51  * INFORMATION_SCHEMA_CATALOG_NAME view
  52  */
  53
  54 CREATE VIEW information_schema_catalog_name AS
  55     SELECT CAST(current_database() AS sql_identifier) AS catalog_name;
  56
  57 GRANT SELECT ON information_schema_catalog_name TO PUBLIC;
  58
  59
  60 /*
  61  * 20.7
  62  * TIME_STAMP domain
  63  */
  64
  65 CREATE DOMAIN time_stamp AS timestamp(2)
  66     DEFAULT current_timestamp(2);
  67
  68
  69 /*
  70  * 20.13
  71  * CHECK_CONSTRAINTS view
  72  */
  73
  74 CREATE VIEW check_constraints AS
  75     SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
  76            CAST(rs.nspname AS sql_identifier) AS constraint_schema,
  77            CAST(con.conname AS sql_identifier) AS constraint_name,
  78            CAST(con.consrc AS character_data) AS check_clause
  79     FROM pg_namespace rs, pg_class c, pg_constraint con, pg_user u
  80     WHERE rs.oid = c.relnamespace AND c.oid = con.conrelid
  81           AND c.relowner = u.usesysid AND u.usename = current_user
  82           AND con.contype = 'c';
  83
  84 GRANT SELECT ON check_constraints TO PUBLIC;
  85
  86
  87 /*
  88  * 20.15
  89  * COLUMN_DOMAIN_USAGE view
  90  */
  91
  92 CREATE VIEW column_domain_usage AS
  93     SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
  94            CAST(nt.nspname AS sql_identifier) AS domain_schema,
  95            CAST(t.typname AS sql_identifier) AS domain_name,
  96            CAST(current_database() AS sql_identifier) AS table_catalog,
  97            CAST(nc.nspname AS sql_identifier) AS table_schema,
  98            CAST(c.relname AS sql_identifier) AS table_name,
  99            CAST(a.attname AS sql_identifier) AS column_name
 100
 101     FROM pg_type t, pg_namespace nt, pg_class c, pg_namespace nc,
 102          pg_attribute a, pg_user u
 103
 104     WHERE t.typnamespace = nt.oid AND t.typtype = 'd'
 105           AND c.relnamespace = nc.oid AND a.attrelid = c.oid
 106           AND a.atttypid = t.oid AND t.typowner = u.usesysid
 107           AND u.usename = current_user;
 108
 109 GRANT SELECT ON column_domain_usage TO PUBLIC;
 110
 111
 112 /*
 113  * 20.16
 114  * COLUMN_PRIVILEGES
 115  */
 116
 117 -- PostgreSQL does not have column privileges, so this view is empty.
 118 -- (Table privileges do not also count as column privileges.)
 119
 120 CREATE VIEW column_privileges AS
 121     SELECT CAST(null AS sql_identifier) AS grantor,
 122            CAST(null AS sql_identifier) AS grantee,
 123            CAST(null AS sql_identifier) AS table_catalog,
 124            CAST(null AS sql_identifier) AS table_schema,
 125            CAST(null AS sql_identifier) AS table_name,
 126            CAST(null AS sql_identifier) AS column_name,
 127            CAST(null AS character_data) AS privilege_type,
 128            CAST(null AS character_data) AS is_grantable
 129     WHERE false;
 130
 131 GRANT SELECT ON column_privileges TO PUBLIC;
 132
 133
 134 /*
 135  * 20.18
 136  * COLUMNS view
 137  */
 138
 139 CREATE VIEW columns AS
 140     SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
 141            CAST(nc.nspname AS sql_identifier) AS table_schema,
 142            CAST(c.relname AS sql_identifier) AS table_name,
 143            CAST(a.attname AS sql_identifier) AS column_name,
 144            CAST(a.attnum AS cardinal_number) AS ordinal_position,
 145            CAST(
 146              CASE WHEN u.usename = current_user THEN a.adsrc ELSE null END
 147              AS character_data)
 148              AS column_default,
 149            CAST(CASE WHEN a.attnotnull THEN 'NO' ELSE 'YES' END
 150              AS character_data)
 151              AS is_nullable,
 152            CAST(format_type(a.atttypid, null) AS character_data)
 153              AS data_type,
 154
 155            CAST(
 156              CASE WHEN a.atttypid IN (25, 1042, 1043, 1560, 1562) AND a.atttypmod <> -1
 157                   THEN a.atttypmod - 4
 158                   ELSE null END
 159              AS cardinal_number)
 160              AS character_maximum_length,
 161
 162            CAST(
 163              CASE WHEN a.atttypid IN (25, 1042, 1043) THEN 2^30 ELSE null END
 164              AS cardinal_number)
 165              AS character_octet_length,
 166
 167            CAST(
 168              CASE WHEN a.atttypid IN (1700) THEN ((a.atttypmod - 4) >> 16) & 65535 ELSE null END
 169              AS cardinal_number)
 170              AS numeric_precision,
 171
 172            CAST(
 173              CASE WHEN a.atttypid IN (1700) THEN 10 ELSE null END
 174              AS cardinal_number)
 175              AS numeric_precision_radix,
 176
 177            CAST(
 178              CASE WHEN a.atttypid IN (1700) THEN (a.atttypmod - 4) & 65535 ELSE null END
 179              AS cardinal_number)
 180              AS numeric_scale,
 181
 182            CAST(
 183              CASE WHEN a.atttypid IN (1083, 1114, 1184, 1266)
 184                   THEN (CASE WHEN a.atttypmod <> -1 THEN a.atttypmod ELSE null END)
 185                   WHEN a.atttypid IN (1186)
 186                   THEN (CASE WHEN a.atttypmod <> -1 THEN a.atttypmod & 65535 ELSE null END)
 187                   ELSE null END
 188              AS cardinal_number)
 189              AS datetime_precision,
 190
 191            CAST(null AS character_data) AS interval_type, -- XXX
 192            CAST(null AS character_data) AS interval_precision, -- XXX
 193
 194            CAST(null AS sql_identifier) AS character_set_catalog,
 195            CAST(null AS sql_identifier) AS character_set_schema,
 196            CAST(null AS sql_identifier) AS character_set_name,
 197
 198            CAST(null AS sql_identifier) AS collation_catalog,
 199            CAST(null AS sql_identifier) AS collation_schema,
 200            CAST(null AS sql_identifier) AS collation_name,
 201
 202            CAST(CASE WHEN t.typbasetype <> 0 THEN current_database() ELSE null END
 203              AS sql_identifier) AS domain_catalog,
 204            CAST(CASE WHEN t.typbasetype <> 0 THEN nt.nspname ELSE null END
 205              AS sql_identifier) AS domain_schema,
 206            CAST(CASE WHEN t.typbasetype <> 0 THEN t.typname ELSE null END
 207              AS sql_identifier) AS domain_name,
 208
 209            CAST(CASE WHEN t.typbasetype = 0 THEN current_database() ELSE null END
 210              AS sql_identifier) AS udt_catalog,
 211            CAST(CASE WHEN t.typbasetype = 0 THEN nt.nspname ELSE null END
 212              AS sql_identifier) AS udt_schema,
 213            CAST(CASE WHEN t.typbasetype = 0 THEN t.typname ELSE null END
 214              AS sql_identifier) AS udt_name,
 215
 216            CAST(null AS sql_identifier) AS scope_catalog,
 217            CAST(null AS sql_identifier) AS scope_schema,
 218            CAST(null AS sql_identifier) AS scope_name,
 219
 220            CAST(null AS cardinal_number) AS maximum_cardinality,
 221            CAST(null AS sql_identifier) AS dtd_identifier,
 222            CAST('NO' AS character_data) AS is_self_referencing
 223
 224            FROM (pg_attribute LEFT JOIN pg_attrdef ON attrelid = adrelid AND attnum = adnum) AS a,
 225                 pg_class c, pg_namespace nc, pg_type t, pg_namespace nt, pg_user u
 226
 227            WHERE a.attrelid = c.oid
 228                  AND a.atttypid = t.oid
 229                  AND u.usesysid = c.relowner
 230                  AND nc.oid = c.relnamespace
 231                  AND nt.oid = t.typnamespace
 232                  AND u.usename = current_user
 233
 234                  AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v');
 235
 236 GRANT SELECT ON columns TO PUBLIC;
 237
 238
 239 /*
 240  * 20.35
 241  * REFERENTIAL_CONSTRAINTS view
 242  */
 243
 244 CREATE VIEW referential_constraints AS
 245     SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
 246            CAST(ncon.nspname AS sql_identifier) AS constraint_schema,
 247            CAST(con.conname AS sql_identifier) AS constraint_name,
 248            CAST(current_database() AS sql_identifier) AS unique_constraint_catalog,
 249            CAST(null AS sql_identifier) AS unique_constraint_schema, -- XXX
 250            CAST(null AS sql_identifier) AS unique_constraint_name, -- XXX
 251
 252            CAST(
 253              CASE con.confmatchtype WHEN 'f' THEN 'FULL'
 254                                     WHEN 'p' THEN 'PARTIAL'
 255                                     WHEN 'u' THEN 'NONE' END
 256              AS character_data) AS match_option,
 257
 258            CAST(
 259              CASE con.confupdtype WHEN 'c' THEN 'CASCADE'
 260                                   WHEN 'n' THEN 'SET NULL'
 261                                   WHEN 'd' THEN 'SET DEFAULT'
 262                                   WHEN 'r' THEN 'RESTRICT'
 263                                   WHEN 'a' THEN 'NOACTION' END
 264              AS character_data) AS update_rule,
 265
 266            CAST(
 267              CASE con.confdeltype WHEN 'c' THEN 'CASCADE'
 268                                   WHEN 'n' THEN 'SET NULL'
 269                                   WHEN 'd' THEN 'SET DEFAULT'
 270                                   WHEN 'r' THEN 'RESTRICT'
 271                                   WHEN 'a' THEN 'NOACTION' END
 272              AS character_data) AS delete_rule
 273
 274     FROM pg_namespace ncon,
 275          pg_constraint con,
 276          pg_class r,
 277          pg_user u
 278
 279     WHERE ncon.oid = con.connamespace
 280           AND con.conrelid = r.oid AND r.relowner = u.usesysid
 281           AND u.usename = current_user;
 282
 283 GRANT SELECT ON referential_constraints TO PUBLIC;
 284
 285
 286 /*
 287  * 20.46
 288  * SCHEMATA view
 289  */
 290
 291 CREATE VIEW schemata AS
 292     SELECT CAST(current_database() AS sql_identifier) AS catalog_name,
 293            CAST(n.nspname AS sql_identifier) AS schema_name,
 294            CAST(u.usename AS sql_identifier) AS schema_owner,
 295            CAST(null AS sql_identifier) AS default_character_set_catalog,
 296            CAST(null AS sql_identifier) AS default_character_set_schema,
 297            CAST(null AS sql_identifier) AS default_character_set_name,
 298            CAST(null AS character_data) AS sql_path
 299     FROM pg_namespace n, pg_user u
 300     WHERE n.nspowner = u.usesysid AND u.usename = current_user;
 301
 302 GRANT SELECT ON schemata TO PUBLIC;
 303
 304
 305 /*
 306  * 20.47
 307  * SQL_FEATURES table
 308  */
 309
 310 CREATE TABLE sql_features (
 311     feature_id          character_data,
 312     feature_name        character_data,
 313     sub_feature_id      character_data,
 314     sub_feature_name    character_data,
 315     is_supported        character_data,
 316     is_verified_by      character_data,
 317     comments            character_data
 318 ) WITHOUT OIDS;
 319
 320 -- Will be filled with external data by initdb.
 321
 322 GRANT SELECT ON sql_features TO PUBLIC;
 323
 324
 325 /*
 326  * 20.49
 327  * SQL_LANGUAGES table
 328  */
 329
 330 CREATE TABLE sql_languages (
 331     sql_language_source         character_data,
 332     sql_language_year           character_data,
 333     sql_language_conformance    character_data,
 334     sql_language_integrity      character_data,
 335     sql_language_implementation character_data,
 336     sql_language_binding_style  character_data,
 337     sql_language_programming_language character_data
 338 ) WITHOUT OIDS;
 339
 340 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'DIRECT', NULL);
 341 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
 342
 343 GRANT SELECT ON sql_languages TO PUBLIC;
 344
 345
 346 /*
 347  * 20.50
 348  * SQL_PACKAGES table
 349  */
 350
 351 CREATE TABLE sql_packages (
 352     feature_id      character_data,
 353     feature_name    character_data,
 354     is_supported    character_data,
 355     is_verified_by  character_data,
 356     comments        character_data
 357 ) WITHOUT OIDS;
 358
 359 INSERT INTO sql_packages VALUES ('PKG000', 'Core', 'NO', NULL, '');
 360 INSERT INTO sql_packages VALUES ('PKG001', 'Enhanced datetime facilities', 'YES', NULL, '');
 361 INSERT INTO sql_packages VALUES ('PKG002', 'Enhanced integrity management', 'NO', NULL, '');
 362 INSERT INTO sql_packages VALUES ('PKG003', 'OLAP facilities', 'NO', NULL, '');
 363 INSERT INTO sql_packages VALUES ('PKG004', 'PSM', 'NO', NULL, 'PL/pgSQL is similar.');
 364 INSERT INTO sql_packages VALUES ('PKG005', 'CLI', 'NO', NULL, 'ODBC is similar.');
 365 INSERT INTO sql_packages VALUES ('PKG006', 'Basic object support', 'NO', NULL, '');
 366 INSERT INTO sql_packages VALUES ('PKG007', 'Enhanced object support', 'NO', NULL, '');
 367 INSERT INTO sql_packages VALUES ('PKG008', 'Active database', 'NO', NULL, '');
 368 INSERT INTO sql_packages VALUES ('PKG009', 'SQL/MM support', 'NO', NULL, '');
 369
 370 GRANT SELECT ON sql_packages TO PUBLIC;
 371
 372
 373 /*
 374  * 20.53
 375  * TABLE_CONSTRAINTS view
 376  */
 377
 378 CREATE VIEW table_constraints AS
 379     SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
 380            CAST(nc.nspname AS sql_identifier) AS constraint_schema,
 381            CAST(c.conname AS sql_identifier) AS constraint_name,
 382            CAST(current_database() AS sql_identifier) AS table_catalog,
 383            CAST(nr.nspname AS sql_identifier) AS table_schema,
 384            CAST(r.relname AS sql_identifier) AS table_name,
 385            CAST(
 386              CASE c.contype WHEN 'c' THEN 'CHECK'
 387                             WHEN 'f' THEN 'FOREIGN KEY'
 388                             WHEN 'p' THEN 'PRIMARY KEY'
 389                             WHEN 'u' THEN 'UNIQUE' END
 390              AS character_data) AS constraint_type,
 391            CAST(CASE WHEN c.condeferrable THEN 'YES' ELSE 'NO' END AS character_data)
 392              AS is_deferrable,
 393            CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS character_data)
 394              AS initially_deferred
 395
 396     FROM pg_namespace nc,
 397          pg_namespace nr,
 398          pg_constraint c,
 399          pg_class r,
 400          pg_user u
 401
 402     WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace
 403           AND c.conrelid = r.oid AND r.relowner = u.usesysid
 404           AND u.usename = current_user;
 405
 406 -- FIMXE: Not-null constraints are missing here.
 407
 408 GRANT SELECT ON table_constraints TO PUBLIC;
 409
 410
 411 /*
 412  * 20.55
 413  * TABLE_PRIVILEGES view
 414  */
 415
 416 CREATE VIEW table_privileges AS
 417     SELECT CAST(u_owner.usename AS sql_identifier) AS grantor,
 418            CAST(u_grantee.usename AS sql_identifier) AS grantee,
 419            CAST(current_database() AS sql_identifier) AS table_catalog,
 420            CAST(nc.nspname AS sql_identifier) AS table_schema,
 421            CAST(c.relname AS sql_identifier) AS table_name,
 422            CAST(pr.type AS character_data) AS privilege_type,
 423            CAST('NO' AS character_data) AS is_grantable,
 424            CAST('NO' AS character_data) AS with_hierarchy
 425
 426     FROM pg_user u_owner,
 427          pg_user u_grantee,
 428          pg_namespace nc,
 429          pg_class c,
 430          (SELECT 'SELECT' UNION SELECT 'DELETE' UNION SELECT 'INSERT' UNION SELECT 'UPDATE'
 431           UNION SELECT 'REFERENCES' UNION SELECT 'TRIGGER') AS pr (type)
 432
 433     WHERE u_owner.usesysid = c.relowner
 434           AND c.relnamespace = nc.oid
 435           AND has_table_privilege(u_grantee.usename, c.oid, pr.type)
 436
 437           AND (u_owner.usename = current_user OR u_grantee.usename = current_user);
 438
 439 GRANT SELECT ON table_privileges TO PUBLIC;
 440
 441
 442 /*
 443  * 20.56
 444  * TABLES view
 445  */
 446
 447 CREATE VIEW tables AS
 448     SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
 449            CAST(nc.nspname AS sql_identifier) AS table_schema,
 450            CAST(c.relname AS sql_identifier) AS table_name,
 451
 452            CAST(
 453              CASE WHEN nc.nspname LIKE 'pg!_temp!_%' ESCAPE '!' THEN 'LOCAL TEMPORARY'
 454                   WHEN c.relkind = 'r' THEN 'BASE TABLE'
 455                   WHEN c.relkind = 'v' THEN 'VIEW'
 456                   ELSE null END
 457              AS character_data) AS table_type,
 458
 459            CAST(null AS sql_identifier) AS self_referencing_column_name,
 460            CAST(null AS character_data) AS reference_generation,
 461
 462            CAST(null AS sql_identifier) AS user_defined_type_catalog,
 463            CAST(null AS sql_identifier) AS user_defined_type_schema,
 464            CAST(null AS sql_identifier) AS user_defined_name
 465
 466     FROM pg_namespace nc, pg_class c, pg_user u
 467
 468     WHERE c.relnamespace = nc.oid AND u.usesysid = c.relowner
 469           AND (u.usename = current_user
 470                OR EXISTS(SELECT 1 FROM information_schema.table_privileges tp
 471                                   WHERE tp.table_schema = nc.nspname
 472                                         AND tp.table_name = c.relname
 473                                         AND tp.grantee = current_user))
 474
 475           AND c.relkind IN ('r', 'v');
 476
 477 GRANT SELECT ON tables TO PUBLIC;
 478
 479
 480 /*
 481  * 20.63
 482  * USAGE_PRIVILEGES view
 483  */
 484
 485 -- Of the things currently implemented in PostgreSQL, usage privileges
 486 -- apply only to domains.  Since domains have no real privileges, we
 487 -- represent all domains with implicit usage privilege here.
 488
 489 CREATE VIEW usage_privileges AS
 490     SELECT CAST(u.usename AS sql_identifier) AS grantor,
 491            CAST('PUBLIC' AS sql_identifier) AS grantee,
 492            CAST(current_database() AS sql_identifier) AS object_catalog,
 493            CAST(n.nspname AS sql_identifier) AS object_schema,
 494            CAST(t.typname AS sql_identifier) AS object_name,
 495            CAST('DOMAIN' AS character_data) AS object_type,
 496            CAST('USAGE' AS character_data) AS privilege_type,
 497            CAST('NO' AS character_data) AS is_grantable
 498
 499     FROM pg_user u,
 500          pg_namespace n,
 501          pg_type t
 502
 503     WHERE u.usesysid = t.typowner
 504           AND t.typnamespace = n.oid
 505           AND t.typtype = 'd';
 506
 507 GRANT SELECT ON usage_privileges TO PUBLIC;
 508
 509
 510 /*
 511  * 20.68
 512  * VIEWS view
 513  */
 514
 515 CREATE VIEW views AS
 516     SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
 517            CAST(nc.nspname AS sql_identifier) AS table_schema,
 518            CAST(c.relname AS sql_identifier) AS table_name,
 519
 520            CAST(
 521              CASE WHEN u.usename = current_user THEN pg_get_viewdef(c.oid)
 522                   ELSE null END
 523              AS character_data) AS view_definition,
 524
 525            CAST('NONE' AS character_data) AS check_option,
 526            CAST(null AS character_data) AS is_updatable, -- FIXME
 527            CAST(null AS character_data) AS is_insertable_into  -- FIXME
 528
 529     FROM pg_namespace nc, pg_class c, pg_user u
 530
 531     WHERE c.relnamespace = nc.oid AND u.usesysid = c.relowner
 532           AND (u.usename = current_user
 533                OR EXISTS(SELECT 1 FROM information_schema.table_privileges tp
 534                                   WHERE tp.table_schema = nc.nspname
 535                                         AND tp.table_name = c.relname
 536                                         AND tp.grantee = current_user))
 537
 538           AND c.relkind = 'v';
 539
 540 GRANT SELECT ON views TO PUBLIC;