2 * SQL Information Schema
3 * as defined in ISO/IEC 9075-11:2008
5 * Copyright (c) 2003-2009, PostgreSQL Global Development Group
7 * $PostgreSQL: pgsql/src/backend/catalog/information_schema.sql,v 1.61 2009/12/30 22:48:10 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_keysequal(smallint[], smallint[]) RETURNS boolean
44 LANGUAGE sql IMMUTABLE -- intentionally not STRICT, to allow inlining
45 AS 'select $1 <@ $2 and $2 <@ $1';
47 /* Given an index's OID and an underlying-table column number, return the
48 * column's position in the index (NULL if not there) */
49 CREATE FUNCTION _pg_index_position(oid, smallint) RETURNS int
50 LANGUAGE sql STRICT STABLE
53 (SELECT information_schema._pg_expandarray(indkey) AS a
54 FROM pg_catalog.pg_index WHERE indexrelid = $1) ss
58 CREATE FUNCTION _pg_truetypid(pg_attribute, pg_type) RETURNS oid
61 RETURNS NULL ON NULL INPUT
63 $$SELECT CASE WHEN $2.typtype = 'd' THEN $2.typbasetype ELSE $1.atttypid END$$;
65 CREATE FUNCTION _pg_truetypmod(pg_attribute, pg_type) RETURNS int4
68 RETURNS NULL ON NULL INPUT
70 $$SELECT CASE WHEN $2.typtype = 'd' THEN $2.typtypmod ELSE $1.atttypmod END$$;
72 -- these functions encapsulate knowledge about the encoding of typmod:
74 CREATE FUNCTION _pg_char_max_length(typid oid, typmod int4) RETURNS integer
77 RETURNS NULL ON NULL INPUT
80 CASE WHEN $2 = -1 /* default typmod */
82 WHEN $1 IN (1042, 1043) /* char, varchar */
84 WHEN $1 IN (1560, 1562) /* bit, varbit */
89 CREATE FUNCTION _pg_char_octet_length(typid oid, typmod int4) RETURNS integer
92 RETURNS NULL ON NULL INPUT
95 CASE WHEN $1 IN (25, 1042, 1043) /* text, char, varchar */
96 THEN CASE WHEN $2 = -1 /* default typmod */
97 THEN CAST(2^30 AS integer)
98 ELSE information_schema._pg_char_max_length($1, $2) *
99 pg_catalog.pg_encoding_max_length((SELECT encoding FROM pg_catalog.pg_database WHERE datname = pg_catalog.current_database()))
104 CREATE FUNCTION _pg_numeric_precision(typid oid, typmod int4) RETURNS integer
107 RETURNS NULL ON NULL INPUT
111 WHEN 21 /*int2*/ THEN 16
112 WHEN 23 /*int4*/ THEN 32
113 WHEN 20 /*int8*/ THEN 64
114 WHEN 1700 /*numeric*/ THEN
117 ELSE (($2 - 4) >> 16) & 65535
119 WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/
120 WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/
124 CREATE FUNCTION _pg_numeric_precision_radix(typid oid, typmod int4) RETURNS integer
127 RETURNS NULL ON NULL INPUT
130 CASE WHEN $1 IN (21, 23, 20, 700, 701) THEN 2
131 WHEN $1 IN (1700) THEN 10
135 CREATE FUNCTION _pg_numeric_scale(typid oid, typmod int4) RETURNS integer
138 RETURNS NULL ON NULL INPUT
141 CASE WHEN $1 IN (21, 23, 20) THEN 0
142 WHEN $1 IN (1700) THEN
145 ELSE ($2 - 4) & 65535
150 CREATE FUNCTION _pg_datetime_precision(typid oid, typmod int4) RETURNS integer
153 RETURNS NULL ON NULL INPUT
156 CASE WHEN $1 IN (1082) /* date */
158 WHEN $1 IN (1083, 1114, 1184, 1266) /* time, timestamp, same + tz */
159 THEN CASE WHEN $2 < 0 THEN 6 ELSE $2 END
160 WHEN $1 IN (1186) /* interval */
161 THEN CASE WHEN $2 < 0 THEN 6 ELSE $2 & 65535 END
166 -- 5.2 INFORMATION_SCHEMA_CATALOG_NAME view appears later.
171 * CARDINAL_NUMBER domain
174 CREATE DOMAIN cardinal_number AS integer
175 CONSTRAINT cardinal_number_domain_check CHECK (value >= 0);
180 * CHARACTER_DATA domain
183 CREATE DOMAIN character_data AS character varying;
188 * SQL_IDENTIFIER domain
191 CREATE DOMAIN sql_identifier AS character varying;
196 * INFORMATION_SCHEMA_CATALOG_NAME view
199 CREATE VIEW information_schema_catalog_name AS
200 SELECT CAST(current_database() AS sql_identifier) AS catalog_name;
202 GRANT SELECT ON information_schema_catalog_name TO PUBLIC;
210 CREATE DOMAIN time_stamp AS timestamp(2) with time zone
211 DEFAULT current_timestamp(2);
218 CREATE DOMAIN yes_or_no AS character varying(3)
219 CONSTRAINT yes_or_no_check CHECK (value IN ('YES', 'NO'));
222 -- 5.8 ADMINISTRABLE_ROLE_AUTHORIZATIONS view appears later.
227 * APPLICABLE_ROLES view
230 CREATE VIEW applicable_roles AS
231 SELECT CAST(a.rolname AS sql_identifier) AS grantee,
232 CAST(b.rolname AS sql_identifier) AS role_name,
233 CAST(CASE WHEN m.admin_option THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
234 FROM pg_auth_members m
235 JOIN pg_authid a ON (m.member = a.oid)
236 JOIN pg_authid b ON (m.roleid = b.oid)
237 WHERE pg_has_role(a.oid, 'USAGE');
239 GRANT SELECT ON applicable_roles TO PUBLIC;
244 * ADMINISTRABLE_ROLE_AUTHORIZATIONS view
247 CREATE VIEW administrable_role_authorizations AS
249 FROM applicable_roles
250 WHERE is_grantable = 'YES';
252 GRANT SELECT ON administrable_role_authorizations TO PUBLIC;
260 -- feature not supported
268 CREATE VIEW attributes AS
269 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
270 CAST(nc.nspname AS sql_identifier) AS udt_schema,
271 CAST(c.relname AS sql_identifier) AS udt_name,
272 CAST(a.attname AS sql_identifier) AS attribute_name,
273 CAST(a.attnum AS cardinal_number) AS ordinal_position,
274 CAST(pg_get_expr(ad.adbin, ad.adrelid) AS character_data) AS attribute_default,
275 CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
280 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
281 WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, null)
282 ELSE 'USER-DEFINED' END
287 _pg_char_max_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
289 AS character_maximum_length,
292 _pg_char_octet_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
294 AS character_octet_length,
296 CAST(null AS sql_identifier) AS character_set_catalog,
297 CAST(null AS sql_identifier) AS character_set_schema,
298 CAST(null AS sql_identifier) AS character_set_name,
300 CAST(null AS sql_identifier) AS collation_catalog,
301 CAST(null AS sql_identifier) AS collation_schema,
302 CAST(null AS sql_identifier) AS collation_name,
305 _pg_numeric_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
307 AS numeric_precision,
310 _pg_numeric_precision_radix(_pg_truetypid(a, t), _pg_truetypmod(a, t))
312 AS numeric_precision_radix,
315 _pg_numeric_scale(_pg_truetypid(a, t), _pg_truetypmod(a, t))
320 _pg_datetime_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
322 AS datetime_precision,
324 CAST(null AS character_data) AS interval_type, -- FIXME
325 CAST(null AS character_data) AS interval_precision, -- FIXME
327 CAST(current_database() AS sql_identifier) AS attribute_udt_catalog,
328 CAST(nt.nspname AS sql_identifier) AS attribute_udt_schema,
329 CAST(t.typname AS sql_identifier) AS attribute_udt_name,
331 CAST(null AS sql_identifier) AS scope_catalog,
332 CAST(null AS sql_identifier) AS scope_schema,
333 CAST(null AS sql_identifier) AS scope_name,
335 CAST(null AS cardinal_number) AS maximum_cardinality,
336 CAST(a.attnum AS sql_identifier) AS dtd_identifier,
337 CAST('NO' AS yes_or_no) AS is_derived_reference_attribute
339 FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum),
340 pg_class c, pg_namespace nc,
341 (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
343 WHERE a.attrelid = c.oid
344 AND a.atttypid = t.oid
345 AND nc.oid = c.relnamespace
346 AND a.attnum > 0 AND NOT a.attisdropped
347 AND c.relkind in ('c');
349 GRANT SELECT ON attributes TO PUBLIC;
354 * CHARACTER_SETS view
357 -- feature not supported
362 * CHECK_CONSTRAINT_ROUTINE_USAGE view
365 CREATE VIEW check_constraint_routine_usage AS
366 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
367 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
368 CAST(c.conname AS sql_identifier) AS constraint_name,
369 CAST(current_database() AS sql_identifier) AS specific_catalog,
370 CAST(np.nspname AS sql_identifier) AS specific_schema,
371 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name
372 FROM pg_namespace nc, pg_constraint c, pg_depend d, pg_proc p, pg_namespace np
373 WHERE nc.oid = c.connamespace
376 AND d.classid = 'pg_catalog.pg_constraint'::regclass
377 AND d.refobjid = p.oid
378 AND d.refclassid = 'pg_catalog.pg_proc'::regclass
379 AND p.pronamespace = np.oid
380 AND pg_has_role(p.proowner, 'USAGE');
382 GRANT SELECT ON check_constraint_routine_usage TO PUBLIC;
387 * CHECK_CONSTRAINTS view
390 CREATE VIEW check_constraints AS
391 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
392 CAST(rs.nspname AS sql_identifier) AS constraint_schema,
393 CAST(con.conname AS sql_identifier) AS constraint_name,
394 CAST(substring(pg_get_constraintdef(con.oid) from 7) AS character_data)
396 FROM pg_constraint con
397 LEFT OUTER JOIN pg_namespace rs ON (rs.oid = con.connamespace)
398 LEFT OUTER JOIN pg_class c ON (c.oid = con.conrelid)
399 LEFT OUTER JOIN pg_type t ON (t.oid = con.contypid)
400 WHERE pg_has_role(coalesce(c.relowner, t.typowner), 'USAGE')
401 AND con.contype = 'c'
404 -- not-null constraints
406 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
407 CAST(n.nspname AS sql_identifier) AS constraint_schema,
408 CAST(CAST(n.oid AS text) || '_' || CAST(r.oid AS text) || '_' || CAST(a.attnum AS text) || '_not_null' AS sql_identifier) AS constraint_name, -- XXX
409 CAST(a.attname || ' IS NOT NULL' AS character_data)
411 FROM pg_namespace n, pg_class r, pg_attribute a
412 WHERE n.oid = r.relnamespace
413 AND r.oid = a.attrelid
415 AND NOT a.attisdropped
418 AND pg_has_role(r.relowner, 'USAGE');
420 GRANT SELECT ON check_constraints TO PUBLIC;
428 -- feature not supported
432 * COLLATION_CHARACTER_SET_APPLICABILITY view
435 -- feature not supported
440 * COLUMN_COLUMN_USAGE view
443 -- feature not supported
448 * COLUMN_DOMAIN_USAGE view
451 CREATE VIEW column_domain_usage AS
452 SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
453 CAST(nt.nspname AS sql_identifier) AS domain_schema,
454 CAST(t.typname AS sql_identifier) AS domain_name,
455 CAST(current_database() AS sql_identifier) AS table_catalog,
456 CAST(nc.nspname AS sql_identifier) AS table_schema,
457 CAST(c.relname AS sql_identifier) AS table_name,
458 CAST(a.attname AS sql_identifier) AS column_name
460 FROM pg_type t, pg_namespace nt, pg_class c, pg_namespace nc,
463 WHERE t.typnamespace = nt.oid
464 AND c.relnamespace = nc.oid
465 AND a.attrelid = c.oid
466 AND a.atttypid = t.oid
468 AND c.relkind IN ('r', 'v')
470 AND NOT a.attisdropped
471 AND pg_has_role(t.typowner, 'USAGE');
473 GRANT SELECT ON column_domain_usage TO PUBLIC;
481 CREATE VIEW column_privileges AS
482 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
483 CAST(grantee.rolname AS sql_identifier) AS grantee,
484 CAST(current_database() AS sql_identifier) AS table_catalog,
485 CAST(nc.nspname AS sql_identifier) AS table_schema,
486 CAST(x.relname AS sql_identifier) AS table_name,
487 CAST(x.attname AS sql_identifier) AS column_name,
488 CAST(x.prtype AS character_data) AS privilege_type,
491 -- object owner always has grant options
492 pg_has_role(x.grantee, x.relowner, 'USAGE')
494 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
505 FROM (SELECT oid, relname, relnamespace, relowner, (aclexplode(relacl)).*
507 WHERE relkind IN ('r', 'v')
508 ) pr_c (oid, relname, relnamespace, relowner, grantor, grantee, prtype, grantable),
510 WHERE a.attrelid = pr_c.oid
512 AND NOT a.attisdropped
522 FROM (SELECT attrelid, attname, (aclexplode(attacl)).*
526 ) pr_a (attrelid, attname, grantor, grantee, prtype, grantable),
528 WHERE pr_a.attrelid = c.oid
529 AND relkind IN ('r','v')
534 SELECT oid, rolname FROM pg_authid
536 SELECT 0::oid, 'PUBLIC'
537 ) AS grantee (oid, rolname)
539 WHERE x.relnamespace = nc.oid
540 AND x.grantee = grantee.oid
541 AND x.grantor = u_grantor.oid
542 AND x.prtype IN ('INSERT', 'SELECT', 'UPDATE', 'REFERENCES')
543 AND (pg_has_role(u_grantor.oid, 'USAGE')
544 OR pg_has_role(grantee.oid, 'USAGE')
545 OR grantee.rolname = 'PUBLIC');
547 GRANT SELECT ON column_privileges TO PUBLIC;
552 * COLUMN_UDT_USAGE view
555 CREATE VIEW column_udt_usage AS
556 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
557 CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,
558 CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,
559 CAST(current_database() AS sql_identifier) AS table_catalog,
560 CAST(nc.nspname AS sql_identifier) AS table_schema,
561 CAST(c.relname AS sql_identifier) AS table_name,
562 CAST(a.attname AS sql_identifier) AS column_name
564 FROM pg_attribute a, pg_class c, pg_namespace nc,
565 (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
566 LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
567 ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
569 WHERE a.attrelid = c.oid
570 AND a.atttypid = t.oid
571 AND nc.oid = c.relnamespace
572 AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
573 AND pg_has_role(coalesce(bt.typowner, t.typowner), 'USAGE');
575 GRANT SELECT ON column_udt_usage TO PUBLIC;
583 CREATE VIEW columns AS
584 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
585 CAST(nc.nspname AS sql_identifier) AS table_schema,
586 CAST(c.relname AS sql_identifier) AS table_name,
587 CAST(a.attname AS sql_identifier) AS column_name,
588 CAST(a.attnum AS cardinal_number) AS ordinal_position,
589 CAST(pg_get_expr(ad.adbin, ad.adrelid) AS character_data) AS column_default,
590 CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
595 CASE WHEN t.typtype = 'd' THEN
596 CASE WHEN bt.typelem <> 0 AND bt.typlen = -1 THEN 'ARRAY'
597 WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
598 ELSE 'USER-DEFINED' END
600 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
601 WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, null)
602 ELSE 'USER-DEFINED' END
608 _pg_char_max_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
610 AS character_maximum_length,
613 _pg_char_octet_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
615 AS character_octet_length,
618 _pg_numeric_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
620 AS numeric_precision,
623 _pg_numeric_precision_radix(_pg_truetypid(a, t), _pg_truetypmod(a, t))
625 AS numeric_precision_radix,
628 _pg_numeric_scale(_pg_truetypid(a, t), _pg_truetypmod(a, t))
633 _pg_datetime_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
635 AS datetime_precision,
637 CAST(null AS character_data) AS interval_type, -- FIXME
638 CAST(null AS character_data) AS interval_precision, -- FIXME
640 CAST(null AS sql_identifier) AS character_set_catalog,
641 CAST(null AS sql_identifier) AS character_set_schema,
642 CAST(null AS sql_identifier) AS character_set_name,
644 CAST(null AS sql_identifier) AS collation_catalog,
645 CAST(null AS sql_identifier) AS collation_schema,
646 CAST(null AS sql_identifier) AS collation_name,
648 CAST(CASE WHEN t.typtype = 'd' THEN current_database() ELSE null END
649 AS sql_identifier) AS domain_catalog,
650 CAST(CASE WHEN t.typtype = 'd' THEN nt.nspname ELSE null END
651 AS sql_identifier) AS domain_schema,
652 CAST(CASE WHEN t.typtype = 'd' THEN t.typname ELSE null END
653 AS sql_identifier) AS domain_name,
655 CAST(current_database() AS sql_identifier) AS udt_catalog,
656 CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,
657 CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,
659 CAST(null AS sql_identifier) AS scope_catalog,
660 CAST(null AS sql_identifier) AS scope_schema,
661 CAST(null AS sql_identifier) AS scope_name,
663 CAST(null AS cardinal_number) AS maximum_cardinality,
664 CAST(a.attnum AS sql_identifier) AS dtd_identifier,
665 CAST('NO' AS yes_or_no) AS is_self_referencing,
667 CAST('NO' AS yes_or_no) AS is_identity,
668 CAST(null AS character_data) AS identity_generation,
669 CAST(null AS character_data) AS identity_start,
670 CAST(null AS character_data) AS identity_increment,
671 CAST(null AS character_data) AS identity_maximum,
672 CAST(null AS character_data) AS identity_minimum,
673 CAST(null AS yes_or_no) AS identity_cycle,
675 CAST('NEVER' AS character_data) AS is_generated,
676 CAST(null AS character_data) AS generation_expression,
678 CAST(CASE WHEN c.relkind = 'r'
680 AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '2' AND is_instead)
681 AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '4' AND is_instead))
682 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_updatable
684 FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum),
685 pg_class c, pg_namespace nc,
686 (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
687 LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
688 ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
690 WHERE a.attrelid = c.oid
691 AND a.atttypid = t.oid
692 AND nc.oid = c.relnamespace
693 AND (NOT pg_is_other_temp_schema(nc.oid))
695 AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
697 AND (pg_has_role(c.relowner, 'USAGE')
698 OR has_column_privilege(c.oid, a.attnum,
699 'SELECT, INSERT, UPDATE, REFERENCES'));
701 GRANT SELECT ON columns TO PUBLIC;
706 * CONSTRAINT_COLUMN_USAGE view
709 CREATE VIEW constraint_column_usage AS
710 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
711 CAST(tblschema AS sql_identifier) AS table_schema,
712 CAST(tblname AS sql_identifier) AS table_name,
713 CAST(colname AS sql_identifier) AS column_name,
714 CAST(current_database() AS sql_identifier) AS constraint_catalog,
715 CAST(cstrschema AS sql_identifier) AS constraint_schema,
716 CAST(cstrname AS sql_identifier) AS constraint_name
719 /* check constraints */
720 SELECT DISTINCT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
721 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_depend d, pg_namespace nc, pg_constraint c
722 WHERE nr.oid = r.relnamespace
723 AND r.oid = a.attrelid
724 AND d.refclassid = 'pg_catalog.pg_class'::regclass
725 AND d.refobjid = r.oid
726 AND d.refobjsubid = a.attnum
727 AND d.classid = 'pg_catalog.pg_constraint'::regclass
729 AND c.connamespace = nc.oid
732 AND NOT a.attisdropped
736 /* unique/primary key/foreign key constraints */
737 SELECT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
738 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc,
740 WHERE nr.oid = r.relnamespace
741 AND r.oid = a.attrelid
742 AND nc.oid = c.connamespace
743 AND (CASE WHEN c.contype = 'f' THEN r.oid = c.confrelid AND a.attnum = ANY (c.confkey)
744 ELSE r.oid = c.conrelid AND a.attnum = ANY (c.conkey) END)
745 AND NOT a.attisdropped
746 AND c.contype IN ('p', 'u', 'f')
749 ) AS x (tblschema, tblname, tblowner, colname, cstrschema, cstrname)
751 WHERE pg_has_role(x.tblowner, 'USAGE');
753 GRANT SELECT ON constraint_column_usage TO PUBLIC;
758 * CONSTRAINT_TABLE_USAGE view
761 CREATE VIEW constraint_table_usage AS
762 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
763 CAST(nr.nspname AS sql_identifier) AS table_schema,
764 CAST(r.relname AS sql_identifier) AS table_name,
765 CAST(current_database() AS sql_identifier) AS constraint_catalog,
766 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
767 CAST(c.conname AS sql_identifier) AS constraint_name
769 FROM pg_constraint c, pg_namespace nc,
770 pg_class r, pg_namespace nr
772 WHERE c.connamespace = nc.oid AND r.relnamespace = nr.oid
773 AND ( (c.contype = 'f' AND c.confrelid = r.oid)
774 OR (c.contype IN ('p', 'u') AND c.conrelid = r.oid) )
776 AND pg_has_role(r.relowner, 'USAGE');
778 GRANT SELECT ON constraint_table_usage TO PUBLIC;
781 -- 5.24 DATA_TYPE_PRIVILEGES view appears later.
786 * DIRECT_SUPERTABLES view
789 -- feature not supported
794 * DIRECT_SUPERTYPES view
797 -- feature not supported
802 * DOMAIN_CONSTRAINTS view
805 CREATE VIEW domain_constraints AS
806 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
807 CAST(rs.nspname AS sql_identifier) AS constraint_schema,
808 CAST(con.conname AS sql_identifier) AS constraint_name,
809 CAST(current_database() AS sql_identifier) AS domain_catalog,
810 CAST(n.nspname AS sql_identifier) AS domain_schema,
811 CAST(t.typname AS sql_identifier) AS domain_name,
812 CAST(CASE WHEN condeferrable THEN 'YES' ELSE 'NO' END
813 AS yes_or_no) AS is_deferrable,
814 CAST(CASE WHEN condeferred THEN 'YES' ELSE 'NO' END
815 AS yes_or_no) AS initially_deferred
816 FROM pg_namespace rs, pg_namespace n, pg_constraint con, pg_type t
817 WHERE rs.oid = con.connamespace
818 AND n.oid = t.typnamespace
819 AND t.oid = con.contypid;
821 GRANT SELECT ON domain_constraints TO PUBLIC;
825 * DOMAIN_UDT_USAGE view
826 * apparently removed in SQL:2003
829 CREATE VIEW domain_udt_usage AS
830 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
831 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
832 CAST(bt.typname AS sql_identifier) AS udt_name,
833 CAST(current_database() AS sql_identifier) AS domain_catalog,
834 CAST(nt.nspname AS sql_identifier) AS domain_schema,
835 CAST(t.typname AS sql_identifier) AS domain_name
837 FROM pg_type t, pg_namespace nt,
838 pg_type bt, pg_namespace nbt
840 WHERE t.typnamespace = nt.oid
841 AND t.typbasetype = bt.oid
842 AND bt.typnamespace = nbt.oid
844 AND pg_has_role(bt.typowner, 'USAGE');
846 GRANT SELECT ON domain_udt_usage TO PUBLIC;
854 CREATE VIEW domains AS
855 SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
856 CAST(nt.nspname AS sql_identifier) AS domain_schema,
857 CAST(t.typname AS sql_identifier) AS domain_name,
860 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
861 WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
862 ELSE 'USER-DEFINED' END
867 _pg_char_max_length(t.typbasetype, t.typtypmod)
869 AS character_maximum_length,
872 _pg_char_octet_length(t.typbasetype, t.typtypmod)
874 AS character_octet_length,
876 CAST(null AS sql_identifier) AS character_set_catalog,
877 CAST(null AS sql_identifier) AS character_set_schema,
878 CAST(null AS sql_identifier) AS character_set_name,
880 CAST(null AS sql_identifier) AS collation_catalog,
881 CAST(null AS sql_identifier) AS collation_schema,
882 CAST(null AS sql_identifier) AS collation_name,
885 _pg_numeric_precision(t.typbasetype, t.typtypmod)
887 AS numeric_precision,
890 _pg_numeric_precision_radix(t.typbasetype, t.typtypmod)
892 AS numeric_precision_radix,
895 _pg_numeric_scale(t.typbasetype, t.typtypmod)
900 _pg_datetime_precision(t.typbasetype, t.typtypmod)
902 AS datetime_precision,
904 CAST(null AS character_data) AS interval_type, -- FIXME
905 CAST(null AS character_data) AS interval_precision, -- FIXME
907 CAST(t.typdefault AS character_data) AS domain_default,
909 CAST(current_database() AS sql_identifier) AS udt_catalog,
910 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
911 CAST(bt.typname AS sql_identifier) AS udt_name,
913 CAST(null AS sql_identifier) AS scope_catalog,
914 CAST(null AS sql_identifier) AS scope_schema,
915 CAST(null AS sql_identifier) AS scope_name,
917 CAST(null AS cardinal_number) AS maximum_cardinality,
918 CAST(1 AS sql_identifier) AS dtd_identifier
920 FROM pg_type t, pg_namespace nt,
921 pg_type bt, pg_namespace nbt
923 WHERE t.typnamespace = nt.oid
924 AND t.typbasetype = bt.oid
925 AND bt.typnamespace = nbt.oid
928 GRANT SELECT ON domains TO PUBLIC;
931 -- 5.29 ELEMENT_TYPES view appears later.
939 CREATE VIEW enabled_roles AS
940 SELECT CAST(a.rolname AS sql_identifier) AS role_name
942 WHERE pg_has_role(a.oid, 'USAGE');
944 GRANT SELECT ON enabled_roles TO PUBLIC;
952 -- feature not supported
957 * KEY_COLUMN_USAGE view
960 CREATE VIEW key_column_usage AS
961 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
962 CAST(nc_nspname AS sql_identifier) AS constraint_schema,
963 CAST(conname AS sql_identifier) AS constraint_name,
964 CAST(current_database() AS sql_identifier) AS table_catalog,
965 CAST(nr_nspname AS sql_identifier) AS table_schema,
966 CAST(relname AS sql_identifier) AS table_name,
967 CAST(a.attname AS sql_identifier) AS column_name,
968 CAST((ss.x).n AS cardinal_number) AS ordinal_position,
969 CAST(CASE WHEN contype = 'f' THEN
970 _pg_index_position(ss.conindid, ss.confkey[(ss.x).n])
972 END AS cardinal_number)
973 AS position_in_unique_constraint
975 (SELECT r.oid AS roid, r.relname, r.relowner,
976 nc.nspname AS nc_nspname, nr.nspname AS nr_nspname,
977 c.oid AS coid, c.conname, c.contype, c.conindid,
978 c.confkey, c.confrelid,
979 _pg_expandarray(c.conkey) AS x
980 FROM pg_namespace nr, pg_class r, pg_namespace nc,
982 WHERE nr.oid = r.relnamespace
983 AND r.oid = c.conrelid
984 AND nc.oid = c.connamespace
985 AND c.contype IN ('p', 'u', 'f')
987 AND (NOT pg_is_other_temp_schema(nr.oid)) ) AS ss
988 WHERE ss.roid = a.attrelid
989 AND a.attnum = (ss.x).x
990 AND NOT a.attisdropped
991 AND (pg_has_role(relowner, 'USAGE')
992 OR has_column_privilege(roid, a.attnum,
993 'SELECT, INSERT, UPDATE, REFERENCES'));
995 GRANT SELECT ON key_column_usage TO PUBLIC;
1000 * METHOD_SPECIFICATION_PARAMETERS view
1003 -- feature not supported
1008 * METHOD_SPECIFICATIONS view
1011 -- feature not supported
1019 CREATE VIEW parameters AS
1020 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
1021 CAST(n_nspname AS sql_identifier) AS specific_schema,
1022 CAST(proname || '_' || CAST(p_oid AS text) AS sql_identifier) AS specific_name,
1023 CAST((ss.x).n AS cardinal_number) AS ordinal_position,
1025 CASE WHEN proargmodes IS NULL THEN 'IN'
1026 WHEN proargmodes[(ss.x).n] = 'i' THEN 'IN'
1027 WHEN proargmodes[(ss.x).n] = 'o' THEN 'OUT'
1028 WHEN proargmodes[(ss.x).n] = 'b' THEN 'INOUT'
1029 WHEN proargmodes[(ss.x).n] = 'v' THEN 'IN'
1030 WHEN proargmodes[(ss.x).n] = 't' THEN 'OUT'
1031 END AS character_data) AS parameter_mode,
1032 CAST('NO' AS yes_or_no) AS is_result,
1033 CAST('NO' AS yes_or_no) AS as_locator,
1034 CAST(NULLIF(proargnames[(ss.x).n], '') AS sql_identifier) AS parameter_name,
1036 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
1037 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
1038 ELSE 'USER-DEFINED' END AS character_data)
1040 CAST(null AS cardinal_number) AS character_maximum_length,
1041 CAST(null AS cardinal_number) AS character_octet_length,
1042 CAST(null AS sql_identifier) AS character_set_catalog,
1043 CAST(null AS sql_identifier) AS character_set_schema,
1044 CAST(null AS sql_identifier) AS character_set_name,
1045 CAST(null AS sql_identifier) AS collation_catalog,
1046 CAST(null AS sql_identifier) AS collation_schema,
1047 CAST(null AS sql_identifier) AS collation_name,
1048 CAST(null AS cardinal_number) AS numeric_precision,
1049 CAST(null AS cardinal_number) AS numeric_precision_radix,
1050 CAST(null AS cardinal_number) AS numeric_scale,
1051 CAST(null AS cardinal_number) AS datetime_precision,
1052 CAST(null AS character_data) AS interval_type,
1053 CAST(null AS character_data) AS interval_precision,
1054 CAST(current_database() AS sql_identifier) AS udt_catalog,
1055 CAST(nt.nspname AS sql_identifier) AS udt_schema,
1056 CAST(t.typname AS sql_identifier) AS udt_name,
1057 CAST(null AS sql_identifier) AS scope_catalog,
1058 CAST(null AS sql_identifier) AS scope_schema,
1059 CAST(null AS sql_identifier) AS scope_name,
1060 CAST(null AS cardinal_number) AS maximum_cardinality,
1061 CAST((ss.x).n AS sql_identifier) AS dtd_identifier
1063 FROM pg_type t, pg_namespace nt,
1064 (SELECT n.nspname AS n_nspname, p.proname, p.oid AS p_oid,
1065 p.proargnames, p.proargmodes,
1066 _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
1067 FROM pg_namespace n, pg_proc p
1068 WHERE n.oid = p.pronamespace
1069 AND (pg_has_role(p.proowner, 'USAGE') OR
1070 has_function_privilege(p.oid, 'EXECUTE'))) AS ss
1071 WHERE t.oid = (ss.x).x AND t.typnamespace = nt.oid;
1073 GRANT SELECT ON parameters TO PUBLIC;
1078 * REFERENCED_TYPES view
1081 -- feature not supported
1086 * REFERENTIAL_CONSTRAINTS view
1089 CREATE VIEW referential_constraints AS
1090 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1091 CAST(ncon.nspname AS sql_identifier) AS constraint_schema,
1092 CAST(con.conname AS sql_identifier) AS constraint_name,
1094 CASE WHEN npkc.nspname IS NULL THEN NULL
1095 ELSE current_database() END
1096 AS sql_identifier) AS unique_constraint_catalog,
1097 CAST(npkc.nspname AS sql_identifier) AS unique_constraint_schema,
1098 CAST(pkc.conname AS sql_identifier) AS unique_constraint_name,
1101 CASE con.confmatchtype WHEN 'f' THEN 'FULL'
1102 WHEN 'p' THEN 'PARTIAL'
1103 WHEN 'u' THEN 'NONE' END
1104 AS character_data) AS match_option,
1107 CASE con.confupdtype WHEN 'c' THEN 'CASCADE'
1108 WHEN 'n' THEN 'SET NULL'
1109 WHEN 'd' THEN 'SET DEFAULT'
1110 WHEN 'r' THEN 'RESTRICT'
1111 WHEN 'a' THEN 'NO ACTION' END
1112 AS character_data) AS update_rule,
1115 CASE con.confdeltype WHEN 'c' THEN 'CASCADE'
1116 WHEN 'n' THEN 'SET NULL'
1117 WHEN 'd' THEN 'SET DEFAULT'
1118 WHEN 'r' THEN 'RESTRICT'
1119 WHEN 'a' THEN 'NO ACTION' END
1120 AS character_data) AS delete_rule
1122 FROM (pg_namespace ncon
1123 INNER JOIN pg_constraint con ON ncon.oid = con.connamespace
1124 INNER JOIN pg_class c ON con.conrelid = c.oid)
1127 INNER JOIN pg_namespace npkc ON pkc.connamespace = npkc.oid)
1128 ON con.confrelid = pkc.conrelid
1129 AND _pg_keysequal(con.confkey, pkc.conkey)
1131 WHERE c.relkind = 'r'
1132 AND con.contype = 'f'
1133 AND (pkc.contype IN ('p', 'u') OR pkc.contype IS NULL)
1134 AND (pg_has_role(c.relowner, 'USAGE')
1135 -- SELECT privilege omitted, per SQL standard
1136 OR has_table_privilege(c.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1137 OR has_any_column_privilege(c.oid, 'INSERT, UPDATE, REFERENCES') );
1139 GRANT SELECT ON referential_constraints TO PUBLIC;
1144 * ROLE_COLUMN_GRANTS view
1147 CREATE VIEW role_column_grants AS
1156 FROM column_privileges
1157 WHERE grantor IN (SELECT role_name FROM enabled_roles)
1158 OR grantee IN (SELECT role_name FROM enabled_roles);
1160 GRANT SELECT ON role_column_grants TO PUBLIC;
1163 -- 5.39 ROLE_ROUTINE_GRANTS view is based on 5.45 ROUTINE_PRIVILEGES and is defined there instead.
1166 -- 5.40 ROLE_TABLE_GRANTS view is based on 5.60 TABLE_PRIVILEGES and is defined there instead.
1171 * ROLE_TABLE_METHOD_GRANTS view
1174 -- feature not supported
1178 -- 5.42 ROLE_USAGE_GRANTS view is based on 5.71 USAGE_PRIVILEGES and is defined there instead.
1183 * ROLE_UDT_GRANTS view
1186 -- feature not supported
1191 * ROUTINE_COLUMN_USAGE view
1194 -- not tracked by PostgreSQL
1199 * ROUTINE_PRIVILEGES view
1202 CREATE VIEW routine_privileges AS
1203 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1204 CAST(grantee.rolname AS sql_identifier) AS grantee,
1205 CAST(current_database() AS sql_identifier) AS specific_catalog,
1206 CAST(n.nspname AS sql_identifier) AS specific_schema,
1207 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1208 CAST(current_database() AS sql_identifier) AS routine_catalog,
1209 CAST(n.nspname AS sql_identifier) AS routine_schema,
1210 CAST(p.proname AS sql_identifier) AS routine_name,
1211 CAST('EXECUTE' AS character_data) AS privilege_type,
1214 -- object owner always has grant options
1215 pg_has_role(grantee.oid, p.proowner, 'USAGE')
1217 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
1220 SELECT oid, proname, proowner, pronamespace, (aclexplode(proacl)).* FROM pg_proc
1221 ) p (oid, proname, proowner, pronamespace, grantor, grantee, prtype, grantable),
1223 pg_authid u_grantor,
1225 SELECT oid, rolname FROM pg_authid
1227 SELECT 0::oid, 'PUBLIC'
1228 ) AS grantee (oid, rolname)
1230 WHERE p.pronamespace = n.oid
1231 AND grantee.oid = p.grantee
1232 AND u_grantor.oid = p.grantor
1233 AND p.prtype IN ('EXECUTE')
1234 AND (pg_has_role(u_grantor.oid, 'USAGE')
1235 OR pg_has_role(grantee.oid, 'USAGE')
1236 OR grantee.rolname = 'PUBLIC');
1238 GRANT SELECT ON routine_privileges TO PUBLIC;
1243 * ROLE_ROUTINE_GRANTS view
1246 CREATE VIEW role_routine_grants AS
1257 FROM routine_privileges
1258 WHERE grantor IN (SELECT role_name FROM enabled_roles)
1259 OR grantee IN (SELECT role_name FROM enabled_roles);
1261 GRANT SELECT ON role_routine_grants TO PUBLIC;
1266 * ROUTINE_ROUTINE_USAGE view
1269 -- not tracked by PostgreSQL
1274 * ROUTINE_SEQUENCE_USAGE view
1277 -- not tracked by PostgreSQL
1282 * ROUTINE_TABLE_USAGE view
1285 -- not tracked by PostgreSQL
1293 CREATE VIEW routines AS
1294 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
1295 CAST(n.nspname AS sql_identifier) AS specific_schema,
1296 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1297 CAST(current_database() AS sql_identifier) AS routine_catalog,
1298 CAST(n.nspname AS sql_identifier) AS routine_schema,
1299 CAST(p.proname AS sql_identifier) AS routine_name,
1300 CAST('FUNCTION' AS character_data) AS routine_type,
1301 CAST(null AS sql_identifier) AS module_catalog,
1302 CAST(null AS sql_identifier) AS module_schema,
1303 CAST(null AS sql_identifier) AS module_name,
1304 CAST(null AS sql_identifier) AS udt_catalog,
1305 CAST(null AS sql_identifier) AS udt_schema,
1306 CAST(null AS sql_identifier) AS udt_name,
1309 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
1310 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
1311 ELSE 'USER-DEFINED' END AS character_data)
1313 CAST(null AS cardinal_number) AS character_maximum_length,
1314 CAST(null AS cardinal_number) AS character_octet_length,
1315 CAST(null AS sql_identifier) AS character_set_catalog,
1316 CAST(null AS sql_identifier) AS character_set_schema,
1317 CAST(null AS sql_identifier) AS character_set_name,
1318 CAST(null AS sql_identifier) AS collation_catalog,
1319 CAST(null AS sql_identifier) AS collation_schema,
1320 CAST(null AS sql_identifier) AS collation_name,
1321 CAST(null AS cardinal_number) AS numeric_precision,
1322 CAST(null AS cardinal_number) AS numeric_precision_radix,
1323 CAST(null AS cardinal_number) AS numeric_scale,
1324 CAST(null AS cardinal_number) AS datetime_precision,
1325 CAST(null AS character_data) AS interval_type,
1326 CAST(null AS character_data) AS interval_precision,
1327 CAST(current_database() AS sql_identifier) AS type_udt_catalog,
1328 CAST(nt.nspname AS sql_identifier) AS type_udt_schema,
1329 CAST(t.typname AS sql_identifier) AS type_udt_name,
1330 CAST(null AS sql_identifier) AS scope_catalog,
1331 CAST(null AS sql_identifier) AS scope_schema,
1332 CAST(null AS sql_identifier) AS scope_name,
1333 CAST(null AS cardinal_number) AS maximum_cardinality,
1334 CAST(0 AS sql_identifier) AS dtd_identifier,
1336 CAST(CASE WHEN l.lanname = 'sql' THEN 'SQL' ELSE 'EXTERNAL' END AS character_data)
1339 CASE WHEN pg_has_role(p.proowner, 'USAGE') THEN p.prosrc ELSE null END
1340 AS character_data) AS routine_definition,
1342 CASE WHEN l.lanname = 'c' THEN p.prosrc ELSE null END
1343 AS character_data) AS external_name,
1344 CAST(upper(l.lanname) AS character_data) AS external_language,
1346 CAST('GENERAL' AS character_data) AS parameter_style,
1347 CAST(CASE WHEN p.provolatile = 'i' THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_deterministic,
1348 CAST('MODIFIES' AS character_data) AS sql_data_access,
1349 CAST(CASE WHEN p.proisstrict THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_null_call,
1350 CAST(null AS character_data) AS sql_path,
1351 CAST('YES' AS yes_or_no) AS schema_level_routine,
1352 CAST(0 AS cardinal_number) AS max_dynamic_result_sets,
1353 CAST(null AS yes_or_no) AS is_user_defined_cast,
1354 CAST(null AS yes_or_no) AS is_implicitly_invocable,
1355 CAST(CASE WHEN p.prosecdef THEN 'DEFINER' ELSE 'INVOKER' END AS character_data) AS security_type,
1356 CAST(null AS sql_identifier) AS to_sql_specific_catalog,
1357 CAST(null AS sql_identifier) AS to_sql_specific_schema,
1358 CAST(null AS sql_identifier) AS to_sql_specific_name,
1359 CAST('NO' AS yes_or_no) AS as_locator,
1360 CAST(null AS time_stamp) AS created,
1361 CAST(null AS time_stamp) AS last_altered,
1362 CAST(null AS yes_or_no) AS new_savepoint_level,
1363 CAST('YES' AS yes_or_no) AS is_udt_dependent, -- FIXME?
1365 CAST(null AS character_data) AS result_cast_from_data_type,
1366 CAST(null AS yes_or_no) AS result_cast_as_locator,
1367 CAST(null AS cardinal_number) AS result_cast_char_max_length,
1368 CAST(null AS cardinal_number) AS result_cast_char_octet_length,
1369 CAST(null AS sql_identifier) AS result_cast_char_set_catalog,
1370 CAST(null AS sql_identifier) AS result_cast_char_set_schema,
1371 CAST(null AS sql_identifier) AS result_cast_character_set_name,
1372 CAST(null AS sql_identifier) AS result_cast_collation_catalog,
1373 CAST(null AS sql_identifier) AS result_cast_collation_schema,
1374 CAST(null AS sql_identifier) AS result_cast_collation_name,
1375 CAST(null AS cardinal_number) AS result_cast_numeric_precision,
1376 CAST(null AS cardinal_number) AS result_cast_numeric_precision_radix,
1377 CAST(null AS cardinal_number) AS result_cast_numeric_scale,
1378 CAST(null AS cardinal_number) AS result_cast_datetime_precision,
1379 CAST(null AS character_data) AS result_cast_interval_type,
1380 CAST(null AS character_data) AS result_cast_interval_precision,
1381 CAST(null AS sql_identifier) AS result_cast_type_udt_catalog,
1382 CAST(null AS sql_identifier) AS result_cast_type_udt_schema,
1383 CAST(null AS sql_identifier) AS result_cast_type_udt_name,
1384 CAST(null AS sql_identifier) AS result_cast_scope_catalog,
1385 CAST(null AS sql_identifier) AS result_cast_scope_schema,
1386 CAST(null AS sql_identifier) AS result_cast_scope_name,
1387 CAST(null AS cardinal_number) AS result_cast_maximum_cardinality,
1388 CAST(null AS sql_identifier) AS result_cast_dtd_identifier
1390 FROM pg_namespace n, pg_proc p, pg_language l,
1391 pg_type t, pg_namespace nt
1393 WHERE n.oid = p.pronamespace AND p.prolang = l.oid
1394 AND p.prorettype = t.oid AND t.typnamespace = nt.oid
1395 AND (pg_has_role(p.proowner, 'USAGE')
1396 OR has_function_privilege(p.oid, 'EXECUTE'));
1398 GRANT SELECT ON routines TO PUBLIC;
1406 CREATE VIEW schemata AS
1407 SELECT CAST(current_database() AS sql_identifier) AS catalog_name,
1408 CAST(n.nspname AS sql_identifier) AS schema_name,
1409 CAST(u.rolname AS sql_identifier) AS schema_owner,
1410 CAST(null AS sql_identifier) AS default_character_set_catalog,
1411 CAST(null AS sql_identifier) AS default_character_set_schema,
1412 CAST(null AS sql_identifier) AS default_character_set_name,
1413 CAST(null AS character_data) AS sql_path
1414 FROM pg_namespace n, pg_authid u
1415 WHERE n.nspowner = u.oid AND pg_has_role(n.nspowner, 'USAGE');
1417 GRANT SELECT ON schemata TO PUBLIC;
1425 CREATE VIEW sequences AS
1426 SELECT CAST(current_database() AS sql_identifier) AS sequence_catalog,
1427 CAST(nc.nspname AS sql_identifier) AS sequence_schema,
1428 CAST(c.relname AS sql_identifier) AS sequence_name,
1429 CAST('bigint' AS character_data) AS data_type,
1430 CAST(64 AS cardinal_number) AS numeric_precision,
1431 CAST(2 AS cardinal_number) AS numeric_precision_radix,
1432 CAST(0 AS cardinal_number) AS numeric_scale,
1433 CAST(null AS cardinal_number) AS maximum_value, -- FIXME
1434 CAST(null AS cardinal_number) AS minimum_value, -- FIXME
1435 CAST(null AS cardinal_number) AS increment, -- FIXME
1436 CAST(null AS yes_or_no) AS cycle_option -- FIXME
1437 FROM pg_namespace nc, pg_class c
1438 WHERE c.relnamespace = nc.oid
1440 AND (NOT pg_is_other_temp_schema(nc.oid))
1441 AND (pg_has_role(c.relowner, 'USAGE')
1442 OR has_table_privilege(c.oid, 'SELECT, UPDATE') );
1444 GRANT SELECT ON sequences TO PUBLIC;
1449 * SQL_FEATURES table
1452 CREATE TABLE sql_features (
1453 feature_id character_data,
1454 feature_name character_data,
1455 sub_feature_id character_data,
1456 sub_feature_name character_data,
1457 is_supported yes_or_no,
1458 is_verified_by character_data,
1459 comments character_data
1462 -- Will be filled with external data by initdb.
1464 GRANT SELECT ON sql_features TO PUBLIC;
1469 * SQL_IMPLEMENTATION_INFO table
1472 -- Note: Implementation information items are defined in ISO/IEC 9075-3:2008,
1475 CREATE TABLE sql_implementation_info (
1476 implementation_info_id character_data,
1477 implementation_info_name character_data,
1478 integer_value cardinal_number,
1479 character_value character_data,
1480 comments character_data
1483 INSERT INTO sql_implementation_info VALUES ('10003', 'CATALOG NAME', NULL, 'Y', NULL);
1484 INSERT INTO sql_implementation_info VALUES ('10004', 'COLLATING SEQUENCE', NULL, '', 'not supported');
1485 INSERT INTO sql_implementation_info VALUES ('23', 'CURSOR COMMIT BEHAVIOR', 1, NULL, 'close cursors and retain prepared statements');
1486 INSERT INTO sql_implementation_info VALUES ('2', 'DATA SOURCE NAME', NULL, '', NULL);
1487 INSERT INTO sql_implementation_info VALUES ('17', 'DBMS NAME', NULL, (select trim(trailing ' ' from substring(version() from '^[^0-9]*'))), NULL);
1488 INSERT INTO sql_implementation_info VALUES ('18', 'DBMS VERSION', NULL, '???', NULL); -- filled by initdb
1489 INSERT INTO sql_implementation_info VALUES ('26', 'DEFAULT TRANSACTION ISOLATION', 2, NULL, 'READ COMMITTED; user-settable');
1490 INSERT INTO sql_implementation_info VALUES ('28', 'IDENTIFIER CASE', 3, NULL, 'stored in mixed case - case sensitive');
1491 INSERT INTO sql_implementation_info VALUES ('85', 'NULL COLLATION', 0, NULL, 'nulls higher than non-nulls');
1492 INSERT INTO sql_implementation_info VALUES ('13', 'SERVER NAME', NULL, '', NULL);
1493 INSERT INTO sql_implementation_info VALUES ('94', 'SPECIAL CHARACTERS', NULL, '', 'all non-ASCII characters allowed');
1494 INSERT INTO sql_implementation_info VALUES ('46', 'TRANSACTION CAPABLE', 2, NULL, 'both DML and DDL');
1496 GRANT SELECT ON sql_implementation_info TO PUBLIC;
1500 * SQL_LANGUAGES table
1501 * apparently removed in SQL:2008
1504 CREATE TABLE sql_languages (
1505 sql_language_source character_data,
1506 sql_language_year character_data,
1507 sql_language_conformance character_data,
1508 sql_language_integrity character_data,
1509 sql_language_implementation character_data,
1510 sql_language_binding_style character_data,
1511 sql_language_programming_language character_data
1514 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'DIRECT', NULL);
1515 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
1516 INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'DIRECT', NULL);
1517 INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
1519 GRANT SELECT ON sql_languages TO PUBLIC;
1524 * SQL_PACKAGES table
1527 CREATE TABLE sql_packages (
1528 feature_id character_data,
1529 feature_name character_data,
1530 is_supported yes_or_no,
1531 is_verified_by character_data,
1532 comments character_data
1535 INSERT INTO sql_packages VALUES ('PKG000', 'Core', 'NO', NULL, '');
1536 INSERT INTO sql_packages VALUES ('PKG001', 'Enhanced datetime facilities', 'YES', NULL, '');
1537 INSERT INTO sql_packages VALUES ('PKG002', 'Enhanced integrity management', 'NO', NULL, '');
1538 INSERT INTO sql_packages VALUES ('PKG003', 'OLAP facilities', 'NO', NULL, '');
1539 INSERT INTO sql_packages VALUES ('PKG004', 'PSM', 'NO', NULL, 'PL/pgSQL is similar.');
1540 INSERT INTO sql_packages VALUES ('PKG005', 'CLI', 'NO', NULL, 'ODBC is similar.');
1541 INSERT INTO sql_packages VALUES ('PKG006', 'Basic object support', 'NO', NULL, '');
1542 INSERT INTO sql_packages VALUES ('PKG007', 'Enhanced object support', 'NO', NULL, '');
1543 INSERT INTO sql_packages VALUES ('PKG008', 'Active database', 'NO', NULL, '');
1544 INSERT INTO sql_packages VALUES ('PKG010', 'OLAP', 'NO', NULL, 'NO');
1546 GRANT SELECT ON sql_packages TO PUBLIC;
1554 CREATE TABLE sql_parts (
1555 feature_id character_data,
1556 feature_name character_data,
1557 is_supported yes_or_no,
1558 is_verified_by character_data,
1559 comments character_data
1562 INSERT INTO sql_parts VALUES ('1', 'Framework (SQL/Framework)', 'NO', NULL, '');
1563 INSERT INTO sql_parts VALUES ('2', 'Foundation (SQL/Foundation)', 'NO', NULL, '');
1564 INSERT INTO sql_parts VALUES ('3', 'Call-Level Interface (SQL/CLI)', 'NO', NULL, '');
1565 INSERT INTO sql_parts VALUES ('4', 'Persistent Stored Modules (SQL/PSM)', 'NO', NULL, '');
1566 INSERT INTO sql_parts VALUES ('9', 'Management of External Data (SQL/MED)', 'NO', NULL, '');
1567 INSERT INTO sql_parts VALUES ('10', 'Object Language Bindings (SQL/OLB)', 'NO', NULL, '');
1568 INSERT INTO sql_parts VALUES ('11', 'Information and Definition Schema (SQL/Schemata)', 'NO', NULL, '');
1569 INSERT INTO sql_parts VALUES ('13', 'Routines and Types Using the Java Programming Language (SQL/JRT)', 'NO', NULL, '');
1570 INSERT INTO sql_parts VALUES ('14', 'XML-Related Specifications (SQL/XML)', 'YES', NULL, '');
1578 -- Note: Sizing items are defined in ISO/IEC 9075-3:2008, clause 9.2.
1580 CREATE TABLE sql_sizing (
1581 sizing_id cardinal_number,
1582 sizing_name character_data,
1583 supported_value cardinal_number,
1584 comments character_data
1587 INSERT INTO sql_sizing VALUES (34, 'MAXIMUM CATALOG NAME LENGTH', 63, NULL);
1588 INSERT INTO sql_sizing VALUES (30, 'MAXIMUM COLUMN NAME LENGTH', 63, NULL);
1589 INSERT INTO sql_sizing VALUES (97, 'MAXIMUM COLUMNS IN GROUP BY', 0, NULL);
1590 INSERT INTO sql_sizing VALUES (99, 'MAXIMUM COLUMNS IN ORDER BY', 0, NULL);
1591 INSERT INTO sql_sizing VALUES (100, 'MAXIMUM COLUMNS IN SELECT', 1664, NULL); -- match MaxTupleAttributeNumber
1592 INSERT INTO sql_sizing VALUES (101, 'MAXIMUM COLUMNS IN TABLE', 1600, NULL); -- match MaxHeapAttributeNumber
1593 INSERT INTO sql_sizing VALUES (1, 'MAXIMUM CONCURRENT ACTIVITIES', 0, NULL);
1594 INSERT INTO sql_sizing VALUES (31, 'MAXIMUM CURSOR NAME LENGTH', 63, NULL);
1595 INSERT INTO sql_sizing VALUES (0, 'MAXIMUM DRIVER CONNECTIONS', NULL, NULL);
1596 INSERT INTO sql_sizing VALUES (10005, 'MAXIMUM IDENTIFIER LENGTH', 63, NULL);
1597 INSERT INTO sql_sizing VALUES (32, 'MAXIMUM SCHEMA NAME LENGTH', 63, NULL);
1598 INSERT INTO sql_sizing VALUES (20000, 'MAXIMUM STATEMENT OCTETS', 0, NULL);
1599 INSERT INTO sql_sizing VALUES (20001, 'MAXIMUM STATEMENT OCTETS DATA', 0, NULL);
1600 INSERT INTO sql_sizing VALUES (20002, 'MAXIMUM STATEMENT OCTETS SCHEMA', 0, NULL);
1601 INSERT INTO sql_sizing VALUES (35, 'MAXIMUM TABLE NAME LENGTH', 63, NULL);
1602 INSERT INTO sql_sizing VALUES (106, 'MAXIMUM TABLES IN SELECT', 0, NULL);
1603 INSERT INTO sql_sizing VALUES (107, 'MAXIMUM USER NAME LENGTH', 63, NULL);
1604 INSERT INTO sql_sizing VALUES (25000, 'MAXIMUM CURRENT DEFAULT TRANSFORM GROUP LENGTH', NULL, NULL);
1605 INSERT INTO sql_sizing VALUES (25001, 'MAXIMUM CURRENT TRANSFORM GROUP LENGTH', NULL, NULL);
1606 INSERT INTO sql_sizing VALUES (25002, 'MAXIMUM CURRENT PATH LENGTH', 0, NULL);
1607 INSERT INTO sql_sizing VALUES (25003, 'MAXIMUM CURRENT ROLE LENGTH', NULL, NULL);
1608 INSERT INTO sql_sizing VALUES (25004, 'MAXIMUM SESSION USER LENGTH', 63, NULL);
1609 INSERT INTO sql_sizing VALUES (25005, 'MAXIMUM SYSTEM USER LENGTH', 63, NULL);
1612 SET supported_value = (SELECT typlen-1 FROM pg_catalog.pg_type WHERE typname = 'name'),
1613 comments = 'Might be less, depending on character set.'
1614 WHERE supported_value = 63;
1616 GRANT SELECT ON sql_sizing TO PUBLIC;
1621 * SQL_SIZING_PROFILES table
1624 -- The data in this table are defined by various profiles of SQL.
1625 -- Since we don't have any information about such profiles, we provide
1628 CREATE TABLE sql_sizing_profiles (
1629 sizing_id cardinal_number,
1630 sizing_name character_data,
1631 profile_id character_data,
1632 required_value cardinal_number,
1633 comments character_data
1636 GRANT SELECT ON sql_sizing_profiles TO PUBLIC;
1641 * TABLE_CONSTRAINTS view
1644 CREATE VIEW table_constraints AS
1645 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1646 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
1647 CAST(c.conname AS sql_identifier) AS constraint_name,
1648 CAST(current_database() AS sql_identifier) AS table_catalog,
1649 CAST(nr.nspname AS sql_identifier) AS table_schema,
1650 CAST(r.relname AS sql_identifier) AS table_name,
1652 CASE c.contype WHEN 'c' THEN 'CHECK'
1653 WHEN 'f' THEN 'FOREIGN KEY'
1654 WHEN 'p' THEN 'PRIMARY KEY'
1655 WHEN 'u' THEN 'UNIQUE' END
1656 AS character_data) AS constraint_type,
1657 CAST(CASE WHEN c.condeferrable THEN 'YES' ELSE 'NO' END AS yes_or_no)
1659 CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS yes_or_no)
1660 AS initially_deferred
1662 FROM pg_namespace nc,
1667 WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace
1668 AND c.conrelid = r.oid
1669 AND c.contype <> 'x' -- ignore nonstandard exclusion constraints
1671 AND (NOT pg_is_other_temp_schema(nr.oid))
1672 AND (pg_has_role(r.relowner, 'USAGE')
1673 -- SELECT privilege omitted, per SQL standard
1674 OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1675 OR has_any_column_privilege(r.oid, 'INSERT, UPDATE, REFERENCES') )
1679 -- not-null constraints
1681 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1682 CAST(nr.nspname AS sql_identifier) AS constraint_schema,
1683 CAST(CAST(nr.oid AS text) || '_' || CAST(r.oid AS text) || '_' || CAST(a.attnum AS text) || '_not_null' AS sql_identifier) AS constraint_name, -- XXX
1684 CAST(current_database() AS sql_identifier) AS table_catalog,
1685 CAST(nr.nspname AS sql_identifier) AS table_schema,
1686 CAST(r.relname AS sql_identifier) AS table_name,
1687 CAST('CHECK' AS character_data) AS constraint_type,
1688 CAST('NO' AS yes_or_no) AS is_deferrable,
1689 CAST('NO' AS yes_or_no) AS initially_deferred
1691 FROM pg_namespace nr,
1695 WHERE nr.oid = r.relnamespace
1696 AND r.oid = a.attrelid
1699 AND NOT a.attisdropped
1701 AND (NOT pg_is_other_temp_schema(nr.oid))
1702 AND (pg_has_role(r.relowner, 'USAGE')
1703 -- SELECT privilege omitted, per SQL standard
1704 OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1705 OR has_any_column_privilege(r.oid, 'INSERT, UPDATE, REFERENCES') );
1707 GRANT SELECT ON table_constraints TO PUBLIC;
1712 * TABLE_METHOD_PRIVILEGES view
1715 -- feature not supported
1720 * TABLE_PRIVILEGES view
1723 CREATE VIEW table_privileges AS
1724 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1725 CAST(grantee.rolname AS sql_identifier) AS grantee,
1726 CAST(current_database() AS sql_identifier) AS table_catalog,
1727 CAST(nc.nspname AS sql_identifier) AS table_schema,
1728 CAST(c.relname AS sql_identifier) AS table_name,
1729 CAST(c.prtype AS character_data) AS privilege_type,
1732 -- object owner always has grant options
1733 pg_has_role(grantee.oid, c.relowner, 'USAGE')
1735 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable,
1736 CAST('NO' AS yes_or_no) AS with_hierarchy
1739 SELECT oid, relname, relnamespace, relkind, relowner, (aclexplode(relacl)).* FROM pg_class
1740 ) AS c (oid, relname, relnamespace, relkind, relowner, grantor, grantee, prtype, grantable),
1742 pg_authid u_grantor,
1744 SELECT oid, rolname FROM pg_authid
1746 SELECT 0::oid, 'PUBLIC'
1747 ) AS grantee (oid, rolname)
1749 WHERE c.relnamespace = nc.oid
1750 AND c.relkind IN ('r', 'v')
1751 AND c.grantee = grantee.oid
1752 AND c.grantor = u_grantor.oid
1753 AND c.prtype IN ('INSERT', 'SELECT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER')
1754 AND (pg_has_role(u_grantor.oid, 'USAGE')
1755 OR pg_has_role(grantee.oid, 'USAGE')
1756 OR grantee.rolname = 'PUBLIC');
1758 GRANT SELECT ON table_privileges TO PUBLIC;
1763 * ROLE_TABLE_GRANTS view
1766 CREATE VIEW role_table_grants AS
1775 FROM table_privileges
1776 WHERE grantor IN (SELECT role_name FROM enabled_roles)
1777 OR grantee IN (SELECT role_name FROM enabled_roles);
1779 GRANT SELECT ON role_table_grants TO PUBLIC;
1787 CREATE VIEW tables AS
1788 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
1789 CAST(nc.nspname AS sql_identifier) AS table_schema,
1790 CAST(c.relname AS sql_identifier) AS table_name,
1793 CASE WHEN nc.oid = pg_my_temp_schema() THEN 'LOCAL TEMPORARY'
1794 WHEN c.relkind = 'r' THEN 'BASE TABLE'
1795 WHEN c.relkind = 'v' THEN 'VIEW'
1797 AS character_data) AS table_type,
1799 CAST(null AS sql_identifier) AS self_referencing_column_name,
1800 CAST(null AS character_data) AS reference_generation,
1802 CAST(null AS sql_identifier) AS user_defined_type_catalog,
1803 CAST(null AS sql_identifier) AS user_defined_type_schema,
1804 CAST(null AS sql_identifier) AS user_defined_type_name,
1806 CAST(CASE WHEN c.relkind = 'r'
1808 AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '3' AND is_instead))
1809 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_insertable_into,
1811 CAST('NO' AS yes_or_no) AS is_typed,
1813 CASE WHEN nc.oid = pg_my_temp_schema() THEN 'PRESERVE' -- FIXME
1815 AS character_data) AS commit_action
1817 FROM pg_namespace nc, pg_class c
1819 WHERE c.relnamespace = nc.oid
1820 AND c.relkind IN ('r', 'v')
1821 AND (NOT pg_is_other_temp_schema(nc.oid))
1822 AND (pg_has_role(c.relowner, 'USAGE')
1823 OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1824 OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES') );
1826 GRANT SELECT ON tables TO PUBLIC;
1834 -- feature not supported
1842 -- feature not supported
1847 * TRIGGERED_UPDATE_COLUMNS view
1850 -- PostgreSQL doesn't allow the specification of individual triggered
1851 -- update columns, so this view is empty.
1853 CREATE VIEW triggered_update_columns AS
1854 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1855 CAST(null AS sql_identifier) AS trigger_schema,
1856 CAST(null AS sql_identifier) AS trigger_name,
1857 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1858 CAST(null AS sql_identifier) AS event_object_schema,
1859 CAST(null AS sql_identifier) AS event_object_table,
1860 CAST(null AS sql_identifier) AS event_object_column
1863 GRANT SELECT ON triggered_update_columns TO PUBLIC;
1868 * TRIGGER_COLUMN_USAGE view
1871 -- not tracked by PostgreSQL
1876 * TRIGGER_ROUTINE_USAGE view
1879 -- not tracked by PostgreSQL
1884 * TRIGGER_SEQUENCE_USAGE view
1887 -- not tracked by PostgreSQL
1892 * TRIGGER_TABLE_USAGE view
1895 -- not tracked by PostgreSQL
1903 CREATE VIEW triggers AS
1904 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1905 CAST(n.nspname AS sql_identifier) AS trigger_schema,
1906 CAST(t.tgname AS sql_identifier) AS trigger_name,
1907 CAST(em.text AS character_data) AS event_manipulation,
1908 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1909 CAST(n.nspname AS sql_identifier) AS event_object_schema,
1910 CAST(c.relname AS sql_identifier) AS event_object_table,
1911 CAST(null AS cardinal_number) AS action_order,
1912 -- XXX strange hacks follow
1914 CASE WHEN pg_has_role(c.relowner, 'USAGE')
1915 THEN (SELECT m[1] FROM regexp_matches(pg_get_triggerdef(t.oid), E'.{35,} WHEN \\((.+)\\) EXECUTE PROCEDURE') AS rm(m) LIMIT 1)
1917 AS character_data) AS action_condition,
1919 substring(pg_get_triggerdef(t.oid) from
1920 position('EXECUTE PROCEDURE' in substring(pg_get_triggerdef(t.oid) from 48)) + 47)
1921 AS character_data) AS action_statement,
1923 CASE WHEN t.tgtype & 1 = 1 THEN 'ROW' ELSE 'STATEMENT' END
1924 AS character_data) AS action_orientation,
1926 CASE WHEN t.tgtype & 2 = 2 THEN 'BEFORE' ELSE 'AFTER' END
1927 AS character_data) AS condition_timing,
1928 CAST(null AS sql_identifier) AS condition_reference_old_table,
1929 CAST(null AS sql_identifier) AS condition_reference_new_table,
1930 CAST(null AS sql_identifier) AS condition_reference_old_row,
1931 CAST(null AS sql_identifier) AS condition_reference_new_row,
1932 CAST(null AS time_stamp) AS created
1934 FROM pg_namespace n, pg_class c, pg_trigger t,
1935 (VALUES (4, 'INSERT'),
1937 (16, 'UPDATE')) AS em (num, text)
1939 WHERE n.oid = c.relnamespace
1940 AND c.oid = t.tgrelid
1941 AND t.tgtype & em.num <> 0
1942 AND NOT t.tgisconstraint
1943 AND (NOT pg_is_other_temp_schema(n.oid))
1944 AND (pg_has_role(c.relowner, 'USAGE')
1945 -- SELECT privilege omitted, per SQL standard
1946 OR has_table_privilege(c.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1947 OR has_any_column_privilege(c.oid, 'INSERT, UPDATE, REFERENCES') );
1949 GRANT SELECT ON triggers TO PUBLIC;
1954 * UDT_PRIVILEGES view
1957 -- feature not supported
1962 * USAGE_PRIVILEGES view
1965 CREATE VIEW usage_privileges AS
1968 -- Domains have no real privileges, so we represent all domains with implicit usage privilege here.
1969 SELECT CAST(u.rolname AS sql_identifier) AS grantor,
1970 CAST('PUBLIC' AS sql_identifier) AS grantee,
1971 CAST(current_database() AS sql_identifier) AS object_catalog,
1972 CAST(n.nspname AS sql_identifier) AS object_schema,
1973 CAST(t.typname AS sql_identifier) AS object_name,
1974 CAST('DOMAIN' AS character_data) AS object_type,
1975 CAST('USAGE' AS character_data) AS privilege_type,
1976 CAST('NO' AS yes_or_no) AS is_grantable
1982 WHERE u.oid = t.typowner
1983 AND t.typnamespace = n.oid
1988 /* foreign-data wrappers */
1989 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1990 CAST(grantee.rolname AS sql_identifier) AS grantee,
1991 CAST(current_database() AS sql_identifier) AS object_catalog,
1992 CAST('' AS sql_identifier) AS object_schema,
1993 CAST(fdw.fdwname AS sql_identifier) AS object_name,
1994 CAST('FOREIGN DATA WRAPPER' AS character_data) AS object_type,
1995 CAST('USAGE' AS character_data) AS privilege_type,
1998 -- object owner always has grant options
1999 pg_has_role(grantee.oid, fdw.fdwowner, 'USAGE')
2001 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2004 SELECT fdwname, fdwowner, (aclexplode(fdwacl)).* FROM pg_foreign_data_wrapper
2005 ) AS fdw (fdwname, fdwowner, grantor, grantee, prtype, grantable),
2006 pg_authid u_grantor,
2008 SELECT oid, rolname FROM pg_authid
2010 SELECT 0::oid, 'PUBLIC'
2011 ) AS grantee (oid, rolname)
2013 WHERE u_grantor.oid = fdw.grantor
2014 AND grantee.oid = fdw.grantee
2015 AND fdw.prtype IN ('USAGE')
2016 AND (pg_has_role(u_grantor.oid, 'USAGE')
2017 OR pg_has_role(grantee.oid, 'USAGE')
2018 OR grantee.rolname = 'PUBLIC')
2022 /* foreign servers */
2023 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2024 CAST(grantee.rolname AS sql_identifier) AS grantee,
2025 CAST(current_database() AS sql_identifier) AS object_catalog,
2026 CAST('' AS sql_identifier) AS object_schema,
2027 CAST(srv.srvname AS sql_identifier) AS object_name,
2028 CAST('FOREIGN SERVER' AS character_data) AS object_type,
2029 CAST('USAGE' AS character_data) AS privilege_type,
2032 -- object owner always has grant options
2033 pg_has_role(grantee.oid, srv.srvowner, 'USAGE')
2035 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2038 SELECT srvname, srvowner, (aclexplode(srvacl)).* FROM pg_foreign_server
2039 ) AS srv (srvname, srvowner, grantor, grantee, prtype, grantable),
2040 pg_authid u_grantor,
2042 SELECT oid, rolname FROM pg_authid
2044 SELECT 0::oid, 'PUBLIC'
2045 ) AS grantee (oid, rolname)
2047 WHERE u_grantor.oid = srv.grantor
2048 AND grantee.oid = srv.grantee
2049 AND srv.prtype IN ('USAGE')
2050 AND (pg_has_role(u_grantor.oid, 'USAGE')
2051 OR pg_has_role(grantee.oid, 'USAGE')
2052 OR grantee.rolname = 'PUBLIC');
2054 GRANT SELECT ON usage_privileges TO PUBLIC;
2059 * ROLE_USAGE_GRANTS view
2062 CREATE VIEW role_usage_grants AS
2071 FROM usage_privileges
2072 WHERE grantor IN (SELECT role_name FROM enabled_roles)
2073 OR grantee IN (SELECT role_name FROM enabled_roles);
2075 GRANT SELECT ON role_usage_grants TO PUBLIC;
2080 * USER_DEFINED_TYPES view
2083 -- feature not supported
2091 CREATE VIEW view_column_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,
2099 CAST(a.attname AS sql_identifier) AS column_name
2101 FROM pg_namespace nv, pg_class v, pg_depend dv,
2102 pg_depend dt, pg_class t, pg_namespace nt,
2105 WHERE nv.oid = v.relnamespace
2107 AND v.oid = dv.refobjid
2108 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2109 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2110 AND dv.deptype = 'i'
2111 AND dv.objid = dt.objid
2112 AND dv.refobjid <> dt.refobjid
2113 AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2114 AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2115 AND dt.refobjid = t.oid
2116 AND t.relnamespace = nt.oid
2117 AND t.relkind IN ('r', 'v')
2118 AND t.oid = a.attrelid
2119 AND dt.refobjsubid = a.attnum
2120 AND pg_has_role(t.relowner, 'USAGE');
2122 GRANT SELECT ON view_column_usage TO PUBLIC;
2127 * VIEW_ROUTINE_USAGE
2130 CREATE VIEW view_routine_usage AS
2132 CAST(current_database() AS sql_identifier) AS table_catalog,
2133 CAST(nv.nspname AS sql_identifier) AS table_schema,
2134 CAST(v.relname AS sql_identifier) AS table_name,
2135 CAST(current_database() AS sql_identifier) AS specific_catalog,
2136 CAST(np.nspname AS sql_identifier) AS specific_schema,
2137 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name
2139 FROM pg_namespace nv, pg_class v, pg_depend dv,
2140 pg_depend dp, pg_proc p, pg_namespace np
2142 WHERE nv.oid = v.relnamespace
2144 AND v.oid = dv.refobjid
2145 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2146 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2147 AND dv.deptype = 'i'
2148 AND dv.objid = dp.objid
2149 AND dp.classid = 'pg_catalog.pg_rewrite'::regclass
2150 AND dp.refclassid = 'pg_catalog.pg_proc'::regclass
2151 AND dp.refobjid = p.oid
2152 AND p.pronamespace = np.oid
2153 AND pg_has_role(p.proowner, 'USAGE');
2155 GRANT SELECT ON view_routine_usage TO PUBLIC;
2163 CREATE VIEW view_table_usage AS
2165 CAST(current_database() AS sql_identifier) AS view_catalog,
2166 CAST(nv.nspname AS sql_identifier) AS view_schema,
2167 CAST(v.relname AS sql_identifier) AS view_name,
2168 CAST(current_database() AS sql_identifier) AS table_catalog,
2169 CAST(nt.nspname AS sql_identifier) AS table_schema,
2170 CAST(t.relname AS sql_identifier) AS table_name
2172 FROM pg_namespace nv, pg_class v, pg_depend dv,
2173 pg_depend dt, pg_class t, pg_namespace nt
2175 WHERE nv.oid = v.relnamespace
2177 AND v.oid = dv.refobjid
2178 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2179 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2180 AND dv.deptype = 'i'
2181 AND dv.objid = dt.objid
2182 AND dv.refobjid <> dt.refobjid
2183 AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2184 AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2185 AND dt.refobjid = t.oid
2186 AND t.relnamespace = nt.oid
2187 AND t.relkind IN ('r', 'v')
2188 AND pg_has_role(t.relowner, 'USAGE');
2190 GRANT SELECT ON view_table_usage TO PUBLIC;
2198 CREATE VIEW views AS
2199 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
2200 CAST(nc.nspname AS sql_identifier) AS table_schema,
2201 CAST(c.relname AS sql_identifier) AS table_name,
2204 CASE WHEN pg_has_role(c.relowner, 'USAGE')
2205 THEN pg_get_viewdef(c.oid)
2207 AS character_data) AS view_definition,
2209 CAST('NONE' AS character_data) AS check_option,
2212 CASE WHEN EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '2' AND is_instead)
2213 AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '4' AND is_instead)
2214 THEN 'YES' ELSE 'NO' END
2215 AS yes_or_no) AS is_updatable,
2218 CASE WHEN EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '3' AND is_instead)
2219 THEN 'YES' ELSE 'NO' END
2220 AS yes_or_no) AS is_insertable_into,
2222 CAST('NO' AS yes_or_no) AS is_trigger_updatable,
2223 CAST('NO' AS yes_or_no) AS is_trigger_deletable,
2224 CAST('NO' AS yes_or_no) AS is_trigger_insertable_into
2226 FROM pg_namespace nc, pg_class c
2228 WHERE c.relnamespace = nc.oid
2230 AND (NOT pg_is_other_temp_schema(nc.oid))
2231 AND (pg_has_role(c.relowner, 'USAGE')
2232 OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
2233 OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES') );
2235 GRANT SELECT ON views TO PUBLIC;
2238 -- The following views have dependencies that force them to appear out of order.
2242 * DATA_TYPE_PRIVILEGES view
2245 CREATE VIEW data_type_privileges AS
2246 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2247 CAST(x.objschema AS sql_identifier) AS object_schema,
2248 CAST(x.objname AS sql_identifier) AS object_name,
2249 CAST(x.objtype AS character_data) AS object_type,
2250 CAST(x.objdtdid AS sql_identifier) AS dtd_identifier
2254 SELECT udt_schema, udt_name, 'USER-DEFINED TYPE'::text, dtd_identifier FROM attributes
2256 SELECT table_schema, table_name, 'TABLE'::text, dtd_identifier FROM columns
2258 SELECT domain_schema, domain_name, 'DOMAIN'::text, dtd_identifier FROM domains
2260 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM parameters
2262 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM routines
2263 ) AS x (objschema, objname, objtype, objdtdid);
2265 GRANT SELECT ON data_type_privileges TO PUBLIC;
2270 * ELEMENT_TYPES view
2273 CREATE VIEW element_types AS
2274 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2275 CAST(n.nspname AS sql_identifier) AS object_schema,
2276 CAST(x.objname AS sql_identifier) AS object_name,
2277 CAST(x.objtype AS character_data) AS object_type,
2278 CAST(x.objdtdid AS sql_identifier) AS collection_type_identifier,
2280 CASE WHEN nbt.nspname = 'pg_catalog' THEN format_type(bt.oid, null)
2281 ELSE 'USER-DEFINED' END AS character_data) AS data_type,
2283 CAST(null AS cardinal_number) AS character_maximum_length,
2284 CAST(null AS cardinal_number) AS character_octet_length,
2285 CAST(null AS sql_identifier) AS character_set_catalog,
2286 CAST(null AS sql_identifier) AS character_set_schema,
2287 CAST(null AS sql_identifier) AS character_set_name,
2288 CAST(null AS sql_identifier) AS collation_catalog,
2289 CAST(null AS sql_identifier) AS collation_schema,
2290 CAST(null AS sql_identifier) AS collation_name,
2291 CAST(null AS cardinal_number) AS numeric_precision,
2292 CAST(null AS cardinal_number) AS numeric_precision_radix,
2293 CAST(null AS cardinal_number) AS numeric_scale,
2294 CAST(null AS cardinal_number) AS datetime_precision,
2295 CAST(null AS character_data) AS interval_type,
2296 CAST(null AS character_data) AS interval_precision,
2298 CAST(null AS character_data) AS domain_default, -- XXX maybe a bug in the standard
2300 CAST(current_database() AS sql_identifier) AS udt_catalog,
2301 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
2302 CAST(bt.typname AS sql_identifier) AS udt_name,
2304 CAST(null AS sql_identifier) AS scope_catalog,
2305 CAST(null AS sql_identifier) AS scope_schema,
2306 CAST(null AS sql_identifier) AS scope_name,
2308 CAST(null AS cardinal_number) AS maximum_cardinality,
2309 CAST('a' || CAST(x.objdtdid AS text) AS sql_identifier) AS dtd_identifier
2311 FROM pg_namespace n, pg_type at, pg_namespace nbt, pg_type bt,
2314 SELECT c.relnamespace, CAST(c.relname AS sql_identifier),
2315 'TABLE'::text, a.attnum, a.atttypid
2316 FROM pg_class c, pg_attribute a
2317 WHERE c.oid = a.attrelid
2318 AND c.relkind IN ('r', 'v')
2319 AND attnum > 0 AND NOT attisdropped
2324 SELECT t.typnamespace, CAST(t.typname AS sql_identifier),
2325 'DOMAIN'::text, 1, t.typbasetype
2327 WHERE t.typtype = 'd'
2332 SELECT pronamespace, CAST(proname || '_' || CAST(oid AS text) AS sql_identifier),
2333 'ROUTINE'::text, (ss.x).n, (ss.x).x
2334 FROM (SELECT p.pronamespace, p.proname, p.oid,
2335 _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
2336 FROM pg_proc p) AS ss
2341 SELECT p.pronamespace, CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier),
2342 'ROUTINE'::text, 0, p.prorettype
2345 ) AS x (objschema, objname, objtype, objdtdid, objtypeid)
2347 WHERE n.oid = x.objschema
2348 AND at.oid = x.objtypeid
2349 AND (at.typelem <> 0 AND at.typlen = -1)
2350 AND at.typelem = bt.oid
2351 AND nbt.oid = bt.typnamespace
2353 AND (n.nspname, x.objname, x.objtype, CAST(x.objdtdid AS sql_identifier)) IN
2354 ( SELECT object_schema, object_name, object_type, dtd_identifier
2355 FROM data_type_privileges );
2357 GRANT SELECT ON element_types TO PUBLIC;
2360 -- SQL/MED views; these use section numbers from part 9 of the standard.
2362 /* Base view for foreign-data wrappers */
2363 CREATE VIEW _pg_foreign_data_wrappers AS
2367 CAST(current_database() AS sql_identifier) AS foreign_data_wrapper_catalog,
2368 CAST(fdwname AS sql_identifier) AS foreign_data_wrapper_name,
2369 CAST(u.rolname AS sql_identifier) AS authorization_identifier,
2370 CAST('c' AS character_data) AS foreign_data_wrapper_language
2371 FROM pg_foreign_data_wrapper w, pg_authid u
2372 WHERE u.oid = w.fdwowner
2373 AND (pg_has_role(fdwowner, 'USAGE')
2374 OR has_foreign_data_wrapper_privilege(w.oid, 'USAGE'));
2379 * FOREIGN_DATA_WRAPPER_OPTIONS view
2381 CREATE VIEW foreign_data_wrapper_options AS
2382 SELECT foreign_data_wrapper_catalog,
2383 foreign_data_wrapper_name,
2384 CAST((pg_options_to_table(w.fdwoptions)).option_name AS sql_identifier) AS option_name,
2385 CAST((pg_options_to_table(w.fdwoptions)).option_value AS character_data) AS option_value
2386 FROM _pg_foreign_data_wrappers w;
2388 GRANT SELECT ON foreign_data_wrapper_options TO PUBLIC;
2393 * FOREIGN_DATA_WRAPPERS view
2395 CREATE VIEW foreign_data_wrappers AS
2396 SELECT foreign_data_wrapper_catalog,
2397 foreign_data_wrapper_name,
2398 authorization_identifier,
2399 CAST(NULL AS character_data) AS library_name,
2400 foreign_data_wrapper_language
2401 FROM _pg_foreign_data_wrappers w;
2403 GRANT SELECT ON foreign_data_wrappers TO PUBLIC;
2406 /* Base view for foreign servers */
2407 CREATE VIEW _pg_foreign_servers AS
2410 CAST(current_database() AS sql_identifier) AS foreign_server_catalog,
2411 CAST(srvname AS sql_identifier) AS foreign_server_name,
2412 CAST(current_database() AS sql_identifier) AS foreign_data_wrapper_catalog,
2413 CAST(w.fdwname AS sql_identifier) AS foreign_data_wrapper_name,
2414 CAST(srvtype AS character_data) AS foreign_server_type,
2415 CAST(srvversion AS character_data) AS foreign_server_version,
2416 CAST(u.rolname AS sql_identifier) AS authorization_identifier
2417 FROM pg_foreign_server s, pg_foreign_data_wrapper w, pg_authid u
2418 WHERE w.oid = s.srvfdw
2419 AND u.oid = s.srvowner
2420 AND (pg_has_role(s.srvowner, 'USAGE')
2421 OR has_server_privilege(s.oid, 'USAGE'));
2426 * FOREIGN_SERVER_OPTIONS view
2428 CREATE VIEW foreign_server_options AS
2429 SELECT foreign_server_catalog,
2430 foreign_server_name,
2431 CAST((pg_options_to_table(s.srvoptions)).option_name AS sql_identifier) AS option_name,
2432 CAST((pg_options_to_table(s.srvoptions)).option_value AS character_data) AS option_value
2433 FROM _pg_foreign_servers s;
2435 GRANT SELECT ON TABLE foreign_server_options TO PUBLIC;
2440 * FOREIGN_SERVERS view
2442 CREATE VIEW foreign_servers AS
2443 SELECT foreign_server_catalog,
2444 foreign_server_name,
2445 foreign_data_wrapper_catalog,
2446 foreign_data_wrapper_name,
2447 foreign_server_type,
2448 foreign_server_version,
2449 authorization_identifier
2450 FROM _pg_foreign_servers;
2452 GRANT SELECT ON foreign_servers TO PUBLIC;
2455 /* Base view for user mappings */
2456 CREATE VIEW _pg_user_mappings AS
2460 CAST(COALESCE(u.rolname,'PUBLIC') AS sql_identifier ) AS authorization_identifier,
2461 s.foreign_server_catalog,
2462 s.foreign_server_name,
2463 s.authorization_identifier AS srvowner
2464 FROM pg_user_mapping um LEFT JOIN pg_authid u ON (u.oid = um.umuser),
2465 _pg_foreign_servers s
2466 WHERE s.oid = um.umserver;
2471 * USER_MAPPING_OPTIONS view
2473 CREATE VIEW user_mapping_options AS
2474 SELECT authorization_identifier,
2475 foreign_server_catalog,
2476 foreign_server_name,
2477 CAST((pg_options_to_table(um.umoptions)).option_name AS sql_identifier) AS option_name,
2478 CAST(CASE WHEN (umuser <> 0 AND authorization_identifier = current_user)
2479 OR (umuser = 0 AND pg_has_role(srvowner, 'USAGE'))
2480 OR (SELECT rolsuper FROM pg_authid WHERE rolname = current_user) THEN (pg_options_to_table(um.umoptions)).option_value
2481 ELSE NULL END AS character_data) AS option_value
2482 FROM _pg_user_mappings um;
2484 GRANT SELECT ON user_mapping_options TO PUBLIC;
2489 * USER_MAPPINGS view
2491 CREATE VIEW user_mappings AS
2492 SELECT authorization_identifier,
2493 foreign_server_catalog,
2495 FROM _pg_user_mappings;
2497 GRANT SELECT ON user_mappings TO PUBLIC;