information_schema.sql 64 KB
Newer Older
1 2 3 4
/*
 * SQL Information Schema
 * as defined in ISO 9075-2:1999 chapter 20
 *
5
 * Copyright 2003, PostgreSQL Global Development Group
6
 *
7
 * $Id: information_schema.sql,v 1.15 2003/10/18 19:06:10 tgl Exp $
8 9 10 11 12 13 14 15 16
 */

/*
 * Note: Generally, the definitions in this file should be ordered
 * according to the clause numbers in the SQL standard, which is also the
 * alphabetical order.  In some cases it is convenient or necessary to
 * define one information schema view by using another one; in that case,
 * put the referencing view at the very end and leave a note where it
 * should have been put.
17 18 19 20 21 22 23 24 25 26 27 28 29
 */


/*
 * 20.2
 * INFORMATION_SCHEMA schema
 */

CREATE SCHEMA information_schema;
GRANT USAGE ON SCHEMA information_schema TO PUBLIC;
SET search_path TO information_schema, public;


30
-- 20.3 INFORMATION_SCHEMA_CATALOG_NAME view appears later.
31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77


/*
 * 20.4
 * CARDINAL_NUMBER domain
 */

CREATE DOMAIN cardinal_number AS integer
    CONSTRAINT cardinal_number_domain_check CHECK (value >= 0);


/*
 * 20.5
 * CHARACTER_DATA domain
 */

CREATE DOMAIN character_data AS character varying;


/*
 * 20.6
 * SQL_IDENTIFIER domain
 */

CREATE DOMAIN sql_identifier AS character varying;


/*
 * 20.3
 * INFORMATION_SCHEMA_CATALOG_NAME view
 */

CREATE VIEW information_schema_catalog_name AS
    SELECT CAST(current_database() AS sql_identifier) AS catalog_name;

GRANT SELECT ON information_schema_catalog_name TO PUBLIC;


/*
 * 20.7
 * TIME_STAMP domain
 */

CREATE DOMAIN time_stamp AS timestamp(2)
    DEFAULT current_timestamp(2);


78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95
/*
 * 20.9
 * APPLICABLE_ROLES view
 */

CREATE VIEW applicable_roles AS
    SELECT CAST(current_user AS sql_identifier) AS grantee,
           CAST(g.groname AS sql_identifier) AS role_name,
           CAST('NO' AS character_data) AS is_grantable

    FROM pg_group g, pg_user u

    WHERE u.usesysid = ANY (g.grolist)
          AND u.usename = current_user;

GRANT SELECT ON applicable_roles TO PUBLIC;


96 97 98 99 100 101 102 103 104
/*
 * 20.13
 * CHECK_CONSTRAINTS view
 */

CREATE VIEW check_constraints AS
    SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
           CAST(rs.nspname AS sql_identifier) AS constraint_schema,
           CAST(con.conname AS sql_identifier) AS constraint_name,
Peter Eisentraut's avatar
Peter Eisentraut committed
105 106
           CAST(substring(pg_get_constraintdef(con.oid) from 7) AS character_data)
             AS check_clause
107 108
    FROM pg_namespace rs,
         pg_constraint con
109 110
           LEFT OUTER JOIN pg_class c ON (c.oid = con.conrelid)
           LEFT OUTER JOIN pg_type t ON (t.oid = con.contypid),
Bruce Momjian's avatar
Bruce Momjian committed
111 112
         pg_user u
    WHERE rs.oid = con.connamespace
113
          AND u.usesysid = coalesce(c.relowner, t.typowner)
Bruce Momjian's avatar
Bruce Momjian committed
114
          AND u.usename = current_user
115 116
          AND con.contype = 'c'
          AND c.relkind = 'r';
117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137

GRANT SELECT ON check_constraints TO PUBLIC;


/*
 * 20.15
 * COLUMN_DOMAIN_USAGE view
 */

CREATE VIEW column_domain_usage AS
    SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
           CAST(nt.nspname AS sql_identifier) AS domain_schema,
           CAST(t.typname AS sql_identifier) AS domain_name,
           CAST(current_database() AS sql_identifier) AS table_catalog,
           CAST(nc.nspname AS sql_identifier) AS table_schema,
           CAST(c.relname AS sql_identifier) AS table_name,
           CAST(a.attname AS sql_identifier) AS column_name

    FROM pg_type t, pg_namespace nt, pg_class c, pg_namespace nc,
         pg_attribute a, pg_user u

138 139 140 141 142 143 144 145 146
    WHERE t.typnamespace = nt.oid
          AND c.relnamespace = nc.oid
          AND a.attrelid = c.oid
          AND a.atttypid = t.oid
          AND t.typowner = u.usesysid
          AND t.typtype = 'd'
          AND c.relkind IN ('r', 'v')
          AND a.attnum > 0
          AND NOT a.attisdropped
147 148 149 150 151 152 153 154 155 156 157
          AND u.usename = current_user;

GRANT SELECT ON column_domain_usage TO PUBLIC;


/*
 * 20.16
 * COLUMN_PRIVILEGES
 */

CREATE VIEW column_privileges AS
158
    SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
159
           CAST(grantee.name AS sql_identifier) AS grantee,
160 161 162 163 164 165 166
           CAST(current_database() AS sql_identifier) AS table_catalog,
           CAST(nc.nspname AS sql_identifier) AS table_schema,
           CAST(c.relname AS sql_identifier) AS table_name,
           CAST(a.attname AS sql_identifier) AS column_name,
           CAST(pr.type AS character_data) AS privilege_type,
           CAST(
             CASE WHEN aclcontains(c.relacl,
167
                                   makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr.type, true))
168 169 170 171 172 173
                  THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable

    FROM pg_attribute a,
         pg_class c,
         pg_namespace nc,
         pg_user u_grantor,
174 175
         (
           SELECT usesysid, 0, usename FROM pg_user
Peter Eisentraut's avatar
Peter Eisentraut committed
176
           UNION ALL
177
           SELECT 0, grosysid, groname FROM pg_group
Peter Eisentraut's avatar
Peter Eisentraut committed
178
           UNION ALL
179 180
           SELECT 0, 0, 'PUBLIC'
         ) AS grantee (usesysid, grosysid, name),
181 182 183 184
         (SELECT 'SELECT' UNION ALL
          SELECT 'INSERT' UNION ALL
          SELECT 'UPDATE' UNION ALL
          SELECT 'REFERENCES') AS pr (type)
185 186 187 188 189 190 191

    WHERE a.attrelid = c.oid
          AND c.relnamespace = nc.oid
          AND a.attnum > 0
          AND NOT a.attisdropped
          AND c.relkind IN ('r', 'v')
          AND aclcontains(c.relacl,
192
                          makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr.type, false))
193
          AND (u_grantor.usename = current_user
194 195
               OR grantee.name = current_user
               OR grantee.name = 'PUBLIC');
196 197 198 199

GRANT SELECT ON column_privileges TO PUBLIC;


200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228
/*
 * 20.17
 * COLUMN_UDT_USAGE view
 */

CREATE VIEW column_udt_usage AS
    SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
           CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,
           CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,
           CAST(current_database() AS sql_identifier) AS table_catalog,
           CAST(nc.nspname AS sql_identifier) AS table_schema,
           CAST(c.relname AS sql_identifier) AS table_name,
           CAST(a.attname AS sql_identifier) AS column_name

    FROM pg_attribute a, pg_class c, pg_namespace nc, pg_user u,
         (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
           LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
           ON (t.typtype = 'd' AND t.typbasetype = bt.oid)

    WHERE a.attrelid = c.oid
          AND a.atttypid = t.oid
          AND u.usesysid = coalesce(bt.typowner, t.typowner)
          AND nc.oid = c.relnamespace
          AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
          AND u.usename = current_user;

GRANT SELECT ON column_udt_usage TO PUBLIC;


229 230 231 232 233 234 235 236 237 238 239 240 241 242 243
/*
 * 20.18
 * COLUMNS view
 */

CREATE VIEW columns AS
    SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
           CAST(nc.nspname AS sql_identifier) AS table_schema,
           CAST(c.relname AS sql_identifier) AS table_name,
           CAST(a.attname AS sql_identifier) AS column_name,
           CAST(a.attnum AS cardinal_number) AS ordinal_position,
           CAST(
             CASE WHEN u.usename = current_user THEN a.adsrc ELSE null END
             AS character_data)
             AS column_default,
244
           CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
245 246
             AS character_data)
             AS is_nullable,
247 248 249

           CAST(
             CASE WHEN t.typtype = 'd' THEN
250 251
               CASE WHEN bt.typelem <> 0 AND bt.typlen = -1 THEN 'ARRAY'
                    WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
252 253
                    ELSE 'USER-DEFINED' END
             ELSE
254 255
               CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
                    WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, null)
256 257 258
                    ELSE 'USER-DEFINED' END
             END
             AS character_data)
