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.37 2006/09/14 22:05:06 tgl Exp $
11 * Note: Generally, the definitions in this file should be ordered
12 * according to the clause numbers in the SQL standard, which is also the
13 * alphabetical order. In some cases it is convenient or necessary to
14 * define one information schema view by using another one; in that case,
15 * put the referencing view at the very end and leave a note where it
16 * should have been put.
22 * INFORMATION_SCHEMA schema
25 CREATE SCHEMA information_schema;
26 GRANT USAGE ON SCHEMA information_schema TO PUBLIC;
27 SET search_path TO information_schema, public;
31 * A few supporting functions first ...
34 /* Expand any 1-D array into a set with integers 1..N */
35 CREATE FUNCTION _pg_expandarray(IN anyarray, OUT x anyelement, OUT n int)
37 LANGUAGE sql STRICT IMMUTABLE
38 AS 'select $1[s], s - pg_catalog.array_lower($1,1) + 1
39 from pg_catalog.generate_series(pg_catalog.array_lower($1,1),
40 pg_catalog.array_upper($1,1),
43 CREATE FUNCTION _pg_keyissubset(smallint[], smallint[]) RETURNS boolean
46 RETURNS NULL ON NULL INPUT
47 AS 'select $1[1] is null or ($1[1] = any ($2) and coalesce(information_schema._pg_keyissubset($1[2:pg_catalog.array_upper($1,1)], $2), true))';
49 CREATE FUNCTION _pg_keysequal(smallint[], smallint[]) RETURNS boolean
52 RETURNS NULL ON NULL INPUT
53 AS 'select information_schema._pg_keyissubset($1, $2) and information_schema._pg_keyissubset($2, $1)';
55 CREATE FUNCTION _pg_truetypid(pg_attribute, pg_type) RETURNS oid
58 RETURNS NULL ON NULL INPUT
60 $$SELECT CASE WHEN $2.typtype = 'd' THEN $2.typbasetype ELSE $1.atttypid END$$;
62 CREATE FUNCTION _pg_truetypmod(pg_attribute, pg_type) RETURNS int4
65 RETURNS NULL ON NULL INPUT
67 $$SELECT CASE WHEN $2.typtype = 'd' THEN $2.typtypmod ELSE $1.atttypmod END$$;
69 -- these functions encapsulate knowledge about the encoding of typmod:
71 CREATE FUNCTION _pg_char_max_length(typid oid, typmod int4) RETURNS integer
74 RETURNS NULL ON NULL INPUT
77 CASE WHEN $2 = -1 /* default typmod */
79 WHEN $1 IN (1042, 1043) /* char, varchar */
81 WHEN $1 IN (1560, 1562) /* bit, varbit */
86 CREATE FUNCTION _pg_char_octet_length(typid oid, typmod int4) RETURNS integer
89 RETURNS NULL ON NULL INPUT
92 CASE WHEN $1 IN (25, 1042, 1043) /* text, char, varchar */
93 THEN CAST(2^30 AS integer)
97 CREATE FUNCTION _pg_numeric_precision(typid oid, typmod int4) RETURNS integer
100 RETURNS NULL ON NULL INPUT
104 WHEN 21 /*int2*/ THEN 16
105 WHEN 23 /*int4*/ THEN 32
106 WHEN 20 /*int8*/ THEN 64
107 WHEN 1700 /*numeric*/ THEN
110 ELSE (($2 - 4) >> 16) & 65535
112 WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/
113 WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/
117 CREATE FUNCTION _pg_numeric_precision_radix(typid oid, typmod int4) RETURNS integer
120 RETURNS NULL ON NULL INPUT
123 CASE WHEN $1 IN (21, 23, 20, 700, 701) THEN 2
124 WHEN $1 IN (1700) THEN 10
128 CREATE FUNCTION _pg_numeric_scale(typid oid, typmod int4) RETURNS integer
131 RETURNS NULL ON NULL INPUT
134 CASE WHEN $1 IN (21, 23, 20) THEN 0
135 WHEN $1 IN (1700) THEN
138 ELSE ($2 - 4) & 65535
143 CREATE FUNCTION _pg_datetime_precision(typid oid, typmod int4) RETURNS integer
146 RETURNS NULL ON NULL INPUT
149 CASE WHEN $2 = -1 /* default typmod */
151 WHEN $1 IN (1083, 1114, 1184, 1266) /* time, timestamp, same + tz */
153 WHEN $1 IN (1186) /* interval */
159 -- 5.2 INFORMATION_SCHEMA_CATALOG_NAME view appears later.
164 * CARDINAL_NUMBER domain
167 CREATE DOMAIN cardinal_number AS integer
168 CONSTRAINT cardinal_number_domain_check CHECK (value >= 0);
173 * CHARACTER_DATA domain
176 CREATE DOMAIN character_data AS character varying;
181 * SQL_IDENTIFIER domain
184 CREATE DOMAIN sql_identifier AS character varying;
189 * INFORMATION_SCHEMA_CATALOG_NAME view
192 CREATE VIEW information_schema_catalog_name AS
193 SELECT CAST(current_database() AS sql_identifier) AS catalog_name;
195 GRANT SELECT ON information_schema_catalog_name TO PUBLIC;
203 CREATE DOMAIN time_stamp AS timestamp(2) with time zone
204 DEFAULT current_timestamp(2);
207 -- 5.7 ADMINISTRABLE_ROLE_AUTHORIZATIONS view appears later.
212 * APPLICABLE_ROLES view
215 CREATE VIEW applicable_roles AS
216 SELECT CAST(a.rolname AS sql_identifier) AS grantee,
217 CAST(b.rolname AS sql_identifier) AS role_name,
218 CAST(CASE WHEN m.admin_option THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
219 FROM pg_auth_members m
220 JOIN pg_authid a ON (m.member = a.oid)
221 JOIN pg_authid b ON (m.roleid = b.oid)
222 WHERE pg_has_role(a.oid, 'USAGE');
224 GRANT SELECT ON applicable_roles TO PUBLIC;
229 * ADMINISTRABLE_ROLE_AUTHORIZATIONS view
232 CREATE VIEW administrable_role_authorizations AS
234 FROM applicable_roles
235 WHERE is_grantable = 'YES';
237 GRANT SELECT ON administrable_role_authorizations TO PUBLIC;
245 -- feature not supported
253 CREATE VIEW attributes AS
254 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
255 CAST(nc.nspname AS sql_identifier) AS udt_schema,
256 CAST(c.relname AS sql_identifier) AS udt_name,
257 CAST(a.attname AS sql_identifier) AS attribute_name,
258 CAST(a.attnum AS cardinal_number) AS ordinal_position,
259 CAST(pg_get_expr(ad.adbin, ad.adrelid) AS character_data) AS attribute_default,
260 CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
265 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
266 WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, null)
267 ELSE 'USER-DEFINED' END
272 _pg_char_max_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
274 AS character_maximum_length,
277 _pg_char_octet_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
279 AS character_octet_length,
281 CAST(null AS sql_identifier) AS character_set_catalog,
282 CAST(null AS sql_identifier) AS character_set_schema,
283 CAST(null AS sql_identifier) AS character_set_name,
285 CAST(null AS sql_identifier) AS collation_catalog,
286 CAST(null AS sql_identifier) AS collation_schema,
287 CAST(null AS sql_identifier) AS collation_name,
290 _pg_numeric_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
292 AS numeric_precision,
295 _pg_numeric_precision_radix(_pg_truetypid(a, t), _pg_truetypmod(a, t))
297 AS numeric_precision_radix,
300 _pg_numeric_scale(_pg_truetypid(a, t), _pg_truetypmod(a, t))
305 _pg_datetime_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
307 AS datetime_precision,
309 CAST(null AS character_data) AS interval_type, -- FIXME
310 CAST(null AS character_data) AS interval_precision, -- FIXME
312 CAST(current_database() AS sql_identifier) AS attribute_udt_catalog,
313 CAST(nt.nspname AS sql_identifier) AS attribute_udt_schema,
314 CAST(t.typname AS sql_identifier) AS attribute_udt_name,
316 CAST(null AS sql_identifier) AS scope_catalog,
317 CAST(null AS sql_identifier) AS scope_schema,
318 CAST(null AS sql_identifier) AS scope_name,
320 CAST(null AS cardinal_number) AS maximum_cardinality,
321 CAST(a.attnum AS sql_identifier) AS dtd_identifier,
322 CAST('NO' AS character_data) AS is_derived_reference_attribute
324 FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum),
325 pg_class c, pg_namespace nc,
326 (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
328 WHERE a.attrelid = c.oid
329 AND a.atttypid = t.oid
330 AND nc.oid = c.relnamespace
331 AND a.attnum > 0 AND NOT a.attisdropped
332 AND c.relkind in ('c');
334 GRANT SELECT ON attributes TO PUBLIC;
339 * CHARACTER_SETS view
342 -- feature not supported
347 * CHECK_CONSTRAINT_ROUTINE_USAGE view
350 CREATE VIEW check_constraint_routine_usage AS
351 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
352 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
353 CAST(c.conname AS sql_identifier) AS constraint_name,
354 CAST(current_database() AS sql_identifier) AS specific_catalog,
355 CAST(np.nspname AS sql_identifier) AS specific_schema,
356 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name
357 FROM pg_namespace nc, pg_constraint c, pg_depend d, pg_proc p, pg_namespace np
358 WHERE nc.oid = c.connamespace
361 AND d.classid = 'pg_catalog.pg_constraint'::regclass
362 AND d.refobjid = p.oid
363 AND d.refclassid = 'pg_catalog.pg_proc'::regclass
364 AND p.pronamespace = np.oid
365 AND pg_has_role(p.proowner, 'USAGE');
367 GRANT SELECT ON check_constraint_routine_usage TO PUBLIC;
372 * CHECK_CONSTRAINTS view
375 CREATE VIEW check_constraints AS
376 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
377 CAST(rs.nspname AS sql_identifier) AS constraint_schema,
378 CAST(con.conname AS sql_identifier) AS constraint_name,
379 CAST(substring(pg_get_constraintdef(con.oid) from 7) AS character_data)
381 FROM pg_constraint con
382 LEFT OUTER JOIN pg_namespace rs ON (rs.oid = con.connamespace)
383 LEFT OUTER JOIN pg_class c ON (c.oid = con.conrelid)
384 LEFT OUTER JOIN pg_type t ON (t.oid = con.contypid)
385 WHERE pg_has_role(coalesce(c.relowner, t.typowner), 'USAGE')
386 AND con.contype = 'c'
389 -- not-null constraints
391 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
392 CAST(n.nspname AS sql_identifier) AS constraint_schema,
393 CAST(n.oid || '_' || r.oid || '_' || a.attnum || '_not_null' AS sql_identifier) AS constraint_name, -- XXX
394 CAST(a.attname || ' IS NOT NULL' AS character_data)
396 FROM pg_namespace n, pg_class r, pg_attribute a
397 WHERE n.oid = r.relnamespace
398 AND r.oid = a.attrelid
400 AND NOT a.attisdropped
403 AND pg_has_role(r.relowner, 'USAGE');
405 GRANT SELECT ON check_constraints TO PUBLIC;
413 -- feature not supported
417 * COLLATION_CHARACTER_SET_APPLICABILITY view
420 -- feature not supported
425 * COLUMN_COLUMN_USAGE view
428 -- feature not supported
433 * COLUMN_DOMAIN_USAGE view
436 CREATE VIEW column_domain_usage AS
437 SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
438 CAST(nt.nspname AS sql_identifier) AS domain_schema,
439 CAST(t.typname AS sql_identifier) AS domain_name,
440 CAST(current_database() AS sql_identifier) AS table_catalog,
441 CAST(nc.nspname AS sql_identifier) AS table_schema,
442 CAST(c.relname AS sql_identifier) AS table_name,
443 CAST(a.attname AS sql_identifier) AS column_name
445 FROM pg_type t, pg_namespace nt, pg_class c, pg_namespace nc,
448 WHERE t.typnamespace = nt.oid
449 AND c.relnamespace = nc.oid
450 AND a.attrelid = c.oid
451 AND a.atttypid = t.oid
453 AND c.relkind IN ('r', 'v')
455 AND NOT a.attisdropped
456 AND pg_has_role(t.typowner, 'USAGE');
458 GRANT SELECT ON column_domain_usage TO PUBLIC;
466 CREATE VIEW column_privileges AS
467 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
468 CAST(grantee.rolname AS sql_identifier) AS grantee,
469 CAST(current_database() AS sql_identifier) AS table_catalog,
470 CAST(nc.nspname AS sql_identifier) AS table_schema,
471 CAST(c.relname AS sql_identifier) AS table_name,
472 CAST(a.attname AS sql_identifier) AS column_name,
473 CAST(pr.type AS character_data) AS privilege_type,
475 CASE WHEN aclcontains(c.relacl,
476 makeaclitem(grantee.oid, u_grantor.oid, pr.type, true))
477 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
484 SELECT oid, rolname FROM pg_authid
486 SELECT 0::oid, 'PUBLIC'
487 ) AS grantee (oid, rolname),
488 (SELECT 'SELECT' UNION ALL
489 SELECT 'INSERT' UNION ALL
490 SELECT 'UPDATE' UNION ALL
491 SELECT 'REFERENCES') AS pr (type)
493 WHERE a.attrelid = c.oid
494 AND c.relnamespace = nc.oid
496 AND NOT a.attisdropped
497 AND c.relkind IN ('r', 'v')
498 AND aclcontains(c.relacl,
499 makeaclitem(grantee.oid, u_grantor.oid, pr.type, false))
500 AND (pg_has_role(u_grantor.oid, 'USAGE')
501 OR pg_has_role(grantee.oid, 'USAGE')
502 OR grantee.rolname = 'PUBLIC');
504 GRANT SELECT ON column_privileges TO PUBLIC;
509 * COLUMN_UDT_USAGE view
512 CREATE VIEW column_udt_usage AS
513 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
514 CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,
515 CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,
516 CAST(current_database() AS sql_identifier) AS table_catalog,
517 CAST(nc.nspname AS sql_identifier) AS table_schema,
518 CAST(c.relname AS sql_identifier) AS table_name,
519 CAST(a.attname AS sql_identifier) AS column_name
521 FROM pg_attribute a, pg_class c, pg_namespace nc,
522 (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
523 LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
524 ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
526 WHERE a.attrelid = c.oid
527 AND a.atttypid = t.oid
528 AND nc.oid = c.relnamespace
529 AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
530 AND pg_has_role(coalesce(bt.typowner, t.typowner), 'USAGE');
532 GRANT SELECT ON column_udt_usage TO PUBLIC;
540 CREATE VIEW columns AS
541 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
542 CAST(nc.nspname AS sql_identifier) AS table_schema,
543 CAST(c.relname AS sql_identifier) AS table_name,
544 CAST(a.attname AS sql_identifier) AS column_name,
545 CAST(a.attnum AS cardinal_number) AS ordinal_position,
546 CAST(pg_get_expr(ad.adbin, ad.adrelid) AS character_data) AS column_default,
547 CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
552 CASE WHEN t.typtype = 'd' THEN
553 CASE WHEN bt.typelem <> 0 AND bt.typlen = -1 THEN 'ARRAY'
554 WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
555 ELSE 'USER-DEFINED' END
557 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
558 WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, null)
559 ELSE 'USER-DEFINED' END
565 _pg_char_max_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
567 AS character_maximum_length,
570 _pg_char_octet_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
572 AS character_octet_length,
575 _pg_numeric_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
577 AS numeric_precision,
580 _pg_numeric_precision_radix(_pg_truetypid(a, t), _pg_truetypmod(a, t))
582 AS numeric_precision_radix,
585 _pg_numeric_scale(_pg_truetypid(a, t), _pg_truetypmod(a, t))
590 _pg_datetime_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
592 AS datetime_precision,
594 CAST(null AS character_data) AS interval_type, -- FIXME
595 CAST(null AS character_data) AS interval_precision, -- FIXME
597 CAST(null AS sql_identifier) AS character_set_catalog,
598 CAST(null AS sql_identifier) AS character_set_schema,
599 CAST(null AS sql_identifier) AS character_set_name,
601 CAST(null AS sql_identifier) AS collation_catalog,
602 CAST(null AS sql_identifier) AS collation_schema,
603 CAST(null AS sql_identifier) AS collation_name,
605 CAST(CASE WHEN t.typtype = 'd' THEN current_database() ELSE null END
606 AS sql_identifier) AS domain_catalog,
607 CAST(CASE WHEN t.typtype = 'd' THEN nt.nspname ELSE null END
608 AS sql_identifier) AS domain_schema,
609 CAST(CASE WHEN t.typtype = 'd' THEN t.typname ELSE null END
610 AS sql_identifier) AS domain_name,
612 CAST(current_database() AS sql_identifier) AS udt_catalog,
613 CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,
614 CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,
616 CAST(null AS sql_identifier) AS scope_catalog,
617 CAST(null AS sql_identifier) AS scope_schema,
618 CAST(null AS sql_identifier) AS scope_name,
620 CAST(null AS cardinal_number) AS maximum_cardinality,
621 CAST(a.attnum AS sql_identifier) AS dtd_identifier,
622 CAST('NO' AS character_data) AS is_self_referencing,
624 CAST('NO' AS character_data) AS is_identity,
625 CAST(null AS character_data) AS identity_generation,
626 CAST(null AS character_data) AS identity_start,
627 CAST(null AS character_data) AS identity_increment,
628 CAST(null AS character_data) AS identity_maximum,
629 CAST(null AS character_data) AS identity_minimum,
630 CAST(null AS character_data) AS identity_cycle,
632 CAST('NEVER' AS character_data) AS is_generated,
633 CAST(null AS character_data) AS generation_expression,
635 CAST(CASE WHEN c.relkind = 'r'
636 THEN 'YES' ELSE 'NO' END AS character_data) AS is_updatable
638 FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum),
639 pg_class c, pg_namespace nc,
640 (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
641 LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
642 ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
644 WHERE a.attrelid = c.oid
645 AND a.atttypid = t.oid
646 AND nc.oid = c.relnamespace
647 AND (NOT pg_is_other_temp_schema(nc.oid))
649 AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
651 AND (pg_has_role(c.relowner, 'USAGE')
652 OR has_table_privilege(c.oid, 'SELECT')
653 OR has_table_privilege(c.oid, 'INSERT')
654 OR has_table_privilege(c.oid, 'UPDATE')
655 OR has_table_privilege(c.oid, 'REFERENCES') );
657 GRANT SELECT ON columns TO PUBLIC;
662 * CONSTRAINT_COLUMN_USAGE view
665 CREATE VIEW constraint_column_usage AS
666 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
667 CAST(tblschema AS sql_identifier) AS table_schema,
668 CAST(tblname AS sql_identifier) AS table_name,
669 CAST(colname AS sql_identifier) AS column_name,
670 CAST(current_database() AS sql_identifier) AS constraint_catalog,
671 CAST(cstrschema AS sql_identifier) AS constraint_schema,
672 CAST(cstrname AS sql_identifier) AS constraint_name
675 /* check constraints */
676 SELECT DISTINCT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
677 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_depend d, pg_namespace nc, pg_constraint c
678 WHERE nr.oid = r.relnamespace
679 AND r.oid = a.attrelid
680 AND d.refclassid = 'pg_catalog.pg_class'::regclass
681 AND d.refobjid = r.oid
682 AND d.refobjsubid = a.attnum
683 AND d.classid = 'pg_catalog.pg_constraint'::regclass
685 AND c.connamespace = nc.oid
688 AND NOT a.attisdropped
692 /* unique/primary key/foreign key constraints */
693 SELECT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
694 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc,
696 WHERE nr.oid = r.relnamespace
697 AND r.oid = a.attrelid
698 AND nc.oid = c.connamespace
699 AND (CASE WHEN c.contype = 'f' THEN r.oid = c.confrelid AND a.attnum = ANY (c.confkey)
700 ELSE r.oid = c.conrelid AND a.attnum = ANY (c.conkey) END)
701 AND NOT a.attisdropped
702 AND c.contype IN ('p', 'u', 'f')
705 ) AS x (tblschema, tblname, tblowner, colname, cstrschema, cstrname)
707 WHERE pg_has_role(x.tblowner, 'USAGE');
709 GRANT SELECT ON constraint_column_usage TO PUBLIC;
714 * CONSTRAINT_TABLE_USAGE view
717 CREATE VIEW constraint_table_usage AS
718 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
719 CAST(nr.nspname AS sql_identifier) AS table_schema,
720 CAST(r.relname AS sql_identifier) AS table_name,
721 CAST(current_database() AS sql_identifier) AS constraint_catalog,
722 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
723 CAST(c.conname AS sql_identifier) AS constraint_name
725 FROM pg_constraint c, pg_namespace nc,
726 pg_class r, pg_namespace nr
728 WHERE c.connamespace = nc.oid AND r.relnamespace = nr.oid
729 AND ( (c.contype = 'f' AND c.confrelid = r.oid)
730 OR (c.contype IN ('p', 'u') AND c.conrelid = r.oid) )
732 AND pg_has_role(r.relowner, 'USAGE');
734 GRANT SELECT ON constraint_table_usage TO PUBLIC;
737 -- 5.23 DATA_TYPE_PRIVILEGES view appears later.
742 * DIRECT_SUPERTABLES view
745 -- feature not supported
750 * DIRECT_SUPERTYPES view
753 -- feature not supported
758 * DOMAIN_CONSTRAINTS view
761 CREATE VIEW domain_constraints AS
762 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
763 CAST(rs.nspname AS sql_identifier) AS constraint_schema,
764 CAST(con.conname AS sql_identifier) AS constraint_name,
765 CAST(current_database() AS sql_identifier) AS domain_catalog,
766 CAST(n.nspname AS sql_identifier) AS domain_schema,
767 CAST(t.typname AS sql_identifier) AS domain_name,
768 CAST(CASE WHEN condeferrable THEN 'YES' ELSE 'NO' END
769 AS character_data) AS is_deferrable,
770 CAST(CASE WHEN condeferred THEN 'YES' ELSE 'NO' END
771 AS character_data) AS initially_deferred
772 FROM pg_namespace rs, pg_namespace n, pg_constraint con, pg_type t
773 WHERE rs.oid = con.connamespace
774 AND n.oid = t.typnamespace
775 AND t.oid = con.contypid;
777 GRANT SELECT ON domain_constraints TO PUBLIC;
781 * DOMAIN_UDT_USAGE view
782 * apparently removed in SQL:2003
785 CREATE VIEW domain_udt_usage AS
786 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
787 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
788 CAST(bt.typname AS sql_identifier) AS udt_name,
789 CAST(current_database() AS sql_identifier) AS domain_catalog,
790 CAST(nt.nspname AS sql_identifier) AS domain_schema,
791 CAST(t.typname AS sql_identifier) AS domain_name
793 FROM pg_type t, pg_namespace nt,
794 pg_type bt, pg_namespace nbt
796 WHERE t.typnamespace = nt.oid
797 AND t.typbasetype = bt.oid
798 AND bt.typnamespace = nbt.oid
800 AND pg_has_role(bt.typowner, 'USAGE');
802 GRANT SELECT ON domain_udt_usage TO PUBLIC;
810 CREATE VIEW domains AS
811 SELECT 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,
816 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
817 WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
818 ELSE 'USER-DEFINED' END
823 _pg_char_max_length(t.typbasetype, t.typtypmod)
825 AS character_maximum_length,
828 _pg_char_octet_length(t.typbasetype, t.typtypmod)
830 AS character_octet_length,
832 CAST(null AS sql_identifier) AS character_set_catalog,
833 CAST(null AS sql_identifier) AS character_set_schema,
834 CAST(null AS sql_identifier) AS character_set_name,
836 CAST(null AS sql_identifier) AS collation_catalog,
837 CAST(null AS sql_identifier) AS collation_schema,
838 CAST(null AS sql_identifier) AS collation_name,
841 _pg_numeric_precision(t.typbasetype, t.typtypmod)
843 AS numeric_precision,
846 _pg_numeric_precision_radix(t.typbasetype, t.typtypmod)
848 AS numeric_precision_radix,
851 _pg_numeric_scale(t.typbasetype, t.typtypmod)
856 _pg_datetime_precision(t.typbasetype, t.typtypmod)
858 AS datetime_precision,
860 CAST(null AS character_data) AS interval_type, -- FIXME
861 CAST(null AS character_data) AS interval_precision, -- FIXME
863 CAST(t.typdefault AS character_data) AS domain_default,
865 CAST(current_database() AS sql_identifier) AS udt_catalog,
866 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
867 CAST(bt.typname AS sql_identifier) AS udt_name,
869 CAST(null AS sql_identifier) AS scope_catalog,
870 CAST(null AS sql_identifier) AS scope_schema,
871 CAST(null AS sql_identifier) AS scope_name,
873 CAST(null AS cardinal_number) AS maximum_cardinality,
874 CAST(1 AS sql_identifier) AS dtd_identifier
876 FROM pg_type t, pg_namespace nt,
877 pg_type bt, pg_namespace nbt
879 WHERE t.typnamespace = nt.oid
880 AND t.typbasetype = bt.oid
881 AND bt.typnamespace = nbt.oid
884 GRANT SELECT ON domains TO PUBLIC;
887 -- 5.28 ELEMENT_TYPES view appears later.
895 CREATE VIEW enabled_roles AS
896 SELECT CAST(a.rolname AS sql_identifier) AS role_name
898 WHERE pg_has_role(a.oid, 'USAGE');
900 GRANT SELECT ON enabled_roles TO PUBLIC;
908 -- feature not supported
913 * KEY_COLUMN_USAGE view
916 CREATE VIEW key_column_usage AS
917 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
918 CAST(nc_nspname AS sql_identifier) AS constraint_schema,
919 CAST(conname AS sql_identifier) AS constraint_name,
920 CAST(current_database() AS sql_identifier) AS table_catalog,
921 CAST(nr_nspname AS sql_identifier) AS table_schema,
922 CAST(relname AS sql_identifier) AS table_name,
923 CAST(a.attname AS sql_identifier) AS column_name,
924 CAST((ss.x).n AS cardinal_number) AS ordinal_position,
926 SELECT CAST(a AS cardinal_number)
928 (SELECT a FROM generate_series(1, array_upper(ss.confkey,1)) a) AS foo
929 WHERE conrelid = ss.confrelid
930 AND conkey[foo.a] = ss.confkey[(ss.x).n]
931 ) AS position_in_unique_constraint
933 (SELECT r.oid, r.relname, nc.nspname AS nc_nspname,
934 nr.nspname AS nr_nspname,
935 c.conname, c.confkey, c.confrelid,
936 _pg_expandarray(c.conkey) AS x
937 FROM pg_namespace nr, pg_class r, pg_namespace nc,
939 WHERE nr.oid = r.relnamespace
940 AND r.oid = c.conrelid
941 AND nc.oid = c.connamespace
942 AND c.contype IN ('p', 'u', 'f')
944 AND (NOT pg_is_other_temp_schema(nr.oid))
945 AND (pg_has_role(r.relowner, 'USAGE')
946 OR has_table_privilege(c.oid, 'SELECT')
947 OR has_table_privilege(c.oid, 'INSERT')
948 OR has_table_privilege(c.oid, 'UPDATE')
949 OR has_table_privilege(c.oid, 'REFERENCES')) ) AS ss
950 WHERE ss.oid = a.attrelid
951 AND a.attnum = (ss.x).x
952 AND NOT a.attisdropped;
954 GRANT SELECT ON key_column_usage TO PUBLIC;
959 * METHOD_SPECIFICATION_PARAMETERS view
962 -- feature not supported
967 * METHOD_SPECIFICATIONS view
970 -- feature not supported
978 CREATE VIEW parameters AS
979 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
980 CAST(n_nspname AS sql_identifier) AS specific_schema,
981 CAST(proname || '_' || CAST(p_oid AS text) AS sql_identifier) AS specific_name,
982 CAST((ss.x).n AS cardinal_number) AS ordinal_position,
984 CASE WHEN proargmodes IS NULL THEN 'IN'
985 WHEN proargmodes[(ss.x).n] = 'i' THEN 'IN'
986 WHEN proargmodes[(ss.x).n] = 'o' THEN 'OUT'
987 WHEN proargmodes[(ss.x).n] = 'b' THEN 'INOUT'
988 END AS character_data) AS parameter_mode,
989 CAST('NO' AS character_data) AS is_result,
990 CAST('NO' AS character_data) AS as_locator,
991 CAST(NULLIF(proargnames[(ss.x).n], '') AS sql_identifier) AS parameter_name,
993 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
994 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
995 ELSE 'USER-DEFINED' END AS character_data)
997 CAST(null AS cardinal_number) AS character_maximum_length,
998 CAST(null AS cardinal_number) AS character_octet_length,
999 CAST(null AS sql_identifier) AS character_set_catalog,
1000 CAST(null AS sql_identifier) AS character_set_schema,
1001 CAST(null AS sql_identifier) AS character_set_name,
1002 CAST(null AS sql_identifier) AS collation_catalog,
1003 CAST(null AS sql_identifier) AS collation_schema,
1004 CAST(null AS sql_identifier) AS collation_name,
1005 CAST(null AS cardinal_number) AS numeric_precision,
1006 CAST(null AS cardinal_number) AS numeric_precision_radix,
1007 CAST(null AS cardinal_number) AS numeric_scale,
1008 CAST(null AS cardinal_number) AS datetime_precision,
1009 CAST(null AS character_data) AS interval_type,
1010 CAST(null AS character_data) AS interval_precision,
1011 CAST(current_database() AS sql_identifier) AS udt_catalog,
1012 CAST(nt.nspname AS sql_identifier) AS udt_schema,
1013 CAST(t.typname AS sql_identifier) AS udt_name,
1014 CAST(null AS sql_identifier) AS scope_catalog,
1015 CAST(null AS sql_identifier) AS scope_schema,
1016 CAST(null AS sql_identifier) AS scope_name,
1017 CAST(null AS cardinal_number) AS maximum_cardinality,
1018 CAST((ss.x).n AS sql_identifier) AS dtd_identifier
1020 FROM pg_type t, pg_namespace nt,
1021 (SELECT n.nspname AS n_nspname, p.proname, p.oid AS p_oid,
1022 p.proargnames, p.proargmodes,
1023 _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
1024 FROM pg_namespace n, pg_proc p
1025 WHERE n.oid = p.pronamespace
1026 AND (pg_has_role(p.proowner, 'USAGE') OR
1027 has_function_privilege(p.oid, 'EXECUTE'))) AS ss
1028 WHERE t.oid = (ss.x).x AND t.typnamespace = nt.oid;
1030 GRANT SELECT ON parameters TO PUBLIC;
1035 * REFERENCED_TYPES view
1038 -- feature not supported
1043 * REFERENTIAL_CONSTRAINTS view
1046 CREATE VIEW referential_constraints AS
1047 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1048 CAST(ncon.nspname AS sql_identifier) AS constraint_schema,
1049 CAST(con.conname AS sql_identifier) AS constraint_name,
1051 CASE WHEN npkc.nspname IS NULL THEN NULL
1052 ELSE current_database() END
1053 AS sql_identifier) AS unique_constraint_catalog,
1054 CAST(npkc.nspname AS sql_identifier) AS unique_constraint_schema,
1055 CAST(pkc.conname AS sql_identifier) AS unique_constraint_name,
1058 CASE con.confmatchtype WHEN 'f' THEN 'FULL'
1059 WHEN 'p' THEN 'PARTIAL'
1060 WHEN 'u' THEN 'NONE' END
1061 AS character_data) AS match_option,
1064 CASE con.confupdtype 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 update_rule,
1072 CASE con.confdeltype WHEN 'c' THEN 'CASCADE'
1073 WHEN 'n' THEN 'SET NULL'
1074 WHEN 'd' THEN 'SET DEFAULT'
1075 WHEN 'r' THEN 'RESTRICT'
1076 WHEN 'a' THEN 'NO ACTION' END
1077 AS character_data) AS delete_rule
1079 FROM (pg_namespace ncon
1080 INNER JOIN pg_constraint con ON ncon.oid = con.connamespace
1081 INNER JOIN pg_class c ON con.conrelid = c.oid)
1084 INNER JOIN pg_namespace npkc ON pkc.connamespace = npkc.oid)
1085 ON con.confrelid = pkc.conrelid
1086 AND _pg_keysequal(con.confkey, pkc.conkey)
1088 WHERE c.relkind = 'r'
1089 AND con.contype = 'f'
1090 AND (pkc.contype IN ('p', 'u') OR pkc.contype IS NULL)
1091 AND pg_has_role(c.relowner, 'USAGE');
1093 GRANT SELECT ON referential_constraints TO PUBLIC;
1098 * ROLE_COLUMN_GRANTS view
1101 CREATE VIEW role_column_grants AS
1102 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1103 CAST(g_grantee.rolname AS sql_identifier) AS grantee,
1104 CAST(current_database() AS sql_identifier) AS table_catalog,
1105 CAST(nc.nspname AS sql_identifier) AS table_schema,
1106 CAST(c.relname AS sql_identifier) AS table_name,
1107 CAST(a.attname AS sql_identifier) AS column_name,
1108 CAST(pr.type AS character_data) AS privilege_type,
1110 CASE WHEN aclcontains(c.relacl,
1111 makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, true))
1112 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
1114 FROM pg_attribute a,
1117 pg_authid u_grantor,
1118 pg_authid g_grantee,
1119 (SELECT 'SELECT' UNION ALL
1120 SELECT 'INSERT' UNION ALL
1121 SELECT 'UPDATE' UNION ALL
1122 SELECT 'REFERENCES') AS pr (type)
1124 WHERE a.attrelid = c.oid
1125 AND c.relnamespace = nc.oid
1127 AND NOT a.attisdropped
1128 AND c.relkind IN ('r', 'v')
1129 AND aclcontains(c.relacl,
1130 makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, false))
1131 AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
1132 OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));
1134 GRANT SELECT ON role_column_grants TO PUBLIC;
1139 * ROLE_ROUTINE_GRANTS view
1142 CREATE VIEW role_routine_grants AS
1143 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1144 CAST(g_grantee.rolname AS sql_identifier) AS grantee,
1145 CAST(current_database() AS sql_identifier) AS specific_catalog,
1146 CAST(n.nspname AS sql_identifier) AS specific_schema,
1147 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1148 CAST(current_database() AS sql_identifier) AS routine_catalog,
1149 CAST(n.nspname AS sql_identifier) AS routine_schema,
1150 CAST(p.proname AS sql_identifier) AS routine_name,
1151 CAST('EXECUTE' AS character_data) AS privilege_type,
1153 CASE WHEN aclcontains(p.proacl,
1154 makeaclitem(g_grantee.oid, u_grantor.oid, 'EXECUTE', true))
1155 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
1159 pg_authid u_grantor,
1162 WHERE p.pronamespace = n.oid
1163 AND aclcontains(p.proacl,
1164 makeaclitem(g_grantee.oid, u_grantor.oid, 'EXECUTE', false))
1165 AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
1166 OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));
1168 GRANT SELECT ON role_routine_grants TO PUBLIC;
1173 * ROLE_TABLE_GRANTS view
1176 CREATE VIEW role_table_grants AS
1177 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1178 CAST(g_grantee.rolname AS sql_identifier) AS grantee,
1179 CAST(current_database() AS sql_identifier) AS table_catalog,
1180 CAST(nc.nspname AS sql_identifier) AS table_schema,
1181 CAST(c.relname AS sql_identifier) AS table_name,
1182 CAST(pr.type AS character_data) AS privilege_type,
1184 CASE WHEN aclcontains(c.relacl,
1185 makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, true))
1186 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable,
1187 CAST('NO' AS character_data) AS with_hierarchy
1191 pg_authid u_grantor,
1192 pg_authid g_grantee,
1193 (SELECT 'SELECT' UNION ALL
1194 SELECT 'DELETE' UNION ALL
1195 SELECT 'INSERT' UNION ALL
1196 SELECT 'UPDATE' UNION ALL
1197 SELECT 'REFERENCES' UNION ALL
1198 SELECT 'TRIGGER') AS pr (type)
1200 WHERE c.relnamespace = nc.oid
1201 AND c.relkind IN ('r', 'v')
1202 AND aclcontains(c.relacl,
1203 makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, false))
1204 AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
1205 OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));
1207 GRANT SELECT ON role_table_grants TO PUBLIC;
1212 * ROLE_TABLE_METHOD_GRANTS view
1215 -- feature not supported
1220 * ROLE_USAGE_GRANTS view
1223 -- See USAGE_PRIVILEGES.
1225 CREATE VIEW role_usage_grants AS
1226 SELECT CAST(null AS sql_identifier) AS grantor,
1227 CAST(null AS sql_identifier) AS grantee,
1228 CAST(current_database() AS sql_identifier) AS object_catalog,
1229 CAST(null AS sql_identifier) AS object_schema,
1230 CAST(null AS sql_identifier) AS object_name,
1231 CAST(null AS character_data) AS object_type,
1232 CAST('USAGE' AS character_data) AS privilege_type,
1233 CAST(null AS character_data) AS is_grantable
1237 GRANT SELECT ON role_usage_grants TO PUBLIC;
1242 * ROLE_UDT_GRANTS view
1245 -- feature not supported
1250 * ROUTINE_COLUMN_USAGE view
1253 -- not tracked by PostgreSQL
1258 * ROUTINE_PRIVILEGES view
1261 CREATE VIEW routine_privileges AS
1262 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1263 CAST(grantee.rolname AS sql_identifier) AS grantee,
1264 CAST(current_database() AS sql_identifier) AS specific_catalog,
1265 CAST(n.nspname AS sql_identifier) AS specific_schema,
1266 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1267 CAST(current_database() AS sql_identifier) AS routine_catalog,
1268 CAST(n.nspname AS sql_identifier) AS routine_schema,
1269 CAST(p.proname AS sql_identifier) AS routine_name,
1270 CAST('EXECUTE' AS character_data) AS privilege_type,
1272 CASE WHEN aclcontains(p.proacl,
1273 makeaclitem(grantee.oid, u_grantor.oid, 'EXECUTE', true))
1274 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
1278 pg_authid u_grantor,
1280 SELECT oid, rolname FROM pg_authid
1282 SELECT 0::oid, 'PUBLIC'
1283 ) AS grantee (oid, rolname)
1285 WHERE p.pronamespace = n.oid
1286 AND aclcontains(p.proacl,
1287 makeaclitem(grantee.oid, u_grantor.oid, 'EXECUTE', false))
1288 AND (pg_has_role(u_grantor.oid, 'USAGE')
1289 OR pg_has_role(grantee.oid, 'USAGE')
1290 OR grantee.rolname = 'PUBLIC');
1292 GRANT SELECT ON routine_privileges TO PUBLIC;
1297 * ROUTINE_ROUTINE_USAGE view
1300 -- not tracked by PostgreSQL
1305 * ROUTINE_SEQUENCE_USAGE view
1308 -- not tracked by PostgreSQL
1313 * ROUTINE_TABLE_USAGE view
1316 -- not tracked by PostgreSQL
1324 CREATE VIEW routines AS
1325 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
1326 CAST(n.nspname AS sql_identifier) AS specific_schema,
1327 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1328 CAST(current_database() AS sql_identifier) AS routine_catalog,
1329 CAST(n.nspname AS sql_identifier) AS routine_schema,
1330 CAST(p.proname AS sql_identifier) AS routine_name,
1331 CAST('FUNCTION' AS character_data) AS routine_type,
1332 CAST(null AS sql_identifier) AS module_catalog,
1333 CAST(null AS sql_identifier) AS module_schema,
1334 CAST(null AS sql_identifier) AS module_name,
1335 CAST(null AS sql_identifier) AS udt_catalog,
1336 CAST(null AS sql_identifier) AS udt_schema,
1337 CAST(null AS sql_identifier) AS udt_name,
1340 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
1341 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
1342 ELSE 'USER-DEFINED' END AS character_data)
1344 CAST(null AS cardinal_number) AS character_maximum_length,
1345 CAST(null AS cardinal_number) AS character_octet_length,
1346 CAST(null AS sql_identifier) AS character_set_catalog,
1347 CAST(null AS sql_identifier) AS character_set_schema,
1348 CAST(null AS sql_identifier) AS character_set_name,
1349 CAST(null AS sql_identifier) AS collation_catalog,
1350 CAST(null AS sql_identifier) AS collation_schema,
1351 CAST(null AS sql_identifier) AS collation_name,
1352 CAST(null AS cardinal_number) AS numeric_precision,
1353 CAST(null AS cardinal_number) AS numeric_precision_radix,
1354 CAST(null AS cardinal_number) AS numeric_scale,
1355 CAST(null AS cardinal_number) AS datetime_precision,
1356 CAST(null AS character_data) AS interval_type,
1357 CAST(null AS character_data) AS interval_precision,
1358 CAST(current_database() AS sql_identifier) AS type_udt_catalog,
1359 CAST(nt.nspname AS sql_identifier) AS type_udt_schema,
1360 CAST(t.typname AS sql_identifier) AS type_udt_name,
1361 CAST(null AS sql_identifier) AS scope_catalog,
1362 CAST(null AS sql_identifier) AS scope_schema,
1363 CAST(null AS sql_identifier) AS scope_name,
1364 CAST(null AS cardinal_number) AS maximum_cardinality,
1365 CAST(0 AS sql_identifier) AS dtd_identifier,
1367 CAST(CASE WHEN l.lanname = 'sql' THEN 'SQL' ELSE 'EXTERNAL' END AS character_data)
1370 CASE WHEN pg_has_role(p.proowner, 'USAGE') THEN p.prosrc ELSE null END
1371 AS character_data) AS routine_definition,
1373 CASE WHEN l.lanname = 'c' THEN p.prosrc ELSE null END
1374 AS character_data) AS external_name,
1375 CAST(upper(l.lanname) AS character_data) AS external_language,
1377 CAST('GENERAL' AS character_data) AS parameter_style,
1378 CAST(CASE WHEN p.provolatile = 'i' THEN 'YES' ELSE 'NO' END AS character_data) AS is_deterministic,
1379 CAST('MODIFIES' AS character_data) AS sql_data_access,
1380 CAST(CASE WHEN p.proisstrict THEN 'YES' ELSE 'NO' END AS character_data) AS is_null_call,
1381 CAST(null AS character_data) AS sql_path,
1382 CAST('YES' AS character_data) AS schema_level_routine,
1383 CAST(0 AS cardinal_number) AS max_dynamic_result_sets,
1384 CAST(null AS character_data) AS is_user_defined_cast,
1385 CAST(null AS character_data) AS is_implicitly_invocable,
1386 CAST(CASE WHEN p.prosecdef THEN 'DEFINER' ELSE 'INVOKER' END AS character_data) AS security_type,
1387 CAST(null AS sql_identifier) AS to_sql_specific_catalog,
1388 CAST(null AS sql_identifier) AS to_sql_specific_schema,
1389 CAST(null AS sql_identifier) AS to_sql_specific_name,
1390 CAST('NO' AS character_data) AS as_locator,
1391 CAST(null AS time_stamp) AS created,
1392 CAST(null AS time_stamp) AS last_altered,
1393 CAST(null AS character_data) AS new_savepoint_level,
1394 CAST('YES' AS character_data) AS is_udt_dependent, -- FIXME?
1396 CAST(null AS character_data) AS result_cast_from_data_type,
1397 CAST(null AS character_data) AS result_cast_as_locator,
1398 CAST(null AS cardinal_number) AS result_cast_char_max_length,
1399 CAST(null AS cardinal_number) AS result_cast_char_octet_length,
1400 CAST(null AS sql_identifier) AS result_cast_char_set_catalog,
1401 CAST(null AS sql_identifier) AS result_cast_char_set_schema,
1402 CAST(null AS sql_identifier) AS result_cast_character_set_name,
1403 CAST(null AS sql_identifier) AS result_cast_collation_catalog,
1404 CAST(null AS sql_identifier) AS result_cast_collation_schema,
1405 CAST(null AS sql_identifier) AS result_cast_collation_name,
1406 CAST(null AS cardinal_number) AS result_cast_numeric_precision,
1407 CAST(null AS cardinal_number) AS result_cast_numeric_precision_radix,
1408 CAST(null AS cardinal_number) AS result_cast_numeric_scale,
1409 CAST(null AS cardinal_number) AS result_cast_datetime_precision,
1410 CAST(null AS character_data) AS result_cast_interval_type,
1411 CAST(null AS character_data) AS result_cast_interval_precision,
1412 CAST(null AS sql_identifier) AS result_cast_type_udt_catalog,
1413 CAST(null AS sql_identifier) AS result_cast_type_udt_schema,
1414 CAST(null AS sql_identifier) AS result_cast_type_udt_name,
1415 CAST(null AS sql_identifier) AS result_cast_scope_catalog,
1416 CAST(null AS sql_identifier) AS result_cast_scope_schema,
1417 CAST(null AS sql_identifier) AS result_cast_scope_name,
1418 CAST(null AS cardinal_number) AS result_cast_maximum_cardinality,
1419 CAST(null AS sql_identifier) AS result_cast_dtd_identifier
1421 FROM pg_namespace n, pg_proc p, pg_language l,
1422 pg_type t, pg_namespace nt
1424 WHERE n.oid = p.pronamespace AND p.prolang = l.oid
1425 AND p.prorettype = t.oid AND t.typnamespace = nt.oid
1426 AND (pg_has_role(p.proowner, 'USAGE')
1427 OR has_function_privilege(p.oid, 'EXECUTE'));
1429 GRANT SELECT ON routines TO PUBLIC;
1437 CREATE VIEW schemata AS
1438 SELECT CAST(current_database() AS sql_identifier) AS catalog_name,
1439 CAST(n.nspname AS sql_identifier) AS schema_name,
1440 CAST(u.rolname AS sql_identifier) AS schema_owner,
1441 CAST(null AS sql_identifier) AS default_character_set_catalog,
1442 CAST(null AS sql_identifier) AS default_character_set_schema,
1443 CAST(null AS sql_identifier) AS default_character_set_name,
1444 CAST(null AS character_data) AS sql_path
1445 FROM pg_namespace n, pg_authid u
1446 WHERE n.nspowner = u.oid AND pg_has_role(n.nspowner, 'USAGE');
1448 GRANT SELECT ON schemata TO PUBLIC;
1456 CREATE VIEW sequences AS
1457 SELECT CAST(current_database() AS sql_identifier) AS sequence_catalog,
1458 CAST(nc.nspname AS sql_identifier) AS sequence_schema,
1459 CAST(c.relname AS sql_identifier) AS sequence_name,
1460 CAST('bigint' AS character_data) AS data_type,
1461 CAST(64 AS cardinal_number) AS numeric_precision,
1462 CAST(2 AS cardinal_number) AS numeric_precision_radix,
1463 CAST(0 AS cardinal_number) AS numeric_scale,
1464 CAST(null AS cardinal_number) AS maximum_value, -- FIXME
1465 CAST(null AS cardinal_number) AS minimum_value, -- FIXME
1466 CAST(null AS cardinal_number) AS increment, -- FIXME
1467 CAST(null AS character_data) AS cycle_option -- FIXME
1468 FROM pg_namespace nc, pg_class c
1469 WHERE c.relnamespace = nc.oid
1471 AND (NOT pg_is_other_temp_schema(nc.oid))
1472 AND (pg_has_role(c.relowner, 'USAGE')
1473 OR has_table_privilege(c.oid, 'SELECT')
1474 OR has_table_privilege(c.oid, 'UPDATE') );
1476 GRANT SELECT ON sequences TO PUBLIC;
1481 * SQL_FEATURES table
1484 CREATE TABLE sql_features (
1485 feature_id character_data,
1486 feature_name character_data,
1487 sub_feature_id character_data,
1488 sub_feature_name character_data,
1489 is_supported character_data,
1490 is_verified_by character_data,
1491 comments character_data
1494 -- Will be filled with external data by initdb.
1496 GRANT SELECT ON sql_features TO PUBLIC;
1501 * SQL_IMPLEMENTATION_INFO table
1504 -- Note: Implementation information items are defined in ISO/IEC 9075-3:2003,
1507 CREATE TABLE sql_implementation_info (
1508 implementation_info_id character_data,
1509 implementation_info_name character_data,
1510 integer_value cardinal_number,
1511 character_value character_data,
1512 comments character_data
1515 INSERT INTO sql_implementation_info VALUES ('10003', 'CATALOG NAME', NULL, 'Y', NULL);
1516 INSERT INTO sql_implementation_info VALUES ('10004', 'COLLATING SEQUENCE', NULL, '', 'not supported');
1517 INSERT INTO sql_implementation_info VALUES ('23', 'CURSOR COMMIT BEHAVIOR', 1, NULL, 'close cursors and retain prepared statements');
1518 INSERT INTO sql_implementation_info VALUES ('2', 'DATA SOURCE NAME', NULL, '', NULL);
1519 INSERT INTO sql_implementation_info VALUES ('17', 'DBMS NAME', NULL, (select trim(trailing ' ' from substring(version() from '^[^0-9]*'))), NULL);
1520 INSERT INTO sql_implementation_info VALUES ('18', 'DBMS VERSION', NULL, '???', NULL); -- filled by initdb
1521 INSERT INTO sql_implementation_info VALUES ('26', 'DEFAULT TRANSACTION ISOLATION', 2, NULL, 'READ COMMITTED; user-settable');
1522 INSERT INTO sql_implementation_info VALUES ('28', 'IDENTIFIER CASE', 3, NULL, 'stored in mixed case - case sensitive');
1523 INSERT INTO sql_implementation_info VALUES ('85', 'NULL COLLATION', 0, NULL, 'nulls higher than non-nulls');
1524 INSERT INTO sql_implementation_info VALUES ('13', 'SERVER NAME', NULL, '', NULL);
1525 INSERT INTO sql_implementation_info VALUES ('94', 'SPECIAL CHARACTERS', NULL, '', 'all non-ASCII characters allowed');
1526 INSERT INTO sql_implementation_info VALUES ('46', 'TRANSACTION CAPABLE', 2, NULL, 'both DML and DDL');
1528 GRANT SELECT ON sql_implementation_info TO PUBLIC;
1533 * SQL_LANGUAGES table
1536 CREATE TABLE sql_languages (
1537 sql_language_source character_data,
1538 sql_language_year character_data,
1539 sql_language_conformance character_data,
1540 sql_language_integrity character_data,
1541 sql_language_implementation character_data,
1542 sql_language_binding_style character_data,
1543 sql_language_programming_language character_data
1546 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'DIRECT', NULL);
1547 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
1548 INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'DIRECT', NULL);
1549 INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
1551 GRANT SELECT ON sql_languages TO PUBLIC;
1556 * SQL_PACKAGES table
1559 CREATE TABLE sql_packages (
1560 feature_id character_data,
1561 feature_name character_data,
1562 is_supported character_data,
1563 is_verified_by character_data,
1564 comments character_data
1567 INSERT INTO sql_packages VALUES ('PKG000', 'Core', 'NO', NULL, '');
1568 INSERT INTO sql_packages VALUES ('PKG001', 'Enhanced datetime facilities', 'YES', NULL, '');
1569 INSERT INTO sql_packages VALUES ('PKG002', 'Enhanced integrity management', 'NO', NULL, '');
1570 INSERT INTO sql_packages VALUES ('PKG003', 'OLAP facilities', 'NO', NULL, '');
1571 INSERT INTO sql_packages VALUES ('PKG004', 'PSM', 'NO', NULL, 'PL/pgSQL is similar.');
1572 INSERT INTO sql_packages VALUES ('PKG005', 'CLI', 'NO', NULL, 'ODBC is similar.');
1573 INSERT INTO sql_packages VALUES ('PKG006', 'Basic object support', 'NO', NULL, '');
1574 INSERT INTO sql_packages VALUES ('PKG007', 'Enhanced object support', 'NO', NULL, '');
1575 INSERT INTO sql_packages VALUES ('PKG008', 'Active database', 'NO', NULL, '');
1576 INSERT INTO sql_packages VALUES ('PKG010', 'OLAP', 'NO', NULL, 'NO');
1578 GRANT SELECT ON sql_packages TO PUBLIC;
1586 CREATE TABLE sql_parts (
1587 feature_id character_data,
1588 feature_name character_data,
1589 is_supported character_data,
1590 is_verified_by character_data,
1591 comments character_data
1594 INSERT INTO sql_parts VALUES ('1', 'Framework (SQL/Framework)', 'NO', NULL, '');
1595 INSERT INTO sql_parts VALUES ('2', 'Foundation (SQL/Foundation)', 'NO', NULL, '');
1596 INSERT INTO sql_parts VALUES ('3', 'Call-Level Interface (SQL/CLI)', 'NO', NULL, '');
1597 INSERT INTO sql_parts VALUES ('4', 'Persistent Stored Modules (SQL/PSM)', NULL, '');
1598 INSERT INTO sql_parts VALUES ('9', 'Management of External Data (SQL/MED)', NULL, '');
1599 INSERT INTO sql_parts VALUES ('10', 'Object Language Bindings (SQL/OLB)', NULL, '');
1600 INSERT INTO sql_parts VALUES ('11', 'Information and Definition Schema (SQL/Schemata)', NULL, '');
1601 INSERT INTO sql_parts VALUES ('13', 'Routines and Types Using the Java Programming Language (SQL/JRT)', NULL, '');
1602 INSERT INTO sql_parts VALUES ('14', 'XML-Related Specifications (SQL/XML)', NULL, '');
1610 -- Note: Sizing items are defined in ISO/IEC 9075-3:2003, clause 7.2.
1612 CREATE TABLE sql_sizing (
1613 sizing_id cardinal_number,
1614 sizing_name character_data,
1615 supported_value cardinal_number,
1616 comments character_data
1619 INSERT INTO sql_sizing VALUES (34, 'MAXIMUM CATALOG NAME LENGTH', 63, NULL);
1620 INSERT INTO sql_sizing VALUES (30, 'MAXIMUM COLUMN NAME LENGTH', 63, NULL);
1621 INSERT INTO sql_sizing VALUES (97, 'MAXIMUM COLUMNS IN GROUP BY', 0, NULL);
1622 INSERT INTO sql_sizing VALUES (99, 'MAXIMUM COLUMNS IN ORDER BY', 0, NULL);
1623 INSERT INTO sql_sizing VALUES (100, 'MAXIMUM COLUMNS IN SELECT', 1664, NULL); -- match MaxTupleAttributeNumber
1624 INSERT INTO sql_sizing VALUES (101, 'MAXIMUM COLUMNS IN TABLE', 1600, NULL); -- match MaxHeapAttributeNumber
1625 INSERT INTO sql_sizing VALUES (1, 'MAXIMUM CONCURRENT ACTIVITIES', 0, NULL);
1626 INSERT INTO sql_sizing VALUES (31, 'MAXIMUM CURSOR NAME LENGTH', 63, NULL);
1627 INSERT INTO sql_sizing VALUES (0, 'MAXIMUM DRIVER CONNECTIONS', NULL, NULL);
1628 INSERT INTO sql_sizing VALUES (10005, 'MAXIMUM IDENTIFIER LENGTH', 63, NULL);
1629 INSERT INTO sql_sizing VALUES (32, 'MAXIMUM SCHEMA NAME LENGTH', 63, NULL);
1630 INSERT INTO sql_sizing VALUES (20000, 'MAXIMUM STATEMENT OCTETS', 0, NULL);
1631 INSERT INTO sql_sizing VALUES (20001, 'MAXIMUM STATEMENT OCTETS DATA', 0, NULL);
1632 INSERT INTO sql_sizing VALUES (20002, 'MAXIMUM STATEMENT OCTETS SCHEMA', 0, NULL);
1633 INSERT INTO sql_sizing VALUES (35, 'MAXIMUM TABLE NAME LENGTH', 63, NULL);
1634 INSERT INTO sql_sizing VALUES (106, 'MAXIMUM TABLES IN SELECT', 0, NULL);
1635 INSERT INTO sql_sizing VALUES (107, 'MAXIMUM USER NAME LENGTH', 63, NULL);
1636 INSERT INTO sql_sizing VALUES (25000, 'MAXIMUM CURRENT DEFAULT TRANSFORM GROUP LENGTH', NULL, NULL);
1637 INSERT INTO sql_sizing VALUES (25001, 'MAXIMUM CURRENT TRANSFORM GROUP LENGTH', NULL, NULL);
1638 INSERT INTO sql_sizing VALUES (25002, 'MAXIMUM CURRENT PATH LENGTH', 0, NULL);
1639 INSERT INTO sql_sizing VALUES (25003, 'MAXIMUM CURRENT ROLE LENGTH', NULL, NULL);
1640 INSERT INTO sql_sizing VALUES (25004, 'MAXIMUM SESSION USER LENGTH', 63, NULL);
1641 INSERT INTO sql_sizing VALUES (25005, 'MAXIMUM SYSTEM USER LENGTH', 63, NULL);
1644 SET supported_value = (SELECT typlen-1 FROM pg_catalog.pg_type WHERE typname = 'name'),
1645 comments = 'Might be less, depending on character set.'
1646 WHERE supported_value = 63;
1648 GRANT SELECT ON sql_sizing TO PUBLIC;
1653 * SQL_SIZING_PROFILES table
1656 -- The data in this table are defined by various profiles of SQL.
1657 -- Since we don't have any information about such profiles, we provide
1660 CREATE TABLE sql_sizing_profiles (
1661 sizing_id cardinal_number,
1662 sizing_name character_data,
1663 profile_id character_data,
1664 required_value cardinal_number,
1665 comments character_data
1668 GRANT SELECT ON sql_sizing_profiles TO PUBLIC;
1673 * TABLE_CONSTRAINTS view
1676 CREATE VIEW table_constraints AS
1677 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1678 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
1679 CAST(c.conname AS sql_identifier) AS constraint_name,
1680 CAST(current_database() AS sql_identifier) AS table_catalog,
1681 CAST(nr.nspname AS sql_identifier) AS table_schema,
1682 CAST(r.relname AS sql_identifier) AS table_name,
1684 CASE c.contype WHEN 'c' THEN 'CHECK'
1685 WHEN 'f' THEN 'FOREIGN KEY'
1686 WHEN 'p' THEN 'PRIMARY KEY'
1687 WHEN 'u' THEN 'UNIQUE' END
1688 AS character_data) AS constraint_type,
1689 CAST(CASE WHEN c.condeferrable THEN 'YES' ELSE 'NO' END AS character_data)
1691 CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS character_data)
1692 AS initially_deferred
1694 FROM pg_namespace nc,
1699 WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace
1700 AND c.conrelid = r.oid
1702 AND (NOT pg_is_other_temp_schema(nr.oid))
1703 AND (pg_has_role(r.relowner, 'USAGE')
1704 -- SELECT privilege omitted, per SQL standard
1705 OR has_table_privilege(r.oid, 'INSERT')
1706 OR has_table_privilege(r.oid, 'UPDATE')
1707 OR has_table_privilege(r.oid, 'DELETE')
1708 OR has_table_privilege(r.oid, 'REFERENCES')
1709 OR has_table_privilege(r.oid, 'TRIGGER') )
1713 -- not-null constraints
1715 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1716 CAST(nr.nspname AS sql_identifier) AS constraint_schema,
1717 CAST(nr.oid || '_' || r.oid || '_' || a.attnum || '_not_null' AS sql_identifier) AS constraint_name, -- XXX
1718 CAST(current_database() AS sql_identifier) AS table_catalog,
1719 CAST(nr.nspname AS sql_identifier) AS table_schema,
1720 CAST(r.relname AS sql_identifier) AS table_name,
1721 CAST('CHECK' AS character_data) AS constraint_type,
1722 CAST('NO' AS character_data) AS is_deferrable,
1723 CAST('NO' AS character_data) AS initially_deferred
1725 FROM pg_namespace nr,
1729 WHERE nr.oid = r.relnamespace
1730 AND r.oid = a.attrelid
1733 AND NOT a.attisdropped
1735 AND (NOT pg_is_other_temp_schema(nr.oid))
1736 AND (pg_has_role(r.relowner, 'USAGE')
1737 OR has_table_privilege(r.oid, 'SELECT')
1738 OR has_table_privilege(r.oid, 'INSERT')
1739 OR has_table_privilege(r.oid, 'UPDATE')
1740 OR has_table_privilege(r.oid, 'DELETE')
1741 OR has_table_privilege(r.oid, 'REFERENCES')
1742 OR has_table_privilege(r.oid, 'TRIGGER') );
1744 GRANT SELECT ON table_constraints TO PUBLIC;
1749 * TABLE_METHOD_PRIVILEGES view
1752 -- feature not supported
1757 * TABLE_PRIVILEGES view
1760 CREATE VIEW table_privileges AS
1761 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1762 CAST(grantee.rolname AS sql_identifier) AS grantee,
1763 CAST(current_database() AS sql_identifier) AS table_catalog,
1764 CAST(nc.nspname AS sql_identifier) AS table_schema,
1765 CAST(c.relname AS sql_identifier) AS table_name,
1766 CAST(pr.type AS character_data) AS privilege_type,
1768 CASE WHEN aclcontains(c.relacl,
1769 makeaclitem(grantee.oid, u_grantor.oid, pr.type, true))
1770 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable,
1771 CAST('NO' AS character_data) AS with_hierarchy
1775 pg_authid u_grantor,
1777 SELECT oid, rolname FROM pg_authid
1779 SELECT 0::oid, 'PUBLIC'
1780 ) AS grantee (oid, rolname),
1781 (SELECT 'SELECT' UNION ALL
1782 SELECT 'DELETE' UNION ALL
1783 SELECT 'INSERT' UNION ALL
1784 SELECT 'UPDATE' UNION ALL
1785 SELECT 'REFERENCES' UNION ALL
1786 SELECT 'TRIGGER') AS pr (type)
1788 WHERE c.relnamespace = nc.oid
1789 AND c.relkind IN ('r', 'v')
1790 AND aclcontains(c.relacl,
1791 makeaclitem(grantee.oid, u_grantor.oid, pr.type, false))
1792 AND (pg_has_role(u_grantor.oid, 'USAGE')
1793 OR pg_has_role(grantee.oid, 'USAGE')
1794 OR grantee.rolname = 'PUBLIC');
1796 GRANT SELECT ON table_privileges TO PUBLIC;
1804 CREATE VIEW tables AS
1805 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
1806 CAST(nc.nspname AS sql_identifier) AS table_schema,
1807 CAST(c.relname AS sql_identifier) AS table_name,
1810 CASE WHEN nc.oid = pg_my_temp_schema() THEN 'LOCAL TEMPORARY'
1811 WHEN c.relkind = 'r' THEN 'BASE TABLE'
1812 WHEN c.relkind = 'v' THEN 'VIEW'
1814 AS character_data) AS table_type,
1816 CAST(null AS sql_identifier) AS self_referencing_column_name,
1817 CAST(null AS character_data) AS reference_generation,
1819 CAST(null AS sql_identifier) AS user_defined_type_catalog,
1820 CAST(null AS sql_identifier) AS user_defined_type_schema,
1821 CAST(null AS sql_identifier) AS user_defined_type_name,
1823 CAST(CASE WHEN c.relkind = 'r'
1824 THEN 'YES' ELSE 'NO' END AS character_data) AS is_insertable_into,
1825 CAST('NO' AS character_data) AS is_typed,
1827 CASE WHEN nc.oid = pg_my_temp_schema() THEN 'PRESERVE' -- FIXME
1829 AS character_data) AS commit_action
1831 FROM pg_namespace nc, pg_class c
1833 WHERE c.relnamespace = nc.oid
1834 AND c.relkind IN ('r', 'v')
1835 AND (NOT pg_is_other_temp_schema(nc.oid))
1836 AND (pg_has_role(c.relowner, 'USAGE')
1837 OR has_table_privilege(c.oid, 'SELECT')
1838 OR has_table_privilege(c.oid, 'INSERT')
1839 OR has_table_privilege(c.oid, 'UPDATE')
1840 OR has_table_privilege(c.oid, 'DELETE')
1841 OR has_table_privilege(c.oid, 'REFERENCES')
1842 OR has_table_privilege(c.oid, 'TRIGGER') );
1844 GRANT SELECT ON tables TO PUBLIC;
1852 -- feature not supported
1860 -- feature not supported
1865 * TRIGGERED_UPDATE_COLUMNS view
1868 -- PostgreSQL doesn't allow the specification of individual triggered
1869 -- update columns, so this view is empty.
1871 CREATE VIEW triggered_update_columns AS
1872 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1873 CAST(null AS sql_identifier) AS trigger_schema,
1874 CAST(null AS sql_identifier) AS trigger_name,
1875 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1876 CAST(null AS sql_identifier) AS event_object_schema,
1877 CAST(null AS sql_identifier) AS event_object_table,
1878 CAST(null AS sql_identifier) AS event_object_column
1881 GRANT SELECT ON triggered_update_columns TO PUBLIC;
1886 * TRIGGER_COLUMN_USAGE view
1889 -- not tracked by PostgreSQL
1894 * TRIGGER_ROUTINE_USAGE view
1897 -- not tracked by PostgreSQL
1902 * TRIGGER_SEQUENCE_USAGE view
1905 -- not tracked by PostgreSQL
1910 * TRIGGER_TABLE_USAGE view
1913 -- not tracked by PostgreSQL
1921 CREATE VIEW triggers AS
1922 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1923 CAST(n.nspname AS sql_identifier) AS trigger_schema,
1924 CAST(t.tgname AS sql_identifier) AS trigger_name,
1925 CAST(em.text AS character_data) AS event_manipulation,
1926 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1927 CAST(n.nspname AS sql_identifier) AS event_object_schema,
1928 CAST(c.relname AS sql_identifier) AS event_object_table,
1929 CAST(null AS cardinal_number) AS action_order,
1930 CAST(null AS character_data) AS action_condition,
1932 substring(pg_get_triggerdef(t.oid) from
1933 position('EXECUTE PROCEDURE' in substring(pg_get_triggerdef(t.oid) from 48)) + 47)
1934 AS character_data) AS action_statement,
1936 CASE WHEN t.tgtype & 1 = 1 THEN 'ROW' ELSE 'STATEMENT' END
1937 AS character_data) AS action_orientation,
1939 CASE WHEN t.tgtype & 2 = 2 THEN 'BEFORE' ELSE 'AFTER' END
1940 AS character_data) AS condition_timing,
1941 CAST(null AS sql_identifier) AS condition_reference_old_table,
1942 CAST(null AS sql_identifier) AS condition_reference_new_table,
1943 CAST(null AS sql_identifier) AS condition_reference_old_row,
1944 CAST(null AS sql_identifier) AS condition_reference_new_row,
1945 CAST(null AS time_stamp) AS created
1947 FROM pg_namespace n, pg_class c, pg_trigger t,
1948 (SELECT 4, 'INSERT' UNION ALL
1949 SELECT 8, 'DELETE' UNION ALL
1950 SELECT 16, 'UPDATE') AS em (num, text)
1952 WHERE n.oid = c.relnamespace
1953 AND c.oid = t.tgrelid
1954 AND t.tgtype & em.num <> 0
1955 AND NOT t.tgisconstraint
1956 AND (NOT pg_is_other_temp_schema(n.oid))
1957 AND (pg_has_role(c.relowner, 'USAGE')
1958 -- SELECT privilege omitted, per SQL standard
1959 OR has_table_privilege(c.oid, 'INSERT')
1960 OR has_table_privilege(c.oid, 'UPDATE')
1961 OR has_table_privilege(c.oid, 'DELETE')
1962 OR has_table_privilege(c.oid, 'REFERENCES')
1963 OR has_table_privilege(c.oid, 'TRIGGER') );
1965 GRANT SELECT ON triggers TO PUBLIC;
1970 * UDT_PRIVILEGES view
1973 -- feature not supported
1978 * USAGE_PRIVILEGES view
1981 -- Of the things currently implemented in PostgreSQL, usage privileges
1982 -- apply only to domains. Since domains have no real privileges, we
1983 -- represent all domains with implicit usage privilege here.
1985 CREATE VIEW usage_privileges AS
1986 SELECT CAST(u.rolname AS sql_identifier) AS grantor,
1987 CAST('PUBLIC' AS sql_identifier) AS grantee,
1988 CAST(current_database() AS sql_identifier) AS object_catalog,
1989 CAST(n.nspname AS sql_identifier) AS object_schema,
1990 CAST(t.typname AS sql_identifier) AS object_name,
1991 CAST('DOMAIN' AS character_data) AS object_type,
1992 CAST('USAGE' AS character_data) AS privilege_type,
1993 CAST('NO' AS character_data) AS is_grantable
1999 WHERE u.oid = t.typowner
2000 AND t.typnamespace = n.oid
2001 AND t.typtype = 'd';
2003 GRANT SELECT ON usage_privileges TO PUBLIC;
2008 * USER_DEFINED_TYPES view
2011 -- feature not supported
2019 CREATE VIEW view_column_usage AS
2021 CAST(current_database() AS sql_identifier) AS view_catalog,
2022 CAST(nv.nspname AS sql_identifier) AS view_schema,
2023 CAST(v.relname AS sql_identifier) AS view_name,
2024 CAST(current_database() AS sql_identifier) AS table_catalog,
2025 CAST(nt.nspname AS sql_identifier) AS table_schema,
2026 CAST(t.relname AS sql_identifier) AS table_name,
2027 CAST(a.attname AS sql_identifier) AS column_name
2029 FROM pg_namespace nv, pg_class v, pg_depend dv,
2030 pg_depend dt, pg_class t, pg_namespace nt,
2033 WHERE nv.oid = v.relnamespace
2035 AND v.oid = dv.refobjid
2036 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2037 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2038 AND dv.deptype = 'i'
2039 AND dv.objid = dt.objid
2040 AND dv.refobjid <> dt.refobjid
2041 AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2042 AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2043 AND dt.refobjid = t.oid
2044 AND t.relnamespace = nt.oid
2045 AND t.relkind IN ('r', 'v')
2046 AND t.oid = a.attrelid
2047 AND dt.refobjsubid = a.attnum
2048 AND pg_has_role(t.relowner, 'USAGE');
2050 GRANT SELECT ON view_column_usage TO PUBLIC;
2055 * VIEW_ROUTINE_USAGE
2058 CREATE VIEW view_routine_usage AS
2060 CAST(current_database() AS sql_identifier) AS table_catalog,
2061 CAST(nv.nspname AS sql_identifier) AS table_schema,
2062 CAST(v.relname AS sql_identifier) AS table_name,
2063 CAST(current_database() AS sql_identifier) AS specific_catalog,
2064 CAST(np.nspname AS sql_identifier) AS specific_schema,
2065 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name
2067 FROM pg_namespace nv, pg_class v, pg_depend dv,
2068 pg_depend dp, pg_proc p, pg_namespace np
2070 WHERE nv.oid = v.relnamespace
2072 AND v.oid = dv.refobjid
2073 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2074 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2075 AND dv.deptype = 'i'
2076 AND dv.objid = dp.objid
2077 AND dp.classid = 'pg_catalog.pg_rewrite'::regclass
2078 AND dp.refclassid = 'pg_catalog.pg_proc'::regclass
2079 AND dp.refobjid = p.oid
2080 AND p.pronamespace = np.oid
2081 AND pg_has_role(p.proowner, 'USAGE');
2083 GRANT SELECT ON view_routine_usage TO PUBLIC;
2091 CREATE VIEW view_table_usage AS
2093 CAST(current_database() AS sql_identifier) AS view_catalog,
2094 CAST(nv.nspname AS sql_identifier) AS view_schema,
2095 CAST(v.relname AS sql_identifier) AS view_name,
2096 CAST(current_database() AS sql_identifier) AS table_catalog,
2097 CAST(nt.nspname AS sql_identifier) AS table_schema,
2098 CAST(t.relname AS sql_identifier) AS table_name
2100 FROM pg_namespace nv, pg_class v, pg_depend dv,
2101 pg_depend dt, pg_class t, pg_namespace nt
2103 WHERE nv.oid = v.relnamespace
2105 AND v.oid = dv.refobjid
2106 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2107 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2108 AND dv.deptype = 'i'
2109 AND dv.objid = dt.objid
2110 AND dv.refobjid <> dt.refobjid
2111 AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2112 AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2113 AND dt.refobjid = t.oid
2114 AND t.relnamespace = nt.oid
2115 AND t.relkind IN ('r', 'v')
2116 AND pg_has_role(t.relowner, 'USAGE');
2118 GRANT SELECT ON view_table_usage TO PUBLIC;
2126 CREATE VIEW views AS
2127 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
2128 CAST(nc.nspname AS sql_identifier) AS table_schema,
2129 CAST(c.relname AS sql_identifier) AS table_name,
2132 CASE WHEN pg_has_role(c.relowner, 'USAGE')
2133 THEN pg_get_viewdef(c.oid)
2135 AS character_data) AS view_definition,
2137 CAST('NONE' AS character_data) AS check_option,
2140 CASE WHEN EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = 2 AND is_instead)
2141 AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = 4 AND is_instead)
2142 THEN 'YES' ELSE 'NO' END
2143 AS character_data) AS is_updatable,
2146 CASE WHEN EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = 3 AND is_instead)
2147 THEN 'YES' ELSE 'NO' END
2148 AS character_data) AS is_insertable_into
2150 FROM pg_namespace nc, pg_class c
2152 WHERE c.relnamespace = nc.oid
2154 AND (NOT pg_is_other_temp_schema(nc.oid))
2155 AND (pg_has_role(c.relowner, 'USAGE')
2156 OR has_table_privilege(c.oid, 'SELECT')
2157 OR has_table_privilege(c.oid, 'INSERT')
2158 OR has_table_privilege(c.oid, 'UPDATE')
2159 OR has_table_privilege(c.oid, 'DELETE')
2160 OR has_table_privilege(c.oid, 'REFERENCES')
2161 OR has_table_privilege(c.oid, 'TRIGGER') );
2163 GRANT SELECT ON views TO PUBLIC;
2166 -- The following views have dependencies that force them to appear out of order.
2170 * DATA_TYPE_PRIVILEGES view
2173 CREATE VIEW data_type_privileges AS
2174 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2175 CAST(x.objschema AS sql_identifier) AS object_schema,
2176 CAST(x.objname AS sql_identifier) AS object_name,
2177 CAST(x.objtype AS character_data) AS object_type,
2178 CAST(x.objdtdid AS sql_identifier) AS dtd_identifier
2182 SELECT udt_schema, udt_name, 'USER-DEFINED TYPE'::text, dtd_identifier FROM attributes
2184 SELECT table_schema, table_name, 'TABLE'::text, dtd_identifier FROM columns
2186 SELECT domain_schema, domain_name, 'DOMAIN'::text, dtd_identifier FROM domains
2188 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM parameters
2190 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM routines
2191 ) AS x (objschema, objname, objtype, objdtdid);
2193 GRANT SELECT ON data_type_privileges TO PUBLIC;
2198 * ELEMENT_TYPES view
2201 CREATE VIEW element_types AS
2202 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2203 CAST(n.nspname AS sql_identifier) AS object_schema,
2204 CAST(x.objname AS sql_identifier) AS object_name,
2205 CAST(x.objtype AS character_data) AS object_type,
2206 CAST(x.objdtdid AS sql_identifier) AS collection_type_identifier,
2208 CASE WHEN nbt.nspname = 'pg_catalog' THEN format_type(bt.oid, null)
2209 ELSE 'USER-DEFINED' END AS character_data) AS data_type,
2211 CAST(null AS cardinal_number) AS character_maximum_length,
2212 CAST(null AS cardinal_number) AS character_octet_length,
2213 CAST(null AS sql_identifier) AS character_set_catalog,
2214 CAST(null AS sql_identifier) AS character_set_schema,
2215 CAST(null AS sql_identifier) AS character_set_name,
2216 CAST(null AS sql_identifier) AS collation_catalog,
2217 CAST(null AS sql_identifier) AS collation_schema,
2218 CAST(null AS sql_identifier) AS collation_name,
2219 CAST(null AS cardinal_number) AS numeric_precision,
2220 CAST(null AS cardinal_number) AS numeric_precision_radix,
2221 CAST(null AS cardinal_number) AS numeric_scale,
2222 CAST(null AS cardinal_number) AS datetime_precision,
2223 CAST(null AS character_data) AS interval_type,
2224 CAST(null AS character_data) AS interval_precision,
2226 CAST(null AS character_data) AS domain_default, -- XXX maybe a bug in the standard
2228 CAST(current_database() AS sql_identifier) AS udt_catalog,
2229 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
2230 CAST(bt.typname AS sql_identifier) AS udt_name,
2232 CAST(null AS sql_identifier) AS scope_catalog,
2233 CAST(null AS sql_identifier) AS scope_schema,
2234 CAST(null AS sql_identifier) AS scope_name,
2236 CAST(null AS cardinal_number) AS maximum_cardinality,
2237 CAST('a' || x.objdtdid AS sql_identifier) AS dtd_identifier
2239 FROM pg_namespace n, pg_type at, pg_namespace nbt, pg_type bt,
2242 SELECT c.relnamespace, CAST(c.relname AS sql_identifier),
2243 'TABLE'::text, a.attnum, a.atttypid
2244 FROM pg_class c, pg_attribute a
2245 WHERE c.oid = a.attrelid
2246 AND c.relkind IN ('r', 'v')
2247 AND attnum > 0 AND NOT attisdropped
2252 SELECT t.typnamespace, CAST(t.typname AS sql_identifier),
2253 'DOMAIN'::text, 1, t.typbasetype
2255 WHERE t.typtype = 'd'
2260 SELECT pronamespace, CAST(proname || '_' || CAST(oid AS text) AS sql_identifier),
2261 'ROUTINE'::text, (ss.x).n, (ss.x).x
2262 FROM (SELECT p.pronamespace, p.proname, p.oid,
2263 _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
2264 FROM pg_proc p) AS ss
2269 SELECT p.pronamespace, CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier),
2270 'ROUTINE'::text, 0, p.prorettype
2273 ) AS x (objschema, objname, objtype, objdtdid, objtypeid)
2275 WHERE n.oid = x.objschema
2276 AND at.oid = x.objtypeid
2277 AND (at.typelem <> 0 AND at.typlen = -1)
2278 AND at.typelem = bt.oid
2279 AND nbt.oid = bt.typnamespace
2281 AND (n.nspname, x.objname, x.objtype, x.objdtdid) IN
2282 ( SELECT object_schema, object_name, object_type, dtd_identifier
2283 FROM data_type_privileges );
2285 GRANT SELECT ON element_types TO PUBLIC;