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 CREATE VIEW character_sets AS
358 SELECT CAST(null AS sql_identifier) AS character_set_catalog,
359 CAST(null AS sql_identifier) AS character_set_schema,
360 CAST(getdatabaseencoding() AS sql_identifier) AS character_set_name,
361 CAST(CASE WHEN getdatabaseencoding() = 'UTF8' THEN 'UCS' ELSE getdatabaseencoding() END AS sql_identifier) AS character_repertoire,
362 CAST(getdatabaseencoding() AS sql_identifier) AS form_of_use,
363 CAST(current_database() AS sql_identifier) AS default_collate_catalog,
364 CAST(nc.nspname AS sql_identifier) AS default_collate_schema,
365 CAST(c.collname AS sql_identifier) AS default_collate_name
367 LEFT JOIN (pg_collation c JOIN pg_namespace nc ON (c.collnamespace = nc.oid))
368 ON (datcollate = collcollate AND datctype = collctype)
369 WHERE d.datname = current_database()
370 ORDER BY char_length(c.collname) DESC, c.collname ASC -- prefer full/canonical name
373 GRANT SELECT ON character_sets TO PUBLIC;
378 * CHECK_CONSTRAINT_ROUTINE_USAGE view
381 CREATE VIEW check_constraint_routine_usage AS
382 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
383 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
384 CAST(c.conname AS sql_identifier) AS constraint_name,
385 CAST(current_database() AS sql_identifier) AS specific_catalog,
386 CAST(np.nspname AS sql_identifier) AS specific_schema,
387 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name
388 FROM pg_namespace nc, pg_constraint c, pg_depend d, pg_proc p, pg_namespace np
389 WHERE nc.oid = c.connamespace
392 AND d.classid = 'pg_catalog.pg_constraint'::regclass
393 AND d.refobjid = p.oid
394 AND d.refclassid = 'pg_catalog.pg_proc'::regclass
395 AND p.pronamespace = np.oid
396 AND pg_has_role(p.proowner, 'USAGE');
398 GRANT SELECT ON check_constraint_routine_usage TO PUBLIC;
403 * CHECK_CONSTRAINTS view
406 CREATE VIEW check_constraints AS
407 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
408 CAST(rs.nspname AS sql_identifier) AS constraint_schema,
409 CAST(con.conname AS sql_identifier) AS constraint_name,
410 CAST(substring(pg_get_constraintdef(con.oid) from 7) AS character_data)
412 FROM pg_constraint con
413 LEFT OUTER JOIN pg_namespace rs ON (rs.oid = con.connamespace)
414 LEFT OUTER JOIN pg_class c ON (c.oid = con.conrelid)
415 LEFT OUTER JOIN pg_type t ON (t.oid = con.contypid)
416 WHERE pg_has_role(coalesce(c.relowner, t.typowner), 'USAGE')
417 AND con.contype = 'c'
420 -- not-null constraints
422 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
423 CAST(n.nspname AS sql_identifier) AS constraint_schema,
424 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
425 CAST(a.attname || ' IS NOT NULL' AS character_data)
427 FROM pg_namespace n, pg_class r, pg_attribute a
428 WHERE n.oid = r.relnamespace
429 AND r.oid = a.attrelid
431 AND NOT a.attisdropped
434 AND pg_has_role(r.relowner, 'USAGE');
436 GRANT SELECT ON check_constraints TO PUBLIC;
444 CREATE VIEW collations AS
445 SELECT CAST(current_database() AS sql_identifier) AS collation_catalog,
446 CAST(nc.nspname AS sql_identifier) AS collation_schema,
447 CAST(c.collname AS sql_identifier) AS collation_name,
448 CAST('NO PAD' AS character_data) AS pad_attribute
449 FROM pg_collation c, pg_namespace nc
450 WHERE c.collnamespace = nc.oid
451 AND collencoding IN (-1, (SELECT encoding FROM pg_database WHERE datname = current_database()));
453 GRANT SELECT ON collations TO PUBLIC;
458 * COLLATION_CHARACTER_SET_APPLICABILITY view
461 CREATE VIEW collation_character_set_applicability AS
462 SELECT CAST(current_database() AS sql_identifier) AS collation_catalog,
463 CAST(nc.nspname AS sql_identifier) AS collation_schema,
464 CAST(c.collname AS sql_identifier) AS collation_name,
465 CAST(null AS sql_identifier) AS character_set_catalog,
466 CAST(null AS sql_identifier) AS character_set_schema,
467 CAST(getdatabaseencoding() AS sql_identifier) AS character_set_name
468 FROM pg_collation c, pg_namespace nc
469 WHERE c.collnamespace = nc.oid
470 AND collencoding IN (-1, (SELECT encoding FROM pg_database WHERE datname = current_database()));
472 GRANT SELECT ON collation_character_set_applicability TO PUBLIC;
477 * COLUMN_COLUMN_USAGE view
480 -- feature not supported
485 * COLUMN_DOMAIN_USAGE view
488 CREATE VIEW column_domain_usage AS
489 SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
490 CAST(nt.nspname AS sql_identifier) AS domain_schema,
491 CAST(t.typname AS sql_identifier) AS domain_name,
492 CAST(current_database() AS sql_identifier) AS table_catalog,
493 CAST(nc.nspname AS sql_identifier) AS table_schema,
494 CAST(c.relname AS sql_identifier) AS table_name,
495 CAST(a.attname AS sql_identifier) AS column_name
497 FROM pg_type t, pg_namespace nt, pg_class c, pg_namespace nc,
500 WHERE t.typnamespace = nt.oid
501 AND c.relnamespace = nc.oid
502 AND a.attrelid = c.oid
503 AND a.atttypid = t.oid
505 AND c.relkind IN ('r', 'v', 'f')
507 AND NOT a.attisdropped
508 AND pg_has_role(t.typowner, 'USAGE');
510 GRANT SELECT ON column_domain_usage TO PUBLIC;
518 CREATE VIEW column_privileges AS
519 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
520 CAST(grantee.rolname AS sql_identifier) AS grantee,
521 CAST(current_database() AS sql_identifier) AS table_catalog,
522 CAST(nc.nspname AS sql_identifier) AS table_schema,
523 CAST(x.relname AS sql_identifier) AS table_name,
524 CAST(x.attname AS sql_identifier) AS column_name,
525 CAST(x.prtype AS character_data) AS privilege_type,
528 -- object owner always has grant options
529 pg_has_role(x.grantee, x.relowner, 'USAGE')
531 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
542 FROM (SELECT oid, relname, relnamespace, relowner, (aclexplode(relacl)).*
544 WHERE relkind IN ('r', 'v', 'f')
545 ) pr_c (oid, relname, relnamespace, relowner, grantor, grantee, prtype, grantable),
547 WHERE a.attrelid = pr_c.oid
549 AND NOT a.attisdropped
559 FROM (SELECT attrelid, attname, (aclexplode(attacl)).*
563 ) pr_a (attrelid, attname, grantor, grantee, prtype, grantable),
565 WHERE pr_a.attrelid = c.oid
566 AND relkind IN ('r', 'v', 'f')
571 SELECT oid, rolname FROM pg_authid
573 SELECT 0::oid, 'PUBLIC'
574 ) AS grantee (oid, rolname)
576 WHERE x.relnamespace = nc.oid
577 AND x.grantee = grantee.oid
578 AND x.grantor = u_grantor.oid
579 AND x.prtype IN ('INSERT', 'SELECT', 'UPDATE', 'REFERENCES')
580 AND (pg_has_role(u_grantor.oid, 'USAGE')
581 OR pg_has_role(grantee.oid, 'USAGE')
582 OR grantee.rolname = 'PUBLIC');
584 GRANT SELECT ON column_privileges TO PUBLIC;
589 * COLUMN_UDT_USAGE view
592 CREATE VIEW column_udt_usage AS
593 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
594 CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,
595 CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,
596 CAST(current_database() AS sql_identifier) AS table_catalog,
597 CAST(nc.nspname AS sql_identifier) AS table_schema,
598 CAST(c.relname AS sql_identifier) AS table_name,
599 CAST(a.attname AS sql_identifier) AS column_name
601 FROM pg_attribute a, pg_class c, pg_namespace nc,
602 (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
603 LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
604 ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
606 WHERE a.attrelid = c.oid
607 AND a.atttypid = t.oid
608 AND nc.oid = c.relnamespace
609 AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v', 'f')
610 AND pg_has_role(coalesce(bt.typowner, t.typowner), 'USAGE');
612 GRANT SELECT ON column_udt_usage TO PUBLIC;
620 CREATE VIEW columns AS
621 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
622 CAST(nc.nspname AS sql_identifier) AS table_schema,
623 CAST(c.relname AS sql_identifier) AS table_name,
624 CAST(a.attname AS sql_identifier) AS column_name,
625 CAST(a.attnum AS cardinal_number) AS ordinal_position,
626 CAST(pg_get_expr(ad.adbin, ad.adrelid) AS character_data) AS column_default,
627 CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
632 CASE WHEN t.typtype = 'd' THEN
633 CASE WHEN bt.typelem <> 0 AND bt.typlen = -1 THEN 'ARRAY'
634 WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
635 ELSE 'USER-DEFINED' END
637 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
638 WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, null)
639 ELSE 'USER-DEFINED' END
645 _pg_char_max_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
647 AS character_maximum_length,
650 _pg_char_octet_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
652 AS character_octet_length,
655 _pg_numeric_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
657 AS numeric_precision,
660 _pg_numeric_precision_radix(_pg_truetypid(a, t), _pg_truetypmod(a, t))
662 AS numeric_precision_radix,
665 _pg_numeric_scale(_pg_truetypid(a, t), _pg_truetypmod(a, t))
670 _pg_datetime_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
672 AS datetime_precision,
674 CAST(null AS character_data) AS interval_type, -- FIXME
675 CAST(null AS character_data) AS interval_precision, -- FIXME
677 CAST(null AS sql_identifier) AS character_set_catalog,
678 CAST(null AS sql_identifier) AS character_set_schema,
679 CAST(null AS sql_identifier) AS character_set_name,
681 CAST(null AS sql_identifier) AS collation_catalog,
682 CAST(null AS sql_identifier) AS collation_schema,
683 CAST(null AS sql_identifier) AS collation_name,
685 CAST(CASE WHEN t.typtype = 'd' THEN current_database() ELSE null END
686 AS sql_identifier) AS domain_catalog,
687 CAST(CASE WHEN t.typtype = 'd' THEN nt.nspname ELSE null END
688 AS sql_identifier) AS domain_schema,
689 CAST(CASE WHEN t.typtype = 'd' THEN t.typname ELSE null END
690 AS sql_identifier) AS domain_name,
692 CAST(current_database() AS sql_identifier) AS udt_catalog,
693 CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,
694 CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,
696 CAST(null AS sql_identifier) AS scope_catalog,
697 CAST(null AS sql_identifier) AS scope_schema,
698 CAST(null AS sql_identifier) AS scope_name,
700 CAST(null AS cardinal_number) AS maximum_cardinality,
701 CAST(a.attnum AS sql_identifier) AS dtd_identifier,
702 CAST('NO' AS yes_or_no) AS is_self_referencing,
704 CAST('NO' AS yes_or_no) AS is_identity,
705 CAST(null AS character_data) AS identity_generation,
706 CAST(null AS character_data) AS identity_start,
707 CAST(null AS character_data) AS identity_increment,
708 CAST(null AS character_data) AS identity_maximum,
709 CAST(null AS character_data) AS identity_minimum,
710 CAST(null AS yes_or_no) AS identity_cycle,
712 CAST('NEVER' AS character_data) AS is_generated,
713 CAST(null AS character_data) AS generation_expression,
715 CAST(CASE WHEN c.relkind = 'r'
717 AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '2' AND is_instead)
718 AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '4' AND is_instead))
719 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_updatable
721 FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum),
722 pg_class c, pg_namespace nc,
723 (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
724 LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
725 ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
727 WHERE a.attrelid = c.oid
728 AND a.atttypid = t.oid
729 AND nc.oid = c.relnamespace
730 AND (NOT pg_is_other_temp_schema(nc.oid))
732 AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v', 'f')
734 AND (pg_has_role(c.relowner, 'USAGE')
735 OR has_column_privilege(c.oid, a.attnum,
736 'SELECT, INSERT, UPDATE, REFERENCES'));
738 GRANT SELECT ON columns TO PUBLIC;
743 * CONSTRAINT_COLUMN_USAGE view
746 CREATE VIEW constraint_column_usage AS
747 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
748 CAST(tblschema AS sql_identifier) AS table_schema,
749 CAST(tblname AS sql_identifier) AS table_name,
750 CAST(colname AS sql_identifier) AS column_name,
751 CAST(current_database() AS sql_identifier) AS constraint_catalog,
752 CAST(cstrschema AS sql_identifier) AS constraint_schema,
753 CAST(cstrname AS sql_identifier) AS constraint_name
756 /* check constraints */
757 SELECT DISTINCT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
758 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_depend d, pg_namespace nc, pg_constraint c
759 WHERE nr.oid = r.relnamespace
760 AND r.oid = a.attrelid
761 AND d.refclassid = 'pg_catalog.pg_class'::regclass
762 AND d.refobjid = r.oid
763 AND d.refobjsubid = a.attnum
764 AND d.classid = 'pg_catalog.pg_constraint'::regclass
766 AND c.connamespace = nc.oid
769 AND NOT a.attisdropped
773 /* unique/primary key/foreign key constraints */
774 SELECT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
775 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc,
777 WHERE nr.oid = r.relnamespace
778 AND r.oid = a.attrelid
779 AND nc.oid = c.connamespace
780 AND (CASE WHEN c.contype = 'f' THEN r.oid = c.confrelid AND a.attnum = ANY (c.confkey)
781 ELSE r.oid = c.conrelid AND a.attnum = ANY (c.conkey) END)
782 AND NOT a.attisdropped
783 AND c.contype IN ('p', 'u', 'f')
786 ) AS x (tblschema, tblname, tblowner, colname, cstrschema, cstrname)
788 WHERE pg_has_role(x.tblowner, 'USAGE');
790 GRANT SELECT ON constraint_column_usage TO PUBLIC;
795 * CONSTRAINT_TABLE_USAGE view
798 CREATE VIEW constraint_table_usage AS
799 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
800 CAST(nr.nspname AS sql_identifier) AS table_schema,
801 CAST(r.relname AS sql_identifier) AS table_name,
802 CAST(current_database() AS sql_identifier) AS constraint_catalog,
803 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
804 CAST(c.conname AS sql_identifier) AS constraint_name
806 FROM pg_constraint c, pg_namespace nc,
807 pg_class r, pg_namespace nr
809 WHERE c.connamespace = nc.oid AND r.relnamespace = nr.oid
810 AND ( (c.contype = 'f' AND c.confrelid = r.oid)
811 OR (c.contype IN ('p', 'u') AND c.conrelid = r.oid) )
813 AND pg_has_role(r.relowner, 'USAGE');
815 GRANT SELECT ON constraint_table_usage TO PUBLIC;
818 -- 5.24 DATA_TYPE_PRIVILEGES view appears later.
823 * DIRECT_SUPERTABLES view
826 -- feature not supported
831 * DIRECT_SUPERTYPES view
834 -- feature not supported
839 * DOMAIN_CONSTRAINTS view
842 CREATE VIEW domain_constraints AS
843 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
844 CAST(rs.nspname AS sql_identifier) AS constraint_schema,
845 CAST(con.conname AS sql_identifier) AS constraint_name,
846 CAST(current_database() AS sql_identifier) AS domain_catalog,
847 CAST(n.nspname AS sql_identifier) AS domain_schema,
848 CAST(t.typname AS sql_identifier) AS domain_name,
849 CAST(CASE WHEN condeferrable THEN 'YES' ELSE 'NO' END
850 AS yes_or_no) AS is_deferrable,
851 CAST(CASE WHEN condeferred THEN 'YES' ELSE 'NO' END
852 AS yes_or_no) AS initially_deferred
853 FROM pg_namespace rs, pg_namespace n, pg_constraint con, pg_type t
854 WHERE rs.oid = con.connamespace
855 AND n.oid = t.typnamespace
856 AND t.oid = con.contypid;
858 GRANT SELECT ON domain_constraints TO PUBLIC;
862 * DOMAIN_UDT_USAGE view
863 * apparently removed in SQL:2003
866 CREATE VIEW domain_udt_usage AS
867 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
868 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
869 CAST(bt.typname AS sql_identifier) AS udt_name,
870 CAST(current_database() AS sql_identifier) AS domain_catalog,
871 CAST(nt.nspname AS sql_identifier) AS domain_schema,
872 CAST(t.typname AS sql_identifier) AS domain_name
874 FROM pg_type t, pg_namespace nt,
875 pg_type bt, pg_namespace nbt
877 WHERE t.typnamespace = nt.oid
878 AND t.typbasetype = bt.oid
879 AND bt.typnamespace = nbt.oid
881 AND pg_has_role(bt.typowner, 'USAGE');
883 GRANT SELECT ON domain_udt_usage TO PUBLIC;
891 CREATE VIEW domains AS
892 SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
893 CAST(nt.nspname AS sql_identifier) AS domain_schema,
894 CAST(t.typname AS sql_identifier) AS domain_name,
897 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
898 WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
899 ELSE 'USER-DEFINED' END
904 _pg_char_max_length(t.typbasetype, t.typtypmod)
906 AS character_maximum_length,
909 _pg_char_octet_length(t.typbasetype, t.typtypmod)
911 AS character_octet_length,
913 CAST(null AS sql_identifier) AS character_set_catalog,
914 CAST(null AS sql_identifier) AS character_set_schema,
915 CAST(null AS sql_identifier) AS character_set_name,
917 CAST(null AS sql_identifier) AS collation_catalog,
918 CAST(null AS sql_identifier) AS collation_schema,
919 CAST(null AS sql_identifier) AS collation_name,
922 _pg_numeric_precision(t.typbasetype, t.typtypmod)
924 AS numeric_precision,
927 _pg_numeric_precision_radix(t.typbasetype, t.typtypmod)
929 AS numeric_precision_radix,
932 _pg_numeric_scale(t.typbasetype, t.typtypmod)
937 _pg_datetime_precision(t.typbasetype, t.typtypmod)
939 AS datetime_precision,
941 CAST(null AS character_data) AS interval_type, -- FIXME
942 CAST(null AS character_data) AS interval_precision, -- FIXME
944 CAST(t.typdefault AS character_data) AS domain_default,
946 CAST(current_database() AS sql_identifier) AS udt_catalog,
947 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
948 CAST(bt.typname AS sql_identifier) AS udt_name,
950 CAST(null AS sql_identifier) AS scope_catalog,
951 CAST(null AS sql_identifier) AS scope_schema,
952 CAST(null AS sql_identifier) AS scope_name,
954 CAST(null AS cardinal_number) AS maximum_cardinality,
955 CAST(1 AS sql_identifier) AS dtd_identifier
957 FROM pg_type t, pg_namespace nt,
958 pg_type bt, pg_namespace nbt
960 WHERE t.typnamespace = nt.oid
961 AND t.typbasetype = bt.oid
962 AND bt.typnamespace = nbt.oid
965 GRANT SELECT ON domains TO PUBLIC;
968 -- 5.29 ELEMENT_TYPES view appears later.
976 CREATE VIEW enabled_roles AS
977 SELECT CAST(a.rolname AS sql_identifier) AS role_name
979 WHERE pg_has_role(a.oid, 'USAGE');
981 GRANT SELECT ON enabled_roles TO PUBLIC;
989 -- feature not supported
994 * KEY_COLUMN_USAGE view
997 CREATE VIEW key_column_usage AS
998 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
999 CAST(nc_nspname AS sql_identifier) AS constraint_schema,
1000 CAST(conname AS sql_identifier) AS constraint_name,
1001 CAST(current_database() AS sql_identifier) AS table_catalog,
1002 CAST(nr_nspname AS sql_identifier) AS table_schema,
1003 CAST(relname AS sql_identifier) AS table_name,
1004 CAST(a.attname AS sql_identifier) AS column_name,
1005 CAST((ss.x).n AS cardinal_number) AS ordinal_position,
1006 CAST(CASE WHEN contype = 'f' THEN
1007 _pg_index_position(ss.conindid, ss.confkey[(ss.x).n])
1009 END AS cardinal_number)
1010 AS position_in_unique_constraint
1011 FROM pg_attribute a,
1012 (SELECT r.oid AS roid, r.relname, r.relowner,
1013 nc.nspname AS nc_nspname, nr.nspname AS nr_nspname,
1014 c.oid AS coid, c.conname, c.contype, c.conindid,
1015 c.confkey, c.confrelid,
1016 _pg_expandarray(c.conkey) AS x
1017 FROM pg_namespace nr, pg_class r, pg_namespace nc,
1019 WHERE nr.oid = r.relnamespace
1020 AND r.oid = c.conrelid
1021 AND nc.oid = c.connamespace
1022 AND c.contype IN ('p', 'u', 'f')
1024 AND (NOT pg_is_other_temp_schema(nr.oid)) ) AS ss
1025 WHERE ss.roid = a.attrelid
1026 AND a.attnum = (ss.x).x
1027 AND NOT a.attisdropped
1028 AND (pg_has_role(relowner, 'USAGE')
1029 OR has_column_privilege(roid, a.attnum,
1030 'SELECT, INSERT, UPDATE, REFERENCES'));
1032 GRANT SELECT ON key_column_usage TO PUBLIC;
1037 * METHOD_SPECIFICATION_PARAMETERS view
1040 -- feature not supported
1045 * METHOD_SPECIFICATIONS view
1048 -- feature not supported
1056 CREATE VIEW parameters AS
1057 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
1058 CAST(n_nspname AS sql_identifier) AS specific_schema,
1059 CAST(proname || '_' || CAST(p_oid AS text) AS sql_identifier) AS specific_name,
1060 CAST((ss.x).n AS cardinal_number) AS ordinal_position,
1062 CASE WHEN proargmodes IS NULL THEN 'IN'
1063 WHEN proargmodes[(ss.x).n] = 'i' THEN 'IN'
1064 WHEN proargmodes[(ss.x).n] = 'o' THEN 'OUT'
1065 WHEN proargmodes[(ss.x).n] = 'b' THEN 'INOUT'
1066 WHEN proargmodes[(ss.x).n] = 'v' THEN 'IN'
1067 WHEN proargmodes[(ss.x).n] = 't' THEN 'OUT'
1068 END AS character_data) AS parameter_mode,
1069 CAST('NO' AS yes_or_no) AS is_result,
1070 CAST('NO' AS yes_or_no) AS as_locator,
1071 CAST(NULLIF(proargnames[(ss.x).n], '') AS sql_identifier) AS parameter_name,
1073 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
1074 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
1075 ELSE 'USER-DEFINED' END AS character_data)
1077 CAST(null AS cardinal_number) AS character_maximum_length,
1078 CAST(null AS cardinal_number) AS character_octet_length,
1079 CAST(null AS sql_identifier) AS character_set_catalog,
1080 CAST(null AS sql_identifier) AS character_set_schema,
1081 CAST(null AS sql_identifier) AS character_set_name,
1082 CAST(null AS sql_identifier) AS collation_catalog,
1083 CAST(null AS sql_identifier) AS collation_schema,
1084 CAST(null AS sql_identifier) AS collation_name,
1085 CAST(null AS cardinal_number) AS numeric_precision,
1086 CAST(null AS cardinal_number) AS numeric_precision_radix,
1087 CAST(null AS cardinal_number) AS numeric_scale,
1088 CAST(null AS cardinal_number) AS datetime_precision,
1089 CAST(null AS character_data) AS interval_type,
1090 CAST(null AS character_data) AS interval_precision,
1091 CAST(current_database() AS sql_identifier) AS udt_catalog,
1092 CAST(nt.nspname AS sql_identifier) AS udt_schema,
1093 CAST(t.typname AS sql_identifier) AS udt_name,
1094 CAST(null AS sql_identifier) AS scope_catalog,
1095 CAST(null AS sql_identifier) AS scope_schema,
1096 CAST(null AS sql_identifier) AS scope_name,
1097 CAST(null AS cardinal_number) AS maximum_cardinality,
1098 CAST((ss.x).n AS sql_identifier) AS dtd_identifier
1100 FROM pg_type t, pg_namespace nt,
1101 (SELECT n.nspname AS n_nspname, p.proname, p.oid AS p_oid,
1102 p.proargnames, p.proargmodes,
1103 _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
1104 FROM pg_namespace n, pg_proc p
1105 WHERE n.oid = p.pronamespace
1106 AND (pg_has_role(p.proowner, 'USAGE') OR
1107 has_function_privilege(p.oid, 'EXECUTE'))) AS ss
1108 WHERE t.oid = (ss.x).x AND t.typnamespace = nt.oid;
1110 GRANT SELECT ON parameters TO PUBLIC;
1115 * REFERENCED_TYPES view
1118 -- feature not supported
1123 * REFERENTIAL_CONSTRAINTS view
1126 CREATE VIEW referential_constraints AS
1127 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1128 CAST(ncon.nspname AS sql_identifier) AS constraint_schema,
1129 CAST(con.conname AS sql_identifier) AS constraint_name,
1131 CASE WHEN npkc.nspname IS NULL THEN NULL
1132 ELSE current_database() END
1133 AS sql_identifier) AS unique_constraint_catalog,
1134 CAST(npkc.nspname AS sql_identifier) AS unique_constraint_schema,
1135 CAST(pkc.conname AS sql_identifier) AS unique_constraint_name,
1138 CASE con.confmatchtype WHEN 'f' THEN 'FULL'
1139 WHEN 'p' THEN 'PARTIAL'
1140 WHEN 'u' THEN 'NONE' END
1141 AS character_data) AS match_option,
1144 CASE con.confupdtype WHEN 'c' THEN 'CASCADE'
1145 WHEN 'n' THEN 'SET NULL'
1146 WHEN 'd' THEN 'SET DEFAULT'
1147 WHEN 'r' THEN 'RESTRICT'
1148 WHEN 'a' THEN 'NO ACTION' END
1149 AS character_data) AS update_rule,
1152 CASE con.confdeltype WHEN 'c' THEN 'CASCADE'
1153 WHEN 'n' THEN 'SET NULL'
1154 WHEN 'd' THEN 'SET DEFAULT'
1155 WHEN 'r' THEN 'RESTRICT'
1156 WHEN 'a' THEN 'NO ACTION' END
1157 AS character_data) AS delete_rule
1159 FROM (pg_namespace ncon
1160 INNER JOIN pg_constraint con ON ncon.oid = con.connamespace
1161 INNER JOIN pg_class c ON con.conrelid = c.oid)
1164 INNER JOIN pg_namespace npkc ON pkc.connamespace = npkc.oid)
1165 ON con.confrelid = pkc.conrelid
1166 AND _pg_keysequal(con.confkey, pkc.conkey)
1168 WHERE c.relkind = 'r'
1169 AND con.contype = 'f'
1170 AND (pkc.contype IN ('p', 'u') OR pkc.contype IS NULL)
1171 AND (pg_has_role(c.relowner, 'USAGE')
1172 -- SELECT privilege omitted, per SQL standard
1173 OR has_table_privilege(c.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1174 OR has_any_column_privilege(c.oid, 'INSERT, UPDATE, REFERENCES') );
1176 GRANT SELECT ON referential_constraints TO PUBLIC;
1181 * ROLE_COLUMN_GRANTS view
1184 CREATE VIEW role_column_grants AS
1193 FROM column_privileges
1194 WHERE grantor IN (SELECT role_name FROM enabled_roles)
1195 OR grantee IN (SELECT role_name FROM enabled_roles);
1197 GRANT SELECT ON role_column_grants TO PUBLIC;
1200 -- 5.39 ROLE_ROUTINE_GRANTS view is based on 5.45 ROUTINE_PRIVILEGES and is defined there instead.
1203 -- 5.40 ROLE_TABLE_GRANTS view is based on 5.60 TABLE_PRIVILEGES and is defined there instead.
1208 * ROLE_TABLE_METHOD_GRANTS view
1211 -- feature not supported
1215 -- 5.42 ROLE_USAGE_GRANTS view is based on 5.71 USAGE_PRIVILEGES and is defined there instead.
1220 * ROLE_UDT_GRANTS view
1223 -- feature not supported
1228 * ROUTINE_COLUMN_USAGE view
1231 -- not tracked by PostgreSQL
1236 * ROUTINE_PRIVILEGES view
1239 CREATE VIEW routine_privileges AS
1240 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1241 CAST(grantee.rolname AS sql_identifier) AS grantee,
1242 CAST(current_database() AS sql_identifier) AS specific_catalog,
1243 CAST(n.nspname AS sql_identifier) AS specific_schema,
1244 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1245 CAST(current_database() AS sql_identifier) AS routine_catalog,
1246 CAST(n.nspname AS sql_identifier) AS routine_schema,
1247 CAST(p.proname AS sql_identifier) AS routine_name,
1248 CAST('EXECUTE' AS character_data) AS privilege_type,
1251 -- object owner always has grant options
1252 pg_has_role(grantee.oid, p.proowner, 'USAGE')
1254 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
1257 SELECT oid, proname, proowner, pronamespace, (aclexplode(proacl)).* FROM pg_proc
1258 ) p (oid, proname, proowner, pronamespace, grantor, grantee, prtype, grantable),
1260 pg_authid u_grantor,
1262 SELECT oid, rolname FROM pg_authid
1264 SELECT 0::oid, 'PUBLIC'
1265 ) AS grantee (oid, rolname)
1267 WHERE p.pronamespace = n.oid
1268 AND grantee.oid = p.grantee
1269 AND u_grantor.oid = p.grantor
1270 AND p.prtype IN ('EXECUTE')
1271 AND (pg_has_role(u_grantor.oid, 'USAGE')
1272 OR pg_has_role(grantee.oid, 'USAGE')
1273 OR grantee.rolname = 'PUBLIC');
1275 GRANT SELECT ON routine_privileges TO PUBLIC;
1280 * ROLE_ROUTINE_GRANTS view
1283 CREATE VIEW role_routine_grants AS
1294 FROM routine_privileges
1295 WHERE grantor IN (SELECT role_name FROM enabled_roles)
1296 OR grantee IN (SELECT role_name FROM enabled_roles);
1298 GRANT SELECT ON role_routine_grants TO PUBLIC;
1303 * ROUTINE_ROUTINE_USAGE view
1306 -- not tracked by PostgreSQL
1311 * ROUTINE_SEQUENCE_USAGE view
1314 -- not tracked by PostgreSQL
1319 * ROUTINE_TABLE_USAGE view
1322 -- not tracked by PostgreSQL
1330 CREATE VIEW routines AS
1331 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
1332 CAST(n.nspname AS sql_identifier) AS specific_schema,
1333 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1334 CAST(current_database() AS sql_identifier) AS routine_catalog,
1335 CAST(n.nspname AS sql_identifier) AS routine_schema,
1336 CAST(p.proname AS sql_identifier) AS routine_name,
1337 CAST('FUNCTION' AS character_data) AS routine_type,
1338 CAST(null AS sql_identifier) AS module_catalog,
1339 CAST(null AS sql_identifier) AS module_schema,
1340 CAST(null AS sql_identifier) AS module_name,
1341 CAST(null AS sql_identifier) AS udt_catalog,
1342 CAST(null AS sql_identifier) AS udt_schema,
1343 CAST(null AS sql_identifier) AS udt_name,
1346 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
1347 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
1348 ELSE 'USER-DEFINED' END AS character_data)
1350 CAST(null AS cardinal_number) AS character_maximum_length,
1351 CAST(null AS cardinal_number) AS character_octet_length,
1352 CAST(null AS sql_identifier) AS character_set_catalog,
1353 CAST(null AS sql_identifier) AS character_set_schema,
1354 CAST(null AS sql_identifier) AS character_set_name,
1355 CAST(null AS sql_identifier) AS collation_catalog,
1356 CAST(null AS sql_identifier) AS collation_schema,
1357 CAST(null AS sql_identifier) AS collation_name,
1358 CAST(null AS cardinal_number) AS numeric_precision,
1359 CAST(null AS cardinal_number) AS numeric_precision_radix,
1360 CAST(null AS cardinal_number) AS numeric_scale,
1361 CAST(null AS cardinal_number) AS datetime_precision,
1362 CAST(null AS character_data) AS interval_type,
1363 CAST(null AS character_data) AS interval_precision,
1364 CAST(current_database() AS sql_identifier) AS type_udt_catalog,
1365 CAST(nt.nspname AS sql_identifier) AS type_udt_schema,
1366 CAST(t.typname AS sql_identifier) AS type_udt_name,
1367 CAST(null AS sql_identifier) AS scope_catalog,
1368 CAST(null AS sql_identifier) AS scope_schema,
1369 CAST(null AS sql_identifier) AS scope_name,
1370 CAST(null AS cardinal_number) AS maximum_cardinality,
1371 CAST(0 AS sql_identifier) AS dtd_identifier,
1373 CAST(CASE WHEN l.lanname = 'sql' THEN 'SQL' ELSE 'EXTERNAL' END AS character_data)
1376 CASE WHEN pg_has_role(p.proowner, 'USAGE') THEN p.prosrc ELSE null END
1377 AS character_data) AS routine_definition,
1379 CASE WHEN l.lanname = 'c' THEN p.prosrc ELSE null END
1380 AS character_data) AS external_name,
1381 CAST(upper(l.lanname) AS character_data) AS external_language,
1383 CAST('GENERAL' AS character_data) AS parameter_style,
1384 CAST(CASE WHEN p.provolatile = 'i' THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_deterministic,
1385 CAST('MODIFIES' AS character_data) AS sql_data_access,
1386 CAST(CASE WHEN p.proisstrict THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_null_call,
1387 CAST(null AS character_data) AS sql_path,
1388 CAST('YES' AS yes_or_no) AS schema_level_routine,
1389 CAST(0 AS cardinal_number) AS max_dynamic_result_sets,
1390 CAST(null AS yes_or_no) AS is_user_defined_cast,
1391 CAST(null AS yes_or_no) AS is_implicitly_invocable,
1392 CAST(CASE WHEN p.prosecdef THEN 'DEFINER' ELSE 'INVOKER' END AS character_data) AS security_type,
1393 CAST(null AS sql_identifier) AS to_sql_specific_catalog,
1394 CAST(null AS sql_identifier) AS to_sql_specific_schema,
1395 CAST(null AS sql_identifier) AS to_sql_specific_name,
1396 CAST('NO' AS yes_or_no) AS as_locator,
1397 CAST(null AS time_stamp) AS created,
1398 CAST(null AS time_stamp) AS last_altered,
1399 CAST(null AS yes_or_no) AS new_savepoint_level,
1400 CAST('YES' AS yes_or_no) AS is_udt_dependent, -- FIXME?
1402 CAST(null AS character_data) AS result_cast_from_data_type,
1403 CAST(null AS yes_or_no) AS result_cast_as_locator,
1404 CAST(null AS cardinal_number) AS result_cast_char_max_length,
1405 CAST(null AS cardinal_number) AS result_cast_char_octet_length,
1406 CAST(null AS sql_identifier) AS result_cast_char_set_catalog,
1407 CAST(null AS sql_identifier) AS result_cast_char_set_schema,
1408 CAST(null AS sql_identifier) AS result_cast_character_set_name,
1409 CAST(null AS sql_identifier) AS result_cast_collation_catalog,
1410 CAST(null AS sql_identifier) AS result_cast_collation_schema,
1411 CAST(null AS sql_identifier) AS result_cast_collation_name,
1412 CAST(null AS cardinal_number) AS result_cast_numeric_precision,
1413 CAST(null AS cardinal_number) AS result_cast_numeric_precision_radix,
1414 CAST(null AS cardinal_number) AS result_cast_numeric_scale,
1415 CAST(null AS cardinal_number) AS result_cast_datetime_precision,
1416 CAST(null AS character_data) AS result_cast_interval_type,
1417 CAST(null AS character_data) AS result_cast_interval_precision,
1418 CAST(null AS sql_identifier) AS result_cast_type_udt_catalog,
1419 CAST(null AS sql_identifier) AS result_cast_type_udt_schema,
1420 CAST(null AS sql_identifier) AS result_cast_type_udt_name,
1421 CAST(null AS sql_identifier) AS result_cast_scope_catalog,
1422 CAST(null AS sql_identifier) AS result_cast_scope_schema,
1423 CAST(null AS sql_identifier) AS result_cast_scope_name,
1424 CAST(null AS cardinal_number) AS result_cast_maximum_cardinality,
1425 CAST(null AS sql_identifier) AS result_cast_dtd_identifier
1427 FROM pg_namespace n, pg_proc p, pg_language l,
1428 pg_type t, pg_namespace nt
1430 WHERE n.oid = p.pronamespace AND p.prolang = l.oid
1431 AND p.prorettype = t.oid AND t.typnamespace = nt.oid
1432 AND (pg_has_role(p.proowner, 'USAGE')
1433 OR has_function_privilege(p.oid, 'EXECUTE'));
1435 GRANT SELECT ON routines TO PUBLIC;
1443 CREATE VIEW schemata AS
1444 SELECT CAST(current_database() AS sql_identifier) AS catalog_name,
1445 CAST(n.nspname AS sql_identifier) AS schema_name,
1446 CAST(u.rolname AS sql_identifier) AS schema_owner,
1447 CAST(null AS sql_identifier) AS default_character_set_catalog,
1448 CAST(null AS sql_identifier) AS default_character_set_schema,
1449 CAST(null AS sql_identifier) AS default_character_set_name,
1450 CAST(null AS character_data) AS sql_path
1451 FROM pg_namespace n, pg_authid u
1452 WHERE n.nspowner = u.oid AND pg_has_role(n.nspowner, 'USAGE');
1454 GRANT SELECT ON schemata TO PUBLIC;
1462 CREATE VIEW sequences AS
1463 SELECT CAST(current_database() AS sql_identifier) AS sequence_catalog,
1464 CAST(nc.nspname AS sql_identifier) AS sequence_schema,
1465 CAST(c.relname AS sql_identifier) AS sequence_name,
1466 CAST('bigint' AS character_data) AS data_type,
1467 CAST(64 AS cardinal_number) AS numeric_precision,
1468 CAST(2 AS cardinal_number) AS numeric_precision_radix,
1469 CAST(0 AS cardinal_number) AS numeric_scale,
1470 -- XXX: The following could be improved if we had LATERAL.
1471 CAST((pg_sequence_parameters(c.oid)).start_value AS character_data) AS start_value,
1472 CAST((pg_sequence_parameters(c.oid)).minimum_value AS character_data) AS minimum_value,
1473 CAST((pg_sequence_parameters(c.oid)).maximum_value AS character_data) AS maximum_value,
1474 CAST((pg_sequence_parameters(c.oid)).increment AS character_data) AS increment,
1475 CAST(CASE WHEN (pg_sequence_parameters(c.oid)).cycle_option THEN 'YES' ELSE 'NO' END AS yes_or_no) AS cycle_option
1476 FROM pg_namespace nc, pg_class c
1477 WHERE c.relnamespace = nc.oid
1479 AND (NOT pg_is_other_temp_schema(nc.oid))
1480 AND (pg_has_role(c.relowner, 'USAGE')
1481 OR has_sequence_privilege(c.oid, 'SELECT, UPDATE, USAGE') );
1483 GRANT SELECT ON sequences TO PUBLIC;
1488 * SQL_FEATURES table
1491 CREATE TABLE sql_features (
1492 feature_id character_data,
1493 feature_name character_data,
1494 sub_feature_id character_data,
1495 sub_feature_name character_data,
1496 is_supported yes_or_no,
1497 is_verified_by character_data,
1498 comments character_data
1501 -- Will be filled with external data by initdb.
1503 GRANT SELECT ON sql_features TO PUBLIC;
1508 * SQL_IMPLEMENTATION_INFO table
1511 -- Note: Implementation information items are defined in ISO/IEC 9075-3:2008,
1514 CREATE TABLE sql_implementation_info (
1515 implementation_info_id character_data,
1516 implementation_info_name character_data,
1517 integer_value cardinal_number,
1518 character_value character_data,
1519 comments character_data
1522 INSERT INTO sql_implementation_info VALUES ('10003', 'CATALOG NAME', NULL, 'Y', NULL);
1523 INSERT INTO sql_implementation_info VALUES ('10004', 'COLLATING SEQUENCE', NULL, '', 'not supported');
1524 INSERT INTO sql_implementation_info VALUES ('23', 'CURSOR COMMIT BEHAVIOR', 1, NULL, 'close cursors and retain prepared statements');
1525 INSERT INTO sql_implementation_info VALUES ('2', 'DATA SOURCE NAME', NULL, '', NULL);
1526 INSERT INTO sql_implementation_info VALUES ('17', 'DBMS NAME', NULL, (select trim(trailing ' ' from substring(version() from '^[^0-9]*'))), NULL);
1527 INSERT INTO sql_implementation_info VALUES ('18', 'DBMS VERSION', NULL, '???', NULL); -- filled by initdb
1528 INSERT INTO sql_implementation_info VALUES ('26', 'DEFAULT TRANSACTION ISOLATION', 2, NULL, 'READ COMMITTED; user-settable');
1529 INSERT INTO sql_implementation_info VALUES ('28', 'IDENTIFIER CASE', 3, NULL, 'stored in mixed case - case sensitive');
1530 INSERT INTO sql_implementation_info VALUES ('85', 'NULL COLLATION', 0, NULL, 'nulls higher than non-nulls');
1531 INSERT INTO sql_implementation_info VALUES ('13', 'SERVER NAME', NULL, '', NULL);
1532 INSERT INTO sql_implementation_info VALUES ('94', 'SPECIAL CHARACTERS', NULL, '', 'all non-ASCII characters allowed');
1533 INSERT INTO sql_implementation_info VALUES ('46', 'TRANSACTION CAPABLE', 2, NULL, 'both DML and DDL');
1535 GRANT SELECT ON sql_implementation_info TO PUBLIC;
1539 * SQL_LANGUAGES table
1540 * apparently removed in SQL:2008
1543 CREATE TABLE sql_languages (
1544 sql_language_source character_data,
1545 sql_language_year character_data,
1546 sql_language_conformance character_data,
1547 sql_language_integrity character_data,
1548 sql_language_implementation character_data,
1549 sql_language_binding_style character_data,
1550 sql_language_programming_language character_data
1553 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'DIRECT', NULL);
1554 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
1555 INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'DIRECT', NULL);
1556 INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
1558 GRANT SELECT ON sql_languages TO PUBLIC;
1563 * SQL_PACKAGES table
1566 CREATE TABLE sql_packages (
1567 feature_id character_data,
1568 feature_name character_data,
1569 is_supported yes_or_no,
1570 is_verified_by character_data,
1571 comments character_data
1574 INSERT INTO sql_packages VALUES ('PKG000', 'Core', 'NO', NULL, '');
1575 INSERT INTO sql_packages VALUES ('PKG001', 'Enhanced datetime facilities', 'YES', NULL, '');
1576 INSERT INTO sql_packages VALUES ('PKG002', 'Enhanced integrity management', 'NO', NULL, '');
1577 INSERT INTO sql_packages VALUES ('PKG003', 'OLAP facilities', 'NO', NULL, '');
1578 INSERT INTO sql_packages VALUES ('PKG004', 'PSM', 'NO', NULL, 'PL/pgSQL is similar.');
1579 INSERT INTO sql_packages VALUES ('PKG005', 'CLI', 'NO', NULL, 'ODBC is similar.');
1580 INSERT INTO sql_packages VALUES ('PKG006', 'Basic object support', 'NO', NULL, '');
1581 INSERT INTO sql_packages VALUES ('PKG007', 'Enhanced object support', 'NO', NULL, '');
1582 INSERT INTO sql_packages VALUES ('PKG008', 'Active database', 'NO', NULL, '');
1583 INSERT INTO sql_packages VALUES ('PKG010', 'OLAP', 'NO', NULL, 'NO');
1585 GRANT SELECT ON sql_packages TO PUBLIC;
1593 CREATE TABLE sql_parts (
1594 feature_id character_data,
1595 feature_name character_data,
1596 is_supported yes_or_no,
1597 is_verified_by character_data,
1598 comments character_data
1601 INSERT INTO sql_parts VALUES ('1', 'Framework (SQL/Framework)', 'NO', NULL, '');
1602 INSERT INTO sql_parts VALUES ('2', 'Foundation (SQL/Foundation)', 'NO', NULL, '');
1603 INSERT INTO sql_parts VALUES ('3', 'Call-Level Interface (SQL/CLI)', 'NO', NULL, '');
1604 INSERT INTO sql_parts VALUES ('4', 'Persistent Stored Modules (SQL/PSM)', 'NO', NULL, '');
1605 INSERT INTO sql_parts VALUES ('9', 'Management of External Data (SQL/MED)', 'NO', NULL, '');
1606 INSERT INTO sql_parts VALUES ('10', 'Object Language Bindings (SQL/OLB)', 'NO', NULL, '');
1607 INSERT INTO sql_parts VALUES ('11', 'Information and Definition Schema (SQL/Schemata)', 'NO', NULL, '');
1608 INSERT INTO sql_parts VALUES ('13', 'Routines and Types Using the Java Programming Language (SQL/JRT)', 'NO', NULL, '');
1609 INSERT INTO sql_parts VALUES ('14', 'XML-Related Specifications (SQL/XML)', 'YES', NULL, '');
1617 -- Note: Sizing items are defined in ISO/IEC 9075-3:2008, clause 9.2.
1619 CREATE TABLE sql_sizing (
1620 sizing_id cardinal_number,
1621 sizing_name character_data,
1622 supported_value cardinal_number,
1623 comments character_data
1626 INSERT INTO sql_sizing VALUES (34, 'MAXIMUM CATALOG NAME LENGTH', 63, NULL);
1627 INSERT INTO sql_sizing VALUES (30, 'MAXIMUM COLUMN NAME LENGTH', 63, NULL);
1628 INSERT INTO sql_sizing VALUES (97, 'MAXIMUM COLUMNS IN GROUP BY', 0, NULL);
1629 INSERT INTO sql_sizing VALUES (99, 'MAXIMUM COLUMNS IN ORDER BY', 0, NULL);
1630 INSERT INTO sql_sizing VALUES (100, 'MAXIMUM COLUMNS IN SELECT', 1664, NULL); -- match MaxTupleAttributeNumber
1631 INSERT INTO sql_sizing VALUES (101, 'MAXIMUM COLUMNS IN TABLE', 1600, NULL); -- match MaxHeapAttributeNumber
1632 INSERT INTO sql_sizing VALUES (1, 'MAXIMUM CONCURRENT ACTIVITIES', 0, NULL);
1633 INSERT INTO sql_sizing VALUES (31, 'MAXIMUM CURSOR NAME LENGTH', 63, NULL);
1634 INSERT INTO sql_sizing VALUES (0, 'MAXIMUM DRIVER CONNECTIONS', NULL, NULL);
1635 INSERT INTO sql_sizing VALUES (10005, 'MAXIMUM IDENTIFIER LENGTH', 63, NULL);
1636 INSERT INTO sql_sizing VALUES (32, 'MAXIMUM SCHEMA NAME LENGTH', 63, NULL);
1637 INSERT INTO sql_sizing VALUES (20000, 'MAXIMUM STATEMENT OCTETS', 0, NULL);
1638 INSERT INTO sql_sizing VALUES (20001, 'MAXIMUM STATEMENT OCTETS DATA', 0, NULL);
1639 INSERT INTO sql_sizing VALUES (20002, 'MAXIMUM STATEMENT OCTETS SCHEMA', 0, NULL);
1640 INSERT INTO sql_sizing VALUES (35, 'MAXIMUM TABLE NAME LENGTH', 63, NULL);
1641 INSERT INTO sql_sizing VALUES (106, 'MAXIMUM TABLES IN SELECT', 0, NULL);
1642 INSERT INTO sql_sizing VALUES (107, 'MAXIMUM USER NAME LENGTH', 63, NULL);
1643 INSERT INTO sql_sizing VALUES (25000, 'MAXIMUM CURRENT DEFAULT TRANSFORM GROUP LENGTH', NULL, NULL);
1644 INSERT INTO sql_sizing VALUES (25001, 'MAXIMUM CURRENT TRANSFORM GROUP LENGTH', NULL, NULL);
1645 INSERT INTO sql_sizing VALUES (25002, 'MAXIMUM CURRENT PATH LENGTH', 0, NULL);
1646 INSERT INTO sql_sizing VALUES (25003, 'MAXIMUM CURRENT ROLE LENGTH', NULL, NULL);
1647 INSERT INTO sql_sizing VALUES (25004, 'MAXIMUM SESSION USER LENGTH', 63, NULL);
1648 INSERT INTO sql_sizing VALUES (25005, 'MAXIMUM SYSTEM USER LENGTH', 63, NULL);
1651 SET supported_value = (SELECT typlen-1 FROM pg_catalog.pg_type WHERE typname = 'name'),
1652 comments = 'Might be less, depending on character set.'
1653 WHERE supported_value = 63;
1655 GRANT SELECT ON sql_sizing TO PUBLIC;
1660 * SQL_SIZING_PROFILES table
1663 -- The data in this table are defined by various profiles of SQL.
1664 -- Since we don't have any information about such profiles, we provide
1667 CREATE TABLE sql_sizing_profiles (
1668 sizing_id cardinal_number,
1669 sizing_name character_data,
1670 profile_id character_data,
1671 required_value cardinal_number,
1672 comments character_data
1675 GRANT SELECT ON sql_sizing_profiles TO PUBLIC;
1680 * TABLE_CONSTRAINTS view
1683 CREATE VIEW table_constraints AS
1684 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1685 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
1686 CAST(c.conname AS sql_identifier) AS constraint_name,
1687 CAST(current_database() AS sql_identifier) AS table_catalog,
1688 CAST(nr.nspname AS sql_identifier) AS table_schema,
1689 CAST(r.relname AS sql_identifier) AS table_name,
1691 CASE c.contype WHEN 'c' THEN 'CHECK'
1692 WHEN 'f' THEN 'FOREIGN KEY'
1693 WHEN 'p' THEN 'PRIMARY KEY'
1694 WHEN 'u' THEN 'UNIQUE' END
1695 AS character_data) AS constraint_type,
1696 CAST(CASE WHEN c.condeferrable THEN 'YES' ELSE 'NO' END AS yes_or_no)
1698 CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS yes_or_no)
1699 AS initially_deferred
1701 FROM pg_namespace nc,
1706 WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace
1707 AND c.conrelid = r.oid
1708 AND c.contype NOT IN ('t', 'x') -- ignore nonstandard constraints
1710 AND (NOT pg_is_other_temp_schema(nr.oid))
1711 AND (pg_has_role(r.relowner, 'USAGE')
1712 -- SELECT privilege omitted, per SQL standard
1713 OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1714 OR has_any_column_privilege(r.oid, 'INSERT, UPDATE, REFERENCES') )
1718 -- not-null constraints
1720 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1721 CAST(nr.nspname AS sql_identifier) AS constraint_schema,
1722 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
1723 CAST(current_database() AS sql_identifier) AS table_catalog,
1724 CAST(nr.nspname AS sql_identifier) AS table_schema,
1725 CAST(r.relname AS sql_identifier) AS table_name,
1726 CAST('CHECK' AS character_data) AS constraint_type,
1727 CAST('NO' AS yes_or_no) AS is_deferrable,
1728 CAST('NO' AS yes_or_no) AS initially_deferred
1730 FROM pg_namespace nr,
1734 WHERE nr.oid = r.relnamespace
1735 AND r.oid = a.attrelid
1738 AND NOT a.attisdropped
1740 AND (NOT pg_is_other_temp_schema(nr.oid))
1741 AND (pg_has_role(r.relowner, 'USAGE')
1742 -- SELECT privilege omitted, per SQL standard
1743 OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1744 OR has_any_column_privilege(r.oid, 'INSERT, UPDATE, REFERENCES') );
1746 GRANT SELECT ON table_constraints TO PUBLIC;
1751 * TABLE_METHOD_PRIVILEGES view
1754 -- feature not supported
1759 * TABLE_PRIVILEGES view
1762 CREATE VIEW table_privileges AS
1763 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1764 CAST(grantee.rolname AS sql_identifier) AS grantee,
1765 CAST(current_database() AS sql_identifier) AS table_catalog,
1766 CAST(nc.nspname AS sql_identifier) AS table_schema,
1767 CAST(c.relname AS sql_identifier) AS table_name,
1768 CAST(c.prtype AS character_data) AS privilege_type,
1771 -- object owner always has grant options
1772 pg_has_role(grantee.oid, c.relowner, 'USAGE')
1774 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable,
1775 CAST('NO' AS yes_or_no) AS with_hierarchy
1778 SELECT oid, relname, relnamespace, relkind, relowner, (aclexplode(relacl)).* FROM pg_class
1779 ) AS c (oid, relname, relnamespace, relkind, relowner, grantor, grantee, prtype, grantable),
1781 pg_authid u_grantor,
1783 SELECT oid, rolname FROM pg_authid
1785 SELECT 0::oid, 'PUBLIC'
1786 ) AS grantee (oid, rolname)
1788 WHERE c.relnamespace = nc.oid
1789 AND c.relkind IN ('r', 'v')
1790 AND c.grantee = grantee.oid
1791 AND c.grantor = u_grantor.oid
1792 AND c.prtype IN ('INSERT', 'SELECT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER')
1793 AND (pg_has_role(u_grantor.oid, 'USAGE')
1794 OR pg_has_role(grantee.oid, 'USAGE')
1795 OR grantee.rolname = 'PUBLIC');
1797 GRANT SELECT ON table_privileges TO PUBLIC;
1802 * ROLE_TABLE_GRANTS view
1805 CREATE VIEW role_table_grants AS
1814 FROM table_privileges
1815 WHERE grantor IN (SELECT role_name FROM enabled_roles)
1816 OR grantee IN (SELECT role_name FROM enabled_roles);
1818 GRANT SELECT ON role_table_grants TO PUBLIC;
1826 CREATE VIEW tables AS
1827 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
1828 CAST(nc.nspname AS sql_identifier) AS table_schema,
1829 CAST(c.relname AS sql_identifier) AS table_name,
1832 CASE WHEN nc.oid = pg_my_temp_schema() THEN 'LOCAL TEMPORARY'
1833 WHEN c.relkind = 'r' THEN 'BASE TABLE'
1834 WHEN c.relkind = 'v' THEN 'VIEW'
1835 WHEN c.relkind = 'f' THEN 'FOREIGN TABLE'
1837 AS character_data) AS table_type,
1839 CAST(null AS sql_identifier) AS self_referencing_column_name,
1840 CAST(null AS character_data) AS reference_generation,
1842 CAST(CASE WHEN t.typname IS NOT NULL THEN current_database() ELSE null END AS sql_identifier) AS user_defined_type_catalog,
1843 CAST(nt.nspname AS sql_identifier) AS user_defined_type_schema,
1844 CAST(t.typname AS sql_identifier) AS user_defined_type_name,
1846 CAST(CASE WHEN c.relkind = 'r'
1848 AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '3' AND is_instead))
1849 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_insertable_into,
1851 CAST(CASE WHEN t.typname IS NOT NULL THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_typed,
1853 CASE WHEN nc.oid = pg_my_temp_schema() THEN 'PRESERVE' -- FIXME
1855 AS character_data) AS commit_action
1857 FROM pg_namespace nc JOIN pg_class c ON (nc.oid = c.relnamespace)
1858 LEFT JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON (c.reloftype = t.oid)
1860 WHERE c.relkind IN ('r', 'v', 'f')
1861 AND (NOT pg_is_other_temp_schema(nc.oid))
1862 AND (pg_has_role(c.relowner, 'USAGE')
1863 OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1864 OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES') );
1866 GRANT SELECT ON tables TO PUBLIC;
1874 -- feature not supported
1882 -- feature not supported
1887 * TRIGGERED_UPDATE_COLUMNS view
1890 CREATE VIEW triggered_update_columns AS
1891 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1892 CAST(n.nspname AS sql_identifier) AS trigger_schema,
1893 CAST(t.tgname AS sql_identifier) AS trigger_name,
1894 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1895 CAST(n.nspname AS sql_identifier) AS event_object_schema,
1896 CAST(c.relname AS sql_identifier) AS event_object_table,
1897 CAST(a.attname AS sql_identifier) AS event_object_column
1899 FROM pg_namespace n, pg_class c, pg_trigger t,
1900 (SELECT tgoid, (ta0.tgat).x AS tgattnum, (ta0.tgat).n AS tgattpos
1901 FROM (SELECT oid AS tgoid, information_schema._pg_expandarray(tgattr) AS tgat FROM pg_trigger) AS ta0) AS ta,
1904 WHERE n.oid = c.relnamespace
1905 AND c.oid = t.tgrelid
1906 AND t.oid = ta.tgoid
1907 AND (a.attrelid, a.attnum) = (t.tgrelid, ta.tgattnum)
1908 AND NOT t.tgisinternal
1909 AND (NOT pg_is_other_temp_schema(n.oid))
1910 AND (pg_has_role(c.relowner, 'USAGE')
1911 -- SELECT privilege omitted, per SQL standard
1912 OR has_column_privilege(c.oid, a.attnum, 'INSERT, UPDATE, REFERENCES') );
1914 GRANT SELECT ON triggered_update_columns TO PUBLIC;
1919 * TRIGGER_COLUMN_USAGE view
1922 -- not tracked by PostgreSQL
1927 * TRIGGER_ROUTINE_USAGE view
1930 -- not tracked by PostgreSQL
1935 * TRIGGER_SEQUENCE_USAGE view
1938 -- not tracked by PostgreSQL
1943 * TRIGGER_TABLE_USAGE view
1946 -- not tracked by PostgreSQL
1954 CREATE VIEW triggers AS
1955 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1956 CAST(n.nspname AS sql_identifier) AS trigger_schema,
1957 CAST(t.tgname AS sql_identifier) AS trigger_name,
1958 CAST(em.text AS character_data) AS event_manipulation,
1959 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1960 CAST(n.nspname AS sql_identifier) AS event_object_schema,
1961 CAST(c.relname AS sql_identifier) AS event_object_table,
1962 CAST(null AS cardinal_number) AS action_order,
1963 -- XXX strange hacks follow
1965 CASE WHEN pg_has_role(c.relowner, 'USAGE')
1966 THEN (SELECT m[1] FROM regexp_matches(pg_get_triggerdef(t.oid), E'.{35,} WHEN \\((.+)\\) EXECUTE PROCEDURE') AS rm(m) LIMIT 1)
1968 AS character_data) AS action_condition,
1970 substring(pg_get_triggerdef(t.oid) from
1971 position('EXECUTE PROCEDURE' in substring(pg_get_triggerdef(t.oid) from 48)) + 47)
1972 AS character_data) AS action_statement,
1974 -- hard-wired reference to TRIGGER_TYPE_ROW
1975 CASE t.tgtype & 1 WHEN 1 THEN 'ROW' ELSE 'STATEMENT' END
1976 AS character_data) AS action_orientation,
1978 -- hard-wired refs to TRIGGER_TYPE_BEFORE, TRIGGER_TYPE_INSTEAD
1979 CASE t.tgtype & 66 WHEN 2 THEN 'BEFORE' WHEN 64 THEN 'INSTEAD OF' ELSE 'AFTER' END
1980 AS character_data) AS action_timing,
1981 CAST(null AS sql_identifier) AS action_reference_old_table,
1982 CAST(null AS sql_identifier) AS action_reference_new_table,
1983 CAST(null AS sql_identifier) AS action_reference_old_row,
1984 CAST(null AS sql_identifier) AS action_reference_new_row,
1985 CAST(null AS time_stamp) AS created
1987 FROM pg_namespace n, pg_class c, pg_trigger t,
1988 -- hard-wired refs to TRIGGER_TYPE_INSERT, TRIGGER_TYPE_DELETE,
1989 -- TRIGGER_TYPE_UPDATE; we intentionally omit TRIGGER_TYPE_TRUNCATE
1990 (VALUES (4, 'INSERT'),
1992 (16, 'UPDATE')) AS em (num, text)
1994 WHERE n.oid = c.relnamespace
1995 AND c.oid = t.tgrelid
1996 AND t.tgtype & em.num <> 0
1997 AND NOT t.tgisinternal
1998 AND (NOT pg_is_other_temp_schema(n.oid))
1999 AND (pg_has_role(c.relowner, 'USAGE')
2000 -- SELECT privilege omitted, per SQL standard
2001 OR has_table_privilege(c.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
2002 OR has_any_column_privilege(c.oid, 'INSERT, UPDATE, REFERENCES') );
2004 GRANT SELECT ON triggers TO PUBLIC;
2009 * UDT_PRIVILEGES view
2012 -- feature not supported
2017 * USAGE_PRIVILEGES view
2020 CREATE VIEW usage_privileges AS
2023 -- Collations have no real privileges, so we represent all collations with implicit usage privilege here.
2024 SELECT CAST(u.rolname AS sql_identifier) AS grantor,
2025 CAST('PUBLIC' AS sql_identifier) AS grantee,
2026 CAST(current_database() AS sql_identifier) AS object_catalog,
2027 CAST(n.nspname AS sql_identifier) AS object_schema,
2028 CAST(c.collname AS sql_identifier) AS object_name,
2029 CAST('COLLATION' AS character_data) AS object_type,
2030 CAST('USAGE' AS character_data) AS privilege_type,
2031 CAST('NO' AS yes_or_no) AS is_grantable
2037 WHERE u.oid = c.collowner
2038 AND c.collnamespace = n.oid
2039 AND collencoding IN (-1, (SELECT encoding FROM pg_database WHERE datname = current_database()))
2044 -- Domains have no real privileges, so we represent all domains with implicit usage privilege here.
2045 SELECT CAST(u.rolname AS sql_identifier) AS grantor,
2046 CAST('PUBLIC' AS sql_identifier) AS grantee,
2047 CAST(current_database() AS sql_identifier) AS object_catalog,
2048 CAST(n.nspname AS sql_identifier) AS object_schema,
2049 CAST(t.typname AS sql_identifier) AS object_name,
2050 CAST('DOMAIN' AS character_data) AS object_type,
2051 CAST('USAGE' AS character_data) AS privilege_type,
2052 CAST('NO' AS yes_or_no) AS is_grantable
2058 WHERE u.oid = t.typowner
2059 AND t.typnamespace = n.oid
2064 /* foreign-data wrappers */
2065 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2066 CAST(grantee.rolname AS sql_identifier) AS grantee,
2067 CAST(current_database() AS sql_identifier) AS object_catalog,
2068 CAST('' AS sql_identifier) AS object_schema,
2069 CAST(fdw.fdwname AS sql_identifier) AS object_name,
2070 CAST('FOREIGN DATA WRAPPER' AS character_data) AS object_type,
2071 CAST('USAGE' AS character_data) AS privilege_type,
2074 -- object owner always has grant options
2075 pg_has_role(grantee.oid, fdw.fdwowner, 'USAGE')
2077 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2080 SELECT fdwname, fdwowner, (aclexplode(fdwacl)).* FROM pg_foreign_data_wrapper
2081 ) AS fdw (fdwname, fdwowner, grantor, grantee, prtype, grantable),
2082 pg_authid u_grantor,
2084 SELECT oid, rolname FROM pg_authid
2086 SELECT 0::oid, 'PUBLIC'
2087 ) AS grantee (oid, rolname)
2089 WHERE u_grantor.oid = fdw.grantor
2090 AND grantee.oid = fdw.grantee
2091 AND fdw.prtype IN ('USAGE')
2092 AND (pg_has_role(u_grantor.oid, 'USAGE')
2093 OR pg_has_role(grantee.oid, 'USAGE')
2094 OR grantee.rolname = 'PUBLIC')
2098 /* foreign servers */
2099 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2100 CAST(grantee.rolname AS sql_identifier) AS grantee,
2101 CAST(current_database() AS sql_identifier) AS object_catalog,
2102 CAST('' AS sql_identifier) AS object_schema,
2103 CAST(srv.srvname AS sql_identifier) AS object_name,
2104 CAST('FOREIGN SERVER' AS character_data) AS object_type,
2105 CAST('USAGE' AS character_data) AS privilege_type,
2108 -- object owner always has grant options
2109 pg_has_role(grantee.oid, srv.srvowner, 'USAGE')
2111 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2114 SELECT srvname, srvowner, (aclexplode(srvacl)).* FROM pg_foreign_server
2115 ) AS srv (srvname, srvowner, grantor, grantee, prtype, grantable),
2116 pg_authid u_grantor,
2118 SELECT oid, rolname FROM pg_authid
2120 SELECT 0::oid, 'PUBLIC'
2121 ) AS grantee (oid, rolname)
2123 WHERE u_grantor.oid = srv.grantor
2124 AND grantee.oid = srv.grantee
2125 AND srv.prtype IN ('USAGE')
2126 AND (pg_has_role(u_grantor.oid, 'USAGE')
2127 OR pg_has_role(grantee.oid, 'USAGE')
2128 OR grantee.rolname = 'PUBLIC');
2130 GRANT SELECT ON usage_privileges TO PUBLIC;
2135 * ROLE_USAGE_GRANTS view
2138 CREATE VIEW role_usage_grants AS
2147 FROM usage_privileges
2148 WHERE grantor IN (SELECT role_name FROM enabled_roles)
2149 OR grantee IN (SELECT role_name FROM enabled_roles);
2151 GRANT SELECT ON role_usage_grants TO PUBLIC;
2156 * USER_DEFINED_TYPES view
2159 -- feature not supported
2167 CREATE VIEW view_column_usage AS
2169 CAST(current_database() AS sql_identifier) AS view_catalog,
2170 CAST(nv.nspname AS sql_identifier) AS view_schema,
2171 CAST(v.relname AS sql_identifier) AS view_name,
2172 CAST(current_database() AS sql_identifier) AS table_catalog,
2173 CAST(nt.nspname AS sql_identifier) AS table_schema,
2174 CAST(t.relname AS sql_identifier) AS table_name,
2175 CAST(a.attname AS sql_identifier) AS column_name
2177 FROM pg_namespace nv, pg_class v, pg_depend dv,
2178 pg_depend dt, pg_class t, pg_namespace nt,
2181 WHERE nv.oid = v.relnamespace
2183 AND v.oid = dv.refobjid
2184 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2185 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2186 AND dv.deptype = 'i'
2187 AND dv.objid = dt.objid
2188 AND dv.refobjid <> dt.refobjid
2189 AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2190 AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2191 AND dt.refobjid = t.oid
2192 AND t.relnamespace = nt.oid
2193 AND t.relkind IN ('r', 'v', 'f')
2194 AND t.oid = a.attrelid
2195 AND dt.refobjsubid = a.attnum
2196 AND pg_has_role(t.relowner, 'USAGE');
2198 GRANT SELECT ON view_column_usage TO PUBLIC;
2203 * VIEW_ROUTINE_USAGE
2206 CREATE VIEW view_routine_usage AS
2208 CAST(current_database() AS sql_identifier) AS table_catalog,
2209 CAST(nv.nspname AS sql_identifier) AS table_schema,
2210 CAST(v.relname AS sql_identifier) AS table_name,
2211 CAST(current_database() AS sql_identifier) AS specific_catalog,
2212 CAST(np.nspname AS sql_identifier) AS specific_schema,
2213 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name
2215 FROM pg_namespace nv, pg_class v, pg_depend dv,
2216 pg_depend dp, pg_proc p, pg_namespace np
2218 WHERE nv.oid = v.relnamespace
2220 AND v.oid = dv.refobjid
2221 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2222 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2223 AND dv.deptype = 'i'
2224 AND dv.objid = dp.objid
2225 AND dp.classid = 'pg_catalog.pg_rewrite'::regclass
2226 AND dp.refclassid = 'pg_catalog.pg_proc'::regclass
2227 AND dp.refobjid = p.oid
2228 AND p.pronamespace = np.oid
2229 AND pg_has_role(p.proowner, 'USAGE');
2231 GRANT SELECT ON view_routine_usage TO PUBLIC;
2239 CREATE VIEW view_table_usage AS
2241 CAST(current_database() AS sql_identifier) AS view_catalog,
2242 CAST(nv.nspname AS sql_identifier) AS view_schema,
2243 CAST(v.relname AS sql_identifier) AS view_name,
2244 CAST(current_database() AS sql_identifier) AS table_catalog,
2245 CAST(nt.nspname AS sql_identifier) AS table_schema,
2246 CAST(t.relname AS sql_identifier) AS table_name
2248 FROM pg_namespace nv, pg_class v, pg_depend dv,
2249 pg_depend dt, pg_class t, pg_namespace nt
2251 WHERE nv.oid = v.relnamespace
2253 AND v.oid = dv.refobjid
2254 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2255 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2256 AND dv.deptype = 'i'
2257 AND dv.objid = dt.objid
2258 AND dv.refobjid <> dt.refobjid
2259 AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2260 AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2261 AND dt.refobjid = t.oid
2262 AND t.relnamespace = nt.oid
2263 AND t.relkind IN ('r', 'v', 'f')
2264 AND pg_has_role(t.relowner, 'USAGE');
2266 GRANT SELECT ON view_table_usage TO PUBLIC;
2274 CREATE VIEW views AS
2275 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
2276 CAST(nc.nspname AS sql_identifier) AS table_schema,
2277 CAST(c.relname AS sql_identifier) AS table_name,
2280 CASE WHEN pg_has_role(c.relowner, 'USAGE')
2281 THEN pg_get_viewdef(c.oid)
2283 AS character_data) AS view_definition,
2285 CAST('NONE' AS character_data) AS check_option,
2288 CASE WHEN EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '2' AND is_instead)
2289 AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '4' AND is_instead)
2290 THEN 'YES' ELSE 'NO' END
2291 AS yes_or_no) AS is_updatable,
2294 CASE WHEN EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '3' AND is_instead)
2295 THEN 'YES' ELSE 'NO' END
2296 AS yes_or_no) AS is_insertable_into,
2299 -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_UPDATE
2300 CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 81 = 81)
2301 THEN 'YES' ELSE 'NO' END
2302 AS yes_or_no) AS is_trigger_updatable,
2305 -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_DELETE
2306 CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 73 = 73)
2307 THEN 'YES' ELSE 'NO' END
2308 AS yes_or_no) AS is_trigger_deletable,
2311 -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_INSERT
2312 CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 69 = 69)
2313 THEN 'YES' ELSE 'NO' END
2314 AS yes_or_no) AS is_trigger_insertable_into
2316 FROM pg_namespace nc, pg_class c
2318 WHERE c.relnamespace = nc.oid
2320 AND (NOT pg_is_other_temp_schema(nc.oid))
2321 AND (pg_has_role(c.relowner, 'USAGE')
2322 OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
2323 OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES') );
2325 GRANT SELECT ON views TO PUBLIC;
2328 -- The following views have dependencies that force them to appear out of order.
2332 * DATA_TYPE_PRIVILEGES view
2335 CREATE VIEW data_type_privileges AS
2336 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2337 CAST(x.objschema AS sql_identifier) AS object_schema,
2338 CAST(x.objname AS sql_identifier) AS object_name,
2339 CAST(x.objtype AS character_data) AS object_type,
2340 CAST(x.objdtdid AS sql_identifier) AS dtd_identifier
2344 SELECT udt_schema, udt_name, 'USER-DEFINED TYPE'::text, dtd_identifier FROM attributes
2346 SELECT table_schema, table_name, 'TABLE'::text, dtd_identifier FROM columns
2348 SELECT domain_schema, domain_name, 'DOMAIN'::text, dtd_identifier FROM domains
2350 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM parameters
2352 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM routines
2353 ) AS x (objschema, objname, objtype, objdtdid);
2355 GRANT SELECT ON data_type_privileges TO PUBLIC;
2360 * ELEMENT_TYPES view
2363 CREATE VIEW element_types AS
2364 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2365 CAST(n.nspname AS sql_identifier) AS object_schema,
2366 CAST(x.objname AS sql_identifier) AS object_name,
2367 CAST(x.objtype AS character_data) AS object_type,
2368 CAST(x.objdtdid AS sql_identifier) AS collection_type_identifier,
2370 CASE WHEN nbt.nspname = 'pg_catalog' THEN format_type(bt.oid, null)
2371 ELSE 'USER-DEFINED' END AS character_data) AS data_type,
2373 CAST(null AS cardinal_number) AS character_maximum_length,
2374 CAST(null AS cardinal_number) AS character_octet_length,
2375 CAST(null AS sql_identifier) AS character_set_catalog,
2376 CAST(null AS sql_identifier) AS character_set_schema,
2377 CAST(null AS sql_identifier) AS character_set_name,
2378 CAST(null AS sql_identifier) AS collation_catalog,
2379 CAST(null AS sql_identifier) AS collation_schema,
2380 CAST(null AS sql_identifier) AS collation_name,
2381 CAST(null AS cardinal_number) AS numeric_precision,
2382 CAST(null AS cardinal_number) AS numeric_precision_radix,
2383 CAST(null AS cardinal_number) AS numeric_scale,
2384 CAST(null AS cardinal_number) AS datetime_precision,
2385 CAST(null AS character_data) AS interval_type,
2386 CAST(null AS character_data) AS interval_precision,
2388 CAST(null AS character_data) AS domain_default, -- XXX maybe a bug in the standard
2390 CAST(current_database() AS sql_identifier) AS udt_catalog,
2391 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
2392 CAST(bt.typname AS sql_identifier) AS udt_name,
2394 CAST(null AS sql_identifier) AS scope_catalog,
2395 CAST(null AS sql_identifier) AS scope_schema,
2396 CAST(null AS sql_identifier) AS scope_name,
2398 CAST(null AS cardinal_number) AS maximum_cardinality,
2399 CAST('a' || CAST(x.objdtdid AS text) AS sql_identifier) AS dtd_identifier
2401 FROM pg_namespace n, pg_type at, pg_namespace nbt, pg_type bt,
2404 SELECT c.relnamespace, CAST(c.relname AS sql_identifier),
2405 'TABLE'::text, a.attnum, a.atttypid
2406 FROM pg_class c, pg_attribute a
2407 WHERE c.oid = a.attrelid
2408 AND c.relkind IN ('r', 'v', 'f')
2409 AND attnum > 0 AND NOT attisdropped
2414 SELECT t.typnamespace, CAST(t.typname AS sql_identifier),
2415 'DOMAIN'::text, 1, t.typbasetype
2417 WHERE t.typtype = 'd'
2422 SELECT pronamespace, CAST(proname || '_' || CAST(oid AS text) AS sql_identifier),
2423 'ROUTINE'::text, (ss.x).n, (ss.x).x
2424 FROM (SELECT p.pronamespace, p.proname, p.oid,
2425 _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
2426 FROM pg_proc p) AS ss
2431 SELECT p.pronamespace, CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier),
2432 'ROUTINE'::text, 0, p.prorettype
2435 ) AS x (objschema, objname, objtype, objdtdid, objtypeid)
2437 WHERE n.oid = x.objschema
2438 AND at.oid = x.objtypeid
2439 AND (at.typelem <> 0 AND at.typlen = -1)
2440 AND at.typelem = bt.oid
2441 AND nbt.oid = bt.typnamespace
2443 AND (n.nspname, x.objname, x.objtype, CAST(x.objdtdid AS sql_identifier)) IN
2444 ( SELECT object_schema, object_name, object_type, dtd_identifier
2445 FROM data_type_privileges );
2447 GRANT SELECT ON element_types TO PUBLIC;
2450 -- SQL/MED views; these use section numbers from part 9 of the standard.
2452 /* Base view for foreign-data wrappers */
2453 CREATE VIEW _pg_foreign_data_wrappers AS
2457 CAST(current_database() AS sql_identifier) AS foreign_data_wrapper_catalog,
2458 CAST(fdwname AS sql_identifier) AS foreign_data_wrapper_name,
2459 CAST(u.rolname AS sql_identifier) AS authorization_identifier,
2460 CAST('c' AS character_data) AS foreign_data_wrapper_language
2461 FROM pg_foreign_data_wrapper w, pg_authid u
2462 WHERE u.oid = w.fdwowner
2463 AND (pg_has_role(fdwowner, 'USAGE')
2464 OR has_foreign_data_wrapper_privilege(w.oid, 'USAGE'));
2469 * FOREIGN_DATA_WRAPPER_OPTIONS view
2471 CREATE VIEW foreign_data_wrapper_options AS
2472 SELECT foreign_data_wrapper_catalog,
2473 foreign_data_wrapper_name,
2474 CAST((pg_options_to_table(w.fdwoptions)).option_name AS sql_identifier) AS option_name,
2475 CAST((pg_options_to_table(w.fdwoptions)).option_value AS character_data) AS option_value
2476 FROM _pg_foreign_data_wrappers w;
2478 GRANT SELECT ON foreign_data_wrapper_options TO PUBLIC;
2483 * FOREIGN_DATA_WRAPPERS view
2485 CREATE VIEW foreign_data_wrappers AS
2486 SELECT foreign_data_wrapper_catalog,
2487 foreign_data_wrapper_name,
2488 authorization_identifier,
2489 CAST(NULL AS character_data) AS library_name,
2490 foreign_data_wrapper_language
2491 FROM _pg_foreign_data_wrappers w;
2493 GRANT SELECT ON foreign_data_wrappers TO PUBLIC;
2496 /* Base view for foreign servers */
2497 CREATE VIEW _pg_foreign_servers AS
2500 CAST(current_database() AS sql_identifier) AS foreign_server_catalog,
2501 CAST(srvname AS sql_identifier) AS foreign_server_name,
2502 CAST(current_database() AS sql_identifier) AS foreign_data_wrapper_catalog,
2503 CAST(w.fdwname AS sql_identifier) AS foreign_data_wrapper_name,
2504 CAST(srvtype AS character_data) AS foreign_server_type,
2505 CAST(srvversion AS character_data) AS foreign_server_version,
2506 CAST(u.rolname AS sql_identifier) AS authorization_identifier
2507 FROM pg_foreign_server s, pg_foreign_data_wrapper w, pg_authid u
2508 WHERE w.oid = s.srvfdw
2509 AND u.oid = s.srvowner
2510 AND (pg_has_role(s.srvowner, 'USAGE')
2511 OR has_server_privilege(s.oid, 'USAGE'));
2516 * FOREIGN_SERVER_OPTIONS view
2518 CREATE VIEW foreign_server_options AS
2519 SELECT foreign_server_catalog,
2520 foreign_server_name,
2521 CAST((pg_options_to_table(s.srvoptions)).option_name AS sql_identifier) AS option_name,
2522 CAST((pg_options_to_table(s.srvoptions)).option_value AS character_data) AS option_value
2523 FROM _pg_foreign_servers s;
2525 GRANT SELECT ON TABLE foreign_server_options TO PUBLIC;
2530 * FOREIGN_SERVERS view
2532 CREATE VIEW foreign_servers AS
2533 SELECT foreign_server_catalog,
2534 foreign_server_name,
2535 foreign_data_wrapper_catalog,
2536 foreign_data_wrapper_name,
2537 foreign_server_type,
2538 foreign_server_version,
2539 authorization_identifier
2540 FROM _pg_foreign_servers;
2542 GRANT SELECT ON foreign_servers TO PUBLIC;
2545 /* Base view for foreign tables */
2546 CREATE VIEW _pg_foreign_tables AS
2548 CAST(current_database() AS sql_identifier) AS foreign_table_catalog,
2549 n.nspname AS foreign_table_schema,
2550 c.relname AS foreign_table_name,
2551 t.ftoptions AS ftoptions,
2552 CAST(current_database() AS sql_identifier) AS foreign_server_catalog,
2553 CAST(srvname AS sql_identifier) AS foreign_server_name,
2554 CAST(u.rolname AS sql_identifier) AS authorization_identifier
2555 FROM pg_foreign_table t, pg_foreign_server s, pg_foreign_data_wrapper w,
2556 pg_authid u, pg_namespace n, pg_class c
2557 WHERE w.oid = s.srvfdw
2558 AND u.oid = c.relowner
2559 AND (pg_has_role(c.relowner, 'USAGE')
2560 OR has_table_privilege(c.oid, 'SELECT')
2561 OR has_any_column_privilege(c.oid, 'SELECT'))
2562 AND n.oid = c.relnamespace
2563 AND c.oid = t.ftrelid
2565 AND s.oid = t.ftserver;
2570 * FOREIGN_TABLE_OPTIONS view
2572 CREATE VIEW foreign_table_options AS
2573 SELECT foreign_table_catalog,
2574 foreign_table_schema,
2576 CAST((pg_options_to_table(t.ftoptions)).option_name AS sql_identifier) AS option_name,
2577 CAST((pg_options_to_table(t.ftoptions)).option_value AS character_data) AS option_value
2578 FROM _pg_foreign_tables t;
2580 GRANT SELECT ON TABLE foreign_table_options TO PUBLIC;
2585 * FOREIGN_TABLES view
2587 CREATE VIEW foreign_tables AS
2588 SELECT foreign_table_catalog,
2589 foreign_table_schema,
2591 foreign_server_catalog,
2593 FROM _pg_foreign_tables;
2595 GRANT SELECT ON foreign_tables TO PUBLIC;
2599 /* Base view for user mappings */
2600 CREATE VIEW _pg_user_mappings AS
2604 CAST(COALESCE(u.rolname,'PUBLIC') AS sql_identifier ) AS authorization_identifier,
2605 s.foreign_server_catalog,
2606 s.foreign_server_name,
2607 s.authorization_identifier AS srvowner
2608 FROM pg_user_mapping um LEFT JOIN pg_authid u ON (u.oid = um.umuser),
2609 _pg_foreign_servers s
2610 WHERE s.oid = um.umserver;
2615 * USER_MAPPING_OPTIONS view
2617 CREATE VIEW user_mapping_options AS
2618 SELECT authorization_identifier,
2619 foreign_server_catalog,
2620 foreign_server_name,
2621 CAST((pg_options_to_table(um.umoptions)).option_name AS sql_identifier) AS option_name,
2622 CAST(CASE WHEN (umuser <> 0 AND authorization_identifier = current_user)
2623 OR (umuser = 0 AND pg_has_role(srvowner, 'USAGE'))
2624 OR (SELECT rolsuper FROM pg_authid WHERE rolname = current_user) THEN (pg_options_to_table(um.umoptions)).option_value
2625 ELSE NULL END AS character_data) AS option_value
2626 FROM _pg_user_mappings um;
2628 GRANT SELECT ON user_mapping_options TO PUBLIC;
2633 * USER_MAPPINGS view
2635 CREATE VIEW user_mappings AS
2636 SELECT authorization_identifier,
2637 foreign_server_catalog,
2639 FROM _pg_user_mappings;
2641 GRANT SELECT ON user_mappings TO PUBLIC;