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.34 2006/09/04 21:03:18 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 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,
924 CAST(null AS cardinal_number) AS position_in_unique_constraint -- FIXME
926 (SELECT r.oid, nc.nspname AS nc_nspname, c.conname,
927 nr.nspname AS nr_nspname, r.relname,
928 _pg_expandarray(c.conkey) AS x
929 FROM pg_namespace nr, pg_class r, pg_namespace nc,
931 WHERE nr.oid = r.relnamespace
932 AND r.oid = c.conrelid
933 AND nc.oid = c.connamespace
934 AND c.contype IN ('p', 'u', 'f')
936 AND (nr.nspname NOT LIKE 'pg!_temp!_%' ESCAPE '!' OR pg_catalog.pg_table_is_visible(r.oid))
937 AND (pg_has_role(r.relowner, 'USAGE')
938 OR has_table_privilege(c.oid, 'SELECT')
939 OR has_table_privilege(c.oid, 'INSERT')
940 OR has_table_privilege(c.oid, 'UPDATE')
941 OR has_table_privilege(c.oid, 'REFERENCES')) ) AS ss
942 WHERE ss.oid = a.attrelid
943 AND a.attnum = (ss.x).x
944 AND NOT a.attisdropped;
946 GRANT SELECT ON key_column_usage TO PUBLIC;
951 * METHOD_SPECIFICATION_PARAMETERS view
954 -- feature not supported
959 * METHOD_SPECIFICATIONS view
962 -- feature not supported
970 CREATE VIEW parameters AS
971 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
972 CAST(n_nspname AS sql_identifier) AS specific_schema,
973 CAST(proname || '_' || CAST(p_oid AS text) AS sql_identifier) AS specific_name,
974 CAST((ss.x).n AS cardinal_number) AS ordinal_position,
976 CASE WHEN proargmodes IS NULL THEN 'IN'
977 WHEN proargmodes[(ss.x).n] = 'i' THEN 'IN'
978 WHEN proargmodes[(ss.x).n] = 'o' THEN 'OUT'
979 WHEN proargmodes[(ss.x).n] = 'b' THEN 'INOUT'
980 END AS character_data) AS parameter_mode,
981 CAST('NO' AS character_data) AS is_result,
982 CAST('NO' AS character_data) AS as_locator,
983 CAST(NULLIF(proargnames[(ss.x).n], '') AS sql_identifier) AS parameter_name,
985 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
986 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
987 ELSE 'USER-DEFINED' END AS character_data)
989 CAST(null AS cardinal_number) AS character_maximum_length,
990 CAST(null AS cardinal_number) AS character_octet_length,
991 CAST(null AS sql_identifier) AS character_set_catalog,
992 CAST(null AS sql_identifier) AS character_set_schema,
993 CAST(null AS sql_identifier) AS character_set_name,
994 CAST(null AS sql_identifier) AS collation_catalog,
995 CAST(null AS sql_identifier) AS collation_schema,
996 CAST(null AS sql_identifier) AS collation_name,
997 CAST(null AS cardinal_number) AS numeric_precision,
998 CAST(null AS cardinal_number) AS numeric_precision_radix,
999 CAST(null AS cardinal_number) AS numeric_scale,
1000 CAST(null AS cardinal_number) AS datetime_precision,
1001 CAST(null AS character_data) AS interval_type,
1002 CAST(null AS character_data) AS interval_precision,
1003 CAST(current_database() AS sql_identifier) AS udt_catalog,
1004 CAST(nt.nspname AS sql_identifier) AS udt_schema,
1005 CAST(t.typname AS sql_identifier) AS udt_name,
1006 CAST(null AS sql_identifier) AS scope_catalog,
1007 CAST(null AS sql_identifier) AS scope_schema,
1008 CAST(null AS sql_identifier) AS scope_name,
1009 CAST(null AS cardinal_number) AS maximum_cardinality,
1010 CAST((ss.x).n AS sql_identifier) AS dtd_identifier
1012 FROM pg_type t, pg_namespace nt,
1013 (SELECT n.nspname AS n_nspname, p.proname, p.oid AS p_oid,
1014 p.proargnames, p.proargmodes,
1015 _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
1016 FROM pg_namespace n, pg_proc p
1017 WHERE n.oid = p.pronamespace
1018 AND (pg_has_role(p.proowner, 'USAGE') OR
1019 has_function_privilege(p.oid, 'EXECUTE'))) AS ss
1020 WHERE t.oid = (ss.x).x AND t.typnamespace = nt.oid;
1022 GRANT SELECT ON parameters TO PUBLIC;
1027 * REFERENCED_TYPES view
1030 -- feature not supported
1035 * REFERENTIAL_CONSTRAINTS view
1038 CREATE VIEW referential_constraints AS
1039 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1040 CAST(ncon.nspname AS sql_identifier) AS constraint_schema,
1041 CAST(con.conname AS sql_identifier) AS constraint_name,
1043 CASE WHEN npkc.nspname IS NULL THEN NULL
1044 ELSE current_database() END
1045 AS sql_identifier) AS unique_constraint_catalog,
1046 CAST(npkc.nspname AS sql_identifier) AS unique_constraint_schema,
1047 CAST(pkc.conname AS sql_identifier) AS unique_constraint_name,
1050 CASE con.confmatchtype WHEN 'f' THEN 'FULL'
1051 WHEN 'p' THEN 'PARTIAL'
1052 WHEN 'u' THEN 'NONE' END
1053 AS character_data) AS match_option,
1056 CASE con.confupdtype WHEN 'c' THEN 'CASCADE'
1057 WHEN 'n' THEN 'SET NULL'
1058 WHEN 'd' THEN 'SET DEFAULT'
1059 WHEN 'r' THEN 'RESTRICT'
1060 WHEN 'a' THEN 'NO ACTION' END
1061 AS character_data) AS update_rule,
1064 CASE con.confdeltype WHEN 'c' THEN 'CASCADE'
1065 WHEN 'n' THEN 'SET NULL'
1066 WHEN 'd' THEN 'SET DEFAULT'
1067 WHEN 'r' THEN 'RESTRICT'
1068 WHEN 'a' THEN 'NO ACTION' END
1069 AS character_data) AS delete_rule
1071 FROM (pg_namespace ncon
1072 INNER JOIN pg_constraint con ON ncon.oid = con.connamespace
1073 INNER JOIN pg_class c ON con.conrelid = c.oid)
1076 INNER JOIN pg_namespace npkc ON pkc.connamespace = npkc.oid)
1077 ON con.confrelid = pkc.conrelid
1078 AND _pg_keysequal(con.confkey, pkc.conkey)
1080 WHERE c.relkind = 'r'
1081 AND con.contype = 'f'
1082 AND (pkc.contype IN ('p', 'u') OR pkc.contype IS NULL)
1083 AND pg_has_role(c.relowner, 'USAGE');
1085 GRANT SELECT ON referential_constraints TO PUBLIC;
1090 * ROLE_COLUMN_GRANTS view
1093 CREATE VIEW role_column_grants AS
1094 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1095 CAST(g_grantee.rolname AS sql_identifier) AS grantee,
1096 CAST(current_database() AS sql_identifier) AS table_catalog,
1097 CAST(nc.nspname AS sql_identifier) AS table_schema,
1098 CAST(c.relname AS sql_identifier) AS table_name,
1099 CAST(a.attname AS sql_identifier) AS column_name,
1100 CAST(pr.type AS character_data) AS privilege_type,
1102 CASE WHEN aclcontains(c.relacl,
1103 makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, true))
1104 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
1106 FROM pg_attribute a,
1109 pg_authid u_grantor,
1110 pg_authid g_grantee,
1111 (SELECT 'SELECT' UNION ALL
1112 SELECT 'INSERT' UNION ALL
1113 SELECT 'UPDATE' UNION ALL
1114 SELECT 'REFERENCES') AS pr (type)
1116 WHERE a.attrelid = c.oid
1117 AND c.relnamespace = nc.oid
1119 AND NOT a.attisdropped
1120 AND c.relkind IN ('r', 'v')
1121 AND aclcontains(c.relacl,
1122 makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, false))
1123 AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
1124 OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));
1126 GRANT SELECT ON role_column_grants TO PUBLIC;
1131 * ROLE_ROUTINE_GRANTS view
1134 CREATE VIEW role_routine_grants AS
1135 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1136 CAST(g_grantee.rolname AS sql_identifier) AS grantee,
1137 CAST(current_database() AS sql_identifier) AS specific_catalog,
1138 CAST(n.nspname AS sql_identifier) AS specific_schema,
1139 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1140 CAST(current_database() AS sql_identifier) AS routine_catalog,
1141 CAST(n.nspname AS sql_identifier) AS routine_schema,
1142 CAST(p.proname AS sql_identifier) AS routine_name,
1143 CAST('EXECUTE' AS character_data) AS privilege_type,
1145 CASE WHEN aclcontains(p.proacl,
1146 makeaclitem(g_grantee.oid, u_grantor.oid, 'EXECUTE', true))
1147 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
1151 pg_authid u_grantor,
1154 WHERE p.pronamespace = n.oid
1155 AND aclcontains(p.proacl,
1156 makeaclitem(g_grantee.oid, u_grantor.oid, 'EXECUTE', false))
1157 AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
1158 OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));
1160 GRANT SELECT ON role_routine_grants TO PUBLIC;
1165 * ROLE_TABLE_GRANTS view
1168 CREATE VIEW role_table_grants AS
1169 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1170 CAST(g_grantee.rolname AS sql_identifier) AS grantee,
1171 CAST(current_database() AS sql_identifier) AS table_catalog,
1172 CAST(nc.nspname AS sql_identifier) AS table_schema,
1173 CAST(c.relname AS sql_identifier) AS table_name,
1174 CAST(pr.type AS character_data) AS privilege_type,
1176 CASE WHEN aclcontains(c.relacl,
1177 makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, true))
1178 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable,
1179 CAST('NO' AS character_data) AS with_hierarchy
1183 pg_authid u_grantor,
1184 pg_authid g_grantee,
1185 (SELECT 'SELECT' UNION ALL
1186 SELECT 'DELETE' UNION ALL
1187 SELECT 'INSERT' UNION ALL
1188 SELECT 'UPDATE' UNION ALL
1189 SELECT 'REFERENCES' UNION ALL
1190 SELECT 'RULE' UNION ALL
1191 SELECT 'TRIGGER') AS pr (type)
1193 WHERE c.relnamespace = nc.oid
1194 AND c.relkind IN ('r', 'v')
1195 AND aclcontains(c.relacl,
1196 makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, false))
1197 AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
1198 OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));
1200 GRANT SELECT ON role_table_grants TO PUBLIC;
1205 * ROLE_TABLE_METHOD_GRANTS view
1208 -- feature not supported
1213 * ROLE_USAGE_GRANTS view
1216 -- See USAGE_PRIVILEGES.
1218 CREATE VIEW role_usage_grants AS
1219 SELECT CAST(null AS sql_identifier) AS grantor,
1220 CAST(null AS sql_identifier) AS grantee,
1221 CAST(current_database() AS sql_identifier) AS object_catalog,
1222 CAST(null AS sql_identifier) AS object_schema,
1223 CAST(null AS sql_identifier) AS object_name,
1224 CAST(null AS character_data) AS object_type,
1225 CAST('USAGE' AS character_data) AS privilege_type,
1226 CAST(null AS character_data) AS is_grantable
1230 GRANT SELECT ON role_usage_grants TO PUBLIC;
1235 * ROLE_UDT_GRANTS view
1238 -- feature not supported
1243 * ROUTINE_COLUMN_USAGE view
1246 -- not tracked by PostgreSQL
1251 * ROUTINE_PRIVILEGES view
1254 CREATE VIEW routine_privileges AS
1255 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1256 CAST(grantee.rolname AS sql_identifier) AS grantee,
1257 CAST(current_database() AS sql_identifier) AS specific_catalog,
1258 CAST(n.nspname AS sql_identifier) AS specific_schema,
1259 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1260 CAST(current_database() AS sql_identifier) AS routine_catalog,
1261 CAST(n.nspname AS sql_identifier) AS routine_schema,
1262 CAST(p.proname AS sql_identifier) AS routine_name,
1263 CAST('EXECUTE' AS character_data) AS privilege_type,
1265 CASE WHEN aclcontains(p.proacl,
1266 makeaclitem(grantee.oid, u_grantor.oid, 'EXECUTE', true))
1267 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
1271 pg_authid u_grantor,
1273 SELECT oid, rolname FROM pg_authid
1275 SELECT 0::oid, 'PUBLIC'
1276 ) AS grantee (oid, rolname)
1278 WHERE p.pronamespace = n.oid
1279 AND aclcontains(p.proacl,
1280 makeaclitem(grantee.oid, u_grantor.oid, 'EXECUTE', false))
1281 AND (pg_has_role(u_grantor.oid, 'USAGE')
1282 OR pg_has_role(grantee.oid, 'USAGE')
1283 OR grantee.rolname = 'PUBLIC');
1285 GRANT SELECT ON routine_privileges TO PUBLIC;
1290 * ROUTINE_ROUTINE_USAGE view
1293 -- not tracked by PostgreSQL
1298 * ROUTINE_SEQUENCE_USAGE view
1301 -- not tracked by PostgreSQL
1306 * ROUTINE_TABLE_USAGE view
1309 -- not tracked by PostgreSQL
1317 CREATE VIEW routines AS
1318 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
1319 CAST(n.nspname AS sql_identifier) AS specific_schema,
1320 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1321 CAST(current_database() AS sql_identifier) AS routine_catalog,
1322 CAST(n.nspname AS sql_identifier) AS routine_schema,
1323 CAST(p.proname AS sql_identifier) AS routine_name,
1324 CAST('FUNCTION' AS character_data) AS routine_type,
1325 CAST(null AS sql_identifier) AS module_catalog,
1326 CAST(null AS sql_identifier) AS module_schema,
1327 CAST(null AS sql_identifier) AS module_name,
1328 CAST(null AS sql_identifier) AS udt_catalog,
1329 CAST(null AS sql_identifier) AS udt_schema,
1330 CAST(null AS sql_identifier) AS udt_name,
1333 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
1334 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
1335 ELSE 'USER-DEFINED' END AS character_data)
1337 CAST(null AS cardinal_number) AS character_maximum_length,
1338 CAST(null AS cardinal_number) AS character_octet_length,
1339 CAST(null AS sql_identifier) AS character_set_catalog,
1340 CAST(null AS sql_identifier) AS character_set_schema,
1341 CAST(null AS sql_identifier) AS character_set_name,
1342 CAST(null AS sql_identifier) AS collation_catalog,
1343 CAST(null AS sql_identifier) AS collation_schema,
1344 CAST(null AS sql_identifier) AS collation_name,
1345 CAST(null AS cardinal_number) AS numeric_precision,
1346 CAST(null AS cardinal_number) AS numeric_precision_radix,
1347 CAST(null AS cardinal_number) AS numeric_scale,
1348 CAST(null AS cardinal_number) AS datetime_precision,
1349 CAST(null AS character_data) AS interval_type,
1350 CAST(null AS character_data) AS interval_precision,
1351 CAST(current_database() AS sql_identifier) AS type_udt_catalog,
1352 CAST(nt.nspname AS sql_identifier) AS type_udt_schema,
1353 CAST(t.typname AS sql_identifier) AS type_udt_name,
1354 CAST(null AS sql_identifier) AS scope_catalog,
1355 CAST(null AS sql_identifier) AS scope_schema,
1356 CAST(null AS sql_identifier) AS scope_name,
1357 CAST(null AS cardinal_number) AS maximum_cardinality,
1358 CAST(0 AS sql_identifier) AS dtd_identifier,
1360 CAST(CASE WHEN l.lanname = 'sql' THEN 'SQL' ELSE 'EXTERNAL' END AS character_data)
1363 CASE WHEN pg_has_role(p.proowner, 'USAGE') THEN p.prosrc ELSE null END
1364 AS character_data) AS routine_definition,
1366 CASE WHEN l.lanname = 'c' THEN p.prosrc ELSE null END
1367 AS character_data) AS external_name,
1368 CAST(upper(l.lanname) AS character_data) AS external_language,
1370 CAST('GENERAL' AS character_data) AS parameter_style,
1371 CAST(CASE WHEN p.provolatile = 'i' THEN 'YES' ELSE 'NO' END AS character_data) AS is_deterministic,
1372 CAST('MODIFIES' AS character_data) AS sql_data_access,
1373 CAST(CASE WHEN p.proisstrict THEN 'YES' ELSE 'NO' END AS character_data) AS is_null_call,
1374 CAST(null AS character_data) AS sql_path,
1375 CAST('YES' AS character_data) AS schema_level_routine,
1376 CAST(0 AS cardinal_number) AS max_dynamic_result_sets,
1377 CAST(null AS character_data) AS is_user_defined_cast,
1378 CAST(null AS character_data) AS is_implicitly_invocable,
1379 CAST(CASE WHEN p.prosecdef THEN 'DEFINER' ELSE 'INVOKER' END AS character_data) AS security_type,
1380 CAST(null AS sql_identifier) AS to_sql_specific_catalog,
1381 CAST(null AS sql_identifier) AS to_sql_specific_schema,
1382 CAST(null AS sql_identifier) AS to_sql_specific_name,
1383 CAST('NO' AS character_data) AS as_locator,
1384 CAST(null AS time_stamp) AS created,
1385 CAST(null AS time_stamp) AS last_altered,
1386 CAST(null AS character_data) AS new_savepoint_level,
1387 CAST('YES' AS character_data) AS is_udt_dependent, -- FIXME?
1389 CAST(null AS character_data) AS result_cast_from_data_type,
1390 CAST(null AS character_data) AS result_cast_as_locator,
1391 CAST(null AS cardinal_number) AS result_cast_char_max_length,
1392 CAST(null AS cardinal_number) AS result_cast_char_octet_length,
1393 CAST(null AS sql_identifier) AS result_cast_char_set_catalog,
1394 CAST(null AS sql_identifier) AS result_cast_char_set_schema,
1395 CAST(null AS sql_identifier) AS result_cast_character_set_name,
1396 CAST(null AS sql_identifier) AS result_cast_collation_catalog,
1397 CAST(null AS sql_identifier) AS result_cast_collation_schema,
1398 CAST(null AS sql_identifier) AS result_cast_collation_name,
1399 CAST(null AS cardinal_number) AS result_cast_numeric_precision,
1400 CAST(null AS cardinal_number) AS result_cast_numeric_precision_radix,
1401 CAST(null AS cardinal_number) AS result_cast_numeric_scale,
1402 CAST(null AS cardinal_number) AS result_cast_datetime_precision,
1403 CAST(null AS character_data) AS result_cast_interval_type,
1404 CAST(null AS character_data) AS result_cast_interval_precision,
1405 CAST(null AS sql_identifier) AS result_cast_type_udt_catalog,
1406 CAST(null AS sql_identifier) AS result_cast_type_udt_schema,
1407 CAST(null AS sql_identifier) AS result_cast_type_udt_name,
1408 CAST(null AS sql_identifier) AS result_cast_scope_catalog,
1409 CAST(null AS sql_identifier) AS result_cast_scope_schema,
1410 CAST(null AS sql_identifier) AS result_cast_scope_name,
1411 CAST(null AS cardinal_number) AS result_cast_maximum_cardinality,
1412 CAST(null AS sql_identifier) AS result_cast_dtd_identifier
1414 FROM pg_namespace n, pg_proc p, pg_language l,
1415 pg_type t, pg_namespace nt
1417 WHERE n.oid = p.pronamespace AND p.prolang = l.oid
1418 AND p.prorettype = t.oid AND t.typnamespace = nt.oid
1419 AND (pg_has_role(p.proowner, 'USAGE')
1420 OR has_function_privilege(p.oid, 'EXECUTE'));
1422 GRANT SELECT ON routines TO PUBLIC;
1430 CREATE VIEW schemata AS
1431 SELECT CAST(current_database() AS sql_identifier) AS catalog_name,
1432 CAST(n.nspname AS sql_identifier) AS schema_name,
1433 CAST(u.rolname AS sql_identifier) AS schema_owner,
1434 CAST(null AS sql_identifier) AS default_character_set_catalog,
1435 CAST(null AS sql_identifier) AS default_character_set_schema,
1436 CAST(null AS sql_identifier) AS default_character_set_name,
1437 CAST(null AS character_data) AS sql_path
1438 FROM pg_namespace n, pg_authid u
1439 WHERE n.nspowner = u.oid AND pg_has_role(n.nspowner, 'USAGE');
1441 GRANT SELECT ON schemata TO PUBLIC;
1449 CREATE VIEW sequences AS
1450 SELECT CAST(current_database() AS sql_identifier) AS sequence_catalog,
1451 CAST(nc.nspname AS sql_identifier) AS sequence_schema,
1452 CAST(c.relname AS sql_identifier) AS sequence_name,
1453 CAST('bigint' AS character_data) AS data_type,
1454 CAST(64 AS cardinal_number) AS numeric_precision,
1455 CAST(2 AS cardinal_number) AS numeric_precision_radix,
1456 CAST(0 AS cardinal_number) AS numeric_scale,
1457 CAST(null AS cardinal_number) AS maximum_value, -- FIXME
1458 CAST(null AS cardinal_number) AS minimum_value, -- FIXME
1459 CAST(null AS cardinal_number) AS increment, -- FIXME
1460 CAST(null AS character_data) AS cycle_option -- FIXME
1461 FROM pg_namespace nc, pg_class c
1462 WHERE c.relnamespace = nc.oid
1464 AND (nc.nspname NOT LIKE 'pg!_temp!_%' ESCAPE '!' OR pg_catalog.pg_table_is_visible(c.oid))
1465 AND (pg_has_role(c.relowner, 'USAGE')
1466 OR has_table_privilege(c.oid, 'SELECT')
1467 OR has_table_privilege(c.oid, 'UPDATE') );
1469 GRANT SELECT ON sequences TO PUBLIC;
1474 * SQL_FEATURES table
1477 CREATE TABLE sql_features (
1478 feature_id character_data,
1479 feature_name character_data,
1480 sub_feature_id character_data,
1481 sub_feature_name character_data,
1482 is_supported character_data,
1483 is_verified_by character_data,
1484 comments character_data
1487 -- Will be filled with external data by initdb.
1489 GRANT SELECT ON sql_features TO PUBLIC;
1494 * SQL_IMPLEMENTATION_INFO table
1497 -- Note: Implementation information items are defined in ISO/IEC 9075-3:2003,
1500 CREATE TABLE sql_implementation_info (
1501 implementation_info_id character_data,
1502 implementation_info_name character_data,
1503 integer_value cardinal_number,
1504 character_value character_data,
1505 comments character_data
1508 INSERT INTO sql_implementation_info VALUES ('10003', 'CATALOG NAME', NULL, 'Y', NULL);
1509 INSERT INTO sql_implementation_info VALUES ('10004', 'COLLATING SEQUENCE', NULL, '', 'not supported');
1510 INSERT INTO sql_implementation_info VALUES ('23', 'CURSOR COMMIT BEHAVIOR', 1, NULL, 'close cursors and retain prepared statements');
1511 INSERT INTO sql_implementation_info VALUES ('2', 'DATA SOURCE NAME', NULL, '', NULL);
1512 INSERT INTO sql_implementation_info VALUES ('17', 'DBMS NAME', NULL, (select trim(trailing ' ' from substring(version() from '^[^0-9]*'))), NULL);
1513 INSERT INTO sql_implementation_info VALUES ('18', 'DBMS VERSION', NULL, '???', NULL); -- filled by initdb
1514 INSERT INTO sql_implementation_info VALUES ('26', 'DEFAULT TRANSACTION ISOLATION', 2, NULL, 'READ COMMITTED; user-settable');
1515 INSERT INTO sql_implementation_info VALUES ('28', 'IDENTIFIER CASE', 3, NULL, 'stored in mixed case - case sensitive');
1516 INSERT INTO sql_implementation_info VALUES ('85', 'NULL COLLATION', 0, NULL, 'nulls higher than non-nulls');
1517 INSERT INTO sql_implementation_info VALUES ('13', 'SERVER NAME', NULL, '', NULL);
1518 INSERT INTO sql_implementation_info VALUES ('94', 'SPECIAL CHARACTERS', NULL, '', 'all non-ASCII characters allowed');
1519 INSERT INTO sql_implementation_info VALUES ('46', 'TRANSACTION CAPABLE', 2, NULL, 'both DML and DDL');
1521 GRANT SELECT ON sql_implementation_info TO PUBLIC;
1526 * SQL_LANGUAGES table
1529 CREATE TABLE sql_languages (
1530 sql_language_source character_data,
1531 sql_language_year character_data,
1532 sql_language_conformance character_data,
1533 sql_language_integrity character_data,
1534 sql_language_implementation character_data,
1535 sql_language_binding_style character_data,
1536 sql_language_programming_language character_data
1539 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'DIRECT', NULL);
1540 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
1541 INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'DIRECT', NULL);
1542 INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
1544 GRANT SELECT ON sql_languages TO PUBLIC;
1549 * SQL_PACKAGES table
1552 CREATE TABLE sql_packages (
1553 feature_id character_data,
1554 feature_name character_data,
1555 is_supported character_data,
1556 is_verified_by character_data,
1557 comments character_data
1560 INSERT INTO sql_packages VALUES ('PKG000', 'Core', 'NO', NULL, '');
1561 INSERT INTO sql_packages VALUES ('PKG001', 'Enhanced datetime facilities', 'YES', NULL, '');
1562 INSERT INTO sql_packages VALUES ('PKG002', 'Enhanced integrity management', 'NO', NULL, '');
1563 INSERT INTO sql_packages VALUES ('PKG003', 'OLAP facilities', 'NO', NULL, '');
1564 INSERT INTO sql_packages VALUES ('PKG004', 'PSM', 'NO', NULL, 'PL/pgSQL is similar.');
1565 INSERT INTO sql_packages VALUES ('PKG005', 'CLI', 'NO', NULL, 'ODBC is similar.');
1566 INSERT INTO sql_packages VALUES ('PKG006', 'Basic object support', 'NO', NULL, '');
1567 INSERT INTO sql_packages VALUES ('PKG007', 'Enhanced object support', 'NO', NULL, '');
1568 INSERT INTO sql_packages VALUES ('PKG008', 'Active database', 'NO', NULL, '');
1569 INSERT INTO sql_packages VALUES ('PKG010', 'OLAP', 'NO', NULL, 'NO');
1571 GRANT SELECT ON sql_packages TO PUBLIC;
1579 CREATE TABLE sql_parts (
1580 feature_id character_data,
1581 feature_name character_data,
1582 is_supported character_data,
1583 is_verified_by character_data,
1584 comments character_data
1587 INSERT INTO sql_parts VALUES ('1', 'Framework (SQL/Framework)', 'NO', NULL, '');
1588 INSERT INTO sql_parts VALUES ('2', 'Foundation (SQL/Foundation)', 'NO', NULL, '');
1589 INSERT INTO sql_parts VALUES ('3', 'Call-Level Interface (SQL/CLI)', 'NO', NULL, '');
1590 INSERT INTO sql_parts VALUES ('4', 'Persistent Stored Modules (SQL/PSM)', NULL, '');
1591 INSERT INTO sql_parts VALUES ('9', 'Management of External Data (SQL/MED)', NULL, '');
1592 INSERT INTO sql_parts VALUES ('10', 'Object Language Bindings (SQL/OLB)', NULL, '');
1593 INSERT INTO sql_parts VALUES ('11', 'Information and Definition Schema (SQL/Schemata)', NULL, '');
1594 INSERT INTO sql_parts VALUES ('13', 'Routines and Types Using the Java Programming Language (SQL/JRT)', NULL, '');
1595 INSERT INTO sql_parts VALUES ('14', 'XML-Related Specifications (SQL/XML)', NULL, '');
1603 -- Note: Sizing items are defined in ISO/IEC 9075-3:2003, clause 7.2.
1605 CREATE TABLE sql_sizing (
1606 sizing_id cardinal_number,
1607 sizing_name character_data,
1608 supported_value cardinal_number,
1609 comments character_data
1612 INSERT INTO sql_sizing VALUES (34, 'MAXIMUM CATALOG NAME LENGTH', 63, NULL);
1613 INSERT INTO sql_sizing VALUES (30, 'MAXIMUM COLUMN NAME LENGTH', 63, NULL);
1614 INSERT INTO sql_sizing VALUES (97, 'MAXIMUM COLUMNS IN GROUP BY', 0, NULL);
1615 INSERT INTO sql_sizing VALUES (99, 'MAXIMUM COLUMNS IN ORDER BY', 0, NULL);
1616 INSERT INTO sql_sizing VALUES (100, 'MAXIMUM COLUMNS IN SELECT', 1664, NULL); -- match MaxTupleAttributeNumber
1617 INSERT INTO sql_sizing VALUES (101, 'MAXIMUM COLUMNS IN TABLE', 1600, NULL); -- match MaxHeapAttributeNumber
1618 INSERT INTO sql_sizing VALUES (1, 'MAXIMUM CONCURRENT ACTIVITIES', 0, NULL);
1619 INSERT INTO sql_sizing VALUES (31, 'MAXIMUM CURSOR NAME LENGTH', 63, NULL);
1620 INSERT INTO sql_sizing VALUES (0, 'MAXIMUM DRIVER CONNECTIONS', NULL, NULL);
1621 INSERT INTO sql_sizing VALUES (10005, 'MAXIMUM IDENTIFIER LENGTH', 63, NULL);
1622 INSERT INTO sql_sizing VALUES (32, 'MAXIMUM SCHEMA NAME LENGTH', 63, NULL);
1623 INSERT INTO sql_sizing VALUES (20000, 'MAXIMUM STATEMENT OCTETS', 0, NULL);
1624 INSERT INTO sql_sizing VALUES (20001, 'MAXIMUM STATEMENT OCTETS DATA', 0, NULL);
1625 INSERT INTO sql_sizing VALUES (20002, 'MAXIMUM STATEMENT OCTETS SCHEMA', 0, NULL);
1626 INSERT INTO sql_sizing VALUES (35, 'MAXIMUM TABLE NAME LENGTH', 63, NULL);
1627 INSERT INTO sql_sizing VALUES (106, 'MAXIMUM TABLES IN SELECT', 0, NULL);
1628 INSERT INTO sql_sizing VALUES (107, 'MAXIMUM USER NAME LENGTH', 63, NULL);
1629 INSERT INTO sql_sizing VALUES (25000, 'MAXIMUM CURRENT DEFAULT TRANSFORM GROUP LENGTH', NULL, NULL);
1630 INSERT INTO sql_sizing VALUES (25001, 'MAXIMUM CURRENT TRANSFORM GROUP LENGTH', NULL, NULL);
1631 INSERT INTO sql_sizing VALUES (25002, 'MAXIMUM CURRENT PATH LENGTH', 0, NULL);
1632 INSERT INTO sql_sizing VALUES (25003, 'MAXIMUM CURRENT ROLE LENGTH', NULL, NULL);
1633 INSERT INTO sql_sizing VALUES (25004, 'MAXIMUM SESSION USER LENGTH', 63, NULL);
1634 INSERT INTO sql_sizing VALUES (25005, 'MAXIMUM SYSTEM USER LENGTH', 63, NULL);
1637 SET supported_value = (SELECT typlen-1 FROM pg_catalog.pg_type WHERE typname = 'name'),
1638 comments = 'Might be less, depending on character set.'
1639 WHERE supported_value = 63;
1641 GRANT SELECT ON sql_sizing TO PUBLIC;
1646 * SQL_SIZING_PROFILES table
1649 -- The data in this table are defined by various profiles of SQL.
1650 -- Since we don't have any information about such profiles, we provide
1653 CREATE TABLE sql_sizing_profiles (
1654 sizing_id cardinal_number,
1655 sizing_name character_data,
1656 profile_id character_data,
1657 required_value cardinal_number,
1658 comments character_data
1661 GRANT SELECT ON sql_sizing_profiles TO PUBLIC;
1666 * TABLE_CONSTRAINTS view
1669 CREATE VIEW table_constraints AS
1670 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1671 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
1672 CAST(c.conname AS sql_identifier) AS constraint_name,
1673 CAST(current_database() AS sql_identifier) AS table_catalog,
1674 CAST(nr.nspname AS sql_identifier) AS table_schema,
1675 CAST(r.relname AS sql_identifier) AS table_name,
1677 CASE c.contype WHEN 'c' THEN 'CHECK'
1678 WHEN 'f' THEN 'FOREIGN KEY'
1679 WHEN 'p' THEN 'PRIMARY KEY'
1680 WHEN 'u' THEN 'UNIQUE' END
1681 AS character_data) AS constraint_type,
1682 CAST(CASE WHEN c.condeferrable THEN 'YES' ELSE 'NO' END AS character_data)
1684 CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS character_data)
1685 AS initially_deferred
1687 FROM pg_namespace nc,
1692 WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace
1693 AND c.conrelid = r.oid
1695 AND (nr.nspname NOT LIKE 'pg!_temp!_%' ESCAPE '!' OR pg_catalog.pg_table_is_visible(r.oid))
1696 AND (pg_has_role(r.relowner, 'USAGE')
1697 -- SELECT privilege omitted, per SQL standard
1698 OR has_table_privilege(r.oid, 'INSERT')
1699 OR has_table_privilege(r.oid, 'UPDATE')
1700 OR has_table_privilege(r.oid, 'DELETE')
1701 OR has_table_privilege(r.oid, 'RULE')
1702 OR has_table_privilege(r.oid, 'REFERENCES')
1703 OR has_table_privilege(r.oid, 'TRIGGER') )
1707 -- not-null constraints
1709 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1710 CAST(nr.nspname AS sql_identifier) AS constraint_schema,
1711 CAST(nr.oid || '_' || r.oid || '_' || a.attnum || '_not_null' AS sql_identifier) AS constraint_name, -- XXX
1712 CAST(current_database() AS sql_identifier) AS table_catalog,
1713 CAST(nr.nspname AS sql_identifier) AS table_schema,
1714 CAST(r.relname AS sql_identifier) AS table_name,
1715 CAST('CHECK' AS character_data) AS constraint_type,
1716 CAST('NO' AS character_data) AS is_deferrable,
1717 CAST('NO' AS character_data) AS initially_deferred
1719 FROM pg_namespace nr,
1723 WHERE nr.oid = r.relnamespace
1724 AND r.oid = a.attrelid
1727 AND NOT a.attisdropped
1729 AND (nr.nspname NOT LIKE 'pg!_temp!_%' ESCAPE '!' OR pg_catalog.pg_table_is_visible(r.oid))
1730 AND (pg_has_role(r.relowner, 'USAGE')
1731 OR has_table_privilege(r.oid, 'SELECT')
1732 OR has_table_privilege(r.oid, 'INSERT')
1733 OR has_table_privilege(r.oid, 'UPDATE')
1734 OR has_table_privilege(r.oid, 'DELETE')
1735 OR has_table_privilege(r.oid, 'RULE')
1736 OR has_table_privilege(r.oid, 'REFERENCES')
1737 OR has_table_privilege(r.oid, 'TRIGGER') );
1739 GRANT SELECT ON table_constraints TO PUBLIC;
1744 * TABLE_METHOD_PRIVILEGES view
1747 -- feature not supported
1752 * TABLE_PRIVILEGES view
1755 CREATE VIEW table_privileges AS
1756 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1757 CAST(grantee.rolname AS sql_identifier) AS grantee,
1758 CAST(current_database() AS sql_identifier) AS table_catalog,
1759 CAST(nc.nspname AS sql_identifier) AS table_schema,
1760 CAST(c.relname AS sql_identifier) AS table_name,
1761 CAST(pr.type AS character_data) AS privilege_type,
1763 CASE WHEN aclcontains(c.relacl,
1764 makeaclitem(grantee.oid, u_grantor.oid, pr.type, true))
1765 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable,
1766 CAST('NO' AS character_data) AS with_hierarchy
1770 pg_authid u_grantor,
1772 SELECT oid, rolname FROM pg_authid
1774 SELECT 0::oid, 'PUBLIC'
1775 ) AS grantee (oid, rolname),
1776 (SELECT 'SELECT' UNION ALL
1777 SELECT 'DELETE' UNION ALL
1778 SELECT 'INSERT' UNION ALL
1779 SELECT 'UPDATE' UNION ALL
1780 SELECT 'REFERENCES' UNION ALL
1781 SELECT 'RULE' UNION ALL
1782 SELECT 'TRIGGER') AS pr (type)
1784 WHERE c.relnamespace = nc.oid
1785 AND c.relkind IN ('r', 'v')
1786 AND aclcontains(c.relacl,
1787 makeaclitem(grantee.oid, u_grantor.oid, pr.type, false))
1788 AND (pg_has_role(u_grantor.oid, 'USAGE')
1789 OR pg_has_role(grantee.oid, 'USAGE')
1790 OR grantee.rolname = 'PUBLIC');
1792 GRANT SELECT ON table_privileges TO PUBLIC;
1800 CREATE VIEW tables AS
1801 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
1802 CAST(nc.nspname AS sql_identifier) AS table_schema,
1803 CAST(c.relname AS sql_identifier) AS table_name,
1806 CASE WHEN nc.nspname LIKE 'pg!_temp!_%' ESCAPE '!' THEN 'LOCAL TEMPORARY'
1807 WHEN c.relkind = 'r' THEN 'BASE TABLE'
1808 WHEN c.relkind = 'v' THEN 'VIEW'
1810 AS character_data) AS table_type,
1812 CAST(null AS sql_identifier) AS self_referencing_column_name,
1813 CAST(null AS character_data) AS reference_generation,
1815 CAST(null AS sql_identifier) AS user_defined_type_catalog,
1816 CAST(null AS sql_identifier) AS user_defined_type_schema,
1817 CAST(null AS sql_identifier) AS user_defined_type_name,
1819 CAST(CASE WHEN c.relkind = 'r'
1820 THEN 'YES' ELSE 'NO' END AS character_data) AS is_insertable_into,
1821 CAST('NO' AS character_data) AS is_typed,
1823 CASE WHEN nc.nspname LIKE 'pg!_temp!_%' ESCAPE '!' THEN 'PRESERVE'
1825 AS character_data) AS commit_action
1827 FROM pg_namespace nc, pg_class c
1829 WHERE c.relnamespace = nc.oid
1830 AND c.relkind IN ('r', 'v')
1831 AND (nc.nspname NOT LIKE 'pg!_temp!_%' ESCAPE '!' OR pg_catalog.pg_table_is_visible(c.oid))
1832 AND (pg_has_role(c.relowner, 'USAGE')
1833 OR has_table_privilege(c.oid, 'SELECT')
1834 OR has_table_privilege(c.oid, 'INSERT')
1835 OR has_table_privilege(c.oid, 'UPDATE')
1836 OR has_table_privilege(c.oid, 'DELETE')
1837 OR has_table_privilege(c.oid, 'RULE')
1838 OR has_table_privilege(c.oid, 'REFERENCES')
1839 OR has_table_privilege(c.oid, 'TRIGGER') );
1841 GRANT SELECT ON tables TO PUBLIC;
1849 -- feature not supported
1857 -- feature not supported
1862 * TRIGGERED_UPDATE_COLUMNS view
1865 -- PostgreSQL doesn't allow the specification of individual triggered
1866 -- update columns, so this view is empty.
1868 CREATE VIEW triggered_update_columns AS
1869 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1870 CAST(null AS sql_identifier) AS trigger_schema,
1871 CAST(null AS sql_identifier) AS trigger_name,
1872 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1873 CAST(null AS sql_identifier) AS event_object_schema,
1874 CAST(null AS sql_identifier) AS event_object_table,
1875 CAST(null AS sql_identifier) AS event_object_column
1878 GRANT SELECT ON triggered_update_columns TO PUBLIC;
1883 * TRIGGER_COLUMN_USAGE view
1886 -- not tracked by PostgreSQL
1891 * TRIGGER_ROUTINE_USAGE view
1894 -- not tracked by PostgreSQL
1899 * TRIGGER_SEQUENCE_USAGE view
1902 -- not tracked by PostgreSQL
1907 * TRIGGER_TABLE_USAGE view
1910 -- not tracked by PostgreSQL
1918 CREATE VIEW triggers AS
1919 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1920 CAST(n.nspname AS sql_identifier) AS trigger_schema,
1921 CAST(t.tgname AS sql_identifier) AS trigger_name,
1922 CAST(em.text AS character_data) AS event_manipulation,
1923 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1924 CAST(n.nspname AS sql_identifier) AS event_object_schema,
1925 CAST(c.relname AS sql_identifier) AS event_object_table,
1926 CAST(null AS cardinal_number) AS action_order,
1927 CAST(null AS character_data) AS action_condition,
1929 substring(pg_get_triggerdef(t.oid) from
1930 position('EXECUTE PROCEDURE' in substring(pg_get_triggerdef(t.oid) from 48)) + 47)
1931 AS character_data) AS action_statement,
1933 CASE WHEN t.tgtype & 1 = 1 THEN 'ROW' ELSE 'STATEMENT' END
1934 AS character_data) AS action_orientation,
1936 CASE WHEN t.tgtype & 2 = 2 THEN 'BEFORE' ELSE 'AFTER' END
1937 AS character_data) AS condition_timing,
1938 CAST(null AS sql_identifier) AS condition_reference_old_table,
1939 CAST(null AS sql_identifier) AS condition_reference_new_table,
1940 CAST(null AS sql_identifier) AS condition_reference_old_row,
1941 CAST(null AS sql_identifier) AS condition_reference_new_row,
1942 CAST(null AS time_stamp) AS created
1944 FROM pg_namespace n, pg_class c, pg_trigger t,
1945 (SELECT 4, 'INSERT' UNION ALL
1946 SELECT 8, 'DELETE' UNION ALL
1947 SELECT 16, 'UPDATE') AS em (num, text)
1949 WHERE n.oid = c.relnamespace
1950 AND c.oid = t.tgrelid
1951 AND t.tgtype & em.num <> 0
1952 AND NOT t.tgisconstraint
1953 AND (n.nspname NOT LIKE 'pg!_temp!_%' ESCAPE '!' OR pg_catalog.pg_table_is_visible(c.oid))
1954 AND (pg_has_role(c.relowner, 'USAGE')
1955 -- SELECT privilege omitted, per SQL standard
1956 OR has_table_privilege(c.oid, 'INSERT')
1957 OR has_table_privilege(c.oid, 'UPDATE')
1958 OR has_table_privilege(c.oid, 'DELETE')
1959 OR has_table_privilege(c.oid, 'RULE')
1960 OR has_table_privilege(c.oid, 'REFERENCES')
1961 OR has_table_privilege(c.oid, 'TRIGGER') );
1963 GRANT SELECT ON triggers TO PUBLIC;
1968 * UDT_PRIVILEGES view
1971 -- feature not supported
1976 * USAGE_PRIVILEGES view
1979 -- Of the things currently implemented in PostgreSQL, usage privileges
1980 -- apply only to domains. Since domains have no real privileges, we
1981 -- represent all domains with implicit usage privilege here.
1983 CREATE VIEW usage_privileges AS
1984 SELECT CAST(u.rolname AS sql_identifier) AS grantor,
1985 CAST('PUBLIC' AS sql_identifier) AS grantee,
1986 CAST(current_database() AS sql_identifier) AS object_catalog,
1987 CAST(n.nspname AS sql_identifier) AS object_schema,
1988 CAST(t.typname AS sql_identifier) AS object_name,
1989 CAST('DOMAIN' AS character_data) AS object_type,
1990 CAST('USAGE' AS character_data) AS privilege_type,
1991 CAST('NO' AS character_data) AS is_grantable
1997 WHERE u.oid = t.typowner
1998 AND t.typnamespace = n.oid
1999 AND t.typtype = 'd';
2001 GRANT SELECT ON usage_privileges TO PUBLIC;
2006 * USER_DEFINED_TYPES view
2009 -- feature not supported
2017 CREATE VIEW view_column_usage AS
2019 CAST(current_database() AS sql_identifier) AS view_catalog,
2020 CAST(nv.nspname AS sql_identifier) AS view_schema,
2021 CAST(v.relname AS sql_identifier) AS view_name,
2022 CAST(current_database() AS sql_identifier) AS table_catalog,
2023 CAST(nt.nspname AS sql_identifier) AS table_schema,
2024 CAST(t.relname AS sql_identifier) AS table_name,
2025 CAST(a.attname AS sql_identifier) AS column_name
2027 FROM pg_namespace nv, pg_class v, pg_depend dv,
2028 pg_depend dt, pg_class t, pg_namespace nt,
2031 WHERE nv.oid = v.relnamespace
2033 AND v.oid = dv.refobjid
2034 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2035 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2036 AND dv.deptype = 'i'
2037 AND dv.objid = dt.objid
2038 AND dv.refobjid <> dt.refobjid
2039 AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2040 AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2041 AND dt.refobjid = t.oid
2042 AND t.relnamespace = nt.oid
2043 AND t.relkind IN ('r', 'v')
2044 AND t.oid = a.attrelid
2045 AND dt.refobjsubid = a.attnum
2046 AND pg_has_role(t.relowner, 'USAGE');
2048 GRANT SELECT ON view_column_usage TO PUBLIC;
2053 * VIEW_ROUTINE_USAGE
2056 CREATE VIEW view_routine_usage AS
2058 CAST(current_database() AS sql_identifier) AS table_catalog,
2059 CAST(nv.nspname AS sql_identifier) AS table_schema,
2060 CAST(v.relname AS sql_identifier) AS table_name,
2061 CAST(current_database() AS sql_identifier) AS specific_catalog,
2062 CAST(np.nspname AS sql_identifier) AS specific_schema,
2063 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name
2065 FROM pg_namespace nv, pg_class v, pg_depend dv,
2066 pg_depend dp, pg_proc p, pg_namespace np
2068 WHERE nv.oid = v.relnamespace
2070 AND v.oid = dv.refobjid
2071 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2072 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2073 AND dv.deptype = 'i'
2074 AND dv.objid = dp.objid
2075 AND dp.classid = 'pg_catalog.pg_rewrite'::regclass
2076 AND dp.refclassid = 'pg_catalog.pg_proc'::regclass
2077 AND dp.refobjid = p.oid
2078 AND p.pronamespace = np.oid
2079 AND pg_has_role(p.proowner, 'USAGE');
2081 GRANT SELECT ON view_routine_usage TO PUBLIC;
2089 CREATE VIEW view_table_usage AS
2091 CAST(current_database() AS sql_identifier) AS view_catalog,
2092 CAST(nv.nspname AS sql_identifier) AS view_schema,
2093 CAST(v.relname AS sql_identifier) AS view_name,
2094 CAST(current_database() AS sql_identifier) AS table_catalog,
2095 CAST(nt.nspname AS sql_identifier) AS table_schema,
2096 CAST(t.relname AS sql_identifier) AS table_name
2098 FROM pg_namespace nv, pg_class v, pg_depend dv,
2099 pg_depend dt, pg_class t, pg_namespace nt
2101 WHERE nv.oid = v.relnamespace
2103 AND v.oid = dv.refobjid
2104 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2105 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2106 AND dv.deptype = 'i'
2107 AND dv.objid = dt.objid
2108 AND dv.refobjid <> dt.refobjid
2109 AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2110 AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2111 AND dt.refobjid = t.oid
2112 AND t.relnamespace = nt.oid
2113 AND t.relkind IN ('r', 'v')
2114 AND pg_has_role(t.relowner, 'USAGE');
2116 GRANT SELECT ON view_table_usage TO PUBLIC;
2124 CREATE VIEW views AS
2125 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
2126 CAST(nc.nspname AS sql_identifier) AS table_schema,
2127 CAST(c.relname AS sql_identifier) AS table_name,
2130 CASE WHEN pg_has_role(c.relowner, 'USAGE')
2131 THEN pg_get_viewdef(c.oid)
2133 AS character_data) AS view_definition,
2135 CAST('NONE' AS character_data) AS check_option,
2138 CASE WHEN EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = 2 AND is_instead)
2139 AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = 4 AND is_instead)
2140 THEN 'YES' ELSE 'NO' END
2141 AS character_data) AS is_updatable,
2144 CASE WHEN EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = 3 AND is_instead)
2145 THEN 'YES' ELSE 'NO' END
2146 AS character_data) AS is_insertable_into
2148 FROM pg_namespace nc, pg_class c
2150 WHERE c.relnamespace = nc.oid
2152 AND (nc.nspname NOT LIKE 'pg!_temp!_%' ESCAPE '!' OR pg_catalog.pg_table_is_visible(c.oid))
2153 AND (pg_has_role(c.relowner, 'USAGE')
2154 OR has_table_privilege(c.oid, 'SELECT')
2155 OR has_table_privilege(c.oid, 'INSERT')
2156 OR has_table_privilege(c.oid, 'UPDATE')
2157 OR has_table_privilege(c.oid, 'DELETE')
2158 OR has_table_privilege(c.oid, 'RULE')
2159 OR has_table_privilege(c.oid, 'REFERENCES')
2160 OR has_table_privilege(c.oid, 'TRIGGER') );
2162 GRANT SELECT ON views TO PUBLIC;
2165 -- The following views have dependencies that force them to appear out of order.
2169 * DATA_TYPE_PRIVILEGES view
2172 CREATE VIEW data_type_privileges AS
2173 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2174 CAST(x.objschema AS sql_identifier) AS object_schema,
2175 CAST(x.objname AS sql_identifier) AS object_name,
2176 CAST(x.objtype AS character_data) AS object_type,
2177 CAST(x.objdtdid AS sql_identifier) AS dtd_identifier
2181 SELECT udt_schema, udt_name, 'USER-DEFINED TYPE'::text, dtd_identifier FROM attributes
2183 SELECT table_schema, table_name, 'TABLE'::text, dtd_identifier FROM columns
2185 SELECT domain_schema, domain_name, 'DOMAIN'::text, dtd_identifier FROM domains
2187 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM parameters
2189 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM routines
2190 ) AS x (objschema, objname, objtype, objdtdid);
2192 GRANT SELECT ON data_type_privileges TO PUBLIC;
2197 * ELEMENT_TYPES view
2200 CREATE VIEW element_types AS
2201 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2202 CAST(n.nspname AS sql_identifier) AS object_schema,
2203 CAST(x.objname AS sql_identifier) AS object_name,
2204 CAST(x.objtype AS character_data) AS object_type,
2205 CAST(x.objdtdid AS sql_identifier) AS collection_type_identifier,
2207 CASE WHEN nbt.nspname = 'pg_catalog' THEN format_type(bt.oid, null)
2208 ELSE 'USER-DEFINED' END AS character_data) AS data_type,
2210 CAST(null AS cardinal_number) AS character_maximum_length,
2211 CAST(null AS cardinal_number) AS character_octet_length,
2212 CAST(null AS sql_identifier) AS character_set_catalog,
2213 CAST(null AS sql_identifier) AS character_set_schema,
2214 CAST(null AS sql_identifier) AS character_set_name,
2215 CAST(null AS sql_identifier) AS collation_catalog,
2216 CAST(null AS sql_identifier) AS collation_schema,
2217 CAST(null AS sql_identifier) AS collation_name,
2218 CAST(null AS cardinal_number) AS numeric_precision,
2219 CAST(null AS cardinal_number) AS numeric_precision_radix,
2220 CAST(null AS cardinal_number) AS numeric_scale,
2221 CAST(null AS cardinal_number) AS datetime_precision,
2222 CAST(null AS character_data) AS interval_type,
2223 CAST(null AS character_data) AS interval_precision,
2225 CAST(null AS character_data) AS domain_default, -- XXX maybe a bug in the standard
2227 CAST(current_database() AS sql_identifier) AS udt_catalog,
2228 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
2229 CAST(bt.typname AS sql_identifier) AS udt_name,
2231 CAST(null AS sql_identifier) AS scope_catalog,
2232 CAST(null AS sql_identifier) AS scope_schema,
2233 CAST(null AS sql_identifier) AS scope_name,
2235 CAST(null AS cardinal_number) AS maximum_cardinality,
2236 CAST('a' || x.objdtdid AS sql_identifier) AS dtd_identifier
2238 FROM pg_namespace n, pg_type at, pg_namespace nbt, pg_type bt,
2241 SELECT c.relnamespace, CAST(c.relname AS sql_identifier),
2242 'TABLE'::text, a.attnum, a.atttypid
2243 FROM pg_class c, pg_attribute a
2244 WHERE c.oid = a.attrelid
2245 AND c.relkind IN ('r', 'v')
2246 AND attnum > 0 AND NOT attisdropped
2251 SELECT t.typnamespace, CAST(t.typname AS sql_identifier),
2252 'DOMAIN'::text, 1, t.typbasetype
2254 WHERE t.typtype = 'd'
2259 SELECT pronamespace, CAST(proname || '_' || CAST(oid AS text) AS sql_identifier),
2260 'ROUTINE'::text, (ss.x).n, (ss.x).x
2261 FROM (SELECT p.pronamespace, p.proname, p.oid,
2262 _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
2263 FROM pg_proc p) AS ss
2268 SELECT p.pronamespace, CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier),
2269 'ROUTINE'::text, 0, p.prorettype
2272 ) AS x (objschema, objname, objtype, objdtdid, objtypeid)
2274 WHERE n.oid = x.objschema
2275 AND at.oid = x.objtypeid
2276 AND (at.typelem <> 0 AND at.typlen = -1)
2277 AND at.typelem = bt.oid
2278 AND nbt.oid = bt.typnamespace
2280 AND (n.nspname, x.objname, x.objtype, x.objdtdid) IN
2281 ( SELECT object_schema, object_name, object_type, dtd_identifier
2282 FROM data_type_privileges );
2284 GRANT SELECT ON element_types TO PUBLIC;