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