2 * SQL Information Schema
3 * as defined in ISO/IEC 9075-11:2008
5 * Copyright (c) 2003-2011, PostgreSQL Global Development Group
7 * src/backend/catalog/information_schema.sql
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;
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 operator(pg_catalog.<@) $2 and $2 operator(pg_catalog.<@) $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', 'f')
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', 'f')
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', 'f')
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', 'f')
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', 'f')
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 NOT IN ('t', 'x') -- ignore nonstandard 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'
1796 WHEN c.relkind = 'f' THEN 'FOREIGN TABLE'
1798 AS character_data) AS table_type,
1800 CAST(null AS sql_identifier) AS self_referencing_column_name,
1801 CAST(null AS character_data) AS reference_generation,
1803 CAST(CASE WHEN t.typname IS NOT NULL THEN current_database() ELSE null END AS sql_identifier) AS user_defined_type_catalog,
1804 CAST(nt.nspname AS sql_identifier) AS user_defined_type_schema,
1805 CAST(t.typname AS sql_identifier) AS user_defined_type_name,
1807 CAST(CASE WHEN c.relkind = 'r'
1809 AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '3' AND is_instead))
1810 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_insertable_into,
1812 CAST(CASE WHEN t.typname IS NOT NULL THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_typed,
1814 CASE WHEN nc.oid = pg_my_temp_schema() THEN 'PRESERVE' -- FIXME
1816 AS character_data) AS commit_action
1818 FROM pg_namespace nc JOIN pg_class c ON (nc.oid = c.relnamespace)
1819 LEFT JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON (c.reloftype = t.oid)
1821 WHERE c.relkind IN ('r', 'v', 'f')
1822 AND (NOT pg_is_other_temp_schema(nc.oid))
1823 AND (pg_has_role(c.relowner, 'USAGE')
1824 OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1825 OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES') );
1827 GRANT SELECT ON tables TO PUBLIC;
1835 -- feature not supported
1843 -- feature not supported
1848 * TRIGGERED_UPDATE_COLUMNS view
1851 CREATE VIEW triggered_update_columns AS
1852 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1853 CAST(n.nspname AS sql_identifier) AS trigger_schema,
1854 CAST(t.tgname AS sql_identifier) AS trigger_name,
1855 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1856 CAST(n.nspname AS sql_identifier) AS event_object_schema,
1857 CAST(c.relname AS sql_identifier) AS event_object_table,
1858 CAST(a.attname AS sql_identifier) AS event_object_column
1860 FROM pg_namespace n, pg_class c, pg_trigger t,
1861 (SELECT tgoid, (ta0.tgat).x AS tgattnum, (ta0.tgat).n AS tgattpos
1862 FROM (SELECT oid AS tgoid, information_schema._pg_expandarray(tgattr) AS tgat FROM pg_trigger) AS ta0) AS ta,
1865 WHERE n.oid = c.relnamespace
1866 AND c.oid = t.tgrelid
1867 AND t.oid = ta.tgoid
1868 AND (a.attrelid, a.attnum) = (t.tgrelid, ta.tgattnum)
1869 AND NOT t.tgisinternal
1870 AND (NOT pg_is_other_temp_schema(n.oid))
1871 AND (pg_has_role(c.relowner, 'USAGE')
1872 -- SELECT privilege omitted, per SQL standard
1873 OR has_column_privilege(c.oid, a.attnum, 'INSERT, UPDATE, REFERENCES') );
1875 GRANT SELECT ON triggered_update_columns TO PUBLIC;
1880 * TRIGGER_COLUMN_USAGE view
1883 -- not tracked by PostgreSQL
1888 * TRIGGER_ROUTINE_USAGE view
1891 -- not tracked by PostgreSQL
1896 * TRIGGER_SEQUENCE_USAGE view
1899 -- not tracked by PostgreSQL
1904 * TRIGGER_TABLE_USAGE view
1907 -- not tracked by PostgreSQL
1915 CREATE VIEW triggers AS
1916 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1917 CAST(n.nspname AS sql_identifier) AS trigger_schema,
1918 CAST(t.tgname AS sql_identifier) AS trigger_name,
1919 CAST(em.text AS character_data) AS event_manipulation,
1920 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1921 CAST(n.nspname AS sql_identifier) AS event_object_schema,
1922 CAST(c.relname AS sql_identifier) AS event_object_table,
1923 CAST(null AS cardinal_number) AS action_order,
1924 -- XXX strange hacks follow
1926 CASE WHEN pg_has_role(c.relowner, 'USAGE')
1927 THEN (SELECT m[1] FROM regexp_matches(pg_get_triggerdef(t.oid), E'.{35,} WHEN \\((.+)\\) EXECUTE PROCEDURE') AS rm(m) LIMIT 1)
1929 AS character_data) AS action_condition,
1931 substring(pg_get_triggerdef(t.oid) from
1932 position('EXECUTE PROCEDURE' in substring(pg_get_triggerdef(t.oid) from 48)) + 47)
1933 AS character_data) AS action_statement,
1935 -- hard-wired reference to TRIGGER_TYPE_ROW
1936 CASE t.tgtype & 1 WHEN 1 THEN 'ROW' ELSE 'STATEMENT' END
1937 AS character_data) AS action_orientation,
1939 -- hard-wired refs to TRIGGER_TYPE_BEFORE, TRIGGER_TYPE_INSTEAD
1940 CASE t.tgtype & 66 WHEN 2 THEN 'BEFORE' WHEN 64 THEN 'INSTEAD OF' ELSE 'AFTER' END
1941 AS character_data) AS action_timing,
1942 CAST(null AS sql_identifier) AS action_reference_old_table,
1943 CAST(null AS sql_identifier) AS action_reference_new_table,
1944 CAST(null AS sql_identifier) AS action_reference_old_row,
1945 CAST(null AS sql_identifier) AS action_reference_new_row,
1946 CAST(null AS time_stamp) AS created
1948 FROM pg_namespace n, pg_class c, pg_trigger t,
1949 -- hard-wired refs to TRIGGER_TYPE_INSERT, TRIGGER_TYPE_DELETE,
1950 -- TRIGGER_TYPE_UPDATE; we intentionally omit TRIGGER_TYPE_TRUNCATE
1951 (VALUES (4, 'INSERT'),
1953 (16, 'UPDATE')) AS em (num, text)
1955 WHERE n.oid = c.relnamespace
1956 AND c.oid = t.tgrelid
1957 AND t.tgtype & em.num <> 0
1958 AND NOT t.tgisinternal
1959 AND (NOT pg_is_other_temp_schema(n.oid))
1960 AND (pg_has_role(c.relowner, 'USAGE')
1961 -- SELECT privilege omitted, per SQL standard
1962 OR has_table_privilege(c.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1963 OR has_any_column_privilege(c.oid, 'INSERT, UPDATE, REFERENCES') );
1965 GRANT SELECT ON triggers TO PUBLIC;
1970 * UDT_PRIVILEGES view
1973 -- feature not supported
1978 * USAGE_PRIVILEGES view
1981 CREATE VIEW usage_privileges AS
1984 -- Domains have no real privileges, so we represent all domains with implicit usage privilege here.
1985 SELECT CAST(u.rolname AS sql_identifier) AS grantor,
1986 CAST('PUBLIC' AS sql_identifier) AS grantee,
1987 CAST(current_database() AS sql_identifier) AS object_catalog,
1988 CAST(n.nspname AS sql_identifier) AS object_schema,
1989 CAST(t.typname AS sql_identifier) AS object_name,
1990 CAST('DOMAIN' AS character_data) AS object_type,
1991 CAST('USAGE' AS character_data) AS privilege_type,
1992 CAST('NO' AS yes_or_no) AS is_grantable
1998 WHERE u.oid = t.typowner
1999 AND t.typnamespace = n.oid
2004 /* foreign-data wrappers */
2005 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2006 CAST(grantee.rolname AS sql_identifier) AS grantee,
2007 CAST(current_database() AS sql_identifier) AS object_catalog,
2008 CAST('' AS sql_identifier) AS object_schema,
2009 CAST(fdw.fdwname AS sql_identifier) AS object_name,
2010 CAST('FOREIGN DATA WRAPPER' AS character_data) AS object_type,
2011 CAST('USAGE' AS character_data) AS privilege_type,
2014 -- object owner always has grant options
2015 pg_has_role(grantee.oid, fdw.fdwowner, 'USAGE')
2017 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2020 SELECT fdwname, fdwowner, (aclexplode(fdwacl)).* FROM pg_foreign_data_wrapper
2021 ) AS fdw (fdwname, fdwowner, grantor, grantee, prtype, grantable),
2022 pg_authid u_grantor,
2024 SELECT oid, rolname FROM pg_authid
2026 SELECT 0::oid, 'PUBLIC'
2027 ) AS grantee (oid, rolname)
2029 WHERE u_grantor.oid = fdw.grantor
2030 AND grantee.oid = fdw.grantee
2031 AND fdw.prtype IN ('USAGE')
2032 AND (pg_has_role(u_grantor.oid, 'USAGE')
2033 OR pg_has_role(grantee.oid, 'USAGE')
2034 OR grantee.rolname = 'PUBLIC')
2038 /* foreign servers */
2039 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2040 CAST(grantee.rolname AS sql_identifier) AS grantee,
2041 CAST(current_database() AS sql_identifier) AS object_catalog,
2042 CAST('' AS sql_identifier) AS object_schema,
2043 CAST(srv.srvname AS sql_identifier) AS object_name,
2044 CAST('FOREIGN SERVER' AS character_data) AS object_type,
2045 CAST('USAGE' AS character_data) AS privilege_type,
2048 -- object owner always has grant options
2049 pg_has_role(grantee.oid, srv.srvowner, 'USAGE')
2051 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2054 SELECT srvname, srvowner, (aclexplode(srvacl)).* FROM pg_foreign_server
2055 ) AS srv (srvname, srvowner, grantor, grantee, prtype, grantable),
2056 pg_authid u_grantor,
2058 SELECT oid, rolname FROM pg_authid
2060 SELECT 0::oid, 'PUBLIC'
2061 ) AS grantee (oid, rolname)
2063 WHERE u_grantor.oid = srv.grantor
2064 AND grantee.oid = srv.grantee
2065 AND srv.prtype IN ('USAGE')
2066 AND (pg_has_role(u_grantor.oid, 'USAGE')
2067 OR pg_has_role(grantee.oid, 'USAGE')
2068 OR grantee.rolname = 'PUBLIC');
2070 GRANT SELECT ON usage_privileges TO PUBLIC;
2075 * ROLE_USAGE_GRANTS view
2078 CREATE VIEW role_usage_grants AS
2087 FROM usage_privileges
2088 WHERE grantor IN (SELECT role_name FROM enabled_roles)
2089 OR grantee IN (SELECT role_name FROM enabled_roles);
2091 GRANT SELECT ON role_usage_grants TO PUBLIC;
2096 * USER_DEFINED_TYPES view
2099 -- feature not supported
2107 CREATE VIEW view_column_usage AS
2109 CAST(current_database() AS sql_identifier) AS view_catalog,
2110 CAST(nv.nspname AS sql_identifier) AS view_schema,
2111 CAST(v.relname AS sql_identifier) AS view_name,
2112 CAST(current_database() AS sql_identifier) AS table_catalog,
2113 CAST(nt.nspname AS sql_identifier) AS table_schema,
2114 CAST(t.relname AS sql_identifier) AS table_name,
2115 CAST(a.attname AS sql_identifier) AS column_name
2117 FROM pg_namespace nv, pg_class v, pg_depend dv,
2118 pg_depend dt, pg_class t, pg_namespace nt,
2121 WHERE nv.oid = v.relnamespace
2123 AND v.oid = dv.refobjid
2124 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2125 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2126 AND dv.deptype = 'i'
2127 AND dv.objid = dt.objid
2128 AND dv.refobjid <> dt.refobjid
2129 AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2130 AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2131 AND dt.refobjid = t.oid
2132 AND t.relnamespace = nt.oid
2133 AND t.relkind IN ('r', 'v', 'f')
2134 AND t.oid = a.attrelid
2135 AND dt.refobjsubid = a.attnum
2136 AND pg_has_role(t.relowner, 'USAGE');
2138 GRANT SELECT ON view_column_usage TO PUBLIC;
2143 * VIEW_ROUTINE_USAGE
2146 CREATE VIEW view_routine_usage AS
2148 CAST(current_database() AS sql_identifier) AS table_catalog,
2149 CAST(nv.nspname AS sql_identifier) AS table_schema,
2150 CAST(v.relname AS sql_identifier) AS table_name,
2151 CAST(current_database() AS sql_identifier) AS specific_catalog,
2152 CAST(np.nspname AS sql_identifier) AS specific_schema,
2153 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name
2155 FROM pg_namespace nv, pg_class v, pg_depend dv,
2156 pg_depend dp, pg_proc p, pg_namespace np
2158 WHERE nv.oid = v.relnamespace
2160 AND v.oid = dv.refobjid
2161 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2162 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2163 AND dv.deptype = 'i'
2164 AND dv.objid = dp.objid
2165 AND dp.classid = 'pg_catalog.pg_rewrite'::regclass
2166 AND dp.refclassid = 'pg_catalog.pg_proc'::regclass
2167 AND dp.refobjid = p.oid
2168 AND p.pronamespace = np.oid
2169 AND pg_has_role(p.proowner, 'USAGE');
2171 GRANT SELECT ON view_routine_usage TO PUBLIC;
2179 CREATE VIEW view_table_usage AS
2181 CAST(current_database() AS sql_identifier) AS view_catalog,
2182 CAST(nv.nspname AS sql_identifier) AS view_schema,
2183 CAST(v.relname AS sql_identifier) AS view_name,
2184 CAST(current_database() AS sql_identifier) AS table_catalog,
2185 CAST(nt.nspname AS sql_identifier) AS table_schema,
2186 CAST(t.relname AS sql_identifier) AS table_name
2188 FROM pg_namespace nv, pg_class v, pg_depend dv,
2189 pg_depend dt, pg_class t, pg_namespace nt
2191 WHERE nv.oid = v.relnamespace
2193 AND v.oid = dv.refobjid
2194 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2195 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2196 AND dv.deptype = 'i'
2197 AND dv.objid = dt.objid
2198 AND dv.refobjid <> dt.refobjid
2199 AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2200 AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2201 AND dt.refobjid = t.oid
2202 AND t.relnamespace = nt.oid
2203 AND t.relkind IN ('r', 'v', 'f')
2204 AND pg_has_role(t.relowner, 'USAGE');
2206 GRANT SELECT ON view_table_usage TO PUBLIC;
2214 CREATE VIEW views AS
2215 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
2216 CAST(nc.nspname AS sql_identifier) AS table_schema,
2217 CAST(c.relname AS sql_identifier) AS table_name,
2220 CASE WHEN pg_has_role(c.relowner, 'USAGE')
2221 THEN pg_get_viewdef(c.oid)
2223 AS character_data) AS view_definition,
2225 CAST('NONE' AS character_data) AS check_option,
2228 CASE WHEN EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '2' AND is_instead)
2229 AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '4' AND is_instead)
2230 THEN 'YES' ELSE 'NO' END
2231 AS yes_or_no) AS is_updatable,
2234 CASE WHEN EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '3' AND is_instead)
2235 THEN 'YES' ELSE 'NO' END
2236 AS yes_or_no) AS is_insertable_into,
2239 -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_UPDATE
2240 CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 81 = 81)
2241 THEN 'YES' ELSE 'NO' END
2242 AS yes_or_no) AS is_trigger_updatable,
2245 -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_DELETE
2246 CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 73 = 73)
2247 THEN 'YES' ELSE 'NO' END
2248 AS yes_or_no) AS is_trigger_deletable,
2251 -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_INSERT
2252 CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 69 = 69)
2253 THEN 'YES' ELSE 'NO' END
2254 AS yes_or_no) AS is_trigger_insertable_into
2256 FROM pg_namespace nc, pg_class c
2258 WHERE c.relnamespace = nc.oid
2260 AND (NOT pg_is_other_temp_schema(nc.oid))
2261 AND (pg_has_role(c.relowner, 'USAGE')
2262 OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
2263 OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES') );
2265 GRANT SELECT ON views TO PUBLIC;
2268 -- The following views have dependencies that force them to appear out of order.
2272 * DATA_TYPE_PRIVILEGES view
2275 CREATE VIEW data_type_privileges AS
2276 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2277 CAST(x.objschema AS sql_identifier) AS object_schema,
2278 CAST(x.objname AS sql_identifier) AS object_name,
2279 CAST(x.objtype AS character_data) AS object_type,
2280 CAST(x.objdtdid AS sql_identifier) AS dtd_identifier
2284 SELECT udt_schema, udt_name, 'USER-DEFINED TYPE'::text, dtd_identifier FROM attributes
2286 SELECT table_schema, table_name, 'TABLE'::text, dtd_identifier FROM columns
2288 SELECT domain_schema, domain_name, 'DOMAIN'::text, dtd_identifier FROM domains
2290 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM parameters
2292 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM routines
2293 ) AS x (objschema, objname, objtype, objdtdid);
2295 GRANT SELECT ON data_type_privileges TO PUBLIC;
2300 * ELEMENT_TYPES view
2303 CREATE VIEW element_types AS
2304 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2305 CAST(n.nspname AS sql_identifier) AS object_schema,
2306 CAST(x.objname AS sql_identifier) AS object_name,
2307 CAST(x.objtype AS character_data) AS object_type,
2308 CAST(x.objdtdid AS sql_identifier) AS collection_type_identifier,
2310 CASE WHEN nbt.nspname = 'pg_catalog' THEN format_type(bt.oid, null)
2311 ELSE 'USER-DEFINED' END AS character_data) AS data_type,
2313 CAST(null AS cardinal_number) AS character_maximum_length,
2314 CAST(null AS cardinal_number) AS character_octet_length,
2315 CAST(null AS sql_identifier) AS character_set_catalog,
2316 CAST(null AS sql_identifier) AS character_set_schema,
2317 CAST(null AS sql_identifier) AS character_set_name,
2318 CAST(null AS sql_identifier) AS collation_catalog,
2319 CAST(null AS sql_identifier) AS collation_schema,
2320 CAST(null AS sql_identifier) AS collation_name,
2321 CAST(null AS cardinal_number) AS numeric_precision,
2322 CAST(null AS cardinal_number) AS numeric_precision_radix,
2323 CAST(null AS cardinal_number) AS numeric_scale,
2324 CAST(null AS cardinal_number) AS datetime_precision,
2325 CAST(null AS character_data) AS interval_type,
2326 CAST(null AS character_data) AS interval_precision,
2328 CAST(null AS character_data) AS domain_default, -- XXX maybe a bug in the standard
2330 CAST(current_database() AS sql_identifier) AS udt_catalog,
2331 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
2332 CAST(bt.typname AS sql_identifier) AS udt_name,
2334 CAST(null AS sql_identifier) AS scope_catalog,
2335 CAST(null AS sql_identifier) AS scope_schema,
2336 CAST(null AS sql_identifier) AS scope_name,
2338 CAST(null AS cardinal_number) AS maximum_cardinality,
2339 CAST('a' || CAST(x.objdtdid AS text) AS sql_identifier) AS dtd_identifier
2341 FROM pg_namespace n, pg_type at, pg_namespace nbt, pg_type bt,
2344 SELECT c.relnamespace, CAST(c.relname AS sql_identifier),
2345 'TABLE'::text, a.attnum, a.atttypid
2346 FROM pg_class c, pg_attribute a
2347 WHERE c.oid = a.attrelid
2348 AND c.relkind IN ('r', 'v', 'f')
2349 AND attnum > 0 AND NOT attisdropped
2354 SELECT t.typnamespace, CAST(t.typname AS sql_identifier),
2355 'DOMAIN'::text, 1, t.typbasetype
2357 WHERE t.typtype = 'd'
2362 SELECT pronamespace, CAST(proname || '_' || CAST(oid AS text) AS sql_identifier),
2363 'ROUTINE'::text, (ss.x).n, (ss.x).x
2364 FROM (SELECT p.pronamespace, p.proname, p.oid,
2365 _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
2366 FROM pg_proc p) AS ss
2371 SELECT p.pronamespace, CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier),
2372 'ROUTINE'::text, 0, p.prorettype
2375 ) AS x (objschema, objname, objtype, objdtdid, objtypeid)
2377 WHERE n.oid = x.objschema
2378 AND at.oid = x.objtypeid
2379 AND (at.typelem <> 0 AND at.typlen = -1)
2380 AND at.typelem = bt.oid
2381 AND nbt.oid = bt.typnamespace
2383 AND (n.nspname, x.objname, x.objtype, CAST(x.objdtdid AS sql_identifier)) IN
2384 ( SELECT object_schema, object_name, object_type, dtd_identifier
2385 FROM data_type_privileges );
2387 GRANT SELECT ON element_types TO PUBLIC;
2390 -- SQL/MED views; these use section numbers from part 9 of the standard.
2392 /* Base view for foreign-data wrappers */
2393 CREATE VIEW _pg_foreign_data_wrappers AS
2397 CAST(current_database() AS sql_identifier) AS foreign_data_wrapper_catalog,
2398 CAST(fdwname AS sql_identifier) AS foreign_data_wrapper_name,
2399 CAST(u.rolname AS sql_identifier) AS authorization_identifier,
2400 CAST('c' AS character_data) AS foreign_data_wrapper_language
2401 FROM pg_foreign_data_wrapper w, pg_authid u
2402 WHERE u.oid = w.fdwowner
2403 AND (pg_has_role(fdwowner, 'USAGE')
2404 OR has_foreign_data_wrapper_privilege(w.oid, 'USAGE'));
2409 * FOREIGN_DATA_WRAPPER_OPTIONS view
2411 CREATE VIEW foreign_data_wrapper_options AS
2412 SELECT foreign_data_wrapper_catalog,
2413 foreign_data_wrapper_name,
2414 CAST((pg_options_to_table(w.fdwoptions)).option_name AS sql_identifier) AS option_name,
2415 CAST((pg_options_to_table(w.fdwoptions)).option_value AS character_data) AS option_value
2416 FROM _pg_foreign_data_wrappers w;
2418 GRANT SELECT ON foreign_data_wrapper_options TO PUBLIC;
2423 * FOREIGN_DATA_WRAPPERS view
2425 CREATE VIEW foreign_data_wrappers AS
2426 SELECT foreign_data_wrapper_catalog,
2427 foreign_data_wrapper_name,
2428 authorization_identifier,
2429 CAST(NULL AS character_data) AS library_name,
2430 foreign_data_wrapper_language
2431 FROM _pg_foreign_data_wrappers w;
2433 GRANT SELECT ON foreign_data_wrappers TO PUBLIC;
2436 /* Base view for foreign servers */
2437 CREATE VIEW _pg_foreign_servers AS
2440 CAST(current_database() AS sql_identifier) AS foreign_server_catalog,
2441 CAST(srvname AS sql_identifier) AS foreign_server_name,
2442 CAST(current_database() AS sql_identifier) AS foreign_data_wrapper_catalog,
2443 CAST(w.fdwname AS sql_identifier) AS foreign_data_wrapper_name,
2444 CAST(srvtype AS character_data) AS foreign_server_type,
2445 CAST(srvversion AS character_data) AS foreign_server_version,
2446 CAST(u.rolname AS sql_identifier) AS authorization_identifier
2447 FROM pg_foreign_server s, pg_foreign_data_wrapper w, pg_authid u
2448 WHERE w.oid = s.srvfdw
2449 AND u.oid = s.srvowner
2450 AND (pg_has_role(s.srvowner, 'USAGE')
2451 OR has_server_privilege(s.oid, 'USAGE'));
2456 * FOREIGN_SERVER_OPTIONS view
2458 CREATE VIEW foreign_server_options AS
2459 SELECT foreign_server_catalog,
2460 foreign_server_name,
2461 CAST((pg_options_to_table(s.srvoptions)).option_name AS sql_identifier) AS option_name,
2462 CAST((pg_options_to_table(s.srvoptions)).option_value AS character_data) AS option_value
2463 FROM _pg_foreign_servers s;
2465 GRANT SELECT ON TABLE foreign_server_options TO PUBLIC;
2470 * FOREIGN_SERVERS view
2472 CREATE VIEW foreign_servers AS
2473 SELECT foreign_server_catalog,
2474 foreign_server_name,
2475 foreign_data_wrapper_catalog,
2476 foreign_data_wrapper_name,
2477 foreign_server_type,
2478 foreign_server_version,
2479 authorization_identifier
2480 FROM _pg_foreign_servers;
2482 GRANT SELECT ON foreign_servers TO PUBLIC;
2485 /* Base view for foreign tables */
2486 CREATE VIEW _pg_foreign_tables AS
2488 CAST(current_database() AS sql_identifier) AS foreign_table_catalog,
2489 n.nspname AS foreign_table_schema,
2490 c.relname AS foreign_table_name,
2491 t.ftoptions AS ftoptions,
2492 CAST(current_database() AS sql_identifier) AS foreign_server_catalog,
2493 CAST(srvname AS sql_identifier) AS foreign_server_name,
2494 CAST(u.rolname AS sql_identifier) AS authorization_identifier
2495 FROM pg_foreign_table t, pg_foreign_server s, pg_foreign_data_wrapper w,
2496 pg_authid u, pg_namespace n, pg_class c
2497 WHERE w.oid = s.srvfdw
2498 AND u.oid = c.relowner
2499 AND (pg_has_role(c.relowner, 'USAGE')
2500 OR has_table_privilege(c.oid, 'SELECT')
2501 OR has_any_column_privilege(c.oid, 'SELECT'))
2502 AND n.oid = c.relnamespace
2503 AND c.oid = t.ftrelid
2505 AND s.oid = t.ftserver;
2510 * FOREIGN_TABLE_OPTIONS view
2512 CREATE VIEW foreign_table_options AS
2513 SELECT foreign_table_catalog,
2514 foreign_table_schema,
2516 CAST((pg_options_to_table(t.ftoptions)).option_name AS sql_identifier) AS option_name,
2517 CAST((pg_options_to_table(t.ftoptions)).option_value AS character_data) AS option_value
2518 FROM _pg_foreign_tables t;
2520 GRANT SELECT ON TABLE foreign_table_options TO PUBLIC;
2525 * FOREIGN_TABLES view
2527 CREATE VIEW foreign_tables AS
2528 SELECT foreign_table_catalog,
2529 foreign_table_schema,
2531 foreign_server_catalog,
2533 FROM _pg_foreign_tables;
2535 GRANT SELECT ON foreign_tables TO PUBLIC;
2539 /* Base view for user mappings */
2540 CREATE VIEW _pg_user_mappings AS
2544 CAST(COALESCE(u.rolname,'PUBLIC') AS sql_identifier ) AS authorization_identifier,
2545 s.foreign_server_catalog,
2546 s.foreign_server_name,
2547 s.authorization_identifier AS srvowner
2548 FROM pg_user_mapping um LEFT JOIN pg_authid u ON (u.oid = um.umuser),
2549 _pg_foreign_servers s
2550 WHERE s.oid = um.umserver;
2555 * USER_MAPPING_OPTIONS view
2557 CREATE VIEW user_mapping_options AS
2558 SELECT authorization_identifier,
2559 foreign_server_catalog,
2560 foreign_server_name,
2561 CAST((pg_options_to_table(um.umoptions)).option_name AS sql_identifier) AS option_name,
2562 CAST(CASE WHEN (umuser <> 0 AND authorization_identifier = current_user)
2563 OR (umuser = 0 AND pg_has_role(srvowner, 'USAGE'))
2564 OR (SELECT rolsuper FROM pg_authid WHERE rolname = current_user) THEN (pg_options_to_table(um.umoptions)).option_value
2565 ELSE NULL END AS character_data) AS option_value
2566 FROM _pg_user_mappings um;
2568 GRANT SELECT ON user_mapping_options TO PUBLIC;
2573 * USER_MAPPINGS view
2575 CREATE VIEW user_mappings AS
2576 SELECT authorization_identifier,
2577 foreign_server_catalog,
2579 FROM _pg_user_mappings;
2581 GRANT SELECT ON user_mappings TO PUBLIC;