259 260 261
             AS data_type,

           CAST(
262 263 264 265 266 267 268 269 270
             CASE WHEN t.typtype = 'd' THEN
               CASE WHEN t.typbasetype IN (25, 1042, 1043, 1560, 1562) AND t.typtypmod <> -1
                    THEN t.typtypmod - 4
                    ELSE null END
             ELSE
               CASE WHEN a.atttypid IN (25, 1042, 1043, 1560, 1562) AND a.atttypmod <> -1
                    THEN a.atttypmod - 4
                    ELSE null END
             END
271 272 273 274
             AS cardinal_number)
             AS character_maximum_length,

           CAST(
275 276 277 278 279
             CASE WHEN t.typtype = 'd' THEN
               CASE WHEN t.typbasetype IN (25, 1042, 1043) THEN 2^30 ELSE null END
             ELSE
               CASE WHEN a.atttypid IN (25, 1042, 1043) THEN 2^30 ELSE null END
             END
280 281 282 283
             AS cardinal_number)
             AS character_octet_length,

           CAST(
284
             CASE (CASE WHEN t.typtype = 'd' THEN t.typbasetype ELSE a.atttypid END)
285 286 287
               WHEN 21 /*int2*/ THEN 16
               WHEN 23 /*int4*/ THEN 32
               WHEN 20 /*int8*/ THEN 64
288
               WHEN 1700 /*numeric*/ THEN ((CASE WHEN t.typtype = 'd' THEN t.typtypmod ELSE a.atttypmod END - 4) >> 16) & 65535
289 290 291
               WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/
               WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/
               ELSE null END
292 293 294 295
             AS cardinal_number)
             AS numeric_precision,

           CAST(
296 297 298 299 300 301 302 303 304
             CASE WHEN t.typtype = 'd' THEN
               CASE WHEN t.typbasetype IN (21, 23, 20, 700, 701) THEN 2
                    WHEN t.typbasetype IN (1700) THEN 10
                    ELSE null END
             ELSE
               CASE WHEN a.atttypid IN (21, 23, 20, 700, 701) THEN 2
                    WHEN a.atttypid IN (1700) THEN 10
                    ELSE null END
             END
305 306 307 308
             AS cardinal_number)
             AS numeric_precision_radix,

           CAST(
309 310 311 312 313 314 315 316 317
             CASE WHEN t.typtype = 'd' THEN
               CASE WHEN t.typbasetype IN (21, 23, 20) THEN 0
                    WHEN t.typbasetype IN (1700) THEN (t.typtypmod - 4) & 65535
                    ELSE null END
             ELSE
               CASE WHEN a.atttypid IN (21, 23, 20) THEN 0
                    WHEN a.atttypid IN (1700) THEN (a.atttypmod - 4) & 65535
                    ELSE null END
             END
318 319 320 321
             AS cardinal_number)
             AS numeric_scale,

           CAST(
322 323 324 325 326 327 328 329 330 331 332 333 334
             CASE WHEN t.typtype = 'd' THEN
               CASE WHEN t.typbasetype IN (1083, 1114, 1184, 1266)
                    THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod ELSE null END)
                    WHEN t.typbasetype IN (1186)
                    THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod & 65535 ELSE null END)
                    ELSE null END
             ELSE
               CASE WHEN a.atttypid IN (1083, 1114, 1184, 1266)
                    THEN (CASE WHEN a.atttypmod <> -1 THEN a.atttypmod ELSE null END)
                    WHEN a.atttypid IN (1186)
                    THEN (CASE WHEN a.atttypmod <> -1 THEN a.atttypmod & 65535 ELSE null END)
                    ELSE null END
             END
335 336 337 338 339 340 341 342 343 344 345 346 347 348
             AS cardinal_number)
             AS datetime_precision,

           CAST(null AS character_data) AS interval_type, -- XXX
           CAST(null AS character_data) AS interval_precision, -- XXX

           CAST(null AS sql_identifier) AS character_set_catalog,
           CAST(null AS sql_identifier) AS character_set_schema,
           CAST(null AS sql_identifier) AS character_set_name,

           CAST(null AS sql_identifier) AS collation_catalog,
           CAST(null AS sql_identifier) AS collation_schema,
           CAST(null AS sql_identifier) AS collation_name,

349
           CAST(CASE WHEN t.typtype = 'd' THEN current_database() ELSE null END
350
             AS sql_identifier) AS domain_catalog,
351
           CAST(CASE WHEN t.typtype = 'd' THEN nt.nspname ELSE null END
352
             AS sql_identifier) AS domain_schema,
353
           CAST(CASE WHEN t.typtype = 'd' THEN t.typname ELSE null END
354 355
             AS sql_identifier) AS domain_name,

356 357 358
           CAST(current_database() AS sql_identifier) AS udt_catalog,
           CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,
           CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,
359 360 361 362 363 364

           CAST(null AS sql_identifier) AS scope_catalog,
           CAST(null AS sql_identifier) AS scope_schema,
           CAST(null AS sql_identifier) AS scope_name,

           CAST(null AS cardinal_number) AS maximum_cardinality,
365
           CAST(a.attnum AS sql_identifier) AS dtd_identifier,
366 367
           CAST('NO' AS character_data) AS is_self_referencing

