2 * SQL Information Schema
3 * as defined in ISO/IEC 9075-11:2011
5 * Copyright (c) 2003-2014, PostgreSQL Global Development Group
7 * src/backend/catalog/information_schema.sql
11 * Note: Generally, the definitions in this file should be ordered
12 * according to the clause numbers in the SQL standard, which is also the
13 * alphabetical order. In some cases it is convenient or necessary to
14 * define one information schema view by using another one; in that case,
15 * put the referencing view at the very end and leave a note where it
16 * should have been put.
22 * INFORMATION_SCHEMA schema
25 CREATE SCHEMA information_schema;
26 GRANT USAGE ON SCHEMA information_schema TO PUBLIC;
27 SET search_path TO information_schema;
31 * A few supporting functions first ...
34 /* Expand any 1-D array into a set with integers 1..N */
35 CREATE FUNCTION _pg_expandarray(IN anyarray, OUT x anyelement, OUT n int)
37 LANGUAGE sql STRICT IMMUTABLE
38 AS 'select $1[s], s - pg_catalog.array_lower($1,1) + 1
39 from pg_catalog.generate_series(pg_catalog.array_lower($1,1),
40 pg_catalog.array_upper($1,1),
43 CREATE FUNCTION _pg_keysequal(smallint[], smallint[]) RETURNS boolean
44 LANGUAGE sql IMMUTABLE -- intentionally not STRICT, to allow inlining
45 AS 'select $1 operator(pg_catalog.<@) $2 and $2 operator(pg_catalog.<@) $1';
47 /* Given an index's OID and an underlying-table column number, return the
48 * column's position in the index (NULL if not there) */
49 CREATE FUNCTION _pg_index_position(oid, smallint) RETURNS int
50 LANGUAGE sql STRICT STABLE
53 (SELECT information_schema._pg_expandarray(indkey) AS a
54 FROM pg_catalog.pg_index WHERE indexrelid = $1) ss
58 CREATE FUNCTION _pg_truetypid(pg_attribute, pg_type) RETURNS oid
61 RETURNS NULL ON NULL INPUT
63 $$SELECT CASE WHEN $2.typtype = 'd' THEN $2.typbasetype ELSE $1.atttypid END$$;
65 CREATE FUNCTION _pg_truetypmod(pg_attribute, pg_type) RETURNS int4
68 RETURNS NULL ON NULL INPUT
70 $$SELECT CASE WHEN $2.typtype = 'd' THEN $2.typtypmod ELSE $1.atttypmod END$$;
72 -- these functions encapsulate knowledge about the encoding of typmod:
74 CREATE FUNCTION _pg_char_max_length(typid oid, typmod int4) RETURNS integer
77 RETURNS NULL ON NULL INPUT
80 CASE WHEN $2 = -1 /* default typmod */
82 WHEN $1 IN (1042, 1043) /* char, varchar */
84 WHEN $1 IN (1560, 1562) /* bit, varbit */
89 CREATE FUNCTION _pg_char_octet_length(typid oid, typmod int4) RETURNS integer
92 RETURNS NULL ON NULL INPUT
95 CASE WHEN $1 IN (25, 1042, 1043) /* text, char, varchar */
96 THEN CASE WHEN $2 = -1 /* default typmod */
97 THEN CAST(2^30 AS integer)
98 ELSE information_schema._pg_char_max_length($1, $2) *
99 pg_catalog.pg_encoding_max_length((SELECT encoding FROM pg_catalog.pg_database WHERE datname = pg_catalog.current_database()))
104 CREATE FUNCTION _pg_numeric_precision(typid oid, typmod int4) RETURNS integer
107 RETURNS NULL ON NULL INPUT
111 WHEN 21 /*int2*/ THEN 16
112 WHEN 23 /*int4*/ THEN 32
113 WHEN 20 /*int8*/ THEN 64
114 WHEN 1700 /*numeric*/ THEN
117 ELSE (($2 - 4) >> 16) & 65535
119 WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/
120 WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/
124 CREATE FUNCTION _pg_numeric_precision_radix(typid oid, typmod int4) RETURNS integer
127 RETURNS NULL ON NULL INPUT
130 CASE WHEN $1 IN (21, 23, 20, 700, 701) THEN 2
131 WHEN $1 IN (1700) THEN 10
135 CREATE FUNCTION _pg_numeric_scale(typid oid, typmod int4) RETURNS integer
138 RETURNS NULL ON NULL INPUT
141 CASE WHEN $1 IN (21, 23, 20) THEN 0
142 WHEN $1 IN (1700) THEN
145 ELSE ($2 - 4) & 65535
150 CREATE FUNCTION _pg_datetime_precision(typid oid, typmod int4) RETURNS integer
153 RETURNS NULL ON NULL INPUT
156 CASE WHEN $1 IN (1082) /* date */
158 WHEN $1 IN (1083, 1114, 1184, 1266) /* time, timestamp, same + tz */
159 THEN CASE WHEN $2 < 0 THEN 6 ELSE $2 END
160 WHEN $1 IN (1186) /* interval */
161 THEN CASE WHEN $2 < 0 OR $2 & 65535 = 65535 THEN 6 ELSE $2 & 65535 END
165 CREATE FUNCTION _pg_interval_type(typid oid, mod int4) RETURNS text
168 RETURNS NULL ON NULL INPUT
171 CASE WHEN $1 IN (1186) /* interval */
172 THEN upper(substring(format_type($1, $2) from 'interval[()0-9]* #"%#"' for '#'))
177 -- 5.2 INFORMATION_SCHEMA_CATALOG_NAME view appears later.
182 * CARDINAL_NUMBER domain
185 CREATE DOMAIN cardinal_number AS integer
186 CONSTRAINT cardinal_number_domain_check CHECK (value >= 0);
191 * CHARACTER_DATA domain
194 CREATE DOMAIN character_data AS character varying;
199 * SQL_IDENTIFIER domain
202 CREATE DOMAIN sql_identifier AS character varying;
207 * INFORMATION_SCHEMA_CATALOG_NAME view
210 CREATE VIEW information_schema_catalog_name AS
211 SELECT CAST(current_database() AS sql_identifier) AS catalog_name;
213 GRANT SELECT ON information_schema_catalog_name TO PUBLIC;
221 CREATE DOMAIN time_stamp AS timestamp(2) with time zone
222 DEFAULT current_timestamp(2);
229 CREATE DOMAIN yes_or_no AS character varying(3)
230 CONSTRAINT yes_or_no_check CHECK (value IN ('YES', 'NO'));
233 -- 5.8 ADMINISTRABLE_ROLE_AUTHORIZATIONS view appears later.
238 * APPLICABLE_ROLES view
241 CREATE VIEW applicable_roles AS
242 SELECT CAST(a.rolname AS sql_identifier) AS grantee,
243 CAST(b.rolname AS sql_identifier) AS role_name,
244 CAST(CASE WHEN m.admin_option THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
245 FROM pg_auth_members m
246 JOIN pg_authid a ON (m.member = a.oid)
247 JOIN pg_authid b ON (m.roleid = b.oid)
248 WHERE pg_has_role(a.oid, 'USAGE');
250 GRANT SELECT ON applicable_roles TO PUBLIC;
255 * ADMINISTRABLE_ROLE_AUTHORIZATIONS view
258 CREATE VIEW administrable_role_authorizations AS
260 FROM applicable_roles
261 WHERE is_grantable = 'YES';
263 GRANT SELECT ON administrable_role_authorizations TO PUBLIC;
271 -- feature not supported
279 CREATE VIEW attributes AS
280 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
281 CAST(nc.nspname AS sql_identifier) AS udt_schema,
282 CAST(c.relname AS sql_identifier) AS udt_name,
283 CAST(a.attname AS sql_identifier) AS attribute_name,
284 CAST(a.attnum AS cardinal_number) AS ordinal_position,
285 CAST(pg_get_expr(ad.adbin, ad.adrelid) AS character_data) AS attribute_default,
286 CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
288 AS is_nullable, -- This column was apparently removed between SQL:2003 and SQL:2008.
291 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
292 WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, null)
293 ELSE 'USER-DEFINED' END
298 _pg_char_max_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
300 AS character_maximum_length,
303 _pg_char_octet_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
305 AS character_octet_length,
307 CAST(null AS sql_identifier) AS character_set_catalog,
308 CAST(null AS sql_identifier) AS character_set_schema,
309 CAST(null AS sql_identifier) AS character_set_name,
311 CAST(CASE WHEN nco.nspname IS NOT NULL THEN current_database() END AS sql_identifier) AS collation_catalog,
312 CAST(nco.nspname AS sql_identifier) AS collation_schema,
313 CAST(co.collname AS sql_identifier) AS collation_name,
316 _pg_numeric_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
318 AS numeric_precision,
321 _pg_numeric_precision_radix(_pg_truetypid(a, t), _pg_truetypmod(a, t))
323 AS numeric_precision_radix,
326 _pg_numeric_scale(_pg_truetypid(a, t), _pg_truetypmod(a, t))
331 _pg_datetime_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
333 AS datetime_precision,
336 _pg_interval_type(_pg_truetypid(a, t), _pg_truetypmod(a, t))
339 CAST(null AS cardinal_number) AS interval_precision,
341 CAST(current_database() AS sql_identifier) AS attribute_udt_catalog,
342 CAST(nt.nspname AS sql_identifier) AS attribute_udt_schema,
343 CAST(t.typname AS sql_identifier) AS attribute_udt_name,
345 CAST(null AS sql_identifier) AS scope_catalog,
346 CAST(null AS sql_identifier) AS scope_schema,
347 CAST(null AS sql_identifier) AS scope_name,
349 CAST(null AS cardinal_number) AS maximum_cardinality,
350 CAST(a.attnum AS sql_identifier) AS dtd_identifier,
351 CAST('NO' AS yes_or_no) AS is_derived_reference_attribute
353 FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum)
354 JOIN (pg_class c JOIN pg_namespace nc ON (c.relnamespace = nc.oid)) ON a.attrelid = c.oid
355 JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON a.atttypid = t.oid
356 LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid))
357 ON a.attcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default')
359 WHERE a.attnum > 0 AND NOT a.attisdropped
360 AND c.relkind in ('c')
361 AND (pg_has_role(c.relowner, 'USAGE')
362 OR has_type_privilege(c.reltype, 'USAGE'));
364 GRANT SELECT ON attributes TO PUBLIC;
369 * CHARACTER_SETS view
372 CREATE VIEW character_sets AS
373 SELECT CAST(null AS sql_identifier) AS character_set_catalog,
374 CAST(null AS sql_identifier) AS character_set_schema,
375 CAST(getdatabaseencoding() AS sql_identifier) AS character_set_name,
376 CAST(CASE WHEN getdatabaseencoding() = 'UTF8' THEN 'UCS' ELSE getdatabaseencoding() END AS sql_identifier) AS character_repertoire,
377 CAST(getdatabaseencoding() AS sql_identifier) AS form_of_use,
378 CAST(current_database() AS sql_identifier) AS default_collate_catalog,
379 CAST(nc.nspname AS sql_identifier) AS default_collate_schema,
380 CAST(c.collname AS sql_identifier) AS default_collate_name
382 LEFT JOIN (pg_collation c JOIN pg_namespace nc ON (c.collnamespace = nc.oid))
383 ON (datcollate = collcollate AND datctype = collctype)
384 WHERE d.datname = current_database()
385 ORDER BY char_length(c.collname) DESC, c.collname ASC -- prefer full/canonical name
388 GRANT SELECT ON character_sets TO PUBLIC;
393 * CHECK_CONSTRAINT_ROUTINE_USAGE view
396 CREATE VIEW check_constraint_routine_usage AS
397 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
398 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
399 CAST(c.conname AS sql_identifier) AS constraint_name,
400 CAST(current_database() AS sql_identifier) AS specific_catalog,
401 CAST(np.nspname AS sql_identifier) AS specific_schema,
402 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name
403 FROM pg_namespace nc, pg_constraint c, pg_depend d, pg_proc p, pg_namespace np
404 WHERE nc.oid = c.connamespace
407 AND d.classid = 'pg_catalog.pg_constraint'::regclass
408 AND d.refobjid = p.oid
409 AND d.refclassid = 'pg_catalog.pg_proc'::regclass
410 AND p.pronamespace = np.oid
411 AND pg_has_role(p.proowner, 'USAGE');
413 GRANT SELECT ON check_constraint_routine_usage TO PUBLIC;
418 * CHECK_CONSTRAINTS view
421 CREATE VIEW check_constraints AS
422 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
423 CAST(rs.nspname AS sql_identifier) AS constraint_schema,
424 CAST(con.conname AS sql_identifier) AS constraint_name,
425 CAST(substring(pg_get_constraintdef(con.oid) from 7) AS character_data)
427 FROM pg_constraint con
428 LEFT OUTER JOIN pg_namespace rs ON (rs.oid = con.connamespace)
429 LEFT OUTER JOIN pg_class c ON (c.oid = con.conrelid)
430 LEFT OUTER JOIN pg_type t ON (t.oid = con.contypid)
431 WHERE pg_has_role(coalesce(c.relowner, t.typowner), 'USAGE')
432 AND con.contype = 'c'
435 -- not-null constraints
437 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
438 CAST(n.nspname AS sql_identifier) AS constraint_schema,
439 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
440 CAST(a.attname || ' IS NOT NULL' AS character_data)
442 FROM pg_namespace n, pg_class r, pg_attribute a
443 WHERE n.oid = r.relnamespace
444 AND r.oid = a.attrelid
446 AND NOT a.attisdropped
449 AND pg_has_role(r.relowner, 'USAGE');
451 GRANT SELECT ON check_constraints TO PUBLIC;
459 CREATE VIEW collations AS
460 SELECT CAST(current_database() AS sql_identifier) AS collation_catalog,
461 CAST(nc.nspname AS sql_identifier) AS collation_schema,
462 CAST(c.collname AS sql_identifier) AS collation_name,
463 CAST('NO PAD' AS character_data) AS pad_attribute
464 FROM pg_collation c, pg_namespace nc
465 WHERE c.collnamespace = nc.oid
466 AND collencoding IN (-1, (SELECT encoding FROM pg_database WHERE datname = current_database()));
468 GRANT SELECT ON collations TO PUBLIC;
473 * COLLATION_CHARACTER_SET_APPLICABILITY view
476 CREATE VIEW collation_character_set_applicability AS
477 SELECT CAST(current_database() AS sql_identifier) AS collation_catalog,
478 CAST(nc.nspname AS sql_identifier) AS collation_schema,
479 CAST(c.collname AS sql_identifier) AS collation_name,
480 CAST(null AS sql_identifier) AS character_set_catalog,
481 CAST(null AS sql_identifier) AS character_set_schema,
482 CAST(getdatabaseencoding() AS sql_identifier) AS character_set_name
483 FROM pg_collation c, pg_namespace nc
484 WHERE c.collnamespace = nc.oid
485 AND collencoding IN (-1, (SELECT encoding FROM pg_database WHERE datname = current_database()));
487 GRANT SELECT ON collation_character_set_applicability TO PUBLIC;
492 * COLUMN_COLUMN_USAGE view
495 -- feature not supported
500 * COLUMN_DOMAIN_USAGE view
503 CREATE VIEW column_domain_usage AS
504 SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
505 CAST(nt.nspname AS sql_identifier) AS domain_schema,
506 CAST(t.typname AS sql_identifier) AS domain_name,
507 CAST(current_database() AS sql_identifier) AS table_catalog,
508 CAST(nc.nspname AS sql_identifier) AS table_schema,
509 CAST(c.relname AS sql_identifier) AS table_name,
510 CAST(a.attname AS sql_identifier) AS column_name
512 FROM pg_type t, pg_namespace nt, pg_class c, pg_namespace nc,
515 WHERE t.typnamespace = nt.oid
516 AND c.relnamespace = nc.oid
517 AND a.attrelid = c.oid
518 AND a.atttypid = t.oid
520 AND c.relkind IN ('r', 'v', 'f')
522 AND NOT a.attisdropped
523 AND pg_has_role(t.typowner, 'USAGE');
525 GRANT SELECT ON column_domain_usage TO PUBLIC;
533 CREATE VIEW column_privileges AS
534 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
535 CAST(grantee.rolname AS sql_identifier) AS grantee,
536 CAST(current_database() AS sql_identifier) AS table_catalog,
537 CAST(nc.nspname AS sql_identifier) AS table_schema,
538 CAST(x.relname AS sql_identifier) AS table_name,
539 CAST(x.attname AS sql_identifier) AS column_name,
540 CAST(x.prtype AS character_data) AS privilege_type,
543 -- object owner always has grant options
544 pg_has_role(x.grantee, x.relowner, 'USAGE')
546 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
557 FROM (SELECT oid, relname, relnamespace, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).*
559 WHERE relkind IN ('r', 'v', 'f')
560 ) pr_c (oid, relname, relnamespace, relowner, grantor, grantee, prtype, grantable),
562 WHERE a.attrelid = pr_c.oid
564 AND NOT a.attisdropped
574 FROM (SELECT attrelid, attname, (aclexplode(coalesce(attacl, acldefault('c', relowner)))).*
575 FROM pg_attribute a JOIN pg_class cc ON (a.attrelid = cc.oid)
578 ) pr_a (attrelid, attname, grantor, grantee, prtype, grantable),
580 WHERE pr_a.attrelid = c.oid
581 AND relkind IN ('r', 'v', 'f')
586 SELECT oid, rolname FROM pg_authid
588 SELECT 0::oid, 'PUBLIC'
589 ) AS grantee (oid, rolname)
591 WHERE x.relnamespace = nc.oid
592 AND x.grantee = grantee.oid
593 AND x.grantor = u_grantor.oid
594 AND x.prtype IN ('INSERT', 'SELECT', 'UPDATE', 'REFERENCES')
595 AND (pg_has_role(u_grantor.oid, 'USAGE')
596 OR pg_has_role(grantee.oid, 'USAGE')
597 OR grantee.rolname = 'PUBLIC');
599 GRANT SELECT ON column_privileges TO PUBLIC;
604 * COLUMN_UDT_USAGE view
607 CREATE VIEW column_udt_usage AS
608 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
609 CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,
610 CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,
611 CAST(current_database() AS sql_identifier) AS table_catalog,
612 CAST(nc.nspname AS sql_identifier) AS table_schema,
613 CAST(c.relname AS sql_identifier) AS table_name,
614 CAST(a.attname AS sql_identifier) AS column_name
616 FROM pg_attribute a, pg_class c, pg_namespace nc,
617 (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
618 LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
619 ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
621 WHERE a.attrelid = c.oid
622 AND a.atttypid = t.oid
623 AND nc.oid = c.relnamespace
624 AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v', 'f')
625 AND pg_has_role(coalesce(bt.typowner, t.typowner), 'USAGE');
627 GRANT SELECT ON column_udt_usage TO PUBLIC;
635 CREATE VIEW columns AS
636 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
637 CAST(nc.nspname AS sql_identifier) AS table_schema,
638 CAST(c.relname AS sql_identifier) AS table_name,
639 CAST(a.attname AS sql_identifier) AS column_name,
640 CAST(a.attnum AS cardinal_number) AS ordinal_position,
641 CAST(pg_get_expr(ad.adbin, ad.adrelid) AS character_data) AS column_default,
642 CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
647 CASE WHEN t.typtype = 'd' THEN
648 CASE WHEN bt.typelem <> 0 AND bt.typlen = -1 THEN 'ARRAY'
649 WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
650 ELSE 'USER-DEFINED' END
652 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
653 WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, null)
654 ELSE 'USER-DEFINED' END
660 _pg_char_max_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
662 AS character_maximum_length,
665 _pg_char_octet_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
667 AS character_octet_length,
670 _pg_numeric_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
672 AS numeric_precision,
675 _pg_numeric_precision_radix(_pg_truetypid(a, t), _pg_truetypmod(a, t))
677 AS numeric_precision_radix,
680 _pg_numeric_scale(_pg_truetypid(a, t), _pg_truetypmod(a, t))
685 _pg_datetime_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
687 AS datetime_precision,
690 _pg_interval_type(_pg_truetypid(a, t), _pg_truetypmod(a, t))
693 CAST(null AS cardinal_number) AS interval_precision,
695 CAST(null AS sql_identifier) AS character_set_catalog,
696 CAST(null AS sql_identifier) AS character_set_schema,
697 CAST(null AS sql_identifier) AS character_set_name,
699 CAST(CASE WHEN nco.nspname IS NOT NULL THEN current_database() END AS sql_identifier) AS collation_catalog,
700 CAST(nco.nspname AS sql_identifier) AS collation_schema,
701 CAST(co.collname AS sql_identifier) AS collation_name,
703 CAST(CASE WHEN t.typtype = 'd' THEN current_database() ELSE null END
704 AS sql_identifier) AS domain_catalog,
705 CAST(CASE WHEN t.typtype = 'd' THEN nt.nspname ELSE null END
706 AS sql_identifier) AS domain_schema,
707 CAST(CASE WHEN t.typtype = 'd' THEN t.typname ELSE null END
708 AS sql_identifier) AS domain_name,
710 CAST(current_database() AS sql_identifier) AS udt_catalog,
711 CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,
712 CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,
714 CAST(null AS sql_identifier) AS scope_catalog,
715 CAST(null AS sql_identifier) AS scope_schema,
716 CAST(null AS sql_identifier) AS scope_name,
718 CAST(null AS cardinal_number) AS maximum_cardinality,
719 CAST(a.attnum AS sql_identifier) AS dtd_identifier,
720 CAST('NO' AS yes_or_no) AS is_self_referencing,
722 CAST('NO' AS yes_or_no) AS is_identity,
723 CAST(null AS character_data) AS identity_generation,
724 CAST(null AS character_data) AS identity_start,
725 CAST(null AS character_data) AS identity_increment,
726 CAST(null AS character_data) AS identity_maximum,
727 CAST(null AS character_data) AS identity_minimum,
728 CAST(null AS yes_or_no) AS identity_cycle,
730 CAST('NEVER' AS character_data) AS is_generated,
731 CAST(null AS character_data) AS generation_expression,
733 CAST(CASE WHEN c.relkind = 'r' OR
734 (c.relkind IN ('v', 'f') AND
735 pg_column_is_updatable(c.oid, a.attnum, false))
736 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_updatable
738 FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum)
739 JOIN (pg_class c JOIN pg_namespace nc ON (c.relnamespace = nc.oid)) ON a.attrelid = c.oid
740 JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON a.atttypid = t.oid
741 LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
742 ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
743 LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid))
744 ON a.attcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default')
746 WHERE (NOT pg_is_other_temp_schema(nc.oid))
748 AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v', 'f')
750 AND (pg_has_role(c.relowner, 'USAGE')
751 OR has_column_privilege(c.oid, a.attnum,
752 'SELECT, INSERT, UPDATE, REFERENCES'));
754 GRANT SELECT ON columns TO PUBLIC;
759 * CONSTRAINT_COLUMN_USAGE view
762 CREATE VIEW constraint_column_usage AS
763 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
764 CAST(tblschema AS sql_identifier) AS table_schema,
765 CAST(tblname AS sql_identifier) AS table_name,
766 CAST(colname AS sql_identifier) AS column_name,
767 CAST(current_database() AS sql_identifier) AS constraint_catalog,
768 CAST(cstrschema AS sql_identifier) AS constraint_schema,
769 CAST(cstrname AS sql_identifier) AS constraint_name
772 /* check constraints */
773 SELECT DISTINCT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
774 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_depend d, pg_namespace nc, pg_constraint c
775 WHERE nr.oid = r.relnamespace
776 AND r.oid = a.attrelid
777 AND d.refclassid = 'pg_catalog.pg_class'::regclass
778 AND d.refobjid = r.oid
779 AND d.refobjsubid = a.attnum
780 AND d.classid = 'pg_catalog.pg_constraint'::regclass
782 AND c.connamespace = nc.oid
785 AND NOT a.attisdropped
789 /* unique/primary key/foreign key constraints */
790 SELECT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
791 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc,
793 WHERE nr.oid = r.relnamespace
794 AND r.oid = a.attrelid
795 AND nc.oid = c.connamespace
796 AND (CASE WHEN c.contype = 'f' THEN r.oid = c.confrelid AND a.attnum = ANY (c.confkey)
797 ELSE r.oid = c.conrelid AND a.attnum = ANY (c.conkey) END)
798 AND NOT a.attisdropped
799 AND c.contype IN ('p', 'u', 'f')
802 ) AS x (tblschema, tblname, tblowner, colname, cstrschema, cstrname)
804 WHERE pg_has_role(x.tblowner, 'USAGE');
806 GRANT SELECT ON constraint_column_usage TO PUBLIC;
811 * CONSTRAINT_PERIOD_USAGE view
814 -- feature not supported
819 * CONSTRAINT_TABLE_USAGE view
822 CREATE VIEW constraint_table_usage AS
823 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
824 CAST(nr.nspname AS sql_identifier) AS table_schema,
825 CAST(r.relname AS sql_identifier) AS table_name,
826 CAST(current_database() AS sql_identifier) AS constraint_catalog,
827 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
828 CAST(c.conname AS sql_identifier) AS constraint_name
830 FROM pg_constraint c, pg_namespace nc,
831 pg_class r, pg_namespace nr
833 WHERE c.connamespace = nc.oid AND r.relnamespace = nr.oid
834 AND ( (c.contype = 'f' AND c.confrelid = r.oid)
835 OR (c.contype IN ('p', 'u') AND c.conrelid = r.oid) )
837 AND pg_has_role(r.relowner, 'USAGE');
839 GRANT SELECT ON constraint_table_usage TO PUBLIC;
842 -- 5.25 DATA_TYPE_PRIVILEGES view appears later.
847 * DIRECT_SUPERTABLES view
850 -- feature not supported
855 * DIRECT_SUPERTYPES view
858 -- feature not supported
863 * DOMAIN_CONSTRAINTS view
866 CREATE VIEW domain_constraints AS
867 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
868 CAST(rs.nspname AS sql_identifier) AS constraint_schema,
869 CAST(con.conname AS sql_identifier) AS constraint_name,
870 CAST(current_database() AS sql_identifier) AS domain_catalog,
871 CAST(n.nspname AS sql_identifier) AS domain_schema,
872 CAST(t.typname AS sql_identifier) AS domain_name,
873 CAST(CASE WHEN condeferrable THEN 'YES' ELSE 'NO' END
874 AS yes_or_no) AS is_deferrable,
875 CAST(CASE WHEN condeferred THEN 'YES' ELSE 'NO' END
876 AS yes_or_no) AS initially_deferred
877 FROM pg_namespace rs, pg_namespace n, pg_constraint con, pg_type t
878 WHERE rs.oid = con.connamespace
879 AND n.oid = t.typnamespace
880 AND t.oid = con.contypid
881 AND (pg_has_role(t.typowner, 'USAGE')
882 OR has_type_privilege(t.oid, 'USAGE'));
884 GRANT SELECT ON domain_constraints TO PUBLIC;
888 * DOMAIN_UDT_USAGE view
889 * apparently removed in SQL:2003
892 CREATE VIEW domain_udt_usage AS
893 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
894 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
895 CAST(bt.typname AS sql_identifier) AS udt_name,
896 CAST(current_database() AS sql_identifier) AS domain_catalog,
897 CAST(nt.nspname AS sql_identifier) AS domain_schema,
898 CAST(t.typname AS sql_identifier) AS domain_name
900 FROM pg_type t, pg_namespace nt,
901 pg_type bt, pg_namespace nbt
903 WHERE t.typnamespace = nt.oid
904 AND t.typbasetype = bt.oid
905 AND bt.typnamespace = nbt.oid
907 AND pg_has_role(bt.typowner, 'USAGE');
909 GRANT SELECT ON domain_udt_usage TO PUBLIC;
917 CREATE VIEW domains AS
918 SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
919 CAST(nt.nspname AS sql_identifier) AS domain_schema,
920 CAST(t.typname AS sql_identifier) AS domain_name,
923 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
924 WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
925 ELSE 'USER-DEFINED' END
930 _pg_char_max_length(t.typbasetype, t.typtypmod)
932 AS character_maximum_length,
935 _pg_char_octet_length(t.typbasetype, t.typtypmod)
937 AS character_octet_length,
939 CAST(null AS sql_identifier) AS character_set_catalog,
940 CAST(null AS sql_identifier) AS character_set_schema,
941 CAST(null AS sql_identifier) AS character_set_name,
943 CAST(CASE WHEN nco.nspname IS NOT NULL THEN current_database() END AS sql_identifier) AS collation_catalog,
944 CAST(nco.nspname AS sql_identifier) AS collation_schema,
945 CAST(co.collname AS sql_identifier) AS collation_name,
948 _pg_numeric_precision(t.typbasetype, t.typtypmod)
950 AS numeric_precision,
953 _pg_numeric_precision_radix(t.typbasetype, t.typtypmod)
955 AS numeric_precision_radix,
958 _pg_numeric_scale(t.typbasetype, t.typtypmod)
963 _pg_datetime_precision(t.typbasetype, t.typtypmod)
965 AS datetime_precision,
968 _pg_interval_type(t.typbasetype, t.typtypmod)
971 CAST(null AS cardinal_number) AS interval_precision,
973 CAST(t.typdefault AS character_data) AS domain_default,
975 CAST(current_database() AS sql_identifier) AS udt_catalog,
976 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
977 CAST(bt.typname AS sql_identifier) AS udt_name,
979 CAST(null AS sql_identifier) AS scope_catalog,
980 CAST(null AS sql_identifier) AS scope_schema,
981 CAST(null AS sql_identifier) AS scope_name,
983 CAST(null AS cardinal_number) AS maximum_cardinality,
984 CAST(1 AS sql_identifier) AS dtd_identifier
986 FROM (pg_type t JOIN pg_namespace nt ON t.typnamespace = nt.oid)
987 JOIN (pg_type bt JOIN pg_namespace nbt ON bt.typnamespace = nbt.oid)
988 ON (t.typbasetype = bt.oid AND t.typtype = 'd')
989 LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid))
990 ON t.typcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default')
992 WHERE (pg_has_role(t.typowner, 'USAGE')
993 OR has_type_privilege(t.oid, 'USAGE'));
995 GRANT SELECT ON domains TO PUBLIC;
998 -- 5.30 ELEMENT_TYPES view appears later.
1003 * ENABLED_ROLES view
1006 CREATE VIEW enabled_roles AS
1007 SELECT CAST(a.rolname AS sql_identifier) AS role_name
1009 WHERE pg_has_role(a.oid, 'USAGE');
1011 GRANT SELECT ON enabled_roles TO PUBLIC;
1019 -- feature not supported
1024 * KEY_COLUMN_USAGE view
1027 CREATE VIEW key_column_usage AS
1028 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1029 CAST(nc_nspname AS sql_identifier) AS constraint_schema,
1030 CAST(conname AS sql_identifier) AS constraint_name,
1031 CAST(current_database() AS sql_identifier) AS table_catalog,
1032 CAST(nr_nspname AS sql_identifier) AS table_schema,
1033 CAST(relname AS sql_identifier) AS table_name,
1034 CAST(a.attname AS sql_identifier) AS column_name,
1035 CAST((ss.x).n AS cardinal_number) AS ordinal_position,
1036 CAST(CASE WHEN contype = 'f' THEN
1037 _pg_index_position(ss.conindid, ss.confkey[(ss.x).n])
1039 END AS cardinal_number)
1040 AS position_in_unique_constraint
1041 FROM pg_attribute a,
1042 (SELECT r.oid AS roid, r.relname, r.relowner,
1043 nc.nspname AS nc_nspname, nr.nspname AS nr_nspname,
1044 c.oid AS coid, c.conname, c.contype, c.conindid,
1045 c.confkey, c.confrelid,
1046 _pg_expandarray(c.conkey) AS x
1047 FROM pg_namespace nr, pg_class r, pg_namespace nc,
1049 WHERE nr.oid = r.relnamespace
1050 AND r.oid = c.conrelid
1051 AND nc.oid = c.connamespace
1052 AND c.contype IN ('p', 'u', 'f')
1054 AND (NOT pg_is_other_temp_schema(nr.oid)) ) AS ss
1055 WHERE ss.roid = a.attrelid
1056 AND a.attnum = (ss.x).x
1057 AND NOT a.attisdropped
1058 AND (pg_has_role(relowner, 'USAGE')
1059 OR has_column_privilege(roid, a.attnum,
1060 'SELECT, INSERT, UPDATE, REFERENCES'));
1062 GRANT SELECT ON key_column_usage TO PUBLIC;
1067 * KEY_PERIOD_USAGE view
1070 -- feature not supported
1075 * METHOD_SPECIFICATION_PARAMETERS view
1078 -- feature not supported
1083 * METHOD_SPECIFICATIONS view
1086 -- feature not supported
1094 CREATE VIEW parameters AS
1095 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
1096 CAST(n_nspname AS sql_identifier) AS specific_schema,
1097 CAST(proname || '_' || CAST(p_oid AS text) AS sql_identifier) AS specific_name,
1098 CAST((ss.x).n AS cardinal_number) AS ordinal_position,
1100 CASE WHEN proargmodes IS NULL THEN 'IN'
1101 WHEN proargmodes[(ss.x).n] = 'i' THEN 'IN'
1102 WHEN proargmodes[(ss.x).n] = 'o' THEN 'OUT'
1103 WHEN proargmodes[(ss.x).n] = 'b' THEN 'INOUT'
1104 WHEN proargmodes[(ss.x).n] = 'v' THEN 'IN'
1105 WHEN proargmodes[(ss.x).n] = 't' THEN 'OUT'
1106 END AS character_data) AS parameter_mode,
1107 CAST('NO' AS yes_or_no) AS is_result,
1108 CAST('NO' AS yes_or_no) AS as_locator,
1109 CAST(NULLIF(proargnames[(ss.x).n], '') AS sql_identifier) AS parameter_name,
1111 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
1112 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
1113 ELSE 'USER-DEFINED' END AS character_data)
1115 CAST(null AS cardinal_number) AS character_maximum_length,
1116 CAST(null AS cardinal_number) AS character_octet_length,
1117 CAST(null AS sql_identifier) AS character_set_catalog,
1118 CAST(null AS sql_identifier) AS character_set_schema,
1119 CAST(null AS sql_identifier) AS character_set_name,
1120 CAST(null AS sql_identifier) AS collation_catalog,
1121 CAST(null AS sql_identifier) AS collation_schema,
1122 CAST(null AS sql_identifier) AS collation_name,
1123 CAST(null AS cardinal_number) AS numeric_precision,
1124 CAST(null AS cardinal_number) AS numeric_precision_radix,
1125 CAST(null AS cardinal_number) AS numeric_scale,
1126 CAST(null AS cardinal_number) AS datetime_precision,
1127 CAST(null AS character_data) AS interval_type,
1128 CAST(null AS cardinal_number) AS interval_precision,
1129 CAST(current_database() AS sql_identifier) AS udt_catalog,
1130 CAST(nt.nspname AS sql_identifier) AS udt_schema,
1131 CAST(t.typname AS sql_identifier) AS udt_name,
1132 CAST(null AS sql_identifier) AS scope_catalog,
1133 CAST(null AS sql_identifier) AS scope_schema,
1134 CAST(null AS sql_identifier) AS scope_name,
1135 CAST(null AS cardinal_number) AS maximum_cardinality,
1136 CAST((ss.x).n AS sql_identifier) AS dtd_identifier,
1138 CASE WHEN pg_has_role(proowner, 'USAGE')
1139 THEN pg_get_function_arg_default(p_oid, (ss.x).n)
1141 AS character_data) AS parameter_default
1143 FROM pg_type t, pg_namespace nt,
1144 (SELECT n.nspname AS n_nspname, p.proname, p.oid AS p_oid, p.proowner,
1145 p.proargnames, p.proargmodes,
1146 _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
1147 FROM pg_namespace n, pg_proc p
1148 WHERE n.oid = p.pronamespace
1149 AND (pg_has_role(p.proowner, 'USAGE') OR
1150 has_function_privilege(p.oid, 'EXECUTE'))) AS ss
1151 WHERE t.oid = (ss.x).x AND t.typnamespace = nt.oid;
1153 GRANT SELECT ON parameters TO PUBLIC;
1161 -- feature not supported
1166 * REFERENCED_TYPES view
1169 -- feature not supported
1174 * REFERENTIAL_CONSTRAINTS view
1177 CREATE VIEW referential_constraints AS
1178 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1179 CAST(ncon.nspname AS sql_identifier) AS constraint_schema,
1180 CAST(con.conname AS sql_identifier) AS constraint_name,
1182 CASE WHEN npkc.nspname IS NULL THEN NULL
1183 ELSE current_database() END
1184 AS sql_identifier) AS unique_constraint_catalog,
1185 CAST(npkc.nspname AS sql_identifier) AS unique_constraint_schema,
1186 CAST(pkc.conname AS sql_identifier) AS unique_constraint_name,
1189 CASE con.confmatchtype WHEN 'f' THEN 'FULL'
1190 WHEN 'p' THEN 'PARTIAL'
1191 WHEN 's' THEN 'NONE' END
1192 AS character_data) AS match_option,
1195 CASE con.confupdtype WHEN 'c' THEN 'CASCADE'
1196 WHEN 'n' THEN 'SET NULL'
1197 WHEN 'd' THEN 'SET DEFAULT'
1198 WHEN 'r' THEN 'RESTRICT'
1199 WHEN 'a' THEN 'NO ACTION' END
1200 AS character_data) AS update_rule,
1203 CASE con.confdeltype WHEN 'c' THEN 'CASCADE'
1204 WHEN 'n' THEN 'SET NULL'
1205 WHEN 'd' THEN 'SET DEFAULT'
1206 WHEN 'r' THEN 'RESTRICT'
1207 WHEN 'a' THEN 'NO ACTION' END
1208 AS character_data) AS delete_rule
1210 FROM (pg_namespace ncon
1211 INNER JOIN pg_constraint con ON ncon.oid = con.connamespace
1212 INNER JOIN pg_class c ON con.conrelid = c.oid AND con.contype = 'f')
1213 LEFT JOIN pg_depend d1 -- find constraint's dependency on an index
1214 ON d1.objid = con.oid AND d1.classid = 'pg_constraint'::regclass
1215 AND d1.refclassid = 'pg_class'::regclass AND d1.refobjsubid = 0
1216 LEFT JOIN pg_depend d2 -- find pkey/unique constraint for that index
1217 ON d2.refclassid = 'pg_constraint'::regclass
1218 AND d2.classid = 'pg_class'::regclass
1219 AND d2.objid = d1.refobjid AND d2.objsubid = 0
1220 AND d2.deptype = 'i'
1221 LEFT JOIN pg_constraint pkc ON pkc.oid = d2.refobjid
1222 AND pkc.contype IN ('p', 'u')
1223 AND pkc.conrelid = con.confrelid
1224 LEFT JOIN pg_namespace npkc ON pkc.connamespace = npkc.oid
1226 WHERE pg_has_role(c.relowner, 'USAGE')
1227 -- SELECT privilege omitted, per SQL standard
1228 OR has_table_privilege(c.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1229 OR has_any_column_privilege(c.oid, 'INSERT, UPDATE, REFERENCES') ;
1231 GRANT SELECT ON referential_constraints TO PUBLIC;
1236 * ROLE_COLUMN_GRANTS view
1239 CREATE VIEW role_column_grants AS
1248 FROM column_privileges
1249 WHERE grantor IN (SELECT role_name FROM enabled_roles)
1250 OR grantee IN (SELECT role_name FROM enabled_roles);
1252 GRANT SELECT ON role_column_grants TO PUBLIC;
1255 -- 5.42 ROLE_ROUTINE_GRANTS view is based on 5.49 ROUTINE_PRIVILEGES and is defined there instead.
1258 -- 5.43 ROLE_TABLE_GRANTS view is based on 5.62 TABLE_PRIVILEGES and is defined there instead.
1263 * ROLE_TABLE_METHOD_GRANTS view
1266 -- feature not supported
1270 -- 5.45 ROLE_USAGE_GRANTS view is based on 5.74 USAGE_PRIVILEGES and is defined there instead.
1273 -- 5.46 ROLE_UDT_GRANTS view is based on 5.73 UDT_PRIVILEGES and is defined there instead.
1278 * ROUTINE_COLUMN_USAGE view
1281 -- not tracked by PostgreSQL
1286 * ROUTINE_PERIOD_USAGE view
1289 -- feature not supported
1294 * ROUTINE_PRIVILEGES view
1297 CREATE VIEW routine_privileges AS
1298 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1299 CAST(grantee.rolname AS sql_identifier) AS grantee,
1300 CAST(current_database() AS sql_identifier) AS specific_catalog,
1301 CAST(n.nspname AS sql_identifier) AS specific_schema,
1302 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1303 CAST(current_database() AS sql_identifier) AS routine_catalog,
1304 CAST(n.nspname AS sql_identifier) AS routine_schema,
1305 CAST(p.proname AS sql_identifier) AS routine_name,
1306 CAST('EXECUTE' AS character_data) AS privilege_type,
1309 -- object owner always has grant options
1310 pg_has_role(grantee.oid, p.proowner, 'USAGE')
1312 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
1315 SELECT oid, proname, proowner, pronamespace, (aclexplode(coalesce(proacl, acldefault('f', proowner)))).* FROM pg_proc
1316 ) p (oid, proname, proowner, pronamespace, grantor, grantee, prtype, grantable),
1318 pg_authid u_grantor,
1320 SELECT oid, rolname FROM pg_authid
1322 SELECT 0::oid, 'PUBLIC'
1323 ) AS grantee (oid, rolname)
1325 WHERE p.pronamespace = n.oid
1326 AND grantee.oid = p.grantee
1327 AND u_grantor.oid = p.grantor
1328 AND p.prtype IN ('EXECUTE')
1329 AND (pg_has_role(u_grantor.oid, 'USAGE')
1330 OR pg_has_role(grantee.oid, 'USAGE')
1331 OR grantee.rolname = 'PUBLIC');
1333 GRANT SELECT ON routine_privileges TO PUBLIC;
1338 * ROLE_ROUTINE_GRANTS view
1341 CREATE VIEW role_routine_grants AS
1352 FROM routine_privileges
1353 WHERE grantor IN (SELECT role_name FROM enabled_roles)
1354 OR grantee IN (SELECT role_name FROM enabled_roles);
1356 GRANT SELECT ON role_routine_grants TO PUBLIC;
1361 * ROUTINE_ROUTINE_USAGE view
1364 -- not tracked by PostgreSQL
1369 * ROUTINE_SEQUENCE_USAGE view
1372 -- not tracked by PostgreSQL
1377 * ROUTINE_TABLE_USAGE view
1380 -- not tracked by PostgreSQL
1388 CREATE VIEW routines AS
1389 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
1390 CAST(n.nspname AS sql_identifier) AS specific_schema,
1391 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1392 CAST(current_database() AS sql_identifier) AS routine_catalog,
1393 CAST(n.nspname AS sql_identifier) AS routine_schema,
1394 CAST(p.proname AS sql_identifier) AS routine_name,
1395 CAST('FUNCTION' AS character_data) AS routine_type,
1396 CAST(null AS sql_identifier) AS module_catalog,
1397 CAST(null AS sql_identifier) AS module_schema,
1398 CAST(null AS sql_identifier) AS module_name,
1399 CAST(null AS sql_identifier) AS udt_catalog,
1400 CAST(null AS sql_identifier) AS udt_schema,
1401 CAST(null AS sql_identifier) AS udt_name,
1404 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
1405 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
1406 ELSE 'USER-DEFINED' END AS character_data)
1408 CAST(null AS cardinal_number) AS character_maximum_length,
1409 CAST(null AS cardinal_number) AS character_octet_length,
1410 CAST(null AS sql_identifier) AS character_set_catalog,
1411 CAST(null AS sql_identifier) AS character_set_schema,
1412 CAST(null AS sql_identifier) AS character_set_name,
1413 CAST(null AS sql_identifier) AS collation_catalog,
1414 CAST(null AS sql_identifier) AS collation_schema,
1415 CAST(null AS sql_identifier) AS collation_name,
1416 CAST(null AS cardinal_number) AS numeric_precision,
1417 CAST(null AS cardinal_number) AS numeric_precision_radix,
1418 CAST(null AS cardinal_number) AS numeric_scale,
1419 CAST(null AS cardinal_number) AS datetime_precision,
1420 CAST(null AS character_data) AS interval_type,
1421 CAST(null AS cardinal_number) AS interval_precision,
1422 CAST(current_database() AS sql_identifier) AS type_udt_catalog,
1423 CAST(nt.nspname AS sql_identifier) AS type_udt_schema,
1424 CAST(t.typname AS sql_identifier) AS type_udt_name,
1425 CAST(null AS sql_identifier) AS scope_catalog,
1426 CAST(null AS sql_identifier) AS scope_schema,
1427 CAST(null AS sql_identifier) AS scope_name,
1428 CAST(null AS cardinal_number) AS maximum_cardinality,
1429 CAST(0 AS sql_identifier) AS dtd_identifier,
1431 CAST(CASE WHEN l.lanname = 'sql' THEN 'SQL' ELSE 'EXTERNAL' END AS character_data)
1434 CASE WHEN pg_has_role(p.proowner, 'USAGE') THEN p.prosrc ELSE null END
1435 AS character_data) AS routine_definition,
1437 CASE WHEN l.lanname = 'c' THEN p.prosrc ELSE null END
1438 AS character_data) AS external_name,
1439 CAST(upper(l.lanname) AS character_data) AS external_language,
1441 CAST('GENERAL' AS character_data) AS parameter_style,
1442 CAST(CASE WHEN p.provolatile = 'i' THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_deterministic,
1443 CAST('MODIFIES' AS character_data) AS sql_data_access,
1444 CAST(CASE WHEN p.proisstrict THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_null_call,
1445 CAST(null AS character_data) AS sql_path,
1446 CAST('YES' AS yes_or_no) AS schema_level_routine,
1447 CAST(0 AS cardinal_number) AS max_dynamic_result_sets,
1448 CAST(null AS yes_or_no) AS is_user_defined_cast,
1449 CAST(null AS yes_or_no) AS is_implicitly_invocable,
1450 CAST(CASE WHEN p.prosecdef THEN 'DEFINER' ELSE 'INVOKER' END AS character_data) AS security_type,
1451 CAST(null AS sql_identifier) AS to_sql_specific_catalog,
1452 CAST(null AS sql_identifier) AS to_sql_specific_schema,
1453 CAST(null AS sql_identifier) AS to_sql_specific_name,
1454 CAST('NO' AS yes_or_no) AS as_locator,
1455 CAST(null AS time_stamp) AS created,
1456 CAST(null AS time_stamp) AS last_altered,
1457 CAST(null AS yes_or_no) AS new_savepoint_level,
1458 CAST('NO' AS yes_or_no) AS is_udt_dependent,
1460 CAST(null AS character_data) AS result_cast_from_data_type,
1461 CAST(null AS yes_or_no) AS result_cast_as_locator,
1462 CAST(null AS cardinal_number) AS result_cast_char_max_length,
1463 CAST(null AS cardinal_number) AS result_cast_char_octet_length,
1464 CAST(null AS sql_identifier) AS result_cast_char_set_catalog,
1465 CAST(null AS sql_identifier) AS result_cast_char_set_schema,
1466 CAST(null AS sql_identifier) AS result_cast_character_set_name,
1467 CAST(null AS sql_identifier) AS result_cast_collation_catalog,
1468 CAST(null AS sql_identifier) AS result_cast_collation_schema,
1469 CAST(null AS sql_identifier) AS result_cast_collation_name,
1470 CAST(null AS cardinal_number) AS result_cast_numeric_precision,
1471 CAST(null AS cardinal_number) AS result_cast_numeric_precision_radix,
1472 CAST(null AS cardinal_number) AS result_cast_numeric_scale,
1473 CAST(null AS cardinal_number) AS result_cast_datetime_precision,
1474 CAST(null AS character_data) AS result_cast_interval_type,
1475 CAST(null AS cardinal_number) AS result_cast_interval_precision,
1476 CAST(null AS sql_identifier) AS result_cast_type_udt_catalog,
1477 CAST(null AS sql_identifier) AS result_cast_type_udt_schema,
1478 CAST(null AS sql_identifier) AS result_cast_type_udt_name,
1479 CAST(null AS sql_identifier) AS result_cast_scope_catalog,
1480 CAST(null AS sql_identifier) AS result_cast_scope_schema,
1481 CAST(null AS sql_identifier) AS result_cast_scope_name,
1482 CAST(null AS cardinal_number) AS result_cast_maximum_cardinality,
1483 CAST(null AS sql_identifier) AS result_cast_dtd_identifier
1485 FROM pg_namespace n, pg_proc p, pg_language l,
1486 pg_type t, pg_namespace nt
1488 WHERE n.oid = p.pronamespace AND p.prolang = l.oid
1489 AND p.prorettype = t.oid AND t.typnamespace = nt.oid
1490 AND (pg_has_role(p.proowner, 'USAGE')
1491 OR has_function_privilege(p.oid, 'EXECUTE'));
1493 GRANT SELECT ON routines TO PUBLIC;
1501 CREATE VIEW schemata AS
1502 SELECT CAST(current_database() AS sql_identifier) AS catalog_name,
1503 CAST(n.nspname AS sql_identifier) AS schema_name,
1504 CAST(u.rolname AS sql_identifier) AS schema_owner,
1505 CAST(null AS sql_identifier) AS default_character_set_catalog,
1506 CAST(null AS sql_identifier) AS default_character_set_schema,
1507 CAST(null AS sql_identifier) AS default_character_set_name,
1508 CAST(null AS character_data) AS sql_path
1509 FROM pg_namespace n, pg_authid u
1510 WHERE n.nspowner = u.oid
1511 AND (pg_has_role(n.nspowner, 'USAGE')
1512 OR has_schema_privilege(n.oid, 'CREATE, USAGE'));
1514 GRANT SELECT ON schemata TO PUBLIC;
1522 CREATE VIEW sequences AS
1523 SELECT CAST(current_database() AS sql_identifier) AS sequence_catalog,
1524 CAST(nc.nspname AS sql_identifier) AS sequence_schema,
1525 CAST(c.relname AS sql_identifier) AS sequence_name,
1526 CAST('bigint' AS character_data) AS data_type,
1527 CAST(64 AS cardinal_number) AS numeric_precision,
1528 CAST(2 AS cardinal_number) AS numeric_precision_radix,
1529 CAST(0 AS cardinal_number) AS numeric_scale,
1530 CAST(p.start_value AS character_data) AS start_value,
1531 CAST(p.minimum_value AS character_data) AS minimum_value,
1532 CAST(p.maximum_value AS character_data) AS maximum_value,
1533 CAST(p.increment AS character_data) AS increment,
1534 CAST(CASE WHEN p.cycle_option THEN 'YES' ELSE 'NO' END AS yes_or_no) AS cycle_option
1535 FROM pg_namespace nc, pg_class c, LATERAL pg_sequence_parameters(c.oid) p
1536 WHERE c.relnamespace = nc.oid
1538 AND (NOT pg_is_other_temp_schema(nc.oid))
1539 AND (pg_has_role(c.relowner, 'USAGE')
1540 OR has_sequence_privilege(c.oid, 'SELECT, UPDATE, USAGE') );
1542 GRANT SELECT ON sequences TO PUBLIC;
1547 * SQL_FEATURES table
1550 CREATE TABLE sql_features (
1551 feature_id character_data,
1552 feature_name character_data,
1553 sub_feature_id character_data,
1554 sub_feature_name character_data,
1555 is_supported yes_or_no,
1556 is_verified_by character_data,
1557 comments character_data
1560 -- Will be filled with external data by initdb.
1562 GRANT SELECT ON sql_features TO PUBLIC;
1567 * SQL_IMPLEMENTATION_INFO table
1570 -- Note: Implementation information items are defined in ISO/IEC 9075-3:2008,
1573 CREATE TABLE sql_implementation_info (
1574 implementation_info_id character_data,
1575 implementation_info_name character_data,
1576 integer_value cardinal_number,
1577 character_value character_data,
1578 comments character_data
1581 INSERT INTO sql_implementation_info VALUES ('10003', 'CATALOG NAME', NULL, 'Y', NULL);
1582 INSERT INTO sql_implementation_info VALUES ('10004', 'COLLATING SEQUENCE', NULL, (SELECT default_collate_name FROM character_sets), NULL);
1583 INSERT INTO sql_implementation_info VALUES ('23', 'CURSOR COMMIT BEHAVIOR', 1, NULL, 'close cursors and retain prepared statements');
1584 INSERT INTO sql_implementation_info VALUES ('2', 'DATA SOURCE NAME', NULL, '', NULL);
1585 INSERT INTO sql_implementation_info VALUES ('17', 'DBMS NAME', NULL, (select trim(trailing ' ' from substring(version() from '^[^0-9]*'))), NULL);
1586 INSERT INTO sql_implementation_info VALUES ('18', 'DBMS VERSION', NULL, '???', NULL); -- filled by initdb
1587 INSERT INTO sql_implementation_info VALUES ('26', 'DEFAULT TRANSACTION ISOLATION', 2, NULL, 'READ COMMITTED; user-settable');
1588 INSERT INTO sql_implementation_info VALUES ('28', 'IDENTIFIER CASE', 3, NULL, 'stored in mixed case - case sensitive');
1589 INSERT INTO sql_implementation_info VALUES ('85', 'NULL COLLATION', 0, NULL, 'nulls higher than non-nulls');
1590 INSERT INTO sql_implementation_info VALUES ('13', 'SERVER NAME', NULL, '', NULL);
1591 INSERT INTO sql_implementation_info VALUES ('94', 'SPECIAL CHARACTERS', NULL, '', 'all non-ASCII characters allowed');
1592 INSERT INTO sql_implementation_info VALUES ('46', 'TRANSACTION CAPABLE', 2, NULL, 'both DML and DDL');
1594 GRANT SELECT ON sql_implementation_info TO PUBLIC;
1598 * SQL_LANGUAGES table
1599 * apparently removed in SQL:2008
1602 CREATE TABLE sql_languages (
1603 sql_language_source character_data,
1604 sql_language_year character_data,
1605 sql_language_conformance character_data,
1606 sql_language_integrity character_data,
1607 sql_language_implementation character_data,
1608 sql_language_binding_style character_data,
1609 sql_language_programming_language character_data
1612 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'DIRECT', NULL);
1613 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
1614 INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'DIRECT', NULL);
1615 INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
1617 GRANT SELECT ON sql_languages TO PUBLIC;
1621 * SQL_PACKAGES table
1622 * removed in SQL:2011
1625 CREATE TABLE sql_packages (
1626 feature_id character_data,
1627 feature_name character_data,
1628 is_supported yes_or_no,
1629 is_verified_by character_data,
1630 comments character_data
1633 INSERT INTO sql_packages VALUES ('PKG000', 'Core', 'NO', NULL, '');
1634 INSERT INTO sql_packages VALUES ('PKG001', 'Enhanced datetime facilities', 'YES', NULL, '');
1635 INSERT INTO sql_packages VALUES ('PKG002', 'Enhanced integrity management', 'NO', NULL, '');
1636 INSERT INTO sql_packages VALUES ('PKG003', 'OLAP facilities', 'NO', NULL, '');
1637 INSERT INTO sql_packages VALUES ('PKG004', 'PSM', 'NO', NULL, 'PL/pgSQL is similar.');
1638 INSERT INTO sql_packages VALUES ('PKG005', 'CLI', 'NO', NULL, 'ODBC is similar.');
1639 INSERT INTO sql_packages VALUES ('PKG006', 'Basic object support', 'NO', NULL, '');
1640 INSERT INTO sql_packages VALUES ('PKG007', 'Enhanced object support', 'NO', NULL, '');
1641 INSERT INTO sql_packages VALUES ('PKG008', 'Active database', 'NO', NULL, '');
1642 INSERT INTO sql_packages VALUES ('PKG010', 'OLAP', 'NO', NULL, 'NO');
1644 GRANT SELECT ON sql_packages TO PUBLIC;
1652 CREATE TABLE sql_parts (
1653 feature_id character_data,
1654 feature_name character_data,
1655 is_supported yes_or_no,
1656 is_verified_by character_data,
1657 comments character_data
1660 INSERT INTO sql_parts VALUES ('1', 'Framework (SQL/Framework)', 'NO', NULL, '');
1661 INSERT INTO sql_parts VALUES ('2', 'Foundation (SQL/Foundation)', 'NO', NULL, '');
1662 INSERT INTO sql_parts VALUES ('3', 'Call-Level Interface (SQL/CLI)', 'NO', NULL, '');
1663 INSERT INTO sql_parts VALUES ('4', 'Persistent Stored Modules (SQL/PSM)', 'NO', NULL, '');
1664 INSERT INTO sql_parts VALUES ('9', 'Management of External Data (SQL/MED)', 'NO', NULL, '');
1665 INSERT INTO sql_parts VALUES ('10', 'Object Language Bindings (SQL/OLB)', 'NO', NULL, '');
1666 INSERT INTO sql_parts VALUES ('11', 'Information and Definition Schema (SQL/Schemata)', 'NO', NULL, '');
1667 INSERT INTO sql_parts VALUES ('13', 'Routines and Types Using the Java Programming Language (SQL/JRT)', 'NO', NULL, '');
1668 INSERT INTO sql_parts VALUES ('14', 'XML-Related Specifications (SQL/XML)', 'YES', NULL, '');
1676 -- Note: Sizing items are defined in ISO/IEC 9075-3:2008, clause 9.2.
1678 CREATE TABLE sql_sizing (
1679 sizing_id cardinal_number,
1680 sizing_name character_data,
1681 supported_value cardinal_number,
1682 comments character_data
1685 INSERT INTO sql_sizing VALUES (34, 'MAXIMUM CATALOG NAME LENGTH', 63, NULL);
1686 INSERT INTO sql_sizing VALUES (30, 'MAXIMUM COLUMN NAME LENGTH', 63, NULL);
1687 INSERT INTO sql_sizing VALUES (97, 'MAXIMUM COLUMNS IN GROUP BY', 0, NULL);
1688 INSERT INTO sql_sizing VALUES (99, 'MAXIMUM COLUMNS IN ORDER BY', 0, NULL);
1689 INSERT INTO sql_sizing VALUES (100, 'MAXIMUM COLUMNS IN SELECT', 1664, NULL); -- match MaxTupleAttributeNumber
1690 INSERT INTO sql_sizing VALUES (101, 'MAXIMUM COLUMNS IN TABLE', 1600, NULL); -- match MaxHeapAttributeNumber
1691 INSERT INTO sql_sizing VALUES (1, 'MAXIMUM CONCURRENT ACTIVITIES', 0, NULL);
1692 INSERT INTO sql_sizing VALUES (31, 'MAXIMUM CURSOR NAME LENGTH', 63, NULL);
1693 INSERT INTO sql_sizing VALUES (0, 'MAXIMUM DRIVER CONNECTIONS', NULL, NULL);
1694 INSERT INTO sql_sizing VALUES (10005, 'MAXIMUM IDENTIFIER LENGTH', 63, NULL);
1695 INSERT INTO sql_sizing VALUES (32, 'MAXIMUM SCHEMA NAME LENGTH', 63, NULL);
1696 INSERT INTO sql_sizing VALUES (20000, 'MAXIMUM STATEMENT OCTETS', 0, NULL);
1697 INSERT INTO sql_sizing VALUES (20001, 'MAXIMUM STATEMENT OCTETS DATA', 0, NULL);
1698 INSERT INTO sql_sizing VALUES (20002, 'MAXIMUM STATEMENT OCTETS SCHEMA', 0, NULL);
1699 INSERT INTO sql_sizing VALUES (35, 'MAXIMUM TABLE NAME LENGTH', 63, NULL);
1700 INSERT INTO sql_sizing VALUES (106, 'MAXIMUM TABLES IN SELECT', 0, NULL);
1701 INSERT INTO sql_sizing VALUES (107, 'MAXIMUM USER NAME LENGTH', 63, NULL);
1702 INSERT INTO sql_sizing VALUES (25000, 'MAXIMUM CURRENT DEFAULT TRANSFORM GROUP LENGTH', NULL, NULL);
1703 INSERT INTO sql_sizing VALUES (25001, 'MAXIMUM CURRENT TRANSFORM GROUP LENGTH', NULL, NULL);
1704 INSERT INTO sql_sizing VALUES (25002, 'MAXIMUM CURRENT PATH LENGTH', 0, NULL);
1705 INSERT INTO sql_sizing VALUES (25003, 'MAXIMUM CURRENT ROLE LENGTH', NULL, NULL);
1706 INSERT INTO sql_sizing VALUES (25004, 'MAXIMUM SESSION USER LENGTH', 63, NULL);
1707 INSERT INTO sql_sizing VALUES (25005, 'MAXIMUM SYSTEM USER LENGTH', 63, NULL);
1710 SET supported_value = (SELECT typlen-1 FROM pg_catalog.pg_type WHERE typname = 'name'),
1711 comments = 'Might be less, depending on character set.'
1712 WHERE supported_value = 63;
1714 GRANT SELECT ON sql_sizing TO PUBLIC;
1718 * SQL_SIZING_PROFILES table
1719 * removed in SQL:2011
1722 -- The data in this table are defined by various profiles of SQL.
1723 -- Since we don't have any information about such profiles, we provide
1726 CREATE TABLE sql_sizing_profiles (
1727 sizing_id cardinal_number,
1728 sizing_name character_data,
1729 profile_id character_data,
1730 required_value cardinal_number,
1731 comments character_data
1734 GRANT SELECT ON sql_sizing_profiles TO PUBLIC;
1739 * TABLE_CONSTRAINTS view
1742 CREATE VIEW table_constraints AS
1743 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1744 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
1745 CAST(c.conname AS sql_identifier) AS constraint_name,
1746 CAST(current_database() AS sql_identifier) AS table_catalog,
1747 CAST(nr.nspname AS sql_identifier) AS table_schema,
1748 CAST(r.relname AS sql_identifier) AS table_name,
1750 CASE c.contype WHEN 'c' THEN 'CHECK'
1751 WHEN 'f' THEN 'FOREIGN KEY'
1752 WHEN 'p' THEN 'PRIMARY KEY'
1753 WHEN 'u' THEN 'UNIQUE' END
1754 AS character_data) AS constraint_type,
1755 CAST(CASE WHEN c.condeferrable THEN 'YES' ELSE 'NO' END AS yes_or_no)
1757 CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS yes_or_no)
1758 AS initially_deferred
1760 FROM pg_namespace nc,
1765 WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace
1766 AND c.conrelid = r.oid
1767 AND c.contype NOT IN ('t', 'x') -- ignore nonstandard constraints
1769 AND (NOT pg_is_other_temp_schema(nr.oid))
1770 AND (pg_has_role(r.relowner, 'USAGE')
1771 -- SELECT privilege omitted, per SQL standard
1772 OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1773 OR has_any_column_privilege(r.oid, 'INSERT, UPDATE, REFERENCES') )
1777 -- not-null constraints
1779 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1780 CAST(nr.nspname AS sql_identifier) AS constraint_schema,
1781 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
1782 CAST(current_database() AS sql_identifier) AS table_catalog,
1783 CAST(nr.nspname AS sql_identifier) AS table_schema,
1784 CAST(r.relname AS sql_identifier) AS table_name,
1785 CAST('CHECK' AS character_data) AS constraint_type,
1786 CAST('NO' AS yes_or_no) AS is_deferrable,
1787 CAST('NO' AS yes_or_no) AS initially_deferred
1789 FROM pg_namespace nr,
1793 WHERE nr.oid = r.relnamespace
1794 AND r.oid = a.attrelid
1797 AND NOT a.attisdropped
1799 AND (NOT pg_is_other_temp_schema(nr.oid))
1800 AND (pg_has_role(r.relowner, 'USAGE')
1801 -- SELECT privilege omitted, per SQL standard
1802 OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1803 OR has_any_column_privilege(r.oid, 'INSERT, UPDATE, REFERENCES') );
1805 GRANT SELECT ON table_constraints TO PUBLIC;
1810 * TABLE_METHOD_PRIVILEGES view
1813 -- feature not supported
1818 * TABLE_PRIVILEGES view
1821 CREATE VIEW table_privileges AS
1822 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1823 CAST(grantee.rolname AS sql_identifier) AS grantee,
1824 CAST(current_database() AS sql_identifier) AS table_catalog,
1825 CAST(nc.nspname AS sql_identifier) AS table_schema,
1826 CAST(c.relname AS sql_identifier) AS table_name,
1827 CAST(c.prtype AS character_data) AS privilege_type,
1830 -- object owner always has grant options
1831 pg_has_role(grantee.oid, c.relowner, 'USAGE')
1833 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable,
1834 CAST(CASE WHEN c.prtype = 'SELECT' THEN 'YES' ELSE 'NO' END AS yes_or_no) AS with_hierarchy
1837 SELECT oid, relname, relnamespace, relkind, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).* FROM pg_class
1838 ) AS c (oid, relname, relnamespace, relkind, relowner, grantor, grantee, prtype, grantable),
1840 pg_authid u_grantor,
1842 SELECT oid, rolname FROM pg_authid
1844 SELECT 0::oid, 'PUBLIC'
1845 ) AS grantee (oid, rolname)
1847 WHERE c.relnamespace = nc.oid
1848 AND c.relkind IN ('r', 'v')
1849 AND c.grantee = grantee.oid
1850 AND c.grantor = u_grantor.oid
1851 AND c.prtype IN ('INSERT', 'SELECT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER')
1852 AND (pg_has_role(u_grantor.oid, 'USAGE')
1853 OR pg_has_role(grantee.oid, 'USAGE')
1854 OR grantee.rolname = 'PUBLIC');
1856 GRANT SELECT ON table_privileges TO PUBLIC;
1861 * ROLE_TABLE_GRANTS view
1864 CREATE VIEW role_table_grants AS
1873 FROM table_privileges
1874 WHERE grantor IN (SELECT role_name FROM enabled_roles)
1875 OR grantee IN (SELECT role_name FROM enabled_roles);
1877 GRANT SELECT ON role_table_grants TO PUBLIC;
1885 CREATE VIEW tables AS
1886 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
1887 CAST(nc.nspname AS sql_identifier) AS table_schema,
1888 CAST(c.relname AS sql_identifier) AS table_name,
1891 CASE WHEN nc.oid = pg_my_temp_schema() THEN 'LOCAL TEMPORARY'
1892 WHEN c.relkind = 'r' THEN 'BASE TABLE'
1893 WHEN c.relkind = 'v' THEN 'VIEW'
1894 WHEN c.relkind = 'f' THEN 'FOREIGN TABLE'
1896 AS character_data) AS table_type,
1898 CAST(null AS sql_identifier) AS self_referencing_column_name,
1899 CAST(null AS character_data) AS reference_generation,
1901 CAST(CASE WHEN t.typname IS NOT NULL THEN current_database() ELSE null END AS sql_identifier) AS user_defined_type_catalog,
1902 CAST(nt.nspname AS sql_identifier) AS user_defined_type_schema,
1903 CAST(t.typname AS sql_identifier) AS user_defined_type_name,
1905 CAST(CASE WHEN c.relkind = 'r' OR
1906 (c.relkind IN ('v', 'f') AND
1908 pg_relation_is_updatable(c.oid, false) & 8 = 8)
1909 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_insertable_into,
1911 CAST(CASE WHEN t.typname IS NOT NULL THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_typed,
1912 CAST(null AS character_data) AS commit_action
1914 FROM pg_namespace nc JOIN pg_class c ON (nc.oid = c.relnamespace)
1915 LEFT JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON (c.reloftype = t.oid)
1917 WHERE c.relkind IN ('r', 'v', 'f')
1918 AND (NOT pg_is_other_temp_schema(nc.oid))
1919 AND (pg_has_role(c.relowner, 'USAGE')
1920 OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1921 OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES') );
1923 GRANT SELECT ON tables TO PUBLIC;
1931 -- feature not supported
1939 -- feature not supported
1944 * TRIGGERED_UPDATE_COLUMNS view
1947 CREATE VIEW triggered_update_columns AS
1948 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1949 CAST(n.nspname AS sql_identifier) AS trigger_schema,
1950 CAST(t.tgname AS sql_identifier) AS trigger_name,
1951 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1952 CAST(n.nspname AS sql_identifier) AS event_object_schema,
1953 CAST(c.relname AS sql_identifier) AS event_object_table,
1954 CAST(a.attname AS sql_identifier) AS event_object_column
1956 FROM pg_namespace n, pg_class c, pg_trigger t,
1957 (SELECT tgoid, (ta0.tgat).x AS tgattnum, (ta0.tgat).n AS tgattpos
1958 FROM (SELECT oid AS tgoid, information_schema._pg_expandarray(tgattr) AS tgat FROM pg_trigger) AS ta0) AS ta,
1961 WHERE n.oid = c.relnamespace
1962 AND c.oid = t.tgrelid
1963 AND t.oid = ta.tgoid
1964 AND (a.attrelid, a.attnum) = (t.tgrelid, ta.tgattnum)
1965 AND NOT t.tgisinternal
1966 AND (NOT pg_is_other_temp_schema(n.oid))
1967 AND (pg_has_role(c.relowner, 'USAGE')
1968 -- SELECT privilege omitted, per SQL standard
1969 OR has_column_privilege(c.oid, a.attnum, 'INSERT, UPDATE, REFERENCES') );
1971 GRANT SELECT ON triggered_update_columns TO PUBLIC;
1976 * TRIGGER_COLUMN_USAGE view
1979 -- not tracked by PostgreSQL
1984 * TRIGGER_PERIOD_USAGE view
1987 -- feature not supported
1992 * TRIGGER_ROUTINE_USAGE view
1995 -- not tracked by PostgreSQL
2000 * TRIGGER_SEQUENCE_USAGE view
2003 -- not tracked by PostgreSQL
2008 * TRIGGER_TABLE_USAGE view
2011 -- not tracked by PostgreSQL
2019 CREATE VIEW triggers AS
2020 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
2021 CAST(n.nspname AS sql_identifier) AS trigger_schema,
2022 CAST(t.tgname AS sql_identifier) AS trigger_name,
2023 CAST(em.text AS character_data) AS event_manipulation,
2024 CAST(current_database() AS sql_identifier) AS event_object_catalog,
2025 CAST(n.nspname AS sql_identifier) AS event_object_schema,
2026 CAST(c.relname AS sql_identifier) AS event_object_table,
2027 CAST(null AS cardinal_number) AS action_order,
2028 -- XXX strange hacks follow
2030 CASE WHEN pg_has_role(c.relowner, 'USAGE')
2031 THEN (SELECT m[1] FROM regexp_matches(pg_get_triggerdef(t.oid), E'.{35,} WHEN \\((.+)\\) EXECUTE PROCEDURE') AS rm(m) LIMIT 1)
2033 AS character_data) AS action_condition,
2035 substring(pg_get_triggerdef(t.oid) from
2036 position('EXECUTE PROCEDURE' in substring(pg_get_triggerdef(t.oid) from 48)) + 47)
2037 AS character_data) AS action_statement,
2039 -- hard-wired reference to TRIGGER_TYPE_ROW
2040 CASE t.tgtype & 1 WHEN 1 THEN 'ROW' ELSE 'STATEMENT' END
2041 AS character_data) AS action_orientation,
2043 -- hard-wired refs to TRIGGER_TYPE_BEFORE, TRIGGER_TYPE_INSTEAD
2044 CASE t.tgtype & 66 WHEN 2 THEN 'BEFORE' WHEN 64 THEN 'INSTEAD OF' ELSE 'AFTER' END
2045 AS character_data) AS action_timing,
2046 CAST(null AS sql_identifier) AS action_reference_old_table,
2047 CAST(null AS sql_identifier) AS action_reference_new_table,
2048 CAST(null AS sql_identifier) AS action_reference_old_row,
2049 CAST(null AS sql_identifier) AS action_reference_new_row,
2050 CAST(null AS time_stamp) AS created
2052 FROM pg_namespace n, pg_class c, pg_trigger t,
2053 -- hard-wired refs to TRIGGER_TYPE_INSERT, TRIGGER_TYPE_DELETE,
2054 -- TRIGGER_TYPE_UPDATE; we intentionally omit TRIGGER_TYPE_TRUNCATE
2055 (VALUES (4, 'INSERT'),
2057 (16, 'UPDATE')) AS em (num, text)
2059 WHERE n.oid = c.relnamespace
2060 AND c.oid = t.tgrelid
2061 AND t.tgtype & em.num <> 0
2062 AND NOT t.tgisinternal
2063 AND (NOT pg_is_other_temp_schema(n.oid))
2064 AND (pg_has_role(c.relowner, 'USAGE')
2065 -- SELECT privilege omitted, per SQL standard
2066 OR has_table_privilege(c.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
2067 OR has_any_column_privilege(c.oid, 'INSERT, UPDATE, REFERENCES') );
2069 GRANT SELECT ON triggers TO PUBLIC;
2074 * UDT_PRIVILEGES view
2077 CREATE VIEW udt_privileges AS
2078 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2079 CAST(grantee.rolname AS sql_identifier) AS grantee,
2080 CAST(current_database() AS sql_identifier) AS udt_catalog,
2081 CAST(n.nspname AS sql_identifier) AS udt_schema,
2082 CAST(t.typname AS sql_identifier) AS udt_name,
2083 CAST('TYPE USAGE' AS character_data) AS privilege_type, -- sic
2086 -- object owner always has grant options
2087 pg_has_role(grantee.oid, t.typowner, 'USAGE')
2089 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2092 SELECT oid, typname, typnamespace, typtype, typowner, (aclexplode(coalesce(typacl, acldefault('T', typowner)))).* FROM pg_type
2093 ) AS t (oid, typname, typnamespace, typtype, typowner, grantor, grantee, prtype, grantable),
2095 pg_authid u_grantor,
2097 SELECT oid, rolname FROM pg_authid
2099 SELECT 0::oid, 'PUBLIC'
2100 ) AS grantee (oid, rolname)
2102 WHERE t.typnamespace = n.oid
2104 AND t.grantee = grantee.oid
2105 AND t.grantor = u_grantor.oid
2106 AND t.prtype IN ('USAGE')
2107 AND (pg_has_role(u_grantor.oid, 'USAGE')
2108 OR pg_has_role(grantee.oid, 'USAGE')
2109 OR grantee.rolname = 'PUBLIC');
2111 GRANT SELECT ON udt_privileges TO PUBLIC;
2116 * ROLE_UDT_GRANTS view
2119 CREATE VIEW role_udt_grants AS
2128 WHERE grantor IN (SELECT role_name FROM enabled_roles)
2129 OR grantee IN (SELECT role_name FROM enabled_roles);
2131 GRANT SELECT ON role_udt_grants TO PUBLIC;
2136 * USAGE_PRIVILEGES view
2139 CREATE VIEW usage_privileges AS
2142 -- Collations have no real privileges, so we represent all collations with implicit usage privilege here.
2143 SELECT CAST(u.rolname AS sql_identifier) AS grantor,
2144 CAST('PUBLIC' AS sql_identifier) AS grantee,
2145 CAST(current_database() AS sql_identifier) AS object_catalog,
2146 CAST(n.nspname AS sql_identifier) AS object_schema,
2147 CAST(c.collname AS sql_identifier) AS object_name,
2148 CAST('COLLATION' AS character_data) AS object_type,
2149 CAST('USAGE' AS character_data) AS privilege_type,
2150 CAST('NO' AS yes_or_no) AS is_grantable
2156 WHERE u.oid = c.collowner
2157 AND c.collnamespace = n.oid
2158 AND collencoding IN (-1, (SELECT encoding FROM pg_database WHERE datname = current_database()))
2163 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2164 CAST(grantee.rolname AS sql_identifier) AS grantee,
2165 CAST(current_database() AS sql_identifier) AS object_catalog,
2166 CAST(n.nspname AS sql_identifier) AS object_schema,
2167 CAST(t.typname AS sql_identifier) AS object_name,
2168 CAST('DOMAIN' AS character_data) AS object_type,
2169 CAST('USAGE' AS character_data) AS privilege_type,
2172 -- object owner always has grant options
2173 pg_has_role(grantee.oid, t.typowner, 'USAGE')
2175 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2178 SELECT oid, typname, typnamespace, typtype, typowner, (aclexplode(coalesce(typacl, acldefault('T', typowner)))).* FROM pg_type
2179 ) AS t (oid, typname, typnamespace, typtype, typowner, grantor, grantee, prtype, grantable),
2181 pg_authid u_grantor,
2183 SELECT oid, rolname FROM pg_authid
2185 SELECT 0::oid, 'PUBLIC'
2186 ) AS grantee (oid, rolname)
2188 WHERE t.typnamespace = n.oid
2190 AND t.grantee = grantee.oid
2191 AND t.grantor = u_grantor.oid
2192 AND t.prtype IN ('USAGE')
2193 AND (pg_has_role(u_grantor.oid, 'USAGE')
2194 OR pg_has_role(grantee.oid, 'USAGE')
2195 OR grantee.rolname = 'PUBLIC')
2199 /* foreign-data wrappers */
2200 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2201 CAST(grantee.rolname AS sql_identifier) AS grantee,
2202 CAST(current_database() AS sql_identifier) AS object_catalog,
2203 CAST('' AS sql_identifier) AS object_schema,
2204 CAST(fdw.fdwname AS sql_identifier) AS object_name,
2205 CAST('FOREIGN DATA WRAPPER' AS character_data) AS object_type,
2206 CAST('USAGE' AS character_data) AS privilege_type,
2209 -- object owner always has grant options
2210 pg_has_role(grantee.oid, fdw.fdwowner, 'USAGE')
2212 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2215 SELECT fdwname, fdwowner, (aclexplode(coalesce(fdwacl, acldefault('F', fdwowner)))).* FROM pg_foreign_data_wrapper
2216 ) AS fdw (fdwname, fdwowner, grantor, grantee, prtype, grantable),
2217 pg_authid u_grantor,
2219 SELECT oid, rolname FROM pg_authid
2221 SELECT 0::oid, 'PUBLIC'
2222 ) AS grantee (oid, rolname)
2224 WHERE u_grantor.oid = fdw.grantor
2225 AND grantee.oid = fdw.grantee
2226 AND fdw.prtype IN ('USAGE')
2227 AND (pg_has_role(u_grantor.oid, 'USAGE')
2228 OR pg_has_role(grantee.oid, 'USAGE')
2229 OR grantee.rolname = 'PUBLIC')
2233 /* foreign servers */
2234 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2235 CAST(grantee.rolname AS sql_identifier) AS grantee,
2236 CAST(current_database() AS sql_identifier) AS object_catalog,
2237 CAST('' AS sql_identifier) AS object_schema,
2238 CAST(srv.srvname AS sql_identifier) AS object_name,
2239 CAST('FOREIGN SERVER' AS character_data) AS object_type,
2240 CAST('USAGE' AS character_data) AS privilege_type,
2243 -- object owner always has grant options
2244 pg_has_role(grantee.oid, srv.srvowner, 'USAGE')
2246 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2249 SELECT srvname, srvowner, (aclexplode(coalesce(srvacl, acldefault('S', srvowner)))).* FROM pg_foreign_server
2250 ) AS srv (srvname, srvowner, grantor, grantee, prtype, grantable),
2251 pg_authid u_grantor,
2253 SELECT oid, rolname FROM pg_authid
2255 SELECT 0::oid, 'PUBLIC'
2256 ) AS grantee (oid, rolname)
2258 WHERE u_grantor.oid = srv.grantor
2259 AND grantee.oid = srv.grantee
2260 AND srv.prtype IN ('USAGE')
2261 AND (pg_has_role(u_grantor.oid, 'USAGE')
2262 OR pg_has_role(grantee.oid, 'USAGE')
2263 OR grantee.rolname = 'PUBLIC')
2268 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2269 CAST(grantee.rolname AS sql_identifier) AS grantee,
2270 CAST(current_database() AS sql_identifier) AS object_catalog,
2271 CAST(n.nspname AS sql_identifier) AS object_schema,
2272 CAST(c.relname AS sql_identifier) AS object_name,
2273 CAST('SEQUENCE' AS character_data) AS object_type,
2274 CAST('USAGE' AS character_data) AS privilege_type,
2277 -- object owner always has grant options
2278 pg_has_role(grantee.oid, c.relowner, 'USAGE')
2280 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2283 SELECT oid, relname, relnamespace, relkind, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).* FROM pg_class
2284 ) AS c (oid, relname, relnamespace, relkind, relowner, grantor, grantee, prtype, grantable),
2286 pg_authid u_grantor,
2288 SELECT oid, rolname FROM pg_authid
2290 SELECT 0::oid, 'PUBLIC'
2291 ) AS grantee (oid, rolname)
2293 WHERE c.relnamespace = n.oid
2295 AND c.grantee = grantee.oid
2296 AND c.grantor = u_grantor.oid
2297 AND c.prtype IN ('USAGE')
2298 AND (pg_has_role(u_grantor.oid, 'USAGE')
2299 OR pg_has_role(grantee.oid, 'USAGE')
2300 OR grantee.rolname = 'PUBLIC');
2302 GRANT SELECT ON usage_privileges TO PUBLIC;
2307 * ROLE_USAGE_GRANTS view
2310 CREATE VIEW role_usage_grants AS
2319 FROM usage_privileges
2320 WHERE grantor IN (SELECT role_name FROM enabled_roles)
2321 OR grantee IN (SELECT role_name FROM enabled_roles);
2323 GRANT SELECT ON role_usage_grants TO PUBLIC;
2328 * USER_DEFINED_TYPES view
2331 CREATE VIEW user_defined_types AS
2332 SELECT CAST(current_database() AS sql_identifier) AS user_defined_type_catalog,
2333 CAST(n.nspname AS sql_identifier) AS user_defined_type_schema,
2334 CAST(c.relname AS sql_identifier) AS user_defined_type_name,
2335 CAST('STRUCTURED' AS character_data) AS user_defined_type_category,
2336 CAST('YES' AS yes_or_no) AS is_instantiable,
2337 CAST(null AS yes_or_no) AS is_final,
2338 CAST(null AS character_data) AS ordering_form,
2339 CAST(null AS character_data) AS ordering_category,
2340 CAST(null AS sql_identifier) AS ordering_routine_catalog,
2341 CAST(null AS sql_identifier) AS ordering_routine_schema,
2342 CAST(null AS sql_identifier) AS ordering_routine_name,
2343 CAST(null AS character_data) AS reference_type,
2344 CAST(null AS character_data) AS data_type,
2345 CAST(null AS cardinal_number) AS character_maximum_length,
2346 CAST(null AS cardinal_number) AS character_octet_length,
2347 CAST(null AS sql_identifier) AS character_set_catalog,
2348 CAST(null AS sql_identifier) AS character_set_schema,
2349 CAST(null AS sql_identifier) AS character_set_name,
2350 CAST(null AS sql_identifier) AS collation_catalog,
2351 CAST(null AS sql_identifier) AS collation_schema,
2352 CAST(null AS sql_identifier) AS collation_name,
2353 CAST(null AS cardinal_number) AS numeric_precision,
2354 CAST(null AS cardinal_number) AS numeric_precision_radix,
2355 CAST(null AS cardinal_number) AS numeric_scale,
2356 CAST(null AS cardinal_number) AS datetime_precision,
2357 CAST(null AS character_data) AS interval_type,
2358 CAST(null AS cardinal_number) AS interval_precision,
2359 CAST(null AS sql_identifier) AS source_dtd_identifier,
2360 CAST(null AS sql_identifier) AS ref_dtd_identifier
2362 FROM pg_namespace n, pg_class c, pg_type t
2364 WHERE n.oid = c.relnamespace
2365 AND t.typrelid = c.oid
2367 AND (pg_has_role(t.typowner, 'USAGE')
2368 OR has_type_privilege(t.oid, 'USAGE'));
2370 GRANT SELECT ON user_defined_types TO PUBLIC;
2378 CREATE VIEW view_column_usage AS
2380 CAST(current_database() AS sql_identifier) AS view_catalog,
2381 CAST(nv.nspname AS sql_identifier) AS view_schema,
2382 CAST(v.relname AS sql_identifier) AS view_name,
2383 CAST(current_database() AS sql_identifier) AS table_catalog,
2384 CAST(nt.nspname AS sql_identifier) AS table_schema,
2385 CAST(t.relname AS sql_identifier) AS table_name,
2386 CAST(a.attname AS sql_identifier) AS column_name
2388 FROM pg_namespace nv, pg_class v, pg_depend dv,
2389 pg_depend dt, pg_class t, pg_namespace nt,
2392 WHERE nv.oid = v.relnamespace
2394 AND v.oid = dv.refobjid
2395 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2396 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2397 AND dv.deptype = 'i'
2398 AND dv.objid = dt.objid
2399 AND dv.refobjid <> dt.refobjid
2400 AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2401 AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2402 AND dt.refobjid = t.oid
2403 AND t.relnamespace = nt.oid
2404 AND t.relkind IN ('r', 'v', 'f')
2405 AND t.oid = a.attrelid
2406 AND dt.refobjsubid = a.attnum
2407 AND pg_has_role(t.relowner, 'USAGE');
2409 GRANT SELECT ON view_column_usage TO PUBLIC;
2417 -- feature not supported
2422 * VIEW_ROUTINE_USAGE
2425 CREATE VIEW view_routine_usage AS
2427 CAST(current_database() AS sql_identifier) AS table_catalog,
2428 CAST(nv.nspname AS sql_identifier) AS table_schema,
2429 CAST(v.relname AS sql_identifier) AS table_name,
2430 CAST(current_database() AS sql_identifier) AS specific_catalog,
2431 CAST(np.nspname AS sql_identifier) AS specific_schema,
2432 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name
2434 FROM pg_namespace nv, pg_class v, pg_depend dv,
2435 pg_depend dp, pg_proc p, pg_namespace np
2437 WHERE nv.oid = v.relnamespace
2439 AND v.oid = dv.refobjid
2440 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2441 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2442 AND dv.deptype = 'i'
2443 AND dv.objid = dp.objid
2444 AND dp.classid = 'pg_catalog.pg_rewrite'::regclass
2445 AND dp.refclassid = 'pg_catalog.pg_proc'::regclass
2446 AND dp.refobjid = p.oid
2447 AND p.pronamespace = np.oid
2448 AND pg_has_role(p.proowner, 'USAGE');
2450 GRANT SELECT ON view_routine_usage TO PUBLIC;
2458 CREATE VIEW view_table_usage AS
2460 CAST(current_database() AS sql_identifier) AS view_catalog,
2461 CAST(nv.nspname AS sql_identifier) AS view_schema,
2462 CAST(v.relname AS sql_identifier) AS view_name,
2463 CAST(current_database() AS sql_identifier) AS table_catalog,
2464 CAST(nt.nspname AS sql_identifier) AS table_schema,
2465 CAST(t.relname AS sql_identifier) AS table_name
2467 FROM pg_namespace nv, pg_class v, pg_depend dv,
2468 pg_depend dt, pg_class t, pg_namespace nt
2470 WHERE nv.oid = v.relnamespace
2472 AND v.oid = dv.refobjid
2473 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2474 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2475 AND dv.deptype = 'i'
2476 AND dv.objid = dt.objid
2477 AND dv.refobjid <> dt.refobjid
2478 AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2479 AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2480 AND dt.refobjid = t.oid
2481 AND t.relnamespace = nt.oid
2482 AND t.relkind IN ('r', 'v', 'f')
2483 AND pg_has_role(t.relowner, 'USAGE');
2485 GRANT SELECT ON view_table_usage TO PUBLIC;
2493 CREATE VIEW views AS
2494 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
2495 CAST(nc.nspname AS sql_identifier) AS table_schema,
2496 CAST(c.relname AS sql_identifier) AS table_name,
2499 CASE WHEN pg_has_role(c.relowner, 'USAGE')
2500 THEN pg_get_viewdef(c.oid)
2502 AS character_data) AS view_definition,
2505 CASE WHEN 'check_option=cascaded' = ANY (c.reloptions)
2507 WHEN 'check_option=local' = ANY (c.reloptions)
2510 AS character_data) AS check_option,
2513 -- (1 << CMD_UPDATE) + (1 << CMD_DELETE)
2514 CASE WHEN pg_relation_is_updatable(c.oid, false) & 20 = 20
2515 THEN 'YES' ELSE 'NO' END
2516 AS yes_or_no) AS is_updatable,
2520 CASE WHEN pg_relation_is_updatable(c.oid, false) & 8 = 8
2521 THEN 'YES' ELSE 'NO' END
2522 AS yes_or_no) AS is_insertable_into,
2525 -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_UPDATE
2526 CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 81 = 81)
2527 THEN 'YES' ELSE 'NO' END
2528 AS yes_or_no) AS is_trigger_updatable,
2531 -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_DELETE
2532 CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 73 = 73)
2533 THEN 'YES' ELSE 'NO' END
2534 AS yes_or_no) AS is_trigger_deletable,
2537 -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_INSERT
2538 CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 69 = 69)
2539 THEN 'YES' ELSE 'NO' END
2540 AS yes_or_no) AS is_trigger_insertable_into
2542 FROM pg_namespace nc, pg_class c
2544 WHERE c.relnamespace = nc.oid
2546 AND (NOT pg_is_other_temp_schema(nc.oid))
2547 AND (pg_has_role(c.relowner, 'USAGE')
2548 OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
2549 OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES') );
2551 GRANT SELECT ON views TO PUBLIC;
2554 -- The following views have dependencies that force them to appear out of order.
2558 * DATA_TYPE_PRIVILEGES view
2561 CREATE VIEW data_type_privileges AS
2562 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2563 CAST(x.objschema AS sql_identifier) AS object_schema,
2564 CAST(x.objname AS sql_identifier) AS object_name,
2565 CAST(x.objtype AS character_data) AS object_type,
2566 CAST(x.objdtdid AS sql_identifier) AS dtd_identifier
2570 SELECT udt_schema, udt_name, 'USER-DEFINED TYPE'::text, dtd_identifier FROM attributes
2572 SELECT table_schema, table_name, 'TABLE'::text, dtd_identifier FROM columns
2574 SELECT domain_schema, domain_name, 'DOMAIN'::text, dtd_identifier FROM domains
2576 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM parameters
2578 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM routines
2579 ) AS x (objschema, objname, objtype, objdtdid);
2581 GRANT SELECT ON data_type_privileges TO PUBLIC;
2586 * ELEMENT_TYPES view
2589 CREATE VIEW element_types AS
2590 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2591 CAST(n.nspname AS sql_identifier) AS object_schema,
2592 CAST(x.objname AS sql_identifier) AS object_name,
2593 CAST(x.objtype AS character_data) AS object_type,
2594 CAST(x.objdtdid AS sql_identifier) AS collection_type_identifier,
2596 CASE WHEN nbt.nspname = 'pg_catalog' THEN format_type(bt.oid, null)
2597 ELSE 'USER-DEFINED' END AS character_data) AS data_type,
2599 CAST(null AS cardinal_number) AS character_maximum_length,
2600 CAST(null AS cardinal_number) AS character_octet_length,
2601 CAST(null AS sql_identifier) AS character_set_catalog,
2602 CAST(null AS sql_identifier) AS character_set_schema,
2603 CAST(null AS sql_identifier) AS character_set_name,
2604 CAST(CASE WHEN nco.nspname IS NOT NULL THEN current_database() END AS sql_identifier) AS collation_catalog,
2605 CAST(nco.nspname AS sql_identifier) AS collation_schema,
2606 CAST(co.collname AS sql_identifier) AS collation_name,
2607 CAST(null AS cardinal_number) AS numeric_precision,
2608 CAST(null AS cardinal_number) AS numeric_precision_radix,
2609 CAST(null AS cardinal_number) AS numeric_scale,
2610 CAST(null AS cardinal_number) AS datetime_precision,
2611 CAST(null AS character_data) AS interval_type,
2612 CAST(null AS cardinal_number) AS interval_precision,
2614 CAST(null AS character_data) AS domain_default, -- XXX maybe a bug in the standard
2616 CAST(current_database() AS sql_identifier) AS udt_catalog,
2617 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
2618 CAST(bt.typname AS sql_identifier) AS udt_name,
2620 CAST(null AS sql_identifier) AS scope_catalog,
2621 CAST(null AS sql_identifier) AS scope_schema,
2622 CAST(null AS sql_identifier) AS scope_name,
2624 CAST(null AS cardinal_number) AS maximum_cardinality,
2625 CAST('a' || CAST(x.objdtdid AS text) AS sql_identifier) AS dtd_identifier
2627 FROM pg_namespace n, pg_type at, pg_namespace nbt, pg_type bt,
2629 /* columns, attributes */
2630 SELECT c.relnamespace, CAST(c.relname AS sql_identifier),
2631 CASE WHEN c.relkind = 'c' THEN 'USER-DEFINED TYPE'::text ELSE 'TABLE'::text END,
2632 a.attnum, a.atttypid, a.attcollation
2633 FROM pg_class c, pg_attribute a
2634 WHERE c.oid = a.attrelid
2635 AND c.relkind IN ('r', 'v', 'f', 'c')
2636 AND attnum > 0 AND NOT attisdropped
2641 SELECT t.typnamespace, CAST(t.typname AS sql_identifier),
2642 'DOMAIN'::text, 1, t.typbasetype, t.typcollation
2644 WHERE t.typtype = 'd'
2649 SELECT pronamespace, CAST(proname || '_' || CAST(oid AS text) AS sql_identifier),
2650 'ROUTINE'::text, (ss.x).n, (ss.x).x, 0
2651 FROM (SELECT p.pronamespace, p.proname, p.oid,
2652 _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
2653 FROM pg_proc p) AS ss
2658 SELECT p.pronamespace, CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier),
2659 'ROUTINE'::text, 0, p.prorettype, 0
2662 ) AS x (objschema, objname, objtype, objdtdid, objtypeid, objcollation)
2663 LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid))
2664 ON x.objcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default')
2666 WHERE n.oid = x.objschema
2667 AND at.oid = x.objtypeid
2668 AND (at.typelem <> 0 AND at.typlen = -1)
2669 AND at.typelem = bt.oid
2670 AND nbt.oid = bt.typnamespace
2672 AND (n.nspname, x.objname, x.objtype, CAST(x.objdtdid AS sql_identifier)) IN
2673 ( SELECT object_schema, object_name, object_type, dtd_identifier
2674 FROM data_type_privileges );
2676 GRANT SELECT ON element_types TO PUBLIC;
2679 -- SQL/MED views; these use section numbers from part 9 of the standard.
2680 -- (still SQL:2008; there is no SQL:2011 SQL/MED)
2682 /* Base view for foreign table columns */
2683 CREATE VIEW _pg_foreign_table_columns AS
2688 FROM pg_foreign_table t, pg_authid u, pg_namespace n, pg_class c,
2690 WHERE u.oid = c.relowner
2691 AND (pg_has_role(c.relowner, 'USAGE')
2692 OR has_column_privilege(c.oid, a.attnum, 'SELECT, INSERT, UPDATE, REFERENCES'))
2693 AND n.oid = c.relnamespace
2694 AND c.oid = t.ftrelid
2696 AND a.attrelid = c.oid
2701 * COLUMN_OPTIONS view
2703 CREATE VIEW column_options AS
2704 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
2705 c.nspname AS table_schema,
2706 c.relname AS table_name,
2707 c.attname AS column_name,
2708 CAST((pg_options_to_table(c.attfdwoptions)).option_name AS sql_identifier) AS option_name,
2709 CAST((pg_options_to_table(c.attfdwoptions)).option_value AS character_data) AS option_value
2710 FROM _pg_foreign_table_columns c;
2712 GRANT SELECT ON column_options TO PUBLIC;
2715 /* Base view for foreign-data wrappers */
2716 CREATE VIEW _pg_foreign_data_wrappers AS
2720 CAST(current_database() AS sql_identifier) AS foreign_data_wrapper_catalog,
2721 CAST(fdwname AS sql_identifier) AS foreign_data_wrapper_name,
2722 CAST(u.rolname AS sql_identifier) AS authorization_identifier,
2723 CAST('c' AS character_data) AS foreign_data_wrapper_language
2724 FROM pg_foreign_data_wrapper w, pg_authid u
2725 WHERE u.oid = w.fdwowner
2726 AND (pg_has_role(fdwowner, 'USAGE')
2727 OR has_foreign_data_wrapper_privilege(w.oid, 'USAGE'));
2732 * FOREIGN_DATA_WRAPPER_OPTIONS view
2734 CREATE VIEW foreign_data_wrapper_options AS
2735 SELECT foreign_data_wrapper_catalog,
2736 foreign_data_wrapper_name,
2737 CAST((pg_options_to_table(w.fdwoptions)).option_name AS sql_identifier) AS option_name,
2738 CAST((pg_options_to_table(w.fdwoptions)).option_value AS character_data) AS option_value
2739 FROM _pg_foreign_data_wrappers w;
2741 GRANT SELECT ON foreign_data_wrapper_options TO PUBLIC;
2746 * FOREIGN_DATA_WRAPPERS view
2748 CREATE VIEW foreign_data_wrappers AS
2749 SELECT foreign_data_wrapper_catalog,
2750 foreign_data_wrapper_name,
2751 authorization_identifier,
2752 CAST(NULL AS character_data) AS library_name,
2753 foreign_data_wrapper_language
2754 FROM _pg_foreign_data_wrappers w;
2756 GRANT SELECT ON foreign_data_wrappers TO PUBLIC;
2759 /* Base view for foreign servers */
2760 CREATE VIEW _pg_foreign_servers AS
2763 CAST(current_database() AS sql_identifier) AS foreign_server_catalog,
2764 CAST(srvname AS sql_identifier) AS foreign_server_name,
2765 CAST(current_database() AS sql_identifier) AS foreign_data_wrapper_catalog,
2766 CAST(w.fdwname AS sql_identifier) AS foreign_data_wrapper_name,
2767 CAST(srvtype AS character_data) AS foreign_server_type,
2768 CAST(srvversion AS character_data) AS foreign_server_version,
2769 CAST(u.rolname AS sql_identifier) AS authorization_identifier
2770 FROM pg_foreign_server s, pg_foreign_data_wrapper w, pg_authid u
2771 WHERE w.oid = s.srvfdw
2772 AND u.oid = s.srvowner
2773 AND (pg_has_role(s.srvowner, 'USAGE')
2774 OR has_server_privilege(s.oid, 'USAGE'));
2779 * FOREIGN_SERVER_OPTIONS view
2781 CREATE VIEW foreign_server_options AS
2782 SELECT foreign_server_catalog,
2783 foreign_server_name,
2784 CAST((pg_options_to_table(s.srvoptions)).option_name AS sql_identifier) AS option_name,
2785 CAST((pg_options_to_table(s.srvoptions)).option_value AS character_data) AS option_value
2786 FROM _pg_foreign_servers s;
2788 GRANT SELECT ON TABLE foreign_server_options TO PUBLIC;
2793 * FOREIGN_SERVERS view
2795 CREATE VIEW foreign_servers AS
2796 SELECT foreign_server_catalog,
2797 foreign_server_name,
2798 foreign_data_wrapper_catalog,
2799 foreign_data_wrapper_name,
2800 foreign_server_type,
2801 foreign_server_version,
2802 authorization_identifier
2803 FROM _pg_foreign_servers;
2805 GRANT SELECT ON foreign_servers TO PUBLIC;
2808 /* Base view for foreign tables */
2809 CREATE VIEW _pg_foreign_tables AS
2811 CAST(current_database() AS sql_identifier) AS foreign_table_catalog,
2812 n.nspname AS foreign_table_schema,
2813 c.relname AS foreign_table_name,
2814 t.ftoptions AS ftoptions,
2815 CAST(current_database() AS sql_identifier) AS foreign_server_catalog,
2816 CAST(srvname AS sql_identifier) AS foreign_server_name,
2817 CAST(u.rolname AS sql_identifier) AS authorization_identifier
2818 FROM pg_foreign_table t, pg_foreign_server s, pg_foreign_data_wrapper w,
2819 pg_authid u, pg_namespace n, pg_class c
2820 WHERE w.oid = s.srvfdw
2821 AND u.oid = c.relowner
2822 AND (pg_has_role(c.relowner, 'USAGE')
2823 OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
2824 OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'))
2825 AND n.oid = c.relnamespace
2826 AND c.oid = t.ftrelid
2828 AND s.oid = t.ftserver;
2833 * FOREIGN_TABLE_OPTIONS view
2835 CREATE VIEW foreign_table_options AS
2836 SELECT foreign_table_catalog,
2837 foreign_table_schema,
2839 CAST((pg_options_to_table(t.ftoptions)).option_name AS sql_identifier) AS option_name,
2840 CAST((pg_options_to_table(t.ftoptions)).option_value AS character_data) AS option_value
2841 FROM _pg_foreign_tables t;
2843 GRANT SELECT ON TABLE foreign_table_options TO PUBLIC;
2848 * FOREIGN_TABLES view
2850 CREATE VIEW foreign_tables AS
2851 SELECT foreign_table_catalog,
2852 foreign_table_schema,
2854 foreign_server_catalog,
2856 FROM _pg_foreign_tables;
2858 GRANT SELECT ON foreign_tables TO PUBLIC;
2862 /* Base view for user mappings */
2863 CREATE VIEW _pg_user_mappings AS
2867 CAST(COALESCE(u.rolname,'PUBLIC') AS sql_identifier ) AS authorization_identifier,
2868 s.foreign_server_catalog,
2869 s.foreign_server_name,
2870 s.authorization_identifier AS srvowner
2871 FROM pg_user_mapping um LEFT JOIN pg_authid u ON (u.oid = um.umuser),
2872 _pg_foreign_servers s
2873 WHERE s.oid = um.umserver;
2878 * USER_MAPPING_OPTIONS view
2880 CREATE VIEW user_mapping_options AS
2881 SELECT authorization_identifier,
2882 foreign_server_catalog,
2883 foreign_server_name,
2884 CAST((pg_options_to_table(um.umoptions)).option_name AS sql_identifier) AS option_name,
2885 CAST(CASE WHEN (umuser <> 0 AND authorization_identifier = current_user)
2886 OR (umuser = 0 AND pg_has_role(srvowner, 'USAGE'))
2888 SELECT pg_check_role_attribute(pg_authid.rolattr, 'SUPERUSER') AS rolsuper
2890 WHERE rolname = current_user
2892 THEN (pg_options_to_table(um.umoptions)).option_value
2893 ELSE NULL END AS character_data) AS option_value
2894 FROM _pg_user_mappings um;
2896 GRANT SELECT ON user_mapping_options TO PUBLIC;
2901 * USER_MAPPINGS view
2903 CREATE VIEW user_mappings AS
2904 SELECT authorization_identifier,
2905 foreign_server_catalog,
2907 FROM _pg_user_mappings;
2909 GRANT SELECT ON user_mappings TO PUBLIC;