codekingpro/portable-devtools
115k
1/*2 * SQL Information Schema3 * as defined in ISO/IEC 9075-11:20234 *5 * Copyright (c) 2003-2023, PostgreSQL Global Development Group6 *7 * src/backend/catalog/information_schema.sql8 *9 * Note: this file is read in single-user -j mode, which means that the10 * command terminator is semicolon-newline-newline; whenever the backend11 * sees that, it stops and executes what it's got. If you write a lot of12 * statements without empty lines between, they'll all get quoted to you13 * in any error message about one of them, so don't do that. Also, you14 * cannot write a semicolon immediately followed by an empty line in a15 * string literal (including a function body!) or a multiline comment.16 */17 18/*19 * Note: Generally, the definitions in this file should be ordered20 * according to the clause numbers in the SQL standard, which is also the21 * alphabetical order. In some cases it is convenient or necessary to22 * 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 it24 * should have been put.25 */26 27 28/*29 * 6.230 * INFORMATION_SCHEMA schema31 */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 RECORD45 LANGUAGE sql STRICT IMMUTABLE PARALLEL SAFE46 AS 'select $1[s],47 s operator(pg_catalog.-) pg_catalog.array_lower($1,1) operator(pg_catalog.+) 148 from pg_catalog.generate_series(pg_catalog.array_lower($1,1),49 pg_catalog.array_upper($1,1),50 1) as g(s)';51 52/* Given an index's OID and an underlying-table column number, return the53 * column's position in the index (NULL if not there) */54CREATE FUNCTION _pg_index_position(oid, smallint) RETURNS int55 LANGUAGE sql STRICT STABLE56BEGIN ATOMIC57SELECT (ss.a).n FROM58 (SELECT information_schema._pg_expandarray(indkey) AS a59 FROM pg_catalog.pg_index WHERE indexrelid = $1) ss60 WHERE (ss.a).x = $2;61END;62 63CREATE FUNCTION _pg_truetypid(pg_attribute, pg_type) RETURNS oid64 LANGUAGE sql65 IMMUTABLE66 PARALLEL SAFE67 RETURNS NULL ON NULL INPUT68RETURN CASE WHEN $2.typtype = 'd' THEN $2.typbasetype ELSE $1.atttypid END;69 70CREATE FUNCTION _pg_truetypmod(pg_attribute, pg_type) RETURNS int471 LANGUAGE sql72 IMMUTABLE73 PARALLEL SAFE74 RETURNS NULL ON NULL INPUT75RETURN CASE WHEN $2.typtype = 'd' THEN $2.typtypmod ELSE $1.atttypmod END;76 77-- these functions encapsulate knowledge about the encoding of typmod:78 79CREATE FUNCTION _pg_char_max_length(typid oid, typmod int4) RETURNS integer80 LANGUAGE sql81 IMMUTABLE82 PARALLEL SAFE83 RETURNS NULL ON NULL INPUT84RETURN85 CASE WHEN $2 = -1 /* default typmod */86 THEN null87 WHEN $1 IN (1042, 1043) /* char, varchar */88 THEN $2 - 489 WHEN $1 IN (1560, 1562) /* bit, varbit */90 THEN $291 ELSE null92 END;93 94CREATE FUNCTION _pg_char_octet_length(typid oid, typmod int4) RETURNS integer95 LANGUAGE sql96 IMMUTABLE97 PARALLEL SAFE98 RETURNS NULL ON NULL INPUT99RETURN100 CASE WHEN $1 IN (25, 1042, 1043) /* text, char, varchar */101 THEN CASE WHEN $2 = -1 /* default typmod */102 THEN CAST(2^30 AS integer)103 ELSE information_schema._pg_char_max_length($1, $2) *104 pg_catalog.pg_encoding_max_length((SELECT encoding FROM pg_catalog.pg_database WHERE datname = pg_catalog.current_database()))105 END106 ELSE null107 END;108 109CREATE FUNCTION _pg_numeric_precision(typid oid, typmod int4) RETURNS integer110 LANGUAGE sql111 IMMUTABLE112 PARALLEL SAFE113 RETURNS NULL ON NULL INPUT114RETURN115 CASE $1116 WHEN 21 /*int2*/ THEN 16117 WHEN 23 /*int4*/ THEN 32118 WHEN 20 /*int8*/ THEN 64119 WHEN 1700 /*numeric*/ THEN120 CASE WHEN $2 = -1121 THEN null122 ELSE (($2 - 4) >> 16) & 0xFFFF123 END124 WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/125 WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/126 ELSE null127 END;128 129CREATE FUNCTION _pg_numeric_precision_radix(typid oid, typmod int4) RETURNS integer130 LANGUAGE sql131 IMMUTABLE132 PARALLEL SAFE133 RETURNS NULL ON NULL INPUT134RETURN135 CASE WHEN $1 IN (21, 23, 20, 700, 701) THEN 2136 WHEN $1 IN (1700) THEN 10137 ELSE null138 END;139 140CREATE FUNCTION _pg_numeric_scale(typid oid, typmod int4) RETURNS integer141 LANGUAGE sql142 IMMUTABLE143 PARALLEL SAFE144 RETURNS NULL ON NULL INPUT145RETURN146 CASE WHEN $1 IN (21, 23, 20) THEN 0147 WHEN $1 IN (1700) THEN148 CASE WHEN $2 = -1149 THEN null150 ELSE ($2 - 4) & 0xFFFF151 END152 ELSE null153 END;154 155CREATE FUNCTION _pg_datetime_precision(typid oid, typmod int4) RETURNS integer156 LANGUAGE sql157 IMMUTABLE158 PARALLEL SAFE159 RETURNS NULL ON NULL INPUT160RETURN161 CASE WHEN $1 IN (1082) /* date */162 THEN 0163 WHEN $1 IN (1083, 1114, 1184, 1266) /* time, timestamp, same + tz */164 THEN CASE WHEN $2 < 0 THEN 6 ELSE $2 END165 WHEN $1 IN (1186) /* interval */166 THEN CASE WHEN $2 < 0 OR $2 & 0xFFFF = 0xFFFF THEN 6 ELSE $2 & 0xFFFF END167 ELSE null168 END;169 170CREATE FUNCTION _pg_interval_type(typid oid, mod int4) RETURNS text171 LANGUAGE sql172 IMMUTABLE173 PARALLEL SAFE174 RETURNS NULL ON NULL INPUT175RETURN176 CASE WHEN $1 IN (1186) /* interval */177 THEN pg_catalog.upper(substring(pg_catalog.format_type($1, $2) similar 'interval[()0-9]* #"%#"' escape '#'))178 ELSE null179 END;180 181 182-- 6.3 INFORMATION_SCHEMA_CATALOG_NAME view appears later.183 184 185/*186 * 6.4187 * CARDINAL_NUMBER domain188 */189 190CREATE DOMAIN cardinal_number AS integer191 CONSTRAINT cardinal_number_domain_check CHECK (value >= 0);192 193 194/*195 * 6.5196 * CHARACTER_DATA domain197 */198 199CREATE DOMAIN character_data AS character varying COLLATE "C";200 201 202/*203 * 6.6204 * SQL_IDENTIFIER domain205 */206 207CREATE DOMAIN sql_identifier AS name;208 209 210/*211 * 6.3212 * INFORMATION_SCHEMA_CATALOG_NAME view213 */214 215CREATE VIEW information_schema_catalog_name AS216 SELECT CAST(current_database() AS sql_identifier) AS catalog_name;217 218GRANT SELECT ON information_schema_catalog_name TO PUBLIC;219 220 221/*222 * 6.7223 * TIME_STAMP domain224 */225 226CREATE DOMAIN time_stamp AS timestamp(2) with time zone227 DEFAULT current_timestamp(2);228 229/*230 * 6.8231 * YES_OR_NO domain232 */233 234CREATE DOMAIN yes_or_no AS character varying(3) COLLATE "C"235 CONSTRAINT yes_or_no_check CHECK (value IN ('YES', 'NO'));236 237 238-- 6.9 ADMINISTRABLE_ROLE_AUTHORIZATIONS view appears later.239 240 241/*242 * 6.10243 * APPLICABLE_ROLES view244 */245 246CREATE VIEW applicable_roles AS247 SELECT CAST(a.rolname AS sql_identifier) AS grantee,248 CAST(b.rolname AS sql_identifier) AS role_name,249 CAST(CASE WHEN m.admin_option THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable250 FROM (SELECT member, roleid, admin_option FROM pg_auth_members251 -- This UNION could be UNION ALL, but UNION works even if we start252 -- to allow explicit pg_database_owner membership.253 UNION254 SELECT datdba, pg_authid.oid, false255 FROM pg_database, pg_authid256 WHERE datname = current_database() AND rolname = 'pg_database_owner'257 ) m258 JOIN pg_authid a ON (m.member = a.oid)259 JOIN pg_authid b ON (m.roleid = b.oid)260 WHERE pg_has_role(a.oid, 'USAGE');261 262GRANT SELECT ON applicable_roles TO PUBLIC;263 264 265/*266 * 6.9267 * ADMINISTRABLE_ROLE_AUTHORIZATIONS view268 */269 270CREATE VIEW administrable_role_authorizations AS271 SELECT *272 FROM applicable_roles273 WHERE is_grantable = 'YES';274 275GRANT SELECT ON administrable_role_authorizations TO PUBLIC;276 277 278/*279 * 6.11280 * ASSERTIONS view281 */282 283-- feature not supported284 285 286/*287 * 6.12288 * ATTRIBUTES view289 */290 291CREATE VIEW attributes AS292 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,293 CAST(nc.nspname AS sql_identifier) AS udt_schema,294 CAST(c.relname AS sql_identifier) AS udt_name,295 CAST(a.attname AS sql_identifier) AS attribute_name,296 CAST(a.attnum AS cardinal_number) AS ordinal_position,297 CAST(pg_get_expr(ad.adbin, ad.adrelid) AS character_data) AS attribute_default,298 CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END299 AS yes_or_no)300 AS is_nullable, -- This column was apparently removed between SQL:2003 and SQL:2008.301 302 CAST(303 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'304 WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, null)305 ELSE 'USER-DEFINED' END306 AS character_data)307 AS data_type,308 309 CAST(310 _pg_char_max_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))311 AS cardinal_number)312 AS character_maximum_length,313 314 CAST(315 _pg_char_octet_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))316 AS cardinal_number)317 AS character_octet_length,318 319 CAST(null AS sql_identifier) AS character_set_catalog,320 CAST(null AS sql_identifier) AS character_set_schema,321 CAST(null AS sql_identifier) AS character_set_name,322 323 CAST(CASE WHEN nco.nspname IS NOT NULL THEN current_database() END AS sql_identifier) AS collation_catalog,324 CAST(nco.nspname AS sql_identifier) AS collation_schema,325 CAST(co.collname AS sql_identifier) AS collation_name,326 327 CAST(328 _pg_numeric_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))329 AS cardinal_number)330 AS numeric_precision,331 332 CAST(333 _pg_numeric_precision_radix(_pg_truetypid(a, t), _pg_truetypmod(a, t))334 AS cardinal_number)335 AS numeric_precision_radix,336 337 CAST(338 _pg_numeric_scale(_pg_truetypid(a, t), _pg_truetypmod(a, t))339 AS cardinal_number)340 AS numeric_scale,341 342 CAST(343 _pg_datetime_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))344 AS cardinal_number)345 AS datetime_precision,346 347 CAST(348 _pg_interval_type(_pg_truetypid(a, t), _pg_truetypmod(a, t))349 AS character_data)350 AS interval_type,351 CAST(null AS cardinal_number) AS interval_precision,352 353 CAST(current_database() AS sql_identifier) AS attribute_udt_catalog,354 CAST(nt.nspname AS sql_identifier) AS attribute_udt_schema,355 CAST(t.typname AS sql_identifier) AS attribute_udt_name,356 357 CAST(null AS sql_identifier) AS scope_catalog,358 CAST(null AS sql_identifier) AS scope_schema,359 CAST(null AS sql_identifier) AS scope_name,360 361 CAST(null AS cardinal_number) AS maximum_cardinality,362 CAST(a.attnum AS sql_identifier) AS dtd_identifier,363 CAST('NO' AS yes_or_no) AS is_derived_reference_attribute364 365 FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum)366 JOIN (pg_class c JOIN pg_namespace nc ON (c.relnamespace = nc.oid)) ON a.attrelid = c.oid367 JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON a.atttypid = t.oid368 LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid))369 ON a.attcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default')370 371 WHERE a.attnum > 0 AND NOT a.attisdropped372 AND c.relkind IN ('c')373 AND (pg_has_role(c.relowner, 'USAGE')374 OR has_type_privilege(c.reltype, 'USAGE'));375 376GRANT SELECT ON attributes TO PUBLIC;377 378 379/*380 * 6.13381 * CHARACTER_SETS view382 */383 384CREATE VIEW character_sets AS385 SELECT CAST(null AS sql_identifier) AS character_set_catalog,386 CAST(null AS sql_identifier) AS character_set_schema,387 CAST(getdatabaseencoding() AS sql_identifier) AS character_set_name,388 CAST(CASE WHEN getdatabaseencoding() = 'UTF8' THEN 'UCS' ELSE getdatabaseencoding() END AS sql_identifier) AS character_repertoire,389 CAST(getdatabaseencoding() AS sql_identifier) AS form_of_use,390 CAST(current_database() AS sql_identifier) AS default_collate_catalog,391 CAST(nc.nspname AS sql_identifier) AS default_collate_schema,392 CAST(c.collname AS sql_identifier) AS default_collate_name393 FROM pg_database d394 LEFT JOIN (pg_collation c JOIN pg_namespace nc ON (c.collnamespace = nc.oid))395 ON (datcollate = collcollate AND datctype = collctype)396 WHERE d.datname = current_database()397 ORDER BY char_length(c.collname) DESC, c.collname ASC -- prefer full/canonical name398 LIMIT 1;399 400GRANT SELECT ON character_sets TO PUBLIC;401 402 403/*404 * 6.14405 * CHECK_CONSTRAINT_ROUTINE_USAGE view406 */407 408CREATE VIEW check_constraint_routine_usage AS409 SELECT DISTINCT410 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_name416 FROM pg_namespace nc, pg_constraint c, pg_depend d, pg_proc p, pg_namespace np417 WHERE nc.oid = c.connamespace418 AND c.contype = 'c'419 AND c.oid = d.objid420 AND d.classid = 'pg_catalog.pg_constraint'::regclass421 AND d.refobjid = p.oid422 AND d.refclassid = 'pg_catalog.pg_proc'::regclass423 AND p.pronamespace = np.oid424 AND pg_has_role(p.proowner, 'USAGE');425 426GRANT SELECT ON check_constraint_routine_usage TO PUBLIC;427 428 429/*430 * 6.15431 * CHECK_CONSTRAINTS view432 */433 434CREATE VIEW check_constraints AS435 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_clause440 FROM pg_constraint con441 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 UNION448 -- not-null constraints449 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, -- XXX453 CAST(a.attname || ' IS NOT NULL' AS character_data)454 AS check_clause455 FROM pg_namespace n, pg_class r, pg_attribute a456 WHERE n.oid = r.relnamespace457 AND r.oid = a.attrelid458 AND a.attnum > 0459 AND NOT a.attisdropped460 AND a.attnotnull461 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 * 6.16469 * COLLATIONS view470 */471 472CREATE VIEW collations AS473 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_attribute477 FROM pg_collation c, pg_namespace nc478 WHERE c.collnamespace = nc.oid479 AND collencoding IN (-1, (SELECT encoding FROM pg_database WHERE datname = current_database()));480 481GRANT SELECT ON collations TO PUBLIC;482 483 484/*485 * 6.17486 * COLLATION_CHARACTER_SET_APPLICABILITY view487 */488 489CREATE VIEW collation_character_set_applicability AS490 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_name496 FROM pg_collation c, pg_namespace nc497 WHERE c.collnamespace = nc.oid498 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 * 6.18505 * COLUMN_COLUMN_USAGE view506 */507 508CREATE VIEW column_column_usage AS509 SELECT DISTINCT510 CAST(current_database() AS sql_identifier) AS table_catalog,511 CAST(n.nspname AS sql_identifier) AS table_schema,512 CAST(c.relname AS sql_identifier) AS table_name,513 CAST(ac.attname AS sql_identifier) AS column_name,514 CAST(ad.attname AS sql_identifier) AS dependent_column515 516 FROM pg_namespace n, pg_class c, pg_depend d,517 pg_attribute ac, pg_attribute ad, pg_attrdef atd518 519 WHERE n.oid = c.relnamespace520 AND c.oid = ac.attrelid521 AND c.oid = ad.attrelid522 AND ac.attnum <> ad.attnum523 AND ad.attrelid = atd.adrelid524 AND ad.attnum = atd.adnum525 AND d.classid = 'pg_catalog.pg_attrdef'::regclass526 AND d.refclassid = 'pg_catalog.pg_class'::regclass527 AND d.objid = atd.oid528 AND d.refobjid = ac.attrelid529 AND d.refobjsubid = ac.attnum530 AND ad.attgenerated <> ''531 AND pg_has_role(c.relowner, 'USAGE');532 533GRANT SELECT ON column_column_usage TO PUBLIC;534 535 536/*537 * 6.19538 * COLUMN_DOMAIN_USAGE view539 */540 541CREATE VIEW column_domain_usage AS542 SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,543 CAST(nt.nspname AS sql_identifier) AS domain_schema,544 CAST(t.typname AS sql_identifier) AS domain_name,545 CAST(current_database() AS sql_identifier) AS table_catalog,546 CAST(nc.nspname AS sql_identifier) AS table_schema,547 CAST(c.relname AS sql_identifier) AS table_name,548 CAST(a.attname AS sql_identifier) AS column_name549 550 FROM pg_type t, pg_namespace nt, pg_class c, pg_namespace nc,551 pg_attribute a552 553 WHERE t.typnamespace = nt.oid554 AND c.relnamespace = nc.oid555 AND a.attrelid = c.oid556 AND a.atttypid = t.oid557 AND t.typtype = 'd'558 AND c.relkind IN ('r', 'v', 'f', 'p')559 AND a.attnum > 0560 AND NOT a.attisdropped561 AND pg_has_role(t.typowner, 'USAGE');562 563GRANT SELECT ON column_domain_usage TO PUBLIC;564 565 566/*567 * 6.20568 * COLUMN_PRIVILEGES569 */570 571CREATE VIEW column_privileges AS572 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,573 CAST(grantee.rolname AS sql_identifier) AS grantee,574 CAST(current_database() AS sql_identifier) AS table_catalog,575 CAST(nc.nspname AS sql_identifier) AS table_schema,576 CAST(x.relname AS sql_identifier) AS table_name,577 CAST(x.attname AS sql_identifier) AS column_name,578 CAST(x.prtype AS character_data) AS privilege_type,579 CAST(580 CASE WHEN581 -- object owner always has grant options582 pg_has_role(x.grantee, x.relowner, 'USAGE')583 OR x.grantable584 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable585 586 FROM (587 SELECT pr_c.grantor,588 pr_c.grantee,589 attname,590 relname,591 relnamespace,592 pr_c.prtype,593 pr_c.grantable,594 pr_c.relowner595 FROM (SELECT oid, relname, relnamespace, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).*596 FROM pg_class597 WHERE relkind IN ('r', 'v', 'f', 'p')598 ) pr_c (oid, relname, relnamespace, relowner, grantor, grantee, prtype, grantable),599 pg_attribute a600 WHERE a.attrelid = pr_c.oid601 AND a.attnum > 0602 AND NOT a.attisdropped603 UNION604 SELECT pr_a.grantor,605 pr_a.grantee,606 attname,607 relname,608 relnamespace,609 pr_a.prtype,610 pr_a.grantable,611 c.relowner612 FROM (SELECT attrelid, attname, (aclexplode(coalesce(attacl, acldefault('c', relowner)))).*613 FROM pg_attribute a JOIN pg_class cc ON (a.attrelid = cc.oid)614 WHERE attnum > 0615 AND NOT attisdropped616 ) pr_a (attrelid, attname, grantor, grantee, prtype, grantable),617 pg_class c618 WHERE pr_a.attrelid = c.oid619 AND relkind IN ('r', 'v', 'f', 'p')620 ) x,621 pg_namespace nc,622 pg_authid u_grantor,623 (624 SELECT oid, rolname FROM pg_authid625 UNION ALL626 SELECT 0::oid, 'PUBLIC'627 ) AS grantee (oid, rolname)628 629 WHERE x.relnamespace = nc.oid630 AND x.grantee = grantee.oid631 AND x.grantor = u_grantor.oid632 AND x.prtype IN ('INSERT', 'SELECT', 'UPDATE', 'REFERENCES')633 AND (pg_has_role(u_grantor.oid, 'USAGE')634 OR pg_has_role(grantee.oid, 'USAGE')635 OR grantee.rolname = 'PUBLIC');636 637GRANT SELECT ON column_privileges TO PUBLIC;638 639 640/*641 * 6.21642 * COLUMN_UDT_USAGE view643 */644 645CREATE VIEW column_udt_usage AS646 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,647 CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,648 CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,649 CAST(current_database() AS sql_identifier) AS table_catalog,650 CAST(nc.nspname AS sql_identifier) AS table_schema,651 CAST(c.relname AS sql_identifier) AS table_name,652 CAST(a.attname AS sql_identifier) AS column_name653 654 FROM pg_attribute a, pg_class c, pg_namespace nc,655 (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))656 LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))657 ON (t.typtype = 'd' AND t.typbasetype = bt.oid)658 659 WHERE a.attrelid = c.oid660 AND a.atttypid = t.oid661 AND nc.oid = c.relnamespace662 AND a.attnum > 0 AND NOT a.attisdropped663 AND c.relkind in ('r', 'v', 'f', 'p')664 AND pg_has_role(coalesce(bt.typowner, t.typowner), 'USAGE');665 666GRANT SELECT ON column_udt_usage TO PUBLIC;667 668 669/*670 * 6.22671 * COLUMNS view672 */673 674CREATE VIEW columns AS675 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,676 CAST(nc.nspname AS sql_identifier) AS table_schema,677 CAST(c.relname AS sql_identifier) AS table_name,678 CAST(a.attname AS sql_identifier) AS column_name,679 CAST(a.attnum AS cardinal_number) AS ordinal_position,680 CAST(CASE WHEN a.attgenerated = '' THEN pg_get_expr(ad.adbin, ad.adrelid) END AS character_data) AS column_default,681 CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END682 AS yes_or_no)683 AS is_nullable,684 685 CAST(686 CASE WHEN t.typtype = 'd' THEN687 CASE WHEN bt.typelem <> 0 AND bt.typlen = -1 THEN 'ARRAY'688 WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)689 ELSE 'USER-DEFINED' END690 ELSE691 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'692 WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, null)693 ELSE 'USER-DEFINED' END694 END695 AS character_data)696 AS data_type,697 698 CAST(699 _pg_char_max_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))700 AS cardinal_number)701 AS character_maximum_length,702 703 CAST(704 _pg_char_octet_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))705 AS cardinal_number)706 AS character_octet_length,707 708 CAST(709 _pg_numeric_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))710 AS cardinal_number)711 AS numeric_precision,712 713 CAST(714 _pg_numeric_precision_radix(_pg_truetypid(a, t), _pg_truetypmod(a, t))715 AS cardinal_number)716 AS numeric_precision_radix,717 718 CAST(719 _pg_numeric_scale(_pg_truetypid(a, t), _pg_truetypmod(a, t))720 AS cardinal_number)721 AS numeric_scale,722 723 CAST(724 _pg_datetime_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))725 AS cardinal_number)726 AS datetime_precision,727 728 CAST(729 _pg_interval_type(_pg_truetypid(a, t), _pg_truetypmod(a, t))730 AS character_data)731 AS interval_type,732 CAST(null AS cardinal_number) AS interval_precision,733 734 CAST(null AS sql_identifier) AS character_set_catalog,735 CAST(null AS sql_identifier) AS character_set_schema,736 CAST(null AS sql_identifier) AS character_set_name,737 738 CAST(CASE WHEN nco.nspname IS NOT NULL THEN current_database() END AS sql_identifier) AS collation_catalog,739 CAST(nco.nspname AS sql_identifier) AS collation_schema,740 CAST(co.collname AS sql_identifier) AS collation_name,741 742 CAST(CASE WHEN t.typtype = 'd' THEN current_database() ELSE null END743 AS sql_identifier) AS domain_catalog,744 CAST(CASE WHEN t.typtype = 'd' THEN nt.nspname ELSE null END745 AS sql_identifier) AS domain_schema,746 CAST(CASE WHEN t.typtype = 'd' THEN t.typname ELSE null END747 AS sql_identifier) AS domain_name,748 749 CAST(current_database() AS sql_identifier) AS udt_catalog,750 CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,751 CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,752 753 CAST(null AS sql_identifier) AS scope_catalog,754 CAST(null AS sql_identifier) AS scope_schema,755 CAST(null AS sql_identifier) AS scope_name,756 757 CAST(null AS cardinal_number) AS maximum_cardinality,758 CAST(a.attnum AS sql_identifier) AS dtd_identifier,759 CAST('NO' AS yes_or_no) AS is_self_referencing,760 761 CAST(CASE WHEN a.attidentity IN ('a', 'd') THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_identity,762 CAST(CASE a.attidentity WHEN 'a' THEN 'ALWAYS' WHEN 'd' THEN 'BY DEFAULT' END AS character_data) AS identity_generation,763 CAST(seq.seqstart AS character_data) AS identity_start,764 CAST(seq.seqincrement AS character_data) AS identity_increment,765 CAST(seq.seqmax AS character_data) AS identity_maximum,766 CAST(seq.seqmin AS character_data) AS identity_minimum,767 CAST(CASE WHEN seq.seqcycle THEN 'YES' ELSE 'NO' END AS yes_or_no) AS identity_cycle,768 769 CAST(CASE WHEN a.attgenerated <> '' THEN 'ALWAYS' ELSE 'NEVER' END AS character_data) AS is_generated,770 CAST(CASE WHEN a.attgenerated <> '' THEN pg_get_expr(ad.adbin, ad.adrelid) END AS character_data) AS generation_expression,771 772 CAST(CASE WHEN c.relkind IN ('r', 'p') OR773 (c.relkind IN ('v', 'f') AND774 pg_column_is_updatable(c.oid, a.attnum, false))775 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_updatable776 777 FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum)778 JOIN (pg_class c JOIN pg_namespace nc ON (c.relnamespace = nc.oid)) ON a.attrelid = c.oid779 JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON a.atttypid = t.oid780 LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))781 ON (t.typtype = 'd' AND t.typbasetype = bt.oid)782 LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid))783 ON a.attcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default')784 LEFT JOIN (pg_depend dep JOIN pg_sequence seq ON (dep.classid = 'pg_class'::regclass AND dep.objid = seq.seqrelid AND dep.deptype = 'i'))785 ON (dep.refclassid = 'pg_class'::regclass AND dep.refobjid = c.oid AND dep.refobjsubid = a.attnum)786 787 WHERE (NOT pg_is_other_temp_schema(nc.oid))788 789 AND a.attnum > 0 AND NOT a.attisdropped790 AND c.relkind IN ('r', 'v', 'f', 'p')791 792 AND (pg_has_role(c.relowner, 'USAGE')793 OR has_column_privilege(c.oid, a.attnum,794 'SELECT, INSERT, UPDATE, REFERENCES'));795 796GRANT SELECT ON columns TO PUBLIC;797 798 799/*800 * 6.23801 * CONSTRAINT_COLUMN_USAGE view802 */803 804CREATE VIEW constraint_column_usage AS805 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,806 CAST(tblschema AS sql_identifier) AS table_schema,807 CAST(tblname AS sql_identifier) AS table_name,808 CAST(colname AS sql_identifier) AS column_name,809 CAST(current_database() AS sql_identifier) AS constraint_catalog,810 CAST(cstrschema AS sql_identifier) AS constraint_schema,811 CAST(cstrname AS sql_identifier) AS constraint_name812 813 FROM (814 /* check constraints */815 SELECT DISTINCT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname816 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_depend d, pg_namespace nc, pg_constraint c817 WHERE nr.oid = r.relnamespace818 AND r.oid = a.attrelid819 AND d.refclassid = 'pg_catalog.pg_class'::regclass820 AND d.refobjid = r.oid821 AND d.refobjsubid = a.attnum822 AND d.classid = 'pg_catalog.pg_constraint'::regclass823 AND d.objid = c.oid824 AND c.connamespace = nc.oid825 AND c.contype = 'c'826 AND r.relkind IN ('r', 'p')827 AND NOT a.attisdropped828 829 UNION ALL830 831 /* unique/primary key/foreign key constraints */832 SELECT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname833 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc,834 pg_constraint c835 WHERE nr.oid = r.relnamespace836 AND r.oid = a.attrelid837 AND nc.oid = c.connamespace838 AND r.oid = CASE c.contype WHEN 'f' THEN c.confrelid ELSE c.conrelid END839 AND a.attnum = ANY (CASE c.contype WHEN 'f' THEN c.confkey ELSE c.conkey END)840 AND NOT a.attisdropped841 AND c.contype IN ('p', 'u', 'f')842 AND r.relkind IN ('r', 'p')843 844 ) AS x (tblschema, tblname, tblowner, colname, cstrschema, cstrname)845 846 WHERE pg_has_role(x.tblowner, 'USAGE');847 848GRANT SELECT ON constraint_column_usage TO PUBLIC;849 850 851/*852 * 6.24853 * CONSTRAINT_PERIOD_USAGE view854 */855 856-- feature not supported857 858 859/*860 * 6.25861 * CONSTRAINT_TABLE_USAGE view862 */863 864CREATE VIEW constraint_table_usage AS865 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,866 CAST(nr.nspname AS sql_identifier) AS table_schema,867 CAST(r.relname AS sql_identifier) AS table_name,868 CAST(current_database() AS sql_identifier) AS constraint_catalog,869 CAST(nc.nspname AS sql_identifier) AS constraint_schema,870 CAST(c.conname AS sql_identifier) AS constraint_name871 872 FROM pg_constraint c, pg_namespace nc,873 pg_class r, pg_namespace nr874 875 WHERE c.connamespace = nc.oid AND r.relnamespace = nr.oid876 AND ( (c.contype = 'f' AND c.confrelid = r.oid)877 OR (c.contype IN ('p', 'u') AND c.conrelid = r.oid) )878 AND r.relkind IN ('r', 'p')879 AND pg_has_role(r.relowner, 'USAGE');880 881GRANT SELECT ON constraint_table_usage TO PUBLIC;882 883 884-- 6.26 DATA_TYPE_PRIVILEGES view appears later.885 886 887/*888 * 6.27889 * DIRECT_SUPERTABLES view890 */891 892-- feature not supported893 894 895/*896 * 6.28897 * DIRECT_SUPERTYPES view898 */899 900-- feature not supported901 902 903/*904 * 6.29905 * DOMAIN_CONSTRAINTS view906 */907 908CREATE VIEW domain_constraints AS909 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,910 CAST(rs.nspname AS sql_identifier) AS constraint_schema,911 CAST(con.conname AS sql_identifier) AS constraint_name,912 CAST(current_database() AS sql_identifier) AS domain_catalog,913 CAST(n.nspname AS sql_identifier) AS domain_schema,914 CAST(t.typname AS sql_identifier) AS domain_name,915 CAST(CASE WHEN condeferrable THEN 'YES' ELSE 'NO' END916 AS yes_or_no) AS is_deferrable,917 CAST(CASE WHEN condeferred THEN 'YES' ELSE 'NO' END918 AS yes_or_no) AS initially_deferred919 FROM pg_namespace rs, pg_namespace n, pg_constraint con, pg_type t920 WHERE rs.oid = con.connamespace921 AND n.oid = t.typnamespace922 AND t.oid = con.contypid923 AND (pg_has_role(t.typowner, 'USAGE')924 OR has_type_privilege(t.oid, 'USAGE'));925 926GRANT SELECT ON domain_constraints TO PUBLIC;927 928 929/*930 * DOMAIN_UDT_USAGE view931 * apparently removed in SQL:2003932 */933 934CREATE VIEW domain_udt_usage AS935 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,936 CAST(nbt.nspname AS sql_identifier) AS udt_schema,937 CAST(bt.typname AS sql_identifier) AS udt_name,938 CAST(current_database() AS sql_identifier) AS domain_catalog,939 CAST(nt.nspname AS sql_identifier) AS domain_schema,940 CAST(t.typname AS sql_identifier) AS domain_name941 942 FROM pg_type t, pg_namespace nt,943 pg_type bt, pg_namespace nbt944 945 WHERE t.typnamespace = nt.oid946 AND t.typbasetype = bt.oid947 AND bt.typnamespace = nbt.oid948 AND t.typtype = 'd'949 AND pg_has_role(bt.typowner, 'USAGE');950 951GRANT SELECT ON domain_udt_usage TO PUBLIC;952 953 954/*955 * 6.30956 * DOMAINS view957 */958 959CREATE VIEW domains AS960 SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,961 CAST(nt.nspname AS sql_identifier) AS domain_schema,962 CAST(t.typname AS sql_identifier) AS domain_name,963 964 CAST(965 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'966 WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)967 ELSE 'USER-DEFINED' END968 AS character_data)969 AS data_type,970 971 CAST(972 _pg_char_max_length(t.typbasetype, t.typtypmod)973 AS cardinal_number)974 AS character_maximum_length,975 976 CAST(977 _pg_char_octet_length(t.typbasetype, t.typtypmod)978 AS cardinal_number)979 AS character_octet_length,980 981 CAST(null AS sql_identifier) AS character_set_catalog,982 CAST(null AS sql_identifier) AS character_set_schema,983 CAST(null AS sql_identifier) AS character_set_name,984 985 CAST(CASE WHEN nco.nspname IS NOT NULL THEN current_database() END AS sql_identifier) AS collation_catalog,986 CAST(nco.nspname AS sql_identifier) AS collation_schema,987 CAST(co.collname AS sql_identifier) AS collation_name,988 989 CAST(990 _pg_numeric_precision(t.typbasetype, t.typtypmod)991 AS cardinal_number)992 AS numeric_precision,993 994 CAST(995 _pg_numeric_precision_radix(t.typbasetype, t.typtypmod)996 AS cardinal_number)997 AS numeric_precision_radix,998 999 CAST(1000 _pg_numeric_scale(t.typbasetype, t.typtypmod)1001 AS cardinal_number)1002 AS numeric_scale,1003 1004 CAST(1005 _pg_datetime_precision(t.typbasetype, t.typtypmod)1006 AS cardinal_number)1007 AS datetime_precision,1008 1009 CAST(1010 _pg_interval_type(t.typbasetype, t.typtypmod)1011 AS character_data)1012 AS interval_type,1013 CAST(null AS cardinal_number) AS interval_precision,1014 1015 CAST(t.typdefault AS character_data) AS domain_default,1016 1017 CAST(current_database() AS sql_identifier) AS udt_catalog,1018 CAST(nbt.nspname AS sql_identifier) AS udt_schema,1019 CAST(bt.typname AS sql_identifier) AS udt_name,1020 1021 CAST(null AS sql_identifier) AS scope_catalog,1022 CAST(null AS sql_identifier) AS scope_schema,1023 CAST(null AS sql_identifier) AS scope_name,1024 1025 CAST(null AS cardinal_number) AS maximum_cardinality,1026 CAST(1 AS sql_identifier) AS dtd_identifier1027 1028 FROM (pg_type t JOIN pg_namespace nt ON t.typnamespace = nt.oid)1029 JOIN (pg_type bt JOIN pg_namespace nbt ON bt.typnamespace = nbt.oid)1030 ON (t.typbasetype = bt.oid AND t.typtype = 'd')1031 LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid))1032 ON t.typcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default')1033 1034 WHERE (pg_has_role(t.typowner, 'USAGE')1035 OR has_type_privilege(t.oid, 'USAGE'));1036 1037GRANT SELECT ON domains TO PUBLIC;1038 1039 1040-- 6.31 ELEMENT_TYPES view appears later.1041 1042 1043/*1044 * 6.321045 * ENABLED_ROLES view1046 */1047 1048CREATE VIEW enabled_roles AS1049 SELECT CAST(a.rolname AS sql_identifier) AS role_name1050 FROM pg_authid a1051 WHERE pg_has_role(a.oid, 'USAGE');1052 1053GRANT SELECT ON enabled_roles TO PUBLIC;1054 1055 1056/*1057 * 6.331058 * FIELDS view1059 */1060 1061-- feature not supported1062 1063 1064/*1065 * 6.341066 * KEY_COLUMN_USAGE view1067 */1068 1069CREATE VIEW key_column_usage AS1070 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,1071 CAST(nc_nspname AS sql_identifier) AS constraint_schema,1072 CAST(conname AS sql_identifier) AS constraint_name,1073 CAST(current_database() AS sql_identifier) AS table_catalog,1074 CAST(nr_nspname AS sql_identifier) AS table_schema,1075 CAST(relname AS sql_identifier) AS table_name,1076 CAST(a.attname AS sql_identifier) AS column_name,1077 CAST((ss.x).n AS cardinal_number) AS ordinal_position,1078 CAST(CASE WHEN contype = 'f' THEN1079 _pg_index_position(ss.conindid, ss.confkey[(ss.x).n])1080 ELSE NULL1081 END AS cardinal_number)1082 AS position_in_unique_constraint1083 FROM pg_attribute a,1084 (SELECT r.oid AS roid, r.relname, r.relowner,1085 nc.nspname AS nc_nspname, nr.nspname AS nr_nspname,1086 c.oid AS coid, c.conname, c.contype, c.conindid,1087 c.confkey, c.confrelid,1088 _pg_expandarray(c.conkey) AS x1089 FROM pg_namespace nr, pg_class r, pg_namespace nc,1090 pg_constraint c1091 WHERE nr.oid = r.relnamespace1092 AND r.oid = c.conrelid1093 AND nc.oid = c.connamespace1094 AND c.contype IN ('p', 'u', 'f')1095 AND r.relkind IN ('r', 'p')1096 AND (NOT pg_is_other_temp_schema(nr.oid)) ) AS ss1097 WHERE ss.roid = a.attrelid1098 AND a.attnum = (ss.x).x1099 AND NOT a.attisdropped1100 AND (pg_has_role(relowner, 'USAGE')1101 OR has_column_privilege(roid, a.attnum,1102 'SELECT, INSERT, UPDATE, REFERENCES'));1103 1104GRANT SELECT ON key_column_usage TO PUBLIC;1105 1106 1107/*1108 * 6.351109 * KEY_PERIOD_USAGE view1110 */1111 1112-- feature not supported1113 1114 1115/*1116 * 6.361117 * METHOD_SPECIFICATION_PARAMETERS view1118 */1119 1120-- feature not supported1121 1122 1123/*1124 * 6.371125 * METHOD_SPECIFICATIONS view1126 */1127 1128-- feature not supported1129 1130 1131/*1132 * 6.381133 * PARAMETERS view1134 */1135 1136CREATE VIEW parameters AS1137 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,1138 CAST(n_nspname AS sql_identifier) AS specific_schema,1139 CAST(nameconcatoid(proname, p_oid) AS sql_identifier) AS specific_name,1140 CAST((ss.x).n AS cardinal_number) AS ordinal_position,1141 CAST(1142 CASE WHEN proargmodes IS NULL THEN 'IN'1143 WHEN proargmodes[(ss.x).n] = 'i' THEN 'IN'1144 WHEN proargmodes[(ss.x).n] = 'o' THEN 'OUT'1145 WHEN proargmodes[(ss.x).n] = 'b' THEN 'INOUT'1146 WHEN proargmodes[(ss.x).n] = 'v' THEN 'IN'1147 WHEN proargmodes[(ss.x).n] = 't' THEN 'OUT'1148 END AS character_data) AS parameter_mode,1149 CAST('NO' AS yes_or_no) AS is_result,1150 CAST('NO' AS yes_or_no) AS as_locator,1151 CAST(NULLIF(proargnames[(ss.x).n], '') AS sql_identifier) AS parameter_name,1152 CAST(1153 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'1154 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)1155 ELSE 'USER-DEFINED' END AS character_data)1156 AS data_type,1157 CAST(null AS cardinal_number) AS character_maximum_length,1158 CAST(null AS cardinal_number) AS character_octet_length,1159 CAST(null AS sql_identifier) AS character_set_catalog,1160 CAST(null AS sql_identifier) AS character_set_schema,1161 CAST(null AS sql_identifier) AS character_set_name,1162 CAST(null AS sql_identifier) AS collation_catalog,1163 CAST(null AS sql_identifier) AS collation_schema,1164 CAST(null AS sql_identifier) AS collation_name,1165 CAST(null AS cardinal_number) AS numeric_precision,1166 CAST(null AS cardinal_number) AS numeric_precision_radix,1167 CAST(null AS cardinal_number) AS numeric_scale,1168 CAST(null AS cardinal_number) AS datetime_precision,1169 CAST(null AS character_data) AS interval_type,1170 CAST(null AS cardinal_number) AS interval_precision,1171 CAST(current_database() AS sql_identifier) AS udt_catalog,1172 CAST(nt.nspname AS sql_identifier) AS udt_schema,1173 CAST(t.typname AS sql_identifier) AS udt_name,1174 CAST(null AS sql_identifier) AS scope_catalog,1175 CAST(null AS sql_identifier) AS scope_schema,1176 CAST(null AS sql_identifier) AS scope_name,1177 CAST(null AS cardinal_number) AS maximum_cardinality,1178 CAST((ss.x).n AS sql_identifier) AS dtd_identifier,1179 CAST(1180 CASE WHEN pg_has_role(proowner, 'USAGE')1181 THEN pg_get_function_arg_default(p_oid, (ss.x).n)1182 ELSE NULL END1183 AS character_data) AS parameter_default1184 1185 FROM pg_type t, pg_namespace nt,1186 (SELECT n.nspname AS n_nspname, p.proname, p.oid AS p_oid, p.proowner,1187 p.proargnames, p.proargmodes,1188 _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x1189 FROM pg_namespace n, pg_proc p1190 WHERE n.oid = p.pronamespace1191 AND (pg_has_role(p.proowner, 'USAGE') OR1192 has_function_privilege(p.oid, 'EXECUTE'))) AS ss1193 WHERE t.oid = (ss.x).x AND t.typnamespace = nt.oid;1194 1195GRANT SELECT ON parameters TO PUBLIC;1196 1197 1198/*1199 * 6.391200 * PERIODS view