2 * SQL Information Schema
3 * as defined in ISO/IEC 9075-11:2003
5 * Copyright (c) 2003-2008, PostgreSQL Global Development Group
7 * $PostgreSQL: pgsql/src/backend/catalog/information_schema.sql,v 1.43 2008/01/01 19:45:48 momjian 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 /* Get the OID of the unique index that an FK constraint depends on */
56 CREATE FUNCTION _pg_underlying_index(oid) RETURNS oid
57 LANGUAGE sql STRICT STABLE
59 SELECT refobjid FROM pg_catalog.pg_depend
60 WHERE classid = 'pg_catalog.pg_constraint'::pg_catalog.regclass AND
62 refclassid = 'pg_catalog.pg_class'::pg_catalog.regclass AND
63 refobjsubid = 0 AND deptype = 'n'
66 /* Given an index's OID and an underlying-table column number, return the
67 * column's position in the index (NULL if not there) */
68 CREATE FUNCTION _pg_index_position(oid, smallint) RETURNS int
69 LANGUAGE sql STRICT STABLE
72 (SELECT information_schema._pg_expandarray(indkey) AS a
73 FROM pg_catalog.pg_index WHERE indexrelid = $1) ss
77 CREATE FUNCTION _pg_truetypid(pg_attribute, pg_type) RETURNS oid
80 RETURNS NULL ON NULL INPUT
82 $$SELECT CASE WHEN $2.typtype = 'd' THEN $2.typbasetype ELSE $1.atttypid END$$;
84 CREATE FUNCTION _pg_truetypmod(pg_attribute, pg_type) RETURNS int4
87 RETURNS NULL ON NULL INPUT
89 $$SELECT CASE WHEN $2.typtype = 'd' THEN $2.typtypmod ELSE $1.atttypmod END$$;
91 -- these functions encapsulate knowledge about the encoding of typmod:
93 CREATE FUNCTION _pg_char_max_length(typid oid, typmod int4) RETURNS integer
96 RETURNS NULL ON NULL INPUT
99 CASE WHEN $2 = -1 /* default typmod */
101 WHEN $1 IN (1042, 1043) /* char, varchar */
103 WHEN $1 IN (1560, 1562) /* bit, varbit */
108 CREATE FUNCTION _pg_char_octet_length(typid oid, typmod int4) RETURNS integer
111 RETURNS NULL ON NULL INPUT
114 CASE WHEN $1 IN (25, 1042, 1043) /* text, char, varchar */
115 THEN CAST(2^30 AS integer)
119 CREATE FUNCTION _pg_numeric_precision(typid oid, typmod int4) RETURNS integer
122 RETURNS NULL ON NULL INPUT
126 WHEN 21 /*int2*/ THEN 16
127 WHEN 23 /*int4*/ THEN 32
128 WHEN 20 /*int8*/ THEN 64
129 WHEN 1700 /*numeric*/ THEN
132 ELSE (($2 - 4) >> 16) & 65535
134 WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/
135 WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/
139 CREATE FUNCTION _pg_numeric_precision_radix(typid oid, typmod int4) RETURNS integer
142 RETURNS NULL ON NULL INPUT
145 CASE WHEN $1 IN (21, 23, 20, 700, 701) THEN 2
146 WHEN $1 IN (1700) THEN 10
150 CREATE FUNCTION _pg_numeric_scale(typid oid, typmod int4) RETURNS integer
153 RETURNS NULL ON NULL INPUT
156 CASE WHEN $1 IN (21, 23, 20) THEN 0
157 WHEN $1 IN (1700) THEN
160 ELSE ($2 - 4) & 65535
165 CREATE FUNCTION _pg_datetime_precision(typid oid, typmod int4) RETURNS integer
168 RETURNS NULL ON NULL INPUT
171 CASE WHEN $2 = -1 /* default typmod */
173 WHEN $1 IN (1083, 1114, 1184, 1266) /* time, timestamp, same + tz */
175 WHEN $1 IN (1186) /* interval */
181 -- 5.2 INFORMATION_SCHEMA_CATALOG_NAME view appears later.
186 * CARDINAL_NUMBER domain
189 CREATE DOMAIN cardinal_number AS integer
190 CONSTRAINT cardinal_number_domain_check CHECK (value >= 0);
195 * CHARACTER_DATA domain
198 CREATE DOMAIN character_data AS character varying;
203 * SQL_IDENTIFIER domain
206 CREATE DOMAIN sql_identifier AS character varying;
211 * INFORMATION_SCHEMA_CATALOG_NAME view
214 CREATE VIEW information_schema_catalog_name AS
215 SELECT CAST(current_database() AS sql_identifier) AS catalog_name;
217 GRANT SELECT ON information_schema_catalog_name TO PUBLIC;
225 CREATE DOMAIN time_stamp AS timestamp(2) with time zone
226 DEFAULT current_timestamp(2);
229 -- 5.7 ADMINISTRABLE_ROLE_AUTHORIZATIONS view appears later.
234 * APPLICABLE_ROLES view
237 CREATE VIEW applicable_roles AS
238 SELECT CAST(a.rolname AS sql_identifier) AS grantee,
239 CAST(b.rolname AS sql_identifier) AS role_name,
240 CAST(CASE WHEN m.admin_option THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
241 FROM pg_auth_members m
242 JOIN pg_authid a ON (m.member = a.oid)
243 JOIN pg_authid b ON (m.roleid = b.oid)
244 WHERE pg_has_role(a.oid, 'USAGE');
246 GRANT SELECT ON applicable_roles TO PUBLIC;
251 * ADMINISTRABLE_ROLE_AUTHORIZATIONS view
254 CREATE VIEW administrable_role_authorizations AS
256 FROM applicable_roles
257 WHERE is_grantable = 'YES';
259 GRANT SELECT ON administrable_role_authorizations TO PUBLIC;
267 -- feature not supported
275 CREATE VIEW attributes AS
276 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
277 CAST(nc.nspname AS sql_identifier) AS udt_schema,
278 CAST(c.relname AS sql_identifier) AS udt_name,
279 CAST(a.attname AS sql_identifier) AS attribute_name,
280 CAST(a.attnum AS cardinal_number) AS ordinal_position,
281 CAST(pg_get_expr(ad.adbin, ad.adrelid) AS character_data) AS attribute_default,
282 CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
287 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
288 WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, null)
289 ELSE 'USER-DEFINED' END
294 _pg_char_max_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
296 AS character_maximum_length,
299 _pg_char_octet_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
301 AS character_octet_length,
303 CAST(null AS sql_identifier) AS character_set_catalog,
304 CAST(null AS sql_identifier) AS character_set_schema,
305 CAST(null AS sql_identifier) AS character_set_name,
307 CAST(null AS sql_identifier) AS collation_catalog,
308 CAST(null AS sql_identifier) AS collation_schema,
309 CAST(null AS sql_identifier) AS collation_name,
312 _pg_numeric_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
314 AS numeric_precision,
317 _pg_numeric_precision_radix(_pg_truetypid(a, t), _pg_truetypmod(a, t))
319 AS numeric_precision_radix,
322 _pg_numeric_scale(_pg_truetypid(a, t), _pg_truetypmod(a, t))
327 _pg_datetime_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
329 AS datetime_precision,
331 CAST(null AS character_data) AS interval_type, -- FIXME
332 CAST(null AS character_data) AS interval_precision, -- FIXME
334 CAST(current_database() AS sql_identifier) AS attribute_udt_catalog,
335 CAST(nt.nspname AS sql_identifier) AS attribute_udt_schema,
336 CAST(t.typname AS sql_identifier) AS attribute_udt_name,
338 CAST(null AS sql_identifier) AS scope_catalog,
339 CAST(null AS sql_identifier) AS scope_schema,
340 CAST(null AS sql_identifier) AS scope_name,
342 CAST(null AS cardinal_number) AS maximum_cardinality,
343 CAST(a.attnum AS sql_identifier) AS dtd_identifier,
344 CAST('NO' AS character_data) AS is_derived_reference_attribute
346 FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum),
347 pg_class c, pg_namespace nc,
348 (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
350 WHERE a.attrelid = c.oid
351 AND a.atttypid = t.oid
352 AND nc.oid = c.relnamespace
353 AND a.attnum > 0 AND NOT a.attisdropped
354 AND c.relkind in ('c');
356 GRANT SELECT ON attributes TO PUBLIC;
361 * CHARACTER_SETS view
364 -- feature not supported
369 * CHECK_CONSTRAINT_ROUTINE_USAGE view
372 CREATE VIEW check_constraint_routine_usage AS
373 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
374 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
375 CAST(c.conname AS sql_identifier) AS constraint_name,
376 CAST(current_database() AS sql_identifier) AS specific_catalog,
377 CAST(np.nspname AS sql_identifier) AS specific_schema,
378 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name
379 FROM pg_namespace nc, pg_constraint c, pg_depend d, pg_proc p, pg_namespace np
380 WHERE nc.oid = c.connamespace
383 AND d.classid = 'pg_catalog.pg_constraint'::regclass
384 AND d.refobjid = p.oid
385 AND d.refclassid = 'pg_catalog.pg_proc'::regclass
386 AND p.pronamespace = np.oid
387 AND pg_has_role(p.proowner, 'USAGE');
389 GRANT SELECT ON check_constraint_routine_usage TO PUBLIC;
394 * CHECK_CONSTRAINTS view
397 CREATE VIEW check_constraints AS
398 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
399 CAST(rs.nspname AS sql_identifier) AS constraint_schema,
400 CAST(con.conname AS sql_identifier) AS constraint_name,
401 CAST(substring(pg_get_constraintdef(con.oid) from 7) AS character_data)
403 FROM pg_constraint con
404 LEFT OUTER JOIN pg_namespace rs ON (rs.oid = con.connamespace)
405 LEFT OUTER JOIN pg_class c ON (c.oid = con.conrelid)
406 LEFT OUTER JOIN pg_type t ON (t.oid = con.contypid)
407 WHERE pg_has_role(coalesce(c.relowner, t.typowner), 'USAGE')
408 AND con.contype = 'c'
411 -- not-null constraints
413 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
414 CAST(n.nspname AS sql_identifier) AS constraint_schema,
415 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
416 CAST(a.attname || ' IS NOT NULL' AS character_data)
418 FROM pg_namespace n, pg_class r, pg_attribute a
419 WHERE n.oid = r.relnamespace
420 AND r.oid = a.attrelid
422 AND NOT a.attisdropped
425 AND pg_has_role(r.relowner, 'USAGE');
427 GRANT SELECT ON check_constraints TO PUBLIC;
435 -- feature not supported
439 * COLLATION_CHARACTER_SET_APPLICABILITY view
442 -- feature not supported
447 * COLUMN_COLUMN_USAGE view
450 -- feature not supported
455 * COLUMN_DOMAIN_USAGE view
458 CREATE VIEW column_domain_usage AS
459 SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
460 CAST(nt.nspname AS sql_identifier) AS domain_schema,
461 CAST(t.typname AS sql_identifier) AS domain_name,
462 CAST(current_database() AS sql_identifier) AS table_catalog,
463 CAST(nc.nspname AS sql_identifier) AS table_schema,
464 CAST(c.relname AS sql_identifier) AS table_name,
465 CAST(a.attname AS sql_identifier) AS column_name
467 FROM pg_type t, pg_namespace nt, pg_class c, pg_namespace nc,
470 WHERE t.typnamespace = nt.oid
471 AND c.relnamespace = nc.oid
472 AND a.attrelid = c.oid
473 AND a.atttypid = t.oid
475 AND c.relkind IN ('r', 'v')
477 AND NOT a.attisdropped
478 AND pg_has_role(t.typowner, 'USAGE');
480 GRANT SELECT ON column_domain_usage TO PUBLIC;
488 CREATE VIEW column_privileges AS
489 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
490 CAST(grantee.rolname AS sql_identifier) AS grantee,
491 CAST(current_database() AS sql_identifier) AS table_catalog,
492 CAST(nc.nspname AS sql_identifier) AS table_schema,
493 CAST(c.relname AS sql_identifier) AS table_name,
494 CAST(a.attname AS sql_identifier) AS column_name,
495 CAST(pr.type AS character_data) AS privilege_type,
497 CASE WHEN aclcontains(c.relacl,
498 makeaclitem(grantee.oid, u_grantor.oid, pr.type, true))
499 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
506 SELECT oid, rolname FROM pg_authid
508 SELECT 0::oid, 'PUBLIC'
509 ) AS grantee (oid, rolname),
510 (SELECT 'SELECT' UNION ALL
511 SELECT 'INSERT' UNION ALL
512 SELECT 'UPDATE' UNION ALL
513 SELECT 'REFERENCES') AS pr (type)
515 WHERE a.attrelid = c.oid
516 AND c.relnamespace = nc.oid
518 AND NOT a.attisdropped
519 AND c.relkind IN ('r', 'v')
520 AND aclcontains(c.relacl,
521 makeaclitem(grantee.oid, u_grantor.oid, pr.type, false))
522 AND (pg_has_role(u_grantor.oid, 'USAGE')
523 OR pg_has_role(grantee.oid, 'USAGE')
524 OR grantee.rolname = 'PUBLIC');
526 GRANT SELECT ON column_privileges TO PUBLIC;
531 * COLUMN_UDT_USAGE view
534 CREATE VIEW column_udt_usage AS
535 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
536 CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,
537 CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,
538 CAST(current_database() AS sql_identifier) AS table_catalog,
539 CAST(nc.nspname AS sql_identifier) AS table_schema,
540 CAST(c.relname AS sql_identifier) AS table_name,
541 CAST(a.attname AS sql_identifier) AS column_name
543 FROM pg_attribute a, pg_class c, pg_namespace nc,
544 (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
545 LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
546 ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
548 WHERE a.attrelid = c.oid
549 AND a.atttypid = t.oid
550 AND nc.oid = c.relnamespace
551 AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
552 AND pg_has_role(coalesce(bt.typowner, t.typowner), 'USAGE');
554 GRANT SELECT ON column_udt_usage TO PUBLIC;
562 CREATE VIEW columns AS
563 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
564 CAST(nc.nspname AS sql_identifier) AS table_schema,
565 CAST(c.relname AS sql_identifier) AS table_name,
566 CAST(a.attname AS sql_identifier) AS column_name,
567 CAST(a.attnum AS cardinal_number) AS ordinal_position,
568 CAST(pg_get_expr(ad.adbin, ad.adrelid) AS character_data) AS column_default,
569 CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
574 CASE WHEN t.typtype = 'd' THEN
575 CASE WHEN bt.typelem <> 0 AND bt.typlen = -1 THEN 'ARRAY'
576 WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
577 ELSE 'USER-DEFINED' END
579 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
580 WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, null)
581 ELSE 'USER-DEFINED' END
587 _pg_char_max_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
589 AS character_maximum_length,
592 _pg_char_octet_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
594 AS character_octet_length,
597 _pg_numeric_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
599 AS numeric_precision,
602 _pg_numeric_precision_radix(_pg_truetypid(a, t), _pg_truetypmod(a, t))
604 AS numeric_precision_radix,
607 _pg_numeric_scale(_pg_truetypid(a, t), _pg_truetypmod(a, t))
612 _pg_datetime_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
614 AS datetime_precision,
616 CAST(null AS character_data) AS interval_type, -- FIXME
617 CAST(null AS character_data) AS interval_precision, -- FIXME
619 CAST(null AS sql_identifier) AS character_set_catalog,
620 CAST(null AS sql_identifier) AS character_set_schema,
621 CAST(null AS sql_identifier) AS character_set_name,
623 CAST(null AS sql_identifier) AS collation_catalog,
624 CAST(null AS sql_identifier) AS collation_schema,
625 CAST(null AS sql_identifier) AS collation_name,
627 CAST(CASE WHEN t.typtype = 'd' THEN current_database() ELSE null END
628 AS sql_identifier) AS domain_catalog,
629 CAST(CASE WHEN t.typtype = 'd' THEN nt.nspname ELSE null END
630 AS sql_identifier) AS domain_schema,
631 CAST(CASE WHEN t.typtype = 'd' THEN t.typname ELSE null END
632 AS sql_identifier) AS domain_name,
634 CAST(current_database() AS sql_identifier) AS udt_catalog,
635 CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,
636 CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,
638 CAST(null AS sql_identifier) AS scope_catalog,
639 CAST(null AS sql_identifier) AS scope_schema,
640 CAST(null AS sql_identifier) AS scope_name,
642 CAST(null AS cardinal_number) AS maximum_cardinality,
643 CAST(a.attnum AS sql_identifier) AS dtd_identifier,
644 CAST('NO' AS character_data) AS is_self_referencing,
646 CAST('NO' AS character_data) AS is_identity,
647 CAST(null AS character_data) AS identity_generation,
648 CAST(null AS character_data) AS identity_start,
649 CAST(null AS character_data) AS identity_increment,
650 CAST(null AS character_data) AS identity_maximum,
651 CAST(null AS character_data) AS identity_minimum,
652 CAST(null AS character_data) AS identity_cycle,
654 CAST('NEVER' AS character_data) AS is_generated,
655 CAST(null AS character_data) AS generation_expression,
657 CAST(CASE WHEN c.relkind = 'r'
658 THEN 'YES' ELSE 'NO' END AS character_data) AS is_updatable
660 FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum),
661 pg_class c, pg_namespace nc,
662 (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
663 LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
664 ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
666 WHERE a.attrelid = c.oid
667 AND a.atttypid = t.oid
668 AND nc.oid = c.relnamespace
669 AND (NOT pg_is_other_temp_schema(nc.oid))
671 AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
673 AND (pg_has_role(c.relowner, 'USAGE')
674 OR has_table_privilege(c.oid, 'SELECT')
675 OR has_table_privilege(c.oid, 'INSERT')
676 OR has_table_privilege(c.oid, 'UPDATE')
677 OR has_table_privilege(c.oid, 'REFERENCES') );
679 GRANT SELECT ON columns TO PUBLIC;
684 * CONSTRAINT_COLUMN_USAGE view
687 CREATE VIEW constraint_column_usage AS
688 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
689 CAST(tblschema AS sql_identifier) AS table_schema,
690 CAST(tblname AS sql_identifier) AS table_name,
691 CAST(colname AS sql_identifier) AS column_name,
692 CAST(current_database() AS sql_identifier) AS constraint_catalog,
693 CAST(cstrschema AS sql_identifier) AS constraint_schema,
694 CAST(cstrname AS sql_identifier) AS constraint_name
697 /* check constraints */
698 SELECT DISTINCT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
699 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_depend d, pg_namespace nc, pg_constraint c
700 WHERE nr.oid = r.relnamespace
701 AND r.oid = a.attrelid
702 AND d.refclassid = 'pg_catalog.pg_class'::regclass
703 AND d.refobjid = r.oid
704 AND d.refobjsubid = a.attnum
705 AND d.classid = 'pg_catalog.pg_constraint'::regclass
707 AND c.connamespace = nc.oid
710 AND NOT a.attisdropped
714 /* unique/primary key/foreign key constraints */
715 SELECT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
716 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc,
718 WHERE nr.oid = r.relnamespace
719 AND r.oid = a.attrelid
720 AND nc.oid = c.connamespace
721 AND (CASE WHEN c.contype = 'f' THEN r.oid = c.confrelid AND a.attnum = ANY (c.confkey)
722 ELSE r.oid = c.conrelid AND a.attnum = ANY (c.conkey) END)
723 AND NOT a.attisdropped
724 AND c.contype IN ('p', 'u', 'f')
727 ) AS x (tblschema, tblname, tblowner, colname, cstrschema, cstrname)
729 WHERE pg_has_role(x.tblowner, 'USAGE');
731 GRANT SELECT ON constraint_column_usage TO PUBLIC;
736 * CONSTRAINT_TABLE_USAGE view
739 CREATE VIEW constraint_table_usage AS
740 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
741 CAST(nr.nspname AS sql_identifier) AS table_schema,
742 CAST(r.relname AS sql_identifier) AS table_name,
743 CAST(current_database() AS sql_identifier) AS constraint_catalog,
744 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
745 CAST(c.conname AS sql_identifier) AS constraint_name
747 FROM pg_constraint c, pg_namespace nc,
748 pg_class r, pg_namespace nr
750 WHERE c.connamespace = nc.oid AND r.relnamespace = nr.oid
751 AND ( (c.contype = 'f' AND c.confrelid = r.oid)
752 OR (c.contype IN ('p', 'u') AND c.conrelid = r.oid) )
754 AND pg_has_role(r.relowner, 'USAGE');
756 GRANT SELECT ON constraint_table_usage TO PUBLIC;
759 -- 5.23 DATA_TYPE_PRIVILEGES view appears later.
764 * DIRECT_SUPERTABLES view
767 -- feature not supported
772 * DIRECT_SUPERTYPES view
775 -- feature not supported
780 * DOMAIN_CONSTRAINTS view
783 CREATE VIEW domain_constraints AS
784 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
785 CAST(rs.nspname AS sql_identifier) AS constraint_schema,
786 CAST(con.conname AS sql_identifier) AS constraint_name,
787 CAST(current_database() AS sql_identifier) AS domain_catalog,
788 CAST(n.nspname AS sql_identifier) AS domain_schema,
789 CAST(t.typname AS sql_identifier) AS domain_name,
790 CAST(CASE WHEN condeferrable THEN 'YES' ELSE 'NO' END
791 AS character_data) AS is_deferrable,
792 CAST(CASE WHEN condeferred THEN 'YES' ELSE 'NO' END
793 AS character_data) AS initially_deferred
794 FROM pg_namespace rs, pg_namespace n, pg_constraint con, pg_type t
795 WHERE rs.oid = con.connamespace
796 AND n.oid = t.typnamespace
797 AND t.oid = con.contypid;
799 GRANT SELECT ON domain_constraints TO PUBLIC;
803 * DOMAIN_UDT_USAGE view
804 * apparently removed in SQL:2003
807 CREATE VIEW domain_udt_usage AS
808 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
809 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
810 CAST(bt.typname AS sql_identifier) AS udt_name,
811 CAST(current_database() AS sql_identifier) AS domain_catalog,
812 CAST(nt.nspname AS sql_identifier) AS domain_schema,
813 CAST(t.typname AS sql_identifier) AS domain_name
815 FROM pg_type t, pg_namespace nt,
816 pg_type bt, pg_namespace nbt
818 WHERE t.typnamespace = nt.oid
819 AND t.typbasetype = bt.oid
820 AND bt.typnamespace = nbt.oid
822 AND pg_has_role(bt.typowner, 'USAGE');
824 GRANT SELECT ON domain_udt_usage TO PUBLIC;
832 CREATE VIEW domains AS
833 SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
834 CAST(nt.nspname AS sql_identifier) AS domain_schema,
835 CAST(t.typname AS sql_identifier) AS domain_name,
838 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
839 WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
840 ELSE 'USER-DEFINED' END
845 _pg_char_max_length(t.typbasetype, t.typtypmod)
847 AS character_maximum_length,
850 _pg_char_octet_length(t.typbasetype, t.typtypmod)
852 AS character_octet_length,
854 CAST(null AS sql_identifier) AS character_set_catalog,
855 CAST(null AS sql_identifier) AS character_set_schema,
856 CAST(null AS sql_identifier) AS character_set_name,
858 CAST(null AS sql_identifier) AS collation_catalog,
859 CAST(null AS sql_identifier) AS collation_schema,
860 CAST(null AS sql_identifier) AS collation_name,
863 _pg_numeric_precision(t.typbasetype, t.typtypmod)
865 AS numeric_precision,
868 _pg_numeric_precision_radix(t.typbasetype, t.typtypmod)
870 AS numeric_precision_radix,
873 _pg_numeric_scale(t.typbasetype, t.typtypmod)
878 _pg_datetime_precision(t.typbasetype, t.typtypmod)
880 AS datetime_precision,
882 CAST(null AS character_data) AS interval_type, -- FIXME
883 CAST(null AS character_data) AS interval_precision, -- FIXME
885 CAST(t.typdefault AS character_data) AS domain_default,
887 CAST(current_database() AS sql_identifier) AS udt_catalog,
888 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
889 CAST(bt.typname AS sql_identifier) AS udt_name,
891 CAST(null AS sql_identifier) AS scope_catalog,
892 CAST(null AS sql_identifier) AS scope_schema,
893 CAST(null AS sql_identifier) AS scope_name,
895 CAST(null AS cardinal_number) AS maximum_cardinality,
896 CAST(1 AS sql_identifier) AS dtd_identifier
898 FROM pg_type t, pg_namespace nt,
899 pg_type bt, pg_namespace nbt
901 WHERE t.typnamespace = nt.oid
902 AND t.typbasetype = bt.oid
903 AND bt.typnamespace = nbt.oid
906 GRANT SELECT ON domains TO PUBLIC;
909 -- 5.28 ELEMENT_TYPES view appears later.
917 CREATE VIEW enabled_roles AS
918 SELECT CAST(a.rolname AS sql_identifier) AS role_name
920 WHERE pg_has_role(a.oid, 'USAGE');
922 GRANT SELECT ON enabled_roles TO PUBLIC;
930 -- feature not supported
935 * KEY_COLUMN_USAGE view
938 CREATE VIEW key_column_usage AS
939 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
940 CAST(nc_nspname AS sql_identifier) AS constraint_schema,
941 CAST(conname AS sql_identifier) AS constraint_name,
942 CAST(current_database() AS sql_identifier) AS table_catalog,
943 CAST(nr_nspname AS sql_identifier) AS table_schema,
944 CAST(relname AS sql_identifier) AS table_name,
945 CAST(a.attname AS sql_identifier) AS column_name,
946 CAST((ss.x).n AS cardinal_number) AS ordinal_position,
947 CAST(CASE WHEN contype = 'f' THEN
948 _pg_index_position(_pg_underlying_index(ss.coid),
949 ss.confkey[(ss.x).n])
951 END AS cardinal_number)
952 AS position_in_unique_constraint
954 (SELECT r.oid AS roid, r.relname, nc.nspname AS nc_nspname,
955 nr.nspname AS nr_nspname,
956 c.oid AS coid, c.conname, c.contype, c.confkey, c.confrelid,
957 _pg_expandarray(c.conkey) AS x
958 FROM pg_namespace nr, pg_class r, pg_namespace nc,
960 WHERE nr.oid = r.relnamespace
961 AND r.oid = c.conrelid
962 AND nc.oid = c.connamespace
963 AND c.contype IN ('p', 'u', 'f')
965 AND (NOT pg_is_other_temp_schema(nr.oid))
966 AND (pg_has_role(r.relowner, 'USAGE')
967 OR has_table_privilege(r.oid, 'SELECT')
968 OR has_table_privilege(r.oid, 'INSERT')
969 OR has_table_privilege(r.oid, 'UPDATE')
970 OR has_table_privilege(r.oid, 'REFERENCES')) ) AS ss
971 WHERE ss.roid = a.attrelid
972 AND a.attnum = (ss.x).x
973 AND NOT a.attisdropped;
975 GRANT SELECT ON key_column_usage TO PUBLIC;
980 * METHOD_SPECIFICATION_PARAMETERS view
983 -- feature not supported
988 * METHOD_SPECIFICATIONS view
991 -- feature not supported
999 CREATE VIEW parameters AS
1000 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
1001 CAST(n_nspname AS sql_identifier) AS specific_schema,
1002 CAST(proname || '_' || CAST(p_oid AS text) AS sql_identifier) AS specific_name,
1003 CAST((ss.x).n AS cardinal_number) AS ordinal_position,
1005 CASE WHEN proargmodes IS NULL THEN 'IN'
1006 WHEN proargmodes[(ss.x).n] = 'i' THEN 'IN'
1007 WHEN proargmodes[(ss.x).n] = 'o' THEN 'OUT'
1008 WHEN proargmodes[(ss.x).n] = 'b' THEN 'INOUT'
1009 END AS character_data) AS parameter_mode,
1010 CAST('NO' AS character_data) AS is_result,
1011 CAST('NO' AS character_data) AS as_locator,
1012 CAST(NULLIF(proargnames[(ss.x).n], '') AS sql_identifier) AS parameter_name,
1014 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
1015 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
1016 ELSE 'USER-DEFINED' END AS character_data)
1018 CAST(null AS cardinal_number) AS character_maximum_length,
1019 CAST(null AS cardinal_number) AS character_octet_length,
1020 CAST(null AS sql_identifier) AS character_set_catalog,
1021 CAST(null AS sql_identifier) AS character_set_schema,
1022 CAST(null AS sql_identifier) AS character_set_name,
1023 CAST(null AS sql_identifier) AS collation_catalog,
1024 CAST(null AS sql_identifier) AS collation_schema,
1025 CAST(null AS sql_identifier) AS collation_name,
1026 CAST(null AS cardinal_number) AS numeric_precision,
1027 CAST(null AS cardinal_number) AS numeric_precision_radix,
1028 CAST(null AS cardinal_number) AS numeric_scale,
1029 CAST(null AS cardinal_number) AS datetime_precision,
1030 CAST(null AS character_data) AS interval_type,
1031 CAST(null AS character_data) AS interval_precision,
1032 CAST(current_database() AS sql_identifier) AS udt_catalog,
1033 CAST(nt.nspname AS sql_identifier) AS udt_schema,
1034 CAST(t.typname AS sql_identifier) AS udt_name,
1035 CAST(null AS sql_identifier) AS scope_catalog,
1036 CAST(null AS sql_identifier) AS scope_schema,
1037 CAST(null AS sql_identifier) AS scope_name,
1038 CAST(null AS cardinal_number) AS maximum_cardinality,
1039 CAST((ss.x).n AS sql_identifier) AS dtd_identifier
1041 FROM pg_type t, pg_namespace nt,
1042 (SELECT n.nspname AS n_nspname, p.proname, p.oid AS p_oid,
1043 p.proargnames, p.proargmodes,
1044 _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
1045 FROM pg_namespace n, pg_proc p
1046 WHERE n.oid = p.pronamespace
1047 AND (pg_has_role(p.proowner, 'USAGE') OR
1048 has_function_privilege(p.oid, 'EXECUTE'))) AS ss
1049 WHERE t.oid = (ss.x).x AND t.typnamespace = nt.oid;
1051 GRANT SELECT ON parameters TO PUBLIC;
1056 * REFERENCED_TYPES view
1059 -- feature not supported
1064 * REFERENTIAL_CONSTRAINTS view
1067 CREATE VIEW referential_constraints AS
1068 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1069 CAST(ncon.nspname AS sql_identifier) AS constraint_schema,
1070 CAST(con.conname AS sql_identifier) AS constraint_name,
1072 CASE WHEN npkc.nspname IS NULL THEN NULL
1073 ELSE current_database() END
1074 AS sql_identifier) AS unique_constraint_catalog,
1075 CAST(npkc.nspname AS sql_identifier) AS unique_constraint_schema,
1076 CAST(pkc.conname AS sql_identifier) AS unique_constraint_name,
1079 CASE con.confmatchtype WHEN 'f' THEN 'FULL'
1080 WHEN 'p' THEN 'PARTIAL'
1081 WHEN 'u' THEN 'NONE' END
1082 AS character_data) AS match_option,
1085 CASE con.confupdtype WHEN 'c' THEN 'CASCADE'
1086 WHEN 'n' THEN 'SET NULL'
1087 WHEN 'd' THEN 'SET DEFAULT'
1088 WHEN 'r' THEN 'RESTRICT'
1089 WHEN 'a' THEN 'NO ACTION' END
1090 AS character_data) AS update_rule,
1093 CASE con.confdeltype WHEN 'c' THEN 'CASCADE'
1094 WHEN 'n' THEN 'SET NULL'
1095 WHEN 'd' THEN 'SET DEFAULT'
1096 WHEN 'r' THEN 'RESTRICT'
1097 WHEN 'a' THEN 'NO ACTION' END
1098 AS character_data) AS delete_rule
1100 FROM (pg_namespace ncon
1101 INNER JOIN pg_constraint con ON ncon.oid = con.connamespace
1102 INNER JOIN pg_class c ON con.conrelid = c.oid)
1105 INNER JOIN pg_namespace npkc ON pkc.connamespace = npkc.oid)
1106 ON con.confrelid = pkc.conrelid
1107 AND _pg_keysequal(con.confkey, pkc.conkey)
1109 WHERE c.relkind = 'r'
1110 AND con.contype = 'f'
1111 AND (pkc.contype IN ('p', 'u') OR pkc.contype IS NULL)
1112 AND pg_has_role(c.relowner, 'USAGE');
1114 GRANT SELECT ON referential_constraints TO PUBLIC;
1119 * ROLE_COLUMN_GRANTS view
1122 CREATE VIEW role_column_grants AS
1123 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1124 CAST(g_grantee.rolname AS sql_identifier) AS grantee,
1125 CAST(current_database() AS sql_identifier) AS table_catalog,
1126 CAST(nc.nspname AS sql_identifier) AS table_schema,
1127 CAST(c.relname AS sql_identifier) AS table_name,
1128 CAST(a.attname AS sql_identifier) AS column_name,
1129 CAST(pr.type AS character_data) AS privilege_type,
1131 CASE WHEN aclcontains(c.relacl,
1132 makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, true))
1133 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
1135 FROM pg_attribute a,
1138 pg_authid u_grantor,
1139 pg_authid g_grantee,
1140 (SELECT 'SELECT' UNION ALL
1141 SELECT 'INSERT' UNION ALL
1142 SELECT 'UPDATE' UNION ALL
1143 SELECT 'REFERENCES') AS pr (type)
1145 WHERE a.attrelid = c.oid
1146 AND c.relnamespace = nc.oid
1148 AND NOT a.attisdropped
1149 AND c.relkind IN ('r', 'v')
1150 AND aclcontains(c.relacl,
1151 makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, false))
1152 AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
1153 OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));
1155 GRANT SELECT ON role_column_grants TO PUBLIC;
1160 * ROLE_ROUTINE_GRANTS view
1163 CREATE VIEW role_routine_grants AS
1164 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1165 CAST(g_grantee.rolname AS sql_identifier) AS grantee,
1166 CAST(current_database() AS sql_identifier) AS specific_catalog,
1167 CAST(n.nspname AS sql_identifier) AS specific_schema,
1168 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1169 CAST(current_database() AS sql_identifier) AS routine_catalog,
1170 CAST(n.nspname AS sql_identifier) AS routine_schema,
1171 CAST(p.proname AS sql_identifier) AS routine_name,
1172 CAST('EXECUTE' AS character_data) AS privilege_type,
1174 CASE WHEN aclcontains(p.proacl,
1175 makeaclitem(g_grantee.oid, u_grantor.oid, 'EXECUTE', true))
1176 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
1180 pg_authid u_grantor,
1183 WHERE p.pronamespace = n.oid
1184 AND aclcontains(p.proacl,
1185 makeaclitem(g_grantee.oid, u_grantor.oid, 'EXECUTE', false))
1186 AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
1187 OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));
1189 GRANT SELECT ON role_routine_grants TO PUBLIC;
1194 * ROLE_TABLE_GRANTS view
1197 CREATE VIEW role_table_grants AS
1198 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1199 CAST(g_grantee.rolname AS sql_identifier) AS grantee,
1200 CAST(current_database() AS sql_identifier) AS table_catalog,
1201 CAST(nc.nspname AS sql_identifier) AS table_schema,
1202 CAST(c.relname AS sql_identifier) AS table_name,
1203 CAST(pr.type AS character_data) AS privilege_type,
1205 CASE WHEN aclcontains(c.relacl,
1206 makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, true))
1207 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable,
1208 CAST('NO' AS character_data) AS with_hierarchy
1212 pg_authid u_grantor,
1213 pg_authid g_grantee,
1214 (SELECT 'SELECT' UNION ALL
1215 SELECT 'DELETE' UNION ALL
1216 SELECT 'INSERT' UNION ALL
1217 SELECT 'UPDATE' UNION ALL
1218 SELECT 'REFERENCES' UNION ALL
1219 SELECT 'TRIGGER') AS pr (type)
1221 WHERE c.relnamespace = nc.oid
1222 AND c.relkind IN ('r', 'v')
1223 AND aclcontains(c.relacl,
1224 makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, false))
1225 AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
1226 OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));
1228 GRANT SELECT ON role_table_grants TO PUBLIC;
1233 * ROLE_TABLE_METHOD_GRANTS view
1236 -- feature not supported
1241 * ROLE_USAGE_GRANTS view
1244 -- See USAGE_PRIVILEGES.
1246 CREATE VIEW role_usage_grants AS
1247 SELECT CAST(null AS sql_identifier) AS grantor,
1248 CAST(null AS sql_identifier) AS grantee,
1249 CAST(current_database() AS sql_identifier) AS object_catalog,
1250 CAST(null AS sql_identifier) AS object_schema,
1251 CAST(null AS sql_identifier) AS object_name,
1252 CAST(null AS character_data) AS object_type,
1253 CAST('USAGE' AS character_data) AS privilege_type,
1254 CAST(null AS character_data) AS is_grantable
1258 GRANT SELECT ON role_usage_grants TO PUBLIC;
1263 * ROLE_UDT_GRANTS view
1266 -- feature not supported
1271 * ROUTINE_COLUMN_USAGE view
1274 -- not tracked by PostgreSQL
1279 * ROUTINE_PRIVILEGES view
1282 CREATE VIEW routine_privileges AS
1283 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1284 CAST(grantee.rolname AS sql_identifier) AS grantee,
1285 CAST(current_database() AS sql_identifier) AS specific_catalog,
1286 CAST(n.nspname AS sql_identifier) AS specific_schema,
1287 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1288 CAST(current_database() AS sql_identifier) AS routine_catalog,
1289 CAST(n.nspname AS sql_identifier) AS routine_schema,
1290 CAST(p.proname AS sql_identifier) AS routine_name,
1291 CAST('EXECUTE' AS character_data) AS privilege_type,
1293 CASE WHEN aclcontains(p.proacl,
1294 makeaclitem(grantee.oid, u_grantor.oid, 'EXECUTE', true))
1295 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
1299 pg_authid u_grantor,
1301 SELECT oid, rolname FROM pg_authid
1303 SELECT 0::oid, 'PUBLIC'
1304 ) AS grantee (oid, rolname)
1306 WHERE p.pronamespace = n.oid
1307 AND aclcontains(p.proacl,
1308 makeaclitem(grantee.oid, u_grantor.oid, 'EXECUTE', false))
1309 AND (pg_has_role(u_grantor.oid, 'USAGE')
1310 OR pg_has_role(grantee.oid, 'USAGE')
1311 OR grantee.rolname = 'PUBLIC');
1313 GRANT SELECT ON routine_privileges TO PUBLIC;
1318 * ROUTINE_ROUTINE_USAGE view
1321 -- not tracked by PostgreSQL
1326 * ROUTINE_SEQUENCE_USAGE view
1329 -- not tracked by PostgreSQL
1334 * ROUTINE_TABLE_USAGE view
1337 -- not tracked by PostgreSQL
1345 CREATE VIEW routines AS
1346 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
1347 CAST(n.nspname AS sql_identifier) AS specific_schema,
1348 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1349 CAST(current_database() AS sql_identifier) AS routine_catalog,
1350 CAST(n.nspname AS sql_identifier) AS routine_schema,
1351 CAST(p.proname AS sql_identifier) AS routine_name,
1352 CAST('FUNCTION' AS character_data) AS routine_type,
1353 CAST(null AS sql_identifier) AS module_catalog,
1354 CAST(null AS sql_identifier) AS module_schema,
1355 CAST(null AS sql_identifier) AS module_name,
1356 CAST(null AS sql_identifier) AS udt_catalog,
1357 CAST(null AS sql_identifier) AS udt_schema,
1358 CAST(null AS sql_identifier) AS udt_name,
1361 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
1362 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
1363 ELSE 'USER-DEFINED' END AS character_data)
1365 CAST(null AS cardinal_number) AS character_maximum_length,
1366 CAST(null AS cardinal_number) AS character_octet_length,
1367 CAST(null AS sql_identifier) AS character_set_catalog,
1368 CAST(null AS sql_identifier) AS character_set_schema,
1369 CAST(null AS sql_identifier) AS character_set_name,
1370 CAST(null AS sql_identifier) AS collation_catalog,
1371 CAST(null AS sql_identifier) AS collation_schema,
1372 CAST(null AS sql_identifier) AS collation_name,
1373 CAST(null AS cardinal_number) AS numeric_precision,
1374 CAST(null AS cardinal_number) AS numeric_precision_radix,
1375 CAST(null AS cardinal_number) AS numeric_scale,
1376 CAST(null AS cardinal_number) AS datetime_precision,
1377 CAST(null AS character_data) AS interval_type,
1378 CAST(null AS character_data) AS interval_precision,
1379 CAST(current_database() AS sql_identifier) AS type_udt_catalog,
1380 CAST(nt.nspname AS sql_identifier) AS type_udt_schema,
1381 CAST(t.typname AS sql_identifier) AS type_udt_name,
1382 CAST(null AS sql_identifier) AS scope_catalog,
1383 CAST(null AS sql_identifier) AS scope_schema,
1384 CAST(null AS sql_identifier) AS scope_name,
1385 CAST(null AS cardinal_number) AS maximum_cardinality,
1386 CAST(0 AS sql_identifier) AS dtd_identifier,
1388 CAST(CASE WHEN l.lanname = 'sql' THEN 'SQL' ELSE 'EXTERNAL' END AS character_data)
1391 CASE WHEN pg_has_role(p.proowner, 'USAGE') THEN p.prosrc ELSE null END
1392 AS character_data) AS routine_definition,
1394 CASE WHEN l.lanname = 'c' THEN p.prosrc ELSE null END
1395 AS character_data) AS external_name,
1396 CAST(upper(l.lanname) AS character_data) AS external_language,
1398 CAST('GENERAL' AS character_data) AS parameter_style,
1399 CAST(CASE WHEN p.provolatile = 'i' THEN 'YES' ELSE 'NO' END AS character_data) AS is_deterministic,
1400 CAST('MODIFIES' AS character_data) AS sql_data_access,
1401 CAST(CASE WHEN p.proisstrict THEN 'YES' ELSE 'NO' END AS character_data) AS is_null_call,
1402 CAST(null AS character_data) AS sql_path,
1403 CAST('YES' AS character_data) AS schema_level_routine,
1404 CAST(0 AS cardinal_number) AS max_dynamic_result_sets,
1405 CAST(null AS character_data) AS is_user_defined_cast,
1406 CAST(null AS character_data) AS is_implicitly_invocable,
1407 CAST(CASE WHEN p.prosecdef THEN 'DEFINER' ELSE 'INVOKER' END AS character_data) AS security_type,
1408 CAST(null AS sql_identifier) AS to_sql_specific_catalog,
1409 CAST(null AS sql_identifier) AS to_sql_specific_schema,
1410 CAST(null AS sql_identifier) AS to_sql_specific_name,
1411 CAST('NO' AS character_data) AS as_locator,
1412 CAST(null AS time_stamp) AS created,
1413 CAST(null AS time_stamp) AS last_altered,
1414 CAST(null AS character_data) AS new_savepoint_level,
1415 CAST('YES' AS character_data) AS is_udt_dependent, -- FIXME?
1417 CAST(null AS character_data) AS result_cast_from_data_type,
1418 CAST(null AS character_data) AS result_cast_as_locator,
1419 CAST(null AS cardinal_number) AS result_cast_char_max_length,
1420 CAST(null AS cardinal_number) AS result_cast_char_octet_length,
1421 CAST(null AS sql_identifier) AS result_cast_char_set_catalog,
1422 CAST(null AS sql_identifier) AS result_cast_char_set_schema,
1423 CAST(null AS sql_identifier) AS result_cast_character_set_name,
1424 CAST(null AS sql_identifier) AS result_cast_collation_catalog,
1425 CAST(null AS sql_identifier) AS result_cast_collation_schema,
1426 CAST(null AS sql_identifier) AS result_cast_collation_name,
1427 CAST(null AS cardinal_number) AS result_cast_numeric_precision,
1428 CAST(null AS cardinal_number) AS result_cast_numeric_precision_radix,
1429 CAST(null AS cardinal_number) AS result_cast_numeric_scale,
1430 CAST(null AS cardinal_number) AS result_cast_datetime_precision,
1431 CAST(null AS character_data) AS result_cast_interval_type,
1432 CAST(null AS character_data) AS result_cast_interval_precision,
1433 CAST(null AS sql_identifier) AS result_cast_type_udt_catalog,
1434 CAST(null AS sql_identifier) AS result_cast_type_udt_schema,
1435 CAST(null AS sql_identifier) AS result_cast_type_udt_name,
1436 CAST(null AS sql_identifier) AS result_cast_scope_catalog,
1437 CAST(null AS sql_identifier) AS result_cast_scope_schema,
1438 CAST(null AS sql_identifier) AS result_cast_scope_name,
1439 CAST(null AS cardinal_number) AS result_cast_maximum_cardinality,
1440 CAST(null AS sql_identifier) AS result_cast_dtd_identifier
1442 FROM pg_namespace n, pg_proc p, pg_language l,
1443 pg_type t, pg_namespace nt
1445 WHERE n.oid = p.pronamespace AND p.prolang = l.oid
1446 AND p.prorettype = t.oid AND t.typnamespace = nt.oid
1447 AND (pg_has_role(p.proowner, 'USAGE')
1448 OR has_function_privilege(p.oid, 'EXECUTE'));
1450 GRANT SELECT ON routines TO PUBLIC;
1458 CREATE VIEW schemata AS
1459 SELECT CAST(current_database() AS sql_identifier) AS catalog_name,
1460 CAST(n.nspname AS sql_identifier) AS schema_name,
1461 CAST(u.rolname AS sql_identifier) AS schema_owner,
1462 CAST(null AS sql_identifier) AS default_character_set_catalog,
1463 CAST(null AS sql_identifier) AS default_character_set_schema,
1464 CAST(null AS sql_identifier) AS default_character_set_name,
1465 CAST(null AS character_data) AS sql_path
1466 FROM pg_namespace n, pg_authid u
1467 WHERE n.nspowner = u.oid AND pg_has_role(n.nspowner, 'USAGE');
1469 GRANT SELECT ON schemata TO PUBLIC;
1477 CREATE VIEW sequences AS
1478 SELECT CAST(current_database() AS sql_identifier) AS sequence_catalog,
1479 CAST(nc.nspname AS sql_identifier) AS sequence_schema,
1480 CAST(c.relname AS sql_identifier) AS sequence_name,
1481 CAST('bigint' AS character_data) AS data_type,
1482 CAST(64 AS cardinal_number) AS numeric_precision,
1483 CAST(2 AS cardinal_number) AS numeric_precision_radix,
1484 CAST(0 AS cardinal_number) AS numeric_scale,
1485 CAST(null AS cardinal_number) AS maximum_value, -- FIXME
1486 CAST(null AS cardinal_number) AS minimum_value, -- FIXME
1487 CAST(null AS cardinal_number) AS increment, -- FIXME
1488 CAST(null AS character_data) AS cycle_option -- FIXME
1489 FROM pg_namespace nc, pg_class c
1490 WHERE c.relnamespace = nc.oid
1492 AND (NOT pg_is_other_temp_schema(nc.oid))
1493 AND (pg_has_role(c.relowner, 'USAGE')
1494 OR has_table_privilege(c.oid, 'SELECT')
1495 OR has_table_privilege(c.oid, 'UPDATE') );
1497 GRANT SELECT ON sequences TO PUBLIC;
1502 * SQL_FEATURES table
1505 CREATE TABLE sql_features (
1506 feature_id character_data,
1507 feature_name character_data,
1508 sub_feature_id character_data,
1509 sub_feature_name character_data,
1510 is_supported character_data,
1511 is_verified_by character_data,
1512 comments character_data
1515 -- Will be filled with external data by initdb.
1517 GRANT SELECT ON sql_features TO PUBLIC;
1522 * SQL_IMPLEMENTATION_INFO table
1525 -- Note: Implementation information items are defined in ISO/IEC 9075-3:2003,
1528 CREATE TABLE sql_implementation_info (
1529 implementation_info_id character_data,
1530 implementation_info_name character_data,
1531 integer_value cardinal_number,
1532 character_value character_data,
1533 comments character_data
1536 INSERT INTO sql_implementation_info VALUES ('10003', 'CATALOG NAME', NULL, 'Y', NULL);
1537 INSERT INTO sql_implementation_info VALUES ('10004', 'COLLATING SEQUENCE', NULL, '', 'not supported');
1538 INSERT INTO sql_implementation_info VALUES ('23', 'CURSOR COMMIT BEHAVIOR', 1, NULL, 'close cursors and retain prepared statements');
1539 INSERT INTO sql_implementation_info VALUES ('2', 'DATA SOURCE NAME', NULL, '', NULL);
1540 INSERT INTO sql_implementation_info VALUES ('17', 'DBMS NAME', NULL, (select trim(trailing ' ' from substring(version() from '^[^0-9]*'))), NULL);
1541 INSERT INTO sql_implementation_info VALUES ('18', 'DBMS VERSION', NULL, '???', NULL); -- filled by initdb
1542 INSERT INTO sql_implementation_info VALUES ('26', 'DEFAULT TRANSACTION ISOLATION', 2, NULL, 'READ COMMITTED; user-settable');
1543 INSERT INTO sql_implementation_info VALUES ('28', 'IDENTIFIER CASE', 3, NULL, 'stored in mixed case - case sensitive');
1544 INSERT INTO sql_implementation_info VALUES ('85', 'NULL COLLATION', 0, NULL, 'nulls higher than non-nulls');
1545 INSERT INTO sql_implementation_info VALUES ('13', 'SERVER NAME', NULL, '', NULL);
1546 INSERT INTO sql_implementation_info VALUES ('94', 'SPECIAL CHARACTERS', NULL, '', 'all non-ASCII characters allowed');
1547 INSERT INTO sql_implementation_info VALUES ('46', 'TRANSACTION CAPABLE', 2, NULL, 'both DML and DDL');
1549 GRANT SELECT ON sql_implementation_info TO PUBLIC;
1554 * SQL_LANGUAGES table
1557 CREATE TABLE sql_languages (
1558 sql_language_source character_data,
1559 sql_language_year character_data,
1560 sql_language_conformance character_data,
1561 sql_language_integrity character_data,
1562 sql_language_implementation character_data,
1563 sql_language_binding_style character_data,
1564 sql_language_programming_language character_data
1567 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'DIRECT', NULL);
1568 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
1569 INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'DIRECT', NULL);
1570 INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
1572 GRANT SELECT ON sql_languages TO PUBLIC;
1577 * SQL_PACKAGES table
1580 CREATE TABLE sql_packages (
1581 feature_id character_data,
1582 feature_name character_data,
1583 is_supported character_data,
1584 is_verified_by character_data,
1585 comments character_data
1588 INSERT INTO sql_packages VALUES ('PKG000', 'Core', 'NO', NULL, '');
1589 INSERT INTO sql_packages VALUES ('PKG001', 'Enhanced datetime facilities', 'YES', NULL, '');
1590 INSERT INTO sql_packages VALUES ('PKG002', 'Enhanced integrity management', 'NO', NULL, '');
1591 INSERT INTO sql_packages VALUES ('PKG003', 'OLAP facilities', 'NO', NULL, '');
1592 INSERT INTO sql_packages VALUES ('PKG004', 'PSM', 'NO', NULL, 'PL/pgSQL is similar.');
1593 INSERT INTO sql_packages VALUES ('PKG005', 'CLI', 'NO', NULL, 'ODBC is similar.');
1594 INSERT INTO sql_packages VALUES ('PKG006', 'Basic object support', 'NO', NULL, '');
1595 INSERT INTO sql_packages VALUES ('PKG007', 'Enhanced object support', 'NO', NULL, '');
1596 INSERT INTO sql_packages VALUES ('PKG008', 'Active database', 'NO', NULL, '');
1597 INSERT INTO sql_packages VALUES ('PKG010', 'OLAP', 'NO', NULL, 'NO');
1599 GRANT SELECT ON sql_packages TO PUBLIC;
1607 CREATE TABLE sql_parts (
1608 feature_id character_data,
1609 feature_name character_data,
1610 is_supported character_data,
1611 is_verified_by character_data,
1612 comments character_data
1615 INSERT INTO sql_parts VALUES ('1', 'Framework (SQL/Framework)', 'NO', NULL, '');
1616 INSERT INTO sql_parts VALUES ('2', 'Foundation (SQL/Foundation)', 'NO', NULL, '');
1617 INSERT INTO sql_parts VALUES ('3', 'Call-Level Interface (SQL/CLI)', 'NO', NULL, '');
1618 INSERT INTO sql_parts VALUES ('4', 'Persistent Stored Modules (SQL/PSM)', 'NO', NULL, '');
1619 INSERT INTO sql_parts VALUES ('9', 'Management of External Data (SQL/MED)', 'NO', NULL, '');
1620 INSERT INTO sql_parts VALUES ('10', 'Object Language Bindings (SQL/OLB)', 'NO', NULL, '');
1621 INSERT INTO sql_parts VALUES ('11', 'Information and Definition Schema (SQL/Schemata)', 'NO', NULL, '');
1622 INSERT INTO sql_parts VALUES ('13', 'Routines and Types Using the Java Programming Language (SQL/JRT)', 'NO', NULL, '');
1623 INSERT INTO sql_parts VALUES ('14', 'XML-Related Specifications (SQL/XML)', 'YES', NULL, '');
1631 -- Note: Sizing items are defined in ISO/IEC 9075-3:2003, clause 7.2.
1633 CREATE TABLE sql_sizing (
1634 sizing_id cardinal_number,
1635 sizing_name character_data,
1636 supported_value cardinal_number,
1637 comments character_data
1640 INSERT INTO sql_sizing VALUES (34, 'MAXIMUM CATALOG NAME LENGTH', 63, NULL);
1641 INSERT INTO sql_sizing VALUES (30, 'MAXIMUM COLUMN NAME LENGTH', 63, NULL);
1642 INSERT INTO sql_sizing VALUES (97, 'MAXIMUM COLUMNS IN GROUP BY', 0, NULL);
1643 INSERT INTO sql_sizing VALUES (99, 'MAXIMUM COLUMNS IN ORDER BY', 0, NULL);
1644 INSERT INTO sql_sizing VALUES (100, 'MAXIMUM COLUMNS IN SELECT', 1664, NULL); -- match MaxTupleAttributeNumber
1645 INSERT INTO sql_sizing VALUES (101, 'MAXIMUM COLUMNS IN TABLE', 1600, NULL); -- match MaxHeapAttributeNumber
1646 INSERT INTO sql_sizing VALUES (1, 'MAXIMUM CONCURRENT ACTIVITIES', 0, NULL);
1647 INSERT INTO sql_sizing VALUES (31, 'MAXIMUM CURSOR NAME LENGTH', 63, NULL);
1648 INSERT INTO sql_sizing VALUES (0, 'MAXIMUM DRIVER CONNECTIONS', NULL, NULL);
1649 INSERT INTO sql_sizing VALUES (10005, 'MAXIMUM IDENTIFIER LENGTH', 63, NULL);
1650 INSERT INTO sql_sizing VALUES (32, 'MAXIMUM SCHEMA NAME LENGTH', 63, NULL);
1651 INSERT INTO sql_sizing VALUES (20000, 'MAXIMUM STATEMENT OCTETS', 0, NULL);
1652 INSERT INTO sql_sizing VALUES (20001, 'MAXIMUM STATEMENT OCTETS DATA', 0, NULL);
1653 INSERT INTO sql_sizing VALUES (20002, 'MAXIMUM STATEMENT OCTETS SCHEMA', 0, NULL);
1654 INSERT INTO sql_sizing VALUES (35, 'MAXIMUM TABLE NAME LENGTH', 63, NULL);
1655 INSERT INTO sql_sizing VALUES (106, 'MAXIMUM TABLES IN SELECT', 0, NULL);
1656 INSERT INTO sql_sizing VALUES (107, 'MAXIMUM USER NAME LENGTH', 63, NULL);
1657 INSERT INTO sql_sizing VALUES (25000, 'MAXIMUM CURRENT DEFAULT TRANSFORM GROUP LENGTH', NULL, NULL);
1658 INSERT INTO sql_sizing VALUES (25001, 'MAXIMUM CURRENT TRANSFORM GROUP LENGTH', NULL, NULL);
1659 INSERT INTO sql_sizing VALUES (25002, 'MAXIMUM CURRENT PATH LENGTH', 0, NULL);
1660 INSERT INTO sql_sizing VALUES (25003, 'MAXIMUM CURRENT ROLE LENGTH', NULL, NULL);
1661 INSERT INTO sql_sizing VALUES (25004, 'MAXIMUM SESSION USER LENGTH', 63, NULL);
1662 INSERT INTO sql_sizing VALUES (25005, 'MAXIMUM SYSTEM USER LENGTH', 63, NULL);
1665 SET supported_value = (SELECT typlen-1 FROM pg_catalog.pg_type WHERE typname = 'name'),
1666 comments = 'Might be less, depending on character set.'
1667 WHERE supported_value = 63;
1669 GRANT SELECT ON sql_sizing TO PUBLIC;
1674 * SQL_SIZING_PROFILES table
1677 -- The data in this table are defined by various profiles of SQL.
1678 -- Since we don't have any information about such profiles, we provide
1681 CREATE TABLE sql_sizing_profiles (
1682 sizing_id cardinal_number,
1683 sizing_name character_data,
1684 profile_id character_data,
1685 required_value cardinal_number,
1686 comments character_data
1689 GRANT SELECT ON sql_sizing_profiles TO PUBLIC;
1694 * TABLE_CONSTRAINTS view
1697 CREATE VIEW table_constraints AS
1698 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1699 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
1700 CAST(c.conname AS sql_identifier) AS constraint_name,
1701 CAST(current_database() AS sql_identifier) AS table_catalog,
1702 CAST(nr.nspname AS sql_identifier) AS table_schema,
1703 CAST(r.relname AS sql_identifier) AS table_name,
1705 CASE c.contype WHEN 'c' THEN 'CHECK'
1706 WHEN 'f' THEN 'FOREIGN KEY'
1707 WHEN 'p' THEN 'PRIMARY KEY'
1708 WHEN 'u' THEN 'UNIQUE' END
1709 AS character_data) AS constraint_type,
1710 CAST(CASE WHEN c.condeferrable THEN 'YES' ELSE 'NO' END AS character_data)
1712 CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS character_data)
1713 AS initially_deferred
1715 FROM pg_namespace nc,
1720 WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace
1721 AND c.conrelid = r.oid
1723 AND (NOT pg_is_other_temp_schema(nr.oid))
1724 AND (pg_has_role(r.relowner, 'USAGE')
1725 -- SELECT privilege omitted, per SQL standard
1726 OR has_table_privilege(r.oid, 'INSERT')
1727 OR has_table_privilege(r.oid, 'UPDATE')
1728 OR has_table_privilege(r.oid, 'DELETE')
1729 OR has_table_privilege(r.oid, 'REFERENCES')
1730 OR has_table_privilege(r.oid, 'TRIGGER') )
1734 -- not-null constraints
1736 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1737 CAST(nr.nspname AS sql_identifier) AS constraint_schema,
1738 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
1739 CAST(current_database() AS sql_identifier) AS table_catalog,
1740 CAST(nr.nspname AS sql_identifier) AS table_schema,
1741 CAST(r.relname AS sql_identifier) AS table_name,
1742 CAST('CHECK' AS character_data) AS constraint_type,
1743 CAST('NO' AS character_data) AS is_deferrable,
1744 CAST('NO' AS character_data) AS initially_deferred
1746 FROM pg_namespace nr,
1750 WHERE nr.oid = r.relnamespace
1751 AND r.oid = a.attrelid
1754 AND NOT a.attisdropped
1756 AND (NOT pg_is_other_temp_schema(nr.oid))
1757 AND (pg_has_role(r.relowner, 'USAGE')
1758 OR has_table_privilege(r.oid, 'SELECT')
1759 OR has_table_privilege(r.oid, 'INSERT')
1760 OR has_table_privilege(r.oid, 'UPDATE')
1761 OR has_table_privilege(r.oid, 'DELETE')
1762 OR has_table_privilege(r.oid, 'REFERENCES')
1763 OR has_table_privilege(r.oid, 'TRIGGER') );
1765 GRANT SELECT ON table_constraints TO PUBLIC;
1770 * TABLE_METHOD_PRIVILEGES view
1773 -- feature not supported
1778 * TABLE_PRIVILEGES view
1781 CREATE VIEW table_privileges AS
1782 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1783 CAST(grantee.rolname AS sql_identifier) AS grantee,
1784 CAST(current_database() AS sql_identifier) AS table_catalog,
1785 CAST(nc.nspname AS sql_identifier) AS table_schema,
1786 CAST(c.relname AS sql_identifier) AS table_name,
1787 CAST(pr.type AS character_data) AS privilege_type,
1789 CASE WHEN aclcontains(c.relacl,
1790 makeaclitem(grantee.oid, u_grantor.oid, pr.type, true))
1791 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable,
1792 CAST('NO' AS character_data) AS with_hierarchy
1796 pg_authid u_grantor,
1798 SELECT oid, rolname FROM pg_authid
1800 SELECT 0::oid, 'PUBLIC'
1801 ) AS grantee (oid, rolname),
1802 (SELECT 'SELECT' UNION ALL
1803 SELECT 'DELETE' UNION ALL
1804 SELECT 'INSERT' UNION ALL
1805 SELECT 'UPDATE' UNION ALL
1806 SELECT 'REFERENCES' UNION ALL
1807 SELECT 'TRIGGER') AS pr (type)
1809 WHERE c.relnamespace = nc.oid
1810 AND c.relkind IN ('r', 'v')
1811 AND aclcontains(c.relacl,
1812 makeaclitem(grantee.oid, u_grantor.oid, pr.type, false))
1813 AND (pg_has_role(u_grantor.oid, 'USAGE')
1814 OR pg_has_role(grantee.oid, 'USAGE')
1815 OR grantee.rolname = 'PUBLIC');
1817 GRANT SELECT ON table_privileges TO PUBLIC;
1825 CREATE VIEW tables AS
1826 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
1827 CAST(nc.nspname AS sql_identifier) AS table_schema,
1828 CAST(c.relname AS sql_identifier) AS table_name,
1831 CASE WHEN nc.oid = pg_my_temp_schema() THEN 'LOCAL TEMPORARY'
1832 WHEN c.relkind = 'r' THEN 'BASE TABLE'
1833 WHEN c.relkind = 'v' THEN 'VIEW'
1835 AS character_data) AS table_type,
1837 CAST(null AS sql_identifier) AS self_referencing_column_name,
1838 CAST(null AS character_data) AS reference_generation,
1840 CAST(null AS sql_identifier) AS user_defined_type_catalog,
1841 CAST(null AS sql_identifier) AS user_defined_type_schema,
1842 CAST(null AS sql_identifier) AS user_defined_type_name,
1844 CAST(CASE WHEN c.relkind = 'r'
1845 THEN 'YES' ELSE 'NO' END AS character_data) AS is_insertable_into,
1846 CAST('NO' AS character_data) AS is_typed,
1848 CASE WHEN nc.oid = pg_my_temp_schema() THEN 'PRESERVE' -- FIXME
1850 AS character_data) AS commit_action
1852 FROM pg_namespace nc, pg_class c
1854 WHERE c.relnamespace = nc.oid
1855 AND c.relkind IN ('r', 'v')
1856 AND (NOT pg_is_other_temp_schema(nc.oid))
1857 AND (pg_has_role(c.relowner, 'USAGE')
1858 OR has_table_privilege(c.oid, 'SELECT')
1859 OR has_table_privilege(c.oid, 'INSERT')
1860 OR has_table_privilege(c.oid, 'UPDATE')
1861 OR has_table_privilege(c.oid, 'DELETE')
1862 OR has_table_privilege(c.oid, 'REFERENCES')
1863 OR has_table_privilege(c.oid, 'TRIGGER') );
1865 GRANT SELECT ON tables TO PUBLIC;
1873 -- feature not supported
1881 -- feature not supported
1886 * TRIGGERED_UPDATE_COLUMNS view
1889 -- PostgreSQL doesn't allow the specification of individual triggered
1890 -- update columns, so this view is empty.
1892 CREATE VIEW triggered_update_columns AS
1893 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1894 CAST(null AS sql_identifier) AS trigger_schema,
1895 CAST(null AS sql_identifier) AS trigger_name,
1896 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1897 CAST(null AS sql_identifier) AS event_object_schema,
1898 CAST(null AS sql_identifier) AS event_object_table,
1899 CAST(null AS sql_identifier) AS event_object_column
1902 GRANT SELECT ON triggered_update_columns TO PUBLIC;
1907 * TRIGGER_COLUMN_USAGE view
1910 -- not tracked by PostgreSQL
1915 * TRIGGER_ROUTINE_USAGE view
1918 -- not tracked by PostgreSQL
1923 * TRIGGER_SEQUENCE_USAGE view
1926 -- not tracked by PostgreSQL
1931 * TRIGGER_TABLE_USAGE view
1934 -- not tracked by PostgreSQL
1942 CREATE VIEW triggers AS
1943 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1944 CAST(n.nspname AS sql_identifier) AS trigger_schema,
1945 CAST(t.tgname AS sql_identifier) AS trigger_name,
1946 CAST(em.text AS character_data) AS event_manipulation,
1947 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1948 CAST(n.nspname AS sql_identifier) AS event_object_schema,
1949 CAST(c.relname AS sql_identifier) AS event_object_table,
1950 CAST(null AS cardinal_number) AS action_order,
1951 CAST(null AS character_data) AS action_condition,
1953 substring(pg_get_triggerdef(t.oid) from
1954 position('EXECUTE PROCEDURE' in substring(pg_get_triggerdef(t.oid) from 48)) + 47)
1955 AS character_data) AS action_statement,
1957 CASE WHEN t.tgtype & 1 = 1 THEN 'ROW' ELSE 'STATEMENT' END
1958 AS character_data) AS action_orientation,
1960 CASE WHEN t.tgtype & 2 = 2 THEN 'BEFORE' ELSE 'AFTER' END
1961 AS character_data) AS condition_timing,
1962 CAST(null AS sql_identifier) AS condition_reference_old_table,
1963 CAST(null AS sql_identifier) AS condition_reference_new_table,
1964 CAST(null AS sql_identifier) AS condition_reference_old_row,
1965 CAST(null AS sql_identifier) AS condition_reference_new_row,
1966 CAST(null AS time_stamp) AS created
1968 FROM pg_namespace n, pg_class c, pg_trigger t,
1969 (SELECT 4, 'INSERT' UNION ALL
1970 SELECT 8, 'DELETE' UNION ALL
1971 SELECT 16, 'UPDATE') AS em (num, text)
1973 WHERE n.oid = c.relnamespace
1974 AND c.oid = t.tgrelid
1975 AND t.tgtype & em.num <> 0
1976 AND NOT t.tgisconstraint
1977 AND (NOT pg_is_other_temp_schema(n.oid))
1978 AND (pg_has_role(c.relowner, 'USAGE')
1979 -- SELECT privilege omitted, per SQL standard
1980 OR has_table_privilege(c.oid, 'INSERT')
1981 OR has_table_privilege(c.oid, 'UPDATE')
1982 OR has_table_privilege(c.oid, 'DELETE')
1983 OR has_table_privilege(c.oid, 'REFERENCES')
1984 OR has_table_privilege(c.oid, 'TRIGGER') );
1986 GRANT SELECT ON triggers TO PUBLIC;
1991 * UDT_PRIVILEGES view
1994 -- feature not supported
1999 * USAGE_PRIVILEGES view
2002 -- Of the things currently implemented in PostgreSQL, usage privileges
2003 -- apply only to domains. Since domains have no real privileges, we
2004 -- represent all domains with implicit usage privilege here.
2006 CREATE VIEW usage_privileges AS
2007 SELECT CAST(u.rolname AS sql_identifier) AS grantor,
2008 CAST('PUBLIC' AS sql_identifier) AS grantee,
2009 CAST(current_database() AS sql_identifier) AS object_catalog,
2010 CAST(n.nspname AS sql_identifier) AS object_schema,
2011 CAST(t.typname AS sql_identifier) AS object_name,
2012 CAST('DOMAIN' AS character_data) AS object_type,
2013 CAST('USAGE' AS character_data) AS privilege_type,
2014 CAST('NO' AS character_data) AS is_grantable
2020 WHERE u.oid = t.typowner
2021 AND t.typnamespace = n.oid
2022 AND t.typtype = 'd';
2024 GRANT SELECT ON usage_privileges TO PUBLIC;
2029 * USER_DEFINED_TYPES view
2032 -- feature not supported
2040 CREATE VIEW view_column_usage AS
2042 CAST(current_database() AS sql_identifier) AS view_catalog,
2043 CAST(nv.nspname AS sql_identifier) AS view_schema,
2044 CAST(v.relname AS sql_identifier) AS view_name,
2045 CAST(current_database() AS sql_identifier) AS table_catalog,
2046 CAST(nt.nspname AS sql_identifier) AS table_schema,
2047 CAST(t.relname AS sql_identifier) AS table_name,
2048 CAST(a.attname AS sql_identifier) AS column_name
2050 FROM pg_namespace nv, pg_class v, pg_depend dv,
2051 pg_depend dt, pg_class t, pg_namespace nt,
2054 WHERE nv.oid = v.relnamespace
2056 AND v.oid = dv.refobjid
2057 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2058 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2059 AND dv.deptype = 'i'
2060 AND dv.objid = dt.objid
2061 AND dv.refobjid <> dt.refobjid
2062 AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2063 AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2064 AND dt.refobjid = t.oid
2065 AND t.relnamespace = nt.oid
2066 AND t.relkind IN ('r', 'v')
2067 AND t.oid = a.attrelid
2068 AND dt.refobjsubid = a.attnum
2069 AND pg_has_role(t.relowner, 'USAGE');
2071 GRANT SELECT ON view_column_usage TO PUBLIC;
2076 * VIEW_ROUTINE_USAGE
2079 CREATE VIEW view_routine_usage AS
2081 CAST(current_database() AS sql_identifier) AS table_catalog,
2082 CAST(nv.nspname AS sql_identifier) AS table_schema,
2083 CAST(v.relname AS sql_identifier) AS table_name,
2084 CAST(current_database() AS sql_identifier) AS specific_catalog,
2085 CAST(np.nspname AS sql_identifier) AS specific_schema,
2086 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name
2088 FROM pg_namespace nv, pg_class v, pg_depend dv,
2089 pg_depend dp, pg_proc p, pg_namespace np
2091 WHERE nv.oid = v.relnamespace
2093 AND v.oid = dv.refobjid
2094 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2095 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2096 AND dv.deptype = 'i'
2097 AND dv.objid = dp.objid
2098 AND dp.classid = 'pg_catalog.pg_rewrite'::regclass
2099 AND dp.refclassid = 'pg_catalog.pg_proc'::regclass
2100 AND dp.refobjid = p.oid
2101 AND p.pronamespace = np.oid
2102 AND pg_has_role(p.proowner, 'USAGE');
2104 GRANT SELECT ON view_routine_usage TO PUBLIC;
2112 CREATE VIEW view_table_usage AS
2114 CAST(current_database() AS sql_identifier) AS view_catalog,
2115 CAST(nv.nspname AS sql_identifier) AS view_schema,
2116 CAST(v.relname AS sql_identifier) AS view_name,
2117 CAST(current_database() AS sql_identifier) AS table_catalog,
2118 CAST(nt.nspname AS sql_identifier) AS table_schema,
2119 CAST(t.relname AS sql_identifier) AS table_name
2121 FROM pg_namespace nv, pg_class v, pg_depend dv,
2122 pg_depend dt, pg_class t, pg_namespace nt
2124 WHERE nv.oid = v.relnamespace
2126 AND v.oid = dv.refobjid
2127 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2128 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2129 AND dv.deptype = 'i'
2130 AND dv.objid = dt.objid
2131 AND dv.refobjid <> dt.refobjid
2132 AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2133 AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2134 AND dt.refobjid = t.oid
2135 AND t.relnamespace = nt.oid
2136 AND t.relkind IN ('r', 'v')
2137 AND pg_has_role(t.relowner, 'USAGE');
2139 GRANT SELECT ON view_table_usage TO PUBLIC;
2147 CREATE VIEW views AS
2148 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
2149 CAST(nc.nspname AS sql_identifier) AS table_schema,
2150 CAST(c.relname AS sql_identifier) AS table_name,
2153 CASE WHEN pg_has_role(c.relowner, 'USAGE')
2154 THEN pg_get_viewdef(c.oid)
2156 AS character_data) AS view_definition,
2158 CAST('NONE' AS character_data) AS check_option,
2161 CASE WHEN EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '2' AND is_instead)
2162 AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '4' AND is_instead)
2163 THEN 'YES' ELSE 'NO' END
2164 AS character_data) AS is_updatable,
2167 CASE WHEN EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '3' AND is_instead)
2168 THEN 'YES' ELSE 'NO' END
2169 AS character_data) AS is_insertable_into
2171 FROM pg_namespace nc, pg_class c
2173 WHERE c.relnamespace = nc.oid
2175 AND (NOT pg_is_other_temp_schema(nc.oid))
2176 AND (pg_has_role(c.relowner, 'USAGE')
2177 OR has_table_privilege(c.oid, 'SELECT')
2178 OR has_table_privilege(c.oid, 'INSERT')
2179 OR has_table_privilege(c.oid, 'UPDATE')
2180 OR has_table_privilege(c.oid, 'DELETE')
2181 OR has_table_privilege(c.oid, 'REFERENCES')
2182 OR has_table_privilege(c.oid, 'TRIGGER') );
2184 GRANT SELECT ON views TO PUBLIC;
2187 -- The following views have dependencies that force them to appear out of order.
2191 * DATA_TYPE_PRIVILEGES view
2194 CREATE VIEW data_type_privileges AS
2195 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2196 CAST(x.objschema AS sql_identifier) AS object_schema,
2197 CAST(x.objname AS sql_identifier) AS object_name,
2198 CAST(x.objtype AS character_data) AS object_type,
2199 CAST(x.objdtdid AS sql_identifier) AS dtd_identifier
2203 SELECT udt_schema, udt_name, 'USER-DEFINED TYPE'::text, dtd_identifier FROM attributes
2205 SELECT table_schema, table_name, 'TABLE'::text, dtd_identifier FROM columns
2207 SELECT domain_schema, domain_name, 'DOMAIN'::text, dtd_identifier FROM domains
2209 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM parameters
2211 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM routines
2212 ) AS x (objschema, objname, objtype, objdtdid);
2214 GRANT SELECT ON data_type_privileges TO PUBLIC;
2219 * ELEMENT_TYPES view
2222 CREATE VIEW element_types AS
2223 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2224 CAST(n.nspname AS sql_identifier) AS object_schema,
2225 CAST(x.objname AS sql_identifier) AS object_name,
2226 CAST(x.objtype AS character_data) AS object_type,
2227 CAST(x.objdtdid AS sql_identifier) AS collection_type_identifier,
2229 CASE WHEN nbt.nspname = 'pg_catalog' THEN format_type(bt.oid, null)
2230 ELSE 'USER-DEFINED' END AS character_data) AS data_type,
2232 CAST(null AS cardinal_number) AS character_maximum_length,
2233 CAST(null AS cardinal_number) AS character_octet_length,
2234 CAST(null AS sql_identifier) AS character_set_catalog,
2235 CAST(null AS sql_identifier) AS character_set_schema,
2236 CAST(null AS sql_identifier) AS character_set_name,
2237 CAST(null AS sql_identifier) AS collation_catalog,
2238 CAST(null AS sql_identifier) AS collation_schema,
2239 CAST(null AS sql_identifier) AS collation_name,
2240 CAST(null AS cardinal_number) AS numeric_precision,
2241 CAST(null AS cardinal_number) AS numeric_precision_radix,
2242 CAST(null AS cardinal_number) AS numeric_scale,
2243 CAST(null AS cardinal_number) AS datetime_precision,
2244 CAST(null AS character_data) AS interval_type,
2245 CAST(null AS character_data) AS interval_precision,
2247 CAST(null AS character_data) AS domain_default, -- XXX maybe a bug in the standard
2249 CAST(current_database() AS sql_identifier) AS udt_catalog,
2250 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
2251 CAST(bt.typname AS sql_identifier) AS udt_name,
2253 CAST(null AS sql_identifier) AS scope_catalog,
2254 CAST(null AS sql_identifier) AS scope_schema,
2255 CAST(null AS sql_identifier) AS scope_name,
2257 CAST(null AS cardinal_number) AS maximum_cardinality,
2258 CAST('a' || CAST(x.objdtdid AS text) AS sql_identifier) AS dtd_identifier
2260 FROM pg_namespace n, pg_type at, pg_namespace nbt, pg_type bt,
2263 SELECT c.relnamespace, CAST(c.relname AS sql_identifier),
2264 'TABLE'::text, a.attnum, a.atttypid
2265 FROM pg_class c, pg_attribute a
2266 WHERE c.oid = a.attrelid
2267 AND c.relkind IN ('r', 'v')
2268 AND attnum > 0 AND NOT attisdropped
2273 SELECT t.typnamespace, CAST(t.typname AS sql_identifier),
2274 'DOMAIN'::text, 1, t.typbasetype
2276 WHERE t.typtype = 'd'
2281 SELECT pronamespace, CAST(proname || '_' || CAST(oid AS text) AS sql_identifier),
2282 'ROUTINE'::text, (ss.x).n, (ss.x).x
2283 FROM (SELECT p.pronamespace, p.proname, p.oid,
2284 _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
2285 FROM pg_proc p) AS ss
2290 SELECT p.pronamespace, CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier),
2291 'ROUTINE'::text, 0, p.prorettype
2294 ) AS x (objschema, objname, objtype, objdtdid, objtypeid)
2296 WHERE n.oid = x.objschema
2297 AND at.oid = x.objtypeid
2298 AND (at.typelem <> 0 AND at.typlen = -1)
2299 AND at.typelem = bt.oid
2300 AND nbt.oid = bt.typnamespace
2302 AND (n.nspname, x.objname, x.objtype, CAST(x.objdtdid AS sql_identifier)) IN
2303 ( SELECT object_schema, object_name, object_type, dtd_identifier
2304 FROM data_type_privileges );
2306 GRANT SELECT ON element_types TO PUBLIC;