2 * SQL Information Schema
3 * as defined in ISO/IEC 9075-11:2003
5 * Copyright (c) 2003-2009, PostgreSQL Global Development Group
7 * $PostgreSQL: pgsql/src/backend/catalog/information_schema.sql,v 1.49 2009/01/14 21:12:09 petere Exp $
11 * Note: Generally, the definitions in this file should be ordered
12 * according to the clause numbers in the SQL standard, which is also the
13 * alphabetical order. In some cases it is convenient or necessary to
14 * define one information schema view by using another one; in that case,
15 * put the referencing view at the very end and leave a note where it
16 * should have been put.
22 * INFORMATION_SCHEMA schema
25 CREATE SCHEMA information_schema;
26 GRANT USAGE ON SCHEMA information_schema TO PUBLIC;
27 SET search_path TO information_schema, public;
31 * A few supporting functions first ...
34 /* Expand any 1-D array into a set with integers 1..N */
35 CREATE FUNCTION _pg_expandarray(IN anyarray, OUT x anyelement, OUT n int)
37 LANGUAGE sql STRICT IMMUTABLE
38 AS 'select $1[s], s - pg_catalog.array_lower($1,1) + 1
39 from pg_catalog.generate_series(pg_catalog.array_lower($1,1),
40 pg_catalog.array_upper($1,1),
43 CREATE FUNCTION _pg_keyissubset(smallint[], smallint[]) RETURNS boolean
46 RETURNS NULL ON NULL INPUT
47 AS 'select $1[1] is null or ($1[1] = any ($2) and coalesce(information_schema._pg_keyissubset($1[2:pg_catalog.array_upper($1,1)], $2), true))';
49 CREATE FUNCTION _pg_keysequal(smallint[], smallint[]) RETURNS boolean
52 RETURNS NULL ON NULL INPUT
53 AS 'select information_schema._pg_keyissubset($1, $2) and information_schema._pg_keyissubset($2, $1)';
55 /* Get the OID of the unique index that an FK constraint depends on */
56 CREATE FUNCTION _pg_underlying_index(oid) RETURNS oid
57 LANGUAGE sql STRICT STABLE
59 SELECT refobjid FROM pg_catalog.pg_depend
60 WHERE classid = 'pg_catalog.pg_constraint'::pg_catalog.regclass AND
62 refclassid = 'pg_catalog.pg_class'::pg_catalog.regclass AND
63 refobjsubid = 0 AND deptype = 'n'
66 /* Given an index's OID and an underlying-table column number, return the
67 * column's position in the index (NULL if not there) */
68 CREATE FUNCTION _pg_index_position(oid, smallint) RETURNS int
69 LANGUAGE sql STRICT STABLE
72 (SELECT information_schema._pg_expandarray(indkey) AS a
73 FROM pg_catalog.pg_index WHERE indexrelid = $1) ss
77 CREATE FUNCTION _pg_truetypid(pg_attribute, pg_type) RETURNS oid
80 RETURNS NULL ON NULL INPUT
82 $$SELECT CASE WHEN $2.typtype = 'd' THEN $2.typbasetype ELSE $1.atttypid END$$;
84 CREATE FUNCTION _pg_truetypmod(pg_attribute, pg_type) RETURNS int4
87 RETURNS NULL ON NULL INPUT
89 $$SELECT CASE WHEN $2.typtype = 'd' THEN $2.typtypmod ELSE $1.atttypmod END$$;
91 -- these functions encapsulate knowledge about the encoding of typmod:
93 CREATE FUNCTION _pg_char_max_length(typid oid, typmod int4) RETURNS integer
96 RETURNS NULL ON NULL INPUT
99 CASE WHEN $2 = -1 /* default typmod */
101 WHEN $1 IN (1042, 1043) /* char, varchar */
103 WHEN $1 IN (1560, 1562) /* bit, varbit */
108 CREATE FUNCTION _pg_char_octet_length(typid oid, typmod int4) RETURNS integer
111 RETURNS NULL ON NULL INPUT
114 CASE WHEN $1 IN (25, 1042, 1043) /* text, char, varchar */
115 THEN CAST(2^30 AS integer)
119 CREATE FUNCTION _pg_numeric_precision(typid oid, typmod int4) RETURNS integer
122 RETURNS NULL ON NULL INPUT
126 WHEN 21 /*int2*/ THEN 16
127 WHEN 23 /*int4*/ THEN 32
128 WHEN 20 /*int8*/ THEN 64
129 WHEN 1700 /*numeric*/ THEN
132 ELSE (($2 - 4) >> 16) & 65535
134 WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/
135 WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/
139 CREATE FUNCTION _pg_numeric_precision_radix(typid oid, typmod int4) RETURNS integer
142 RETURNS NULL ON NULL INPUT
145 CASE WHEN $1 IN (21, 23, 20, 700, 701) THEN 2
146 WHEN $1 IN (1700) THEN 10
150 CREATE FUNCTION _pg_numeric_scale(typid oid, typmod int4) RETURNS integer
153 RETURNS NULL ON NULL INPUT
156 CASE WHEN $1 IN (21, 23, 20) THEN 0
157 WHEN $1 IN (1700) THEN
160 ELSE ($2 - 4) & 65535
165 CREATE FUNCTION _pg_datetime_precision(typid oid, typmod int4) RETURNS integer
168 RETURNS NULL ON NULL INPUT
171 CASE WHEN $2 = -1 /* default typmod */
173 WHEN $1 IN (1083, 1114, 1184, 1266) /* time, timestamp, same + tz */
175 WHEN $1 IN (1186) /* interval */
181 -- 5.2 INFORMATION_SCHEMA_CATALOG_NAME view appears later.
186 * CARDINAL_NUMBER domain
189 CREATE DOMAIN cardinal_number AS integer
190 CONSTRAINT cardinal_number_domain_check CHECK (value >= 0);
195 * CHARACTER_DATA domain
198 CREATE DOMAIN character_data AS character varying;
203 * SQL_IDENTIFIER domain
206 CREATE DOMAIN sql_identifier AS character varying;
211 * INFORMATION_SCHEMA_CATALOG_NAME view
214 CREATE VIEW information_schema_catalog_name AS
215 SELECT CAST(current_database() AS sql_identifier) AS catalog_name;
217 GRANT SELECT ON information_schema_catalog_name TO PUBLIC;
225 CREATE DOMAIN time_stamp AS timestamp(2) with time zone
226 DEFAULT current_timestamp(2);
229 -- 5.7 ADMINISTRABLE_ROLE_AUTHORIZATIONS view appears later.
234 * APPLICABLE_ROLES view
237 CREATE VIEW applicable_roles AS
238 SELECT CAST(a.rolname AS sql_identifier) AS grantee,
239 CAST(b.rolname AS sql_identifier) AS role_name,
240 CAST(CASE WHEN m.admin_option THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
241 FROM pg_auth_members m
242 JOIN pg_authid a ON (m.member = a.oid)
243 JOIN pg_authid b ON (m.roleid = b.oid)
244 WHERE pg_has_role(a.oid, 'USAGE');
246 GRANT SELECT ON applicable_roles TO PUBLIC;
251 * ADMINISTRABLE_ROLE_AUTHORIZATIONS view
254 CREATE VIEW administrable_role_authorizations AS
256 FROM applicable_roles
257 WHERE is_grantable = 'YES';
259 GRANT SELECT ON administrable_role_authorizations TO PUBLIC;
267 -- feature not supported
275 CREATE VIEW attributes AS
276 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
277 CAST(nc.nspname AS sql_identifier) AS udt_schema,
278 CAST(c.relname AS sql_identifier) AS udt_name,
279 CAST(a.attname AS sql_identifier) AS attribute_name,
280 CAST(a.attnum AS cardinal_number) AS ordinal_position,
281 CAST(pg_get_expr(ad.adbin, ad.adrelid) AS character_data) AS attribute_default,
282 CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
287 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
288 WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, null)
289 ELSE 'USER-DEFINED' END
294 _pg_char_max_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
296 AS character_maximum_length,
299 _pg_char_octet_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
301 AS character_octet_length,
303 CAST(null AS sql_identifier) AS character_set_catalog,
304 CAST(null AS sql_identifier) AS character_set_schema,
305 CAST(null AS sql_identifier) AS character_set_name,
307 CAST(null AS sql_identifier) AS collation_catalog,
308 CAST(null AS sql_identifier) AS collation_schema,
309 CAST(null AS sql_identifier) AS collation_name,
312 _pg_numeric_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
314 AS numeric_precision,
317 _pg_numeric_precision_radix(_pg_truetypid(a, t), _pg_truetypmod(a, t))
319 AS numeric_precision_radix,
322 _pg_numeric_scale(_pg_truetypid(a, t), _pg_truetypmod(a, t))
327 _pg_datetime_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
329 AS datetime_precision,
331 CAST(null AS character_data) AS interval_type, -- FIXME
332 CAST(null AS character_data) AS interval_precision, -- FIXME
334 CAST(current_database() AS sql_identifier) AS attribute_udt_catalog,
335 CAST(nt.nspname AS sql_identifier) AS attribute_udt_schema,
336 CAST(t.typname AS sql_identifier) AS attribute_udt_name,
338 CAST(null AS sql_identifier) AS scope_catalog,
339 CAST(null AS sql_identifier) AS scope_schema,
340 CAST(null AS sql_identifier) AS scope_name,
342 CAST(null AS cardinal_number) AS maximum_cardinality,
343 CAST(a.attnum AS sql_identifier) AS dtd_identifier,
344 CAST('NO' AS character_data) AS is_derived_reference_attribute
346 FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum),
347 pg_class c, pg_namespace nc,
348 (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
350 WHERE a.attrelid = c.oid
351 AND a.atttypid = t.oid
352 AND nc.oid = c.relnamespace
353 AND a.attnum > 0 AND NOT a.attisdropped
354 AND c.relkind in ('c');
356 GRANT SELECT ON attributes TO PUBLIC;
361 * CHARACTER_SETS view
364 -- feature not supported
369 * CHECK_CONSTRAINT_ROUTINE_USAGE view
372 CREATE VIEW check_constraint_routine_usage AS
373 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
374 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
375 CAST(c.conname AS sql_identifier) AS constraint_name,
376 CAST(current_database() AS sql_identifier) AS specific_catalog,
377 CAST(np.nspname AS sql_identifier) AS specific_schema,
378 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name
379 FROM pg_namespace nc, pg_constraint c, pg_depend d, pg_proc p, pg_namespace np
380 WHERE nc.oid = c.connamespace
383 AND d.classid = 'pg_catalog.pg_constraint'::regclass
384 AND d.refobjid = p.oid
385 AND d.refclassid = 'pg_catalog.pg_proc'::regclass
386 AND p.pronamespace = np.oid
387 AND pg_has_role(p.proowner, 'USAGE');
389 GRANT SELECT ON check_constraint_routine_usage TO PUBLIC;
394 * CHECK_CONSTRAINTS view
397 CREATE VIEW check_constraints AS
398 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
399 CAST(rs.nspname AS sql_identifier) AS constraint_schema,
400 CAST(con.conname AS sql_identifier) AS constraint_name,
401 CAST(substring(pg_get_constraintdef(con.oid) from 7) AS character_data)
403 FROM pg_constraint con
404 LEFT OUTER JOIN pg_namespace rs ON (rs.oid = con.connamespace)
405 LEFT OUTER JOIN pg_class c ON (c.oid = con.conrelid)
406 LEFT OUTER JOIN pg_type t ON (t.oid = con.contypid)
407 WHERE pg_has_role(coalesce(c.relowner, t.typowner), 'USAGE')
408 AND con.contype = 'c'
411 -- not-null constraints
413 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
414 CAST(n.nspname AS sql_identifier) AS constraint_schema,
415 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
416 CAST(a.attname || ' IS NOT NULL' AS character_data)
418 FROM pg_namespace n, pg_class r, pg_attribute a
419 WHERE n.oid = r.relnamespace
420 AND r.oid = a.attrelid
422 AND NOT a.attisdropped
425 AND pg_has_role(r.relowner, 'USAGE');
427 GRANT SELECT ON check_constraints TO PUBLIC;
435 -- feature not supported
439 * COLLATION_CHARACTER_SET_APPLICABILITY view
442 -- feature not supported
447 * COLUMN_COLUMN_USAGE view
450 -- feature not supported
455 * COLUMN_DOMAIN_USAGE view
458 CREATE VIEW column_domain_usage AS
459 SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
460 CAST(nt.nspname AS sql_identifier) AS domain_schema,
461 CAST(t.typname AS sql_identifier) AS domain_name,
462 CAST(current_database() AS sql_identifier) AS table_catalog,
463 CAST(nc.nspname AS sql_identifier) AS table_schema,
464 CAST(c.relname AS sql_identifier) AS table_name,
465 CAST(a.attname AS sql_identifier) AS column_name
467 FROM pg_type t, pg_namespace nt, pg_class c, pg_namespace nc,
470 WHERE t.typnamespace = nt.oid
471 AND c.relnamespace = nc.oid
472 AND a.attrelid = c.oid
473 AND a.atttypid = t.oid
475 AND c.relkind IN ('r', 'v')
477 AND NOT a.attisdropped
478 AND pg_has_role(t.typowner, 'USAGE');
480 GRANT SELECT ON column_domain_usage TO PUBLIC;
488 CREATE VIEW column_privileges AS
489 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
490 CAST(grantee.rolname AS sql_identifier) AS grantee,
491 CAST(current_database() AS sql_identifier) AS table_catalog,
492 CAST(nc.nspname AS sql_identifier) AS table_schema,
493 CAST(c.relname AS sql_identifier) AS table_name,
494 CAST(a.attname AS sql_identifier) AS column_name,
495 CAST(pr.type AS character_data) AS privilege_type,
497 CASE WHEN aclcontains(c.relacl,
498 makeaclitem(grantee.oid, u_grantor.oid, pr.type, true))
499 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
506 SELECT oid, rolname FROM pg_authid
508 SELECT 0::oid, 'PUBLIC'
509 ) AS grantee (oid, rolname),
510 (SELECT 'SELECT' UNION ALL
511 SELECT 'INSERT' UNION ALL
512 SELECT 'UPDATE' UNION ALL
513 SELECT 'REFERENCES') AS pr (type)
515 WHERE a.attrelid = c.oid
516 AND c.relnamespace = nc.oid
518 AND NOT a.attisdropped
519 AND c.relkind IN ('r', 'v')
520 AND aclcontains(c.relacl,
521 makeaclitem(grantee.oid, u_grantor.oid, pr.type, false))
522 AND (pg_has_role(u_grantor.oid, 'USAGE')
523 OR pg_has_role(grantee.oid, 'USAGE')
524 OR grantee.rolname = 'PUBLIC');
526 GRANT SELECT ON column_privileges TO PUBLIC;
531 * COLUMN_UDT_USAGE view
534 CREATE VIEW column_udt_usage AS
535 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
536 CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,
537 CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,
538 CAST(current_database() AS sql_identifier) AS table_catalog,
539 CAST(nc.nspname AS sql_identifier) AS table_schema,
540 CAST(c.relname AS sql_identifier) AS table_name,
541 CAST(a.attname AS sql_identifier) AS column_name
543 FROM pg_attribute a, pg_class c, pg_namespace nc,
544 (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
545 LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
546 ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
548 WHERE a.attrelid = c.oid
549 AND a.atttypid = t.oid
550 AND nc.oid = c.relnamespace
551 AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
552 AND pg_has_role(coalesce(bt.typowner, t.typowner), 'USAGE');
554 GRANT SELECT ON column_udt_usage TO PUBLIC;
562 CREATE VIEW columns AS
563 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
564 CAST(nc.nspname AS sql_identifier) AS table_schema,
565 CAST(c.relname AS sql_identifier) AS table_name,
566 CAST(a.attname AS sql_identifier) AS column_name,
567 CAST(a.attnum AS cardinal_number) AS ordinal_position,
568 CAST(pg_get_expr(ad.adbin, ad.adrelid) AS character_data) AS column_default,
569 CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
574 CASE WHEN t.typtype = 'd' THEN
575 CASE WHEN bt.typelem <> 0 AND bt.typlen = -1 THEN 'ARRAY'
576 WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
577 ELSE 'USER-DEFINED' END
579 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
580 WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, null)
581 ELSE 'USER-DEFINED' END
587 _pg_char_max_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
589 AS character_maximum_length,
592 _pg_char_octet_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
594 AS character_octet_length,
597 _pg_numeric_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
599 AS numeric_precision,
602 _pg_numeric_precision_radix(_pg_truetypid(a, t), _pg_truetypmod(a, t))
604 AS numeric_precision_radix,
607 _pg_numeric_scale(_pg_truetypid(a, t), _pg_truetypmod(a, t))
612 _pg_datetime_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
614 AS datetime_precision,
616 CAST(null AS character_data) AS interval_type, -- FIXME
617 CAST(null AS character_data) AS interval_precision, -- FIXME
619 CAST(null AS sql_identifier) AS character_set_catalog,
620 CAST(null AS sql_identifier) AS character_set_schema,
621 CAST(null AS sql_identifier) AS character_set_name,
623 CAST(null AS sql_identifier) AS collation_catalog,
624 CAST(null AS sql_identifier) AS collation_schema,
625 CAST(null AS sql_identifier) AS collation_name,
627 CAST(CASE WHEN t.typtype = 'd' THEN current_database() ELSE null END
628 AS sql_identifier) AS domain_catalog,
629 CAST(CASE WHEN t.typtype = 'd' THEN nt.nspname ELSE null END
630 AS sql_identifier) AS domain_schema,
631 CAST(CASE WHEN t.typtype = 'd' THEN t.typname ELSE null END
632 AS sql_identifier) AS domain_name,
634 CAST(current_database() AS sql_identifier) AS udt_catalog,
635 CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,
636 CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,
638 CAST(null AS sql_identifier) AS scope_catalog,
639 CAST(null AS sql_identifier) AS scope_schema,
640 CAST(null AS sql_identifier) AS scope_name,
642 CAST(null AS cardinal_number) AS maximum_cardinality,
643 CAST(a.attnum AS sql_identifier) AS dtd_identifier,
644 CAST('NO' AS character_data) AS is_self_referencing,
646 CAST('NO' AS character_data) AS is_identity,
647 CAST(null AS character_data) AS identity_generation,
648 CAST(null AS character_data) AS identity_start,
649 CAST(null AS character_data) AS identity_increment,
650 CAST(null AS character_data) AS identity_maximum,
651 CAST(null AS character_data) AS identity_minimum,
652 CAST(null AS character_data) AS identity_cycle,
654 CAST('NEVER' AS character_data) AS is_generated,
655 CAST(null AS character_data) AS generation_expression,
657 CAST(CASE WHEN c.relkind = 'r'
659 AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '2' AND is_instead)
660 AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '4' AND is_instead))
661 THEN 'YES' ELSE 'NO' END AS character_data) AS is_updatable
663 FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum),
664 pg_class c, pg_namespace nc,
665 (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
666 LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
667 ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
669 WHERE a.attrelid = c.oid
670 AND a.atttypid = t.oid
671 AND nc.oid = c.relnamespace
672 AND (NOT pg_is_other_temp_schema(nc.oid))
674 AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
676 AND (pg_has_role(c.relowner, 'USAGE')
677 OR has_table_privilege(c.oid, 'SELECT')
678 OR has_table_privilege(c.oid, 'INSERT')
679 OR has_table_privilege(c.oid, 'UPDATE')
680 OR has_table_privilege(c.oid, 'REFERENCES') );
682 GRANT SELECT ON columns TO PUBLIC;
687 * CONSTRAINT_COLUMN_USAGE view
690 CREATE VIEW constraint_column_usage AS
691 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
692 CAST(tblschema AS sql_identifier) AS table_schema,
693 CAST(tblname AS sql_identifier) AS table_name,
694 CAST(colname AS sql_identifier) AS column_name,
695 CAST(current_database() AS sql_identifier) AS constraint_catalog,
696 CAST(cstrschema AS sql_identifier) AS constraint_schema,
697 CAST(cstrname AS sql_identifier) AS constraint_name
700 /* check constraints */
701 SELECT DISTINCT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
702 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_depend d, pg_namespace nc, pg_constraint c
703 WHERE nr.oid = r.relnamespace
704 AND r.oid = a.attrelid
705 AND d.refclassid = 'pg_catalog.pg_class'::regclass
706 AND d.refobjid = r.oid
707 AND d.refobjsubid = a.attnum
708 AND d.classid = 'pg_catalog.pg_constraint'::regclass
710 AND c.connamespace = nc.oid
713 AND NOT a.attisdropped
717 /* unique/primary key/foreign key constraints */
718 SELECT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
719 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc,
721 WHERE nr.oid = r.relnamespace
722 AND r.oid = a.attrelid
723 AND nc.oid = c.connamespace
724 AND (CASE WHEN c.contype = 'f' THEN r.oid = c.confrelid AND a.attnum = ANY (c.confkey)
725 ELSE r.oid = c.conrelid AND a.attnum = ANY (c.conkey) END)
726 AND NOT a.attisdropped
727 AND c.contype IN ('p', 'u', 'f')
730 ) AS x (tblschema, tblname, tblowner, colname, cstrschema, cstrname)
732 WHERE pg_has_role(x.tblowner, 'USAGE');
734 GRANT SELECT ON constraint_column_usage TO PUBLIC;
739 * CONSTRAINT_TABLE_USAGE view
742 CREATE VIEW constraint_table_usage AS
743 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
744 CAST(nr.nspname AS sql_identifier) AS table_schema,
745 CAST(r.relname AS sql_identifier) AS table_name,
746 CAST(current_database() AS sql_identifier) AS constraint_catalog,
747 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
748 CAST(c.conname AS sql_identifier) AS constraint_name
750 FROM pg_constraint c, pg_namespace nc,
751 pg_class r, pg_namespace nr
753 WHERE c.connamespace = nc.oid AND r.relnamespace = nr.oid
754 AND ( (c.contype = 'f' AND c.confrelid = r.oid)
755 OR (c.contype IN ('p', 'u') AND c.conrelid = r.oid) )
757 AND pg_has_role(r.relowner, 'USAGE');
759 GRANT SELECT ON constraint_table_usage TO PUBLIC;
762 -- 5.23 DATA_TYPE_PRIVILEGES view appears later.
767 * DIRECT_SUPERTABLES view
770 -- feature not supported
775 * DIRECT_SUPERTYPES view
778 -- feature not supported
783 * DOMAIN_CONSTRAINTS view
786 CREATE VIEW domain_constraints AS
787 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
788 CAST(rs.nspname AS sql_identifier) AS constraint_schema,
789 CAST(con.conname AS sql_identifier) AS constraint_name,
790 CAST(current_database() AS sql_identifier) AS domain_catalog,
791 CAST(n.nspname AS sql_identifier) AS domain_schema,
792 CAST(t.typname AS sql_identifier) AS domain_name,
793 CAST(CASE WHEN condeferrable THEN 'YES' ELSE 'NO' END
794 AS character_data) AS is_deferrable,
795 CAST(CASE WHEN condeferred THEN 'YES' ELSE 'NO' END
796 AS character_data) AS initially_deferred
797 FROM pg_namespace rs, pg_namespace n, pg_constraint con, pg_type t
798 WHERE rs.oid = con.connamespace
799 AND n.oid = t.typnamespace
800 AND t.oid = con.contypid;
802 GRANT SELECT ON domain_constraints TO PUBLIC;
806 * DOMAIN_UDT_USAGE view
807 * apparently removed in SQL:2003
810 CREATE VIEW domain_udt_usage AS
811 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
812 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
813 CAST(bt.typname AS sql_identifier) AS udt_name,
814 CAST(current_database() AS sql_identifier) AS domain_catalog,
815 CAST(nt.nspname AS sql_identifier) AS domain_schema,
816 CAST(t.typname AS sql_identifier) AS domain_name
818 FROM pg_type t, pg_namespace nt,
819 pg_type bt, pg_namespace nbt
821 WHERE t.typnamespace = nt.oid
822 AND t.typbasetype = bt.oid
823 AND bt.typnamespace = nbt.oid
825 AND pg_has_role(bt.typowner, 'USAGE');
827 GRANT SELECT ON domain_udt_usage TO PUBLIC;
835 CREATE VIEW domains AS
836 SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
837 CAST(nt.nspname AS sql_identifier) AS domain_schema,
838 CAST(t.typname AS sql_identifier) AS domain_name,
841 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
842 WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
843 ELSE 'USER-DEFINED' END
848 _pg_char_max_length(t.typbasetype, t.typtypmod)
850 AS character_maximum_length,
853 _pg_char_octet_length(t.typbasetype, t.typtypmod)
855 AS character_octet_length,
857 CAST(null AS sql_identifier) AS character_set_catalog,
858 CAST(null AS sql_identifier) AS character_set_schema,
859 CAST(null AS sql_identifier) AS character_set_name,
861 CAST(null AS sql_identifier) AS collation_catalog,
862 CAST(null AS sql_identifier) AS collation_schema,
863 CAST(null AS sql_identifier) AS collation_name,
866 _pg_numeric_precision(t.typbasetype, t.typtypmod)
868 AS numeric_precision,
871 _pg_numeric_precision_radix(t.typbasetype, t.typtypmod)
873 AS numeric_precision_radix,
876 _pg_numeric_scale(t.typbasetype, t.typtypmod)
881 _pg_datetime_precision(t.typbasetype, t.typtypmod)
883 AS datetime_precision,
885 CAST(null AS character_data) AS interval_type, -- FIXME
886 CAST(null AS character_data) AS interval_precision, -- FIXME
888 CAST(t.typdefault AS character_data) AS domain_default,
890 CAST(current_database() AS sql_identifier) AS udt_catalog,
891 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
892 CAST(bt.typname AS sql_identifier) AS udt_name,
894 CAST(null AS sql_identifier) AS scope_catalog,
895 CAST(null AS sql_identifier) AS scope_schema,
896 CAST(null AS sql_identifier) AS scope_name,
898 CAST(null AS cardinal_number) AS maximum_cardinality,
899 CAST(1 AS sql_identifier) AS dtd_identifier
901 FROM pg_type t, pg_namespace nt,
902 pg_type bt, pg_namespace nbt
904 WHERE t.typnamespace = nt.oid
905 AND t.typbasetype = bt.oid
906 AND bt.typnamespace = nbt.oid
909 GRANT SELECT ON domains TO PUBLIC;
912 -- 5.28 ELEMENT_TYPES view appears later.
920 CREATE VIEW enabled_roles AS
921 SELECT CAST(a.rolname AS sql_identifier) AS role_name
923 WHERE pg_has_role(a.oid, 'USAGE');
925 GRANT SELECT ON enabled_roles TO PUBLIC;
933 -- feature not supported
938 * KEY_COLUMN_USAGE view
941 CREATE VIEW key_column_usage AS
942 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
943 CAST(nc_nspname AS sql_identifier) AS constraint_schema,
944 CAST(conname AS sql_identifier) AS constraint_name,
945 CAST(current_database() AS sql_identifier) AS table_catalog,
946 CAST(nr_nspname AS sql_identifier) AS table_schema,
947 CAST(relname AS sql_identifier) AS table_name,
948 CAST(a.attname AS sql_identifier) AS column_name,
949 CAST((ss.x).n AS cardinal_number) AS ordinal_position,
950 CAST(CASE WHEN contype = 'f' THEN
951 _pg_index_position(_pg_underlying_index(ss.coid),
952 ss.confkey[(ss.x).n])
954 END AS cardinal_number)
955 AS position_in_unique_constraint
957 (SELECT r.oid AS roid, r.relname, nc.nspname AS nc_nspname,
958 nr.nspname AS nr_nspname,
959 c.oid AS coid, c.conname, c.contype, c.confkey, c.confrelid,
960 _pg_expandarray(c.conkey) AS x
961 FROM pg_namespace nr, pg_class r, pg_namespace nc,
963 WHERE nr.oid = r.relnamespace
964 AND r.oid = c.conrelid
965 AND nc.oid = c.connamespace
966 AND c.contype IN ('p', 'u', 'f')
968 AND (NOT pg_is_other_temp_schema(nr.oid))
969 AND (pg_has_role(r.relowner, 'USAGE')
970 OR has_table_privilege(r.oid, 'SELECT')
971 OR has_table_privilege(r.oid, 'INSERT')
972 OR has_table_privilege(r.oid, 'UPDATE')
973 OR has_table_privilege(r.oid, 'REFERENCES')) ) AS ss
974 WHERE ss.roid = a.attrelid
975 AND a.attnum = (ss.x).x
976 AND NOT a.attisdropped;
978 GRANT SELECT ON key_column_usage TO PUBLIC;
983 * METHOD_SPECIFICATION_PARAMETERS view
986 -- feature not supported
991 * METHOD_SPECIFICATIONS view
994 -- feature not supported
1002 CREATE VIEW parameters AS
1003 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
1004 CAST(n_nspname AS sql_identifier) AS specific_schema,
1005 CAST(proname || '_' || CAST(p_oid AS text) AS sql_identifier) AS specific_name,
1006 CAST((ss.x).n AS cardinal_number) AS ordinal_position,
1008 CASE WHEN proargmodes IS NULL THEN 'IN'
1009 WHEN proargmodes[(ss.x).n] = 'i' THEN 'IN'
1010 WHEN proargmodes[(ss.x).n] = 'o' THEN 'OUT'
1011 WHEN proargmodes[(ss.x).n] = 'b' THEN 'INOUT'
1012 WHEN proargmodes[(ss.x).n] = 'v' THEN 'IN'
1013 WHEN proargmodes[(ss.x).n] = 't' THEN 'OUT'
1014 END AS character_data) AS parameter_mode,
1015 CAST('NO' AS character_data) AS is_result,
1016 CAST('NO' AS character_data) AS as_locator,
1017 CAST(NULLIF(proargnames[(ss.x).n], '') AS sql_identifier) AS parameter_name,
1019 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
1020 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
1021 ELSE 'USER-DEFINED' END AS character_data)
1023 CAST(null AS cardinal_number) AS character_maximum_length,
1024 CAST(null AS cardinal_number) AS character_octet_length,
1025 CAST(null AS sql_identifier) AS character_set_catalog,
1026 CAST(null AS sql_identifier) AS character_set_schema,
1027 CAST(null AS sql_identifier) AS character_set_name,
1028 CAST(null AS sql_identifier) AS collation_catalog,
1029 CAST(null AS sql_identifier) AS collation_schema,
1030 CAST(null AS sql_identifier) AS collation_name,
1031 CAST(null AS cardinal_number) AS numeric_precision,
1032 CAST(null AS cardinal_number) AS numeric_precision_radix,
1033 CAST(null AS cardinal_number) AS numeric_scale,
1034 CAST(null AS cardinal_number) AS datetime_precision,
1035 CAST(null AS character_data) AS interval_type,
1036 CAST(null AS character_data) AS interval_precision,
1037 CAST(current_database() AS sql_identifier) AS udt_catalog,
1038 CAST(nt.nspname AS sql_identifier) AS udt_schema,
1039 CAST(t.typname AS sql_identifier) AS udt_name,
1040 CAST(null AS sql_identifier) AS scope_catalog,
1041 CAST(null AS sql_identifier) AS scope_schema,
1042 CAST(null AS sql_identifier) AS scope_name,
1043 CAST(null AS cardinal_number) AS maximum_cardinality,
1044 CAST((ss.x).n AS sql_identifier) AS dtd_identifier
1046 FROM pg_type t, pg_namespace nt,
1047 (SELECT n.nspname AS n_nspname, p.proname, p.oid AS p_oid,
1048 p.proargnames, p.proargmodes,
1049 _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
1050 FROM pg_namespace n, pg_proc p
1051 WHERE n.oid = p.pronamespace
1052 AND (pg_has_role(p.proowner, 'USAGE') OR
1053 has_function_privilege(p.oid, 'EXECUTE'))) AS ss
1054 WHERE t.oid = (ss.x).x AND t.typnamespace = nt.oid;
1056 GRANT SELECT ON parameters TO PUBLIC;
1061 * REFERENCED_TYPES view
1064 -- feature not supported
1069 * REFERENTIAL_CONSTRAINTS view
1072 CREATE VIEW referential_constraints AS
1073 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1074 CAST(ncon.nspname AS sql_identifier) AS constraint_schema,
1075 CAST(con.conname AS sql_identifier) AS constraint_name,
1077 CASE WHEN npkc.nspname IS NULL THEN NULL
1078 ELSE current_database() END
1079 AS sql_identifier) AS unique_constraint_catalog,
1080 CAST(npkc.nspname AS sql_identifier) AS unique_constraint_schema,
1081 CAST(pkc.conname AS sql_identifier) AS unique_constraint_name,
1084 CASE con.confmatchtype WHEN 'f' THEN 'FULL'
1085 WHEN 'p' THEN 'PARTIAL'
1086 WHEN 'u' THEN 'NONE' END
1087 AS character_data) AS match_option,
1090 CASE con.confupdtype WHEN 'c' THEN 'CASCADE'
1091 WHEN 'n' THEN 'SET NULL'
1092 WHEN 'd' THEN 'SET DEFAULT'
1093 WHEN 'r' THEN 'RESTRICT'
1094 WHEN 'a' THEN 'NO ACTION' END
1095 AS character_data) AS update_rule,
1098 CASE con.confdeltype WHEN 'c' THEN 'CASCADE'
1099 WHEN 'n' THEN 'SET NULL'
1100 WHEN 'd' THEN 'SET DEFAULT'
1101 WHEN 'r' THEN 'RESTRICT'
1102 WHEN 'a' THEN 'NO ACTION' END
1103 AS character_data) AS delete_rule
1105 FROM (pg_namespace ncon
1106 INNER JOIN pg_constraint con ON ncon.oid = con.connamespace
1107 INNER JOIN pg_class c ON con.conrelid = c.oid)
1110 INNER JOIN pg_namespace npkc ON pkc.connamespace = npkc.oid)
1111 ON con.confrelid = pkc.conrelid
1112 AND _pg_keysequal(con.confkey, pkc.conkey)
1114 WHERE c.relkind = 'r'
1115 AND con.contype = 'f'
1116 AND (pkc.contype IN ('p', 'u') OR pkc.contype IS NULL)
1117 AND pg_has_role(c.relowner, 'USAGE');
1119 GRANT SELECT ON referential_constraints TO PUBLIC;
1124 * ROLE_COLUMN_GRANTS view
1127 CREATE VIEW role_column_grants AS
1128 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1129 CAST(g_grantee.rolname AS sql_identifier) AS grantee,
1130 CAST(current_database() AS sql_identifier) AS table_catalog,
1131 CAST(nc.nspname AS sql_identifier) AS table_schema,
1132 CAST(c.relname AS sql_identifier) AS table_name,
1133 CAST(a.attname AS sql_identifier) AS column_name,
1134 CAST(pr.type AS character_data) AS privilege_type,
1136 CASE WHEN aclcontains(c.relacl,
1137 makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, true))
1138 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
1140 FROM pg_attribute a,
1143 pg_authid u_grantor,
1144 pg_authid g_grantee,
1145 (SELECT 'SELECT' UNION ALL
1146 SELECT 'INSERT' UNION ALL
1147 SELECT 'UPDATE' UNION ALL
1148 SELECT 'REFERENCES') AS pr (type)
1150 WHERE a.attrelid = c.oid
1151 AND c.relnamespace = nc.oid
1153 AND NOT a.attisdropped
1154 AND c.relkind IN ('r', 'v')
1155 AND aclcontains(c.relacl,
1156 makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, false))
1157 AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
1158 OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));
1160 GRANT SELECT ON role_column_grants TO PUBLIC;
1165 * ROLE_ROUTINE_GRANTS view
1168 CREATE VIEW role_routine_grants AS
1169 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1170 CAST(g_grantee.rolname AS sql_identifier) AS grantee,
1171 CAST(current_database() AS sql_identifier) AS specific_catalog,
1172 CAST(n.nspname AS sql_identifier) AS specific_schema,
1173 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1174 CAST(current_database() AS sql_identifier) AS routine_catalog,
1175 CAST(n.nspname AS sql_identifier) AS routine_schema,
1176 CAST(p.proname AS sql_identifier) AS routine_name,
1177 CAST('EXECUTE' AS character_data) AS privilege_type,
1179 CASE WHEN aclcontains(p.proacl,
1180 makeaclitem(g_grantee.oid, u_grantor.oid, 'EXECUTE', true))
1181 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
1185 pg_authid u_grantor,
1188 WHERE p.pronamespace = n.oid
1189 AND aclcontains(p.proacl,
1190 makeaclitem(g_grantee.oid, u_grantor.oid, 'EXECUTE', false))
1191 AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
1192 OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));
1194 GRANT SELECT ON role_routine_grants TO PUBLIC;
1199 * ROLE_TABLE_GRANTS view
1202 CREATE VIEW role_table_grants AS
1203 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1204 CAST(g_grantee.rolname AS sql_identifier) AS grantee,
1205 CAST(current_database() AS sql_identifier) AS table_catalog,
1206 CAST(nc.nspname AS sql_identifier) AS table_schema,
1207 CAST(c.relname AS sql_identifier) AS table_name,
1208 CAST(pr.type AS character_data) AS privilege_type,
1210 CASE WHEN aclcontains(c.relacl,
1211 makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, true))
1212 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable,
1213 CAST('NO' AS character_data) AS with_hierarchy
1217 pg_authid u_grantor,
1218 pg_authid g_grantee,
1219 (SELECT 'SELECT' UNION ALL
1220 SELECT 'INSERT' UNION ALL
1221 SELECT 'UPDATE' UNION ALL
1222 SELECT 'DELETE' UNION ALL
1223 SELECT 'TRUNCATE' UNION ALL
1224 SELECT 'REFERENCES' UNION ALL
1225 SELECT 'TRIGGER') AS pr (type)
1227 WHERE c.relnamespace = nc.oid
1228 AND c.relkind IN ('r', 'v')
1229 AND aclcontains(c.relacl,
1230 makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, false))
1231 AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
1232 OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));
1234 GRANT SELECT ON role_table_grants TO PUBLIC;
1239 * ROLE_TABLE_METHOD_GRANTS view
1242 -- feature not supported
1247 * ROLE_USAGE_GRANTS view
1250 CREATE VIEW role_usage_grants AS
1252 /* foreign-data wrappers */
1253 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1254 CAST(g_grantee.rolname AS sql_identifier) AS grantee,
1255 CAST(current_database() AS sql_identifier) AS object_catalog,
1256 CAST('' AS sql_identifier) AS object_schema,
1257 CAST(fdw.fdwname AS sql_identifier) AS object_name,
1258 CAST('FOREIGN DATA WRAPPER' AS character_data) AS object_type,
1259 CAST('USAGE' AS character_data) AS privilege_type,
1261 CASE WHEN aclcontains(fdw.fdwacl,
1262 makeaclitem(g_grantee.oid, u_grantor.oid, 'USAGE', true))
1263 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
1265 FROM pg_foreign_data_wrapper fdw,
1266 pg_authid u_grantor,
1269 WHERE aclcontains(fdw.fdwacl,
1270 makeaclitem(g_grantee.oid, u_grantor.oid, 'USAGE', false))
1271 AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
1272 OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles))
1276 /* foreign server */
1277 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1278 CAST(g_grantee.rolname AS sql_identifier) AS grantee,
1279 CAST(current_database() AS sql_identifier) AS object_catalog,
1280 CAST('' AS sql_identifier) AS object_schema,
1281 CAST(srv.srvname AS sql_identifier) AS object_name,
1282 CAST('FOREIGN SERVER' AS character_data) AS object_type,
1283 CAST('USAGE' AS character_data) AS privilege_type,
1285 CASE WHEN aclcontains(srv.srvacl,
1286 makeaclitem(g_grantee.oid, u_grantor.oid, 'USAGE', true))
1287 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
1289 FROM pg_foreign_server srv,
1290 pg_authid u_grantor,
1293 WHERE aclcontains(srv.srvacl,
1294 makeaclitem(g_grantee.oid, u_grantor.oid, 'USAGE', false))
1295 AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
1296 OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));
1298 GRANT SELECT ON role_usage_grants TO PUBLIC;
1303 * ROLE_UDT_GRANTS view
1306 -- feature not supported
1311 * ROUTINE_COLUMN_USAGE view
1314 -- not tracked by PostgreSQL
1319 * ROUTINE_PRIVILEGES view
1322 CREATE VIEW routine_privileges AS
1323 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1324 CAST(grantee.rolname AS sql_identifier) AS grantee,
1325 CAST(current_database() AS sql_identifier) AS specific_catalog,
1326 CAST(n.nspname AS sql_identifier) AS specific_schema,
1327 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1328 CAST(current_database() AS sql_identifier) AS routine_catalog,
1329 CAST(n.nspname AS sql_identifier) AS routine_schema,
1330 CAST(p.proname AS sql_identifier) AS routine_name,
1331 CAST('EXECUTE' AS character_data) AS privilege_type,
1333 CASE WHEN aclcontains(p.proacl,
1334 makeaclitem(grantee.oid, u_grantor.oid, 'EXECUTE', true))
1335 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
1339 pg_authid u_grantor,
1341 SELECT oid, rolname FROM pg_authid
1343 SELECT 0::oid, 'PUBLIC'
1344 ) AS grantee (oid, rolname)
1346 WHERE p.pronamespace = n.oid
1347 AND aclcontains(p.proacl,
1348 makeaclitem(grantee.oid, u_grantor.oid, 'EXECUTE', false))
1349 AND (pg_has_role(u_grantor.oid, 'USAGE')
1350 OR pg_has_role(grantee.oid, 'USAGE')
1351 OR grantee.rolname = 'PUBLIC');
1353 GRANT SELECT ON routine_privileges TO PUBLIC;
1358 * ROUTINE_ROUTINE_USAGE view
1361 -- not tracked by PostgreSQL
1366 * ROUTINE_SEQUENCE_USAGE view
1369 -- not tracked by PostgreSQL
1374 * ROUTINE_TABLE_USAGE view
1377 -- not tracked by PostgreSQL
1385 CREATE VIEW routines AS
1386 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
1387 CAST(n.nspname AS sql_identifier) AS specific_schema,
1388 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1389 CAST(current_database() AS sql_identifier) AS routine_catalog,
1390 CAST(n.nspname AS sql_identifier) AS routine_schema,
1391 CAST(p.proname AS sql_identifier) AS routine_name,
1392 CAST('FUNCTION' AS character_data) AS routine_type,
1393 CAST(null AS sql_identifier) AS module_catalog,
1394 CAST(null AS sql_identifier) AS module_schema,
1395 CAST(null AS sql_identifier) AS module_name,
1396 CAST(null AS sql_identifier) AS udt_catalog,
1397 CAST(null AS sql_identifier) AS udt_schema,
1398 CAST(null AS sql_identifier) AS udt_name,
1401 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
1402 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
1403 ELSE 'USER-DEFINED' END AS character_data)
1405 CAST(null AS cardinal_number) AS character_maximum_length,
1406 CAST(null AS cardinal_number) AS character_octet_length,
1407 CAST(null AS sql_identifier) AS character_set_catalog,
1408 CAST(null AS sql_identifier) AS character_set_schema,
1409 CAST(null AS sql_identifier) AS character_set_name,
1410 CAST(null AS sql_identifier) AS collation_catalog,
1411 CAST(null AS sql_identifier) AS collation_schema,
1412 CAST(null AS sql_identifier) AS collation_name,
1413 CAST(null AS cardinal_number) AS numeric_precision,
1414 CAST(null AS cardinal_number) AS numeric_precision_radix,
1415 CAST(null AS cardinal_number) AS numeric_scale,
1416 CAST(null AS cardinal_number) AS datetime_precision,
1417 CAST(null AS character_data) AS interval_type,
1418 CAST(null AS character_data) AS interval_precision,
1419 CAST(current_database() AS sql_identifier) AS type_udt_catalog,
1420 CAST(nt.nspname AS sql_identifier) AS type_udt_schema,
1421 CAST(t.typname AS sql_identifier) AS type_udt_name,
1422 CAST(null AS sql_identifier) AS scope_catalog,
1423 CAST(null AS sql_identifier) AS scope_schema,
1424 CAST(null AS sql_identifier) AS scope_name,
1425 CAST(null AS cardinal_number) AS maximum_cardinality,
1426 CAST(0 AS sql_identifier) AS dtd_identifier,
1428 CAST(CASE WHEN l.lanname = 'sql' THEN 'SQL' ELSE 'EXTERNAL' END AS character_data)
1431 CASE WHEN pg_has_role(p.proowner, 'USAGE') THEN p.prosrc ELSE null END
1432 AS character_data) AS routine_definition,
1434 CASE WHEN l.lanname = 'c' THEN p.prosrc ELSE null END
1435 AS character_data) AS external_name,
1436 CAST(upper(l.lanname) AS character_data) AS external_language,
1438 CAST('GENERAL' AS character_data) AS parameter_style,
1439 CAST(CASE WHEN p.provolatile = 'i' THEN 'YES' ELSE 'NO' END AS character_data) AS is_deterministic,
1440 CAST('MODIFIES' AS character_data) AS sql_data_access,
1441 CAST(CASE WHEN p.proisstrict THEN 'YES' ELSE 'NO' END AS character_data) AS is_null_call,
1442 CAST(null AS character_data) AS sql_path,
1443 CAST('YES' AS character_data) AS schema_level_routine,
1444 CAST(0 AS cardinal_number) AS max_dynamic_result_sets,
1445 CAST(null AS character_data) AS is_user_defined_cast,
1446 CAST(null AS character_data) AS is_implicitly_invocable,
1447 CAST(CASE WHEN p.prosecdef THEN 'DEFINER' ELSE 'INVOKER' END AS character_data) AS security_type,
1448 CAST(null AS sql_identifier) AS to_sql_specific_catalog,
1449 CAST(null AS sql_identifier) AS to_sql_specific_schema,
1450 CAST(null AS sql_identifier) AS to_sql_specific_name,
1451 CAST('NO' AS character_data) AS as_locator,
1452 CAST(null AS time_stamp) AS created,
1453 CAST(null AS time_stamp) AS last_altered,
1454 CAST(null AS character_data) AS new_savepoint_level,
1455 CAST('YES' AS character_data) AS is_udt_dependent, -- FIXME?
1457 CAST(null AS character_data) AS result_cast_from_data_type,
1458 CAST(null AS character_data) AS result_cast_as_locator,
1459 CAST(null AS cardinal_number) AS result_cast_char_max_length,
1460 CAST(null AS cardinal_number) AS result_cast_char_octet_length,
1461 CAST(null AS sql_identifier) AS result_cast_char_set_catalog,
1462 CAST(null AS sql_identifier) AS result_cast_char_set_schema,
1463 CAST(null AS sql_identifier) AS result_cast_character_set_name,
1464 CAST(null AS sql_identifier) AS result_cast_collation_catalog,
1465 CAST(null AS sql_identifier) AS result_cast_collation_schema,
1466 CAST(null AS sql_identifier) AS result_cast_collation_name,
1467 CAST(null AS cardinal_number) AS result_cast_numeric_precision,
1468 CAST(null AS cardinal_number) AS result_cast_numeric_precision_radix,
1469 CAST(null AS cardinal_number) AS result_cast_numeric_scale,
1470 CAST(null AS cardinal_number) AS result_cast_datetime_precision,
1471 CAST(null AS character_data) AS result_cast_interval_type,
1472 CAST(null AS character_data) AS result_cast_interval_precision,
1473 CAST(null AS sql_identifier) AS result_cast_type_udt_catalog,
1474 CAST(null AS sql_identifier) AS result_cast_type_udt_schema,
1475 CAST(null AS sql_identifier) AS result_cast_type_udt_name,
1476 CAST(null AS sql_identifier) AS result_cast_scope_catalog,
1477 CAST(null AS sql_identifier) AS result_cast_scope_schema,
1478 CAST(null AS sql_identifier) AS result_cast_scope_name,
1479 CAST(null AS cardinal_number) AS result_cast_maximum_cardinality,
1480 CAST(null AS sql_identifier) AS result_cast_dtd_identifier
1482 FROM pg_namespace n, pg_proc p, pg_language l,
1483 pg_type t, pg_namespace nt
1485 WHERE n.oid = p.pronamespace AND p.prolang = l.oid
1486 AND p.prorettype = t.oid AND t.typnamespace = nt.oid
1487 AND (pg_has_role(p.proowner, 'USAGE')
1488 OR has_function_privilege(p.oid, 'EXECUTE'));
1490 GRANT SELECT ON routines TO PUBLIC;
1498 CREATE VIEW schemata AS
1499 SELECT CAST(current_database() AS sql_identifier) AS catalog_name,
1500 CAST(n.nspname AS sql_identifier) AS schema_name,
1501 CAST(u.rolname AS sql_identifier) AS schema_owner,
1502 CAST(null AS sql_identifier) AS default_character_set_catalog,
1503 CAST(null AS sql_identifier) AS default_character_set_schema,
1504 CAST(null AS sql_identifier) AS default_character_set_name,
1505 CAST(null AS character_data) AS sql_path
1506 FROM pg_namespace n, pg_authid u
1507 WHERE n.nspowner = u.oid AND pg_has_role(n.nspowner, 'USAGE');
1509 GRANT SELECT ON schemata TO PUBLIC;
1517 CREATE VIEW sequences AS
1518 SELECT CAST(current_database() AS sql_identifier) AS sequence_catalog,
1519 CAST(nc.nspname AS sql_identifier) AS sequence_schema,
1520 CAST(c.relname AS sql_identifier) AS sequence_name,
1521 CAST('bigint' AS character_data) AS data_type,
1522 CAST(64 AS cardinal_number) AS numeric_precision,
1523 CAST(2 AS cardinal_number) AS numeric_precision_radix,
1524 CAST(0 AS cardinal_number) AS numeric_scale,
1525 CAST(null AS cardinal_number) AS maximum_value, -- FIXME
1526 CAST(null AS cardinal_number) AS minimum_value, -- FIXME
1527 CAST(null AS cardinal_number) AS increment, -- FIXME
1528 CAST(null AS character_data) AS cycle_option -- FIXME
1529 FROM pg_namespace nc, pg_class c
1530 WHERE c.relnamespace = nc.oid
1532 AND (NOT pg_is_other_temp_schema(nc.oid))
1533 AND (pg_has_role(c.relowner, 'USAGE')
1534 OR has_table_privilege(c.oid, 'SELECT')
1535 OR has_table_privilege(c.oid, 'UPDATE') );
1537 GRANT SELECT ON sequences TO PUBLIC;
1542 * SQL_FEATURES table
1545 CREATE TABLE sql_features (
1546 feature_id character_data,
1547 feature_name character_data,
1548 sub_feature_id character_data,
1549 sub_feature_name character_data,
1550 is_supported character_data,
1551 is_verified_by character_data,
1552 comments character_data
1555 -- Will be filled with external data by initdb.
1557 GRANT SELECT ON sql_features TO PUBLIC;
1562 * SQL_IMPLEMENTATION_INFO table
1565 -- Note: Implementation information items are defined in ISO/IEC 9075-3:2003,
1568 CREATE TABLE sql_implementation_info (
1569 implementation_info_id character_data,
1570 implementation_info_name character_data,
1571 integer_value cardinal_number,
1572 character_value character_data,
1573 comments character_data
1576 INSERT INTO sql_implementation_info VALUES ('10003', 'CATALOG NAME', NULL, 'Y', NULL);
1577 INSERT INTO sql_implementation_info VALUES ('10004', 'COLLATING SEQUENCE', NULL, '', 'not supported');
1578 INSERT INTO sql_implementation_info VALUES ('23', 'CURSOR COMMIT BEHAVIOR', 1, NULL, 'close cursors and retain prepared statements');
1579 INSERT INTO sql_implementation_info VALUES ('2', 'DATA SOURCE NAME', NULL, '', NULL);
1580 INSERT INTO sql_implementation_info VALUES ('17', 'DBMS NAME', NULL, (select trim(trailing ' ' from substring(version() from '^[^0-9]*'))), NULL);
1581 INSERT INTO sql_implementation_info VALUES ('18', 'DBMS VERSION', NULL, '???', NULL); -- filled by initdb
1582 INSERT INTO sql_implementation_info VALUES ('26', 'DEFAULT TRANSACTION ISOLATION', 2, NULL, 'READ COMMITTED; user-settable');
1583 INSERT INTO sql_implementation_info VALUES ('28', 'IDENTIFIER CASE', 3, NULL, 'stored in mixed case - case sensitive');
1584 INSERT INTO sql_implementation_info VALUES ('85', 'NULL COLLATION', 0, NULL, 'nulls higher than non-nulls');
1585 INSERT INTO sql_implementation_info VALUES ('13', 'SERVER NAME', NULL, '', NULL);
1586 INSERT INTO sql_implementation_info VALUES ('94', 'SPECIAL CHARACTERS', NULL, '', 'all non-ASCII characters allowed');
1587 INSERT INTO sql_implementation_info VALUES ('46', 'TRANSACTION CAPABLE', 2, NULL, 'both DML and DDL');
1589 GRANT SELECT ON sql_implementation_info TO PUBLIC;
1594 * SQL_LANGUAGES table
1597 CREATE TABLE sql_languages (
1598 sql_language_source character_data,
1599 sql_language_year character_data,
1600 sql_language_conformance character_data,
1601 sql_language_integrity character_data,
1602 sql_language_implementation character_data,
1603 sql_language_binding_style character_data,
1604 sql_language_programming_language character_data
1607 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'DIRECT', NULL);
1608 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
1609 INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'DIRECT', NULL);
1610 INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
1612 GRANT SELECT ON sql_languages TO PUBLIC;
1617 * SQL_PACKAGES table
1620 CREATE TABLE sql_packages (
1621 feature_id character_data,
1622 feature_name character_data,
1623 is_supported character_data,
1624 is_verified_by character_data,
1625 comments character_data
1628 INSERT INTO sql_packages VALUES ('PKG000', 'Core', 'NO', NULL, '');
1629 INSERT INTO sql_packages VALUES ('PKG001', 'Enhanced datetime facilities', 'YES', NULL, '');
1630 INSERT INTO sql_packages VALUES ('PKG002', 'Enhanced integrity management', 'NO', NULL, '');
1631 INSERT INTO sql_packages VALUES ('PKG003', 'OLAP facilities', 'NO', NULL, '');
1632 INSERT INTO sql_packages VALUES ('PKG004', 'PSM', 'NO', NULL, 'PL/pgSQL is similar.');
1633 INSERT INTO sql_packages VALUES ('PKG005', 'CLI', 'NO', NULL, 'ODBC is similar.');
1634 INSERT INTO sql_packages VALUES ('PKG006', 'Basic object support', 'NO', NULL, '');
1635 INSERT INTO sql_packages VALUES ('PKG007', 'Enhanced object support', 'NO', NULL, '');
1636 INSERT INTO sql_packages VALUES ('PKG008', 'Active database', 'NO', NULL, '');
1637 INSERT INTO sql_packages VALUES ('PKG010', 'OLAP', 'NO', NULL, 'NO');
1639 GRANT SELECT ON sql_packages TO PUBLIC;
1647 CREATE TABLE sql_parts (
1648 feature_id character_data,
1649 feature_name character_data,
1650 is_supported character_data,
1651 is_verified_by character_data,
1652 comments character_data
1655 INSERT INTO sql_parts VALUES ('1', 'Framework (SQL/Framework)', 'NO', NULL, '');
1656 INSERT INTO sql_parts VALUES ('2', 'Foundation (SQL/Foundation)', 'NO', NULL, '');
1657 INSERT INTO sql_parts VALUES ('3', 'Call-Level Interface (SQL/CLI)', 'NO', NULL, '');
1658 INSERT INTO sql_parts VALUES ('4', 'Persistent Stored Modules (SQL/PSM)', 'NO', NULL, '');
1659 INSERT INTO sql_parts VALUES ('9', 'Management of External Data (SQL/MED)', 'NO', NULL, '');
1660 INSERT INTO sql_parts VALUES ('10', 'Object Language Bindings (SQL/OLB)', 'NO', NULL, '');
1661 INSERT INTO sql_parts VALUES ('11', 'Information and Definition Schema (SQL/Schemata)', 'NO', NULL, '');
1662 INSERT INTO sql_parts VALUES ('13', 'Routines and Types Using the Java Programming Language (SQL/JRT)', 'NO', NULL, '');
1663 INSERT INTO sql_parts VALUES ('14', 'XML-Related Specifications (SQL/XML)', 'YES', NULL, '');
1671 -- Note: Sizing items are defined in ISO/IEC 9075-3:2003, clause 7.2.
1673 CREATE TABLE sql_sizing (
1674 sizing_id cardinal_number,
1675 sizing_name character_data,
1676 supported_value cardinal_number,
1677 comments character_data
1680 INSERT INTO sql_sizing VALUES (34, 'MAXIMUM CATALOG NAME LENGTH', 63, NULL);
1681 INSERT INTO sql_sizing VALUES (30, 'MAXIMUM COLUMN NAME LENGTH', 63, NULL);
1682 INSERT INTO sql_sizing VALUES (97, 'MAXIMUM COLUMNS IN GROUP BY', 0, NULL);
1683 INSERT INTO sql_sizing VALUES (99, 'MAXIMUM COLUMNS IN ORDER BY', 0, NULL);
1684 INSERT INTO sql_sizing VALUES (100, 'MAXIMUM COLUMNS IN SELECT', 1664, NULL); -- match MaxTupleAttributeNumber
1685 INSERT INTO sql_sizing VALUES (101, 'MAXIMUM COLUMNS IN TABLE', 1600, NULL); -- match MaxHeapAttributeNumber
1686 INSERT INTO sql_sizing VALUES (1, 'MAXIMUM CONCURRENT ACTIVITIES', 0, NULL);
1687 INSERT INTO sql_sizing VALUES (31, 'MAXIMUM CURSOR NAME LENGTH', 63, NULL);
1688 INSERT INTO sql_sizing VALUES (0, 'MAXIMUM DRIVER CONNECTIONS', NULL, NULL);
1689 INSERT INTO sql_sizing VALUES (10005, 'MAXIMUM IDENTIFIER LENGTH', 63, NULL);
1690 INSERT INTO sql_sizing VALUES (32, 'MAXIMUM SCHEMA NAME LENGTH', 63, NULL);
1691 INSERT INTO sql_sizing VALUES (20000, 'MAXIMUM STATEMENT OCTETS', 0, NULL);
1692 INSERT INTO sql_sizing VALUES (20001, 'MAXIMUM STATEMENT OCTETS DATA', 0, NULL);
1693 INSERT INTO sql_sizing VALUES (20002, 'MAXIMUM STATEMENT OCTETS SCHEMA', 0, NULL);
1694 INSERT INTO sql_sizing VALUES (35, 'MAXIMUM TABLE NAME LENGTH', 63, NULL);
1695 INSERT INTO sql_sizing VALUES (106, 'MAXIMUM TABLES IN SELECT', 0, NULL);
1696 INSERT INTO sql_sizing VALUES (107, 'MAXIMUM USER NAME LENGTH', 63, NULL);
1697 INSERT INTO sql_sizing VALUES (25000, 'MAXIMUM CURRENT DEFAULT TRANSFORM GROUP LENGTH', NULL, NULL);
1698 INSERT INTO sql_sizing VALUES (25001, 'MAXIMUM CURRENT TRANSFORM GROUP LENGTH', NULL, NULL);
1699 INSERT INTO sql_sizing VALUES (25002, 'MAXIMUM CURRENT PATH LENGTH', 0, NULL);
1700 INSERT INTO sql_sizing VALUES (25003, 'MAXIMUM CURRENT ROLE LENGTH', NULL, NULL);
1701 INSERT INTO sql_sizing VALUES (25004, 'MAXIMUM SESSION USER LENGTH', 63, NULL);
1702 INSERT INTO sql_sizing VALUES (25005, 'MAXIMUM SYSTEM USER LENGTH', 63, NULL);
1705 SET supported_value = (SELECT typlen-1 FROM pg_catalog.pg_type WHERE typname = 'name'),
1706 comments = 'Might be less, depending on character set.'
1707 WHERE supported_value = 63;
1709 GRANT SELECT ON sql_sizing TO PUBLIC;
1714 * SQL_SIZING_PROFILES table
1717 -- The data in this table are defined by various profiles of SQL.
1718 -- Since we don't have any information about such profiles, we provide
1721 CREATE TABLE sql_sizing_profiles (
1722 sizing_id cardinal_number,
1723 sizing_name character_data,
1724 profile_id character_data,
1725 required_value cardinal_number,
1726 comments character_data
1729 GRANT SELECT ON sql_sizing_profiles TO PUBLIC;
1734 * TABLE_CONSTRAINTS view
1737 CREATE VIEW table_constraints AS
1738 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1739 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
1740 CAST(c.conname AS sql_identifier) AS constraint_name,
1741 CAST(current_database() AS sql_identifier) AS table_catalog,
1742 CAST(nr.nspname AS sql_identifier) AS table_schema,
1743 CAST(r.relname AS sql_identifier) AS table_name,
1745 CASE c.contype WHEN 'c' THEN 'CHECK'
1746 WHEN 'f' THEN 'FOREIGN KEY'
1747 WHEN 'p' THEN 'PRIMARY KEY'
1748 WHEN 'u' THEN 'UNIQUE' END
1749 AS character_data) AS constraint_type,
1750 CAST(CASE WHEN c.condeferrable THEN 'YES' ELSE 'NO' END AS character_data)
1752 CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS character_data)
1753 AS initially_deferred
1755 FROM pg_namespace nc,
1760 WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace
1761 AND c.conrelid = r.oid
1763 AND (NOT pg_is_other_temp_schema(nr.oid))
1764 AND (pg_has_role(r.relowner, 'USAGE')
1765 -- SELECT privilege omitted, per SQL standard
1766 OR has_table_privilege(r.oid, 'INSERT')
1767 OR has_table_privilege(r.oid, 'UPDATE')
1768 OR has_table_privilege(r.oid, 'DELETE')
1769 OR has_table_privilege(r.oid, 'TRUNCATE')
1770 OR has_table_privilege(r.oid, 'REFERENCES')
1771 OR has_table_privilege(r.oid, 'TRIGGER') )
1775 -- not-null constraints
1777 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1778 CAST(nr.nspname AS sql_identifier) AS constraint_schema,
1779 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
1780 CAST(current_database() AS sql_identifier) AS table_catalog,
1781 CAST(nr.nspname AS sql_identifier) AS table_schema,
1782 CAST(r.relname AS sql_identifier) AS table_name,
1783 CAST('CHECK' AS character_data) AS constraint_type,
1784 CAST('NO' AS character_data) AS is_deferrable,
1785 CAST('NO' AS character_data) AS initially_deferred
1787 FROM pg_namespace nr,
1791 WHERE nr.oid = r.relnamespace
1792 AND r.oid = a.attrelid
1795 AND NOT a.attisdropped
1797 AND (NOT pg_is_other_temp_schema(nr.oid))
1798 AND (pg_has_role(r.relowner, 'USAGE')
1799 OR has_table_privilege(r.oid, 'SELECT')
1800 OR has_table_privilege(r.oid, 'INSERT')
1801 OR has_table_privilege(r.oid, 'UPDATE')
1802 OR has_table_privilege(r.oid, 'DELETE')
1803 OR has_table_privilege(r.oid, 'TRUNCATE')
1804 OR has_table_privilege(r.oid, 'REFERENCES')
1805 OR has_table_privilege(r.oid, 'TRIGGER') );
1807 GRANT SELECT ON table_constraints TO PUBLIC;
1812 * TABLE_METHOD_PRIVILEGES view
1815 -- feature not supported
1820 * TABLE_PRIVILEGES view
1823 CREATE VIEW table_privileges AS
1824 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1825 CAST(grantee.rolname AS sql_identifier) AS grantee,
1826 CAST(current_database() AS sql_identifier) AS table_catalog,
1827 CAST(nc.nspname AS sql_identifier) AS table_schema,
1828 CAST(c.relname AS sql_identifier) AS table_name,
1829 CAST(pr.type AS character_data) AS privilege_type,
1831 CASE WHEN aclcontains(c.relacl,
1832 makeaclitem(grantee.oid, u_grantor.oid, pr.type, true))
1833 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable,
1834 CAST('NO' AS character_data) AS with_hierarchy
1838 pg_authid u_grantor,
1840 SELECT oid, rolname FROM pg_authid
1842 SELECT 0::oid, 'PUBLIC'
1843 ) AS grantee (oid, rolname),
1844 (SELECT 'SELECT' UNION ALL
1845 SELECT 'INSERT' UNION ALL
1846 SELECT 'UPDATE' UNION ALL
1847 SELECT 'DELETE' UNION ALL
1848 SELECT 'TRUNCATE' UNION ALL
1849 SELECT 'REFERENCES' UNION ALL
1850 SELECT 'TRIGGER') AS pr (type)
1852 WHERE c.relnamespace = nc.oid
1853 AND c.relkind IN ('r', 'v')
1854 AND aclcontains(c.relacl,
1855 makeaclitem(grantee.oid, u_grantor.oid, pr.type, false))
1856 AND (pg_has_role(u_grantor.oid, 'USAGE')
1857 OR pg_has_role(grantee.oid, 'USAGE')
1858 OR grantee.rolname = 'PUBLIC');
1860 GRANT SELECT ON table_privileges TO PUBLIC;
1868 CREATE VIEW tables AS
1869 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
1870 CAST(nc.nspname AS sql_identifier) AS table_schema,
1871 CAST(c.relname AS sql_identifier) AS table_name,
1874 CASE WHEN nc.oid = pg_my_temp_schema() THEN 'LOCAL TEMPORARY'
1875 WHEN c.relkind = 'r' THEN 'BASE TABLE'
1876 WHEN c.relkind = 'v' THEN 'VIEW'
1878 AS character_data) AS table_type,
1880 CAST(null AS sql_identifier) AS self_referencing_column_name,
1881 CAST(null AS character_data) AS reference_generation,
1883 CAST(null AS sql_identifier) AS user_defined_type_catalog,
1884 CAST(null AS sql_identifier) AS user_defined_type_schema,
1885 CAST(null AS sql_identifier) AS user_defined_type_name,
1887 CAST(CASE WHEN c.relkind = 'r'
1889 AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '3' AND is_instead))
1890 THEN 'YES' ELSE 'NO' END AS character_data) AS is_insertable_into,
1892 CAST('NO' AS character_data) AS is_typed,
1894 CASE WHEN nc.oid = pg_my_temp_schema() THEN 'PRESERVE' -- FIXME
1896 AS character_data) AS commit_action
1898 FROM pg_namespace nc, pg_class c
1900 WHERE c.relnamespace = nc.oid
1901 AND c.relkind IN ('r', 'v')
1902 AND (NOT pg_is_other_temp_schema(nc.oid))
1903 AND (pg_has_role(c.relowner, 'USAGE')
1904 OR has_table_privilege(c.oid, 'SELECT')
1905 OR has_table_privilege(c.oid, 'INSERT')
1906 OR has_table_privilege(c.oid, 'UPDATE')
1907 OR has_table_privilege(c.oid, 'DELETE')
1908 OR has_table_privilege(c.oid, 'TRUNCATE')
1909 OR has_table_privilege(c.oid, 'REFERENCES')
1910 OR has_table_privilege(c.oid, 'TRIGGER') );
1912 GRANT SELECT ON tables TO PUBLIC;
1920 -- feature not supported
1928 -- feature not supported
1933 * TRIGGERED_UPDATE_COLUMNS view
1936 -- PostgreSQL doesn't allow the specification of individual triggered
1937 -- update columns, so this view is empty.
1939 CREATE VIEW triggered_update_columns AS
1940 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1941 CAST(null AS sql_identifier) AS trigger_schema,
1942 CAST(null AS sql_identifier) AS trigger_name,
1943 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1944 CAST(null AS sql_identifier) AS event_object_schema,
1945 CAST(null AS sql_identifier) AS event_object_table,
1946 CAST(null AS sql_identifier) AS event_object_column
1949 GRANT SELECT ON triggered_update_columns TO PUBLIC;
1954 * TRIGGER_COLUMN_USAGE view
1957 -- not tracked by PostgreSQL
1962 * TRIGGER_ROUTINE_USAGE view
1965 -- not tracked by PostgreSQL
1970 * TRIGGER_SEQUENCE_USAGE view
1973 -- not tracked by PostgreSQL
1978 * TRIGGER_TABLE_USAGE view
1981 -- not tracked by PostgreSQL
1989 CREATE VIEW triggers AS
1990 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1991 CAST(n.nspname AS sql_identifier) AS trigger_schema,
1992 CAST(t.tgname AS sql_identifier) AS trigger_name,
1993 CAST(em.text AS character_data) AS event_manipulation,
1994 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1995 CAST(n.nspname AS sql_identifier) AS event_object_schema,
1996 CAST(c.relname AS sql_identifier) AS event_object_table,
1997 CAST(null AS cardinal_number) AS action_order,
1998 CAST(null AS character_data) AS action_condition,
2000 substring(pg_get_triggerdef(t.oid) from
2001 position('EXECUTE PROCEDURE' in substring(pg_get_triggerdef(t.oid) from 48)) + 47)
2002 AS character_data) AS action_statement,
2004 CASE WHEN t.tgtype & 1 = 1 THEN 'ROW' ELSE 'STATEMENT' END
2005 AS character_data) AS action_orientation,
2007 CASE WHEN t.tgtype & 2 = 2 THEN 'BEFORE' ELSE 'AFTER' END
2008 AS character_data) AS condition_timing,
2009 CAST(null AS sql_identifier) AS condition_reference_old_table,
2010 CAST(null AS sql_identifier) AS condition_reference_new_table,
2011 CAST(null AS sql_identifier) AS condition_reference_old_row,
2012 CAST(null AS sql_identifier) AS condition_reference_new_row,
2013 CAST(null AS time_stamp) AS created
2015 FROM pg_namespace n, pg_class c, pg_trigger t,
2016 (SELECT 4, 'INSERT' UNION ALL
2017 SELECT 8, 'DELETE' UNION ALL
2018 SELECT 16, 'UPDATE') AS em (num, text)
2020 WHERE n.oid = c.relnamespace
2021 AND c.oid = t.tgrelid
2022 AND t.tgtype & em.num <> 0
2023 AND NOT t.tgisconstraint
2024 AND (NOT pg_is_other_temp_schema(n.oid))
2025 AND (pg_has_role(c.relowner, 'USAGE')
2026 -- SELECT privilege omitted, per SQL standard
2027 OR has_table_privilege(c.oid, 'INSERT')
2028 OR has_table_privilege(c.oid, 'UPDATE')
2029 OR has_table_privilege(c.oid, 'DELETE')
2030 OR has_table_privilege(c.oid, 'TRUNCATE')
2031 OR has_table_privilege(c.oid, 'REFERENCES')
2032 OR has_table_privilege(c.oid, 'TRIGGER') );
2034 GRANT SELECT ON triggers TO PUBLIC;
2039 * UDT_PRIVILEGES view
2042 -- feature not supported
2047 * USAGE_PRIVILEGES view
2050 CREATE VIEW usage_privileges AS
2053 -- Domains have no real privileges, so we represent all domains with implicit usage privilege here.
2054 SELECT CAST(u.rolname AS sql_identifier) AS grantor,
2055 CAST('PUBLIC' AS sql_identifier) AS grantee,
2056 CAST(current_database() AS sql_identifier) AS object_catalog,
2057 CAST(n.nspname AS sql_identifier) AS object_schema,
2058 CAST(t.typname AS sql_identifier) AS object_name,
2059 CAST('DOMAIN' AS character_data) AS object_type,
2060 CAST('USAGE' AS character_data) AS privilege_type,
2061 CAST('NO' AS character_data) AS is_grantable
2067 WHERE u.oid = t.typowner
2068 AND t.typnamespace = n.oid
2073 /* foreign-data wrappers */
2074 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2075 CAST(grantee.rolname AS sql_identifier) AS grantee,
2076 CAST(current_database() AS sql_identifier) AS object_catalog,
2077 CAST('' AS sql_identifier) AS object_schema,
2078 CAST(fdw.fdwname AS sql_identifier) AS object_name,
2079 CAST('FOREIGN DATA WRAPPER' AS character_data) AS object_type,
2080 CAST('USAGE' AS character_data) AS privilege_type,
2082 CASE WHEN aclcontains(fdw.fdwacl,
2083 makeaclitem(grantee.oid, u_grantor.oid, 'USAGE', true))
2084 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
2086 FROM pg_foreign_data_wrapper fdw,
2087 pg_authid u_grantor,
2089 SELECT oid, rolname FROM pg_authid
2091 SELECT 0::oid, 'PUBLIC'
2092 ) AS grantee (oid, rolname)
2094 WHERE aclcontains(fdw.fdwacl,
2095 makeaclitem(grantee.oid, u_grantor.oid, 'USAGE', false))
2096 AND (pg_has_role(u_grantor.oid, 'USAGE')
2097 OR pg_has_role(grantee.oid, 'USAGE')
2098 OR grantee.rolname = 'PUBLIC')
2102 /* foreign servers */
2103 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2104 CAST(grantee.rolname AS sql_identifier) AS grantee,
2105 CAST(current_database() AS sql_identifier) AS object_catalog,
2106 CAST('' AS sql_identifier) AS object_schema,
2107 CAST(srv.srvname AS sql_identifier) AS object_name,
2108 CAST('FOREIGN SERVER' AS character_data) AS object_type,
2109 CAST('USAGE' AS character_data) AS privilege_type,
2111 CASE WHEN aclcontains(srv.srvacl,
2112 makeaclitem(grantee.oid, u_grantor.oid, 'USAGE', true))
2113 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
2115 FROM pg_foreign_server srv,
2116 pg_authid u_grantor,
2118 SELECT oid, rolname FROM pg_authid
2120 SELECT 0::oid, 'PUBLIC'
2121 ) AS grantee (oid, rolname)
2123 WHERE aclcontains(srv.srvacl,
2124 makeaclitem(grantee.oid, u_grantor.oid, 'USAGE', false))
2125 AND (pg_has_role(u_grantor.oid, 'USAGE')
2126 OR pg_has_role(grantee.oid, 'USAGE')
2127 OR grantee.rolname = 'PUBLIC');
2129 GRANT SELECT ON usage_privileges TO PUBLIC;
2134 * USER_DEFINED_TYPES view
2137 -- feature not supported
2145 CREATE VIEW view_column_usage AS
2147 CAST(current_database() AS sql_identifier) AS view_catalog,
2148 CAST(nv.nspname AS sql_identifier) AS view_schema,
2149 CAST(v.relname AS sql_identifier) AS view_name,
2150 CAST(current_database() AS sql_identifier) AS table_catalog,
2151 CAST(nt.nspname AS sql_identifier) AS table_schema,
2152 CAST(t.relname AS sql_identifier) AS table_name,
2153 CAST(a.attname AS sql_identifier) AS column_name
2155 FROM pg_namespace nv, pg_class v, pg_depend dv,
2156 pg_depend dt, pg_class t, pg_namespace nt,
2159 WHERE nv.oid = v.relnamespace
2161 AND v.oid = dv.refobjid
2162 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2163 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2164 AND dv.deptype = 'i'
2165 AND dv.objid = dt.objid
2166 AND dv.refobjid <> dt.refobjid
2167 AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2168 AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2169 AND dt.refobjid = t.oid
2170 AND t.relnamespace = nt.oid
2171 AND t.relkind IN ('r', 'v')
2172 AND t.oid = a.attrelid
2173 AND dt.refobjsubid = a.attnum
2174 AND pg_has_role(t.relowner, 'USAGE');
2176 GRANT SELECT ON view_column_usage TO PUBLIC;
2181 * VIEW_ROUTINE_USAGE
2184 CREATE VIEW view_routine_usage AS
2186 CAST(current_database() AS sql_identifier) AS table_catalog,
2187 CAST(nv.nspname AS sql_identifier) AS table_schema,
2188 CAST(v.relname AS sql_identifier) AS table_name,
2189 CAST(current_database() AS sql_identifier) AS specific_catalog,
2190 CAST(np.nspname AS sql_identifier) AS specific_schema,
2191 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name
2193 FROM pg_namespace nv, pg_class v, pg_depend dv,
2194 pg_depend dp, pg_proc p, pg_namespace np
2196 WHERE nv.oid = v.relnamespace
2198 AND v.oid = dv.refobjid
2199 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2200 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2201 AND dv.deptype = 'i'
2202 AND dv.objid = dp.objid
2203 AND dp.classid = 'pg_catalog.pg_rewrite'::regclass
2204 AND dp.refclassid = 'pg_catalog.pg_proc'::regclass
2205 AND dp.refobjid = p.oid
2206 AND p.pronamespace = np.oid
2207 AND pg_has_role(p.proowner, 'USAGE');
2209 GRANT SELECT ON view_routine_usage TO PUBLIC;
2217 CREATE VIEW view_table_usage AS
2219 CAST(current_database() AS sql_identifier) AS view_catalog,
2220 CAST(nv.nspname AS sql_identifier) AS view_schema,
2221 CAST(v.relname AS sql_identifier) AS view_name,
2222 CAST(current_database() AS sql_identifier) AS table_catalog,
2223 CAST(nt.nspname AS sql_identifier) AS table_schema,
2224 CAST(t.relname AS sql_identifier) AS table_name
2226 FROM pg_namespace nv, pg_class v, pg_depend dv,
2227 pg_depend dt, pg_class t, pg_namespace nt
2229 WHERE nv.oid = v.relnamespace
2231 AND v.oid = dv.refobjid
2232 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2233 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2234 AND dv.deptype = 'i'
2235 AND dv.objid = dt.objid
2236 AND dv.refobjid <> dt.refobjid
2237 AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2238 AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2239 AND dt.refobjid = t.oid
2240 AND t.relnamespace = nt.oid
2241 AND t.relkind IN ('r', 'v')
2242 AND pg_has_role(t.relowner, 'USAGE');
2244 GRANT SELECT ON view_table_usage TO PUBLIC;
2252 CREATE VIEW views AS
2253 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
2254 CAST(nc.nspname AS sql_identifier) AS table_schema,
2255 CAST(c.relname AS sql_identifier) AS table_name,
2258 CASE WHEN pg_has_role(c.relowner, 'USAGE')
2259 THEN pg_get_viewdef(c.oid)
2261 AS character_data) AS view_definition,
2263 CAST('NONE' AS character_data) AS check_option,
2266 CASE WHEN EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '2' AND is_instead)
2267 AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '4' AND is_instead)
2268 THEN 'YES' ELSE 'NO' END
2269 AS character_data) AS is_updatable,
2272 CASE WHEN EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '3' AND is_instead)
2273 THEN 'YES' ELSE 'NO' END
2274 AS character_data) AS is_insertable_into
2276 FROM pg_namespace nc, pg_class c
2278 WHERE c.relnamespace = nc.oid
2280 AND (NOT pg_is_other_temp_schema(nc.oid))
2281 AND (pg_has_role(c.relowner, 'USAGE')
2282 OR has_table_privilege(c.oid, 'SELECT')
2283 OR has_table_privilege(c.oid, 'INSERT')
2284 OR has_table_privilege(c.oid, 'UPDATE')
2285 OR has_table_privilege(c.oid, 'DELETE')
2286 OR has_table_privilege(c.oid, 'TRUNCATE')
2287 OR has_table_privilege(c.oid, 'REFERENCES')
2288 OR has_table_privilege(c.oid, 'TRIGGER') );
2290 GRANT SELECT ON views TO PUBLIC;
2293 -- The following views have dependencies that force them to appear out of order.
2297 * DATA_TYPE_PRIVILEGES view
2300 CREATE VIEW data_type_privileges AS
2301 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2302 CAST(x.objschema AS sql_identifier) AS object_schema,
2303 CAST(x.objname AS sql_identifier) AS object_name,
2304 CAST(x.objtype AS character_data) AS object_type,
2305 CAST(x.objdtdid AS sql_identifier) AS dtd_identifier
2309 SELECT udt_schema, udt_name, 'USER-DEFINED TYPE'::text, dtd_identifier FROM attributes
2311 SELECT table_schema, table_name, 'TABLE'::text, dtd_identifier FROM columns
2313 SELECT domain_schema, domain_name, 'DOMAIN'::text, dtd_identifier FROM domains
2315 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM parameters
2317 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM routines
2318 ) AS x (objschema, objname, objtype, objdtdid);
2320 GRANT SELECT ON data_type_privileges TO PUBLIC;
2325 * ELEMENT_TYPES view
2328 CREATE VIEW element_types AS
2329 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2330 CAST(n.nspname AS sql_identifier) AS object_schema,
2331 CAST(x.objname AS sql_identifier) AS object_name,
2332 CAST(x.objtype AS character_data) AS object_type,
2333 CAST(x.objdtdid AS sql_identifier) AS collection_type_identifier,
2335 CASE WHEN nbt.nspname = 'pg_catalog' THEN format_type(bt.oid, null)
2336 ELSE 'USER-DEFINED' END AS character_data) AS data_type,
2338 CAST(null AS cardinal_number) AS character_maximum_length,
2339 CAST(null AS cardinal_number) AS character_octet_length,
2340 CAST(null AS sql_identifier) AS character_set_catalog,
2341 CAST(null AS sql_identifier) AS character_set_schema,
2342 CAST(null AS sql_identifier) AS character_set_name,
2343 CAST(null AS sql_identifier) AS collation_catalog,
2344 CAST(null AS sql_identifier) AS collation_schema,
2345 CAST(null AS sql_identifier) AS collation_name,
2346 CAST(null AS cardinal_number) AS numeric_precision,
2347 CAST(null AS cardinal_number) AS numeric_precision_radix,
2348 CAST(null AS cardinal_number) AS numeric_scale,
2349 CAST(null AS cardinal_number) AS datetime_precision,
2350 CAST(null AS character_data) AS interval_type,
2351 CAST(null AS character_data) AS interval_precision,
2353 CAST(null AS character_data) AS domain_default, -- XXX maybe a bug in the standard
2355 CAST(current_database() AS sql_identifier) AS udt_catalog,
2356 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
2357 CAST(bt.typname AS sql_identifier) AS udt_name,
2359 CAST(null AS sql_identifier) AS scope_catalog,
2360 CAST(null AS sql_identifier) AS scope_schema,
2361 CAST(null AS sql_identifier) AS scope_name,
2363 CAST(null AS cardinal_number) AS maximum_cardinality,
2364 CAST('a' || CAST(x.objdtdid AS text) AS sql_identifier) AS dtd_identifier
2366 FROM pg_namespace n, pg_type at, pg_namespace nbt, pg_type bt,
2369 SELECT c.relnamespace, CAST(c.relname AS sql_identifier),
2370 'TABLE'::text, a.attnum, a.atttypid
2371 FROM pg_class c, pg_attribute a
2372 WHERE c.oid = a.attrelid
2373 AND c.relkind IN ('r', 'v')
2374 AND attnum > 0 AND NOT attisdropped
2379 SELECT t.typnamespace, CAST(t.typname AS sql_identifier),
2380 'DOMAIN'::text, 1, t.typbasetype
2382 WHERE t.typtype = 'd'
2387 SELECT pronamespace, CAST(proname || '_' || CAST(oid AS text) AS sql_identifier),
2388 'ROUTINE'::text, (ss.x).n, (ss.x).x
2389 FROM (SELECT p.pronamespace, p.proname, p.oid,
2390 _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
2391 FROM pg_proc p) AS ss
2396 SELECT p.pronamespace, CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier),
2397 'ROUTINE'::text, 0, p.prorettype
2400 ) AS x (objschema, objname, objtype, objdtdid, objtypeid)
2402 WHERE n.oid = x.objschema
2403 AND at.oid = x.objtypeid
2404 AND (at.typelem <> 0 AND at.typlen = -1)
2405 AND at.typelem = bt.oid
2406 AND nbt.oid = bt.typnamespace
2408 AND (n.nspname, x.objname, x.objtype, CAST(x.objdtdid AS sql_identifier)) IN
2409 ( SELECT object_schema, object_name, object_type, dtd_identifier
2410 FROM data_type_privileges );
2412 GRANT SELECT ON element_types TO PUBLIC;
2415 -- SQL/MED views; these use section numbers from part 9 of the standard.
2417 /* Base view for foreign-data wrappers */
2418 CREATE VIEW _pg_foreign_data_wrappers AS
2422 CAST(current_database() AS sql_identifier) AS foreign_data_wrapper_catalog,
2423 CAST(fdwname AS sql_identifier) AS foreign_data_wrapper_name,
2424 CAST(u.rolname AS sql_identifier) AS authorization_identifier,
2425 CAST(fdwlibrary AS character_data) AS library_name,
2426 CAST('c' AS character_data) AS foreign_data_wrapper_language
2427 FROM pg_foreign_data_wrapper w, pg_authid u
2428 WHERE u.oid = w.fdwowner
2429 AND (pg_has_role(fdwowner, 'USAGE')
2430 OR has_foreign_data_wrapper_privilege(w.oid, 'USAGE'));
2435 * FOREIGN_DATA_WRAPPER_OPTIONS view
2437 CREATE VIEW foreign_data_wrapper_options AS
2438 SELECT foreign_data_wrapper_catalog,
2439 foreign_data_wrapper_name,
2440 CAST((pg_options_to_table(w.fdwoptions)).option_name AS sql_identifier) AS option_name,
2441 CAST((pg_options_to_table(w.fdwoptions)).option_value AS character_data) AS option_value
2442 FROM _pg_foreign_data_wrappers w;
2444 GRANT SELECT ON foreign_data_wrapper_options TO PUBLIC;
2449 * FOREIGN_DATA_WRAPPERS view
2451 CREATE VIEW foreign_data_wrappers AS
2452 SELECT foreign_data_wrapper_catalog,
2453 foreign_data_wrapper_name,
2454 authorization_identifier,
2456 foreign_data_wrapper_language
2457 FROM _pg_foreign_data_wrappers w;
2459 GRANT SELECT ON foreign_data_wrappers TO PUBLIC;
2462 /* Base view for foreign servers */
2463 CREATE VIEW _pg_foreign_servers AS
2466 CAST(current_database() AS sql_identifier) AS foreign_server_catalog,
2467 CAST(srvname AS sql_identifier) AS foreign_server_name,
2468 w.foreign_data_wrapper_catalog,
2469 w.foreign_data_wrapper_name,
2470 CAST(srvtype AS character_data) AS foreign_server_type,
2471 CAST(srvversion AS character_data) AS foreign_server_version,
2472 CAST(u.rolname AS sql_identifier) AS authorization_identifier
2473 FROM pg_foreign_server s, _pg_foreign_data_wrappers w, pg_authid u
2474 WHERE w.oid = s.srvfdw
2475 AND u.oid = s.srvowner
2476 AND (pg_has_role(s.srvowner, 'USAGE')
2477 OR has_server_privilege(s.oid, 'USAGE'));
2482 * FOREIGN_SERVER_OPTIONS view
2484 CREATE VIEW foreign_server_options AS
2485 SELECT foreign_server_catalog,
2486 foreign_server_name,
2487 CAST((pg_options_to_table(s.srvoptions)).option_name AS sql_identifier) AS option_name,
2488 CAST((pg_options_to_table(s.srvoptions)).option_value AS character_data) AS option_value
2489 FROM _pg_foreign_servers s;
2491 GRANT SELECT ON TABLE foreign_server_options TO PUBLIC;
2496 * FOREIGN_SERVERS view
2498 CREATE VIEW foreign_servers AS
2499 SELECT foreign_server_catalog,
2500 foreign_server_name,
2501 foreign_data_wrapper_catalog,
2502 foreign_data_wrapper_name,
2503 foreign_server_type,
2504 foreign_server_version,
2505 authorization_identifier
2506 FROM _pg_foreign_servers;
2508 GRANT SELECT ON foreign_servers TO PUBLIC;
2511 /* Base view for user mappings */
2512 CREATE VIEW _pg_user_mappings AS
2515 CAST(COALESCE(u.rolname,'PUBLIC') AS sql_identifier ) AS authorization_identifier,
2516 s.foreign_server_catalog,
2517 s.foreign_server_name
2518 FROM pg_user_mapping um LEFT JOIN pg_authid u ON (u.oid = um.umuser),
2519 _pg_foreign_servers s
2520 WHERE s.oid = um.umserver;
2525 * USER_MAPPING_OPTIONS view
2527 CREATE VIEW user_mapping_options AS
2528 SELECT authorization_identifier,
2529 foreign_server_catalog,
2530 foreign_server_name,
2531 CAST((pg_options_to_table(um.umoptions)).option_name AS sql_identifier) AS option_name,
2532 CAST((pg_options_to_table(um.umoptions)).option_value AS character_data) AS option_value
2533 FROM _pg_user_mappings um;
2535 GRANT SELECT ON user_mapping_options TO PUBLIC;
2540 * USER_MAPPINGS view
2542 CREATE VIEW user_mappings AS
2543 SELECT authorization_identifier,
2544 foreign_server_catalog,
2546 FROM _pg_user_mappings;
2548 GRANT SELECT ON user_mappings TO PUBLIC;