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.33 2006/04/02 17:38:13 petere 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
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 (pg_has_role(r.relowner, 'USAGE')
937 OR has_table_privilege(c.oid, 'SELECT')
938 OR has_table_privilege(c.oid, 'INSERT')
939 OR has_table_privilege(c.oid, 'UPDATE')
940 OR has_table_privilege(c.oid, 'REFERENCES')) ) AS ss
941 WHERE ss.oid = a.attrelid
942 AND a.attnum = (ss.x).x
943 AND NOT a.attisdropped;
945 GRANT SELECT ON key_column_usage TO PUBLIC;
950 * METHOD_SPECIFICATION_PARAMETERS view
953 -- feature not supported
958 * METHOD_SPECIFICATIONS view
961 -- feature not supported
969 CREATE VIEW parameters AS
970 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
971 CAST(n_nspname AS sql_identifier) AS specific_schema,
972 CAST(proname || '_' || CAST(p_oid AS text) AS sql_identifier) AS specific_name,
973 CAST((ss.x).n AS cardinal_number) AS ordinal_position,
975 CASE WHEN proargmodes IS NULL THEN 'IN'
976 WHEN proargmodes[(ss.x).n] = 'i' THEN 'IN'
977 WHEN proargmodes[(ss.x).n] = 'o' THEN 'OUT'
978 WHEN proargmodes[(ss.x).n] = 'b' THEN 'INOUT'
979 END AS character_data) AS parameter_mode,
980 CAST('NO' AS character_data) AS is_result,
981 CAST('NO' AS character_data) AS as_locator,
982 CAST(NULLIF(proargnames[(ss.x).n], '') AS sql_identifier) AS parameter_name,
984 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
985 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
986 ELSE 'USER-DEFINED' END AS character_data)
988 CAST(null AS cardinal_number) AS character_maximum_length,
989 CAST(null AS cardinal_number) AS character_octet_length,
990 CAST(null AS sql_identifier) AS character_set_catalog,
991 CAST(null AS sql_identifier) AS character_set_schema,
992 CAST(null AS sql_identifier) AS character_set_name,
993 CAST(null AS sql_identifier) AS collation_catalog,
994 CAST(null AS sql_identifier) AS collation_schema,
995 CAST(null AS sql_identifier) AS collation_name,
996 CAST(null AS cardinal_number) AS numeric_precision,
997 CAST(null AS cardinal_number) AS numeric_precision_radix,
998 CAST(null AS cardinal_number) AS numeric_scale,
999 CAST(null AS cardinal_number) AS datetime_precision,
1000 CAST(null AS character_data) AS interval_type,
1001 CAST(null AS character_data) AS interval_precision,
1002 CAST(current_database() AS sql_identifier) AS udt_catalog,
1003 CAST(nt.nspname AS sql_identifier) AS udt_schema,
1004 CAST(t.typname AS sql_identifier) AS udt_name,
1005 CAST(null AS sql_identifier) AS scope_catalog,
1006 CAST(null AS sql_identifier) AS scope_schema,
1007 CAST(null AS sql_identifier) AS scope_name,
1008 CAST(null AS cardinal_number) AS maximum_cardinality,
1009 CAST((ss.x).n AS sql_identifier) AS dtd_identifier
1011 FROM pg_type t, pg_namespace nt,
1012 (SELECT n.nspname AS n_nspname, p.proname, p.oid AS p_oid,
1013 p.proargnames, p.proargmodes,
1014 _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
1015 FROM pg_namespace n, pg_proc p
1016 WHERE n.oid = p.pronamespace
1017 AND (pg_has_role(p.proowner, 'USAGE') OR
1018 has_function_privilege(p.oid, 'EXECUTE'))) AS ss
1019 WHERE t.oid = (ss.x).x AND t.typnamespace = nt.oid;
1021 GRANT SELECT ON parameters TO PUBLIC;
1026 * REFERENCED_TYPES view
1029 -- feature not supported
1034 * REFERENTIAL_CONSTRAINTS view
1037 CREATE VIEW referential_constraints AS
1038 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1039 CAST(ncon.nspname AS sql_identifier) AS constraint_schema,
1040 CAST(con.conname AS sql_identifier) AS constraint_name,
1042 CASE WHEN npkc.nspname IS NULL THEN NULL
1043 ELSE current_database() END
1044 AS sql_identifier) AS unique_constraint_catalog,
1045 CAST(npkc.nspname AS sql_identifier) AS unique_constraint_schema,
1046 CAST(pkc.conname AS sql_identifier) AS unique_constraint_name,
1049 CASE con.confmatchtype WHEN 'f' THEN 'FULL'
1050 WHEN 'p' THEN 'PARTIAL'
1051 WHEN 'u' THEN 'NONE' END
1052 AS character_data) AS match_option,
1055 CASE con.confupdtype WHEN 'c' THEN 'CASCADE'
1056 WHEN 'n' THEN 'SET NULL'
1057 WHEN 'd' THEN 'SET DEFAULT'
1058 WHEN 'r' THEN 'RESTRICT'
1059 WHEN 'a' THEN 'NO ACTION' END
1060 AS character_data) AS update_rule,
1063 CASE con.confdeltype WHEN 'c' THEN 'CASCADE'
1064 WHEN 'n' THEN 'SET NULL'
1065 WHEN 'd' THEN 'SET DEFAULT'
1066 WHEN 'r' THEN 'RESTRICT'
1067 WHEN 'a' THEN 'NO ACTION' END
1068 AS character_data) AS delete_rule
1070 FROM (pg_namespace ncon
1071 INNER JOIN pg_constraint con ON ncon.oid = con.connamespace
1072 INNER JOIN pg_class c ON con.conrelid = c.oid)
1075 INNER JOIN pg_namespace npkc ON pkc.connamespace = npkc.oid)
1076 ON con.confrelid = pkc.conrelid
1077 AND _pg_keysequal(con.confkey, pkc.conkey)
1079 WHERE c.relkind = 'r'
1080 AND con.contype = 'f'
1081 AND (pkc.contype IN ('p', 'u') OR pkc.contype IS NULL)
1082 AND pg_has_role(c.relowner, 'USAGE');
1084 GRANT SELECT ON referential_constraints TO PUBLIC;
1089 * ROLE_COLUMN_GRANTS view
1092 CREATE VIEW role_column_grants AS
1093 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1094 CAST(g_grantee.rolname AS sql_identifier) AS grantee,
1095 CAST(current_database() AS sql_identifier) AS table_catalog,
1096 CAST(nc.nspname AS sql_identifier) AS table_schema,
1097 CAST(c.relname AS sql_identifier) AS table_name,
1098 CAST(a.attname AS sql_identifier) AS column_name,
1099 CAST(pr.type AS character_data) AS privilege_type,
1101 CASE WHEN aclcontains(c.relacl,
1102 makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, true))
1103 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
1105 FROM pg_attribute a,
1108 pg_authid u_grantor,
1109 pg_authid g_grantee,
1110 (SELECT 'SELECT' UNION ALL
1111 SELECT 'INSERT' UNION ALL
1112 SELECT 'UPDATE' UNION ALL
1113 SELECT 'REFERENCES') AS pr (type)
1115 WHERE a.attrelid = c.oid
1116 AND c.relnamespace = nc.oid
1118 AND NOT a.attisdropped
1119 AND c.relkind IN ('r', 'v')
1120 AND aclcontains(c.relacl,
1121 makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, false))
1122 AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
1123 OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));
1125 GRANT SELECT ON role_column_grants TO PUBLIC;
1130 * ROLE_ROUTINE_GRANTS view
1133 CREATE VIEW role_routine_grants AS
1134 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1135 CAST(g_grantee.rolname AS sql_identifier) AS grantee,
1136 CAST(current_database() AS sql_identifier) AS specific_catalog,
1137 CAST(n.nspname AS sql_identifier) AS specific_schema,
1138 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1139 CAST(current_database() AS sql_identifier) AS routine_catalog,
1140 CAST(n.nspname AS sql_identifier) AS routine_schema,
1141 CAST(p.proname AS sql_identifier) AS routine_name,
1142 CAST('EXECUTE' AS character_data) AS privilege_type,
1144 CASE WHEN aclcontains(p.proacl,
1145 makeaclitem(g_grantee.oid, u_grantor.oid, 'EXECUTE', true))
1146 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
1150 pg_authid u_grantor,
1153 WHERE p.pronamespace = n.oid
1154 AND aclcontains(p.proacl,
1155 makeaclitem(g_grantee.oid, u_grantor.oid, 'EXECUTE', false))
1156 AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
1157 OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));
1159 GRANT SELECT ON role_routine_grants TO PUBLIC;
1164 * ROLE_TABLE_GRANTS view
1167 CREATE VIEW role_table_grants AS
1168 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1169 CAST(g_grantee.rolname AS sql_identifier) AS grantee,
1170 CAST(current_database() AS sql_identifier) AS table_catalog,
1171 CAST(nc.nspname AS sql_identifier) AS table_schema,
1172 CAST(c.relname AS sql_identifier) AS table_name,
1173 CAST(pr.type AS character_data) AS privilege_type,
1175 CASE WHEN aclcontains(c.relacl,
1176 makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, true))
1177 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable,
1178 CAST('NO' AS character_data) AS with_hierarchy
1182 pg_authid u_grantor,
1183 pg_authid g_grantee,
1184 (SELECT 'SELECT' UNION ALL
1185 SELECT 'DELETE' UNION ALL
1186 SELECT 'INSERT' UNION ALL
1187 SELECT 'UPDATE' UNION ALL
1188 SELECT 'REFERENCES' UNION ALL
1189 SELECT 'RULE' UNION ALL
1190 SELECT 'TRIGGER') AS pr (type)
1192 WHERE c.relnamespace = nc.oid
1193 AND c.relkind IN ('r', 'v')
1194 AND aclcontains(c.relacl,
1195 makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, false))
1196 AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
1197 OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));
1199 GRANT SELECT ON role_table_grants TO PUBLIC;
1204 * ROLE_TABLE_METHOD_GRANTS view
1207 -- feature not supported
1212 * ROLE_USAGE_GRANTS view
1215 -- See USAGE_PRIVILEGES.
1217 CREATE VIEW role_usage_grants AS
1218 SELECT CAST(null AS sql_identifier) AS grantor,
1219 CAST(null AS sql_identifier) AS grantee,
1220 CAST(current_database() AS sql_identifier) AS object_catalog,
1221 CAST(null AS sql_identifier) AS object_schema,
1222 CAST(null AS sql_identifier) AS object_name,
1223 CAST(null AS character_data) AS object_type,
1224 CAST('USAGE' AS character_data) AS privilege_type,
1225 CAST(null AS character_data) AS is_grantable
1229 GRANT SELECT ON role_usage_grants TO PUBLIC;
1234 * ROLE_UDT_GRANTS view
1237 -- feature not supported
1242 * ROUTINE_COLUMN_USAGE view
1245 -- not tracked by PostgreSQL
1250 * ROUTINE_PRIVILEGES view
1253 CREATE VIEW routine_privileges AS
1254 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1255 CAST(grantee.rolname AS sql_identifier) AS grantee,
1256 CAST(current_database() AS sql_identifier) AS specific_catalog,
1257 CAST(n.nspname AS sql_identifier) AS specific_schema,
1258 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1259 CAST(current_database() AS sql_identifier) AS routine_catalog,
1260 CAST(n.nspname AS sql_identifier) AS routine_schema,
1261 CAST(p.proname AS sql_identifier) AS routine_name,
1262 CAST('EXECUTE' AS character_data) AS privilege_type,
1264 CASE WHEN aclcontains(p.proacl,
1265 makeaclitem(grantee.oid, u_grantor.oid, 'EXECUTE', true))
1266 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
1270 pg_authid u_grantor,
1272 SELECT oid, rolname FROM pg_authid
1274 SELECT 0::oid, 'PUBLIC'
1275 ) AS grantee (oid, rolname)
1277 WHERE p.pronamespace = n.oid
1278 AND aclcontains(p.proacl,
1279 makeaclitem(grantee.oid, u_grantor.oid, 'EXECUTE', false))
1280 AND (pg_has_role(u_grantor.oid, 'USAGE')
1281 OR pg_has_role(grantee.oid, 'USAGE')
1282 OR grantee.rolname = 'PUBLIC');
1284 GRANT SELECT ON routine_privileges TO PUBLIC;
1289 * ROUTINE_ROUTINE_USAGE view
1292 -- not tracked by PostgreSQL
1297 * ROUTINE_SEQUENCE_USAGE view
1300 -- not tracked by PostgreSQL
1305 * ROUTINE_TABLE_USAGE view
1308 -- not tracked by PostgreSQL
1316 CREATE VIEW routines AS
1317 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
1318 CAST(n.nspname AS sql_identifier) AS specific_schema,
1319 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1320 CAST(current_database() AS sql_identifier) AS routine_catalog,
1321 CAST(n.nspname AS sql_identifier) AS routine_schema,
1322 CAST(p.proname AS sql_identifier) AS routine_name,
1323 CAST('FUNCTION' AS character_data) AS routine_type,
1324 CAST(null AS sql_identifier) AS module_catalog,
1325 CAST(null AS sql_identifier) AS module_schema,
1326 CAST(null AS sql_identifier) AS module_name,
1327 CAST(null AS sql_identifier) AS udt_catalog,
1328 CAST(null AS sql_identifier) AS udt_schema,
1329 CAST(null AS sql_identifier) AS udt_name,
1332 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
1333 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
1334 ELSE 'USER-DEFINED' END AS character_data)
1336 CAST(null AS cardinal_number) AS character_maximum_length,
1337 CAST(null AS cardinal_number) AS character_octet_length,
1338 CAST(null AS sql_identifier) AS character_set_catalog,
1339 CAST(null AS sql_identifier) AS character_set_schema,
1340 CAST(null AS sql_identifier) AS character_set_name,
1341 CAST(null AS sql_identifier) AS collation_catalog,
1342 CAST(null AS sql_identifier) AS collation_schema,
1343 CAST(null AS sql_identifier) AS collation_name,
1344 CAST(null AS cardinal_number) AS numeric_precision,
1345 CAST(null AS cardinal_number) AS numeric_precision_radix,
1346 CAST(null AS cardinal_number) AS numeric_scale,
1347 CAST(null AS cardinal_number) AS datetime_precision,
1348 CAST(null AS character_data) AS interval_type,
1349 CAST(null AS character_data) AS interval_precision,
1350 CAST(current_database() AS sql_identifier) AS type_udt_catalog,
1351 CAST(nt.nspname AS sql_identifier) AS type_udt_schema,
1352 CAST(t.typname AS sql_identifier) AS type_udt_name,
1353 CAST(null AS sql_identifier) AS scope_catalog,
1354 CAST(null AS sql_identifier) AS scope_schema,
1355 CAST(null AS sql_identifier) AS scope_name,
1356 CAST(null AS cardinal_number) AS maximum_cardinality,
1357 CAST(0 AS sql_identifier) AS dtd_identifier,
1359 CAST(CASE WHEN l.lanname = 'sql' THEN 'SQL' ELSE 'EXTERNAL' END AS character_data)
1362 CASE WHEN pg_has_role(p.proowner, 'USAGE') THEN p.prosrc ELSE null END
1363 AS character_data) AS routine_definition,
1365 CASE WHEN l.lanname = 'c' THEN p.prosrc ELSE null END
1366 AS character_data) AS external_name,
1367 CAST(upper(l.lanname) AS character_data) AS external_language,
1369 CAST('GENERAL' AS character_data) AS parameter_style,
1370 CAST(CASE WHEN p.provolatile = 'i' THEN 'YES' ELSE 'NO' END AS character_data) AS is_deterministic,
1371 CAST('MODIFIES' AS character_data) AS sql_data_access,
1372 CAST(CASE WHEN p.proisstrict THEN 'YES' ELSE 'NO' END AS character_data) AS is_null_call,
1373 CAST(null AS character_data) AS sql_path,
1374 CAST('YES' AS character_data) AS schema_level_routine,
1375 CAST(0 AS cardinal_number) AS max_dynamic_result_sets,
1376 CAST(null AS character_data) AS is_user_defined_cast,
1377 CAST(null AS character_data) AS is_implicitly_invocable,
1378 CAST(CASE WHEN p.prosecdef THEN 'DEFINER' ELSE 'INVOKER' END AS character_data) AS security_type,
1379 CAST(null AS sql_identifier) AS to_sql_specific_catalog,
1380 CAST(null AS sql_identifier) AS to_sql_specific_schema,
1381 CAST(null AS sql_identifier) AS to_sql_specific_name,
1382 CAST('NO' AS character_data) AS as_locator,
1383 CAST(null AS time_stamp) AS created,
1384 CAST(null AS time_stamp) AS last_altered,
1385 CAST(null AS character_data) AS new_savepoint_level,
1386 CAST('YES' AS character_data) AS is_udt_dependent, -- FIXME?
1388 CAST(null AS character_data) AS result_cast_from_data_type,
1389 CAST(null AS character_data) AS result_cast_as_locator,
1390 CAST(null AS cardinal_number) AS result_cast_char_max_length,
1391 CAST(null AS cardinal_number) AS result_cast_char_octet_length,
1392 CAST(null AS sql_identifier) AS result_cast_char_set_catalog,
1393 CAST(null AS sql_identifier) AS result_cast_char_set_schema,
1394 CAST(null AS sql_identifier) AS result_cast_character_set_name,
1395 CAST(null AS sql_identifier) AS result_cast_collation_catalog,
1396 CAST(null AS sql_identifier) AS result_cast_collation_schema,
1397 CAST(null AS sql_identifier) AS result_cast_collation_name,
1398 CAST(null AS cardinal_number) AS result_cast_numeric_precision,
1399 CAST(null AS cardinal_number) AS result_cast_numeric_precision_radix,
1400 CAST(null AS cardinal_number) AS result_cast_numeric_scale,
1401 CAST(null AS cardinal_number) AS result_cast_datetime_precision,
1402 CAST(null AS character_data) AS result_cast_interval_type,
1403 CAST(null AS character_data) AS result_cast_interval_precision,
1404 CAST(null AS sql_identifier) AS result_cast_type_udt_catalog,
1405 CAST(null AS sql_identifier) AS result_cast_type_udt_schema,
1406 CAST(null AS sql_identifier) AS result_cast_type_udt_name,
1407 CAST(null AS sql_identifier) AS result_cast_scope_catalog,
1408 CAST(null AS sql_identifier) AS result_cast_scope_schema,
1409 CAST(null AS sql_identifier) AS result_cast_scope_name,
1410 CAST(null AS cardinal_number) AS result_cast_maximum_cardinality,
1411 CAST(null AS sql_identifier) AS result_cast_dtd_identifier
1413 FROM pg_namespace n, pg_proc p, pg_language l,
1414 pg_type t, pg_namespace nt
1416 WHERE n.oid = p.pronamespace AND p.prolang = l.oid
1417 AND p.prorettype = t.oid AND t.typnamespace = nt.oid
1418 AND (pg_has_role(p.proowner, 'USAGE')
1419 OR has_function_privilege(p.oid, 'EXECUTE'));
1421 GRANT SELECT ON routines TO PUBLIC;
1429 CREATE VIEW schemata AS
1430 SELECT CAST(current_database() AS sql_identifier) AS catalog_name,
1431 CAST(n.nspname AS sql_identifier) AS schema_name,
1432 CAST(u.rolname AS sql_identifier) AS schema_owner,
1433 CAST(null AS sql_identifier) AS default_character_set_catalog,
1434 CAST(null AS sql_identifier) AS default_character_set_schema,
1435 CAST(null AS sql_identifier) AS default_character_set_name,
1436 CAST(null AS character_data) AS sql_path
1437 FROM pg_namespace n, pg_authid u
1438 WHERE n.nspowner = u.oid AND pg_has_role(n.nspowner, 'USAGE');
1440 GRANT SELECT ON schemata TO PUBLIC;
1448 CREATE VIEW sequences AS
1449 SELECT CAST(current_database() AS sql_identifier) AS sequence_catalog,
1450 CAST(nc.nspname AS sql_identifier) AS sequence_schema,
1451 CAST(c.relname AS sql_identifier) AS sequence_name,
1452 CAST('bigint' AS character_data) AS data_type,
1453 CAST(64 AS cardinal_number) AS numeric_precision,
1454 CAST(2 AS cardinal_number) AS numeric_precision_radix,
1455 CAST(0 AS cardinal_number) AS numeric_scale,
1456 CAST(null AS cardinal_number) AS maximum_value, -- FIXME
1457 CAST(null AS cardinal_number) AS minimum_value, -- FIXME
1458 CAST(null AS cardinal_number) AS increment, -- FIXME
1459 CAST(null AS character_data) AS cycle_option -- FIXME
1460 FROM pg_namespace nc, pg_class c
1461 WHERE c.relnamespace = nc.oid
1463 AND (pg_has_role(c.relowner, 'USAGE')
1464 OR has_table_privilege(c.oid, 'SELECT')
1465 OR has_table_privilege(c.oid, 'UPDATE') );
1467 GRANT SELECT ON sequences TO PUBLIC;
1472 * SQL_FEATURES table
1475 CREATE TABLE sql_features (
1476 feature_id character_data,
1477 feature_name character_data,
1478 sub_feature_id character_data,
1479 sub_feature_name character_data,
1480 is_supported character_data,
1481 is_verified_by character_data,
1482 comments character_data
1485 -- Will be filled with external data by initdb.
1487 GRANT SELECT ON sql_features TO PUBLIC;
1492 * SQL_IMPLEMENTATION_INFO table
1495 -- Note: Implementation information items are defined in ISO/IEC 9075-3:2003,
1498 CREATE TABLE sql_implementation_info (
1499 implementation_info_id character_data,
1500 implementation_info_name character_data,
1501 integer_value cardinal_number,
1502 character_value character_data,
1503 comments character_data
1506 INSERT INTO sql_implementation_info VALUES ('10003', 'CATALOG NAME', NULL, 'Y', NULL);
1507 INSERT INTO sql_implementation_info VALUES ('10004', 'COLLATING SEQUENCE', NULL, '', 'not supported');
1508 INSERT INTO sql_implementation_info VALUES ('23', 'CURSOR COMMIT BEHAVIOR', 1, NULL, 'close cursors and retain prepared statements');
1509 INSERT INTO sql_implementation_info VALUES ('2', 'DATA SOURCE NAME', NULL, '', NULL);
1510 INSERT INTO sql_implementation_info VALUES ('17', 'DBMS NAME', NULL, (select trim(trailing ' ' from substring(version() from '^[^0-9]*'))), NULL);
1511 INSERT INTO sql_implementation_info VALUES ('18', 'DBMS VERSION', NULL, '???', NULL); -- filled by initdb
1512 INSERT INTO sql_implementation_info VALUES ('26', 'DEFAULT TRANSACTION ISOLATION', 2, NULL, 'READ COMMITTED; user-settable');
1513 INSERT INTO sql_implementation_info VALUES ('28', 'IDENTIFIER CASE', 3, NULL, 'stored in mixed case - case sensitive');
1514 INSERT INTO sql_implementation_info VALUES ('85', 'NULL COLLATION', 0, NULL, 'nulls higher than non-nulls');
1515 INSERT INTO sql_implementation_info VALUES ('13', 'SERVER NAME', NULL, '', NULL);
1516 INSERT INTO sql_implementation_info VALUES ('94', 'SPECIAL CHARACTERS', NULL, '', 'all non-ASCII characters allowed');
1517 INSERT INTO sql_implementation_info VALUES ('46', 'TRANSACTION CAPABLE', 2, NULL, 'both DML and DDL');
1519 GRANT SELECT ON sql_implementation_info TO PUBLIC;
1524 * SQL_LANGUAGES table
1527 CREATE TABLE sql_languages (
1528 sql_language_source character_data,
1529 sql_language_year character_data,
1530 sql_language_conformance character_data,
1531 sql_language_integrity character_data,
1532 sql_language_implementation character_data,
1533 sql_language_binding_style character_data,
1534 sql_language_programming_language character_data
1537 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'DIRECT', NULL);
1538 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
1539 INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'DIRECT', NULL);
1540 INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
1542 GRANT SELECT ON sql_languages TO PUBLIC;
1547 * SQL_PACKAGES table
1550 CREATE TABLE sql_packages (
1551 feature_id character_data,
1552 feature_name character_data,
1553 is_supported character_data,
1554 is_verified_by character_data,
1555 comments character_data
1558 INSERT INTO sql_packages VALUES ('PKG000', 'Core', 'NO', NULL, '');
1559 INSERT INTO sql_packages VALUES ('PKG001', 'Enhanced datetime facilities', 'YES', NULL, '');
1560 INSERT INTO sql_packages VALUES ('PKG002', 'Enhanced integrity management', 'NO', NULL, '');
1561 INSERT INTO sql_packages VALUES ('PKG003', 'OLAP facilities', 'NO', NULL, '');
1562 INSERT INTO sql_packages VALUES ('PKG004', 'PSM', 'NO', NULL, 'PL/pgSQL is similar.');
1563 INSERT INTO sql_packages VALUES ('PKG005', 'CLI', 'NO', NULL, 'ODBC is similar.');
1564 INSERT INTO sql_packages VALUES ('PKG006', 'Basic object support', 'NO', NULL, '');
1565 INSERT INTO sql_packages VALUES ('PKG007', 'Enhanced object support', 'NO', NULL, '');
1566 INSERT INTO sql_packages VALUES ('PKG008', 'Active database', 'NO', NULL, '');
1567 INSERT INTO sql_packages VALUES ('PKG010', 'OLAP', 'NO', NULL, 'NO');
1569 GRANT SELECT ON sql_packages TO PUBLIC;
1577 CREATE TABLE sql_parts (
1578 feature_id character_data,
1579 feature_name character_data,
1580 is_supported character_data,
1581 is_verified_by character_data,
1582 comments character_data
1585 INSERT INTO sql_parts VALUES ('1', 'Framework (SQL/Framework)', 'NO', NULL, '');
1586 INSERT INTO sql_parts VALUES ('2', 'Foundation (SQL/Foundation)', 'NO', NULL, '');
1587 INSERT INTO sql_parts VALUES ('3', 'Call-Level Interface (SQL/CLI)', 'NO', NULL, '');
1588 INSERT INTO sql_parts VALUES ('4', 'Persistent Stored Modules (SQL/PSM)', NULL, '');
1589 INSERT INTO sql_parts VALUES ('9', 'Management of External Data (SQL/MED)', NULL, '');
1590 INSERT INTO sql_parts VALUES ('10', 'Object Language Bindings (SQL/OLB)', NULL, '');
1591 INSERT INTO sql_parts VALUES ('11', 'Information and Definition Schema (SQL/Schemata)', NULL, '');
1592 INSERT INTO sql_parts VALUES ('13', 'Routines and Types Using the Java Programming Language (SQL/JRT)', NULL, '');
1593 INSERT INTO sql_parts VALUES ('14', 'XML-Related Specifications (SQL/XML)', NULL, '');
1601 -- Note: Sizing items are defined in ISO/IEC 9075-3:2003, clause 7.2.
1603 CREATE TABLE sql_sizing (
1604 sizing_id cardinal_number,
1605 sizing_name character_data,
1606 supported_value cardinal_number,
1607 comments character_data
1610 INSERT INTO sql_sizing VALUES (34, 'MAXIMUM CATALOG NAME LENGTH', 63, NULL);
1611 INSERT INTO sql_sizing VALUES (30, 'MAXIMUM COLUMN NAME LENGTH', 63, NULL);
1612 INSERT INTO sql_sizing VALUES (97, 'MAXIMUM COLUMNS IN GROUP BY', 0, NULL);
1613 INSERT INTO sql_sizing VALUES (99, 'MAXIMUM COLUMNS IN ORDER BY', 0, NULL);
1614 INSERT INTO sql_sizing VALUES (100, 'MAXIMUM COLUMNS IN SELECT', 1664, NULL); -- match MaxTupleAttributeNumber
1615 INSERT INTO sql_sizing VALUES (101, 'MAXIMUM COLUMNS IN TABLE', 1600, NULL); -- match MaxHeapAttributeNumber
1616 INSERT INTO sql_sizing VALUES (1, 'MAXIMUM CONCURRENT ACTIVITIES', 0, NULL);
1617 INSERT INTO sql_sizing VALUES (31, 'MAXIMUM CURSOR NAME LENGTH', 63, NULL);
1618 INSERT INTO sql_sizing VALUES (0, 'MAXIMUM DRIVER CONNECTIONS', NULL, NULL);
1619 INSERT INTO sql_sizing VALUES (10005, 'MAXIMUM IDENTIFIER LENGTH', 63, NULL);
1620 INSERT INTO sql_sizing VALUES (32, 'MAXIMUM SCHEMA NAME LENGTH', 63, NULL);
1621 INSERT INTO sql_sizing VALUES (20000, 'MAXIMUM STATEMENT OCTETS', 0, NULL);
1622 INSERT INTO sql_sizing VALUES (20001, 'MAXIMUM STATEMENT OCTETS DATA', 0, NULL);
1623 INSERT INTO sql_sizing VALUES (20002, 'MAXIMUM STATEMENT OCTETS SCHEMA', 0, NULL);
1624 INSERT INTO sql_sizing VALUES (35, 'MAXIMUM TABLE NAME LENGTH', 63, NULL);
1625 INSERT INTO sql_sizing VALUES (106, 'MAXIMUM TABLES IN SELECT', 0, NULL);
1626 INSERT INTO sql_sizing VALUES (107, 'MAXIMUM USER NAME LENGTH', 63, NULL);
1627 INSERT INTO sql_sizing VALUES (25000, 'MAXIMUM CURRENT DEFAULT TRANSFORM GROUP LENGTH', NULL, NULL);
1628 INSERT INTO sql_sizing VALUES (25001, 'MAXIMUM CURRENT TRANSFORM GROUP LENGTH', NULL, NULL);
1629 INSERT INTO sql_sizing VALUES (25002, 'MAXIMUM CURRENT PATH LENGTH', 0, NULL);
1630 INSERT INTO sql_sizing VALUES (25003, 'MAXIMUM CURRENT ROLE LENGTH', NULL, NULL);
1631 INSERT INTO sql_sizing VALUES (25004, 'MAXIMUM SESSION USER LENGTH', 63, NULL);
1632 INSERT INTO sql_sizing VALUES (25005, 'MAXIMUM SYSTEM USER LENGTH', 63, NULL);
1635 SET supported_value = (SELECT typlen-1 FROM pg_catalog.pg_type WHERE typname = 'name'),
1636 comments = 'Might be less, depending on character set.'
1637 WHERE supported_value = 63;
1639 GRANT SELECT ON sql_sizing TO PUBLIC;
1644 * SQL_SIZING_PROFILES table
1647 -- The data in this table are defined by various profiles of SQL.
1648 -- Since we don't have any information about such profiles, we provide
1651 CREATE TABLE sql_sizing_profiles (
1652 sizing_id cardinal_number,
1653 sizing_name character_data,
1654 profile_id character_data,
1655 required_value cardinal_number,
1656 comments character_data
1659 GRANT SELECT ON sql_sizing_profiles TO PUBLIC;
1664 * TABLE_CONSTRAINTS view
1667 CREATE VIEW table_constraints AS
1668 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1669 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
1670 CAST(c.conname AS sql_identifier) AS constraint_name,
1671 CAST(current_database() AS sql_identifier) AS table_catalog,
1672 CAST(nr.nspname AS sql_identifier) AS table_schema,
1673 CAST(r.relname AS sql_identifier) AS table_name,
1675 CASE c.contype WHEN 'c' THEN 'CHECK'
1676 WHEN 'f' THEN 'FOREIGN KEY'
1677 WHEN 'p' THEN 'PRIMARY KEY'
1678 WHEN 'u' THEN 'UNIQUE' END
1679 AS character_data) AS constraint_type,
1680 CAST(CASE WHEN c.condeferrable THEN 'YES' ELSE 'NO' END AS character_data)
1682 CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS character_data)
1683 AS initially_deferred
1685 FROM pg_namespace nc,
1690 WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace
1691 AND c.conrelid = r.oid
1693 AND (pg_has_role(r.relowner, 'USAGE')
1694 -- SELECT privilege omitted, per SQL standard
1695 OR has_table_privilege(r.oid, 'INSERT')
1696 OR has_table_privilege(r.oid, 'UPDATE')
1697 OR has_table_privilege(r.oid, 'DELETE')
1698 OR has_table_privilege(r.oid, 'RULE')
1699 OR has_table_privilege(r.oid, 'REFERENCES')
1700 OR has_table_privilege(r.oid, 'TRIGGER') )
1704 -- not-null constraints
1706 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1707 CAST(nr.nspname AS sql_identifier) AS constraint_schema,
1708 CAST(nr.oid || '_' || r.oid || '_' || a.attnum || '_not_null' AS sql_identifier) AS constraint_name, -- XXX
1709 CAST(current_database() AS sql_identifier) AS table_catalog,
1710 CAST(nr.nspname AS sql_identifier) AS table_schema,
1711 CAST(r.relname AS sql_identifier) AS table_name,
1712 CAST('CHECK' AS character_data) AS constraint_type,
1713 CAST('NO' AS character_data) AS is_deferrable,
1714 CAST('NO' AS character_data) AS initially_deferred
1716 FROM pg_namespace nr,
1720 WHERE nr.oid = r.relnamespace
1721 AND r.oid = a.attrelid
1724 AND NOT a.attisdropped
1726 AND (pg_has_role(r.relowner, 'USAGE')
1727 OR has_table_privilege(r.oid, 'SELECT')
1728 OR has_table_privilege(r.oid, 'INSERT')
1729 OR has_table_privilege(r.oid, 'UPDATE')
1730 OR has_table_privilege(r.oid, 'DELETE')
1731 OR has_table_privilege(r.oid, 'RULE')
1732 OR has_table_privilege(r.oid, 'REFERENCES')
1733 OR has_table_privilege(r.oid, 'TRIGGER') );
1735 GRANT SELECT ON table_constraints TO PUBLIC;
1740 * TABLE_METHOD_PRIVILEGES view
1743 -- feature not supported
1748 * TABLE_PRIVILEGES view
1751 CREATE VIEW table_privileges AS
1752 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1753 CAST(grantee.rolname AS sql_identifier) AS grantee,
1754 CAST(current_database() AS sql_identifier) AS table_catalog,
1755 CAST(nc.nspname AS sql_identifier) AS table_schema,
1756 CAST(c.relname AS sql_identifier) AS table_name,
1757 CAST(pr.type AS character_data) AS privilege_type,
1759 CASE WHEN aclcontains(c.relacl,
1760 makeaclitem(grantee.oid, u_grantor.oid, pr.type, true))
1761 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable,
1762 CAST('NO' AS character_data) AS with_hierarchy
1766 pg_authid u_grantor,
1768 SELECT oid, rolname FROM pg_authid
1770 SELECT 0::oid, 'PUBLIC'
1771 ) AS grantee (oid, rolname),
1772 (SELECT 'SELECT' UNION ALL
1773 SELECT 'DELETE' UNION ALL
1774 SELECT 'INSERT' UNION ALL
1775 SELECT 'UPDATE' UNION ALL
1776 SELECT 'REFERENCES' UNION ALL
1777 SELECT 'RULE' UNION ALL
1778 SELECT 'TRIGGER') AS pr (type)
1780 WHERE c.relnamespace = nc.oid
1781 AND c.relkind IN ('r', 'v')
1782 AND aclcontains(c.relacl,
1783 makeaclitem(grantee.oid, u_grantor.oid, pr.type, false))
1784 AND (pg_has_role(u_grantor.oid, 'USAGE')
1785 OR pg_has_role(grantee.oid, 'USAGE')
1786 OR grantee.rolname = 'PUBLIC');
1788 GRANT SELECT ON table_privileges TO PUBLIC;
1796 CREATE VIEW tables AS
1797 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
1798 CAST(nc.nspname AS sql_identifier) AS table_schema,
1799 CAST(c.relname AS sql_identifier) AS table_name,
1802 CASE WHEN nc.nspname LIKE 'pg!_temp!_%' ESCAPE '!' THEN 'LOCAL TEMPORARY'
1803 WHEN c.relkind = 'r' THEN 'BASE TABLE'
1804 WHEN c.relkind = 'v' THEN 'VIEW'
1806 AS character_data) AS table_type,
1808 CAST(null AS sql_identifier) AS self_referencing_column_name,
1809 CAST(null AS character_data) AS reference_generation,
1811 CAST(null AS sql_identifier) AS user_defined_type_catalog,
1812 CAST(null AS sql_identifier) AS user_defined_type_schema,
1813 CAST(null AS sql_identifier) AS user_defined_type_name,
1815 CAST(CASE WHEN c.relkind = 'r'
1816 THEN 'YES' ELSE 'NO' END AS character_data) AS is_insertable_into,
1817 CAST('NO' AS character_data) AS is_typed,
1819 CASE WHEN nc.nspname LIKE 'pg!_temp!_%' ESCAPE '!' THEN 'PRESERVE'
1821 AS character_data) AS commit_action
1823 FROM pg_namespace nc, pg_class c
1825 WHERE c.relnamespace = nc.oid
1826 AND c.relkind IN ('r', 'v')
1827 AND (pg_has_role(c.relowner, 'USAGE')
1828 OR has_table_privilege(c.oid, 'SELECT')
1829 OR has_table_privilege(c.oid, 'INSERT')
1830 OR has_table_privilege(c.oid, 'UPDATE')
1831 OR has_table_privilege(c.oid, 'DELETE')
1832 OR has_table_privilege(c.oid, 'RULE')
1833 OR has_table_privilege(c.oid, 'REFERENCES')
1834 OR has_table_privilege(c.oid, 'TRIGGER') );
1836 GRANT SELECT ON tables TO PUBLIC;
1844 -- feature not supported
1852 -- feature not supported
1857 * TRIGGERED_UPDATE_COLUMNS view
1860 -- PostgreSQL doesn't allow the specification of individual triggered
1861 -- update columns, so this view is empty.
1863 CREATE VIEW triggered_update_columns AS
1864 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1865 CAST(null AS sql_identifier) AS trigger_schema,
1866 CAST(null AS sql_identifier) AS trigger_name,
1867 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1868 CAST(null AS sql_identifier) AS event_object_schema,
1869 CAST(null AS sql_identifier) AS event_object_table,
1870 CAST(null AS sql_identifier) AS event_object_column
1873 GRANT SELECT ON triggered_update_columns TO PUBLIC;
1878 * TRIGGER_COLUMN_USAGE view
1881 -- not tracked by PostgreSQL
1886 * TRIGGER_ROUTINE_USAGE view
1889 -- not tracked by PostgreSQL
1894 * TRIGGER_SEQUENCE_USAGE view
1897 -- not tracked by PostgreSQL
1902 * TRIGGER_TABLE_USAGE view
1905 -- not tracked by PostgreSQL
1913 CREATE VIEW triggers AS
1914 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1915 CAST(n.nspname AS sql_identifier) AS trigger_schema,
1916 CAST(t.tgname AS sql_identifier) AS trigger_name,
1917 CAST(em.text AS character_data) AS event_manipulation,
1918 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1919 CAST(n.nspname AS sql_identifier) AS event_object_schema,
1920 CAST(c.relname AS sql_identifier) AS event_object_table,
1921 CAST(null AS cardinal_number) AS action_order,
1922 CAST(null AS character_data) AS action_condition,
1924 substring(pg_get_triggerdef(t.oid) from
1925 position('EXECUTE PROCEDURE' in substring(pg_get_triggerdef(t.oid) from 48)) + 47)
1926 AS character_data) AS action_statement,
1928 CASE WHEN t.tgtype & 1 = 1 THEN 'ROW' ELSE 'STATEMENT' END
1929 AS character_data) AS action_orientation,
1931 CASE WHEN t.tgtype & 2 = 2 THEN 'BEFORE' ELSE 'AFTER' END
1932 AS character_data) AS condition_timing,
1933 CAST(null AS sql_identifier) AS condition_reference_old_table,
1934 CAST(null AS sql_identifier) AS condition_reference_new_table,
1935 CAST(null AS sql_identifier) AS condition_reference_old_row,
1936 CAST(null AS sql_identifier) AS condition_reference_new_row,
1937 CAST(null AS time_stamp) AS created
1939 FROM pg_namespace n, pg_class c, pg_trigger t,
1940 (SELECT 4, 'INSERT' UNION ALL
1941 SELECT 8, 'DELETE' UNION ALL
1942 SELECT 16, 'UPDATE') AS em (num, text)
1944 WHERE n.oid = c.relnamespace
1945 AND c.oid = t.tgrelid
1946 AND t.tgtype & em.num <> 0
1947 AND NOT t.tgisconstraint
1948 AND (pg_has_role(c.relowner, 'USAGE')
1949 -- SELECT privilege omitted, per SQL standard
1950 OR has_table_privilege(c.oid, 'INSERT')
1951 OR has_table_privilege(c.oid, 'UPDATE')
1952 OR has_table_privilege(c.oid, 'DELETE')
1953 OR has_table_privilege(c.oid, 'RULE')
1954 OR has_table_privilege(c.oid, 'REFERENCES')
1955 OR has_table_privilege(c.oid, 'TRIGGER') );
1957 GRANT SELECT ON triggers TO PUBLIC;
1962 * UDT_PRIVILEGES view
1965 -- feature not supported
1970 * USAGE_PRIVILEGES view
1973 -- Of the things currently implemented in PostgreSQL, usage privileges
1974 -- apply only to domains. Since domains have no real privileges, we
1975 -- represent all domains with implicit usage privilege here.
1977 CREATE VIEW usage_privileges AS
1978 SELECT CAST(u.rolname AS sql_identifier) AS grantor,
1979 CAST('PUBLIC' AS sql_identifier) AS grantee,
1980 CAST(current_database() AS sql_identifier) AS object_catalog,
1981 CAST(n.nspname AS sql_identifier) AS object_schema,
1982 CAST(t.typname AS sql_identifier) AS object_name,
1983 CAST('DOMAIN' AS character_data) AS object_type,
1984 CAST('USAGE' AS character_data) AS privilege_type,
1985 CAST('NO' AS character_data) AS is_grantable
1991 WHERE u.oid = t.typowner
1992 AND t.typnamespace = n.oid
1993 AND t.typtype = 'd';
1995 GRANT SELECT ON usage_privileges TO PUBLIC;
2000 * USER_DEFINED_TYPES view
2003 -- feature not supported
2011 CREATE VIEW view_column_usage AS
2013 CAST(current_database() AS sql_identifier) AS view_catalog,
2014 CAST(nv.nspname AS sql_identifier) AS view_schema,
2015 CAST(v.relname AS sql_identifier) AS view_name,
2016 CAST(current_database() AS sql_identifier) AS table_catalog,
2017 CAST(nt.nspname AS sql_identifier) AS table_schema,
2018 CAST(t.relname AS sql_identifier) AS table_name,
2019 CAST(a.attname AS sql_identifier) AS column_name
2021 FROM pg_namespace nv, pg_class v, pg_depend dv,
2022 pg_depend dt, pg_class t, pg_namespace nt,
2025 WHERE nv.oid = v.relnamespace
2027 AND v.oid = dv.refobjid
2028 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2029 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2030 AND dv.deptype = 'i'
2031 AND dv.objid = dt.objid
2032 AND dv.refobjid <> dt.refobjid
2033 AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2034 AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2035 AND dt.refobjid = t.oid
2036 AND t.relnamespace = nt.oid
2037 AND t.relkind IN ('r', 'v')
2038 AND t.oid = a.attrelid
2039 AND dt.refobjsubid = a.attnum
2040 AND pg_has_role(t.relowner, 'USAGE');
2042 GRANT SELECT ON view_column_usage TO PUBLIC;
2047 * VIEW_ROUTINE_USAGE
2050 CREATE VIEW view_routine_usage AS
2052 CAST(current_database() AS sql_identifier) AS table_catalog,
2053 CAST(nv.nspname AS sql_identifier) AS table_schema,
2054 CAST(v.relname AS sql_identifier) AS table_name,
2055 CAST(current_database() AS sql_identifier) AS specific_catalog,
2056 CAST(np.nspname AS sql_identifier) AS specific_schema,
2057 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name
2059 FROM pg_namespace nv, pg_class v, pg_depend dv,
2060 pg_depend dp, pg_proc p, pg_namespace np
2062 WHERE nv.oid = v.relnamespace
2064 AND v.oid = dv.refobjid
2065 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2066 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2067 AND dv.deptype = 'i'
2068 AND dv.objid = dp.objid
2069 AND dp.classid = 'pg_catalog.pg_rewrite'::regclass
2070 AND dp.refclassid = 'pg_catalog.pg_proc'::regclass
2071 AND dp.refobjid = p.oid
2072 AND p.pronamespace = np.oid
2073 AND pg_has_role(p.proowner, 'USAGE');
2075 GRANT SELECT ON view_routine_usage TO PUBLIC;
2083 CREATE VIEW view_table_usage AS
2085 CAST(current_database() AS sql_identifier) AS view_catalog,
2086 CAST(nv.nspname AS sql_identifier) AS view_schema,
2087 CAST(v.relname AS sql_identifier) AS view_name,
2088 CAST(current_database() AS sql_identifier) AS table_catalog,
2089 CAST(nt.nspname AS sql_identifier) AS table_schema,
2090 CAST(t.relname AS sql_identifier) AS table_name
2092 FROM pg_namespace nv, pg_class v, pg_depend dv,
2093 pg_depend dt, pg_class t, pg_namespace nt
2095 WHERE nv.oid = v.relnamespace
2097 AND v.oid = dv.refobjid
2098 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2099 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2100 AND dv.deptype = 'i'
2101 AND dv.objid = dt.objid
2102 AND dv.refobjid <> dt.refobjid
2103 AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2104 AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2105 AND dt.refobjid = t.oid
2106 AND t.relnamespace = nt.oid
2107 AND t.relkind IN ('r', 'v')
2108 AND pg_has_role(t.relowner, 'USAGE');
2110 GRANT SELECT ON view_table_usage TO PUBLIC;
2118 CREATE VIEW views AS
2119 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
2120 CAST(nc.nspname AS sql_identifier) AS table_schema,
2121 CAST(c.relname AS sql_identifier) AS table_name,
2124 CASE WHEN pg_has_role(c.relowner, 'USAGE')
2125 THEN pg_get_viewdef(c.oid)
2127 AS character_data) AS view_definition,
2129 CAST('NONE' AS character_data) AS check_option,
2132 CASE WHEN EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = 2 AND is_instead)
2133 AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = 4 AND is_instead)
2134 THEN 'YES' ELSE 'NO' END
2135 AS character_data) AS is_updatable,
2138 CASE WHEN EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = 3 AND is_instead)
2139 THEN 'YES' ELSE 'NO' END
2140 AS character_data) AS is_insertable_into
2142 FROM pg_namespace nc, pg_class c
2144 WHERE c.relnamespace = nc.oid
2146 AND (pg_has_role(c.relowner, 'USAGE')
2147 OR has_table_privilege(c.oid, 'SELECT')
2148 OR has_table_privilege(c.oid, 'INSERT')
2149 OR has_table_privilege(c.oid, 'UPDATE')
2150 OR has_table_privilege(c.oid, 'DELETE')
2151 OR has_table_privilege(c.oid, 'RULE')
2152 OR has_table_privilege(c.oid, 'REFERENCES')
2153 OR has_table_privilege(c.oid, 'TRIGGER') );
2155 GRANT SELECT ON views TO PUBLIC;
2158 -- The following views have dependencies that force them to appear out of order.
2162 * DATA_TYPE_PRIVILEGES view
2165 CREATE VIEW data_type_privileges AS
2166 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2167 CAST(x.objschema AS sql_identifier) AS object_schema,
2168 CAST(x.objname AS sql_identifier) AS object_name,
2169 CAST(x.objtype AS character_data) AS object_type,
2170 CAST(x.objdtdid AS sql_identifier) AS dtd_identifier
2174 SELECT udt_schema, udt_name, 'USER-DEFINED TYPE'::text, dtd_identifier FROM attributes
2176 SELECT table_schema, table_name, 'TABLE'::text, dtd_identifier FROM columns
2178 SELECT domain_schema, domain_name, 'DOMAIN'::text, dtd_identifier FROM domains
2180 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM parameters
2182 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM routines
2183 ) AS x (objschema, objname, objtype, objdtdid);
2185 GRANT SELECT ON data_type_privileges TO PUBLIC;
2190 * ELEMENT_TYPES view
2193 CREATE VIEW element_types AS
2194 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2195 CAST(n.nspname AS sql_identifier) AS object_schema,
2196 CAST(x.objname AS sql_identifier) AS object_name,
2197 CAST(x.objtype AS character_data) AS object_type,
2198 CAST(x.objdtdid AS sql_identifier) AS collection_type_identifier,
2200 CASE WHEN nbt.nspname = 'pg_catalog' THEN format_type(bt.oid, null)
2201 ELSE 'USER-DEFINED' END AS character_data) AS data_type,
2203 CAST(null AS cardinal_number) AS character_maximum_length,
2204 CAST(null AS cardinal_number) AS character_octet_length,
2205 CAST(null AS sql_identifier) AS character_set_catalog,
2206 CAST(null AS sql_identifier) AS character_set_schema,
2207 CAST(null AS sql_identifier) AS character_set_name,
2208 CAST(null AS sql_identifier) AS collation_catalog,
2209 CAST(null AS sql_identifier) AS collation_schema,
2210 CAST(null AS sql_identifier) AS collation_name,
2211 CAST(null AS cardinal_number) AS numeric_precision,
2212 CAST(null AS cardinal_number) AS numeric_precision_radix,
2213 CAST(null AS cardinal_number) AS numeric_scale,
2214 CAST(null AS cardinal_number) AS datetime_precision,
2215 CAST(null AS character_data) AS interval_type,
2216 CAST(null AS character_data) AS interval_precision,
2218 CAST(null AS character_data) AS domain_default, -- XXX maybe a bug in the standard
2220 CAST(current_database() AS sql_identifier) AS udt_catalog,
2221 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
2222 CAST(bt.typname AS sql_identifier) AS udt_name,
2224 CAST(null AS sql_identifier) AS scope_catalog,
2225 CAST(null AS sql_identifier) AS scope_schema,
2226 CAST(null AS sql_identifier) AS scope_name,
2228 CAST(null AS cardinal_number) AS maximum_cardinality,
2229 CAST('a' || x.objdtdid AS sql_identifier) AS dtd_identifier
2231 FROM pg_namespace n, pg_type at, pg_namespace nbt, pg_type bt,
2234 SELECT c.relnamespace, CAST(c.relname AS sql_identifier),
2235 'TABLE'::text, a.attnum, a.atttypid
2236 FROM pg_class c, pg_attribute a
2237 WHERE c.oid = a.attrelid
2238 AND c.relkind IN ('r', 'v')
2239 AND attnum > 0 AND NOT attisdropped
2244 SELECT t.typnamespace, CAST(t.typname AS sql_identifier),
2245 'DOMAIN'::text, 1, t.typbasetype
2247 WHERE t.typtype = 'd'
2252 SELECT pronamespace, CAST(proname || '_' || CAST(oid AS text) AS sql_identifier),
2253 'ROUTINE'::text, (ss.x).n, (ss.x).x
2254 FROM (SELECT p.pronamespace, p.proname, p.oid,
2255 _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
2256 FROM pg_proc p) AS ss
2261 SELECT p.pronamespace, CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier),
2262 'ROUTINE'::text, 0, p.prorettype
2265 ) AS x (objschema, objname, objtype, objdtdid, objtypeid)
2267 WHERE n.oid = x.objschema
2268 AND at.oid = x.objtypeid
2269 AND (at.typelem <> 0 AND at.typlen = -1)
2270 AND at.typelem = bt.oid
2271 AND nbt.oid = bt.typnamespace
2273 AND (n.nspname, x.objname, x.objtype, x.objdtdid) IN
2274 ( SELECT object_schema, object_name, object_type, dtd_identifier
2275 FROM data_type_privileges );
2277 GRANT SELECT ON element_types TO PUBLIC;