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