2 * SQL Information Schema
3 * as defined in ISO 9075-2:1999 chapter 20
5 * Copyright (c) 2003-2006, PostgreSQL Global Development Group
7 * $PostgreSQL: pgsql/src/backend/catalog/information_schema.sql,v 1.32 2006/03/05 15:58:22 momjian 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;
30 * A few supporting functions first ...
33 /* Expand any 1-D array into a set with integers 1..N */
34 CREATE FUNCTION _pg_expandarray(IN anyarray, OUT x anyelement, OUT n int)
36 LANGUAGE sql STRICT IMMUTABLE
37 AS 'select $1[s], s - pg_catalog.array_lower($1,1) + 1
38 from pg_catalog.generate_series(pg_catalog.array_lower($1,1),
39 pg_catalog.array_upper($1,1),
42 CREATE FUNCTION _pg_keyissubset(smallint[], smallint[]) RETURNS boolean
45 RETURNS NULL ON NULL INPUT
46 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))';
48 CREATE FUNCTION _pg_keysequal(smallint[], smallint[]) RETURNS boolean
51 RETURNS NULL ON NULL INPUT
52 AS 'select information_schema._pg_keyissubset($1, $2) and information_schema._pg_keyissubset($2, $1)';
54 CREATE FUNCTION _pg_truetypid(pg_attribute, pg_type) RETURNS oid
57 RETURNS NULL ON NULL INPUT
59 $$SELECT CASE WHEN $2.typtype = 'd' THEN $2.typbasetype ELSE $1.atttypid END$$;
61 CREATE FUNCTION _pg_truetypmod(pg_attribute, pg_type) RETURNS int4
64 RETURNS NULL ON NULL INPUT
66 $$SELECT CASE WHEN $2.typtype = 'd' THEN $2.typtypmod ELSE $1.atttypmod END$$;
68 -- these functions encapsulate knowledge about the encoding of typmod:
70 CREATE FUNCTION _pg_char_max_length(typid oid, typmod int4) RETURNS integer
73 RETURNS NULL ON NULL INPUT
76 CASE WHEN $2 = -1 /* default typmod */
78 WHEN $1 IN (1042, 1043) /* char, varchar */
80 WHEN $1 IN (1560, 1562) /* bit, varbit */
85 CREATE FUNCTION _pg_char_octet_length(typid oid, typmod int4) RETURNS integer
88 RETURNS NULL ON NULL INPUT
91 CASE WHEN $1 IN (25, 1042, 1043) /* text, char, varchar */
92 THEN CAST(2^30 AS integer)
96 CREATE FUNCTION _pg_numeric_precision(typid oid, typmod int4) RETURNS integer
99 RETURNS NULL ON NULL INPUT
103 WHEN 21 /*int2*/ THEN 16
104 WHEN 23 /*int4*/ THEN 32
105 WHEN 20 /*int8*/ THEN 64
106 WHEN 1700 /*numeric*/ THEN
109 ELSE (($2 - 4) >> 16) & 65535
111 WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/
112 WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/
116 CREATE FUNCTION _pg_numeric_precision_radix(typid oid, typmod int4) RETURNS integer
119 RETURNS NULL ON NULL INPUT
122 CASE WHEN $1 IN (21, 23, 20, 700, 701) THEN 2
123 WHEN $1 IN (1700) THEN 10
127 CREATE FUNCTION _pg_numeric_scale(typid oid, typmod int4) RETURNS integer
130 RETURNS NULL ON NULL INPUT
133 CASE WHEN $1 IN (21, 23, 20) THEN 0
134 WHEN $1 IN (1700) THEN
137 ELSE ($2 - 4) & 65535
142 CREATE FUNCTION _pg_datetime_precision(typid oid, typmod int4) RETURNS integer
145 RETURNS NULL ON NULL INPUT
148 CASE WHEN $2 = -1 /* default typmod */
150 WHEN $1 IN (1083, 1114, 1184, 1266) /* time, timestamp, same + tz */
152 WHEN $1 IN (1186) /* interval */
158 -- 20.3 INFORMATION_SCHEMA_CATALOG_NAME view appears later.
163 * CARDINAL_NUMBER domain
166 CREATE DOMAIN cardinal_number AS integer
167 CONSTRAINT cardinal_number_domain_check CHECK (value >= 0);
172 * CHARACTER_DATA domain
175 CREATE DOMAIN character_data AS character varying;
180 * SQL_IDENTIFIER domain
183 CREATE DOMAIN sql_identifier AS character varying;
188 * INFORMATION_SCHEMA_CATALOG_NAME view
191 CREATE VIEW information_schema_catalog_name AS
192 SELECT CAST(current_database() AS sql_identifier) AS catalog_name;
194 GRANT SELECT ON information_schema_catalog_name TO PUBLIC;
202 CREATE DOMAIN time_stamp AS timestamp(2)
203 DEFAULT current_timestamp(2);
208 * APPLICABLE_ROLES view
211 CREATE VIEW applicable_roles AS
212 SELECT CAST(a.rolname AS sql_identifier) AS grantee,
213 CAST(b.rolname AS sql_identifier) AS role_name,
214 CAST(CASE WHEN m.admin_option THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
215 FROM pg_auth_members m
216 JOIN pg_authid a ON (m.member = a.oid)
217 JOIN pg_authid b ON (m.roleid = b.oid)
218 WHERE pg_has_role(a.oid, 'MEMBER');
220 GRANT SELECT ON applicable_roles TO PUBLIC;
225 * CHECK_CONSTRAINTS view
228 CREATE VIEW check_constraints AS
229 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
230 CAST(rs.nspname AS sql_identifier) AS constraint_schema,
231 CAST(con.conname AS sql_identifier) AS constraint_name,
232 CAST(substring(pg_get_constraintdef(con.oid) from 7) AS character_data)
234 FROM pg_constraint con
235 LEFT OUTER JOIN pg_namespace rs ON (rs.oid = con.connamespace)
236 LEFT OUTER JOIN pg_class c ON (c.oid = con.conrelid)
237 LEFT OUTER JOIN pg_type t ON (t.oid = con.contypid)
238 WHERE pg_has_role(coalesce(c.relowner, t.typowner), 'MEMBER')
239 AND con.contype = 'c';
241 GRANT SELECT ON check_constraints TO PUBLIC;
246 * COLUMN_DOMAIN_USAGE view
249 CREATE VIEW column_domain_usage AS
250 SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
251 CAST(nt.nspname AS sql_identifier) AS domain_schema,
252 CAST(t.typname AS sql_identifier) AS domain_name,
253 CAST(current_database() AS sql_identifier) AS table_catalog,
254 CAST(nc.nspname AS sql_identifier) AS table_schema,
255 CAST(c.relname AS sql_identifier) AS table_name,
256 CAST(a.attname AS sql_identifier) AS column_name
258 FROM pg_type t, pg_namespace nt, pg_class c, pg_namespace nc,
261 WHERE t.typnamespace = nt.oid
262 AND c.relnamespace = nc.oid
263 AND a.attrelid = c.oid
264 AND a.atttypid = t.oid
266 AND c.relkind IN ('r', 'v')
268 AND NOT a.attisdropped
269 AND pg_has_role(t.typowner, 'MEMBER');
271 GRANT SELECT ON column_domain_usage TO PUBLIC;
279 CREATE VIEW column_privileges AS
280 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
281 CAST(grantee.rolname AS sql_identifier) AS grantee,
282 CAST(current_database() AS sql_identifier) AS table_catalog,
283 CAST(nc.nspname AS sql_identifier) AS table_schema,
284 CAST(c.relname AS sql_identifier) AS table_name,
285 CAST(a.attname AS sql_identifier) AS column_name,
286 CAST(pr.type AS character_data) AS privilege_type,
288 CASE WHEN aclcontains(c.relacl,
289 makeaclitem(grantee.oid, u_grantor.oid, pr.type, true))
290 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
297 SELECT oid, rolname FROM pg_authid
299 SELECT 0::oid, 'PUBLIC'
300 ) AS grantee (oid, rolname),
301 (SELECT 'SELECT' UNION ALL
302 SELECT 'INSERT' UNION ALL
303 SELECT 'UPDATE' UNION ALL
304 SELECT 'REFERENCES') AS pr (type)
306 WHERE a.attrelid = c.oid
307 AND c.relnamespace = nc.oid
309 AND NOT a.attisdropped
310 AND c.relkind IN ('r', 'v')
311 AND aclcontains(c.relacl,
312 makeaclitem(grantee.oid, u_grantor.oid, pr.type, false))
313 AND (pg_has_role(u_grantor.oid, 'MEMBER')
314 OR pg_has_role(grantee.oid, 'MEMBER')
315 OR grantee.rolname = 'PUBLIC');
317 GRANT SELECT ON column_privileges TO PUBLIC;
322 * COLUMN_UDT_USAGE view
325 CREATE VIEW column_udt_usage AS
326 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
327 CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,
328 CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,
329 CAST(current_database() AS sql_identifier) AS table_catalog,
330 CAST(nc.nspname AS sql_identifier) AS table_schema,
331 CAST(c.relname AS sql_identifier) AS table_name,
332 CAST(a.attname AS sql_identifier) AS column_name
334 FROM pg_attribute a, pg_class c, pg_namespace nc,
335 (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
336 LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
337 ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
339 WHERE a.attrelid = c.oid
340 AND a.atttypid = t.oid
341 AND nc.oid = c.relnamespace
342 AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
343 AND pg_has_role(coalesce(bt.typowner, t.typowner), 'MEMBER');
345 GRANT SELECT ON column_udt_usage TO PUBLIC;
353 CREATE VIEW columns AS
354 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
355 CAST(nc.nspname AS sql_identifier) AS table_schema,
356 CAST(c.relname AS sql_identifier) AS table_name,
357 CAST(a.attname AS sql_identifier) AS column_name,
358 CAST(a.attnum AS cardinal_number) AS ordinal_position,
360 CASE WHEN pg_has_role(c.relowner, 'MEMBER') THEN pg_get_expr(ad.adbin, ad.adrelid)
364 CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
369 CASE WHEN t.typtype = 'd' THEN
370 CASE WHEN bt.typelem <> 0 AND bt.typlen = -1 THEN 'ARRAY'
371 WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
372 ELSE 'USER-DEFINED' END
374 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
375 WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, null)
376 ELSE 'USER-DEFINED' END
382 _pg_char_max_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
384 AS character_maximum_length,
387 _pg_char_octet_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
389 AS character_octet_length,
392 _pg_numeric_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
394 AS numeric_precision,
397 _pg_numeric_precision_radix(_pg_truetypid(a, t), _pg_truetypmod(a, t))
399 AS numeric_precision_radix,
402 _pg_numeric_scale(_pg_truetypid(a, t), _pg_truetypmod(a, t))
407 _pg_datetime_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
409 AS datetime_precision,
411 CAST(null AS character_data) AS interval_type, -- XXX
412 CAST(null AS character_data) AS interval_precision, -- XXX
414 CAST(null AS sql_identifier) AS character_set_catalog,
415 CAST(null AS sql_identifier) AS character_set_schema,
416 CAST(null AS sql_identifier) AS character_set_name,
418 CAST(null AS sql_identifier) AS collation_catalog,
419 CAST(null AS sql_identifier) AS collation_schema,
420 CAST(null AS sql_identifier) AS collation_name,
422 CAST(CASE WHEN t.typtype = 'd' THEN current_database() ELSE null END
423 AS sql_identifier) AS domain_catalog,
424 CAST(CASE WHEN t.typtype = 'd' THEN nt.nspname ELSE null END
425 AS sql_identifier) AS domain_schema,
426 CAST(CASE WHEN t.typtype = 'd' THEN t.typname ELSE null END
427 AS sql_identifier) AS domain_name,
429 CAST(current_database() AS sql_identifier) AS udt_catalog,
430 CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,
431 CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,
433 CAST(null AS sql_identifier) AS scope_catalog,
434 CAST(null AS sql_identifier) AS scope_schema,
435 CAST(null AS sql_identifier) AS scope_name,
437 CAST(null AS cardinal_number) AS maximum_cardinality,
438 CAST(a.attnum AS sql_identifier) AS dtd_identifier,
439 CAST('NO' AS character_data) AS is_self_referencing
441 FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum),
442 pg_class c, pg_namespace nc,
443 (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
444 LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
445 ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
447 WHERE a.attrelid = c.oid
448 AND a.atttypid = t.oid
449 AND nc.oid = c.relnamespace
451 AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
453 AND (pg_has_role(c.relowner, 'MEMBER')
454 OR has_table_privilege(c.oid, 'SELECT')
455 OR has_table_privilege(c.oid, 'INSERT')
456 OR has_table_privilege(c.oid, 'UPDATE')
457 OR has_table_privilege(c.oid, 'REFERENCES') );
459 GRANT SELECT ON columns TO PUBLIC;
464 * CONSTRAINT_COLUMN_USAGE view
467 CREATE VIEW constraint_column_usage AS
468 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
469 CAST(tblschema AS sql_identifier) AS table_schema,
470 CAST(tblname AS sql_identifier) AS table_name,
471 CAST(colname AS sql_identifier) AS column_name,
472 CAST(current_database() AS sql_identifier) AS constraint_catalog,
473 CAST(cstrschema AS sql_identifier) AS constraint_schema,
474 CAST(cstrname AS sql_identifier) AS constraint_name
477 /* check constraints */
478 SELECT DISTINCT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
479 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_depend d, pg_namespace nc, pg_constraint c
480 WHERE nr.oid = r.relnamespace
481 AND r.oid = a.attrelid
482 AND d.refclassid = 'pg_catalog.pg_class'::regclass
483 AND d.refobjid = r.oid
484 AND d.refobjsubid = a.attnum
485 AND d.classid = 'pg_catalog.pg_constraint'::regclass
487 AND c.connamespace = nc.oid
490 AND NOT a.attisdropped
494 /* unique/primary key/foreign key constraints */
495 SELECT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
496 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc,
498 WHERE nr.oid = r.relnamespace
499 AND r.oid = a.attrelid
500 AND nc.oid = c.connamespace
501 AND (CASE WHEN c.contype = 'f' THEN r.oid = c.confrelid AND a.attnum = ANY (c.confkey)
502 ELSE r.oid = c.conrelid AND a.attnum = ANY (c.conkey) END)
503 AND NOT a.attisdropped
504 AND c.contype IN ('p', 'u', 'f')
507 ) AS x (tblschema, tblname, tblowner, colname, cstrschema, cstrname)
509 WHERE pg_has_role(x.tblowner, 'MEMBER');
511 GRANT SELECT ON constraint_column_usage TO PUBLIC;
516 * CONSTRAINT_TABLE_USAGE view
519 CREATE VIEW constraint_table_usage AS
520 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
521 CAST(nr.nspname AS sql_identifier) AS table_schema,
522 CAST(r.relname AS sql_identifier) AS table_name,
523 CAST(current_database() AS sql_identifier) AS constraint_catalog,
524 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
525 CAST(c.conname AS sql_identifier) AS constraint_name
527 FROM pg_constraint c, pg_namespace nc,
528 pg_class r, pg_namespace nr
530 WHERE c.connamespace = nc.oid AND r.relnamespace = nr.oid
531 AND ( (c.contype = 'f' AND c.confrelid = r.oid)
532 OR (c.contype IN ('p', 'u') AND c.conrelid = r.oid) )
534 AND pg_has_role(r.relowner, 'MEMBER');
536 GRANT SELECT ON constraint_table_usage TO PUBLIC;
539 -- 20.21 DATA_TYPE_PRIVILEGES view appears later.
544 * DOMAIN_CONSTRAINTS view
547 CREATE VIEW domain_constraints AS
548 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
549 CAST(rs.nspname AS sql_identifier) AS constraint_schema,
550 CAST(con.conname AS sql_identifier) AS constraint_name,
551 CAST(current_database() AS sql_identifier) AS domain_catalog,
552 CAST(n.nspname AS sql_identifier) AS domain_schema,
553 CAST(t.typname AS sql_identifier) AS domain_name,
554 CAST(CASE WHEN condeferrable THEN 'YES' ELSE 'NO' END
555 AS character_data) AS is_deferrable,
556 CAST(CASE WHEN condeferred THEN 'YES' ELSE 'NO' END
557 AS character_data) AS initially_deferred
558 FROM pg_namespace rs, pg_namespace n, pg_constraint con, pg_type t
559 WHERE rs.oid = con.connamespace
560 AND n.oid = t.typnamespace
561 AND t.oid = con.contypid
562 AND pg_has_role(t.typowner, 'MEMBER');
564 GRANT SELECT ON domain_constraints TO PUBLIC;
569 * DOMAIN_UDT_USAGE view
572 CREATE VIEW domain_udt_usage AS
573 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
574 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
575 CAST(bt.typname AS sql_identifier) AS udt_name,
576 CAST(current_database() AS sql_identifier) AS domain_catalog,
577 CAST(nt.nspname AS sql_identifier) AS domain_schema,
578 CAST(t.typname AS sql_identifier) AS domain_name
580 FROM pg_type t, pg_namespace nt,
581 pg_type bt, pg_namespace nbt
583 WHERE t.typnamespace = nt.oid
584 AND t.typbasetype = bt.oid
585 AND bt.typnamespace = nbt.oid
587 AND pg_has_role(bt.typowner, 'MEMBER');
589 GRANT SELECT ON domain_udt_usage TO PUBLIC;
597 CREATE VIEW domains AS
598 SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
599 CAST(nt.nspname AS sql_identifier) AS domain_schema,
600 CAST(t.typname AS sql_identifier) AS domain_name,
603 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
604 WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
605 ELSE 'USER-DEFINED' END
610 _pg_char_max_length(t.typbasetype, t.typtypmod)
612 AS character_maximum_length,
615 _pg_char_octet_length(t.typbasetype, t.typtypmod)
617 AS character_octet_length,
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,
628 _pg_numeric_precision(t.typbasetype, t.typtypmod)
630 AS numeric_precision,
633 _pg_numeric_precision_radix(t.typbasetype, t.typtypmod)
635 AS numeric_precision_radix,
638 _pg_numeric_scale(t.typbasetype, t.typtypmod)
643 _pg_datetime_precision(t.typbasetype, t.typtypmod)
645 AS datetime_precision,
647 CAST(null AS character_data) AS interval_type, -- XXX
648 CAST(null AS character_data) AS interval_precision, -- XXX
650 CAST(t.typdefault AS character_data) AS domain_default,
652 CAST(current_database() AS sql_identifier) AS udt_catalog,
653 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
654 CAST(bt.typname AS sql_identifier) AS udt_name,
656 CAST(null AS sql_identifier) AS scope_catalog,
657 CAST(null AS sql_identifier) AS scope_schema,
658 CAST(null AS sql_identifier) AS scope_name,
660 CAST(null AS cardinal_number) AS maximum_cardinality,
661 CAST(1 AS sql_identifier) AS dtd_identifier
663 FROM pg_type t, pg_namespace nt,
664 pg_type bt, pg_namespace nbt
666 WHERE t.typnamespace = nt.oid
667 AND t.typbasetype = bt.oid
668 AND bt.typnamespace = nbt.oid
671 GRANT SELECT ON domains TO PUBLIC;
674 -- 20.27 ELEMENT_TYPES view appears later.
682 CREATE VIEW enabled_roles AS
683 SELECT CAST(a.rolname AS sql_identifier) AS role_name
685 WHERE pg_has_role(a.oid, 'MEMBER');
687 GRANT SELECT ON enabled_roles TO PUBLIC;
692 * KEY_COLUMN_USAGE view
695 CREATE VIEW key_column_usage AS
696 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
697 CAST(nc_nspname AS sql_identifier) AS constraint_schema,
698 CAST(conname AS sql_identifier) AS constraint_name,
699 CAST(current_database() AS sql_identifier) AS table_catalog,
700 CAST(nr_nspname AS sql_identifier) AS table_schema,
701 CAST(relname AS sql_identifier) AS table_name,
702 CAST(a.attname AS sql_identifier) AS column_name,
703 CAST((ss.x).n AS cardinal_number) AS ordinal_position
706 (SELECT r.oid, nc.nspname AS nc_nspname, c.conname,
707 nr.nspname AS nr_nspname, r.relname,
708 _pg_expandarray(c.conkey) AS x
709 FROM pg_namespace nr, pg_class r, pg_namespace nc,
711 WHERE nr.oid = r.relnamespace
712 AND r.oid = c.conrelid
713 AND nc.oid = c.connamespace
714 AND c.contype IN ('p', 'u', 'f')
716 AND pg_has_role(r.relowner, 'MEMBER')) AS ss
717 WHERE ss.oid = a.attrelid
718 AND a.attnum = (ss.x).x
719 AND NOT a.attisdropped;
721 GRANT SELECT ON key_column_usage TO PUBLIC;
729 CREATE VIEW parameters AS
730 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
731 CAST(n_nspname AS sql_identifier) AS specific_schema,
732 CAST(proname || '_' || CAST(p_oid AS text) AS sql_identifier) AS specific_name,
733 CAST((ss.x).n AS cardinal_number) AS ordinal_position,
735 CASE WHEN proargmodes IS NULL THEN 'IN'
736 WHEN proargmodes[(ss.x).n] = 'i' THEN 'IN'
737 WHEN proargmodes[(ss.x).n] = 'o' THEN 'OUT'
738 WHEN proargmodes[(ss.x).n] = 'b' THEN 'INOUT'
739 END AS character_data) AS parameter_mode,
740 CAST('NO' AS character_data) AS is_result,
741 CAST('NO' AS character_data) AS as_locator,
742 CAST(NULLIF(proargnames[(ss.x).n], '') AS sql_identifier) AS parameter_name,
744 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
745 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
746 ELSE 'USER-DEFINED' END AS character_data)
748 CAST(null AS cardinal_number) AS character_maximum_length,
749 CAST(null AS cardinal_number) AS character_octet_length,
750 CAST(null AS sql_identifier) AS character_set_catalog,
751 CAST(null AS sql_identifier) AS character_set_schema,
752 CAST(null AS sql_identifier) AS character_set_name,
753 CAST(null AS sql_identifier) AS collation_catalog,
754 CAST(null AS sql_identifier) AS collation_schema,
755 CAST(null AS sql_identifier) AS collation_name,
756 CAST(null AS cardinal_number) AS numeric_precision,
757 CAST(null AS cardinal_number) AS numeric_precision_radix,
758 CAST(null AS cardinal_number) AS numeric_scale,
759 CAST(null AS cardinal_number) AS datetime_precision,
760 CAST(null AS character_data) AS interval_type,
761 CAST(null AS character_data) AS interval_precision,
762 CAST(current_database() AS sql_identifier) AS udt_catalog,
763 CAST(nt.nspname AS sql_identifier) AS udt_schema,
764 CAST(t.typname AS sql_identifier) AS udt_name,
765 CAST(null AS sql_identifier) AS scope_catalog,
766 CAST(null AS sql_identifier) AS scope_schema,
767 CAST(null AS sql_identifier) AS scope_name,
768 CAST(null AS cardinal_number) AS maximum_cardinality,
769 CAST((ss.x).n AS sql_identifier) AS dtd_identifier
771 FROM pg_type t, pg_namespace nt,
772 (SELECT n.nspname AS n_nspname, p.proname, p.oid AS p_oid,
773 p.proargnames, p.proargmodes,
774 _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
775 FROM pg_namespace n, pg_proc p
776 WHERE n.oid = p.pronamespace
777 AND (pg_has_role(p.proowner, 'MEMBER') OR
778 has_function_privilege(p.oid, 'EXECUTE'))) AS ss
779 WHERE t.oid = (ss.x).x AND t.typnamespace = nt.oid;
781 GRANT SELECT ON parameters TO PUBLIC;
786 * REFERENTIAL_CONSTRAINTS view
789 CREATE VIEW referential_constraints AS
790 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
791 CAST(ncon.nspname AS sql_identifier) AS constraint_schema,
792 CAST(con.conname AS sql_identifier) AS constraint_name,
794 CASE WHEN npkc.nspname IS NULL THEN NULL
795 ELSE current_database() END
796 AS sql_identifier) AS unique_constraint_catalog,
797 CAST(npkc.nspname AS sql_identifier) AS unique_constraint_schema,
798 CAST(pkc.conname AS sql_identifier) AS unique_constraint_name,
801 CASE con.confmatchtype WHEN 'f' THEN 'FULL'
802 WHEN 'p' THEN 'PARTIAL'
803 WHEN 'u' THEN 'NONE' END
804 AS character_data) AS match_option,
807 CASE con.confupdtype WHEN 'c' THEN 'CASCADE'
808 WHEN 'n' THEN 'SET NULL'
809 WHEN 'd' THEN 'SET DEFAULT'
810 WHEN 'r' THEN 'RESTRICT'
811 WHEN 'a' THEN 'NO ACTION' END
812 AS character_data) AS update_rule,
815 CASE con.confdeltype WHEN 'c' THEN 'CASCADE'
816 WHEN 'n' THEN 'SET NULL'
817 WHEN 'd' THEN 'SET DEFAULT'
818 WHEN 'r' THEN 'RESTRICT'
819 WHEN 'a' THEN 'NO ACTION' END
820 AS character_data) AS delete_rule
822 FROM (pg_namespace ncon
823 INNER JOIN pg_constraint con ON ncon.oid = con.connamespace
824 INNER JOIN pg_class c ON con.conrelid = c.oid)
827 INNER JOIN pg_namespace npkc ON pkc.connamespace = npkc.oid)
828 ON con.confrelid = pkc.conrelid
829 AND _pg_keysequal(con.confkey, pkc.conkey)
831 WHERE c.relkind = 'r'
832 AND con.contype = 'f'
833 AND (pkc.contype IN ('p', 'u') OR pkc.contype IS NULL)
834 AND pg_has_role(c.relowner, 'MEMBER');
836 GRANT SELECT ON referential_constraints TO PUBLIC;
841 * ROLE_COLUMN_GRANTS view
844 CREATE VIEW role_column_grants AS
845 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
846 CAST(g_grantee.rolname AS sql_identifier) AS grantee,
847 CAST(current_database() AS sql_identifier) AS table_catalog,
848 CAST(nc.nspname AS sql_identifier) AS table_schema,
849 CAST(c.relname AS sql_identifier) AS table_name,
850 CAST(a.attname AS sql_identifier) AS column_name,
851 CAST(pr.type AS character_data) AS privilege_type,
853 CASE WHEN aclcontains(c.relacl,
854 makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, true))
855 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
862 (SELECT 'SELECT' UNION ALL
863 SELECT 'INSERT' UNION ALL
864 SELECT 'UPDATE' UNION ALL
865 SELECT 'REFERENCES') AS pr (type)
867 WHERE a.attrelid = c.oid
868 AND c.relnamespace = nc.oid
870 AND NOT a.attisdropped
871 AND c.relkind IN ('r', 'v')
872 AND aclcontains(c.relacl,
873 makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, false))
874 AND g_grantee.rolname IN (SELECT role_name FROM enabled_roles);
876 GRANT SELECT ON role_column_grants TO PUBLIC;
881 * ROLE_ROUTINE_GRANTS view
884 CREATE VIEW role_routine_grants AS
885 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
886 CAST(g_grantee.rolname AS sql_identifier) AS grantee,
887 CAST(current_database() AS sql_identifier) AS specific_catalog,
888 CAST(n.nspname AS sql_identifier) AS specific_schema,
889 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
890 CAST(current_database() AS sql_identifier) AS routine_catalog,
891 CAST(n.nspname AS sql_identifier) AS routine_schema,
892 CAST(p.proname AS sql_identifier) AS routine_name,
893 CAST('EXECUTE' AS character_data) AS privilege_type,
895 CASE WHEN aclcontains(p.proacl,
896 makeaclitem(g_grantee.oid, u_grantor.oid, 'EXECUTE', true))
897 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
904 WHERE p.pronamespace = n.oid
905 AND aclcontains(p.proacl,
906 makeaclitem(g_grantee.oid, u_grantor.oid, 'EXECUTE', false))
907 AND g_grantee.rolname IN (SELECT role_name FROM enabled_roles);
909 GRANT SELECT ON role_routine_grants TO PUBLIC;
914 * ROLE_TABLE_GRANTS view
917 CREATE VIEW role_table_grants AS
918 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
919 CAST(g_grantee.rolname AS sql_identifier) AS grantee,
920 CAST(current_database() AS sql_identifier) AS table_catalog,
921 CAST(nc.nspname AS sql_identifier) AS table_schema,
922 CAST(c.relname AS sql_identifier) AS table_name,
923 CAST(pr.type AS character_data) AS privilege_type,
925 CASE WHEN aclcontains(c.relacl,
926 makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, true))
927 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable,
928 CAST('NO' AS character_data) AS with_hierarchy
934 (SELECT 'SELECT' UNION ALL
935 SELECT 'DELETE' UNION ALL
936 SELECT 'INSERT' UNION ALL
937 SELECT 'UPDATE' UNION ALL
938 SELECT 'REFERENCES' UNION ALL
939 SELECT 'RULE' UNION ALL
940 SELECT 'TRIGGER') AS pr (type)
942 WHERE c.relnamespace = nc.oid
943 AND c.relkind IN ('r', 'v')
944 AND aclcontains(c.relacl,
945 makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, false))
946 AND g_grantee.rolname IN (SELECT role_name FROM enabled_roles);
948 GRANT SELECT ON role_table_grants TO PUBLIC;
953 * ROLE_USAGE_GRANTS view
956 -- See USAGE_PRIVILEGES.
958 CREATE VIEW role_usage_grants AS
959 SELECT CAST(null AS sql_identifier) AS grantor,
960 CAST(null AS sql_identifier) AS grantee,
961 CAST(current_database() AS sql_identifier) AS object_catalog,
962 CAST(null AS sql_identifier) AS object_schema,
963 CAST(null AS sql_identifier) AS object_name,
964 CAST(null AS character_data) AS object_type,
965 CAST('USAGE' AS character_data) AS privilege_type,
966 CAST(null AS character_data) AS is_grantable
970 GRANT SELECT ON role_usage_grants TO PUBLIC;
975 * ROUTINE_PRIVILEGES view
978 CREATE VIEW routine_privileges AS
979 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
980 CAST(grantee.rolname AS sql_identifier) AS grantee,
981 CAST(current_database() AS sql_identifier) AS specific_catalog,
982 CAST(n.nspname AS sql_identifier) AS specific_schema,
983 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
984 CAST(current_database() AS sql_identifier) AS routine_catalog,
985 CAST(n.nspname AS sql_identifier) AS routine_schema,
986 CAST(p.proname AS sql_identifier) AS routine_name,
987 CAST('EXECUTE' AS character_data) AS privilege_type,
989 CASE WHEN aclcontains(p.proacl,
990 makeaclitem(grantee.oid, u_grantor.oid, 'EXECUTE', true))
991 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
997 SELECT oid, rolname FROM pg_authid
999 SELECT 0::oid, 'PUBLIC'
1000 ) AS grantee (oid, rolname)
1002 WHERE p.pronamespace = n.oid
1003 AND aclcontains(p.proacl,
1004 makeaclitem(grantee.oid, u_grantor.oid, 'EXECUTE', false))
1005 AND (pg_has_role(u_grantor.oid, 'MEMBER')
1006 OR pg_has_role(grantee.oid, 'MEMBER')
1007 OR grantee.rolname = 'PUBLIC');
1009 GRANT SELECT ON routine_privileges TO PUBLIC;
1017 CREATE VIEW routines AS
1018 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
1019 CAST(n.nspname AS sql_identifier) AS specific_schema,
1020 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1021 CAST(current_database() AS sql_identifier) AS routine_catalog,
1022 CAST(n.nspname AS sql_identifier) AS routine_schema,
1023 CAST(p.proname AS sql_identifier) AS routine_name,
1024 CAST('FUNCTION' AS character_data) AS routine_type,
1025 CAST(null AS sql_identifier) AS module_catalog,
1026 CAST(null AS sql_identifier) AS module_schema,
1027 CAST(null AS sql_identifier) AS module_name,
1028 CAST(null AS sql_identifier) AS udt_catalog,
1029 CAST(null AS sql_identifier) AS udt_schema,
1030 CAST(null AS sql_identifier) AS udt_name,
1033 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
1034 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
1035 ELSE 'USER-DEFINED' END AS character_data)
1037 CAST(null AS cardinal_number) AS character_maximum_length,
1038 CAST(null AS cardinal_number) AS character_octet_length,
1039 CAST(null AS sql_identifier) AS character_set_catalog,
1040 CAST(null AS sql_identifier) AS character_set_schema,
1041 CAST(null AS sql_identifier) AS character_set_name,
1042 CAST(null AS sql_identifier) AS collation_catalog,
1043 CAST(null AS sql_identifier) AS collation_schema,
1044 CAST(null AS sql_identifier) AS collation_name,
1045 CAST(null AS cardinal_number) AS numeric_precision,
1046 CAST(null AS cardinal_number) AS numeric_precision_radix,
1047 CAST(null AS cardinal_number) AS numeric_scale,
1048 CAST(null AS cardinal_number) AS datetime_precision,
1049 CAST(null AS character_data) AS interval_type,
1050 CAST(null AS character_data) AS interval_precision,
1051 CAST(current_database() AS sql_identifier) AS type_udt_catalog,
1052 CAST(nt.nspname AS sql_identifier) AS type_udt_schema,
1053 CAST(t.typname AS sql_identifier) AS type_udt_name,
1054 CAST(null AS sql_identifier) AS scope_catalog,
1055 CAST(null AS sql_identifier) AS scope_schema,
1056 CAST(null AS sql_identifier) AS scope_name,
1057 CAST(null AS cardinal_number) AS maximum_cardinality,
1058 CAST(0 AS sql_identifier) AS dtd_identifier,
1060 CAST(CASE WHEN l.lanname = 'sql' THEN 'SQL' ELSE 'EXTERNAL' END AS character_data)
1063 CASE WHEN pg_has_role(p.proowner, 'MEMBER') THEN p.prosrc ELSE null END
1064 AS character_data) AS routine_definition,
1066 CASE WHEN l.lanname = 'c' THEN p.prosrc ELSE null END
1067 AS character_data) AS external_name,
1068 CAST(upper(l.lanname) AS character_data) AS external_language,
1070 CAST('GENERAL' AS character_data) AS parameter_style,
1071 CAST(CASE WHEN p.provolatile = 'i' THEN 'YES' ELSE 'NO' END AS character_data) AS is_deterministic,
1072 CAST('MODIFIES' AS character_data) AS sql_data_access,
1073 CAST(CASE WHEN p.proisstrict THEN 'YES' ELSE 'NO' END AS character_data) AS is_null_call,
1074 CAST(null AS character_data) AS sql_path,
1075 CAST('YES' AS character_data) AS schema_level_routine,
1076 CAST(0 AS cardinal_number) AS max_dynamic_result_sets,
1077 CAST(null AS character_data) AS is_user_defined_cast,
1078 CAST(null AS character_data) AS is_implicitly_invocable,
1079 CAST(CASE WHEN p.prosecdef THEN 'DEFINER' ELSE 'INVOKER' END AS character_data) AS security_type,
1080 CAST(null AS sql_identifier) AS to_sql_specific_catalog,
1081 CAST(null AS sql_identifier) AS to_sql_specific_schema,
1082 CAST(null AS sql_identifier) AS to_sql_specific_name,
1083 CAST('NO' AS character_data) AS as_locator
1085 FROM pg_namespace n, pg_proc p, pg_language l,
1086 pg_type t, pg_namespace nt
1088 WHERE n.oid = p.pronamespace AND p.prolang = l.oid
1089 AND p.prorettype = t.oid AND t.typnamespace = nt.oid
1090 AND (pg_has_role(p.proowner, 'MEMBER')
1091 OR has_function_privilege(p.oid, 'EXECUTE'));
1093 GRANT SELECT ON routines TO PUBLIC;
1101 CREATE VIEW schemata AS
1102 SELECT CAST(current_database() AS sql_identifier) AS catalog_name,
1103 CAST(n.nspname AS sql_identifier) AS schema_name,
1104 CAST(u.rolname AS sql_identifier) AS schema_owner,
1105 CAST(null AS sql_identifier) AS default_character_set_catalog,
1106 CAST(null AS sql_identifier) AS default_character_set_schema,
1107 CAST(null AS sql_identifier) AS default_character_set_name,
1108 CAST(null AS character_data) AS sql_path
1109 FROM pg_namespace n, pg_authid u
1110 WHERE n.nspowner = u.oid AND pg_has_role(n.nspowner, 'MEMBER');
1112 GRANT SELECT ON schemata TO PUBLIC;
1117 * SQL_FEATURES table
1120 CREATE TABLE sql_features (
1121 feature_id character_data,
1122 feature_name character_data,
1123 sub_feature_id character_data,
1124 sub_feature_name character_data,
1125 is_supported character_data,
1126 is_verified_by character_data,
1127 comments character_data
1130 -- Will be filled with external data by initdb.
1132 GRANT SELECT ON sql_features TO PUBLIC;
1137 * SQL_IMPLEMENTATION_INFO table
1140 -- Note: Implementation information items are defined in ISO 9075-3:1999,
1143 CREATE TABLE sql_implementation_info (
1144 implementation_info_id character_data,
1145 implementation_info_name character_data,
1146 integer_value cardinal_number,
1147 character_value character_data,
1148 comments character_data
1151 INSERT INTO sql_implementation_info VALUES ('10003', 'CATALOG NAME', NULL, 'Y', NULL);
1152 INSERT INTO sql_implementation_info VALUES ('10004', 'COLLATING SEQUENCE', NULL, '', 'not supported');
1153 INSERT INTO sql_implementation_info VALUES ('23', 'CURSOR COMMIT BEHAVIOR', 1, NULL, 'close cursors and retain prepared statements');
1154 INSERT INTO sql_implementation_info VALUES ('2', 'DATA SOURCE NAME', NULL, '', NULL);
1155 INSERT INTO sql_implementation_info VALUES ('17', 'DBMS NAME', NULL, (select trim(trailing ' ' from substring(version() from '^[^0-9]*'))), NULL);
1156 INSERT INTO sql_implementation_info VALUES ('18', 'DBMS VERSION', NULL, '???', NULL); -- filled by initdb
1157 INSERT INTO sql_implementation_info VALUES ('26', 'DEFAULT TRANSACTION ISOLATION', 2, NULL, 'READ COMMITTED; user-settable');
1158 INSERT INTO sql_implementation_info VALUES ('28', 'IDENTIFIER CASE', 3, NULL, 'stored in mixed case - case sensitive');
1159 INSERT INTO sql_implementation_info VALUES ('85', 'NULL COLLATION', 0, NULL, 'nulls higher than non-nulls');
1160 INSERT INTO sql_implementation_info VALUES ('13', 'SERVER NAME', NULL, '', NULL);
1161 INSERT INTO sql_implementation_info VALUES ('94', 'SPECIAL CHARACTERS', NULL, '', 'all non-ASCII characters allowed');
1162 INSERT INTO sql_implementation_info VALUES ('46', 'TRANSACTION CAPABLE', 2, NULL, 'both DML and DDL');
1164 GRANT SELECT ON sql_implementation_info TO PUBLIC;
1169 * SQL_LANGUAGES table
1172 CREATE TABLE sql_languages (
1173 sql_language_source character_data,
1174 sql_language_year character_data,
1175 sql_language_conformance character_data,
1176 sql_language_integrity character_data,
1177 sql_language_implementation character_data,
1178 sql_language_binding_style character_data,
1179 sql_language_programming_language character_data
1182 INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'DIRECT', NULL);
1183 INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
1185 GRANT SELECT ON sql_languages TO PUBLIC;
1190 * SQL_PACKAGES table
1193 CREATE TABLE sql_packages (
1194 feature_id character_data,
1195 feature_name character_data,
1196 is_supported character_data,
1197 is_verified_by character_data,
1198 comments character_data
1201 INSERT INTO sql_packages VALUES ('PKG000', 'Core', 'NO', NULL, '');
1202 INSERT INTO sql_packages VALUES ('PKG001', 'Enhanced datetime facilities', 'YES', NULL, '');
1203 INSERT INTO sql_packages VALUES ('PKG002', 'Enhanced integrity management', 'NO', NULL, '');
1204 INSERT INTO sql_packages VALUES ('PKG003', 'OLAP facilities', 'NO', NULL, '');
1205 INSERT INTO sql_packages VALUES ('PKG004', 'PSM', 'NO', NULL, 'PL/pgSQL is similar.');
1206 INSERT INTO sql_packages VALUES ('PKG005', 'CLI', 'NO', NULL, 'ODBC is similar.');
1207 INSERT INTO sql_packages VALUES ('PKG006', 'Basic object support', 'NO', NULL, '');
1208 INSERT INTO sql_packages VALUES ('PKG007', 'Enhanced object support', 'NO', NULL, '');
1209 INSERT INTO sql_packages VALUES ('PKG008', 'Active database', 'NO', NULL, '');
1210 INSERT INTO sql_packages VALUES ('PKG010', 'OLAP', 'NO', NULL, 'NO');
1212 GRANT SELECT ON sql_packages TO PUBLIC;
1220 -- Note: Sizing items are defined in ISO 9075-3:1999, clause 7.2.
1222 CREATE TABLE sql_sizing (
1223 sizing_id cardinal_number,
1224 sizing_name character_data,
1225 supported_value cardinal_number,
1226 comments character_data
1229 INSERT INTO sql_sizing VALUES (34, 'MAXIMUM CATALOG NAME LENGTH', 63, NULL);
1230 INSERT INTO sql_sizing VALUES (30, 'MAXIMUM COLUMN NAME LENGTH', 63, NULL);
1231 INSERT INTO sql_sizing VALUES (97, 'MAXIMUM COLUMNS IN GROUP BY', 0, NULL);
1232 INSERT INTO sql_sizing VALUES (99, 'MAXIMUM COLUMNS IN ORDER BY', 0, NULL);
1233 INSERT INTO sql_sizing VALUES (100, 'MAXIMUM COLUMNS IN SELECT', 1664, NULL); -- match MaxTupleAttributeNumber
1234 INSERT INTO sql_sizing VALUES (101, 'MAXIMUM COLUMNS IN TABLE', 1600, NULL); -- match MaxHeapAttributeNumber
1235 INSERT INTO sql_sizing VALUES (1, 'MAXIMUM CONCURRENT ACTIVITIES', 0, NULL);
1236 INSERT INTO sql_sizing VALUES (31, 'MAXIMUM CURSOR NAME LENGTH', 63, NULL);
1237 INSERT INTO sql_sizing VALUES (0, 'MAXIMUM DRIVER CONNECTIONS', NULL, NULL);
1238 INSERT INTO sql_sizing VALUES (10005, 'MAXIMUM IDENTIFIER LENGTH', 63, NULL);
1239 INSERT INTO sql_sizing VALUES (32, 'MAXIMUM SCHEMA NAME LENGTH', 63, NULL);
1240 INSERT INTO sql_sizing VALUES (20000, 'MAXIMUM STATEMENT OCTETS', 0, NULL);
1241 INSERT INTO sql_sizing VALUES (20001, 'MAXIMUM STATEMENT OCTETS DATA', 0, NULL);
1242 INSERT INTO sql_sizing VALUES (20002, 'MAXIMUM STATEMENT OCTETS SCHEMA', 0, NULL);
1243 INSERT INTO sql_sizing VALUES (35, 'MAXIMUM TABLE NAME LENGTH', 63, NULL);
1244 INSERT INTO sql_sizing VALUES (106, 'MAXIMUM TABLES IN SELECT', 0, NULL);
1245 INSERT INTO sql_sizing VALUES (107, 'MAXIMUM USER NAME LENGTH', 63, NULL);
1246 INSERT INTO sql_sizing VALUES (25000, 'MAXIMUM CURRENT DEFAULT TRANSFORM GROUP LENGTH', NULL, NULL);
1247 INSERT INTO sql_sizing VALUES (25001, 'MAXIMUM CURRENT TRANSFORM GROUP LENGTH', NULL, NULL);
1248 INSERT INTO sql_sizing VALUES (25002, 'MAXIMUM CURRENT PATH LENGTH', 0, NULL);
1249 INSERT INTO sql_sizing VALUES (25003, 'MAXIMUM CURRENT ROLE LENGTH', NULL, NULL);
1250 INSERT INTO sql_sizing VALUES (25004, 'MAXIMUM SESSION USER LENGTH', 63, NULL);
1251 INSERT INTO sql_sizing VALUES (25005, 'MAXIMUM SYSTEM USER LENGTH', 63, NULL);
1254 SET supported_value = (SELECT typlen-1 FROM pg_catalog.pg_type WHERE typname = 'name'),
1255 comments = 'Might be less, depending on character set.'
1256 WHERE supported_value = 63;
1258 GRANT SELECT ON sql_sizing TO PUBLIC;
1263 * SQL_SIZING_PROFILES table
1266 -- The data in this table are defined by various profiles of SQL.
1267 -- Since we don't have any information about such profiles, we provide
1270 CREATE TABLE sql_sizing_profiles (
1271 sizing_id cardinal_number,
1272 sizing_name character_data,
1273 profile_id character_data,
1274 required_value cardinal_number,
1275 comments character_data
1278 GRANT SELECT ON sql_sizing_profiles TO PUBLIC;
1283 * TABLE_CONSTRAINTS view
1286 CREATE VIEW table_constraints AS
1287 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1288 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
1289 CAST(c.conname AS sql_identifier) AS constraint_name,
1290 CAST(current_database() AS sql_identifier) AS table_catalog,
1291 CAST(nr.nspname AS sql_identifier) AS table_schema,
1292 CAST(r.relname AS sql_identifier) AS table_name,
1294 CASE c.contype WHEN 'c' THEN 'CHECK'
1295 WHEN 'f' THEN 'FOREIGN KEY'
1296 WHEN 'p' THEN 'PRIMARY KEY'
1297 WHEN 'u' THEN 'UNIQUE' END
1298 AS character_data) AS constraint_type,
1299 CAST(CASE WHEN c.condeferrable THEN 'YES' ELSE 'NO' END AS character_data)
1301 CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS character_data)
1302 AS initially_deferred
1304 FROM pg_namespace nc,
1309 WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace
1310 AND c.conrelid = r.oid
1312 AND pg_has_role(r.relowner, 'MEMBER');
1314 -- FIXME: Not-null constraints are missing here.
1316 GRANT SELECT ON table_constraints TO PUBLIC;
1321 * TABLE_PRIVILEGES view
1324 CREATE VIEW table_privileges AS
1325 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1326 CAST(grantee.rolname AS sql_identifier) AS grantee,
1327 CAST(current_database() AS sql_identifier) AS table_catalog,
1328 CAST(nc.nspname AS sql_identifier) AS table_schema,
1329 CAST(c.relname AS sql_identifier) AS table_name,
1330 CAST(pr.type AS character_data) AS privilege_type,
1332 CASE WHEN aclcontains(c.relacl,
1333 makeaclitem(grantee.oid, u_grantor.oid, pr.type, true))
1334 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable,
1335 CAST('NO' AS character_data) AS with_hierarchy
1339 pg_authid u_grantor,
1341 SELECT oid, rolname FROM pg_authid
1343 SELECT 0::oid, 'PUBLIC'
1344 ) AS grantee (oid, rolname),
1345 (SELECT 'SELECT' UNION ALL
1346 SELECT 'DELETE' UNION ALL
1347 SELECT 'INSERT' UNION ALL
1348 SELECT 'UPDATE' UNION ALL
1349 SELECT 'REFERENCES' UNION ALL
1350 SELECT 'RULE' UNION ALL
1351 SELECT 'TRIGGER') AS pr (type)
1353 WHERE c.relnamespace = nc.oid
1354 AND c.relkind IN ('r', 'v')
1355 AND aclcontains(c.relacl,
1356 makeaclitem(grantee.oid, u_grantor.oid, pr.type, false))
1357 AND (pg_has_role(u_grantor.oid, 'MEMBER')
1358 OR pg_has_role(grantee.oid, 'MEMBER')
1359 OR grantee.rolname = 'PUBLIC');
1361 GRANT SELECT ON table_privileges TO PUBLIC;
1369 CREATE VIEW tables AS
1370 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
1371 CAST(nc.nspname AS sql_identifier) AS table_schema,
1372 CAST(c.relname AS sql_identifier) AS table_name,
1375 CASE WHEN nc.nspname LIKE 'pg!_temp!_%' ESCAPE '!' THEN 'LOCAL TEMPORARY'
1376 WHEN c.relkind = 'r' THEN 'BASE TABLE'
1377 WHEN c.relkind = 'v' THEN 'VIEW'
1379 AS character_data) AS table_type,
1381 CAST(null AS sql_identifier) AS self_referencing_column_name,
1382 CAST(null AS character_data) AS reference_generation,
1384 CAST(null AS sql_identifier) AS user_defined_type_catalog,
1385 CAST(null AS sql_identifier) AS user_defined_type_schema,
1386 CAST(null AS sql_identifier) AS user_defined_name
1388 FROM pg_namespace nc, pg_class c
1390 WHERE c.relnamespace = nc.oid
1391 AND c.relkind IN ('r', 'v')
1392 AND (pg_has_role(c.relowner, 'MEMBER')
1393 OR has_table_privilege(c.oid, 'SELECT')
1394 OR has_table_privilege(c.oid, 'INSERT')
1395 OR has_table_privilege(c.oid, 'UPDATE')
1396 OR has_table_privilege(c.oid, 'DELETE')
1397 OR has_table_privilege(c.oid, 'RULE')
1398 OR has_table_privilege(c.oid, 'REFERENCES')
1399 OR has_table_privilege(c.oid, 'TRIGGER') );
1401 GRANT SELECT ON tables TO PUBLIC;
1406 * TRIGGERED_UPDATE_COLUMNS view
1409 -- PostgreSQL doesn't allow the specification of individual triggered
1410 -- update columns, so this view is empty.
1412 CREATE VIEW triggered_update_columns AS
1413 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1414 CAST(null AS sql_identifier) AS trigger_schema,
1415 CAST(null AS sql_identifier) AS trigger_name,
1416 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1417 CAST(null AS sql_identifier) AS event_object_schema,
1418 CAST(null AS sql_identifier) AS event_object_table,
1419 CAST(null AS sql_identifier) AS event_object_column
1422 GRANT SELECT ON triggered_update_columns TO PUBLIC;
1430 CREATE VIEW triggers AS
1431 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1432 CAST(n.nspname AS sql_identifier) AS trigger_schema,
1433 CAST(t.tgname AS sql_identifier) AS trigger_name,
1434 CAST(em.text AS character_data) AS event_manipulation,
1435 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1436 CAST(n.nspname AS sql_identifier) AS event_object_schema,
1437 CAST(c.relname AS sql_identifier) AS event_object_table,
1438 CAST(null AS cardinal_number) AS action_order,
1439 CAST(null AS character_data) AS action_condition,
1441 substring(pg_get_triggerdef(t.oid) from
1442 position('EXECUTE PROCEDURE' in substring(pg_get_triggerdef(t.oid) from 48)) + 47)
1443 AS character_data) AS action_statement,
1445 CASE WHEN t.tgtype & 1 = 1 THEN 'ROW' ELSE 'STATEMENT' END
1446 AS character_data) AS action_orientation,
1448 CASE WHEN t.tgtype & 2 = 2 THEN 'BEFORE' ELSE 'AFTER' END
1449 AS character_data) AS condition_timing,
1450 CAST(null AS sql_identifier) AS condition_reference_old_table,
1451 CAST(null AS sql_identifier) AS condition_reference_new_table
1453 FROM pg_namespace n, pg_class c, pg_trigger t,
1454 (SELECT 4, 'INSERT' UNION ALL
1455 SELECT 8, 'DELETE' UNION ALL
1456 SELECT 16, 'UPDATE') AS em (num, text)
1458 WHERE n.oid = c.relnamespace
1459 AND c.oid = t.tgrelid
1460 AND t.tgtype & em.num <> 0
1461 AND NOT t.tgisconstraint
1462 AND pg_has_role(c.relowner, 'MEMBER');
1464 GRANT SELECT ON triggers TO PUBLIC;
1469 * USAGE_PRIVILEGES view
1472 -- Of the things currently implemented in PostgreSQL, usage privileges
1473 -- apply only to domains. Since domains have no real privileges, we
1474 -- represent all domains with implicit usage privilege here.
1476 CREATE VIEW usage_privileges AS
1477 SELECT CAST(u.rolname AS sql_identifier) AS grantor,
1478 CAST('PUBLIC' AS sql_identifier) AS grantee,
1479 CAST(current_database() AS sql_identifier) AS object_catalog,
1480 CAST(n.nspname AS sql_identifier) AS object_schema,
1481 CAST(t.typname AS sql_identifier) AS object_name,
1482 CAST('DOMAIN' AS character_data) AS object_type,
1483 CAST('USAGE' AS character_data) AS privilege_type,
1484 CAST('NO' AS character_data) AS is_grantable
1490 WHERE u.oid = t.typowner
1491 AND t.typnamespace = n.oid
1492 AND t.typtype = 'd';
1494 GRANT SELECT ON usage_privileges TO PUBLIC;
1502 CREATE VIEW view_column_usage AS
1504 CAST(current_database() AS sql_identifier) AS view_catalog,
1505 CAST(nv.nspname AS sql_identifier) AS view_schema,
1506 CAST(v.relname AS sql_identifier) AS view_name,
1507 CAST(current_database() AS sql_identifier) AS table_catalog,
1508 CAST(nt.nspname AS sql_identifier) AS table_schema,
1509 CAST(t.relname AS sql_identifier) AS table_name,
1510 CAST(a.attname AS sql_identifier) AS column_name
1512 FROM pg_namespace nv, pg_class v, pg_depend dv,
1513 pg_depend dt, pg_class t, pg_namespace nt,
1516 WHERE nv.oid = v.relnamespace
1518 AND v.oid = dv.refobjid
1519 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
1520 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
1521 AND dv.deptype = 'i'
1522 AND dv.objid = dt.objid
1523 AND dv.refobjid <> dt.refobjid
1524 AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
1525 AND dt.refclassid = 'pg_catalog.pg_class'::regclass
1526 AND dt.refobjid = t.oid
1527 AND t.relnamespace = nt.oid
1528 AND t.relkind IN ('r', 'v')
1529 AND t.oid = a.attrelid
1530 AND dt.refobjsubid = a.attnum
1531 AND pg_has_role(t.relowner, 'MEMBER');
1533 GRANT SELECT ON view_column_usage TO PUBLIC;
1541 CREATE VIEW view_table_usage AS
1543 CAST(current_database() AS sql_identifier) AS view_catalog,
1544 CAST(nv.nspname AS sql_identifier) AS view_schema,
1545 CAST(v.relname AS sql_identifier) AS view_name,
1546 CAST(current_database() AS sql_identifier) AS table_catalog,
1547 CAST(nt.nspname AS sql_identifier) AS table_schema,
1548 CAST(t.relname AS sql_identifier) AS table_name
1550 FROM pg_namespace nv, pg_class v, pg_depend dv,
1551 pg_depend dt, pg_class t, pg_namespace nt
1553 WHERE nv.oid = v.relnamespace
1555 AND v.oid = dv.refobjid
1556 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
1557 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
1558 AND dv.deptype = 'i'
1559 AND dv.objid = dt.objid
1560 AND dv.refobjid <> dt.refobjid
1561 AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
1562 AND dt.refclassid = 'pg_catalog.pg_class'::regclass
1563 AND dt.refobjid = t.oid
1564 AND t.relnamespace = nt.oid
1565 AND t.relkind IN ('r', 'v')
1566 AND pg_has_role(t.relowner, 'MEMBER');
1568 GRANT SELECT ON view_table_usage TO PUBLIC;
1576 CREATE VIEW views AS
1577 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
1578 CAST(nc.nspname AS sql_identifier) AS table_schema,
1579 CAST(c.relname AS sql_identifier) AS table_name,
1582 CASE WHEN pg_has_role(c.relowner, 'MEMBER')
1583 THEN pg_get_viewdef(c.oid)
1585 AS character_data) AS view_definition,
1587 CAST('NONE' AS character_data) AS check_option,
1588 CAST(null AS character_data) AS is_updatable, -- FIXME
1589 CAST(null AS character_data) AS is_insertable_into -- FIXME
1591 FROM pg_namespace nc, pg_class c
1593 WHERE c.relnamespace = nc.oid
1595 AND (pg_has_role(c.relowner, 'MEMBER')
1596 OR has_table_privilege(c.oid, 'SELECT')
1597 OR has_table_privilege(c.oid, 'INSERT')
1598 OR has_table_privilege(c.oid, 'UPDATE')
1599 OR has_table_privilege(c.oid, 'DELETE')
1600 OR has_table_privilege(c.oid, 'RULE')
1601 OR has_table_privilege(c.oid, 'REFERENCES')
1602 OR has_table_privilege(c.oid, 'TRIGGER') );
1604 GRANT SELECT ON views TO PUBLIC;
1607 -- The following views have dependencies that force them to appear out of order.
1611 * DATA_TYPE_PRIVILEGES view
1614 CREATE VIEW data_type_privileges AS
1615 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
1616 CAST(x.objschema AS sql_identifier) AS object_schema,
1617 CAST(x.objname AS sql_identifier) AS object_name,
1618 CAST(x.objtype AS character_data) AS object_type,
1619 CAST(x.objdtdid AS sql_identifier) AS dtd_identifier
1623 SELECT table_schema, table_name, 'TABLE'::text, dtd_identifier FROM columns
1625 SELECT domain_schema, domain_name, 'DOMAIN'::text, dtd_identifier FROM domains
1627 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM parameters
1629 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM routines
1630 ) AS x (objschema, objname, objtype, objdtdid);
1632 GRANT SELECT ON data_type_privileges TO PUBLIC;
1637 * ELEMENT_TYPES view
1640 CREATE VIEW element_types AS
1641 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
1642 CAST(n.nspname AS sql_identifier) AS object_schema,
1643 CAST(x.objname AS sql_identifier) AS object_name,
1644 CAST(x.objtype AS character_data) AS object_type,
1645 CAST(x.objdtdid AS sql_identifier) AS array_type_identifier,
1647 CASE WHEN nbt.nspname = 'pg_catalog' THEN format_type(bt.oid, null)
1648 ELSE 'USER-DEFINED' END AS character_data) AS data_type,
1650 CAST(null AS cardinal_number) AS character_maximum_length,
1651 CAST(null AS cardinal_number) AS character_octet_length,
1652 CAST(null AS sql_identifier) AS character_set_catalog,
1653 CAST(null AS sql_identifier) AS character_set_schema,
1654 CAST(null AS sql_identifier) AS character_set_name,
1655 CAST(null AS sql_identifier) AS collation_catalog,
1656 CAST(null AS sql_identifier) AS collation_schema,
1657 CAST(null AS sql_identifier) AS collation_name,
1658 CAST(null AS cardinal_number) AS numeric_precision,
1659 CAST(null AS cardinal_number) AS numeric_precision_radix,
1660 CAST(null AS cardinal_number) AS numeric_scale,
1661 CAST(null AS cardinal_number) AS datetime_precision,
1662 CAST(null AS character_data) AS interval_type,
1663 CAST(null AS character_data) AS interval_precision,
1665 CAST(null AS character_data) AS domain_default, -- XXX maybe a bug in the standard
1667 CAST(current_database() AS sql_identifier) AS udt_catalog,
1668 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
1669 CAST(bt.typname AS sql_identifier) AS udt_name,
1671 CAST(null AS sql_identifier) AS scope_catalog,
1672 CAST(null AS sql_identifier) AS scope_schema,
1673 CAST(null AS sql_identifier) AS scope_name,
1675 CAST(null AS cardinal_number) AS maximum_cardinality,
1676 CAST('a' || x.objdtdid AS sql_identifier) AS dtd_identifier
1678 FROM pg_namespace n, pg_type at, pg_namespace nbt, pg_type bt,
1681 SELECT c.relnamespace, CAST(c.relname AS sql_identifier),
1682 'TABLE'::text, a.attnum, a.atttypid
1683 FROM pg_class c, pg_attribute a
1684 WHERE c.oid = a.attrelid
1685 AND c.relkind IN ('r', 'v')
1686 AND attnum > 0 AND NOT attisdropped
1691 SELECT t.typnamespace, CAST(t.typname AS sql_identifier),
1692 'DOMAIN'::text, 1, t.typbasetype
1694 WHERE t.typtype = 'd'
1699 SELECT pronamespace, CAST(proname || '_' || CAST(oid AS text) AS sql_identifier),
1700 'ROUTINE'::text, (ss.x).n, (ss.x).x
1701 FROM (SELECT p.pronamespace, p.proname, p.oid,
1702 _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
1703 FROM pg_proc p) AS ss
1708 SELECT p.pronamespace, CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier),
1709 'ROUTINE'::text, 0, p.prorettype
1712 ) AS x (objschema, objname, objtype, objdtdid, objtypeid)
1714 WHERE n.oid = x.objschema
1715 AND at.oid = x.objtypeid
1716 AND (at.typelem <> 0 AND at.typlen = -1)
1717 AND at.typelem = bt.oid
1718 AND nbt.oid = bt.typnamespace
1720 AND (n.nspname, x.objname, x.objtype, x.objdtdid) IN
1721 ( SELECT object_schema, object_name, object_type, dtd_identifier
1722 FROM data_type_privileges );
1724 GRANT SELECT ON element_types TO PUBLIC;