368 369 370 371 372
    FROM (pg_attribute LEFT JOIN pg_attrdef ON attrelid = adrelid AND attnum = adnum) AS a,
         pg_class c, pg_namespace nc, pg_user u,
         (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
           LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
           ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
373

374 375 376 377
    WHERE a.attrelid = c.oid
          AND a.atttypid = t.oid
          AND u.usesysid = c.relowner
          AND nc.oid = c.relnamespace
378

379 380 381 382 383 384
          AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')

          AND (u.usename = current_user
               OR has_table_privilege(c.oid, 'SELECT')
               OR has_table_privilege(c.oid, 'INSERT')
               OR has_table_privilege(c.oid, 'UPDATE')
385
               OR has_table_privilege(c.oid, 'RERERENCES') );
386 387 388 389

GRANT SELECT ON columns TO PUBLIC;


390 391 392 393 394
/*
 * 20.19
 * CONSTRAINT_COLUMN_USAGE view
 */

395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410
/* This returns the integers from 1 to INDEX_MAX_KEYS/FUNC_MAX_ARGS */
CREATE FUNCTION _pg_keypositions() RETURNS SETOF integer
    LANGUAGE sql
    IMMUTABLE
    AS 'select 1 union all select 2 union all select 3 union all
        select 4 union all select 5 union all select 6 union all
        select 7 union all select 8 union all select 9 union all
        select 10 union all select 11 union all select 12 union all
        select 13 union all select 14 union all select 15 union all
        select 16 union all select 17 union all select 18 union all
        select 19 union all select 20 union all select 21 union all
        select 22 union all select 23 union all select 24 union all
        select 25 union all select 26 union all select 27 union all
        select 28 union all select 29 union all select 30 union all
        select 31 union all select 32';

411 412 413 414 415 416 417 418 419 420
CREATE VIEW constraint_column_usage AS
    SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
           CAST(tblschema AS sql_identifier) AS table_schema,
           CAST(tblname AS sql_identifier) AS table_name,
           CAST(colname AS sql_identifier) AS column_name,
           CAST(current_database() AS sql_identifier) AS constraint_catalog,
           CAST(cstrschema AS sql_identifier) AS constraint_schema,
           CAST(cstrname AS sql_identifier) AS constraint_name

    FROM (
421
        /* check constraints */
422 423 424 425
        SELECT DISTINCT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
          FROM pg_namespace nr, pg_class r, pg_attribute a, pg_depend d, pg_namespace nc, pg_constraint c
          WHERE nr.oid = r.relnamespace
            AND r.oid = a.attrelid
Peter Eisentraut's avatar
Peter Eisentraut committed
426
            AND d.refclassid = 'pg_catalog.pg_class'::regclass
427 428
            AND d.refobjid = r.oid
            AND d.refobjsubid = a.attnum
Peter Eisentraut's avatar
Peter Eisentraut committed
429
            AND d.classid = 'pg_catalog.pg_constraint'::regclass
430 431 432
            AND d.objid = c.oid
            AND c.connamespace = nc.oid
            AND c.contype = 'c'
433 434 435 436
            AND r.relkind = 'r'
            AND a.attnum > 0
            AND NOT a.attisdropped

Peter Eisentraut's avatar
Peter Eisentraut committed
437
        UNION ALL
438 439 440

        /* unique/primary key/foreign key constraints */
        SELECT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
441 442
          FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc,
               pg_constraint c, _pg_keypositions() AS pos(n)
443 444 445 446 447 448 449 450 451 452 453
          WHERE nr.oid = r.relnamespace
            AND r.oid = a.attrelid
            AND r.oid = c.conrelid
            AND nc.oid = c.connamespace
            AND (CASE WHEN c.contype = 'f' THEN c.confkey[pos.n] = a.attnum
                      ELSE c.conkey[pos.n] = a.attnum END)
            AND a.attnum > 0
            AND NOT a.attisdropped
            AND c.contype IN ('p', 'u', 'f')
            AND r.relkind = 'r'

454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481
      ) AS x (tblschema, tblname, tblowner, colname, cstrschema, cstrname),
      pg_user u

    WHERE x.tblowner = u.usesysid AND u.usename = current_user;

GRANT SELECT ON constraint_column_usage TO PUBLIC;


/*
 * 20.20
 * CONSTRAINT_TABLE_USAGE view
 */

CREATE VIEW constraint_table_usage AS
    SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
           CAST(nr.nspname AS sql_identifier) AS table_schema,
           CAST(r.relname AS sql_identifier) AS table_name,
           CAST(current_database() AS sql_identifier) AS constraint_catalog,
           CAST(nc.nspname AS sql_identifier) AS constraint_schema,
           CAST(c.conname AS sql_identifier) AS constraint_name

    FROM pg_constraint c, pg_namespace nc,
         pg_class r, pg_namespace nr,
         pg_user u

    WHERE c.connamespace = nc.oid AND r.relnamespace = nr.oid
          AND ( (c.contype = 'f' AND c.confrelid = r.oid)
             OR (c.contype IN ('p', 'u') AND c.conrelid = r.oid) )
482
          AND r.relkind = 'r'
483 484 485 486 487
          AND r.relowner = u.usesysid AND u.usename = current_user;

GRANT SELECT ON constraint_table_usage TO PUBLIC;


488 489 490
-- 20.21 DATA_TYPE_PRIVILEGES view appears later.


Bruce Momjian's avatar
Bruce Momjian committed
491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516
/*
 * 20.24
 * DOMAIN_CONSTRAINTS view
 */

CREATE VIEW domain_constraints AS
    SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
           CAST(rs.nspname AS sql_identifier) AS constraint_schema,
           CAST(con.conname AS sql_identifier) AS constraint_name,
           CAST(current_database() AS sql_identifier) AS domain_catalog,
           CAST(n.nspname AS sql_identifier) AS domain_schema,
           CAST(t.typname AS sql_identifier) AS domain_name,
           CAST(CASE WHEN condeferrable THEN 'YES' ELSE 'NO' END
             AS character_data) AS is_deferrable,
           CAST(CASE WHEN condeferred THEN 'YES' ELSE 'NO' END
             AS character_data) AS initially_deferred
    FROM pg_namespace rs, pg_namespace n, pg_constraint con, pg_type t, pg_user u
    WHERE rs.oid = con.connamespace
          AND n.oid = t.typnamespace
          AND u.usesysid = t.typowner
          AND u.usename = current_user
          AND t.oid = con.contypid;

GRANT SELECT ON domain_constraints TO PUBLIC;


517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543
/*
 * 20.25
 * DOMAIN_UDT_USAGE view
 */

CREATE VIEW domain_udt_usage AS
    SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
           CAST(nbt.nspname AS sql_identifier) AS udt_schema,
           CAST(bt.typname AS sql_identifier) AS udt_name,
           CAST(current_database() AS sql_identifier) AS domain_catalog,
           CAST(nt.nspname AS sql_identifier) AS domain_schema,
           CAST(t.typname AS sql_identifier) AS domain_name

    FROM pg_type t, pg_namespace nt,
         pg_type bt, pg_namespace nbt,
         pg_user u

    WHERE t.typnamespace = nt.oid
          AND t.typbasetype = bt.oid
          AND bt.typnamespace = nbt.oid
          AND t.typtype = 'd'
          AND bt.typowner = u.usesysid
          AND u.usename = current_user;

GRANT SELECT ON domain_udt_usage TO PUBLIC;


Bruce Momjian's avatar
Bruce Momjian committed
544 545 546 547 548 549 550
/*
 * 20.26
 * DOMAINS view
 */

CREATE VIEW domains AS
    SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
551
           CAST(nt.nspname AS sql_identifier) AS domain_schema,
Bruce Momjian's avatar
Bruce Momjian committed
552
           CAST(t.typname AS sql_identifier) AS domain_name,
553 554

           CAST(
555 556 557
             CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
                  WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
                  ELSE 'USER-DEFINED' END
558
             AS character_data)
Bruce Momjian's avatar
Bruce Momjian committed
559 560 561 562 563 564 565 566 567 568 569 570 571
             AS data_type,

           CAST(
             CASE WHEN t.typbasetype IN (25, 1042, 1043, 1560, 1562) AND t.typtypmod <> -1
                  THEN t.typtypmod - 4
                  ELSE null END
             AS cardinal_number)
             AS character_maximum_length,

           CAST(
             CASE WHEN t.typbasetype IN (25, 1042, 1043) THEN 2^30 ELSE null END
             AS cardinal_number)
             AS character_octet_length,
572

Bruce Momjian's avatar
Bruce Momjian committed
573 574 575 576 577 578 579 580 581
           CAST(null AS sql_identifier) AS character_set_catalog,
           CAST(null AS sql_identifier) AS character_set_schema,
           CAST(null AS sql_identifier) AS character_set_name,

           CAST(null AS sql_identifier) AS collation_catalog,
           CAST(null AS sql_identifier) AS collation_schema,
           CAST(null AS sql_identifier) AS collation_name,

           CAST(
582 583 584 585 586 587 588 589
             CASE t.typbasetype
               WHEN 21 /*int2*/ THEN 16
               WHEN 23 /*int4*/ THEN 32
               WHEN 20 /*int8*/ THEN 64
               WHEN 1700 /*numeric*/ THEN ((t.typtypmod - 4) >> 16) & 65535
               WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/
               WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/
               ELSE null END
Bruce Momjian's avatar
Bruce Momjian committed
590 591 592 593
             AS cardinal_number)
             AS numeric_precision,

           CAST(
594 595 596
             CASE WHEN t.typbasetype IN (21, 23, 20, 700, 701) THEN 2
                  WHEN t.typbasetype IN (1700) THEN 10
                  ELSE null END
Bruce Momjian's avatar
Bruce Momjian committed
597 598 599 600
             AS cardinal_number)
             AS numeric_precision_radix,

           CAST(
601 602 603
             CASE WHEN t.typbasetype IN (21, 23, 20) THEN 0
                  WHEN t.typbasetype IN (1700) THEN (t.typtypmod - 4) & 65535
                  ELSE null END
Bruce Momjian's avatar
Bruce Momjian committed
604 605 606 607 608 609 610 611 612 613 614 615 616 617 618
             AS cardinal_number)
             AS numeric_scale,

           CAST(
             CASE WHEN t.typbasetype IN (1083, 1114, 1184, 1266)
                  THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod ELSE null END)
                  WHEN t.typbasetype IN (1186)
                  THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod & 65535 ELSE null END)
                  ELSE null END
             AS cardinal_number)
             AS datetime_precision,

           CAST(null AS character_data) AS interval_type, -- XXX
           CAST(null AS character_data) AS interval_precision, -- XXX

619
           CAST(t.typdefault AS character_data) AS domain_default,
Bruce Momjian's avatar
Bruce Momjian committed
620

621 622 623
           CAST(current_database() AS sql_identifier) AS udt_catalog,
           CAST(nbt.nspname AS sql_identifier) AS udt_schema,
           CAST(bt.typname AS sql_identifier) AS udt_name,
Bruce Momjian's avatar
Bruce Momjian committed
624 625 626 627 628 629

           CAST(null AS sql_identifier) AS scope_catalog,
           CAST(null AS sql_identifier) AS scope_schema,
           CAST(null AS sql_identifier) AS scope_name,

           CAST(null AS cardinal_number) AS maximum_cardinality,
630
           CAST(1 AS sql_identifier) AS dtd_identifier
Bruce Momjian's avatar
Bruce Momjian committed
631

632 633
    FROM pg_type t, pg_namespace nt,
         pg_type bt, pg_namespace nbt
Bruce Momjian's avatar
Bruce Momjian committed
634

635 636 637 638
    WHERE t.typnamespace = nt.oid
          AND t.typbasetype = bt.oid
          AND bt.typnamespace = nbt.oid
          AND t.typtype = 'd';
Bruce Momjian's avatar
Bruce Momjian committed
639 640 641 642

GRANT SELECT ON domains TO PUBLIC;


643 644 645
-- 20.27 ELEMENT_TYPES view appears later.


646 647 648 649 650 651 652 653 654 655 656 657 658 659
/*
 * 20.28
 * ENABLED_ROLES view
 */

CREATE VIEW enabled_roles AS
    SELECT CAST(g.groname AS sql_identifier) AS role_name
    FROM pg_group g, pg_user u
    WHERE u.usesysid = ANY (g.grolist)
          AND u.usename = current_user;

GRANT SELECT ON enabled_roles TO PUBLIC;


660 661 662 663 664 665 666 667 668 669 670 671 672 673 674
/*
 * 20.30
 * KEY_COLUMN_USAGE view
 */

CREATE VIEW key_column_usage AS
    SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
           CAST(nc.nspname AS sql_identifier) AS constraint_schema,
           CAST(c.conname AS sql_identifier) AS constraint_name,
           CAST(current_database() AS sql_identifier) AS table_catalog,
           CAST(nr.nspname AS sql_identifier) AS table_schema,
           CAST(r.relname AS sql_identifier) AS table_name,
           CAST(a.attname AS sql_identifier) AS column_name,
           CAST(pos.n AS cardinal_number) AS ordinal_position

675 676
    FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc,
         pg_constraint c, pg_user u, _pg_keypositions() AS pos(n)
677 678 679 680 681 682 683 684 685 686 687 688 689 690 691
    WHERE nr.oid = r.relnamespace
          AND r.oid = a.attrelid
          AND r.oid = c.conrelid
          AND nc.oid = c.connamespace
          AND c.conkey[pos.n] = a.attnum
          AND a.attnum > 0
          AND NOT a.attisdropped
          AND c.contype IN ('p', 'u', 'f')
          AND r.relkind = 'r'
          AND r.relowner = u.usesysid
          AND u.usename = current_user;

GRANT SELECT ON key_column_usage TO PUBLIC;


692 693 694 695 696 697 698 699 700
/*
 * 20.33
 * PARAMETERS view
 */

