2 * SQL Information Schema
3 * as defined in ISO/IEC 9075-11:2008
5 * Copyright (c) 2003-2012, 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'
735 AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '2' AND is_instead)
736 AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '4' AND is_instead))
737 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_updatable
739 FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum)
740 JOIN (pg_class c JOIN pg_namespace nc ON (c.relnamespace = nc.oid)) ON a.attrelid = c.oid
741 JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON a.atttypid = t.oid
742 LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
743 ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
744 LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid))
745 ON a.attcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default')
747 WHERE (NOT pg_is_other_temp_schema(nc.oid))
749 AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v', 'f')
751 AND (pg_has_role(c.relowner, 'USAGE')
752 OR has_column_privilege(c.oid, a.attnum,
753 'SELECT, INSERT, UPDATE, REFERENCES'));
755 GRANT SELECT ON columns TO PUBLIC;
760 * CONSTRAINT_COLUMN_USAGE view
763 CREATE VIEW constraint_column_usage AS
764 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
765 CAST(tblschema AS sql_identifier) AS table_schema,
766 CAST(tblname AS sql_identifier) AS table_name,
767 CAST(colname AS sql_identifier) AS column_name,
768 CAST(current_database() AS sql_identifier) AS constraint_catalog,
769 CAST(cstrschema AS sql_identifier) AS constraint_schema,
770 CAST(cstrname AS sql_identifier) AS constraint_name
773 /* check constraints */
774 SELECT DISTINCT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
775 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_depend d, pg_namespace nc, pg_constraint c
776 WHERE nr.oid = r.relnamespace
777 AND r.oid = a.attrelid
778 AND d.refclassid = 'pg_catalog.pg_class'::regclass
779 AND d.refobjid = r.oid
780 AND d.refobjsubid = a.attnum
781 AND d.classid = 'pg_catalog.pg_constraint'::regclass
783 AND c.connamespace = nc.oid
786 AND NOT a.attisdropped
790 /* unique/primary key/foreign key constraints */
791 SELECT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
792 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc,
794 WHERE nr.oid = r.relnamespace
795 AND r.oid = a.attrelid
796 AND nc.oid = c.connamespace
797 AND (CASE WHEN c.contype = 'f' THEN r.oid = c.confrelid AND a.attnum = ANY (c.confkey)
798 ELSE r.oid = c.conrelid AND a.attnum = ANY (c.conkey) END)
799 AND NOT a.attisdropped
800 AND c.contype IN ('p', 'u', 'f')
803 ) AS x (tblschema, tblname, tblowner, colname, cstrschema, cstrname)
805 WHERE pg_has_role(x.tblowner, 'USAGE');
807 GRANT SELECT ON constraint_column_usage TO PUBLIC;
812 * CONSTRAINT_TABLE_USAGE view
815 CREATE VIEW constraint_table_usage AS
816 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
817 CAST(nr.nspname AS sql_identifier) AS table_schema,
818 CAST(r.relname AS sql_identifier) AS table_name,
819 CAST(current_database() AS sql_identifier) AS constraint_catalog,
820 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
821 CAST(c.conname AS sql_identifier) AS constraint_name
823 FROM pg_constraint c, pg_namespace nc,
824 pg_class r, pg_namespace nr
826 WHERE c.connamespace = nc.oid AND r.relnamespace = nr.oid
827 AND ( (c.contype = 'f' AND c.confrelid = r.oid)
828 OR (c.contype IN ('p', 'u') AND c.conrelid = r.oid) )
830 AND pg_has_role(r.relowner, 'USAGE');
832 GRANT SELECT ON constraint_table_usage TO PUBLIC;
835 -- 5.24 DATA_TYPE_PRIVILEGES view appears later.
840 * DIRECT_SUPERTABLES view
843 -- feature not supported
848 * DIRECT_SUPERTYPES view
851 -- feature not supported
856 * DOMAIN_CONSTRAINTS view
859 CREATE VIEW domain_constraints AS
860 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
861 CAST(rs.nspname AS sql_identifier) AS constraint_schema,
862 CAST(con.conname AS sql_identifier) AS constraint_name,
863 CAST(current_database() AS sql_identifier) AS domain_catalog,
864 CAST(n.nspname AS sql_identifier) AS domain_schema,
865 CAST(t.typname AS sql_identifier) AS domain_name,
866 CAST(CASE WHEN condeferrable THEN 'YES' ELSE 'NO' END
867 AS yes_or_no) AS is_deferrable,
868 CAST(CASE WHEN condeferred THEN 'YES' ELSE 'NO' END
869 AS yes_or_no) AS initially_deferred
870 FROM pg_namespace rs, pg_namespace n, pg_constraint con, pg_type t
871 WHERE rs.oid = con.connamespace
872 AND n.oid = t.typnamespace
873 AND t.oid = con.contypid
874 AND (pg_has_role(t.typowner, 'USAGE')
875 OR has_type_privilege(t.oid, 'USAGE'));
877 GRANT SELECT ON domain_constraints TO PUBLIC;
881 * DOMAIN_UDT_USAGE view
882 * apparently removed in SQL:2003
885 CREATE VIEW domain_udt_usage AS
886 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
887 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
888 CAST(bt.typname AS sql_identifier) AS udt_name,
889 CAST(current_database() AS sql_identifier) AS domain_catalog,
890 CAST(nt.nspname AS sql_identifier) AS domain_schema,
891 CAST(t.typname AS sql_identifier) AS domain_name
893 FROM pg_type t, pg_namespace nt,
894 pg_type bt, pg_namespace nbt
896 WHERE t.typnamespace = nt.oid
897 AND t.typbasetype = bt.oid
898 AND bt.typnamespace = nbt.oid
900 AND pg_has_role(bt.typowner, 'USAGE');
902 GRANT SELECT ON domain_udt_usage TO PUBLIC;
910 CREATE VIEW domains AS
911 SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
912 CAST(nt.nspname AS sql_identifier) AS domain_schema,
913 CAST(t.typname AS sql_identifier) AS domain_name,
916 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
917 WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
918 ELSE 'USER-DEFINED' END
923 _pg_char_max_length(t.typbasetype, t.typtypmod)
925 AS character_maximum_length,
928 _pg_char_octet_length(t.typbasetype, t.typtypmod)
930 AS character_octet_length,
932 CAST(null AS sql_identifier) AS character_set_catalog,
933 CAST(null AS sql_identifier) AS character_set_schema,
934 CAST(null AS sql_identifier) AS character_set_name,
936 CAST(CASE WHEN nco.nspname IS NOT NULL THEN current_database() END AS sql_identifier) AS collation_catalog,
937 CAST(nco.nspname AS sql_identifier) AS collation_schema,
938 CAST(co.collname AS sql_identifier) AS collation_name,
941 _pg_numeric_precision(t.typbasetype, t.typtypmod)
943 AS numeric_precision,
946 _pg_numeric_precision_radix(t.typbasetype, t.typtypmod)
948 AS numeric_precision_radix,
951 _pg_numeric_scale(t.typbasetype, t.typtypmod)
956 _pg_datetime_precision(t.typbasetype, t.typtypmod)
958 AS datetime_precision,
961 _pg_interval_type(t.typbasetype, t.typtypmod)
964 CAST(null AS cardinal_number) AS interval_precision,
966 CAST(t.typdefault AS character_data) AS domain_default,
968 CAST(current_database() AS sql_identifier) AS udt_catalog,
969 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
970 CAST(bt.typname AS sql_identifier) AS udt_name,
972 CAST(null AS sql_identifier) AS scope_catalog,
973 CAST(null AS sql_identifier) AS scope_schema,
974 CAST(null AS sql_identifier) AS scope_name,
976 CAST(null AS cardinal_number) AS maximum_cardinality,
977 CAST(1 AS sql_identifier) AS dtd_identifier
979 FROM (pg_type t JOIN pg_namespace nt ON t.typnamespace = nt.oid)
980 JOIN (pg_type bt JOIN pg_namespace nbt ON bt.typnamespace = nbt.oid)
981 ON (t.typbasetype = bt.oid AND t.typtype = 'd')
982 LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid))
983 ON t.typcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default')
985 WHERE (pg_has_role(t.typowner, 'USAGE')
986 OR has_type_privilege(t.oid, 'USAGE'));
988 GRANT SELECT ON domains TO PUBLIC;
991 -- 5.29 ELEMENT_TYPES view appears later.
999 CREATE VIEW enabled_roles AS
1000 SELECT CAST(a.rolname AS sql_identifier) AS role_name
1002 WHERE pg_has_role(a.oid, 'USAGE');
1004 GRANT SELECT ON enabled_roles TO PUBLIC;
1012 -- feature not supported
1017 * KEY_COLUMN_USAGE view
1020 CREATE VIEW key_column_usage AS
1021 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1022 CAST(nc_nspname AS sql_identifier) AS constraint_schema,
1023 CAST(conname AS sql_identifier) AS constraint_name,
1024 CAST(current_database() AS sql_identifier) AS table_catalog,
1025 CAST(nr_nspname AS sql_identifier) AS table_schema,
1026 CAST(relname AS sql_identifier) AS table_name,
1027 CAST(a.attname AS sql_identifier) AS column_name,
1028 CAST((ss.x).n AS cardinal_number) AS ordinal_position,
1029 CAST(CASE WHEN contype = 'f' THEN
1030 _pg_index_position(ss.conindid, ss.confkey[(ss.x).n])
1032 END AS cardinal_number)
1033 AS position_in_unique_constraint
1034 FROM pg_attribute a,
1035 (SELECT r.oid AS roid, r.relname, r.relowner,
1036 nc.nspname AS nc_nspname, nr.nspname AS nr_nspname,
1037 c.oid AS coid, c.conname, c.contype, c.conindid,
1038 c.confkey, c.confrelid,
1039 _pg_expandarray(c.conkey) AS x
1040 FROM pg_namespace nr, pg_class r, pg_namespace nc,
1042 WHERE nr.oid = r.relnamespace
1043 AND r.oid = c.conrelid
1044 AND nc.oid = c.connamespace
1045 AND c.contype IN ('p', 'u', 'f')
1047 AND (NOT pg_is_other_temp_schema(nr.oid)) ) AS ss
1048 WHERE ss.roid = a.attrelid
1049 AND a.attnum = (ss.x).x
1050 AND NOT a.attisdropped
1051 AND (pg_has_role(relowner, 'USAGE')
1052 OR has_column_privilege(roid, a.attnum,
1053 'SELECT, INSERT, UPDATE, REFERENCES'));
1055 GRANT SELECT ON key_column_usage TO PUBLIC;
1060 * METHOD_SPECIFICATION_PARAMETERS view
1063 -- feature not supported
1068 * METHOD_SPECIFICATIONS view
1071 -- feature not supported
1079 CREATE VIEW parameters AS
1080 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
1081 CAST(n_nspname AS sql_identifier) AS specific_schema,
1082 CAST(proname || '_' || CAST(p_oid AS text) AS sql_identifier) AS specific_name,
1083 CAST((ss.x).n AS cardinal_number) AS ordinal_position,
1085 CASE WHEN proargmodes IS NULL THEN 'IN'
1086 WHEN proargmodes[(ss.x).n] = 'i' THEN 'IN'
1087 WHEN proargmodes[(ss.x).n] = 'o' THEN 'OUT'
1088 WHEN proargmodes[(ss.x).n] = 'b' THEN 'INOUT'
1089 WHEN proargmodes[(ss.x).n] = 'v' THEN 'IN'
1090 WHEN proargmodes[(ss.x).n] = 't' THEN 'OUT'
1091 END AS character_data) AS parameter_mode,
1092 CAST('NO' AS yes_or_no) AS is_result,
1093 CAST('NO' AS yes_or_no) AS as_locator,
1094 CAST(NULLIF(proargnames[(ss.x).n], '') AS sql_identifier) AS parameter_name,
1096 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
1097 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
1098 ELSE 'USER-DEFINED' END AS character_data)
1100 CAST(null AS cardinal_number) AS character_maximum_length,
1101 CAST(null AS cardinal_number) AS character_octet_length,
1102 CAST(null AS sql_identifier) AS character_set_catalog,
1103 CAST(null AS sql_identifier) AS character_set_schema,
1104 CAST(null AS sql_identifier) AS character_set_name,
1105 CAST(null AS sql_identifier) AS collation_catalog,
1106 CAST(null AS sql_identifier) AS collation_schema,
1107 CAST(null AS sql_identifier) AS collation_name,
1108 CAST(null AS cardinal_number) AS numeric_precision,
1109 CAST(null AS cardinal_number) AS numeric_precision_radix,
1110 CAST(null AS cardinal_number) AS numeric_scale,
1111 CAST(null AS cardinal_number) AS datetime_precision,
1112 CAST(null AS character_data) AS interval_type,
1113 CAST(null AS cardinal_number) AS interval_precision,
1114 CAST(current_database() AS sql_identifier) AS udt_catalog,
1115 CAST(nt.nspname AS sql_identifier) AS udt_schema,
1116 CAST(t.typname AS sql_identifier) AS udt_name,
1117 CAST(null AS sql_identifier) AS scope_catalog,
1118 CAST(null AS sql_identifier) AS scope_schema,
1119 CAST(null AS sql_identifier) AS scope_name,
1120 CAST(null AS cardinal_number) AS maximum_cardinality,
1121 CAST((ss.x).n AS sql_identifier) AS dtd_identifier
1123 FROM pg_type t, pg_namespace nt,
1124 (SELECT n.nspname AS n_nspname, p.proname, p.oid AS p_oid,
1125 p.proargnames, p.proargmodes,
1126 _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
1127 FROM pg_namespace n, pg_proc p
1128 WHERE n.oid = p.pronamespace
1129 AND (pg_has_role(p.proowner, 'USAGE') OR
1130 has_function_privilege(p.oid, 'EXECUTE'))) AS ss
1131 WHERE t.oid = (ss.x).x AND t.typnamespace = nt.oid;
1133 GRANT SELECT ON parameters TO PUBLIC;
1138 * REFERENCED_TYPES view
1141 -- feature not supported
1146 * REFERENTIAL_CONSTRAINTS view
1149 CREATE VIEW referential_constraints AS
1150 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1151 CAST(ncon.nspname AS sql_identifier) AS constraint_schema,
1152 CAST(con.conname AS sql_identifier) AS constraint_name,
1154 CASE WHEN npkc.nspname IS NULL THEN NULL
1155 ELSE current_database() END
1156 AS sql_identifier) AS unique_constraint_catalog,
1157 CAST(npkc.nspname AS sql_identifier) AS unique_constraint_schema,
1158 CAST(pkc.conname AS sql_identifier) AS unique_constraint_name,
1161 CASE con.confmatchtype WHEN 'f' THEN 'FULL'
1162 WHEN 'p' THEN 'PARTIAL'
1163 WHEN 's' THEN 'NONE' END
1164 AS character_data) AS match_option,
1167 CASE con.confupdtype WHEN 'c' THEN 'CASCADE'
1168 WHEN 'n' THEN 'SET NULL'
1169 WHEN 'd' THEN 'SET DEFAULT'
1170 WHEN 'r' THEN 'RESTRICT'
1171 WHEN 'a' THEN 'NO ACTION' END
1172 AS character_data) AS update_rule,
1175 CASE con.confdeltype WHEN 'c' THEN 'CASCADE'
1176 WHEN 'n' THEN 'SET NULL'
1177 WHEN 'd' THEN 'SET DEFAULT'
1178 WHEN 'r' THEN 'RESTRICT'
1179 WHEN 'a' THEN 'NO ACTION' END
1180 AS character_data) AS delete_rule
1182 FROM (pg_namespace ncon
1183 INNER JOIN pg_constraint con ON ncon.oid = con.connamespace
1184 INNER JOIN pg_class c ON con.conrelid = c.oid AND con.contype = 'f')
1185 LEFT JOIN pg_depend d1 -- find constraint's dependency on an index
1186 ON d1.objid = con.oid AND d1.classid = 'pg_constraint'::regclass
1187 AND d1.refclassid = 'pg_class'::regclass AND d1.refobjsubid = 0
1188 LEFT JOIN pg_depend d2 -- find pkey/unique constraint for that index
1189 ON d2.refclassid = 'pg_constraint'::regclass
1190 AND d2.classid = 'pg_class'::regclass
1191 AND d2.objid = d1.refobjid AND d2.objsubid = 0
1192 AND d2.deptype = 'i'
1193 LEFT JOIN pg_constraint pkc ON pkc.oid = d2.refobjid
1194 AND pkc.contype IN ('p', 'u')
1195 AND pkc.conrelid = con.confrelid
1196 LEFT JOIN pg_namespace npkc ON pkc.connamespace = npkc.oid
1198 WHERE pg_has_role(c.relowner, 'USAGE')
1199 -- SELECT privilege omitted, per SQL standard
1200 OR has_table_privilege(c.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1201 OR has_any_column_privilege(c.oid, 'INSERT, UPDATE, REFERENCES') ;
1203 GRANT SELECT ON referential_constraints TO PUBLIC;
1208 * ROLE_COLUMN_GRANTS view
1211 CREATE VIEW role_column_grants AS
1220 FROM column_privileges
1221 WHERE grantor IN (SELECT role_name FROM enabled_roles)
1222 OR grantee IN (SELECT role_name FROM enabled_roles);
1224 GRANT SELECT ON role_column_grants TO PUBLIC;
1227 -- 5.39 ROLE_ROUTINE_GRANTS view is based on 5.45 ROUTINE_PRIVILEGES and is defined there instead.
1230 -- 5.40 ROLE_TABLE_GRANTS view is based on 5.60 TABLE_PRIVILEGES and is defined there instead.
1235 * ROLE_TABLE_METHOD_GRANTS view
1238 -- feature not supported
1242 -- 5.42 ROLE_USAGE_GRANTS view is based on 5.71 USAGE_PRIVILEGES and is defined there instead.
1245 -- 5.43 ROLE_UDT_GRANTS view is based on 5.70 UDT_PRIVILEGES and is defined there instead.
1250 * ROUTINE_COLUMN_USAGE view
1253 -- not tracked by PostgreSQL
1258 * ROUTINE_PRIVILEGES view
1261 CREATE VIEW routine_privileges AS
1262 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1263 CAST(grantee.rolname AS sql_identifier) AS grantee,
1264 CAST(current_database() AS sql_identifier) AS specific_catalog,
1265 CAST(n.nspname AS sql_identifier) AS specific_schema,
1266 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1267 CAST(current_database() AS sql_identifier) AS routine_catalog,
1268 CAST(n.nspname AS sql_identifier) AS routine_schema,
1269 CAST(p.proname AS sql_identifier) AS routine_name,
1270 CAST('EXECUTE' AS character_data) AS privilege_type,
1273 -- object owner always has grant options
1274 pg_has_role(grantee.oid, p.proowner, 'USAGE')
1276 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
1279 SELECT oid, proname, proowner, pronamespace, (aclexplode(coalesce(proacl, acldefault('f', proowner)))).* FROM pg_proc
1280 ) p (oid, proname, proowner, pronamespace, grantor, grantee, prtype, grantable),
1282 pg_authid u_grantor,
1284 SELECT oid, rolname FROM pg_authid
1286 SELECT 0::oid, 'PUBLIC'
1287 ) AS grantee (oid, rolname)
1289 WHERE p.pronamespace = n.oid
1290 AND grantee.oid = p.grantee
1291 AND u_grantor.oid = p.grantor
1292 AND p.prtype IN ('EXECUTE')
1293 AND (pg_has_role(u_grantor.oid, 'USAGE')
1294 OR pg_has_role(grantee.oid, 'USAGE')
1295 OR grantee.rolname = 'PUBLIC');
1297 GRANT SELECT ON routine_privileges TO PUBLIC;
1302 * ROLE_ROUTINE_GRANTS view
1305 CREATE VIEW role_routine_grants AS
1316 FROM routine_privileges
1317 WHERE grantor IN (SELECT role_name FROM enabled_roles)
1318 OR grantee IN (SELECT role_name FROM enabled_roles);
1320 GRANT SELECT ON role_routine_grants TO PUBLIC;
1325 * ROUTINE_ROUTINE_USAGE view
1328 -- not tracked by PostgreSQL
1333 * ROUTINE_SEQUENCE_USAGE view
1336 -- not tracked by PostgreSQL
1341 * ROUTINE_TABLE_USAGE view
1344 -- not tracked by PostgreSQL
1352 CREATE VIEW routines AS
1353 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
1354 CAST(n.nspname AS sql_identifier) AS specific_schema,
1355 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1356 CAST(current_database() AS sql_identifier) AS routine_catalog,
1357 CAST(n.nspname AS sql_identifier) AS routine_schema,
1358 CAST(p.proname AS sql_identifier) AS routine_name,
1359 CAST('FUNCTION' AS character_data) AS routine_type,
1360 CAST(null AS sql_identifier) AS module_catalog,
1361 CAST(null AS sql_identifier) AS module_schema,
1362 CAST(null AS sql_identifier) AS module_name,
1363 CAST(null AS sql_identifier) AS udt_catalog,
1364 CAST(null AS sql_identifier) AS udt_schema,
1365 CAST(null AS sql_identifier) AS udt_name,
1368 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
1369 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
1370 ELSE 'USER-DEFINED' END AS character_data)
1372 CAST(null AS cardinal_number) AS character_maximum_length,
1373 CAST(null AS cardinal_number) AS character_octet_length,
1374 CAST(null AS sql_identifier) AS character_set_catalog,
1375 CAST(null AS sql_identifier) AS character_set_schema,
1376 CAST(null AS sql_identifier) AS character_set_name,
1377 CAST(null AS sql_identifier) AS collation_catalog,
1378 CAST(null AS sql_identifier) AS collation_schema,
1379 CAST(null AS sql_identifier) AS collation_name,
1380 CAST(null AS cardinal_number) AS numeric_precision,
1381 CAST(null AS cardinal_number) AS numeric_precision_radix,
1382 CAST(null AS cardinal_number) AS numeric_scale,
1383 CAST(null AS cardinal_number) AS datetime_precision,
1384 CAST(null AS character_data) AS interval_type,
1385 CAST(null AS cardinal_number) AS interval_precision,
1386 CAST(current_database() AS sql_identifier) AS type_udt_catalog,
1387 CAST(nt.nspname AS sql_identifier) AS type_udt_schema,
1388 CAST(t.typname AS sql_identifier) AS type_udt_name,
1389 CAST(null AS sql_identifier) AS scope_catalog,
1390 CAST(null AS sql_identifier) AS scope_schema,
1391 CAST(null AS sql_identifier) AS scope_name,
1392 CAST(null AS cardinal_number) AS maximum_cardinality,
1393 CAST(0 AS sql_identifier) AS dtd_identifier,
1395 CAST(CASE WHEN l.lanname = 'sql' THEN 'SQL' ELSE 'EXTERNAL' END AS character_data)
1398 CASE WHEN pg_has_role(p.proowner, 'USAGE') THEN p.prosrc ELSE null END
1399 AS character_data) AS routine_definition,
1401 CASE WHEN l.lanname = 'c' THEN p.prosrc ELSE null END
1402 AS character_data) AS external_name,
1403 CAST(upper(l.lanname) AS character_data) AS external_language,
1405 CAST('GENERAL' AS character_data) AS parameter_style,
1406 CAST(CASE WHEN p.provolatile = 'i' THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_deterministic,
1407 CAST('MODIFIES' AS character_data) AS sql_data_access,
1408 CAST(CASE WHEN p.proisstrict THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_null_call,
1409 CAST(null AS character_data) AS sql_path,
1410 CAST('YES' AS yes_or_no) AS schema_level_routine,
1411 CAST(0 AS cardinal_number) AS max_dynamic_result_sets,
1412 CAST(null AS yes_or_no) AS is_user_defined_cast,
1413 CAST(null AS yes_or_no) AS is_implicitly_invocable,
1414 CAST(CASE WHEN p.prosecdef THEN 'DEFINER' ELSE 'INVOKER' END AS character_data) AS security_type,
1415 CAST(null AS sql_identifier) AS to_sql_specific_catalog,
1416 CAST(null AS sql_identifier) AS to_sql_specific_schema,
1417 CAST(null AS sql_identifier) AS to_sql_specific_name,
1418 CAST('NO' AS yes_or_no) AS as_locator,
1419 CAST(null AS time_stamp) AS created,
1420 CAST(null AS time_stamp) AS last_altered,
1421 CAST(null AS yes_or_no) AS new_savepoint_level,
1422 CAST('NO' AS yes_or_no) AS is_udt_dependent,
1424 CAST(null AS character_data) AS result_cast_from_data_type,
1425 CAST(null AS yes_or_no) AS result_cast_as_locator,
1426 CAST(null AS cardinal_number) AS result_cast_char_max_length,
1427 CAST(null AS cardinal_number) AS result_cast_char_octet_length,
1428 CAST(null AS sql_identifier) AS result_cast_char_set_catalog,
1429 CAST(null AS sql_identifier) AS result_cast_char_set_schema,
1430 CAST(null AS sql_identifier) AS result_cast_character_set_name,
1431 CAST(null AS sql_identifier) AS result_cast_collation_catalog,
1432 CAST(null AS sql_identifier) AS result_cast_collation_schema,
1433 CAST(null AS sql_identifier) AS result_cast_collation_name,
1434 CAST(null AS cardinal_number) AS result_cast_numeric_precision,
1435 CAST(null AS cardinal_number) AS result_cast_numeric_precision_radix,
1436 CAST(null AS cardinal_number) AS result_cast_numeric_scale,
1437 CAST(null AS cardinal_number) AS result_cast_datetime_precision,
1438 CAST(null AS character_data) AS result_cast_interval_type,
1439 CAST(null AS cardinal_number) AS result_cast_interval_precision,
1440 CAST(null AS sql_identifier) AS result_cast_type_udt_catalog,
1441 CAST(null AS sql_identifier) AS result_cast_type_udt_schema,
1442 CAST(null AS sql_identifier) AS result_cast_type_udt_name,
1443 CAST(null AS sql_identifier) AS result_cast_scope_catalog,
1444 CAST(null AS sql_identifier) AS result_cast_scope_schema,
1445 CAST(null AS sql_identifier) AS result_cast_scope_name,
1446 CAST(null AS cardinal_number) AS result_cast_maximum_cardinality,
1447 CAST(null AS sql_identifier) AS result_cast_dtd_identifier
1449 FROM pg_namespace n, pg_proc p, pg_language l,
1450 pg_type t, pg_namespace nt
1452 WHERE n.oid = p.pronamespace AND p.prolang = l.oid
1453 AND p.prorettype = t.oid AND t.typnamespace = nt.oid
1454 AND (pg_has_role(p.proowner, 'USAGE')
1455 OR has_function_privilege(p.oid, 'EXECUTE'));
1457 GRANT SELECT ON routines TO PUBLIC;
1465 CREATE VIEW schemata AS
1466 SELECT CAST(current_database() AS sql_identifier) AS catalog_name,
1467 CAST(n.nspname AS sql_identifier) AS schema_name,
1468 CAST(u.rolname AS sql_identifier) AS schema_owner,
1469 CAST(null AS sql_identifier) AS default_character_set_catalog,
1470 CAST(null AS sql_identifier) AS default_character_set_schema,
1471 CAST(null AS sql_identifier) AS default_character_set_name,
1472 CAST(null AS character_data) AS sql_path
1473 FROM pg_namespace n, pg_authid u
1474 WHERE n.nspowner = u.oid AND pg_has_role(n.nspowner, 'USAGE');
1476 GRANT SELECT ON schemata TO PUBLIC;
1484 CREATE VIEW sequences AS
1485 SELECT CAST(current_database() AS sql_identifier) AS sequence_catalog,
1486 CAST(nc.nspname AS sql_identifier) AS sequence_schema,
1487 CAST(c.relname AS sql_identifier) AS sequence_name,
1488 CAST('bigint' AS character_data) AS data_type,
1489 CAST(64 AS cardinal_number) AS numeric_precision,
1490 CAST(2 AS cardinal_number) AS numeric_precision_radix,
1491 CAST(0 AS cardinal_number) AS numeric_scale,
1492 -- XXX: The following could be improved if we had LATERAL.
1493 CAST((pg_sequence_parameters(c.oid)).start_value AS character_data) AS start_value,
1494 CAST((pg_sequence_parameters(c.oid)).minimum_value AS character_data) AS minimum_value,
1495 CAST((pg_sequence_parameters(c.oid)).maximum_value AS character_data) AS maximum_value,
1496 CAST((pg_sequence_parameters(c.oid)).increment AS character_data) AS increment,
1497 CAST(CASE WHEN (pg_sequence_parameters(c.oid)).cycle_option THEN 'YES' ELSE 'NO' END AS yes_or_no) AS cycle_option
1498 FROM pg_namespace nc, pg_class c
1499 WHERE c.relnamespace = nc.oid
1501 AND (NOT pg_is_other_temp_schema(nc.oid))
1502 AND (pg_has_role(c.relowner, 'USAGE')
1503 OR has_sequence_privilege(c.oid, 'SELECT, UPDATE, USAGE') );
1505 GRANT SELECT ON sequences TO PUBLIC;
1510 * SQL_FEATURES table
1513 CREATE TABLE sql_features (
1514 feature_id character_data,
1515 feature_name character_data,
1516 sub_feature_id character_data,
1517 sub_feature_name character_data,
1518 is_supported yes_or_no,
1519 is_verified_by character_data,
1520 comments character_data
1523 -- Will be filled with external data by initdb.
1525 GRANT SELECT ON sql_features TO PUBLIC;
1530 * SQL_IMPLEMENTATION_INFO table
1533 -- Note: Implementation information items are defined in ISO/IEC 9075-3:2008,
1536 CREATE TABLE sql_implementation_info (
1537 implementation_info_id character_data,
1538 implementation_info_name character_data,
1539 integer_value cardinal_number,
1540 character_value character_data,
1541 comments character_data
1544 INSERT INTO sql_implementation_info VALUES ('10003', 'CATALOG NAME', NULL, 'Y', NULL);
1545 INSERT INTO sql_implementation_info VALUES ('10004', 'COLLATING SEQUENCE', NULL, (SELECT default_collate_name FROM character_sets), NULL);
1546 INSERT INTO sql_implementation_info VALUES ('23', 'CURSOR COMMIT BEHAVIOR', 1, NULL, 'close cursors and retain prepared statements');
1547 INSERT INTO sql_implementation_info VALUES ('2', 'DATA SOURCE NAME', NULL, '', NULL);
1548 INSERT INTO sql_implementation_info VALUES ('17', 'DBMS NAME', NULL, (select trim(trailing ' ' from substring(version() from '^[^0-9]*'))), NULL);
1549 INSERT INTO sql_implementation_info VALUES ('18', 'DBMS VERSION', NULL, '???', NULL); -- filled by initdb
1550 INSERT INTO sql_implementation_info VALUES ('26', 'DEFAULT TRANSACTION ISOLATION', 2, NULL, 'READ COMMITTED; user-settable');
1551 INSERT INTO sql_implementation_info VALUES ('28', 'IDENTIFIER CASE', 3, NULL, 'stored in mixed case - case sensitive');
1552 INSERT INTO sql_implementation_info VALUES ('85', 'NULL COLLATION', 0, NULL, 'nulls higher than non-nulls');
1553 INSERT INTO sql_implementation_info VALUES ('13', 'SERVER NAME', NULL, '', NULL);
1554 INSERT INTO sql_implementation_info VALUES ('94', 'SPECIAL CHARACTERS', NULL, '', 'all non-ASCII characters allowed');
1555 INSERT INTO sql_implementation_info VALUES ('46', 'TRANSACTION CAPABLE', 2, NULL, 'both DML and DDL');
1557 GRANT SELECT ON sql_implementation_info TO PUBLIC;
1561 * SQL_LANGUAGES table
1562 * apparently removed in SQL:2008
1565 CREATE TABLE sql_languages (
1566 sql_language_source character_data,
1567 sql_language_year character_data,
1568 sql_language_conformance character_data,
1569 sql_language_integrity character_data,
1570 sql_language_implementation character_data,
1571 sql_language_binding_style character_data,
1572 sql_language_programming_language character_data
1575 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'DIRECT', NULL);
1576 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
1577 INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'DIRECT', NULL);
1578 INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
1580 GRANT SELECT ON sql_languages TO PUBLIC;
1585 * SQL_PACKAGES table
1588 CREATE TABLE sql_packages (
1589 feature_id character_data,
1590 feature_name character_data,
1591 is_supported yes_or_no,
1592 is_verified_by character_data,
1593 comments character_data
1596 INSERT INTO sql_packages VALUES ('PKG000', 'Core', 'NO', NULL, '');
1597 INSERT INTO sql_packages VALUES ('PKG001', 'Enhanced datetime facilities', 'YES', NULL, '');
1598 INSERT INTO sql_packages VALUES ('PKG002', 'Enhanced integrity management', 'NO', NULL, '');
1599 INSERT INTO sql_packages VALUES ('PKG003', 'OLAP facilities', 'NO', NULL, '');
1600 INSERT INTO sql_packages VALUES ('PKG004', 'PSM', 'NO', NULL, 'PL/pgSQL is similar.');
1601 INSERT INTO sql_packages VALUES ('PKG005', 'CLI', 'NO', NULL, 'ODBC is similar.');
1602 INSERT INTO sql_packages VALUES ('PKG006', 'Basic object support', 'NO', NULL, '');
1603 INSERT INTO sql_packages VALUES ('PKG007', 'Enhanced object support', 'NO', NULL, '');
1604 INSERT INTO sql_packages VALUES ('PKG008', 'Active database', 'NO', NULL, '');
1605 INSERT INTO sql_packages VALUES ('PKG010', 'OLAP', 'NO', NULL, 'NO');
1607 GRANT SELECT ON sql_packages TO PUBLIC;
1615 CREATE TABLE sql_parts (
1616 feature_id character_data,
1617 feature_name character_data,
1618 is_supported yes_or_no,
1619 is_verified_by character_data,
1620 comments character_data
1623 INSERT INTO sql_parts VALUES ('1', 'Framework (SQL/Framework)', 'NO', NULL, '');
1624 INSERT INTO sql_parts VALUES ('2', 'Foundation (SQL/Foundation)', 'NO', NULL, '');
1625 INSERT INTO sql_parts VALUES ('3', 'Call-Level Interface (SQL/CLI)', 'NO', NULL, '');
1626 INSERT INTO sql_parts VALUES ('4', 'Persistent Stored Modules (SQL/PSM)', 'NO', NULL, '');
1627 INSERT INTO sql_parts VALUES ('9', 'Management of External Data (SQL/MED)', 'NO', NULL, '');
1628 INSERT INTO sql_parts VALUES ('10', 'Object Language Bindings (SQL/OLB)', 'NO', NULL, '');
1629 INSERT INTO sql_parts VALUES ('11', 'Information and Definition Schema (SQL/Schemata)', 'NO', NULL, '');
1630 INSERT INTO sql_parts VALUES ('13', 'Routines and Types Using the Java Programming Language (SQL/JRT)', 'NO', NULL, '');
1631 INSERT INTO sql_parts VALUES ('14', 'XML-Related Specifications (SQL/XML)', 'YES', NULL, '');
1639 -- Note: Sizing items are defined in ISO/IEC 9075-3:2008, clause 9.2.
1641 CREATE TABLE sql_sizing (
1642 sizing_id cardinal_number,
1643 sizing_name character_data,
1644 supported_value cardinal_number,
1645 comments character_data
1648 INSERT INTO sql_sizing VALUES (34, 'MAXIMUM CATALOG NAME LENGTH', 63, NULL);
1649 INSERT INTO sql_sizing VALUES (30, 'MAXIMUM COLUMN NAME LENGTH', 63, NULL);
1650 INSERT INTO sql_sizing VALUES (97, 'MAXIMUM COLUMNS IN GROUP BY', 0, NULL);
1651 INSERT INTO sql_sizing VALUES (99, 'MAXIMUM COLUMNS IN ORDER BY', 0, NULL);
1652 INSERT INTO sql_sizing VALUES (100, 'MAXIMUM COLUMNS IN SELECT', 1664, NULL); -- match MaxTupleAttributeNumber
1653 INSERT INTO sql_sizing VALUES (101, 'MAXIMUM COLUMNS IN TABLE', 1600, NULL); -- match MaxHeapAttributeNumber
1654 INSERT INTO sql_sizing VALUES (1, 'MAXIMUM CONCURRENT ACTIVITIES', 0, NULL);
1655 INSERT INTO sql_sizing VALUES (31, 'MAXIMUM CURSOR NAME LENGTH', 63, NULL);
1656 INSERT INTO sql_sizing VALUES (0, 'MAXIMUM DRIVER CONNECTIONS', NULL, NULL);
1657 INSERT INTO sql_sizing VALUES (10005, 'MAXIMUM IDENTIFIER LENGTH', 63, NULL);
1658 INSERT INTO sql_sizing VALUES (32, 'MAXIMUM SCHEMA NAME LENGTH', 63, NULL);
1659 INSERT INTO sql_sizing VALUES (20000, 'MAXIMUM STATEMENT OCTETS', 0, NULL);
1660 INSERT INTO sql_sizing VALUES (20001, 'MAXIMUM STATEMENT OCTETS DATA', 0, NULL);
1661 INSERT INTO sql_sizing VALUES (20002, 'MAXIMUM STATEMENT OCTETS SCHEMA', 0, NULL);
1662 INSERT INTO sql_sizing VALUES (35, 'MAXIMUM TABLE NAME LENGTH', 63, NULL);
1663 INSERT INTO sql_sizing VALUES (106, 'MAXIMUM TABLES IN SELECT', 0, NULL);
1664 INSERT INTO sql_sizing VALUES (107, 'MAXIMUM USER NAME LENGTH', 63, NULL);
1665 INSERT INTO sql_sizing VALUES (25000, 'MAXIMUM CURRENT DEFAULT TRANSFORM GROUP LENGTH', NULL, NULL);
1666 INSERT INTO sql_sizing VALUES (25001, 'MAXIMUM CURRENT TRANSFORM GROUP LENGTH', NULL, NULL);
1667 INSERT INTO sql_sizing VALUES (25002, 'MAXIMUM CURRENT PATH LENGTH', 0, NULL);
1668 INSERT INTO sql_sizing VALUES (25003, 'MAXIMUM CURRENT ROLE LENGTH', NULL, NULL);
1669 INSERT INTO sql_sizing VALUES (25004, 'MAXIMUM SESSION USER LENGTH', 63, NULL);
1670 INSERT INTO sql_sizing VALUES (25005, 'MAXIMUM SYSTEM USER LENGTH', 63, NULL);
1673 SET supported_value = (SELECT typlen-1 FROM pg_catalog.pg_type WHERE typname = 'name'),
1674 comments = 'Might be less, depending on character set.'
1675 WHERE supported_value = 63;
1677 GRANT SELECT ON sql_sizing TO PUBLIC;
1682 * SQL_SIZING_PROFILES table
1685 -- The data in this table are defined by various profiles of SQL.
1686 -- Since we don't have any information about such profiles, we provide
1689 CREATE TABLE sql_sizing_profiles (
1690 sizing_id cardinal_number,
1691 sizing_name character_data,
1692 profile_id character_data,
1693 required_value cardinal_number,
1694 comments character_data
1697 GRANT SELECT ON sql_sizing_profiles TO PUBLIC;
1702 * TABLE_CONSTRAINTS view
1705 CREATE VIEW table_constraints AS
1706 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1707 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
1708 CAST(c.conname AS sql_identifier) AS constraint_name,
1709 CAST(current_database() AS sql_identifier) AS table_catalog,
1710 CAST(nr.nspname AS sql_identifier) AS table_schema,
1711 CAST(r.relname AS sql_identifier) AS table_name,
1713 CASE c.contype WHEN 'c' THEN 'CHECK'
1714 WHEN 'f' THEN 'FOREIGN KEY'
1715 WHEN 'p' THEN 'PRIMARY KEY'
1716 WHEN 'u' THEN 'UNIQUE' END
1717 AS character_data) AS constraint_type,
1718 CAST(CASE WHEN c.condeferrable THEN 'YES' ELSE 'NO' END AS yes_or_no)
1720 CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS yes_or_no)
1721 AS initially_deferred
1723 FROM pg_namespace nc,
1728 WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace
1729 AND c.conrelid = r.oid
1730 AND c.contype NOT IN ('t', 'x') -- ignore nonstandard constraints
1732 AND (NOT pg_is_other_temp_schema(nr.oid))
1733 AND (pg_has_role(r.relowner, 'USAGE')
1734 -- SELECT privilege omitted, per SQL standard
1735 OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1736 OR has_any_column_privilege(r.oid, 'INSERT, UPDATE, REFERENCES') )
1740 -- not-null constraints
1742 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1743 CAST(nr.nspname AS sql_identifier) AS constraint_schema,
1744 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
1745 CAST(current_database() AS sql_identifier) AS table_catalog,
1746 CAST(nr.nspname AS sql_identifier) AS table_schema,
1747 CAST(r.relname AS sql_identifier) AS table_name,
1748 CAST('CHECK' AS character_data) AS constraint_type,
1749 CAST('NO' AS yes_or_no) AS is_deferrable,
1750 CAST('NO' AS yes_or_no) AS initially_deferred
1752 FROM pg_namespace nr,
1756 WHERE nr.oid = r.relnamespace
1757 AND r.oid = a.attrelid
1760 AND NOT a.attisdropped
1762 AND (NOT pg_is_other_temp_schema(nr.oid))
1763 AND (pg_has_role(r.relowner, 'USAGE')
1764 -- SELECT privilege omitted, per SQL standard
1765 OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1766 OR has_any_column_privilege(r.oid, 'INSERT, UPDATE, REFERENCES') );
1768 GRANT SELECT ON table_constraints TO PUBLIC;
1773 * TABLE_METHOD_PRIVILEGES view
1776 -- feature not supported
1781 * TABLE_PRIVILEGES view
1784 CREATE VIEW table_privileges AS
1785 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1786 CAST(grantee.rolname AS sql_identifier) AS grantee,
1787 CAST(current_database() AS sql_identifier) AS table_catalog,
1788 CAST(nc.nspname AS sql_identifier) AS table_schema,
1789 CAST(c.relname AS sql_identifier) AS table_name,
1790 CAST(c.prtype AS character_data) AS privilege_type,
1793 -- object owner always has grant options
1794 pg_has_role(grantee.oid, c.relowner, 'USAGE')
1796 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable,
1797 CAST(CASE WHEN c.prtype = 'SELECT' THEN 'YES' ELSE 'NO' END AS yes_or_no) AS with_hierarchy
1800 SELECT oid, relname, relnamespace, relkind, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).* FROM pg_class
1801 ) AS c (oid, relname, relnamespace, relkind, relowner, grantor, grantee, prtype, grantable),
1803 pg_authid u_grantor,
1805 SELECT oid, rolname FROM pg_authid
1807 SELECT 0::oid, 'PUBLIC'
1808 ) AS grantee (oid, rolname)
1810 WHERE c.relnamespace = nc.oid
1811 AND c.relkind IN ('r', 'v')
1812 AND c.grantee = grantee.oid
1813 AND c.grantor = u_grantor.oid
1814 AND c.prtype IN ('INSERT', 'SELECT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER')
1815 AND (pg_has_role(u_grantor.oid, 'USAGE')
1816 OR pg_has_role(grantee.oid, 'USAGE')
1817 OR grantee.rolname = 'PUBLIC');
1819 GRANT SELECT ON table_privileges TO PUBLIC;
1824 * ROLE_TABLE_GRANTS view
1827 CREATE VIEW role_table_grants AS
1836 FROM table_privileges
1837 WHERE grantor IN (SELECT role_name FROM enabled_roles)
1838 OR grantee IN (SELECT role_name FROM enabled_roles);
1840 GRANT SELECT ON role_table_grants TO PUBLIC;
1848 CREATE VIEW tables AS
1849 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
1850 CAST(nc.nspname AS sql_identifier) AS table_schema,
1851 CAST(c.relname AS sql_identifier) AS table_name,
1854 CASE WHEN nc.oid = pg_my_temp_schema() THEN 'LOCAL TEMPORARY'
1855 WHEN c.relkind = 'r' THEN 'BASE TABLE'
1856 WHEN c.relkind = 'v' THEN 'VIEW'
1857 WHEN c.relkind = 'f' THEN 'FOREIGN TABLE'
1859 AS character_data) AS table_type,
1861 CAST(null AS sql_identifier) AS self_referencing_column_name,
1862 CAST(null AS character_data) AS reference_generation,
1864 CAST(CASE WHEN t.typname IS NOT NULL THEN current_database() ELSE null END AS sql_identifier) AS user_defined_type_catalog,
1865 CAST(nt.nspname AS sql_identifier) AS user_defined_type_schema,
1866 CAST(t.typname AS sql_identifier) AS user_defined_type_name,
1868 CAST(CASE WHEN c.relkind = 'r'
1870 AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '3' AND is_instead))
1871 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_insertable_into,
1873 CAST(CASE WHEN t.typname IS NOT NULL THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_typed,
1874 CAST(null AS character_data) AS commit_action
1876 FROM pg_namespace nc JOIN pg_class c ON (nc.oid = c.relnamespace)
1877 LEFT JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON (c.reloftype = t.oid)
1879 WHERE c.relkind IN ('r', 'v', 'f')
1880 AND (NOT pg_is_other_temp_schema(nc.oid))
1881 AND (pg_has_role(c.relowner, 'USAGE')
1882 OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1883 OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES') );
1885 GRANT SELECT ON tables TO PUBLIC;
1893 -- feature not supported
1901 -- feature not supported
1906 * TRIGGERED_UPDATE_COLUMNS view
1909 CREATE VIEW triggered_update_columns AS
1910 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1911 CAST(n.nspname AS sql_identifier) AS trigger_schema,
1912 CAST(t.tgname AS sql_identifier) AS trigger_name,
1913 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1914 CAST(n.nspname AS sql_identifier) AS event_object_schema,
1915 CAST(c.relname AS sql_identifier) AS event_object_table,
1916 CAST(a.attname AS sql_identifier) AS event_object_column
1918 FROM pg_namespace n, pg_class c, pg_trigger t,
1919 (SELECT tgoid, (ta0.tgat).x AS tgattnum, (ta0.tgat).n AS tgattpos
1920 FROM (SELECT oid AS tgoid, information_schema._pg_expandarray(tgattr) AS tgat FROM pg_trigger) AS ta0) AS ta,
1923 WHERE n.oid = c.relnamespace
1924 AND c.oid = t.tgrelid
1925 AND t.oid = ta.tgoid
1926 AND (a.attrelid, a.attnum) = (t.tgrelid, ta.tgattnum)
1927 AND NOT t.tgisinternal
1928 AND (NOT pg_is_other_temp_schema(n.oid))
1929 AND (pg_has_role(c.relowner, 'USAGE')
1930 -- SELECT privilege omitted, per SQL standard
1931 OR has_column_privilege(c.oid, a.attnum, 'INSERT, UPDATE, REFERENCES') );
1933 GRANT SELECT ON triggered_update_columns TO PUBLIC;
1938 * TRIGGER_COLUMN_USAGE view
1941 -- not tracked by PostgreSQL
1946 * TRIGGER_ROUTINE_USAGE view
1949 -- not tracked by PostgreSQL
1954 * TRIGGER_SEQUENCE_USAGE view
1957 -- not tracked by PostgreSQL
1962 * TRIGGER_TABLE_USAGE view
1965 -- not tracked by PostgreSQL
1973 CREATE VIEW triggers AS
1974 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1975 CAST(n.nspname AS sql_identifier) AS trigger_schema,
1976 CAST(t.tgname AS sql_identifier) AS trigger_name,
1977 CAST(em.text AS character_data) AS event_manipulation,
1978 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1979 CAST(n.nspname AS sql_identifier) AS event_object_schema,
1980 CAST(c.relname AS sql_identifier) AS event_object_table,
1981 CAST(null AS cardinal_number) AS action_order,
1982 -- XXX strange hacks follow
1984 CASE WHEN pg_has_role(c.relowner, 'USAGE')
1985 THEN (SELECT m[1] FROM regexp_matches(pg_get_triggerdef(t.oid), E'.{35,} WHEN \\((.+)\\) EXECUTE PROCEDURE') AS rm(m) LIMIT 1)
1987 AS character_data) AS action_condition,
1989 substring(pg_get_triggerdef(t.oid) from
1990 position('EXECUTE PROCEDURE' in substring(pg_get_triggerdef(t.oid) from 48)) + 47)
1991 AS character_data) AS action_statement,
1993 -- hard-wired reference to TRIGGER_TYPE_ROW
1994 CASE t.tgtype & 1 WHEN 1 THEN 'ROW' ELSE 'STATEMENT' END
1995 AS character_data) AS action_orientation,
1997 -- hard-wired refs to TRIGGER_TYPE_BEFORE, TRIGGER_TYPE_INSTEAD
1998 CASE t.tgtype & 66 WHEN 2 THEN 'BEFORE' WHEN 64 THEN 'INSTEAD OF' ELSE 'AFTER' END
1999 AS character_data) AS action_timing,
2000 CAST(null AS sql_identifier) AS action_reference_old_table,
2001 CAST(null AS sql_identifier) AS action_reference_new_table,
2002 CAST(null AS sql_identifier) AS action_reference_old_row,
2003 CAST(null AS sql_identifier) AS action_reference_new_row,
2004 CAST(null AS time_stamp) AS created
2006 FROM pg_namespace n, pg_class c, pg_trigger t,
2007 -- hard-wired refs to TRIGGER_TYPE_INSERT, TRIGGER_TYPE_DELETE,
2008 -- TRIGGER_TYPE_UPDATE; we intentionally omit TRIGGER_TYPE_TRUNCATE
2009 (VALUES (4, 'INSERT'),
2011 (16, 'UPDATE')) AS em (num, text)
2013 WHERE n.oid = c.relnamespace
2014 AND c.oid = t.tgrelid
2015 AND t.tgtype & em.num <> 0
2016 AND NOT t.tgisinternal
2017 AND (NOT pg_is_other_temp_schema(n.oid))
2018 AND (pg_has_role(c.relowner, 'USAGE')
2019 -- SELECT privilege omitted, per SQL standard
2020 OR has_table_privilege(c.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
2021 OR has_any_column_privilege(c.oid, 'INSERT, UPDATE, REFERENCES') );
2023 GRANT SELECT ON triggers TO PUBLIC;
2028 * UDT_PRIVILEGES view
2031 CREATE VIEW udt_privileges AS
2032 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2033 CAST(grantee.rolname AS sql_identifier) AS grantee,
2034 CAST(current_database() AS sql_identifier) AS udt_catalog,
2035 CAST(n.nspname AS sql_identifier) AS udt_schema,
2036 CAST(t.typname AS sql_identifier) AS udt_name,
2037 CAST('TYPE USAGE' AS character_data) AS privilege_type, -- sic
2040 -- object owner always has grant options
2041 pg_has_role(grantee.oid, t.typowner, 'USAGE')
2043 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2046 SELECT oid, typname, typnamespace, typtype, typowner, (aclexplode(coalesce(typacl, acldefault('T', typowner)))).* FROM pg_type
2047 ) AS t (oid, typname, typnamespace, typtype, typowner, grantor, grantee, prtype, grantable),
2049 pg_authid u_grantor,
2051 SELECT oid, rolname FROM pg_authid
2053 SELECT 0::oid, 'PUBLIC'
2054 ) AS grantee (oid, rolname)
2056 WHERE t.typnamespace = n.oid
2058 AND t.grantee = grantee.oid
2059 AND t.grantor = u_grantor.oid
2060 AND t.prtype IN ('USAGE')
2061 AND (pg_has_role(u_grantor.oid, 'USAGE')
2062 OR pg_has_role(grantee.oid, 'USAGE')
2063 OR grantee.rolname = 'PUBLIC');
2065 GRANT SELECT ON udt_privileges TO PUBLIC;
2070 * ROLE_UDT_GRANTS view
2073 CREATE VIEW role_udt_grants AS
2082 WHERE grantor IN (SELECT role_name FROM enabled_roles)
2083 OR grantee IN (SELECT role_name FROM enabled_roles);
2085 GRANT SELECT ON role_udt_grants TO PUBLIC;
2090 * USAGE_PRIVILEGES view
2093 CREATE VIEW usage_privileges AS
2096 -- Collations have no real privileges, so we represent all collations with implicit usage privilege here.
2097 SELECT CAST(u.rolname AS sql_identifier) AS grantor,
2098 CAST('PUBLIC' AS sql_identifier) AS grantee,
2099 CAST(current_database() AS sql_identifier) AS object_catalog,
2100 CAST(n.nspname AS sql_identifier) AS object_schema,
2101 CAST(c.collname AS sql_identifier) AS object_name,
2102 CAST('COLLATION' AS character_data) AS object_type,
2103 CAST('USAGE' AS character_data) AS privilege_type,
2104 CAST('NO' AS yes_or_no) AS is_grantable
2110 WHERE u.oid = c.collowner
2111 AND c.collnamespace = n.oid
2112 AND collencoding IN (-1, (SELECT encoding FROM pg_database WHERE datname = current_database()))
2117 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2118 CAST(grantee.rolname AS sql_identifier) AS grantee,
2119 CAST(current_database() AS sql_identifier) AS object_catalog,
2120 CAST(n.nspname AS sql_identifier) AS object_schema,
2121 CAST(t.typname AS sql_identifier) AS object_name,
2122 CAST('DOMAIN' AS character_data) AS object_type,
2123 CAST('USAGE' AS character_data) AS privilege_type,
2126 -- object owner always has grant options
2127 pg_has_role(grantee.oid, t.typowner, 'USAGE')
2129 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2132 SELECT oid, typname, typnamespace, typtype, typowner, (aclexplode(coalesce(typacl, acldefault('T', typowner)))).* FROM pg_type
2133 ) AS t (oid, typname, typnamespace, typtype, typowner, grantor, grantee, prtype, grantable),
2135 pg_authid u_grantor,
2137 SELECT oid, rolname FROM pg_authid
2139 SELECT 0::oid, 'PUBLIC'
2140 ) AS grantee (oid, rolname)
2142 WHERE t.typnamespace = n.oid
2144 AND t.grantee = grantee.oid
2145 AND t.grantor = u_grantor.oid
2146 AND t.prtype IN ('USAGE')
2147 AND (pg_has_role(u_grantor.oid, 'USAGE')
2148 OR pg_has_role(grantee.oid, 'USAGE')
2149 OR grantee.rolname = 'PUBLIC')
2153 /* foreign-data wrappers */
2154 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2155 CAST(grantee.rolname AS sql_identifier) AS grantee,
2156 CAST(current_database() AS sql_identifier) AS object_catalog,
2157 CAST('' AS sql_identifier) AS object_schema,
2158 CAST(fdw.fdwname AS sql_identifier) AS object_name,
2159 CAST('FOREIGN DATA WRAPPER' AS character_data) AS object_type,
2160 CAST('USAGE' AS character_data) AS privilege_type,
2163 -- object owner always has grant options
2164 pg_has_role(grantee.oid, fdw.fdwowner, 'USAGE')
2166 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2169 SELECT fdwname, fdwowner, (aclexplode(coalesce(fdwacl, acldefault('F', fdwowner)))).* FROM pg_foreign_data_wrapper
2170 ) AS fdw (fdwname, fdwowner, grantor, grantee, prtype, grantable),
2171 pg_authid u_grantor,
2173 SELECT oid, rolname FROM pg_authid
2175 SELECT 0::oid, 'PUBLIC'
2176 ) AS grantee (oid, rolname)
2178 WHERE u_grantor.oid = fdw.grantor
2179 AND grantee.oid = fdw.grantee
2180 AND fdw.prtype IN ('USAGE')
2181 AND (pg_has_role(u_grantor.oid, 'USAGE')
2182 OR pg_has_role(grantee.oid, 'USAGE')
2183 OR grantee.rolname = 'PUBLIC')
2187 /* foreign servers */
2188 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2189 CAST(grantee.rolname AS sql_identifier) AS grantee,
2190 CAST(current_database() AS sql_identifier) AS object_catalog,
2191 CAST('' AS sql_identifier) AS object_schema,
2192 CAST(srv.srvname AS sql_identifier) AS object_name,
2193 CAST('FOREIGN SERVER' AS character_data) AS object_type,
2194 CAST('USAGE' AS character_data) AS privilege_type,
2197 -- object owner always has grant options
2198 pg_has_role(grantee.oid, srv.srvowner, 'USAGE')
2200 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2203 SELECT srvname, srvowner, (aclexplode(coalesce(srvacl, acldefault('S', srvowner)))).* FROM pg_foreign_server
2204 ) AS srv (srvname, srvowner, grantor, grantee, prtype, grantable),
2205 pg_authid u_grantor,
2207 SELECT oid, rolname FROM pg_authid
2209 SELECT 0::oid, 'PUBLIC'
2210 ) AS grantee (oid, rolname)
2212 WHERE u_grantor.oid = srv.grantor
2213 AND grantee.oid = srv.grantee
2214 AND srv.prtype IN ('USAGE')
2215 AND (pg_has_role(u_grantor.oid, 'USAGE')
2216 OR pg_has_role(grantee.oid, 'USAGE')
2217 OR grantee.rolname = 'PUBLIC')
2222 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2223 CAST(grantee.rolname AS sql_identifier) AS grantee,
2224 CAST(current_database() AS sql_identifier) AS object_catalog,
2225 CAST(n.nspname AS sql_identifier) AS object_schema,
2226 CAST(c.relname AS sql_identifier) AS object_name,
2227 CAST('SEQUENCE' AS character_data) AS object_type,
2228 CAST('USAGE' AS character_data) AS privilege_type,
2231 -- object owner always has grant options
2232 pg_has_role(grantee.oid, c.relowner, 'USAGE')
2234 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2237 SELECT oid, relname, relnamespace, relkind, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).* FROM pg_class
2238 ) AS c (oid, relname, relnamespace, relkind, relowner, grantor, grantee, prtype, grantable),
2240 pg_authid u_grantor,
2242 SELECT oid, rolname FROM pg_authid
2244 SELECT 0::oid, 'PUBLIC'
2245 ) AS grantee (oid, rolname)
2247 WHERE c.relnamespace = n.oid
2249 AND c.grantee = grantee.oid
2250 AND c.grantor = u_grantor.oid
2251 AND c.prtype IN ('USAGE')
2252 AND (pg_has_role(u_grantor.oid, 'USAGE')
2253 OR pg_has_role(grantee.oid, 'USAGE')
2254 OR grantee.rolname = 'PUBLIC');
2256 GRANT SELECT ON usage_privileges TO PUBLIC;
2261 * ROLE_USAGE_GRANTS view
2264 CREATE VIEW role_usage_grants AS
2273 FROM usage_privileges
2274 WHERE grantor IN (SELECT role_name FROM enabled_roles)
2275 OR grantee IN (SELECT role_name FROM enabled_roles);
2277 GRANT SELECT ON role_usage_grants TO PUBLIC;
2282 * USER_DEFINED_TYPES view
2285 CREATE VIEW user_defined_types AS
2286 SELECT CAST(current_database() AS sql_identifier) AS user_defined_type_catalog,
2287 CAST(n.nspname AS sql_identifier) AS user_defined_type_schema,
2288 CAST(c.relname AS sql_identifier) AS user_defined_type_name,
2289 CAST('STRUCTURED' AS character_data) AS user_defined_type_category,
2290 CAST('YES' AS yes_or_no) AS is_instantiable,
2291 CAST(null AS yes_or_no) AS is_final,
2292 CAST(null AS character_data) AS ordering_form,
2293 CAST(null AS character_data) AS ordering_category,
2294 CAST(null AS sql_identifier) AS ordering_routine_catalog,
2295 CAST(null AS sql_identifier) AS ordering_routine_schema,
2296 CAST(null AS sql_identifier) AS ordering_routine_name,
2297 CAST(null AS character_data) AS reference_type,
2298 CAST(null AS character_data) AS data_type,
2299 CAST(null AS cardinal_number) AS character_maximum_length,
2300 CAST(null AS cardinal_number) AS character_octet_length,
2301 CAST(null AS sql_identifier) AS character_set_catalog,
2302 CAST(null AS sql_identifier) AS character_set_schema,
2303 CAST(null AS sql_identifier) AS character_set_name,
2304 CAST(null AS sql_identifier) AS collation_catalog,
2305 CAST(null AS sql_identifier) AS collation_schema,
2306 CAST(null AS sql_identifier) AS collation_name,
2307 CAST(null AS cardinal_number) AS numeric_precision,
2308 CAST(null AS cardinal_number) AS numeric_precision_radix,
2309 CAST(null AS cardinal_number) AS numeric_scale,
2310 CAST(null AS cardinal_number) AS datetime_precision,
2311 CAST(null AS character_data) AS interval_type,
2312 CAST(null AS cardinal_number) AS interval_precision,
2313 CAST(null AS sql_identifier) AS source_dtd_identifier,
2314 CAST(null AS sql_identifier) AS ref_dtd_identifier
2316 FROM pg_namespace n, pg_class c, pg_type t
2318 WHERE n.oid = c.relnamespace
2319 AND t.typrelid = c.oid
2321 AND (pg_has_role(t.typowner, 'USAGE')
2322 OR has_type_privilege(t.oid, 'USAGE'));
2324 GRANT SELECT ON user_defined_types TO PUBLIC;
2332 CREATE VIEW view_column_usage AS
2334 CAST(current_database() AS sql_identifier) AS view_catalog,
2335 CAST(nv.nspname AS sql_identifier) AS view_schema,
2336 CAST(v.relname AS sql_identifier) AS view_name,
2337 CAST(current_database() AS sql_identifier) AS table_catalog,
2338 CAST(nt.nspname AS sql_identifier) AS table_schema,
2339 CAST(t.relname AS sql_identifier) AS table_name,
2340 CAST(a.attname AS sql_identifier) AS column_name
2342 FROM pg_namespace nv, pg_class v, pg_depend dv,
2343 pg_depend dt, pg_class t, pg_namespace nt,
2346 WHERE nv.oid = v.relnamespace
2348 AND v.oid = dv.refobjid
2349 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2350 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2351 AND dv.deptype = 'i'
2352 AND dv.objid = dt.objid
2353 AND dv.refobjid <> dt.refobjid
2354 AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2355 AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2356 AND dt.refobjid = t.oid
2357 AND t.relnamespace = nt.oid
2358 AND t.relkind IN ('r', 'v', 'f')
2359 AND t.oid = a.attrelid
2360 AND dt.refobjsubid = a.attnum
2361 AND pg_has_role(t.relowner, 'USAGE');
2363 GRANT SELECT ON view_column_usage TO PUBLIC;
2368 * VIEW_ROUTINE_USAGE
2371 CREATE VIEW view_routine_usage AS
2373 CAST(current_database() AS sql_identifier) AS table_catalog,
2374 CAST(nv.nspname AS sql_identifier) AS table_schema,
2375 CAST(v.relname AS sql_identifier) AS table_name,
2376 CAST(current_database() AS sql_identifier) AS specific_catalog,
2377 CAST(np.nspname AS sql_identifier) AS specific_schema,
2378 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name
2380 FROM pg_namespace nv, pg_class v, pg_depend dv,
2381 pg_depend dp, pg_proc p, pg_namespace np
2383 WHERE nv.oid = v.relnamespace
2385 AND v.oid = dv.refobjid
2386 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2387 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2388 AND dv.deptype = 'i'
2389 AND dv.objid = dp.objid
2390 AND dp.classid = 'pg_catalog.pg_rewrite'::regclass
2391 AND dp.refclassid = 'pg_catalog.pg_proc'::regclass
2392 AND dp.refobjid = p.oid
2393 AND p.pronamespace = np.oid
2394 AND pg_has_role(p.proowner, 'USAGE');
2396 GRANT SELECT ON view_routine_usage TO PUBLIC;
2404 CREATE VIEW view_table_usage AS
2406 CAST(current_database() AS sql_identifier) AS view_catalog,
2407 CAST(nv.nspname AS sql_identifier) AS view_schema,
2408 CAST(v.relname AS sql_identifier) AS view_name,
2409 CAST(current_database() AS sql_identifier) AS table_catalog,
2410 CAST(nt.nspname AS sql_identifier) AS table_schema,
2411 CAST(t.relname AS sql_identifier) AS table_name
2413 FROM pg_namespace nv, pg_class v, pg_depend dv,
2414 pg_depend dt, pg_class t, pg_namespace nt
2416 WHERE nv.oid = v.relnamespace
2418 AND v.oid = dv.refobjid
2419 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2420 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2421 AND dv.deptype = 'i'
2422 AND dv.objid = dt.objid
2423 AND dv.refobjid <> dt.refobjid
2424 AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2425 AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2426 AND dt.refobjid = t.oid
2427 AND t.relnamespace = nt.oid
2428 AND t.relkind IN ('r', 'v', 'f')
2429 AND pg_has_role(t.relowner, 'USAGE');
2431 GRANT SELECT ON view_table_usage TO PUBLIC;
2439 CREATE VIEW views AS
2440 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
2441 CAST(nc.nspname AS sql_identifier) AS table_schema,
2442 CAST(c.relname AS sql_identifier) AS table_name,
2445 CASE WHEN pg_has_role(c.relowner, 'USAGE')
2446 THEN pg_get_viewdef(c.oid)
2448 AS character_data) AS view_definition,
2450 CAST('NONE' AS character_data) AS check_option,
2453 CASE WHEN EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '2' AND is_instead)
2454 AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '4' AND is_instead)
2455 THEN 'YES' ELSE 'NO' END
2456 AS yes_or_no) AS is_updatable,
2459 CASE WHEN EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '3' AND is_instead)
2460 THEN 'YES' ELSE 'NO' END
2461 AS yes_or_no) AS is_insertable_into,
2464 -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_UPDATE
2465 CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 81 = 81)
2466 THEN 'YES' ELSE 'NO' END
2467 AS yes_or_no) AS is_trigger_updatable,
2470 -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_DELETE
2471 CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 73 = 73)
2472 THEN 'YES' ELSE 'NO' END
2473 AS yes_or_no) AS is_trigger_deletable,
2476 -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_INSERT
2477 CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 69 = 69)
2478 THEN 'YES' ELSE 'NO' END
2479 AS yes_or_no) AS is_trigger_insertable_into
2481 FROM pg_namespace nc, pg_class c
2483 WHERE c.relnamespace = nc.oid
2485 AND (NOT pg_is_other_temp_schema(nc.oid))
2486 AND (pg_has_role(c.relowner, 'USAGE')
2487 OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
2488 OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES') );
2490 GRANT SELECT ON views TO PUBLIC;
2493 -- The following views have dependencies that force them to appear out of order.
2497 * DATA_TYPE_PRIVILEGES view
2500 CREATE VIEW data_type_privileges AS
2501 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2502 CAST(x.objschema AS sql_identifier) AS object_schema,
2503 CAST(x.objname AS sql_identifier) AS object_name,
2504 CAST(x.objtype AS character_data) AS object_type,
2505 CAST(x.objdtdid AS sql_identifier) AS dtd_identifier
2509 SELECT udt_schema, udt_name, 'USER-DEFINED TYPE'::text, dtd_identifier FROM attributes
2511 SELECT table_schema, table_name, 'TABLE'::text, dtd_identifier FROM columns
2513 SELECT domain_schema, domain_name, 'DOMAIN'::text, dtd_identifier FROM domains
2515 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM parameters
2517 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM routines
2518 ) AS x (objschema, objname, objtype, objdtdid);
2520 GRANT SELECT ON data_type_privileges TO PUBLIC;
2525 * ELEMENT_TYPES view
2528 CREATE VIEW element_types AS
2529 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2530 CAST(n.nspname AS sql_identifier) AS object_schema,
2531 CAST(x.objname AS sql_identifier) AS object_name,
2532 CAST(x.objtype AS character_data) AS object_type,
2533 CAST(x.objdtdid AS sql_identifier) AS collection_type_identifier,
2535 CASE WHEN nbt.nspname = 'pg_catalog' THEN format_type(bt.oid, null)
2536 ELSE 'USER-DEFINED' END AS character_data) AS data_type,
2538 CAST(null AS cardinal_number) AS character_maximum_length,
2539 CAST(null AS cardinal_number) AS character_octet_length,
2540 CAST(null AS sql_identifier) AS character_set_catalog,
2541 CAST(null AS sql_identifier) AS character_set_schema,
2542 CAST(null AS sql_identifier) AS character_set_name,
2543 CAST(CASE WHEN nco.nspname IS NOT NULL THEN current_database() END AS sql_identifier) AS collation_catalog,
2544 CAST(nco.nspname AS sql_identifier) AS collation_schema,
2545 CAST(co.collname AS sql_identifier) AS collation_name,
2546 CAST(null AS cardinal_number) AS numeric_precision,
2547 CAST(null AS cardinal_number) AS numeric_precision_radix,
2548 CAST(null AS cardinal_number) AS numeric_scale,
2549 CAST(null AS cardinal_number) AS datetime_precision,
2550 CAST(null AS character_data) AS interval_type,
2551 CAST(null AS cardinal_number) AS interval_precision,
2553 CAST(null AS character_data) AS domain_default, -- XXX maybe a bug in the standard
2555 CAST(current_database() AS sql_identifier) AS udt_catalog,
2556 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
2557 CAST(bt.typname AS sql_identifier) AS udt_name,
2559 CAST(null AS sql_identifier) AS scope_catalog,
2560 CAST(null AS sql_identifier) AS scope_schema,
2561 CAST(null AS sql_identifier) AS scope_name,
2563 CAST(null AS cardinal_number) AS maximum_cardinality,
2564 CAST('a' || CAST(x.objdtdid AS text) AS sql_identifier) AS dtd_identifier
2566 FROM pg_namespace n, pg_type at, pg_namespace nbt, pg_type bt,
2568 /* columns, attributes */
2569 SELECT c.relnamespace, CAST(c.relname AS sql_identifier),
2570 CASE WHEN c.relkind = 'c' THEN 'USER-DEFINED TYPE'::text ELSE 'TABLE'::text END,
2571 a.attnum, a.atttypid, a.attcollation
2572 FROM pg_class c, pg_attribute a
2573 WHERE c.oid = a.attrelid
2574 AND c.relkind IN ('r', 'v', 'f', 'c')
2575 AND attnum > 0 AND NOT attisdropped
2580 SELECT t.typnamespace, CAST(t.typname AS sql_identifier),
2581 'DOMAIN'::text, 1, t.typbasetype, t.typcollation
2583 WHERE t.typtype = 'd'
2588 SELECT pronamespace, CAST(proname || '_' || CAST(oid AS text) AS sql_identifier),
2589 'ROUTINE'::text, (ss.x).n, (ss.x).x, 0
2590 FROM (SELECT p.pronamespace, p.proname, p.oid,
2591 _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
2592 FROM pg_proc p) AS ss
2597 SELECT p.pronamespace, CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier),
2598 'ROUTINE'::text, 0, p.prorettype, 0
2601 ) AS x (objschema, objname, objtype, objdtdid, objtypeid, objcollation)
2602 LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid))
2603 ON x.objcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default')
2605 WHERE n.oid = x.objschema
2606 AND at.oid = x.objtypeid
2607 AND (at.typelem <> 0 AND at.typlen = -1)
2608 AND at.typelem = bt.oid
2609 AND nbt.oid = bt.typnamespace
2611 AND (n.nspname, x.objname, x.objtype, CAST(x.objdtdid AS sql_identifier)) IN
2612 ( SELECT object_schema, object_name, object_type, dtd_identifier
2613 FROM data_type_privileges );
2615 GRANT SELECT ON element_types TO PUBLIC;
2618 -- SQL/MED views; these use section numbers from part 9 of the standard.
2620 /* Base view for foreign table columns */
2621 CREATE VIEW _pg_foreign_table_columns AS
2626 FROM pg_foreign_table t, pg_authid u, pg_namespace n, pg_class c,
2628 WHERE u.oid = c.relowner
2629 AND (pg_has_role(c.relowner, 'USAGE')
2630 OR has_column_privilege(c.oid, a.attnum, 'SELECT, INSERT, UPDATE, REFERENCES'))
2631 AND n.oid = c.relnamespace
2632 AND c.oid = t.ftrelid
2634 AND a.attrelid = c.oid
2639 * COLUMN_OPTIONS view
2641 CREATE VIEW column_options AS
2642 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
2643 c.nspname AS table_schema,
2644 c.relname AS table_name,
2645 c.attname AS column_name,
2646 CAST((pg_options_to_table(c.attfdwoptions)).option_name AS sql_identifier) AS option_name,
2647 CAST((pg_options_to_table(c.attfdwoptions)).option_value AS character_data) AS option_value
2648 FROM _pg_foreign_table_columns c;
2650 GRANT SELECT ON column_options TO PUBLIC;
2653 /* Base view for foreign-data wrappers */
2654 CREATE VIEW _pg_foreign_data_wrappers AS
2658 CAST(current_database() AS sql_identifier) AS foreign_data_wrapper_catalog,
2659 CAST(fdwname AS sql_identifier) AS foreign_data_wrapper_name,
2660 CAST(u.rolname AS sql_identifier) AS authorization_identifier,
2661 CAST('c' AS character_data) AS foreign_data_wrapper_language
2662 FROM pg_foreign_data_wrapper w, pg_authid u
2663 WHERE u.oid = w.fdwowner
2664 AND (pg_has_role(fdwowner, 'USAGE')
2665 OR has_foreign_data_wrapper_privilege(w.oid, 'USAGE'));
2670 * FOREIGN_DATA_WRAPPER_OPTIONS view
2672 CREATE VIEW foreign_data_wrapper_options AS
2673 SELECT foreign_data_wrapper_catalog,
2674 foreign_data_wrapper_name,
2675 CAST((pg_options_to_table(w.fdwoptions)).option_name AS sql_identifier) AS option_name,
2676 CAST((pg_options_to_table(w.fdwoptions)).option_value AS character_data) AS option_value
2677 FROM _pg_foreign_data_wrappers w;
2679 GRANT SELECT ON foreign_data_wrapper_options TO PUBLIC;
2684 * FOREIGN_DATA_WRAPPERS view
2686 CREATE VIEW foreign_data_wrappers AS
2687 SELECT foreign_data_wrapper_catalog,
2688 foreign_data_wrapper_name,
2689 authorization_identifier,
2690 CAST(NULL AS character_data) AS library_name,
2691 foreign_data_wrapper_language
2692 FROM _pg_foreign_data_wrappers w;
2694 GRANT SELECT ON foreign_data_wrappers TO PUBLIC;
2697 /* Base view for foreign servers */
2698 CREATE VIEW _pg_foreign_servers AS
2701 CAST(current_database() AS sql_identifier) AS foreign_server_catalog,
2702 CAST(srvname AS sql_identifier) AS foreign_server_name,
2703 CAST(current_database() AS sql_identifier) AS foreign_data_wrapper_catalog,
2704 CAST(w.fdwname AS sql_identifier) AS foreign_data_wrapper_name,
2705 CAST(srvtype AS character_data) AS foreign_server_type,
2706 CAST(srvversion AS character_data) AS foreign_server_version,
2707 CAST(u.rolname AS sql_identifier) AS authorization_identifier
2708 FROM pg_foreign_server s, pg_foreign_data_wrapper w, pg_authid u
2709 WHERE w.oid = s.srvfdw
2710 AND u.oid = s.srvowner
2711 AND (pg_has_role(s.srvowner, 'USAGE')
2712 OR has_server_privilege(s.oid, 'USAGE'));
2717 * FOREIGN_SERVER_OPTIONS view
2719 CREATE VIEW foreign_server_options AS
2720 SELECT foreign_server_catalog,
2721 foreign_server_name,
2722 CAST((pg_options_to_table(s.srvoptions)).option_name AS sql_identifier) AS option_name,
2723 CAST((pg_options_to_table(s.srvoptions)).option_value AS character_data) AS option_value
2724 FROM _pg_foreign_servers s;
2726 GRANT SELECT ON TABLE foreign_server_options TO PUBLIC;
2731 * FOREIGN_SERVERS view
2733 CREATE VIEW foreign_servers AS
2734 SELECT foreign_server_catalog,
2735 foreign_server_name,
2736 foreign_data_wrapper_catalog,
2737 foreign_data_wrapper_name,
2738 foreign_server_type,
2739 foreign_server_version,
2740 authorization_identifier
2741 FROM _pg_foreign_servers;
2743 GRANT SELECT ON foreign_servers TO PUBLIC;
2746 /* Base view for foreign tables */
2747 CREATE VIEW _pg_foreign_tables AS
2749 CAST(current_database() AS sql_identifier) AS foreign_table_catalog,
2750 n.nspname AS foreign_table_schema,
2751 c.relname AS foreign_table_name,
2752 t.ftoptions AS ftoptions,
2753 CAST(current_database() AS sql_identifier) AS foreign_server_catalog,
2754 CAST(srvname AS sql_identifier) AS foreign_server_name,
2755 CAST(u.rolname AS sql_identifier) AS authorization_identifier
2756 FROM pg_foreign_table t, pg_foreign_server s, pg_foreign_data_wrapper w,
2757 pg_authid u, pg_namespace n, pg_class c
2758 WHERE w.oid = s.srvfdw
2759 AND u.oid = c.relowner
2760 AND (pg_has_role(c.relowner, 'USAGE')
2761 OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
2762 OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'))
2763 AND n.oid = c.relnamespace
2764 AND c.oid = t.ftrelid
2766 AND s.oid = t.ftserver;
2771 * FOREIGN_TABLE_OPTIONS view
2773 CREATE VIEW foreign_table_options AS
2774 SELECT foreign_table_catalog,
2775 foreign_table_schema,
2777 CAST((pg_options_to_table(t.ftoptions)).option_name AS sql_identifier) AS option_name,
2778 CAST((pg_options_to_table(t.ftoptions)).option_value AS character_data) AS option_value
2779 FROM _pg_foreign_tables t;
2781 GRANT SELECT ON TABLE foreign_table_options TO PUBLIC;
2786 * FOREIGN_TABLES view
2788 CREATE VIEW foreign_tables AS
2789 SELECT foreign_table_catalog,
2790 foreign_table_schema,
2792 foreign_server_catalog,
2794 FROM _pg_foreign_tables;
2796 GRANT SELECT ON foreign_tables TO PUBLIC;
2800 /* Base view for user mappings */
2801 CREATE VIEW _pg_user_mappings AS
2805 CAST(COALESCE(u.rolname,'PUBLIC') AS sql_identifier ) AS authorization_identifier,
2806 s.foreign_server_catalog,
2807 s.foreign_server_name,
2808 s.authorization_identifier AS srvowner
2809 FROM pg_user_mapping um LEFT JOIN pg_authid u ON (u.oid = um.umuser),
2810 _pg_foreign_servers s
2811 WHERE s.oid = um.umserver;
2816 * USER_MAPPING_OPTIONS view
2818 CREATE VIEW user_mapping_options AS
2819 SELECT authorization_identifier,
2820 foreign_server_catalog,
2821 foreign_server_name,
2822 CAST((pg_options_to_table(um.umoptions)).option_name AS sql_identifier) AS option_name,
2823 CAST(CASE WHEN (umuser <> 0 AND authorization_identifier = current_user)
2824 OR (umuser = 0 AND pg_has_role(srvowner, 'USAGE'))
2825 OR (SELECT rolsuper FROM pg_authid WHERE rolname = current_user) THEN (pg_options_to_table(um.umoptions)).option_value
2826 ELSE NULL END AS character_data) AS option_value
2827 FROM _pg_user_mappings um;
2829 GRANT SELECT ON user_mapping_options TO PUBLIC;
2834 * USER_MAPPINGS view
2836 CREATE VIEW user_mappings AS
2837 SELECT authorization_identifier,
2838 foreign_server_catalog,
2840 FROM _pg_user_mappings;
2842 GRANT SELECT ON user_mappings TO PUBLIC;