2 * SQL Information Schema
3 * as defined in ISO/IEC 9075-11:2003
5 * Copyright (c) 2003-2006, PostgreSQL Global Development Group
7 * $PostgreSQL: pgsql/src/backend/catalog/information_schema.sql,v 1.35 2006/09/04 23:13:01 tgl Exp $
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, public;
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_keyissubset(smallint[], smallint[]) RETURNS boolean
46 RETURNS NULL ON NULL INPUT
47 AS 'select $1[1] is null or ($1[1] = any ($2) and coalesce(information_schema._pg_keyissubset($1[2:pg_catalog.array_upper($1,1)], $2), true))';
49 CREATE FUNCTION _pg_keysequal(smallint[], smallint[]) RETURNS boolean
52 RETURNS NULL ON NULL INPUT
53 AS 'select information_schema._pg_keyissubset($1, $2) and information_schema._pg_keyissubset($2, $1)';
55 CREATE FUNCTION _pg_truetypid(pg_attribute, pg_type) RETURNS oid
58 RETURNS NULL ON NULL INPUT
60 $$SELECT CASE WHEN $2.typtype = 'd' THEN $2.typbasetype ELSE $1.atttypid END$$;
62 CREATE FUNCTION _pg_truetypmod(pg_attribute, pg_type) RETURNS int4
65 RETURNS NULL ON NULL INPUT
67 $$SELECT CASE WHEN $2.typtype = 'd' THEN $2.typtypmod ELSE $1.atttypmod END$$;
69 -- these functions encapsulate knowledge about the encoding of typmod:
71 CREATE FUNCTION _pg_char_max_length(typid oid, typmod int4) RETURNS integer
74 RETURNS NULL ON NULL INPUT
77 CASE WHEN $2 = -1 /* default typmod */
79 WHEN $1 IN (1042, 1043) /* char, varchar */
81 WHEN $1 IN (1560, 1562) /* bit, varbit */
86 CREATE FUNCTION _pg_char_octet_length(typid oid, typmod int4) RETURNS integer
89 RETURNS NULL ON NULL INPUT
92 CASE WHEN $1 IN (25, 1042, 1043) /* text, char, varchar */
93 THEN CAST(2^30 AS integer)
97 CREATE FUNCTION _pg_numeric_precision(typid oid, typmod int4) RETURNS integer
100 RETURNS NULL ON NULL INPUT
104 WHEN 21 /*int2*/ THEN 16
105 WHEN 23 /*int4*/ THEN 32
106 WHEN 20 /*int8*/ THEN 64
107 WHEN 1700 /*numeric*/ THEN
110 ELSE (($2 - 4) >> 16) & 65535
112 WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/
113 WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/
117 CREATE FUNCTION _pg_numeric_precision_radix(typid oid, typmod int4) RETURNS integer
120 RETURNS NULL ON NULL INPUT
123 CASE WHEN $1 IN (21, 23, 20, 700, 701) THEN 2
124 WHEN $1 IN (1700) THEN 10
128 CREATE FUNCTION _pg_numeric_scale(typid oid, typmod int4) RETURNS integer
131 RETURNS NULL ON NULL INPUT
134 CASE WHEN $1 IN (21, 23, 20) THEN 0
135 WHEN $1 IN (1700) THEN
138 ELSE ($2 - 4) & 65535
143 CREATE FUNCTION _pg_datetime_precision(typid oid, typmod int4) RETURNS integer
146 RETURNS NULL ON NULL INPUT
149 CASE WHEN $2 = -1 /* default typmod */
151 WHEN $1 IN (1083, 1114, 1184, 1266) /* time, timestamp, same + tz */
153 WHEN $1 IN (1186) /* interval */
159 -- 5.2 INFORMATION_SCHEMA_CATALOG_NAME view appears later.
164 * CARDINAL_NUMBER domain
167 CREATE DOMAIN cardinal_number AS integer
168 CONSTRAINT cardinal_number_domain_check CHECK (value >= 0);
173 * CHARACTER_DATA domain
176 CREATE DOMAIN character_data AS character varying;
181 * SQL_IDENTIFIER domain
184 CREATE DOMAIN sql_identifier AS character varying;
189 * INFORMATION_SCHEMA_CATALOG_NAME view
192 CREATE VIEW information_schema_catalog_name AS
193 SELECT CAST(current_database() AS sql_identifier) AS catalog_name;
195 GRANT SELECT ON information_schema_catalog_name TO PUBLIC;
203 CREATE DOMAIN time_stamp AS timestamp(2) with time zone
204 DEFAULT current_timestamp(2);
207 -- 5.7 ADMINISTRABLE_ROLE_AUTHORIZATIONS view appears later.
212 * APPLICABLE_ROLES view
215 CREATE VIEW applicable_roles AS
216 SELECT CAST(a.rolname AS sql_identifier) AS grantee,
217 CAST(b.rolname AS sql_identifier) AS role_name,
218 CAST(CASE WHEN m.admin_option THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
219 FROM pg_auth_members m
220 JOIN pg_authid a ON (m.member = a.oid)
221 JOIN pg_authid b ON (m.roleid = b.oid)
222 WHERE pg_has_role(a.oid, 'USAGE');
224 GRANT SELECT ON applicable_roles TO PUBLIC;
229 * ADMINISTRABLE_ROLE_AUTHORIZATIONS view
232 CREATE VIEW administrable_role_authorizations AS
234 FROM applicable_roles
235 WHERE is_grantable = 'YES';
237 GRANT SELECT ON administrable_role_authorizations TO PUBLIC;
245 -- feature not supported
253 CREATE VIEW attributes AS
254 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
255 CAST(nc.nspname AS sql_identifier) AS udt_schema,
256 CAST(c.relname AS sql_identifier) AS udt_name,
257 CAST(a.attname AS sql_identifier) AS attribute_name,
258 CAST(a.attnum AS cardinal_number) AS ordinal_position,
259 CAST(pg_get_expr(ad.adbin, ad.adrelid) AS character_data) AS attribute_default,
260 CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
265 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
266 WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, null)
267 ELSE 'USER-DEFINED' END
272 _pg_char_max_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
274 AS character_maximum_length,
277 _pg_char_octet_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
279 AS character_octet_length,
281 CAST(null AS sql_identifier) AS character_set_catalog,
282 CAST(null AS sql_identifier) AS character_set_schema,
283 CAST(null AS sql_identifier) AS character_set_name,
285 CAST(null AS sql_identifier) AS collation_catalog,
286 CAST(null AS sql_identifier) AS collation_schema,
287 CAST(null AS sql_identifier) AS collation_name,
290 _pg_numeric_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
292 AS numeric_precision,
295 _pg_numeric_precision_radix(_pg_truetypid(a, t), _pg_truetypmod(a, t))
297 AS numeric_precision_radix,
300 _pg_numeric_scale(_pg_truetypid(a, t), _pg_truetypmod(a, t))
305 _pg_datetime_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
307 AS datetime_precision,
309 CAST(null AS character_data) AS interval_type, -- FIXME
310 CAST(null AS character_data) AS interval_precision, -- FIXME
312 CAST(current_database() AS sql_identifier) AS attribute_udt_catalog,
313 CAST(nt.nspname AS sql_identifier) AS attribute_udt_schema,
314 CAST(t.typname AS sql_identifier) AS attribute_udt_name,
316 CAST(null AS sql_identifier) AS scope_catalog,
317 CAST(null AS sql_identifier) AS scope_schema,
318 CAST(null AS sql_identifier) AS scope_name,
320 CAST(null AS cardinal_number) AS maximum_cardinality,
321 CAST(a.attnum AS sql_identifier) AS dtd_identifier,
322 CAST('NO' AS character_data) AS is_derived_reference_attribute
324 FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum),
325 pg_class c, pg_namespace nc,
326 (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
328 WHERE a.attrelid = c.oid
329 AND a.atttypid = t.oid
330 AND nc.oid = c.relnamespace
331 AND a.attnum > 0 AND NOT a.attisdropped
332 AND c.relkind in ('c');
334 GRANT SELECT ON attributes TO PUBLIC;
339 * CHARACTER_SETS view
342 -- feature not supported
347 * CHECK_CONSTRAINT_ROUTINE_USAGE view
350 CREATE VIEW check_constraint_routine_usage AS
351 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
352 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
353 CAST(c.conname AS sql_identifier) AS constraint_name,
354 CAST(current_database() AS sql_identifier) AS specific_catalog,
355 CAST(np.nspname AS sql_identifier) AS specific_schema,
356 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name
357 FROM pg_namespace nc, pg_constraint c, pg_depend d, pg_proc p, pg_namespace np
358 WHERE nc.oid = c.connamespace
361 AND d.classid = 'pg_catalog.pg_constraint'::regclass
362 AND d.refobjid = p.oid
363 AND d.refclassid = 'pg_catalog.pg_proc'::regclass
364 AND p.pronamespace = np.oid
365 AND pg_has_role(p.proowner, 'USAGE');
367 GRANT SELECT ON check_constraint_routine_usage TO PUBLIC;
372 * CHECK_CONSTRAINTS view
375 CREATE VIEW check_constraints AS
376 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
377 CAST(rs.nspname AS sql_identifier) AS constraint_schema,
378 CAST(con.conname AS sql_identifier) AS constraint_name,
379 CAST(substring(pg_get_constraintdef(con.oid) from 7) AS character_data)
381 FROM pg_constraint con
382 LEFT OUTER JOIN pg_namespace rs ON (rs.oid = con.connamespace)
383 LEFT OUTER JOIN pg_class c ON (c.oid = con.conrelid)
384 LEFT OUTER JOIN pg_type t ON (t.oid = con.contypid)
385 WHERE pg_has_role(coalesce(c.relowner, t.typowner), 'USAGE')
386 AND con.contype = 'c'
389 -- not-null constraints
391 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
392 CAST(n.nspname AS sql_identifier) AS constraint_schema,
393 CAST(n.oid || '_' || r.oid || '_' || a.attnum || '_not_null' AS sql_identifier) AS constraint_name, -- XXX
394 CAST(a.attname || ' IS NOT NULL' AS character_data)
396 FROM pg_namespace n, pg_class r, pg_attribute a
397 WHERE n.oid = r.relnamespace
398 AND r.oid = a.attrelid
400 AND NOT a.attisdropped
403 AND pg_has_role(r.relowner, 'USAGE');
405 GRANT SELECT ON check_constraints TO PUBLIC;
413 -- feature not supported
417 * COLLATION_CHARACTER_SET_APPLICABILITY view
420 -- feature not supported
425 * COLUMN_COLUMN_USAGE view
428 -- feature not supported
433 * COLUMN_DOMAIN_USAGE view
436 CREATE VIEW column_domain_usage AS
437 SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
438 CAST(nt.nspname AS sql_identifier) AS domain_schema,
439 CAST(t.typname AS sql_identifier) AS domain_name,
440 CAST(current_database() AS sql_identifier) AS table_catalog,
441 CAST(nc.nspname AS sql_identifier) AS table_schema,
442 CAST(c.relname AS sql_identifier) AS table_name,
443 CAST(a.attname AS sql_identifier) AS column_name
445 FROM pg_type t, pg_namespace nt, pg_class c, pg_namespace nc,
448 WHERE t.typnamespace = nt.oid
449 AND c.relnamespace = nc.oid
450 AND a.attrelid = c.oid
451 AND a.atttypid = t.oid
453 AND c.relkind IN ('r', 'v')
455 AND NOT a.attisdropped
456 AND pg_has_role(t.typowner, 'USAGE');
458 GRANT SELECT ON column_domain_usage TO PUBLIC;
466 CREATE VIEW column_privileges AS
467 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
468 CAST(grantee.rolname AS sql_identifier) AS grantee,
469 CAST(current_database() AS sql_identifier) AS table_catalog,
470 CAST(nc.nspname AS sql_identifier) AS table_schema,
471 CAST(c.relname AS sql_identifier) AS table_name,
472 CAST(a.attname AS sql_identifier) AS column_name,
473 CAST(pr.type AS character_data) AS privilege_type,
475 CASE WHEN aclcontains(c.relacl,
476 makeaclitem(grantee.oid, u_grantor.oid, pr.type, true))
477 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
484 SELECT oid, rolname FROM pg_authid
486 SELECT 0::oid, 'PUBLIC'
487 ) AS grantee (oid, rolname),
488 (SELECT 'SELECT' UNION ALL
489 SELECT 'INSERT' UNION ALL
490 SELECT 'UPDATE' UNION ALL
491 SELECT 'REFERENCES') AS pr (type)
493 WHERE a.attrelid = c.oid
494 AND c.relnamespace = nc.oid
496 AND NOT a.attisdropped
497 AND c.relkind IN ('r', 'v')
498 AND aclcontains(c.relacl,
499 makeaclitem(grantee.oid, u_grantor.oid, pr.type, false))
500 AND (pg_has_role(u_grantor.oid, 'USAGE')
501 OR pg_has_role(grantee.oid, 'USAGE')
502 OR grantee.rolname = 'PUBLIC');
504 GRANT SELECT ON column_privileges TO PUBLIC;
509 * COLUMN_UDT_USAGE view
512 CREATE VIEW column_udt_usage AS
513 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
514 CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,
515 CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,
516 CAST(current_database() AS sql_identifier) AS table_catalog,
517 CAST(nc.nspname AS sql_identifier) AS table_schema,
518 CAST(c.relname AS sql_identifier) AS table_name,
519 CAST(a.attname AS sql_identifier) AS column_name
521 FROM pg_attribute a, pg_class c, pg_namespace nc,
522 (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
523 LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
524 ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
526 WHERE a.attrelid = c.oid
527 AND a.atttypid = t.oid
528 AND nc.oid = c.relnamespace
529 AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
530 AND pg_has_role(coalesce(bt.typowner, t.typowner), 'USAGE');
532 GRANT SELECT ON column_udt_usage TO PUBLIC;
540 CREATE VIEW columns AS
541 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
542 CAST(nc.nspname AS sql_identifier) AS table_schema,
543 CAST(c.relname AS sql_identifier) AS table_name,
544 CAST(a.attname AS sql_identifier) AS column_name,
545 CAST(a.attnum AS cardinal_number) AS ordinal_position,
546 CAST(pg_get_expr(ad.adbin, ad.adrelid) AS character_data) AS column_default,
547 CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
552 CASE WHEN t.typtype = 'd' THEN
553 CASE WHEN bt.typelem <> 0 AND bt.typlen = -1 THEN 'ARRAY'
554 WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
555 ELSE 'USER-DEFINED' END
557 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
558 WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, null)
559 ELSE 'USER-DEFINED' END
565 _pg_char_max_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
567 AS character_maximum_length,
570 _pg_char_octet_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
572 AS character_octet_length,
575 _pg_numeric_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
577 AS numeric_precision,
580 _pg_numeric_precision_radix(_pg_truetypid(a, t), _pg_truetypmod(a, t))
582 AS numeric_precision_radix,
585 _pg_numeric_scale(_pg_truetypid(a, t), _pg_truetypmod(a, t))
590 _pg_datetime_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
592 AS datetime_precision,
594 CAST(null AS character_data) AS interval_type, -- FIXME
595 CAST(null AS character_data) AS interval_precision, -- FIXME
597 CAST(null AS sql_identifier) AS character_set_catalog,
598 CAST(null AS sql_identifier) AS character_set_schema,
599 CAST(null AS sql_identifier) AS character_set_name,
601 CAST(null AS sql_identifier) AS collation_catalog,
602 CAST(null AS sql_identifier) AS collation_schema,
603 CAST(null AS sql_identifier) AS collation_name,
605 CAST(CASE WHEN t.typtype = 'd' THEN current_database() ELSE null END
606 AS sql_identifier) AS domain_catalog,
607 CAST(CASE WHEN t.typtype = 'd' THEN nt.nspname ELSE null END
608 AS sql_identifier) AS domain_schema,
609 CAST(CASE WHEN t.typtype = 'd' THEN t.typname ELSE null END
610 AS sql_identifier) AS domain_name,
612 CAST(current_database() AS sql_identifier) AS udt_catalog,
613 CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,
614 CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,
616 CAST(null AS sql_identifier) AS scope_catalog,
617 CAST(null AS sql_identifier) AS scope_schema,
618 CAST(null AS sql_identifier) AS scope_name,
620 CAST(null AS cardinal_number) AS maximum_cardinality,
621 CAST(a.attnum AS sql_identifier) AS dtd_identifier,
622 CAST('NO' AS character_data) AS is_self_referencing,
624 CAST('NO' AS character_data) AS is_identity,
625 CAST(null AS character_data) AS identity_generation,
626 CAST(null AS character_data) AS identity_start,
627 CAST(null AS character_data) AS identity_increment,
628 CAST(null AS character_data) AS identity_maximum,
629 CAST(null AS character_data) AS identity_minimum,
630 CAST(null AS character_data) AS identity_cycle,
632 CAST('NEVER' AS character_data) AS is_generated,
633 CAST(null AS character_data) AS generation_expression,
635 CAST(CASE WHEN c.relkind = 'r'
636 THEN 'YES' ELSE 'NO' END AS character_data) AS is_updatable
638 FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum),
639 pg_class c, pg_namespace nc,
640 (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
641 LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
642 ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
644 WHERE a.attrelid = c.oid
645 AND a.atttypid = t.oid
646 AND nc.oid = c.relnamespace
647 AND (nc.nspname NOT LIKE 'pg!_temp!_%' ESCAPE '!' OR pg_catalog.pg_table_is_visible(c.oid))
648 AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
650 AND (pg_has_role(c.relowner, 'USAGE')
651 OR has_table_privilege(c.oid, 'SELECT')
652 OR has_table_privilege(c.oid, 'INSERT')
653 OR has_table_privilege(c.oid, 'UPDATE')
654 OR has_table_privilege(c.oid, 'REFERENCES') );
656 GRANT SELECT ON columns TO PUBLIC;
661 * CONSTRAINT_COLUMN_USAGE view
664 CREATE VIEW constraint_column_usage AS
665 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
666 CAST(tblschema AS sql_identifier) AS table_schema,
667 CAST(tblname AS sql_identifier) AS table_name,
668 CAST(colname AS sql_identifier) AS column_name,
669 CAST(current_database() AS sql_identifier) AS constraint_catalog,
670 CAST(cstrschema AS sql_identifier) AS constraint_schema,
671 CAST(cstrname AS sql_identifier) AS constraint_name
674 /* check constraints */
675 SELECT DISTINCT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
676 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_depend d, pg_namespace nc, pg_constraint c
677 WHERE nr.oid = r.relnamespace
678 AND r.oid = a.attrelid
679 AND d.refclassid = 'pg_catalog.pg_class'::regclass
680 AND d.refobjid = r.oid
681 AND d.refobjsubid = a.attnum
682 AND d.classid = 'pg_catalog.pg_constraint'::regclass
684 AND c.connamespace = nc.oid
687 AND NOT a.attisdropped
691 /* unique/primary key/foreign key constraints */
692 SELECT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
693 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc,
695 WHERE nr.oid = r.relnamespace
696 AND r.oid = a.attrelid
697 AND nc.oid = c.connamespace
698 AND (CASE WHEN c.contype = 'f' THEN r.oid = c.confrelid AND a.attnum = ANY (c.confkey)
699 ELSE r.oid = c.conrelid AND a.attnum = ANY (c.conkey) END)
700 AND NOT a.attisdropped
701 AND c.contype IN ('p', 'u', 'f')
704 ) AS x (tblschema, tblname, tblowner, colname, cstrschema, cstrname)
706 WHERE pg_has_role(x.tblowner, 'USAGE');
708 GRANT SELECT ON constraint_column_usage TO PUBLIC;
713 * CONSTRAINT_TABLE_USAGE view
716 CREATE VIEW constraint_table_usage AS
717 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
718 CAST(nr.nspname AS sql_identifier) AS table_schema,
719 CAST(r.relname AS sql_identifier) AS table_name,
720 CAST(current_database() AS sql_identifier) AS constraint_catalog,
721 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
722 CAST(c.conname AS sql_identifier) AS constraint_name
724 FROM pg_constraint c, pg_namespace nc,
725 pg_class r, pg_namespace nr
727 WHERE c.connamespace = nc.oid AND r.relnamespace = nr.oid
728 AND ( (c.contype = 'f' AND c.confrelid = r.oid)
729 OR (c.contype IN ('p', 'u') AND c.conrelid = r.oid) )
731 AND pg_has_role(r.relowner, 'USAGE');
733 GRANT SELECT ON constraint_table_usage TO PUBLIC;
736 -- 5.23 DATA_TYPE_PRIVILEGES view appears later.
741 * DIRECT_SUPERTABLES view
744 -- feature not supported
749 * DIRECT_SUPERTYPES view
752 -- feature not supported
757 * DOMAIN_CONSTRAINTS view
760 CREATE VIEW domain_constraints AS
761 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
762 CAST(rs.nspname AS sql_identifier) AS constraint_schema,
763 CAST(con.conname AS sql_identifier) AS constraint_name,
764 CAST(current_database() AS sql_identifier) AS domain_catalog,
765 CAST(n.nspname AS sql_identifier) AS domain_schema,
766 CAST(t.typname AS sql_identifier) AS domain_name,
767 CAST(CASE WHEN condeferrable THEN 'YES' ELSE 'NO' END
768 AS character_data) AS is_deferrable,
769 CAST(CASE WHEN condeferred THEN 'YES' ELSE 'NO' END
770 AS character_data) AS initially_deferred
771 FROM pg_namespace rs, pg_namespace n, pg_constraint con, pg_type t
772 WHERE rs.oid = con.connamespace
773 AND n.oid = t.typnamespace
774 AND t.oid = con.contypid;
776 GRANT SELECT ON domain_constraints TO PUBLIC;
780 * DOMAIN_UDT_USAGE view
781 * apparently removed in SQL:2003
784 CREATE VIEW domain_udt_usage AS
785 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
786 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
787 CAST(bt.typname AS sql_identifier) AS udt_name,
788 CAST(current_database() AS sql_identifier) AS domain_catalog,
789 CAST(nt.nspname AS sql_identifier) AS domain_schema,
790 CAST(t.typname AS sql_identifier) AS domain_name
792 FROM pg_type t, pg_namespace nt,
793 pg_type bt, pg_namespace nbt
795 WHERE t.typnamespace = nt.oid
796 AND t.typbasetype = bt.oid
797 AND bt.typnamespace = nbt.oid
799 AND pg_has_role(bt.typowner, 'USAGE');
801 GRANT SELECT ON domain_udt_usage TO PUBLIC;
809 CREATE VIEW domains AS
810 SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
811 CAST(nt.nspname AS sql_identifier) AS domain_schema,
812 CAST(t.typname AS sql_identifier) AS domain_name,
815 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
816 WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
817 ELSE 'USER-DEFINED' END
822 _pg_char_max_length(t.typbasetype, t.typtypmod)
824 AS character_maximum_length,
827 _pg_char_octet_length(t.typbasetype, t.typtypmod)
829 AS character_octet_length,
831 CAST(null AS sql_identifier) AS character_set_catalog,
832 CAST(null AS sql_identifier) AS character_set_schema,
833 CAST(null AS sql_identifier) AS character_set_name,
835 CAST(null AS sql_identifier) AS collation_catalog,
836 CAST(null AS sql_identifier) AS collation_schema,
837 CAST(null AS sql_identifier) AS collation_name,
840 _pg_numeric_precision(t.typbasetype, t.typtypmod)
842 AS numeric_precision,
845 _pg_numeric_precision_radix(t.typbasetype, t.typtypmod)
847 AS numeric_precision_radix,
850 _pg_numeric_scale(t.typbasetype, t.typtypmod)
855 _pg_datetime_precision(t.typbasetype, t.typtypmod)
857 AS datetime_precision,
859 CAST(null AS character_data) AS interval_type, -- FIXME
860 CAST(null AS character_data) AS interval_precision, -- FIXME
862 CAST(t.typdefault AS character_data) AS domain_default,
864 CAST(current_database() AS sql_identifier) AS udt_catalog,
865 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
866 CAST(bt.typname AS sql_identifier) AS udt_name,
868 CAST(null AS sql_identifier) AS scope_catalog,
869 CAST(null AS sql_identifier) AS scope_schema,
870 CAST(null AS sql_identifier) AS scope_name,
872 CAST(null AS cardinal_number) AS maximum_cardinality,
873 CAST(1 AS sql_identifier) AS dtd_identifier
875 FROM pg_type t, pg_namespace nt,
876 pg_type bt, pg_namespace nbt
878 WHERE t.typnamespace = nt.oid
879 AND t.typbasetype = bt.oid
880 AND bt.typnamespace = nbt.oid
883 GRANT SELECT ON domains TO PUBLIC;
886 -- 5.28 ELEMENT_TYPES view appears later.
894 CREATE VIEW enabled_roles AS
895 SELECT CAST(a.rolname AS sql_identifier) AS role_name
897 WHERE pg_has_role(a.oid, 'USAGE');
899 GRANT SELECT ON enabled_roles TO PUBLIC;
907 -- feature not supported
912 * KEY_COLUMN_USAGE view
915 CREATE VIEW key_column_usage AS
916 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
917 CAST(nc_nspname AS sql_identifier) AS constraint_schema,
918 CAST(conname AS sql_identifier) AS constraint_name,
919 CAST(current_database() AS sql_identifier) AS table_catalog,
920 CAST(nr_nspname AS sql_identifier) AS table_schema,
921 CAST(relname AS sql_identifier) AS table_name,
922 CAST(a.attname AS sql_identifier) AS column_name,
923 CAST((ss.x).n AS cardinal_number) AS ordinal_position,
925 SELECT CAST(a AS cardinal_number)
927 (SELECT a FROM generate_series(1, array_upper(ss.confkey,1)) a) AS foo
928 WHERE conrelid = ss.confrelid
929 AND conkey[foo.a] = ss.confkey[(ss.x).n]
930 ) AS position_in_unique_constraint
932 (SELECT r.oid, r.relname, nc.nspname AS nc_nspname,
933 nr.nspname AS nr_nspname,
934 c.conname, c.confkey, c.confrelid,
935 _pg_expandarray(c.conkey) AS x
936 FROM pg_namespace nr, pg_class r, pg_namespace nc,
938 WHERE nr.oid = r.relnamespace
939 AND r.oid = c.conrelid
940 AND nc.oid = c.connamespace
941 AND c.contype IN ('p', 'u', 'f')
943 AND (nr.nspname NOT LIKE 'pg!_temp!_%' ESCAPE '!' OR pg_catalog.pg_table_is_visible(r.oid))
944 AND (pg_has_role(r.relowner, 'USAGE')
945 OR has_table_privilege(c.oid, 'SELECT')
946 OR has_table_privilege(c.oid, 'INSERT')
947 OR has_table_privilege(c.oid, 'UPDATE')
948 OR has_table_privilege(c.oid, 'REFERENCES')) ) AS ss
949 WHERE ss.oid = a.attrelid
950 AND a.attnum = (ss.x).x
951 AND NOT a.attisdropped;
953 GRANT SELECT ON key_column_usage TO PUBLIC;
958 * METHOD_SPECIFICATION_PARAMETERS view
961 -- feature not supported
966 * METHOD_SPECIFICATIONS view
969 -- feature not supported
977 CREATE VIEW parameters AS
978 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
979 CAST(n_nspname AS sql_identifier) AS specific_schema,
980 CAST(proname || '_' || CAST(p_oid AS text) AS sql_identifier) AS specific_name,
981 CAST((ss.x).n AS cardinal_number) AS ordinal_position,
983 CASE WHEN proargmodes IS NULL THEN 'IN'
984 WHEN proargmodes[(ss.x).n] = 'i' THEN 'IN'
985 WHEN proargmodes[(ss.x).n] = 'o' THEN 'OUT'
986 WHEN proargmodes[(ss.x).n] = 'b' THEN 'INOUT'
987 END AS character_data) AS parameter_mode,
988 CAST('NO' AS character_data) AS is_result,
989 CAST('NO' AS character_data) AS as_locator,
990 CAST(NULLIF(proargnames[(ss.x).n], '') AS sql_identifier) AS parameter_name,
992 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
993 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
994 ELSE 'USER-DEFINED' END AS character_data)
996 CAST(null AS cardinal_number) AS character_maximum_length,
997 CAST(null AS cardinal_number) AS character_octet_length,
998 CAST(null AS sql_identifier) AS character_set_catalog,
999 CAST(null AS sql_identifier) AS character_set_schema,
1000 CAST(null AS sql_identifier) AS character_set_name,
1001 CAST(null AS sql_identifier) AS collation_catalog,
1002 CAST(null AS sql_identifier) AS collation_schema,
1003 CAST(null AS sql_identifier) AS collation_name,
1004 CAST(null AS cardinal_number) AS numeric_precision,
1005 CAST(null AS cardinal_number) AS numeric_precision_radix,
1006 CAST(null AS cardinal_number) AS numeric_scale,
1007 CAST(null AS cardinal_number) AS datetime_precision,
1008 CAST(null AS character_data) AS interval_type,
1009 CAST(null AS character_data) AS interval_precision,
1010 CAST(current_database() AS sql_identifier) AS udt_catalog,
1011 CAST(nt.nspname AS sql_identifier) AS udt_schema,
1012 CAST(t.typname AS sql_identifier) AS udt_name,
1013 CAST(null AS sql_identifier) AS scope_catalog,
1014 CAST(null AS sql_identifier) AS scope_schema,
1015 CAST(null AS sql_identifier) AS scope_name,
1016 CAST(null AS cardinal_number) AS maximum_cardinality,
1017 CAST((ss.x).n AS sql_identifier) AS dtd_identifier
1019 FROM pg_type t, pg_namespace nt,
1020 (SELECT n.nspname AS n_nspname, p.proname, p.oid AS p_oid,
1021 p.proargnames, p.proargmodes,
1022 _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
1023 FROM pg_namespace n, pg_proc p
1024 WHERE n.oid = p.pronamespace
1025 AND (pg_has_role(p.proowner, 'USAGE') OR
1026 has_function_privilege(p.oid, 'EXECUTE'))) AS ss
1027 WHERE t.oid = (ss.x).x AND t.typnamespace = nt.oid;
1029 GRANT SELECT ON parameters TO PUBLIC;
1034 * REFERENCED_TYPES view
1037 -- feature not supported
1042 * REFERENTIAL_CONSTRAINTS view
1045 CREATE VIEW referential_constraints AS
1046 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1047 CAST(ncon.nspname AS sql_identifier) AS constraint_schema,
1048 CAST(con.conname AS sql_identifier) AS constraint_name,
1050 CASE WHEN npkc.nspname IS NULL THEN NULL
1051 ELSE current_database() END
1052 AS sql_identifier) AS unique_constraint_catalog,
1053 CAST(npkc.nspname AS sql_identifier) AS unique_constraint_schema,
1054 CAST(pkc.conname AS sql_identifier) AS unique_constraint_name,
1057 CASE con.confmatchtype WHEN 'f' THEN 'FULL'
1058 WHEN 'p' THEN 'PARTIAL'
1059 WHEN 'u' THEN 'NONE' END
1060 AS character_data) AS match_option,
1063 CASE con.confupdtype WHEN 'c' THEN 'CASCADE'
1064 WHEN 'n' THEN 'SET NULL'
1065 WHEN 'd' THEN 'SET DEFAULT'
1066 WHEN 'r' THEN 'RESTRICT'
1067 WHEN 'a' THEN 'NO ACTION' END
1068 AS character_data) AS update_rule,
1071 CASE con.confdeltype WHEN 'c' THEN 'CASCADE'
1072 WHEN 'n' THEN 'SET NULL'
1073 WHEN 'd' THEN 'SET DEFAULT'
1074 WHEN 'r' THEN 'RESTRICT'
1075 WHEN 'a' THEN 'NO ACTION' END
1076 AS character_data) AS delete_rule
1078 FROM (pg_namespace ncon
1079 INNER JOIN pg_constraint con ON ncon.oid = con.connamespace
1080 INNER JOIN pg_class c ON con.conrelid = c.oid)
1083 INNER JOIN pg_namespace npkc ON pkc.connamespace = npkc.oid)
1084 ON con.confrelid = pkc.conrelid
1085 AND _pg_keysequal(con.confkey, pkc.conkey)
1087 WHERE c.relkind = 'r'
1088 AND con.contype = 'f'
1089 AND (pkc.contype IN ('p', 'u') OR pkc.contype IS NULL)
1090 AND pg_has_role(c.relowner, 'USAGE');
1092 GRANT SELECT ON referential_constraints TO PUBLIC;
1097 * ROLE_COLUMN_GRANTS view
1100 CREATE VIEW role_column_grants AS
1101 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1102 CAST(g_grantee.rolname AS sql_identifier) AS grantee,
1103 CAST(current_database() AS sql_identifier) AS table_catalog,
1104 CAST(nc.nspname AS sql_identifier) AS table_schema,
1105 CAST(c.relname AS sql_identifier) AS table_name,
1106 CAST(a.attname AS sql_identifier) AS column_name,
1107 CAST(pr.type AS character_data) AS privilege_type,
1109 CASE WHEN aclcontains(c.relacl,
1110 makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, true))
1111 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
1113 FROM pg_attribute a,
1116 pg_authid u_grantor,
1117 pg_authid g_grantee,
1118 (SELECT 'SELECT' UNION ALL
1119 SELECT 'INSERT' UNION ALL
1120 SELECT 'UPDATE' UNION ALL
1121 SELECT 'REFERENCES') AS pr (type)
1123 WHERE a.attrelid = c.oid
1124 AND c.relnamespace = nc.oid
1126 AND NOT a.attisdropped
1127 AND c.relkind IN ('r', 'v')
1128 AND aclcontains(c.relacl,
1129 makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, false))
1130 AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
1131 OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));
1133 GRANT SELECT ON role_column_grants TO PUBLIC;
1138 * ROLE_ROUTINE_GRANTS view
1141 CREATE VIEW role_routine_grants AS
1142 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1143 CAST(g_grantee.rolname AS sql_identifier) AS grantee,
1144 CAST(current_database() AS sql_identifier) AS specific_catalog,
1145 CAST(n.nspname AS sql_identifier) AS specific_schema,
1146 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1147 CAST(current_database() AS sql_identifier) AS routine_catalog,
1148 CAST(n.nspname AS sql_identifier) AS routine_schema,
1149 CAST(p.proname AS sql_identifier) AS routine_name,
1150 CAST('EXECUTE' AS character_data) AS privilege_type,
1152 CASE WHEN aclcontains(p.proacl,
1153 makeaclitem(g_grantee.oid, u_grantor.oid, 'EXECUTE', true))
1154 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
1158 pg_authid u_grantor,
1161 WHERE p.pronamespace = n.oid
1162 AND aclcontains(p.proacl,
1163 makeaclitem(g_grantee.oid, u_grantor.oid, 'EXECUTE', false))
1164 AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
1165 OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));
1167 GRANT SELECT ON role_routine_grants TO PUBLIC;
1172 * ROLE_TABLE_GRANTS view
1175 CREATE VIEW role_table_grants AS
1176 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1177 CAST(g_grantee.rolname AS sql_identifier) AS grantee,
1178 CAST(current_database() AS sql_identifier) AS table_catalog,
1179 CAST(nc.nspname AS sql_identifier) AS table_schema,
1180 CAST(c.relname AS sql_identifier) AS table_name,
1181 CAST(pr.type AS character_data) AS privilege_type,
1183 CASE WHEN aclcontains(c.relacl,
1184 makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, true))
1185 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable,
1186 CAST('NO' AS character_data) AS with_hierarchy
1190 pg_authid u_grantor,
1191 pg_authid g_grantee,
1192 (SELECT 'SELECT' UNION ALL
1193 SELECT 'DELETE' UNION ALL
1194 SELECT 'INSERT' UNION ALL
1195 SELECT 'UPDATE' UNION ALL
1196 SELECT 'REFERENCES' UNION ALL
1197 SELECT 'RULE' UNION ALL
1198 SELECT 'TRIGGER') AS pr (type)
1200 WHERE c.relnamespace = nc.oid
1201 AND c.relkind IN ('r', 'v')
1202 AND aclcontains(c.relacl,
1203 makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, false))
1204 AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
1205 OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));
1207 GRANT SELECT ON role_table_grants TO PUBLIC;
1212 * ROLE_TABLE_METHOD_GRANTS view
1215 -- feature not supported
1220 * ROLE_USAGE_GRANTS view
1223 -- See USAGE_PRIVILEGES.
1225 CREATE VIEW role_usage_grants AS
1226 SELECT CAST(null AS sql_identifier) AS grantor,
1227 CAST(null AS sql_identifier) AS grantee,
1228 CAST(current_database() AS sql_identifier) AS object_catalog,
1229 CAST(null AS sql_identifier) AS object_schema,
1230 CAST(null AS sql_identifier) AS object_name,
1231 CAST(null AS character_data) AS object_type,
1232 CAST('USAGE' AS character_data) AS privilege_type,
1233 CAST(null AS character_data) AS is_grantable
1237 GRANT SELECT ON role_usage_grants TO PUBLIC;
1242 * ROLE_UDT_GRANTS view
1245 -- feature not supported
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,
1272 CASE WHEN aclcontains(p.proacl,
1273 makeaclitem(grantee.oid, u_grantor.oid, 'EXECUTE', true))
1274 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
1278 pg_authid u_grantor,
1280 SELECT oid, rolname FROM pg_authid
1282 SELECT 0::oid, 'PUBLIC'
1283 ) AS grantee (oid, rolname)
1285 WHERE p.pronamespace = n.oid
1286 AND aclcontains(p.proacl,
1287 makeaclitem(grantee.oid, u_grantor.oid, 'EXECUTE', false))
1288 AND (pg_has_role(u_grantor.oid, 'USAGE')
1289 OR pg_has_role(grantee.oid, 'USAGE')
1290 OR grantee.rolname = 'PUBLIC');
1292 GRANT SELECT ON routine_privileges TO PUBLIC;
1297 * ROUTINE_ROUTINE_USAGE view
1300 -- not tracked by PostgreSQL
1305 * ROUTINE_SEQUENCE_USAGE view
1308 -- not tracked by PostgreSQL
1313 * ROUTINE_TABLE_USAGE view
1316 -- not tracked by PostgreSQL
1324 CREATE VIEW routines AS
1325 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
1326 CAST(n.nspname AS sql_identifier) AS specific_schema,
1327 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1328 CAST(current_database() AS sql_identifier) AS routine_catalog,
1329 CAST(n.nspname AS sql_identifier) AS routine_schema,
1330 CAST(p.proname AS sql_identifier) AS routine_name,
1331 CAST('FUNCTION' AS character_data) AS routine_type,
1332 CAST(null AS sql_identifier) AS module_catalog,
1333 CAST(null AS sql_identifier) AS module_schema,
1334 CAST(null AS sql_identifier) AS module_name,
1335 CAST(null AS sql_identifier) AS udt_catalog,
1336 CAST(null AS sql_identifier) AS udt_schema,
1337 CAST(null AS sql_identifier) AS udt_name,
1340 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
1341 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
1342 ELSE 'USER-DEFINED' END AS character_data)
1344 CAST(null AS cardinal_number) AS character_maximum_length,
1345 CAST(null AS cardinal_number) AS character_octet_length,
1346 CAST(null AS sql_identifier) AS character_set_catalog,
1347 CAST(null AS sql_identifier) AS character_set_schema,
1348 CAST(null AS sql_identifier) AS character_set_name,
1349 CAST(null AS sql_identifier) AS collation_catalog,
1350 CAST(null AS sql_identifier) AS collation_schema,
1351 CAST(null AS sql_identifier) AS collation_name,
1352 CAST(null AS cardinal_number) AS numeric_precision,
1353 CAST(null AS cardinal_number) AS numeric_precision_radix,
1354 CAST(null AS cardinal_number) AS numeric_scale,
1355 CAST(null AS cardinal_number) AS datetime_precision,
1356 CAST(null AS character_data) AS interval_type,
1357 CAST(null AS character_data) AS interval_precision,
1358 CAST(current_database() AS sql_identifier) AS type_udt_catalog,
1359 CAST(nt.nspname AS sql_identifier) AS type_udt_schema,
1360 CAST(t.typname AS sql_identifier) AS type_udt_name,
1361 CAST(null AS sql_identifier) AS scope_catalog,
1362 CAST(null AS sql_identifier) AS scope_schema,
1363 CAST(null AS sql_identifier) AS scope_name,
1364 CAST(null AS cardinal_number) AS maximum_cardinality,
1365 CAST(0 AS sql_identifier) AS dtd_identifier,
1367 CAST(CASE WHEN l.lanname = 'sql' THEN 'SQL' ELSE 'EXTERNAL' END AS character_data)
1370 CASE WHEN pg_has_role(p.proowner, 'USAGE') THEN p.prosrc ELSE null END
1371 AS character_data) AS routine_definition,
1373 CASE WHEN l.lanname = 'c' THEN p.prosrc ELSE null END
1374 AS character_data) AS external_name,
1375 CAST(upper(l.lanname) AS character_data) AS external_language,
1377 CAST('GENERAL' AS character_data) AS parameter_style,
1378 CAST(CASE WHEN p.provolatile = 'i' THEN 'YES' ELSE 'NO' END AS character_data) AS is_deterministic,
1379 CAST('MODIFIES' AS character_data) AS sql_data_access,
1380 CAST(CASE WHEN p.proisstrict THEN 'YES' ELSE 'NO' END AS character_data) AS is_null_call,
1381 CAST(null AS character_data) AS sql_path,
1382 CAST('YES' AS character_data) AS schema_level_routine,
1383 CAST(0 AS cardinal_number) AS max_dynamic_result_sets,
1384 CAST(null AS character_data) AS is_user_defined_cast,
1385 CAST(null AS character_data) AS is_implicitly_invocable,
1386 CAST(CASE WHEN p.prosecdef THEN 'DEFINER' ELSE 'INVOKER' END AS character_data) AS security_type,
1387 CAST(null AS sql_identifier) AS to_sql_specific_catalog,
1388 CAST(null AS sql_identifier) AS to_sql_specific_schema,
1389 CAST(null AS sql_identifier) AS to_sql_specific_name,
1390 CAST('NO' AS character_data) AS as_locator,
1391 CAST(null AS time_stamp) AS created,
1392 CAST(null AS time_stamp) AS last_altered,
1393 CAST(null AS character_data) AS new_savepoint_level,
1394 CAST('YES' AS character_data) AS is_udt_dependent, -- FIXME?
1396 CAST(null AS character_data) AS result_cast_from_data_type,
1397 CAST(null AS character_data) AS result_cast_as_locator,
1398 CAST(null AS cardinal_number) AS result_cast_char_max_length,
1399 CAST(null AS cardinal_number) AS result_cast_char_octet_length,
1400 CAST(null AS sql_identifier) AS result_cast_char_set_catalog,
1401 CAST(null AS sql_identifier) AS result_cast_char_set_schema,
1402 CAST(null AS sql_identifier) AS result_cast_character_set_name,
1403 CAST(null AS sql_identifier) AS result_cast_collation_catalog,
1404 CAST(null AS sql_identifier) AS result_cast_collation_schema,
1405 CAST(null AS sql_identifier) AS result_cast_collation_name,
1406 CAST(null AS cardinal_number) AS result_cast_numeric_precision,
1407 CAST(null AS cardinal_number) AS result_cast_numeric_precision_radix,
1408 CAST(null AS cardinal_number) AS result_cast_numeric_scale,
1409 CAST(null AS cardinal_number) AS result_cast_datetime_precision,
1410 CAST(null AS character_data) AS result_cast_interval_type,
1411 CAST(null AS character_data) AS result_cast_interval_precision,
1412 CAST(null AS sql_identifier) AS result_cast_type_udt_catalog,
1413 CAST(null AS sql_identifier) AS result_cast_type_udt_schema,
1414 CAST(null AS sql_identifier) AS result_cast_type_udt_name,
1415 CAST(null AS sql_identifier) AS result_cast_scope_catalog,
1416 CAST(null AS sql_identifier) AS result_cast_scope_schema,
1417 CAST(null AS sql_identifier) AS result_cast_scope_name,
1418 CAST(null AS cardinal_number) AS result_cast_maximum_cardinality,
1419 CAST(null AS sql_identifier) AS result_cast_dtd_identifier
1421 FROM pg_namespace n, pg_proc p, pg_language l,
1422 pg_type t, pg_namespace nt
1424 WHERE n.oid = p.pronamespace AND p.prolang = l.oid
1425 AND p.prorettype = t.oid AND t.typnamespace = nt.oid
1426 AND (pg_has_role(p.proowner, 'USAGE')
1427 OR has_function_privilege(p.oid, 'EXECUTE'));
1429 GRANT SELECT ON routines TO PUBLIC;
1437 CREATE VIEW schemata AS
1438 SELECT CAST(current_database() AS sql_identifier) AS catalog_name,
1439 CAST(n.nspname AS sql_identifier) AS schema_name,
1440 CAST(u.rolname AS sql_identifier) AS schema_owner,
1441 CAST(null AS sql_identifier) AS default_character_set_catalog,
1442 CAST(null AS sql_identifier) AS default_character_set_schema,
1443 CAST(null AS sql_identifier) AS default_character_set_name,
1444 CAST(null AS character_data) AS sql_path
1445 FROM pg_namespace n, pg_authid u
1446 WHERE n.nspowner = u.oid AND pg_has_role(n.nspowner, 'USAGE');
1448 GRANT SELECT ON schemata TO PUBLIC;
1456 CREATE VIEW sequences AS
1457 SELECT CAST(current_database() AS sql_identifier) AS sequence_catalog,
1458 CAST(nc.nspname AS sql_identifier) AS sequence_schema,
1459 CAST(c.relname AS sql_identifier) AS sequence_name,
1460 CAST('bigint' AS character_data) AS data_type,
1461 CAST(64 AS cardinal_number) AS numeric_precision,
1462 CAST(2 AS cardinal_number) AS numeric_precision_radix,
1463 CAST(0 AS cardinal_number) AS numeric_scale,
1464 CAST(null AS cardinal_number) AS maximum_value, -- FIXME
1465 CAST(null AS cardinal_number) AS minimum_value, -- FIXME
1466 CAST(null AS cardinal_number) AS increment, -- FIXME
1467 CAST(null AS character_data) AS cycle_option -- FIXME
1468 FROM pg_namespace nc, pg_class c
1469 WHERE c.relnamespace = nc.oid
1471 AND (nc.nspname NOT LIKE 'pg!_temp!_%' ESCAPE '!' OR pg_catalog.pg_table_is_visible(c.oid))
1472 AND (pg_has_role(c.relowner, 'USAGE')
1473 OR has_table_privilege(c.oid, 'SELECT')
1474 OR has_table_privilege(c.oid, 'UPDATE') );
1476 GRANT SELECT ON sequences TO PUBLIC;
1481 * SQL_FEATURES table
1484 CREATE TABLE sql_features (
1485 feature_id character_data,
1486 feature_name character_data,
1487 sub_feature_id character_data,
1488 sub_feature_name character_data,
1489 is_supported character_data,
1490 is_verified_by character_data,
1491 comments character_data
1494 -- Will be filled with external data by initdb.
1496 GRANT SELECT ON sql_features TO PUBLIC;
1501 * SQL_IMPLEMENTATION_INFO table
1504 -- Note: Implementation information items are defined in ISO/IEC 9075-3:2003,
1507 CREATE TABLE sql_implementation_info (
1508 implementation_info_id character_data,
1509 implementation_info_name character_data,
1510 integer_value cardinal_number,
1511 character_value character_data,
1512 comments character_data
1515 INSERT INTO sql_implementation_info VALUES ('10003', 'CATALOG NAME', NULL, 'Y', NULL);
1516 INSERT INTO sql_implementation_info VALUES ('10004', 'COLLATING SEQUENCE', NULL, '', 'not supported');
1517 INSERT INTO sql_implementation_info VALUES ('23', 'CURSOR COMMIT BEHAVIOR', 1, NULL, 'close cursors and retain prepared statements');
1518 INSERT INTO sql_implementation_info VALUES ('2', 'DATA SOURCE NAME', NULL, '', NULL);
1519 INSERT INTO sql_implementation_info VALUES ('17', 'DBMS NAME', NULL, (select trim(trailing ' ' from substring(version() from '^[^0-9]*'))), NULL);
1520 INSERT INTO sql_implementation_info VALUES ('18', 'DBMS VERSION', NULL, '???', NULL); -- filled by initdb
1521 INSERT INTO sql_implementation_info VALUES ('26', 'DEFAULT TRANSACTION ISOLATION', 2, NULL, 'READ COMMITTED; user-settable');
1522 INSERT INTO sql_implementation_info VALUES ('28', 'IDENTIFIER CASE', 3, NULL, 'stored in mixed case - case sensitive');
1523 INSERT INTO sql_implementation_info VALUES ('85', 'NULL COLLATION', 0, NULL, 'nulls higher than non-nulls');
1524 INSERT INTO sql_implementation_info VALUES ('13', 'SERVER NAME', NULL, '', NULL);
1525 INSERT INTO sql_implementation_info VALUES ('94', 'SPECIAL CHARACTERS', NULL, '', 'all non-ASCII characters allowed');
1526 INSERT INTO sql_implementation_info VALUES ('46', 'TRANSACTION CAPABLE', 2, NULL, 'both DML and DDL');
1528 GRANT SELECT ON sql_implementation_info TO PUBLIC;
1533 * SQL_LANGUAGES table
1536 CREATE TABLE sql_languages (
1537 sql_language_source character_data,
1538 sql_language_year character_data,
1539 sql_language_conformance character_data,
1540 sql_language_integrity character_data,
1541 sql_language_implementation character_data,
1542 sql_language_binding_style character_data,
1543 sql_language_programming_language character_data
1546 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'DIRECT', NULL);
1547 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
1548 INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'DIRECT', NULL);
1549 INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
1551 GRANT SELECT ON sql_languages TO PUBLIC;
1556 * SQL_PACKAGES table
1559 CREATE TABLE sql_packages (
1560 feature_id character_data,
1561 feature_name character_data,
1562 is_supported character_data,
1563 is_verified_by character_data,
1564 comments character_data
1567 INSERT INTO sql_packages VALUES ('PKG000', 'Core', 'NO', NULL, '');
1568 INSERT INTO sql_packages VALUES ('PKG001', 'Enhanced datetime facilities', 'YES', NULL, '');
1569 INSERT INTO sql_packages VALUES ('PKG002', 'Enhanced integrity management', 'NO', NULL, '');
1570 INSERT INTO sql_packages VALUES ('PKG003', 'OLAP facilities', 'NO', NULL, '');
1571 INSERT INTO sql_packages VALUES ('PKG004', 'PSM', 'NO', NULL, 'PL/pgSQL is similar.');
1572 INSERT INTO sql_packages VALUES ('PKG005', 'CLI', 'NO', NULL, 'ODBC is similar.');
1573 INSERT INTO sql_packages VALUES ('PKG006', 'Basic object support', 'NO', NULL, '');
1574 INSERT INTO sql_packages VALUES ('PKG007', 'Enhanced object support', 'NO', NULL, '');
1575 INSERT INTO sql_packages VALUES ('PKG008', 'Active database', 'NO', NULL, '');
1576 INSERT INTO sql_packages VALUES ('PKG010', 'OLAP', 'NO', NULL, 'NO');
1578 GRANT SELECT ON sql_packages TO PUBLIC;
1586 CREATE TABLE sql_parts (
1587 feature_id character_data,
1588 feature_name character_data,
1589 is_supported character_data,
1590 is_verified_by character_data,
1591 comments character_data
1594 INSERT INTO sql_parts VALUES ('1', 'Framework (SQL/Framework)', 'NO', NULL, '');
1595 INSERT INTO sql_parts VALUES ('2', 'Foundation (SQL/Foundation)', 'NO', NULL, '');
1596 INSERT INTO sql_parts VALUES ('3', 'Call-Level Interface (SQL/CLI)', 'NO', NULL, '');
1597 INSERT INTO sql_parts VALUES ('4', 'Persistent Stored Modules (SQL/PSM)', NULL, '');
1598 INSERT INTO sql_parts VALUES ('9', 'Management of External Data (SQL/MED)', NULL, '');
1599 INSERT INTO sql_parts VALUES ('10', 'Object Language Bindings (SQL/OLB)', NULL, '');
1600 INSERT INTO sql_parts VALUES ('11', 'Information and Definition Schema (SQL/Schemata)', NULL, '');
1601 INSERT INTO sql_parts VALUES ('13', 'Routines and Types Using the Java Programming Language (SQL/JRT)', NULL, '');
1602 INSERT INTO sql_parts VALUES ('14', 'XML-Related Specifications (SQL/XML)', NULL, '');
1610 -- Note: Sizing items are defined in ISO/IEC 9075-3:2003, clause 7.2.
1612 CREATE TABLE sql_sizing (
1613 sizing_id cardinal_number,
1614 sizing_name character_data,
1615 supported_value cardinal_number,
1616 comments character_data
1619 INSERT INTO sql_sizing VALUES (34, 'MAXIMUM CATALOG NAME LENGTH', 63, NULL);
1620 INSERT INTO sql_sizing VALUES (30, 'MAXIMUM COLUMN NAME LENGTH', 63, NULL);
1621 INSERT INTO sql_sizing VALUES (97, 'MAXIMUM COLUMNS IN GROUP BY', 0, NULL);
1622 INSERT INTO sql_sizing VALUES (99, 'MAXIMUM COLUMNS IN ORDER BY', 0, NULL);
1623 INSERT INTO sql_sizing VALUES (100, 'MAXIMUM COLUMNS IN SELECT', 1664, NULL); -- match MaxTupleAttributeNumber
1624 INSERT INTO sql_sizing VALUES (101, 'MAXIMUM COLUMNS IN TABLE', 1600, NULL); -- match MaxHeapAttributeNumber
1625 INSERT INTO sql_sizing VALUES (1, 'MAXIMUM CONCURRENT ACTIVITIES', 0, NULL);
1626 INSERT INTO sql_sizing VALUES (31, 'MAXIMUM CURSOR NAME LENGTH', 63, NULL);
1627 INSERT INTO sql_sizing VALUES (0, 'MAXIMUM DRIVER CONNECTIONS', NULL, NULL);
1628 INSERT INTO sql_sizing VALUES (10005, 'MAXIMUM IDENTIFIER LENGTH', 63, NULL);
1629 INSERT INTO sql_sizing VALUES (32, 'MAXIMUM SCHEMA NAME LENGTH', 63, NULL);
1630 INSERT INTO sql_sizing VALUES (20000, 'MAXIMUM STATEMENT OCTETS', 0, NULL);
1631 INSERT INTO sql_sizing VALUES (20001, 'MAXIMUM STATEMENT OCTETS DATA', 0, NULL);
1632 INSERT INTO sql_sizing VALUES (20002, 'MAXIMUM STATEMENT OCTETS SCHEMA', 0, NULL);
1633 INSERT INTO sql_sizing VALUES (35, 'MAXIMUM TABLE NAME LENGTH', 63, NULL);
1634 INSERT INTO sql_sizing VALUES (106, 'MAXIMUM TABLES IN SELECT', 0, NULL);
1635 INSERT INTO sql_sizing VALUES (107, 'MAXIMUM USER NAME LENGTH', 63, NULL);
1636 INSERT INTO sql_sizing VALUES (25000, 'MAXIMUM CURRENT DEFAULT TRANSFORM GROUP LENGTH', NULL, NULL);
1637 INSERT INTO sql_sizing VALUES (25001, 'MAXIMUM CURRENT TRANSFORM GROUP LENGTH', NULL, NULL);
1638 INSERT INTO sql_sizing VALUES (25002, 'MAXIMUM CURRENT PATH LENGTH', 0, NULL);
1639 INSERT INTO sql_sizing VALUES (25003, 'MAXIMUM CURRENT ROLE LENGTH', NULL, NULL);
1640 INSERT INTO sql_sizing VALUES (25004, 'MAXIMUM SESSION USER LENGTH', 63, NULL);
1641 INSERT INTO sql_sizing VALUES (25005, 'MAXIMUM SYSTEM USER LENGTH', 63, NULL);
1644 SET supported_value = (SELECT typlen-1 FROM pg_catalog.pg_type WHERE typname = 'name'),
1645 comments = 'Might be less, depending on character set.'
1646 WHERE supported_value = 63;
1648 GRANT SELECT ON sql_sizing TO PUBLIC;
1653 * SQL_SIZING_PROFILES table
1656 -- The data in this table are defined by various profiles of SQL.
1657 -- Since we don't have any information about such profiles, we provide
1660 CREATE TABLE sql_sizing_profiles (
1661 sizing_id cardinal_number,
1662 sizing_name character_data,
1663 profile_id character_data,
1664 required_value cardinal_number,
1665 comments character_data
1668 GRANT SELECT ON sql_sizing_profiles TO PUBLIC;
1673 * TABLE_CONSTRAINTS view
1676 CREATE VIEW table_constraints AS
1677 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1678 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
1679 CAST(c.conname AS sql_identifier) AS constraint_name,
1680 CAST(current_database() AS sql_identifier) AS table_catalog,
1681 CAST(nr.nspname AS sql_identifier) AS table_schema,
1682 CAST(r.relname AS sql_identifier) AS table_name,
1684 CASE c.contype WHEN 'c' THEN 'CHECK'
1685 WHEN 'f' THEN 'FOREIGN KEY'
1686 WHEN 'p' THEN 'PRIMARY KEY'
1687 WHEN 'u' THEN 'UNIQUE' END
1688 AS character_data) AS constraint_type,
1689 CAST(CASE WHEN c.condeferrable THEN 'YES' ELSE 'NO' END AS character_data)
1691 CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS character_data)
1692 AS initially_deferred
1694 FROM pg_namespace nc,
1699 WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace
1700 AND c.conrelid = r.oid
1702 AND (nr.nspname NOT LIKE 'pg!_temp!_%' ESCAPE '!' OR pg_catalog.pg_table_is_visible(r.oid))
1703 AND (pg_has_role(r.relowner, 'USAGE')
1704 -- SELECT privilege omitted, per SQL standard
1705 OR has_table_privilege(r.oid, 'INSERT')
1706 OR has_table_privilege(r.oid, 'UPDATE')
1707 OR has_table_privilege(r.oid, 'DELETE')
1708 OR has_table_privilege(r.oid, 'RULE')
1709 OR has_table_privilege(r.oid, 'REFERENCES')
1710 OR has_table_privilege(r.oid, 'TRIGGER') )
1714 -- not-null constraints
1716 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1717 CAST(nr.nspname AS sql_identifier) AS constraint_schema,
1718 CAST(nr.oid || '_' || r.oid || '_' || a.attnum || '_not_null' AS sql_identifier) AS constraint_name, -- XXX
1719 CAST(current_database() AS sql_identifier) AS table_catalog,
1720 CAST(nr.nspname AS sql_identifier) AS table_schema,
1721 CAST(r.relname AS sql_identifier) AS table_name,
1722 CAST('CHECK' AS character_data) AS constraint_type,
1723 CAST('NO' AS character_data) AS is_deferrable,
1724 CAST('NO' AS character_data) AS initially_deferred
1726 FROM pg_namespace nr,
1730 WHERE nr.oid = r.relnamespace
1731 AND r.oid = a.attrelid
1734 AND NOT a.attisdropped
1736 AND (nr.nspname NOT LIKE 'pg!_temp!_%' ESCAPE '!' OR pg_catalog.pg_table_is_visible(r.oid))
1737 AND (pg_has_role(r.relowner, 'USAGE')
1738 OR has_table_privilege(r.oid, 'SELECT')
1739 OR has_table_privilege(r.oid, 'INSERT')
1740 OR has_table_privilege(r.oid, 'UPDATE')
1741 OR has_table_privilege(r.oid, 'DELETE')
1742 OR has_table_privilege(r.oid, 'RULE')
1743 OR has_table_privilege(r.oid, 'REFERENCES')
1744 OR has_table_privilege(r.oid, 'TRIGGER') );
1746 GRANT SELECT ON table_constraints TO PUBLIC;
1751 * TABLE_METHOD_PRIVILEGES view
1754 -- feature not supported
1759 * TABLE_PRIVILEGES view
1762 CREATE VIEW table_privileges AS
1763 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1764 CAST(grantee.rolname AS sql_identifier) AS grantee,
1765 CAST(current_database() AS sql_identifier) AS table_catalog,
1766 CAST(nc.nspname AS sql_identifier) AS table_schema,
1767 CAST(c.relname AS sql_identifier) AS table_name,
1768 CAST(pr.type AS character_data) AS privilege_type,
1770 CASE WHEN aclcontains(c.relacl,
1771 makeaclitem(grantee.oid, u_grantor.oid, pr.type, true))
1772 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable,
1773 CAST('NO' AS character_data) AS with_hierarchy
1777 pg_authid u_grantor,
1779 SELECT oid, rolname FROM pg_authid
1781 SELECT 0::oid, 'PUBLIC'
1782 ) AS grantee (oid, rolname),
1783 (SELECT 'SELECT' UNION ALL
1784 SELECT 'DELETE' UNION ALL
1785 SELECT 'INSERT' UNION ALL
1786 SELECT 'UPDATE' UNION ALL
1787 SELECT 'REFERENCES' UNION ALL
1788 SELECT 'RULE' UNION ALL
1789 SELECT 'TRIGGER') AS pr (type)
1791 WHERE c.relnamespace = nc.oid
1792 AND c.relkind IN ('r', 'v')
1793 AND aclcontains(c.relacl,
1794 makeaclitem(grantee.oid, u_grantor.oid, pr.type, false))
1795 AND (pg_has_role(u_grantor.oid, 'USAGE')
1796 OR pg_has_role(grantee.oid, 'USAGE')
1797 OR grantee.rolname = 'PUBLIC');
1799 GRANT SELECT ON table_privileges TO PUBLIC;
1807 CREATE VIEW tables AS
1808 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
1809 CAST(nc.nspname AS sql_identifier) AS table_schema,
1810 CAST(c.relname AS sql_identifier) AS table_name,
1813 CASE WHEN nc.nspname LIKE 'pg!_temp!_%' ESCAPE '!' THEN 'LOCAL TEMPORARY'
1814 WHEN c.relkind = 'r' THEN 'BASE TABLE'
1815 WHEN c.relkind = 'v' THEN 'VIEW'
1817 AS character_data) AS table_type,
1819 CAST(null AS sql_identifier) AS self_referencing_column_name,
1820 CAST(null AS character_data) AS reference_generation,
1822 CAST(null AS sql_identifier) AS user_defined_type_catalog,
1823 CAST(null AS sql_identifier) AS user_defined_type_schema,
1824 CAST(null AS sql_identifier) AS user_defined_type_name,
1826 CAST(CASE WHEN c.relkind = 'r'
1827 THEN 'YES' ELSE 'NO' END AS character_data) AS is_insertable_into,
1828 CAST('NO' AS character_data) AS is_typed,
1830 CASE WHEN nc.nspname LIKE 'pg!_temp!_%' ESCAPE '!' THEN 'PRESERVE'
1832 AS character_data) AS commit_action
1834 FROM pg_namespace nc, pg_class c
1836 WHERE c.relnamespace = nc.oid
1837 AND c.relkind IN ('r', 'v')
1838 AND (nc.nspname NOT LIKE 'pg!_temp!_%' ESCAPE '!' OR pg_catalog.pg_table_is_visible(c.oid))
1839 AND (pg_has_role(c.relowner, 'USAGE')
1840 OR has_table_privilege(c.oid, 'SELECT')
1841 OR has_table_privilege(c.oid, 'INSERT')
1842 OR has_table_privilege(c.oid, 'UPDATE')
1843 OR has_table_privilege(c.oid, 'DELETE')
1844 OR has_table_privilege(c.oid, 'RULE')
1845 OR has_table_privilege(c.oid, 'REFERENCES')
1846 OR has_table_privilege(c.oid, 'TRIGGER') );
1848 GRANT SELECT ON tables TO PUBLIC;
1856 -- feature not supported
1864 -- feature not supported
1869 * TRIGGERED_UPDATE_COLUMNS view
1872 -- PostgreSQL doesn't allow the specification of individual triggered
1873 -- update columns, so this view is empty.
1875 CREATE VIEW triggered_update_columns AS
1876 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1877 CAST(null AS sql_identifier) AS trigger_schema,
1878 CAST(null AS sql_identifier) AS trigger_name,
1879 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1880 CAST(null AS sql_identifier) AS event_object_schema,
1881 CAST(null AS sql_identifier) AS event_object_table,
1882 CAST(null AS sql_identifier) AS event_object_column
1885 GRANT SELECT ON triggered_update_columns TO PUBLIC;
1890 * TRIGGER_COLUMN_USAGE view
1893 -- not tracked by PostgreSQL
1898 * TRIGGER_ROUTINE_USAGE view
1901 -- not tracked by PostgreSQL
1906 * TRIGGER_SEQUENCE_USAGE view
1909 -- not tracked by PostgreSQL
1914 * TRIGGER_TABLE_USAGE view
1917 -- not tracked by PostgreSQL
1925 CREATE VIEW triggers AS
1926 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1927 CAST(n.nspname AS sql_identifier) AS trigger_schema,
1928 CAST(t.tgname AS sql_identifier) AS trigger_name,
1929 CAST(em.text AS character_data) AS event_manipulation,
1930 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1931 CAST(n.nspname AS sql_identifier) AS event_object_schema,
1932 CAST(c.relname AS sql_identifier) AS event_object_table,
1933 CAST(null AS cardinal_number) AS action_order,
1934 CAST(null AS character_data) AS action_condition,
1936 substring(pg_get_triggerdef(t.oid) from
1937 position('EXECUTE PROCEDURE' in substring(pg_get_triggerdef(t.oid) from 48)) + 47)
1938 AS character_data) AS action_statement,
1940 CASE WHEN t.tgtype & 1 = 1 THEN 'ROW' ELSE 'STATEMENT' END
1941 AS character_data) AS action_orientation,
1943 CASE WHEN t.tgtype & 2 = 2 THEN 'BEFORE' ELSE 'AFTER' END
1944 AS character_data) AS condition_timing,
1945 CAST(null AS sql_identifier) AS condition_reference_old_table,
1946 CAST(null AS sql_identifier) AS condition_reference_new_table,
1947 CAST(null AS sql_identifier) AS condition_reference_old_row,
1948 CAST(null AS sql_identifier) AS condition_reference_new_row,
1949 CAST(null AS time_stamp) AS created
1951 FROM pg_namespace n, pg_class c, pg_trigger t,
1952 (SELECT 4, 'INSERT' UNION ALL
1953 SELECT 8, 'DELETE' UNION ALL
1954 SELECT 16, 'UPDATE') AS em (num, text)
1956 WHERE n.oid = c.relnamespace
1957 AND c.oid = t.tgrelid
1958 AND t.tgtype & em.num <> 0
1959 AND NOT t.tgisconstraint
1960 AND (n.nspname NOT LIKE 'pg!_temp!_%' ESCAPE '!' OR pg_catalog.pg_table_is_visible(c.oid))
1961 AND (pg_has_role(c.relowner, 'USAGE')
1962 -- SELECT privilege omitted, per SQL standard
1963 OR has_table_privilege(c.oid, 'INSERT')
1964 OR has_table_privilege(c.oid, 'UPDATE')
1965 OR has_table_privilege(c.oid, 'DELETE')
1966 OR has_table_privilege(c.oid, 'RULE')
1967 OR has_table_privilege(c.oid, 'REFERENCES')
1968 OR has_table_privilege(c.oid, 'TRIGGER') );
1970 GRANT SELECT ON triggers TO PUBLIC;
1975 * UDT_PRIVILEGES view
1978 -- feature not supported
1983 * USAGE_PRIVILEGES view
1986 -- Of the things currently implemented in PostgreSQL, usage privileges
1987 -- apply only to domains. Since domains have no real privileges, we
1988 -- represent all domains with implicit usage privilege here.
1990 CREATE VIEW usage_privileges AS
1991 SELECT CAST(u.rolname AS sql_identifier) AS grantor,
1992 CAST('PUBLIC' AS sql_identifier) AS grantee,
1993 CAST(current_database() AS sql_identifier) AS object_catalog,
1994 CAST(n.nspname AS sql_identifier) AS object_schema,
1995 CAST(t.typname AS sql_identifier) AS object_name,
1996 CAST('DOMAIN' AS character_data) AS object_type,
1997 CAST('USAGE' AS character_data) AS privilege_type,
1998 CAST('NO' AS character_data) AS is_grantable
2004 WHERE u.oid = t.typowner
2005 AND t.typnamespace = n.oid
2006 AND t.typtype = 'd';
2008 GRANT SELECT ON usage_privileges TO PUBLIC;
2013 * USER_DEFINED_TYPES view
2016 -- feature not supported
2024 CREATE VIEW view_column_usage AS
2026 CAST(current_database() AS sql_identifier) AS view_catalog,
2027 CAST(nv.nspname AS sql_identifier) AS view_schema,
2028 CAST(v.relname AS sql_identifier) AS view_name,
2029 CAST(current_database() AS sql_identifier) AS table_catalog,
2030 CAST(nt.nspname AS sql_identifier) AS table_schema,
2031 CAST(t.relname AS sql_identifier) AS table_name,
2032 CAST(a.attname AS sql_identifier) AS column_name
2034 FROM pg_namespace nv, pg_class v, pg_depend dv,
2035 pg_depend dt, pg_class t, pg_namespace nt,
2038 WHERE nv.oid = v.relnamespace
2040 AND v.oid = dv.refobjid
2041 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2042 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2043 AND dv.deptype = 'i'
2044 AND dv.objid = dt.objid
2045 AND dv.refobjid <> dt.refobjid
2046 AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2047 AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2048 AND dt.refobjid = t.oid
2049 AND t.relnamespace = nt.oid
2050 AND t.relkind IN ('r', 'v')
2051 AND t.oid = a.attrelid
2052 AND dt.refobjsubid = a.attnum
2053 AND pg_has_role(t.relowner, 'USAGE');
2055 GRANT SELECT ON view_column_usage TO PUBLIC;
2060 * VIEW_ROUTINE_USAGE
2063 CREATE VIEW view_routine_usage AS
2065 CAST(current_database() AS sql_identifier) AS table_catalog,
2066 CAST(nv.nspname AS sql_identifier) AS table_schema,
2067 CAST(v.relname AS sql_identifier) AS table_name,
2068 CAST(current_database() AS sql_identifier) AS specific_catalog,
2069 CAST(np.nspname AS sql_identifier) AS specific_schema,
2070 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name
2072 FROM pg_namespace nv, pg_class v, pg_depend dv,
2073 pg_depend dp, pg_proc p, pg_namespace np
2075 WHERE nv.oid = v.relnamespace
2077 AND v.oid = dv.refobjid
2078 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2079 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2080 AND dv.deptype = 'i'
2081 AND dv.objid = dp.objid
2082 AND dp.classid = 'pg_catalog.pg_rewrite'::regclass
2083 AND dp.refclassid = 'pg_catalog.pg_proc'::regclass
2084 AND dp.refobjid = p.oid
2085 AND p.pronamespace = np.oid
2086 AND pg_has_role(p.proowner, 'USAGE');
2088 GRANT SELECT ON view_routine_usage TO PUBLIC;
2096 CREATE VIEW view_table_usage AS
2098 CAST(current_database() AS sql_identifier) AS view_catalog,
2099 CAST(nv.nspname AS sql_identifier) AS view_schema,
2100 CAST(v.relname AS sql_identifier) AS view_name,
2101 CAST(current_database() AS sql_identifier) AS table_catalog,
2102 CAST(nt.nspname AS sql_identifier) AS table_schema,
2103 CAST(t.relname AS sql_identifier) AS table_name
2105 FROM pg_namespace nv, pg_class v, pg_depend dv,
2106 pg_depend dt, pg_class t, pg_namespace nt
2108 WHERE nv.oid = v.relnamespace
2110 AND v.oid = dv.refobjid
2111 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2112 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2113 AND dv.deptype = 'i'
2114 AND dv.objid = dt.objid
2115 AND dv.refobjid <> dt.refobjid
2116 AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2117 AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2118 AND dt.refobjid = t.oid
2119 AND t.relnamespace = nt.oid
2120 AND t.relkind IN ('r', 'v')
2121 AND pg_has_role(t.relowner, 'USAGE');
2123 GRANT SELECT ON view_table_usage TO PUBLIC;
2131 CREATE VIEW views AS
2132 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
2133 CAST(nc.nspname AS sql_identifier) AS table_schema,
2134 CAST(c.relname AS sql_identifier) AS table_name,
2137 CASE WHEN pg_has_role(c.relowner, 'USAGE')
2138 THEN pg_get_viewdef(c.oid)
2140 AS character_data) AS view_definition,
2142 CAST('NONE' AS character_data) AS check_option,
2145 CASE WHEN EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = 2 AND is_instead)
2146 AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = 4 AND is_instead)
2147 THEN 'YES' ELSE 'NO' END
2148 AS character_data) AS is_updatable,
2151 CASE WHEN EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = 3 AND is_instead)
2152 THEN 'YES' ELSE 'NO' END
2153 AS character_data) AS is_insertable_into
2155 FROM pg_namespace nc, pg_class c
2157 WHERE c.relnamespace = nc.oid
2159 AND (nc.nspname NOT LIKE 'pg!_temp!_%' ESCAPE '!' OR pg_catalog.pg_table_is_visible(c.oid))
2160 AND (pg_has_role(c.relowner, 'USAGE')
2161 OR has_table_privilege(c.oid, 'SELECT')
2162 OR has_table_privilege(c.oid, 'INSERT')
2163 OR has_table_privilege(c.oid, 'UPDATE')
2164 OR has_table_privilege(c.oid, 'DELETE')
2165 OR has_table_privilege(c.oid, 'RULE')
2166 OR has_table_privilege(c.oid, 'REFERENCES')
2167 OR has_table_privilege(c.oid, 'TRIGGER') );
2169 GRANT SELECT ON views TO PUBLIC;
2172 -- The following views have dependencies that force them to appear out of order.
2176 * DATA_TYPE_PRIVILEGES view
2179 CREATE VIEW data_type_privileges AS
2180 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2181 CAST(x.objschema AS sql_identifier) AS object_schema,
2182 CAST(x.objname AS sql_identifier) AS object_name,
2183 CAST(x.objtype AS character_data) AS object_type,
2184 CAST(x.objdtdid AS sql_identifier) AS dtd_identifier
2188 SELECT udt_schema, udt_name, 'USER-DEFINED TYPE'::text, dtd_identifier FROM attributes
2190 SELECT table_schema, table_name, 'TABLE'::text, dtd_identifier FROM columns
2192 SELECT domain_schema, domain_name, 'DOMAIN'::text, dtd_identifier FROM domains
2194 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM parameters
2196 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM routines
2197 ) AS x (objschema, objname, objtype, objdtdid);
2199 GRANT SELECT ON data_type_privileges TO PUBLIC;
2204 * ELEMENT_TYPES view
2207 CREATE VIEW element_types AS
2208 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2209 CAST(n.nspname AS sql_identifier) AS object_schema,
2210 CAST(x.objname AS sql_identifier) AS object_name,
2211 CAST(x.objtype AS character_data) AS object_type,
2212 CAST(x.objdtdid AS sql_identifier) AS collection_type_identifier,
2214 CASE WHEN nbt.nspname = 'pg_catalog' THEN format_type(bt.oid, null)
2215 ELSE 'USER-DEFINED' END AS character_data) AS data_type,
2217 CAST(null AS cardinal_number) AS character_maximum_length,
2218 CAST(null AS cardinal_number) AS character_octet_length,
2219 CAST(null AS sql_identifier) AS character_set_catalog,
2220 CAST(null AS sql_identifier) AS character_set_schema,
2221 CAST(null AS sql_identifier) AS character_set_name,
2222 CAST(null AS sql_identifier) AS collation_catalog,
2223 CAST(null AS sql_identifier) AS collation_schema,
2224 CAST(null AS sql_identifier) AS collation_name,
2225 CAST(null AS cardinal_number) AS numeric_precision,
2226 CAST(null AS cardinal_number) AS numeric_precision_radix,
2227 CAST(null AS cardinal_number) AS numeric_scale,
2228 CAST(null AS cardinal_number) AS datetime_precision,
2229 CAST(null AS character_data) AS interval_type,
2230 CAST(null AS character_data) AS interval_precision,
2232 CAST(null AS character_data) AS domain_default, -- XXX maybe a bug in the standard
2234 CAST(current_database() AS sql_identifier) AS udt_catalog,
2235 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
2236 CAST(bt.typname AS sql_identifier) AS udt_name,
2238 CAST(null AS sql_identifier) AS scope_catalog,
2239 CAST(null AS sql_identifier) AS scope_schema,
2240 CAST(null AS sql_identifier) AS scope_name,
2242 CAST(null AS cardinal_number) AS maximum_cardinality,
2243 CAST('a' || x.objdtdid AS sql_identifier) AS dtd_identifier
2245 FROM pg_namespace n, pg_type at, pg_namespace nbt, pg_type bt,
2248 SELECT c.relnamespace, CAST(c.relname AS sql_identifier),
2249 'TABLE'::text, a.attnum, a.atttypid
2250 FROM pg_class c, pg_attribute a
2251 WHERE c.oid = a.attrelid
2252 AND c.relkind IN ('r', 'v')
2253 AND attnum > 0 AND NOT attisdropped
2258 SELECT t.typnamespace, CAST(t.typname AS sql_identifier),
2259 'DOMAIN'::text, 1, t.typbasetype
2261 WHERE t.typtype = 'd'
2266 SELECT pronamespace, CAST(proname || '_' || CAST(oid AS text) AS sql_identifier),
2267 'ROUTINE'::text, (ss.x).n, (ss.x).x
2268 FROM (SELECT p.pronamespace, p.proname, p.oid,
2269 _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
2270 FROM pg_proc p) AS ss
2275 SELECT p.pronamespace, CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier),
2276 'ROUTINE'::text, 0, p.prorettype
2279 ) AS x (objschema, objname, objtype, objdtdid, objtypeid)
2281 WHERE n.oid = x.objschema
2282 AND at.oid = x.objtypeid
2283 AND (at.typelem <> 0 AND at.typlen = -1)
2284 AND at.typelem = bt.oid
2285 AND nbt.oid = bt.typnamespace
2287 AND (n.nspname, x.objname, x.objtype, x.objdtdid) IN
2288 ( SELECT object_schema, object_name, object_type, dtd_identifier
2289 FROM data_type_privileges );
2291 GRANT SELECT ON element_types TO PUBLIC;