CREATE VIEW parameters AS
    SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
           CAST(n.nspname AS sql_identifier) AS specific_schema,
           CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
701
           CAST(pos.n AS cardinal_number) AS ordinal_position,
702 703 704 705 706
           CAST('IN' AS character_data) AS parameter_mode,
           CAST('NO' AS character_data) AS is_result,
           CAST('NO' AS character_data) AS as_locator,
           CAST(null AS sql_identifier) AS parameter_name,
           CAST(
707 708
             CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
                  WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731
                  ELSE 'USER-DEFINED' END AS character_data)
             AS data_type,
           CAST(null AS cardinal_number) AS character_maximum_length,
           CAST(null AS cardinal_number) AS character_octet_length,
           CAST(null AS sql_identifier) AS character_set_catalog,
           CAST(null AS sql_identifier) AS character_set_schema,
           CAST(null AS sql_identifier) AS character_set_name,
           CAST(null AS sql_identifier) AS collation_catalog,
           CAST(null AS sql_identifier) AS collation_schema,
           CAST(null AS sql_identifier) AS collation_name,
           CAST(null AS cardinal_number) AS numeric_precision,
           CAST(null AS cardinal_number) AS numeric_precision_radix,
           CAST(null AS cardinal_number) AS numeric_scale,
           CAST(null AS cardinal_number) AS datetime_precision,
           CAST(null AS character_data) AS interval_type,
           CAST(null AS character_data) AS interval_precision,
           CAST(current_database() AS sql_identifier) AS udt_catalog,
           CAST(nt.nspname AS sql_identifier) AS udt_schema,
           CAST(t.typname AS sql_identifier) AS udt_name,
           CAST(null AS sql_identifier) AS scope_catalog,
           CAST(null AS sql_identifier) AS scope_schema,
           CAST(null AS sql_identifier) AS scope_name,
           CAST(null AS cardinal_number) AS maximum_cardinality,
732
           CAST(pos.n AS sql_identifier) AS dtd_identifier
733 734

    FROM pg_namespace n, pg_proc p, pg_type t, pg_namespace nt, pg_user u,
735 736 737 738
         _pg_keypositions() AS pos(n)

    WHERE n.oid = p.pronamespace AND p.pronargs >= pos.n
          AND p.proargtypes[pos.n-1] = t.oid AND t.typnamespace = nt.oid
739 740 741 742 743 744
          AND p.proowner = u.usesysid
          AND (u.usename = current_user OR has_function_privilege(p.oid, 'EXECUTE'));

GRANT SELECT ON parameters TO PUBLIC;


745 746 747 748 749
/*
 * 20.35
 * REFERENTIAL_CONSTRAINTS view
 */

750 751 752 753
CREATE FUNCTION _pg_keyissubset(smallint[], smallint[]) RETURNS boolean
    LANGUAGE sql
    IMMUTABLE
    RETURNS NULL ON NULL INPUT
754
    AS 'select $1[1] is null or ($1[1] = any ($2) and coalesce(information_schema._pg_keyissubset($1[2:pg_catalog.array_upper($1,1)], $2), true))';
755 756 757 758 759

CREATE FUNCTION _pg_keysequal(smallint[], smallint[]) RETURNS boolean
    LANGUAGE sql
    IMMUTABLE
    RETURNS NULL ON NULL INPUT
Peter Eisentraut's avatar
Peter Eisentraut committed
760
    AS 'select information_schema._pg_keyissubset($1, $2) and information_schema._pg_keyissubset($2, $1)';
761

762 763 764 765
CREATE VIEW referential_constraints AS
    SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
           CAST(ncon.nspname AS sql_identifier) AS constraint_schema,
           CAST(con.conname AS sql_identifier) AS constraint_name,
766 767 768 769
           CAST(
             CASE WHEN npkc.nspname IS NULL THEN NULL
                  ELSE current_database() END
             AS sql_identifier) AS unique_constraint_catalog,
770 771
           CAST(npkc.nspname AS sql_identifier) AS unique_constraint_schema,
           CAST(pkc.conname AS sql_identifier) AS unique_constraint_name,
772 773 774 775 776 777 778 779 780 781 782 783

           CAST(
             CASE con.confmatchtype WHEN 'f' THEN 'FULL'
                                    WHEN 'p' THEN 'PARTIAL'
                                    WHEN 'u' THEN 'NONE' END
             AS character_data) AS match_option,

           CAST(
             CASE con.confupdtype WHEN 'c' THEN 'CASCADE'
                                  WHEN 'n' THEN 'SET NULL'
                                  WHEN 'd' THEN 'SET DEFAULT'
                                  WHEN 'r' THEN 'RESTRICT'
784
                                  WHEN 'a' THEN 'NO ACTION' END
785 786 787 788 789 790 791
             AS character_data) AS update_rule,

           CAST(
             CASE con.confdeltype WHEN 'c' THEN 'CASCADE'
                                  WHEN 'n' THEN 'SET NULL'
                                  WHEN 'd' THEN 'SET DEFAULT'
                                  WHEN 'r' THEN 'RESTRICT'
792
                                  WHEN 'a' THEN 'NO ACTION' END
793 794
             AS character_data) AS delete_rule

795 796 797 798 799 800
    FROM (pg_namespace ncon INNER JOIN pg_constraint con ON ncon.oid = con.connamespace
         INNER JOIN pg_class c ON con.conrelid = c.oid
         INNER JOIN pg_user u ON c.relowner = u.usesysid)
         LEFT JOIN
         (pg_constraint pkc INNER JOIN pg_namespace npkc ON pkc.connamespace = npkc.oid)
         ON con.confrelid = pkc.conrelid AND _pg_keysequal(con.confkey, pkc.conkey)
801

802 803 804
    WHERE c.relkind = 'r'
          AND con.contype = 'f'
          AND (pkc.contype IN ('p', 'u') OR pkc.contype IS NULL)
805 806 807 808 809
          AND u.usename = current_user;

GRANT SELECT ON referential_constraints TO PUBLIC;


810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832
/*
 * 20.36
 * ROLE_COLUMN_GRANTS view
 */

CREATE VIEW role_column_grants AS
    SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
           CAST(g_grantee.groname AS sql_identifier) AS grantee,
           CAST(current_database() AS sql_identifier) AS table_catalog,
           CAST(nc.nspname AS sql_identifier) AS table_schema,
           CAST(c.relname AS sql_identifier) AS table_name,
           CAST(a.attname AS sql_identifier) AS column_name,
           CAST(pr.type AS character_data) AS privilege_type,
           CAST(
             CASE WHEN aclcontains(c.relacl,
                                   makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, pr.type, true))
                  THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable

    FROM pg_attribute a,
         pg_class c,
         pg_namespace nc,
         pg_user u_grantor,
         pg_group g_grantee,
833 834 835 836
         (SELECT 'SELECT' UNION ALL
          SELECT 'INSERT' UNION ALL
          SELECT 'UPDATE' UNION ALL
          SELECT 'REFERENCES') AS pr (type)
837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904

    WHERE a.attrelid = c.oid
          AND c.relnamespace = nc.oid
          AND a.attnum > 0
          AND NOT a.attisdropped
          AND c.relkind IN ('r', 'v')
          AND aclcontains(c.relacl,
                          makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, pr.type, false))
          AND g_grantee.groname IN (SELECT role_name FROM enabled_roles);

GRANT SELECT ON role_column_grants TO PUBLIC;


/*
 * 20.37
 * ROLE_ROUTINE_GRANTS view
 */

CREATE VIEW role_routine_grants AS
    SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
           CAST(g_grantee.groname AS sql_identifier) AS grantee,
           CAST(current_database() AS sql_identifier) AS specific_catalog,
           CAST(n.nspname AS sql_identifier) AS specific_schema,
           CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
           CAST(current_database() AS sql_identifier) AS routine_catalog,
           CAST(n.nspname AS sql_identifier) AS routine_schema,
           CAST(p.proname AS sql_identifier) AS routine_name,
           CAST('EXECUTE' AS character_data) AS privilege_type,
           CAST(
             CASE WHEN aclcontains(p.proacl,
                                   makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, 'EXECUTE', true))
                  THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable

    FROM pg_proc p,
         pg_namespace n,
         pg_user u_grantor,
         pg_group g_grantee

    WHERE p.pronamespace = n.oid
          AND aclcontains(p.proacl,
                          makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, 'EXECUTE', false))
          AND g_grantee.groname IN (SELECT role_name FROM enabled_roles);

GRANT SELECT ON role_routine_grants TO PUBLIC;


/*
 * 20.38
 * ROLE_TABLE_GRANTS view
 */

CREATE VIEW role_table_grants AS
    SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
           CAST(g_grantee.groname AS sql_identifier) AS grantee,
           CAST(current_database() AS sql_identifier) AS table_catalog,
           CAST(nc.nspname AS sql_identifier) AS table_schema,
           CAST(c.relname AS sql_identifier) AS table_name,
           CAST(pr.type AS character_data) AS privilege_type,
           CAST(
             CASE WHEN aclcontains(c.relacl,
                                   makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, pr.type, true))
                  THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable,
           CAST('NO' AS character_data) AS with_hierarchy

    FROM pg_class c,
         pg_namespace nc,
         pg_user u_grantor,
         pg_group g_grantee,
905 906 907 908 909 910 911
         (SELECT 'SELECT' UNION ALL
          SELECT 'DELETE' UNION ALL
          SELECT 'INSERT' UNION ALL
          SELECT 'UPDATE' UNION ALL
          SELECT 'REFERENCES' UNION ALL
          SELECT 'RULE' UNION ALL
          SELECT 'TRIGGER') AS pr (type)
912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 933 934 935 936 937 938 939 940 941 942 943

    WHERE c.relnamespace = nc.oid
          AND c.relkind IN ('r', 'v')
          AND aclcontains(c.relacl,
                          makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, pr.type, false))
          AND g_grantee.groname IN (SELECT role_name FROM enabled_roles);

GRANT SELECT ON role_table_grants TO PUBLIC;


/*
 * 20.40
 * ROLE_USAGE_GRANTS view
 */

-- See USAGE_PRIVILEGES.

CREATE VIEW role_usage_grants AS
    SELECT CAST(null AS sql_identifier) AS grantor,
           CAST(null AS sql_identifier) AS grantee,
           CAST(current_database() AS sql_identifier) AS object_catalog,
           CAST(null AS sql_identifier) AS object_schema,
           CAST(null AS sql_identifier) AS object_name,
           CAST(null AS character_data) AS object_type,
           CAST('USAGE' AS character_data) AS privilege_type,
           CAST(null AS character_data) AS is_grantable

    WHERE false;

GRANT SELECT ON role_usage_grants TO PUBLIC;


944 945 946 947 948 949
/*
 * 20.43
 * ROUTINE_PRIVILEGES view
 */

CREATE VIEW routine_privileges AS
950
    SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
951
           CAST(grantee.name AS sql_identifier) AS grantee,
952 953 954 955 956 957 958
           CAST(current_database() AS sql_identifier) AS specific_catalog,
           CAST(n.nspname AS sql_identifier) AS specific_schema,
           CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
           CAST(current_database() AS sql_identifier) AS routine_catalog,
           CAST(n.nspname AS sql_identifier) AS routine_schema,
           CAST(p.proname AS sql_identifier) AS routine_name,
           CAST('EXECUTE' AS character_data) AS privilege_type,
959 960
           CAST(
             CASE WHEN aclcontains(p.proacl,
961
                                   makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, 'EXECUTE', true))
962
                  THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
963

964
    FROM pg_proc p,
965
         pg_namespace n,
966
         pg_user u_grantor,
967 968
         (
           SELECT usesysid, 0, usename FROM pg_user
Peter Eisentraut's avatar
Peter Eisentraut committed
969
           UNION ALL
970
           SELECT 0, grosysid, groname FROM pg_group
Peter Eisentraut's avatar
Peter Eisentraut committed
971
           UNION ALL
972 973
           SELECT 0, 0, 'PUBLIC'
         ) AS grantee (usesysid, grosysid, name)
974

975 976
    WHERE p.pronamespace = n.oid
          AND aclcontains(p.proacl,
977
                          makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, 'EXECUTE', false))
978
          AND (u_grantor.usename = current_user
979 980
               OR grantee.name = current_user
               OR grantee.name = 'PUBLIC');
981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005

GRANT SELECT ON routine_privileges TO PUBLIC;


/*
 * 20.45
 * ROUTINES view
 */

CREATE VIEW routines AS
    SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
           CAST(n.nspname AS sql_identifier) AS specific_schema,
           CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
           CAST(current_database() AS sql_identifier) AS routine_catalog,
           CAST(n.nspname AS sql_identifier) AS routine_schema,
           CAST(p.proname AS sql_identifier) AS routine_name,
           CAST('FUNCTION' AS character_data) AS routine_type,
           CAST(null AS sql_identifier) AS module_catalog,
           CAST(null AS sql_identifier) AS module_schema,
           CAST(null AS sql_identifier) AS module_name,
           CAST(null AS sql_identifier) AS udt_catalog,
           CAST(null AS sql_identifier) AS udt_schema,
           CAST(null AS sql_identifier) AS udt_name,

           CAST(
1006 1007
             CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
                  WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030
                  ELSE 'USER-DEFINED' END AS character_data)
             AS data_type,
           CAST(null AS cardinal_number) AS character_maximum_length,
           CAST(null AS cardinal_number) AS character_octet_length,
           CAST(null AS sql_identifier) AS character_set_catalog,
           CAST(null AS sql_identifier) AS character_set_schema,
           CAST(null AS sql_identifier) AS character_set_name,
           CAST(null AS sql_identifier) AS collation_catalog,
           CAST(null AS sql_identifier) AS collation_schema,
           CAST(null AS sql_identifier) AS collation_name,
           CAST(null AS cardinal_number) AS numeric_precision,
           CAST(null AS cardinal_number) AS numeric_precision_radix,
           CAST(null AS cardinal_number) AS numeric_scale,
           CAST(null AS cardinal_number) AS datetime_precision,
           CAST(null AS character_data) AS interval_type,
           CAST(null AS character_data) AS interval_precision,
           CAST(current_database() AS sql_identifier) AS type_udt_catalog,
           CAST(nt.nspname AS sql_identifier) AS type_udt_schema,
           CAST(t.typname AS sql_identifier) AS type_udt_name,
           CAST(null AS sql_identifier) AS scope_catalog,
           CAST(null AS sql_identifier) AS scope_schema,
           CAST(null AS sql_identifier) AS scope_name,
           CAST(null AS cardinal_number) AS maximum_cardinality,
1031
           CAST(0 AS sql_identifier) AS dtd_identifier,
1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067

           CAST(CASE WHEN l.lanname = 'sql' THEN 'SQL' ELSE 'EXTERNAL' END AS character_data)
             AS routine_body,
           CAST(
             CASE WHEN u.usename = current_user THEN p.prosrc ELSE null END
             AS character_data) AS routine_definition,
           CAST(
             CASE WHEN l.lanname = 'c' THEN p.prosrc ELSE null END
             AS character_data) AS external_name,
           CAST(upper(l.lanname) AS character_data) AS external_language,

           CAST('GENERAL' AS character_data) AS parameter_style,
           CAST(CASE WHEN p.provolatile = 'i' THEN 'YES' ELSE 'NO' END AS character_data) AS is_deterministic,
           CAST('MODIFIES' AS character_data) AS sql_data_access,
           CAST(CASE WHEN p.proisstrict THEN 'YES' ELSE 'NO' END AS character_data) AS is_null_call,
           CAST(null AS character_data) AS sql_path,
           CAST('YES' AS character_data) AS schema_level_routine,
           CAST(0 AS cardinal_number) AS max_dynamic_result_sets,
           CAST(null AS character_data) AS is_user_defined_cast,
           CAST(null AS character_data) AS is_implicitly_invocable,
           CAST(CASE WHEN p.prosecdef THEN 'DEFINER' ELSE 'INVOKER' END AS character_data) AS security_type,
           CAST(null AS sql_identifier) AS to_sql_specific_catalog,
           CAST(null AS sql_identifier) AS to_sql_specific_schema,
           CAST(null AS sql_identifier) AS to_sql_specific_name,
           CAST('NO' AS character_data) AS as_locator

    FROM pg_namespace n, pg_proc p, pg_language l, pg_user u,
         pg_type t, pg_namespace nt

    WHERE n.oid = p.pronamespace AND p.prolang = l.oid AND p.proowner = u.usesysid
          AND p.prorettype = t.oid AND t.typnamespace = nt.oid
          AND (u.usename = current_user OR has_function_privilege(p.oid, 'EXECUTE'));

GRANT SELECT ON routines TO PUBLIC;


1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099
/*
 * 20.46
 * SCHEMATA view
 */

CREATE VIEW schemata AS
    SELECT CAST(current_database() AS sql_identifier) AS catalog_name,
           CAST(n.nspname AS sql_identifier) AS schema_name,
           CAST(u.usename AS sql_identifier) AS schema_owner,
           CAST(null AS sql_identifier) AS default_character_set_catalog,
           CAST(null AS sql_identifier) AS default_character_set_schema,
           CAST(null AS sql_identifier) AS default_character_set_name,
           CAST(null AS character_data) AS sql_path
    FROM pg_namespace n, pg_user u
    WHERE n.nspowner = u.usesysid AND u.usename = current_user;

GRANT SELECT ON schemata TO PUBLIC;


/*
 * 20.47
 * SQL_FEATURES table
 */

CREATE TABLE sql_features (
    feature_id          character_data,
    feature_name        character_data,
    sub_feature_id      character_data,
    sub_feature_name    character_data,
    is_supported        character_data,
    is_verified_by      character_data,
    comments            character_data
1100
) WITHOUT OIDS;
1101

1102
-- Will be filled with external data by initdb.
1103 1104 1105 1106

GRANT SELECT ON sql_features TO PUBLIC;


1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128
/*
 * 20.48
 * SQL_IMPLEMENTATION_INFO table
 */

-- Note: Implementation information items are defined in ISO 9075-3:1999,
-- clause 7.1.

CREATE TABLE sql_implementation_info (
    implementation_info_id      character_data,
    implementation_info_name    character_data,
    integer_value               cardinal_number,
    character_value             character_data,
    comments                    character_data
) WITHOUT OIDS;

INSERT INTO sql_implementation_info VALUES ('10003', 'CATALOG NAME', NULL, 'Y', NULL);
INSERT INTO sql_implementation_info VALUES ('10004', 'COLLATING SEQUENCE', NULL, '', 'not supported');
INSERT INTO sql_implementation_info VALUES ('23',    'CURSOR COMMIT BEHAVIOR', 1, NULL, 'close cursors and retain prepared statements');
INSERT INTO sql_implementation_info VALUES ('2',     'DATA SOURCE NAME', NULL, '', NULL);
INSERT INTO sql_implementation_info VALUES ('17',    'DBMS NAME', NULL, (select trim(trailing ' ' from substring(version() from '^[^0-9]*'))), NULL);
INSERT INTO sql_implementation_info VALUES ('18',    'DBMS VERSION', NULL, '???', NULL); -- filled by initdb
Peter Eisentraut's avatar
Peter Eisentraut committed
1129
INSERT INTO sql_implementation_info VALUES ('26',    'DEFAULT TRANSACTION ISOLATION', 2, NULL, 'READ COMMITTED; user-settable');
1130 1131 1132 1133 1134 1135 1136 1137 1138
INSERT INTO sql_implementation_info VALUES ('28',    'IDENTIFIER CASE', 3, NULL, 'stored in mixed case - case sensitive');
INSERT INTO sql_implementation_info VALUES ('85',    'NULL COLLATION', 0, NULL, 'nulls higher than non-nulls');
INSERT INTO sql_implementation_info VALUES ('13',    'SERVER NAME', NULL, '', NULL);
INSERT INTO sql_implementation_info VALUES ('94',    'SPECIAL CHARACTERS', NULL, '', 'all non-ASCII characters allowed');
INSERT INTO sql_implementation_info VALUES ('46',    'TRANSACTION CAPABLE', 2, NULL, 'both DML and DDL');

GRANT SELECT ON sql_implementation_info TO PUBLIC;


1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151
/*
 * 20.49
 * SQL_LANGUAGES table
 */

CREATE TABLE sql_languages (
    sql_language_source         character_data,
    sql_language_year           character_data,
    sql_language_conformance    character_data,
    sql_language_integrity      character_data,
    sql_language_implementation character_data,
    sql_language_binding_style  character_data,
    sql_language_programming_language character_data
1152
) WITHOUT OIDS;
1153 1154 1155 1156 1157 1158 1159

INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'DIRECT', NULL);
INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'EMBEDDED', 'C');

GRANT SELECT ON sql_languages TO PUBLIC;


1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186
/*
 * 20.50
 * SQL_PACKAGES table
 */

CREATE TABLE sql_packages (
    feature_id      character_data,
    feature_name    character_data,
    is_supported    character_data,
    is_verified_by  character_data,
    comments        character_data
) WITHOUT OIDS;

INSERT INTO sql_packages VALUES ('PKG000', 'Core', 'NO', NULL, '');
INSERT INTO sql_packages VALUES ('PKG001', 'Enhanced datetime facilities', 'YES', NULL, '');
INSERT INTO sql_packages VALUES ('PKG002', 'Enhanced integrity management', 'NO', NULL, '');
INSERT INTO sql_packages VALUES ('PKG003', 'OLAP facilities', 'NO', NULL, '');
INSERT INTO sql_packages VALUES ('PKG004', 'PSM', 'NO', NULL, 'PL/pgSQL is similar.');
INSERT INTO sql_packages VALUES ('PKG005', 'CLI', 'NO', NULL, 'ODBC is similar.');
INSERT INTO sql_packages VALUES ('PKG006', 'Basic object support', 'NO', NULL, '');
INSERT INTO sql_packages VALUES ('PKG007', 'Enhanced object support', 'NO', NULL, '');
INSERT INTO sql_packages VALUES ('PKG008', 'Active database', 'NO', NULL, '');
INSERT INTO sql_packages VALUES ('PKG009', 'SQL/MM support', 'NO', NULL, '');

GRANT SELECT ON sql_packages TO PUBLIC;


1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204
/*
 * 20.51
 * SQL_SIZING table
 */

-- Note: Sizing items are defined in ISO 9075-3:1999, clause 7.2.

CREATE TABLE sql_sizing (
    sizing_id       cardinal_number,
    sizing_name     character_data,
    supported_value cardinal_number,
    comments        character_data
) WITHOUT OIDS;

INSERT INTO sql_sizing VALUES (34,    'MAXIMUM CATALOG NAME LENGTH', 63, NULL);
INSERT INTO sql_sizing VALUES (30,    'MAXIMUM COLUMN NAME LENGTH', 63, NULL);
INSERT INTO sql_sizing VALUES (97,    'MAXIMUM COLUMNS IN GROUP BY', 0, NULL);
INSERT INTO sql_sizing VALUES (99,    'MAXIMUM COLUMNS IN ORDER BY', 0, NULL);
Peter Eisentraut's avatar
Peter Eisentraut committed
1205
INSERT INTO sql_sizing VALUES (100,   'MAXIMUM COLUMNS IN SELECT', 1664, NULL); -- match MaxTupleAttributeNumber
1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252
INSERT INTO sql_sizing VALUES (101,   'MAXIMUM COLUMNS IN TABLE', 1600, NULL); -- match MaxHeapAttributeNumber
INSERT INTO sql_sizing VALUES (1,     'MAXIMUM CONCURRENT ACTIVITIES', 0, NULL);
INSERT INTO sql_sizing VALUES (31,    'MAXIMUM CURSOR NAME LENGTH', 63, NULL);
INSERT INTO sql_sizing VALUES (0,     'MAXIMUM DRIVER CONNECTIONS', NULL, NULL);
INSERT INTO sql_sizing VALUES (10005, 'MAXIMUM IDENTIFIER LENGTH', 63, NULL);
INSERT INTO sql_sizing VALUES (32,    'MAXIMUM SCHEMA NAME LENGTH', 63, NULL);
INSERT INTO sql_sizing VALUES (20000, 'MAXIMUM STATEMENT OCTETS', 0, NULL);
INSERT INTO sql_sizing VALUES (20001, 'MAXIMUM STATEMENT OCTETS DATA', 0, NULL);
INSERT INTO sql_sizing VALUES (20002, 'MAXIMUM STATEMENT OCTETS SCHEMA', 0, NULL);
INSERT INTO sql_sizing VALUES (35,    'MAXIMUM TABLE NAME LENGTH', 63, NULL);
INSERT INTO sql_sizing VALUES (106,   'MAXIMUM TABLES IN SELECT', 0, NULL);
INSERT INTO sql_sizing VALUES (107,   'MAXIMUM USER NAME LENGTH', 63, NULL);
INSERT INTO sql_sizing VALUES (25000, 'MAXIMUM CURRENT DEFAULT TRANSFORM GROUP LENGTH', NULL, NULL);
INSERT INTO sql_sizing VALUES (25001, 'MAXIMUM CURRENT TRANSFORM GROUP LENGTH', NULL, NULL);
INSERT INTO sql_sizing VALUES (25002, 'MAXIMUM CURRENT PATH LENGTH', 0, NULL);
INSERT INTO sql_sizing VALUES (25003, 'MAXIMUM CURRENT ROLE LENGTH', NULL, NULL);
INSERT INTO sql_sizing VALUES (25004, 'MAXIMUM SESSION USER LENGTH', 63, NULL);
INSERT INTO sql_sizing VALUES (25005, 'MAXIMUM SYSTEM USER LENGTH', 63, NULL);

UPDATE sql_sizing
    SET supported_value = (SELECT typlen-1 FROM pg_catalog.pg_type WHERE typname = 'name'),
        comments = 'Might be less, depending on character set.'
    WHERE supported_value = 63;

GRANT SELECT ON sql_sizing TO PUBLIC;


/*
 * 20.52
 * SQL_SIZING_PROFILES table
 */

-- The data in this table are defined by various profiles of SQL.
-- Since we don't have any information about such profiles, we provide
-- an empty table.

CREATE TABLE sql_sizing_profiles (
    sizing_id       cardinal_number,
    sizing_name     character_data,
    profile_id      character_data,
    required_value  cardinal_number,
    comments        character_data
) WITHOUT OIDS;

GRANT SELECT ON sql_sizing_profiles TO PUBLIC;


1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283
/*
 * 20.53
 * TABLE_CONSTRAINTS view
 */

CREATE VIEW table_constraints AS
    SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
           CAST(nc.nspname AS sql_identifier) AS constraint_schema,
           CAST(c.conname AS sql_identifier) AS constraint_name,
           CAST(current_database() AS sql_identifier) AS table_catalog,
           CAST(nr.nspname AS sql_identifier) AS table_schema,
           CAST(r.relname AS sql_identifier) AS table_name,
           CAST(
             CASE c.contype WHEN 'c' THEN 'CHECK'
                            WHEN 'f' THEN 'FOREIGN KEY'
                            WHEN 'p' THEN 'PRIMARY KEY'
                            WHEN 'u' THEN 'UNIQUE' END
             AS character_data) AS constraint_type,
           CAST(CASE WHEN c.condeferrable THEN 'YES' ELSE 'NO' END AS character_data)
             AS is_deferrable,
           CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS character_data)
             AS initially_deferred

    FROM pg_namespace nc,
         pg_namespace nr,
         pg_constraint c,
         pg_class r,
         pg_user u

    WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace
          AND c.conrelid = r.oid AND r.relowner = u.usesysid
1284
          AND r.relkind = 'r'
1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296 1297
          AND u.usename = current_user;

-- FIMXE: Not-null constraints are missing here.

GRANT SELECT ON table_constraints TO PUBLIC;


/*
 * 20.55
 * TABLE_PRIVILEGES view
 */

CREATE VIEW table_privileges AS
1298
    SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
1299
           CAST(grantee.name AS sql_identifier) AS grantee,
1300 1301 1302 1303
           CAST(current_database() AS sql_identifier) AS table_catalog,
           CAST(nc.nspname AS sql_identifier) AS table_schema,
           CAST(c.relname AS sql_identifier) AS table_name,
           CAST(pr.type AS character_data) AS privilege_type,
1304 1305
           CAST(
             CASE WHEN aclcontains(c.relacl,
1306
                                   makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr.type, true))
1307
                  THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable,
1308 1309
           CAST('NO' AS character_data) AS with_hierarchy

1310
    FROM pg_class c,
1311
         pg_namespace nc,
1312
         pg_user u_grantor,
1313 1314
         (
           SELECT usesysid, 0, usename FROM pg_user
Peter Eisentraut's avatar
Peter Eisentraut committed
1315
           UNION ALL
1316
           SELECT 0, grosysid, groname FROM pg_group
Peter Eisentraut's avatar
Peter Eisentraut committed
1317
           UNION ALL
1318 1319
           SELECT 0, 0, 'PUBLIC'
         ) AS grantee (usesysid, grosysid, name),
1320 1321 1322 1323 1324 1325 1326
         (SELECT 'SELECT' UNION ALL
          SELECT 'DELETE' UNION ALL
          SELECT 'INSERT' UNION ALL
          SELECT 'UPDATE' UNION ALL
          SELECT 'REFERENCES' UNION ALL
          SELECT 'RULE' UNION ALL
          SELECT 'TRIGGER') AS pr (type)
1327

1328
    WHERE c.relnamespace = nc.oid
1329
          AND c.relkind IN ('r', 'v')
1330
          AND aclcontains(c.relacl,
1331
                          makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr.type, false))
1332
          AND (u_grantor.usename = current_user
1333 1334
               OR grantee.name = current_user
               OR grantee.name = 'PUBLIC');
1335 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365

GRANT SELECT ON table_privileges TO PUBLIC;


/*
 * 20.56
 * TABLES view
 */

CREATE VIEW tables AS
    SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
           CAST(nc.nspname AS sql_identifier) AS table_schema,
           CAST(c.relname AS sql_identifier) AS table_name,

           CAST(
             CASE WHEN nc.nspname LIKE 'pg!_temp!_%' ESCAPE '!' THEN 'LOCAL TEMPORARY'
                  WHEN c.relkind = 'r' THEN 'BASE TABLE'
                  WHEN c.relkind = 'v' THEN 'VIEW'
                  ELSE null END
             AS character_data) AS table_type,

           CAST(null AS sql_identifier) AS self_referencing_column_name,
           CAST(null AS character_data) AS reference_generation,

           CAST(null AS sql_identifier) AS user_defined_type_catalog,
           CAST(null AS sql_identifier) AS user_defined_type_schema,
           CAST(null AS sql_identifier) AS user_defined_name

    FROM pg_namespace nc, pg_class c, pg_user u

    WHERE c.relnamespace = nc.oid AND u.usesysid = c.relowner
1366
          AND c.relkind IN ('r', 'v')
1367
          AND (u.usename = current_user
1368 1369 1370 1371 1372 1373 1374
               OR has_table_privilege(c.oid, 'SELECT')
               OR has_table_privilege(c.oid, 'INSERT')
               OR has_table_privilege(c.oid, 'UPDATE')
               OR has_table_privilege(c.oid, 'DELETE')
               OR has_table_privilege(c.oid, 'RULE')
               OR has_table_privilege(c.oid, 'RERERENCES')
               OR has_table_privilege(c.oid, 'TRIGGER') );
1375 1376 1377 1378

GRANT SELECT ON tables TO PUBLIC;


1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428
/*
 * 20.59
 * TRIGGERED_UPDATE_COLUMNS view
 */

-- PostgreSQL doesn't allow the specification of individual triggered
-- update columns, so this view is empty.

CREATE VIEW triggered_update_columns AS
    SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
           CAST(null AS sql_identifier) AS trigger_schema,
           CAST(null AS sql_identifier) AS trigger_name,
           CAST(current_database() AS sql_identifier) AS event_object_catalog,
           CAST(null AS sql_identifier) AS event_object_schema,
           CAST(null AS sql_identifier) AS event_object_table,
           CAST(null AS sql_identifier) AS event_object_column
    WHERE false;

GRANT SELECT ON triggered_update_columns TO PUBLIC;


/*
 * 20.62
 * TRIGGERS view
 */

CREATE VIEW triggers AS
    SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
           CAST(n.nspname AS sql_identifier) AS trigger_schema,
           CAST(t.tgname AS sql_identifier) AS trigger_name,
           CAST(em.text AS character_data) AS event_manipulation,
           CAST(current_database() AS sql_identifier) AS event_object_catalog,
           CAST(n.nspname AS sql_identifier) AS event_object_schema,
           CAST(c.relname AS sql_identifier) AS event_object_table,
           CAST(null AS cardinal_number) AS action_order,
           CAST(null AS character_data) AS action_condition,
           CAST(
             substring(pg_get_triggerdef(t.oid) from
                       position('EXECUTE PROCEDURE' in substring(pg_get_triggerdef(t.oid) from 48)) + 47)
             AS character_data) AS action_statement,
           CAST(
             CASE WHEN t.tgtype & 1 = 1 THEN 'ROW' ELSE 'STATEMENT' END
             AS character_data) AS action_orientation,
           CAST(
             CASE WHEN t.tgtype & 2 = 2 THEN 'BEFORE' ELSE 'AFTER' END
             AS character_data) AS condition_timing,
           CAST(null AS sql_identifier) AS condition_reference_old_table,
           CAST(null AS sql_identifier) AS condition_reference_new_table

    FROM pg_namespace n, pg_class c, pg_trigger t, pg_user u,
1429 1430 1431
         (SELECT 4, 'INSERT' UNION ALL
          SELECT 8, 'DELETE' UNION ALL
          SELECT 16, 'UPDATE') AS em (num, text)
1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442

    WHERE n.oid = c.relnamespace
          AND c.oid = t.tgrelid
          AND c.relowner = u.usesysid
          AND t.tgtype & em.num <> 0
          AND NOT t.tgisconstraint
          AND u.usename = current_user;

GRANT SELECT ON triggers TO PUBLIC;


1443 1444 1445 1446 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472
/*
 * 20.63
 * USAGE_PRIVILEGES view
 */

-- Of the things currently implemented in PostgreSQL, usage privileges
-- apply only to domains.  Since domains have no real privileges, we
-- represent all domains with implicit usage privilege here.

CREATE VIEW usage_privileges AS
    SELECT CAST(u.usename AS sql_identifier) AS grantor,
           CAST('PUBLIC' AS sql_identifier) AS grantee,
           CAST(current_database() AS sql_identifier) AS object_catalog,
           CAST(n.nspname AS sql_identifier) AS object_schema,
           CAST(t.typname AS sql_identifier) AS object_name,
           CAST('DOMAIN' AS character_data) AS object_type,
           CAST('USAGE' AS character_data) AS privilege_type,
           CAST('NO' AS character_data) AS is_grantable

    FROM pg_user u,
         pg_namespace n,
         pg_type t

    WHERE u.usesysid = t.typowner
          AND t.typnamespace = n.oid
          AND t.typtype = 'd';

GRANT SELECT ON usage_privileges TO PUBLIC;


1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 1484 1485 1486 1487 1488 1489 1490 1491 1492 1493 1494
/*
 * 20.65
 * VIEW_COLUMN_USAGE
 */

CREATE VIEW view_column_usage AS
    SELECT DISTINCT
           CAST(current_database() AS sql_identifier) AS view_catalog,
           CAST(nv.nspname AS sql_identifier) AS view_schema,
           CAST(v.relname AS sql_identifier) AS view_name,
           CAST(current_database() AS sql_identifier) AS table_catalog,
           CAST(nt.nspname AS sql_identifier) AS table_schema,
           CAST(t.relname AS sql_identifier) AS table_name,
           CAST(a.attname AS sql_identifier) AS column_name

    FROM pg_user, pg_namespace nv, pg_class v, pg_depend dv,
         pg_depend dt, pg_class t, pg_namespace nt,
         pg_attribute a, pg_user u

    WHERE nv.oid = v.relnamespace
          AND v.relkind = 'v'
          AND v.oid = dv.refobjid
Peter Eisentraut's avatar
Peter Eisentraut committed
1495 1496
          AND dv.refclassid = 'pg_catalog.pg_class'::regclass
          AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
1497 1498 1499
          AND dv.deptype = 'i'
          AND dv.objid = dt.objid
          AND dv.refobjid <> dt.refobjid
Peter Eisentraut's avatar
Peter Eisentraut committed
1500 1501
          AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
          AND dt.refclassid = 'pg_catalog.pg_class'::regclass
1502 1503 1504 1505 1506 1507 1508 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 1523 1524 1525 1526 1527 1528 1529 1530 1531 1532
          AND dt.refobjid = t.oid
          AND t.relnamespace = nt.oid
          AND t.relkind IN ('r', 'v')
          AND t.oid = a.attrelid
          AND dt.refobjsubid = a.attnum
          AND t.relowner = u.usesysid AND u.usename = current_user;

GRANT SELECT ON view_column_usage TO PUBLIC;


/*
 * 20.66
 * VIEW_TABLE_USAGE
 */

CREATE VIEW view_table_usage AS
    SELECT DISTINCT
           CAST(current_database() AS sql_identifier) AS view_catalog,
           CAST(nv.nspname AS sql_identifier) AS view_schema,
           CAST(v.relname AS sql_identifier) AS view_name,
           CAST(current_database() AS sql_identifier) AS table_catalog,
           CAST(nt.nspname AS sql_identifier) AS table_schema,
           CAST(t.relname AS sql_identifier) AS table_name

    FROM pg_user, pg_namespace nv, pg_class v, pg_depend dv,
         pg_depend dt, pg_class t, pg_namespace nt,
         pg_user u

    WHERE nv.oid = v.relnamespace
          AND v.relkind = 'v'
          AND v.oid = dv.refobjid
Peter Eisentraut's avatar
Peter Eisentraut committed
1533 1534
          AND dv.refclassid = 'pg_catalog.pg_class'::regclass
          AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
1535 1536 1537
          AND dv.deptype = 'i'
          AND dv.objid = dt.objid
          AND dv.refobjid <> dt.refobjid
Peter Eisentraut's avatar
Peter Eisentraut committed
1538 1539
          AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
          AND dt.refclassid = 'pg_catalog.pg_class'::regclass
1540 1541 1542 1543 1544 1545 1546 1547
          AND dt.refobjid = t.oid
          AND t.relnamespace = nt.oid
          AND t.relkind IN ('r', 'v')
          AND t.relowner = u.usesysid AND u.usename = current_user;

GRANT SELECT ON view_table_usage TO PUBLIC;


1548 1549 1550 1551 1552 1553 1554 1555 1556 1557 1558 1559 1560 1561 1562 1563 1564 1565 1566 1567 1568 1569
/*
 * 20.68
 * VIEWS view
 */

CREATE VIEW views AS
    SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
           CAST(nc.nspname AS sql_identifier) AS table_schema,
           CAST(c.relname AS sql_identifier) AS table_name,

           CAST(
             CASE WHEN u.usename = current_user THEN pg_get_viewdef(c.oid)
                  ELSE null END
             AS character_data) AS view_definition,

           CAST('NONE' AS character_data) AS check_option,
           CAST(null AS character_data) AS is_updatable, -- FIXME
           CAST(null AS character_data) AS is_insertable_into  -- FIXME

    FROM pg_namespace nc, pg_class c, pg_user u

    WHERE c.relnamespace = nc.oid AND u.usesysid = c.relowner
1570
          AND c.relkind = 'v'
1571
          AND (u.usename = current_user
1572 1573 1574 1575 1576 1577 1578
               OR has_table_privilege(c.oid, 'SELECT')
               OR has_table_privilege(c.oid, 'INSERT')
               OR has_table_privilege(c.oid, 'UPDATE')
               OR has_table_privilege(c.oid, 'DELETE')
               OR has_table_privilege(c.oid, 'RULE')
               OR has_table_privilege(c.oid, 'RERERENCES')
               OR has_table_privilege(c.oid, 'TRIGGER') );
1579 1580

GRANT SELECT ON views TO PUBLIC;
1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 1592 1593 1594 1595 1596 1597 1598 1599


-- The following views have dependencies that force them to appear out of order.

/*
 * 20.21
 * DATA_TYPE_PRIVILEGES view
 */

CREATE VIEW data_type_privileges AS
    SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
           CAST(x.objschema AS sql_identifier) AS object_schema,
           CAST(x.objname AS sql_identifier) AS object_name,
           CAST(x.objtype AS character_data) AS object_type,
           CAST(x.objdtdid AS sql_identifier) AS dtd_identifier

    FROM
      (
        SELECT table_schema, table_name, 'TABLE'::text, dtd_identifier FROM columns
Peter Eisentraut's avatar
Peter Eisentraut committed
1600
        UNION ALL
1601
        SELECT domain_schema, domain_name, 'DOMAIN'::text, dtd_identifier FROM domains
Peter Eisentraut's avatar
Peter Eisentraut committed
1602
        UNION ALL
1603
        SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM parameters
Peter Eisentraut's avatar
Peter Eisentraut committed
1604
        UNION ALL
1605 1606 1607 1608 1609 1610 1611 1612 1613 1614 1615 1616 1617 1618 1619 1620 1621 1622 1623 1624 1625 1626 1627 1628 1629 1630 1631 1632 1633 1634 1635 1636 1637 1638 1639 1640 1641 1642 1643 1644 1645 1646 1647 1648 1649 1650 1651 1652 1653 1654 1655 1656
        SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM routines
      ) AS x (objschema, objname, objtype, objdtdid);

GRANT SELECT ON data_type_privileges TO PUBLIC;


/*
 * 20.27
 * ELEMENT_TYPES view
 */

CREATE VIEW element_types AS
    SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
           CAST(n.nspname AS sql_identifier) AS object_schema,
           CAST(x.objname AS sql_identifier) AS object_name,
           CAST(x.objtype AS character_data) AS object_type,
           CAST(x.objdtdid AS sql_identifier) AS array_type_identifier,
           CAST(
             CASE WHEN nbt.nspname = 'pg_catalog' THEN format_type(bt.oid, null)
                  ELSE 'USER-DEFINED' END AS character_data) AS data_type,

           CAST(null AS cardinal_number) AS character_maximum_length,
           CAST(null AS cardinal_number) AS character_octet_length,
           CAST(null AS sql_identifier) AS character_set_catalog,
           CAST(null AS sql_identifier) AS character_set_schema,
           CAST(null AS sql_identifier) AS character_set_name,
           CAST(null AS sql_identifier) AS collation_catalog,
           CAST(null AS sql_identifier) AS collation_schema,
           CAST(null AS sql_identifier) AS collation_name,
           CAST(null AS cardinal_number) AS numeric_precision,
           CAST(null AS cardinal_number) AS numeric_precision_radix,
           CAST(null AS cardinal_number) AS numeric_scale,
           CAST(null AS cardinal_number) AS datetime_precision,
           CAST(null AS character_data) AS interval_type,
           CAST(null AS character_data) AS interval_precision,
           
           CAST(null AS character_data) AS domain_default, -- XXX maybe a bug in the standard

           CAST(current_database() AS sql_identifier) AS udt_catalog,
           CAST(nbt.nspname AS sql_identifier) AS udt_schema,
           CAST(bt.typname AS sql_identifier) AS udt_name,

           CAST(null AS sql_identifier) AS scope_catalog,
           CAST(null AS sql_identifier) AS scope_schema,
           CAST(null AS sql_identifier) AS scope_name,

           CAST(null AS cardinal_number) AS maximum_cardinality,
           CAST('a' || x.objdtdid AS sql_identifier) AS dtd_identifier

    FROM pg_namespace n, pg_type at, pg_namespace nbt, pg_type bt,
         (
           /* columns */
1657 1658
           SELECT c.relnamespace, CAST(c.relname AS sql_identifier),
                  'TABLE'::text, a.attnum, a.atttypid
1659 1660 1661 1662 1663
           FROM pg_class c, pg_attribute a
           WHERE c.oid = a.attrelid
                 AND c.relkind IN ('r', 'v')
                 AND attnum > 0 AND NOT attisdropped

Peter Eisentraut's avatar
Peter Eisentraut committed
1664
           UNION ALL
1665 1666

           /* domains */
1667 1668
           SELECT t.typnamespace, CAST(t.typname AS sql_identifier),
                  'DOMAIN'::text, 1, t.typbasetype
1669 1670 1671
           FROM pg_type t
           WHERE t.typtype = 'd'

Peter Eisentraut's avatar
Peter Eisentraut committed
1672
           UNION ALL
1673 1674

           /* parameters */
1675 1676 1677 1678
           SELECT p.pronamespace, CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier),
                  'ROUTINE'::text, pos.n, p.proargtypes[pos.n-1]
           FROM pg_proc p, _pg_keypositions() AS pos(n)
           WHERE p.pronargs >= pos.n
1679

Peter Eisentraut's avatar
Peter Eisentraut committed
1680
           UNION ALL
1681 1682

           /* result types */
1683 1684
           SELECT p.pronamespace, CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier),
                  'ROUTINE'::text, 0, p.prorettype
1685 1686 1687 1688 1689 1690 1691 1692 1693 1694
           FROM pg_proc p

         ) AS x (objschema, objname, objtype, objdtdid, objtypeid)

    WHERE n.oid = x.objschema
          AND at.oid = x.objtypeid
          AND (at.typelem <> 0 AND at.typlen = -1)
          AND at.typelem = bt.oid
          AND nbt.oid = bt.typnamespace

Peter Eisentraut's avatar
Peter Eisentraut committed
1695
          AND (n.nspname, x.objname, x.objtype, x.objdtdid) IN
1696 1697 1698 1699
              ( SELECT object_schema, object_name, object_type, dtd_identifier
                    FROM data_type_privileges );

GRANT SELECT ON element_types TO PUBLIC;