2 * SQL Information Schema
3 * as defined in ISO 9075-2:1999 chapter 20
5 * Copyright 2003, PostgreSQL Global Development Group
7 * $Id: information_schema.sql,v 1.15 2003/10/18 19:06:10 tgl 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 -- 20.3 INFORMATION_SCHEMA_CATALOG_NAME view appears later.
35 * CARDINAL_NUMBER domain
38 CREATE DOMAIN cardinal_number AS integer
39 CONSTRAINT cardinal_number_domain_check CHECK (value >= 0);
44 * CHARACTER_DATA domain
47 CREATE DOMAIN character_data AS character varying;
52 * SQL_IDENTIFIER domain
55 CREATE DOMAIN sql_identifier AS character varying;
60 * INFORMATION_SCHEMA_CATALOG_NAME view
63 CREATE VIEW information_schema_catalog_name AS
64 SELECT CAST(current_database() AS sql_identifier) AS catalog_name;
66 GRANT SELECT ON information_schema_catalog_name TO PUBLIC;
74 CREATE DOMAIN time_stamp AS timestamp(2)
75 DEFAULT current_timestamp(2);
80 * APPLICABLE_ROLES view
83 CREATE VIEW applicable_roles AS
84 SELECT CAST(current_user AS sql_identifier) AS grantee,
85 CAST(g.groname AS sql_identifier) AS role_name,
86 CAST('NO' AS character_data) AS is_grantable
88 FROM pg_group g, pg_user u
90 WHERE u.usesysid = ANY (g.grolist)
91 AND u.usename = current_user;
93 GRANT SELECT ON applicable_roles TO PUBLIC;
98 * CHECK_CONSTRAINTS view
101 CREATE VIEW check_constraints AS
102 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
103 CAST(rs.nspname AS sql_identifier) AS constraint_schema,
104 CAST(con.conname AS sql_identifier) AS constraint_name,
105 CAST(substring(pg_get_constraintdef(con.oid) from 7) AS character_data)
107 FROM pg_namespace rs,
109 LEFT OUTER JOIN pg_class c ON (c.oid = con.conrelid)
110 LEFT OUTER JOIN pg_type t ON (t.oid = con.contypid),
112 WHERE rs.oid = con.connamespace
113 AND u.usesysid = coalesce(c.relowner, t.typowner)
114 AND u.usename = current_user
115 AND con.contype = 'c'
118 GRANT SELECT ON check_constraints TO PUBLIC;
123 * COLUMN_DOMAIN_USAGE view
126 CREATE VIEW column_domain_usage AS
127 SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
128 CAST(nt.nspname AS sql_identifier) AS domain_schema,
129 CAST(t.typname AS sql_identifier) AS domain_name,
130 CAST(current_database() AS sql_identifier) AS table_catalog,
131 CAST(nc.nspname AS sql_identifier) AS table_schema,
132 CAST(c.relname AS sql_identifier) AS table_name,
133 CAST(a.attname AS sql_identifier) AS column_name
135 FROM pg_type t, pg_namespace nt, pg_class c, pg_namespace nc,
136 pg_attribute a, pg_user u
138 WHERE t.typnamespace = nt.oid
139 AND c.relnamespace = nc.oid
140 AND a.attrelid = c.oid
141 AND a.atttypid = t.oid
142 AND t.typowner = u.usesysid
144 AND c.relkind IN ('r', 'v')
146 AND NOT a.attisdropped
147 AND u.usename = current_user;
149 GRANT SELECT ON column_domain_usage TO PUBLIC;
157 CREATE VIEW column_privileges AS
158 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
159 CAST(grantee.name AS sql_identifier) AS grantee,
160 CAST(current_database() AS sql_identifier) AS table_catalog,
161 CAST(nc.nspname AS sql_identifier) AS table_schema,
162 CAST(c.relname AS sql_identifier) AS table_name,
163 CAST(a.attname AS sql_identifier) AS column_name,
164 CAST(pr.type AS character_data) AS privilege_type,
166 CASE WHEN aclcontains(c.relacl,
167 makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr.type, true))
168 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
175 SELECT usesysid, 0, usename FROM pg_user
177 SELECT 0, grosysid, groname FROM pg_group
179 SELECT 0, 0, 'PUBLIC'
180 ) AS grantee (usesysid, grosysid, name),
181 (SELECT 'SELECT' UNION ALL
182 SELECT 'INSERT' UNION ALL
183 SELECT 'UPDATE' UNION ALL
184 SELECT 'REFERENCES') AS pr (type)
186 WHERE a.attrelid = c.oid
187 AND c.relnamespace = nc.oid
189 AND NOT a.attisdropped
190 AND c.relkind IN ('r', 'v')
191 AND aclcontains(c.relacl,
192 makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr.type, false))
193 AND (u_grantor.usename = current_user
194 OR grantee.name = current_user
195 OR grantee.name = 'PUBLIC');
197 GRANT SELECT ON column_privileges TO PUBLIC;
202 * COLUMN_UDT_USAGE view
205 CREATE VIEW column_udt_usage AS
206 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
207 CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,
208 CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,
209 CAST(current_database() AS sql_identifier) AS table_catalog,
210 CAST(nc.nspname AS sql_identifier) AS table_schema,
211 CAST(c.relname AS sql_identifier) AS table_name,
212 CAST(a.attname AS sql_identifier) AS column_name
214 FROM pg_attribute a, pg_class c, pg_namespace nc, pg_user u,
215 (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
216 LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
217 ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
219 WHERE a.attrelid = c.oid
220 AND a.atttypid = t.oid
221 AND u.usesysid = coalesce(bt.typowner, t.typowner)
222 AND nc.oid = c.relnamespace
223 AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
224 AND u.usename = current_user;
226 GRANT SELECT ON column_udt_usage TO PUBLIC;
234 CREATE VIEW columns AS
235 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
236 CAST(nc.nspname AS sql_identifier) AS table_schema,
237 CAST(c.relname AS sql_identifier) AS table_name,
238 CAST(a.attname AS sql_identifier) AS column_name,
239 CAST(a.attnum AS cardinal_number) AS ordinal_position,
241 CASE WHEN u.usename = current_user THEN a.adsrc ELSE null END
244 CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
249 CASE WHEN t.typtype = 'd' THEN
250 CASE WHEN bt.typelem <> 0 AND bt.typlen = -1 THEN 'ARRAY'
251 WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
252 ELSE 'USER-DEFINED' END
254 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
255 WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, null)
256 ELSE 'USER-DEFINED' END
262 CASE WHEN t.typtype = 'd' THEN
263 CASE WHEN t.typbasetype IN (25, 1042, 1043, 1560, 1562) AND t.typtypmod <> -1
267 CASE WHEN a.atttypid IN (25, 1042, 1043, 1560, 1562) AND a.atttypmod <> -1
272 AS character_maximum_length,
275 CASE WHEN t.typtype = 'd' THEN
276 CASE WHEN t.typbasetype IN (25, 1042, 1043) THEN 2^30 ELSE null END
278 CASE WHEN a.atttypid IN (25, 1042, 1043) THEN 2^30 ELSE null END
281 AS character_octet_length,
284 CASE (CASE WHEN t.typtype = 'd' THEN t.typbasetype ELSE a.atttypid END)
285 WHEN 21 /*int2*/ THEN 16
286 WHEN 23 /*int4*/ THEN 32
287 WHEN 20 /*int8*/ THEN 64
288 WHEN 1700 /*numeric*/ THEN ((CASE WHEN t.typtype = 'd' THEN t.typtypmod ELSE a.atttypmod END - 4) >> 16) & 65535
289 WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/
290 WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/
293 AS numeric_precision,
296 CASE WHEN t.typtype = 'd' THEN
297 CASE WHEN t.typbasetype IN (21, 23, 20, 700, 701) THEN 2
298 WHEN t.typbasetype IN (1700) THEN 10
301 CASE WHEN a.atttypid IN (21, 23, 20, 700, 701) THEN 2
302 WHEN a.atttypid IN (1700) THEN 10
306 AS numeric_precision_radix,
309 CASE WHEN t.typtype = 'd' THEN
310 CASE WHEN t.typbasetype IN (21, 23, 20) THEN 0
311 WHEN t.typbasetype IN (1700) THEN (t.typtypmod - 4) & 65535
314 CASE WHEN a.atttypid IN (21, 23, 20) THEN 0
315 WHEN a.atttypid IN (1700) THEN (a.atttypmod - 4) & 65535
322 CASE WHEN t.typtype = 'd' THEN
323 CASE WHEN t.typbasetype IN (1083, 1114, 1184, 1266)
324 THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod ELSE null END)
325 WHEN t.typbasetype IN (1186)
326 THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod & 65535 ELSE null END)
329 CASE WHEN a.atttypid IN (1083, 1114, 1184, 1266)
330 THEN (CASE WHEN a.atttypmod <> -1 THEN a.atttypmod ELSE null END)
331 WHEN a.atttypid IN (1186)
332 THEN (CASE WHEN a.atttypmod <> -1 THEN a.atttypmod & 65535 ELSE null END)
336 AS datetime_precision,
338 CAST(null AS character_data) AS interval_type, -- XXX
339 CAST(null AS character_data) AS interval_precision, -- XXX
341 CAST(null AS sql_identifier) AS character_set_catalog,
342 CAST(null AS sql_identifier) AS character_set_schema,
343 CAST(null AS sql_identifier) AS character_set_name,
345 CAST(null AS sql_identifier) AS collation_catalog,
346 CAST(null AS sql_identifier) AS collation_schema,
347 CAST(null AS sql_identifier) AS collation_name,
349 CAST(CASE WHEN t.typtype = 'd' THEN current_database() ELSE null END
350 AS sql_identifier) AS domain_catalog,
351 CAST(CASE WHEN t.typtype = 'd' THEN nt.nspname ELSE null END
352 AS sql_identifier) AS domain_schema,
353 CAST(CASE WHEN t.typtype = 'd' THEN t.typname ELSE null END
354 AS sql_identifier) AS domain_name,
356 CAST(current_database() AS sql_identifier) AS udt_catalog,
357 CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,
358 CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,
360 CAST(null AS sql_identifier) AS scope_catalog,
361 CAST(null AS sql_identifier) AS scope_schema,
362 CAST(null AS sql_identifier) AS scope_name,
364 CAST(null AS cardinal_number) AS maximum_cardinality,
365 CAST(a.attnum AS sql_identifier) AS dtd_identifier,
366 CAST('NO' AS character_data) AS is_self_referencing
368 FROM (pg_attribute LEFT JOIN pg_attrdef ON attrelid = adrelid AND attnum = adnum) AS a,
369 pg_class c, pg_namespace nc, pg_user u,
370 (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
371 LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
372 ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
374 WHERE a.attrelid = c.oid
375 AND a.atttypid = t.oid
376 AND u.usesysid = c.relowner
377 AND nc.oid = c.relnamespace
379 AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
381 AND (u.usename = current_user
382 OR has_table_privilege(c.oid, 'SELECT')
383 OR has_table_privilege(c.oid, 'INSERT')
384 OR has_table_privilege(c.oid, 'UPDATE')
385 OR has_table_privilege(c.oid, 'RERERENCES') );
387 GRANT SELECT ON columns TO PUBLIC;
392 * CONSTRAINT_COLUMN_USAGE view
395 /* This returns the integers from 1 to INDEX_MAX_KEYS/FUNC_MAX_ARGS */
396 CREATE FUNCTION _pg_keypositions() RETURNS SETOF integer
399 AS 'select 1 union all select 2 union all select 3 union all
400 select 4 union all select 5 union all select 6 union all
401 select 7 union all select 8 union all select 9 union all
402 select 10 union all select 11 union all select 12 union all
403 select 13 union all select 14 union all select 15 union all
404 select 16 union all select 17 union all select 18 union all
405 select 19 union all select 20 union all select 21 union all
406 select 22 union all select 23 union all select 24 union all
407 select 25 union all select 26 union all select 27 union all
408 select 28 union all select 29 union all select 30 union all
409 select 31 union all select 32';
411 CREATE VIEW constraint_column_usage AS
412 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
413 CAST(tblschema AS sql_identifier) AS table_schema,
414 CAST(tblname AS sql_identifier) AS table_name,
415 CAST(colname AS sql_identifier) AS column_name,
416 CAST(current_database() AS sql_identifier) AS constraint_catalog,
417 CAST(cstrschema AS sql_identifier) AS constraint_schema,
418 CAST(cstrname AS sql_identifier) AS constraint_name
421 /* check constraints */
422 SELECT DISTINCT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
423 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_depend d, pg_namespace nc, pg_constraint c
424 WHERE nr.oid = r.relnamespace
425 AND r.oid = a.attrelid
426 AND d.refclassid = 'pg_catalog.pg_class'::regclass
427 AND d.refobjid = r.oid
428 AND d.refobjsubid = a.attnum
429 AND d.classid = 'pg_catalog.pg_constraint'::regclass
431 AND c.connamespace = nc.oid
435 AND NOT a.attisdropped
439 /* unique/primary key/foreign key constraints */
440 SELECT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
441 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc,
442 pg_constraint c, _pg_keypositions() AS pos(n)
443 WHERE nr.oid = r.relnamespace
444 AND r.oid = a.attrelid
445 AND r.oid = c.conrelid
446 AND nc.oid = c.connamespace
447 AND (CASE WHEN c.contype = 'f' THEN c.confkey[pos.n] = a.attnum
448 ELSE c.conkey[pos.n] = a.attnum END)
450 AND NOT a.attisdropped
451 AND c.contype IN ('p', 'u', 'f')
454 ) AS x (tblschema, tblname, tblowner, colname, cstrschema, cstrname),
457 WHERE x.tblowner = u.usesysid AND u.usename = current_user;
459 GRANT SELECT ON constraint_column_usage TO PUBLIC;
464 * CONSTRAINT_TABLE_USAGE view
467 CREATE VIEW constraint_table_usage AS
468 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
469 CAST(nr.nspname AS sql_identifier) AS table_schema,
470 CAST(r.relname AS sql_identifier) AS table_name,
471 CAST(current_database() AS sql_identifier) AS constraint_catalog,
472 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
473 CAST(c.conname AS sql_identifier) AS constraint_name
475 FROM pg_constraint c, pg_namespace nc,
476 pg_class r, pg_namespace nr,
479 WHERE c.connamespace = nc.oid AND r.relnamespace = nr.oid
480 AND ( (c.contype = 'f' AND c.confrelid = r.oid)
481 OR (c.contype IN ('p', 'u') AND c.conrelid = r.oid) )
483 AND r.relowner = u.usesysid AND u.usename = current_user;
485 GRANT SELECT ON constraint_table_usage TO PUBLIC;
488 -- 20.21 DATA_TYPE_PRIVILEGES view appears later.
493 * DOMAIN_CONSTRAINTS view
496 CREATE VIEW domain_constraints AS
497 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
498 CAST(rs.nspname AS sql_identifier) AS constraint_schema,
499 CAST(con.conname AS sql_identifier) AS constraint_name,
500 CAST(current_database() AS sql_identifier) AS domain_catalog,
501 CAST(n.nspname AS sql_identifier) AS domain_schema,
502 CAST(t.typname AS sql_identifier) AS domain_name,
503 CAST(CASE WHEN condeferrable THEN 'YES' ELSE 'NO' END
504 AS character_data) AS is_deferrable,
505 CAST(CASE WHEN condeferred THEN 'YES' ELSE 'NO' END
506 AS character_data) AS initially_deferred
507 FROM pg_namespace rs, pg_namespace n, pg_constraint con, pg_type t, pg_user u
508 WHERE rs.oid = con.connamespace
509 AND n.oid = t.typnamespace
510 AND u.usesysid = t.typowner
511 AND u.usename = current_user
512 AND t.oid = con.contypid;
514 GRANT SELECT ON domain_constraints TO PUBLIC;
519 * DOMAIN_UDT_USAGE view
522 CREATE VIEW domain_udt_usage AS
523 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
524 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
525 CAST(bt.typname AS sql_identifier) AS udt_name,
526 CAST(current_database() AS sql_identifier) AS domain_catalog,
527 CAST(nt.nspname AS sql_identifier) AS domain_schema,
528 CAST(t.typname AS sql_identifier) AS domain_name
530 FROM pg_type t, pg_namespace nt,
531 pg_type bt, pg_namespace nbt,
534 WHERE t.typnamespace = nt.oid
535 AND t.typbasetype = bt.oid
536 AND bt.typnamespace = nbt.oid
538 AND bt.typowner = u.usesysid
539 AND u.usename = current_user;
541 GRANT SELECT ON domain_udt_usage TO PUBLIC;
549 CREATE VIEW domains AS
550 SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
551 CAST(nt.nspname AS sql_identifier) AS domain_schema,
552 CAST(t.typname AS sql_identifier) AS domain_name,
555 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
556 WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
557 ELSE 'USER-DEFINED' END
562 CASE WHEN t.typbasetype IN (25, 1042, 1043, 1560, 1562) AND t.typtypmod <> -1
566 AS character_maximum_length,
569 CASE WHEN t.typbasetype IN (25, 1042, 1043) THEN 2^30 ELSE null END
571 AS character_octet_length,
573 CAST(null AS sql_identifier) AS character_set_catalog,
574 CAST(null AS sql_identifier) AS character_set_schema,
575 CAST(null AS sql_identifier) AS character_set_name,
577 CAST(null AS sql_identifier) AS collation_catalog,
578 CAST(null AS sql_identifier) AS collation_schema,
579 CAST(null AS sql_identifier) AS collation_name,
583 WHEN 21 /*int2*/ THEN 16
584 WHEN 23 /*int4*/ THEN 32
585 WHEN 20 /*int8*/ THEN 64
586 WHEN 1700 /*numeric*/ THEN ((t.typtypmod - 4) >> 16) & 65535
587 WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/
588 WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/
591 AS numeric_precision,
594 CASE WHEN t.typbasetype IN (21, 23, 20, 700, 701) THEN 2
595 WHEN t.typbasetype IN (1700) THEN 10
598 AS numeric_precision_radix,
601 CASE WHEN t.typbasetype IN (21, 23, 20) THEN 0
602 WHEN t.typbasetype IN (1700) THEN (t.typtypmod - 4) & 65535
608 CASE WHEN t.typbasetype IN (1083, 1114, 1184, 1266)
609 THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod ELSE null END)
610 WHEN t.typbasetype IN (1186)
611 THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod & 65535 ELSE null END)
614 AS datetime_precision,
616 CAST(null AS character_data) AS interval_type, -- XXX
617 CAST(null AS character_data) AS interval_precision, -- XXX
619 CAST(t.typdefault AS character_data) AS domain_default,
621 CAST(current_database() AS sql_identifier) AS udt_catalog,
622 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
623 CAST(bt.typname AS sql_identifier) AS udt_name,
625 CAST(null AS sql_identifier) AS scope_catalog,
626 CAST(null AS sql_identifier) AS scope_schema,
627 CAST(null AS sql_identifier) AS scope_name,
629 CAST(null AS cardinal_number) AS maximum_cardinality,
630 CAST(1 AS sql_identifier) AS dtd_identifier
632 FROM pg_type t, pg_namespace nt,
633 pg_type bt, pg_namespace nbt
635 WHERE t.typnamespace = nt.oid
636 AND t.typbasetype = bt.oid
637 AND bt.typnamespace = nbt.oid
640 GRANT SELECT ON domains TO PUBLIC;
643 -- 20.27 ELEMENT_TYPES view appears later.
651 CREATE VIEW enabled_roles AS
652 SELECT CAST(g.groname AS sql_identifier) AS role_name
653 FROM pg_group g, pg_user u
654 WHERE u.usesysid = ANY (g.grolist)
655 AND u.usename = current_user;
657 GRANT SELECT ON enabled_roles TO PUBLIC;
662 * KEY_COLUMN_USAGE view
665 CREATE VIEW key_column_usage AS
666 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
667 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
668 CAST(c.conname AS sql_identifier) AS constraint_name,
669 CAST(current_database() AS sql_identifier) AS table_catalog,
670 CAST(nr.nspname AS sql_identifier) AS table_schema,
671 CAST(r.relname AS sql_identifier) AS table_name,
672 CAST(a.attname AS sql_identifier) AS column_name,
673 CAST(pos.n AS cardinal_number) AS ordinal_position
675 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc,
676 pg_constraint c, pg_user u, _pg_keypositions() AS pos(n)
677 WHERE nr.oid = r.relnamespace
678 AND r.oid = a.attrelid
679 AND r.oid = c.conrelid
680 AND nc.oid = c.connamespace
681 AND c.conkey[pos.n] = a.attnum
683 AND NOT a.attisdropped
684 AND c.contype IN ('p', 'u', 'f')
686 AND r.relowner = u.usesysid
687 AND u.usename = current_user;
689 GRANT SELECT ON key_column_usage TO PUBLIC;
697 CREATE VIEW parameters AS
698 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
699 CAST(n.nspname AS sql_identifier) AS specific_schema,
700 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
701 CAST(pos.n AS cardinal_number) AS ordinal_position,
702 CAST('IN' AS character_data) AS parameter_mode,
703 CAST('NO' AS character_data) AS is_result,
704 CAST('NO' AS character_data) AS as_locator,
705 CAST(null AS sql_identifier) AS parameter_name,
707 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
708 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
709 ELSE 'USER-DEFINED' END AS character_data)
711 CAST(null AS cardinal_number) AS character_maximum_length,
712 CAST(null AS cardinal_number) AS character_octet_length,
713 CAST(null AS sql_identifier) AS character_set_catalog,
714 CAST(null AS sql_identifier) AS character_set_schema,
715 CAST(null AS sql_identifier) AS character_set_name,
716 CAST(null AS sql_identifier) AS collation_catalog,
717 CAST(null AS sql_identifier) AS collation_schema,
718 CAST(null AS sql_identifier) AS collation_name,
719 CAST(null AS cardinal_number) AS numeric_precision,
720 CAST(null AS cardinal_number) AS numeric_precision_radix,
721 CAST(null AS cardinal_number) AS numeric_scale,
722 CAST(null AS cardinal_number) AS datetime_precision,
723 CAST(null AS character_data) AS interval_type,
724 CAST(null AS character_data) AS interval_precision,
725 CAST(current_database() AS sql_identifier) AS udt_catalog,
726 CAST(nt.nspname AS sql_identifier) AS udt_schema,
727 CAST(t.typname AS sql_identifier) AS udt_name,
728 CAST(null AS sql_identifier) AS scope_catalog,
729 CAST(null AS sql_identifier) AS scope_schema,
730 CAST(null AS sql_identifier) AS scope_name,
731 CAST(null AS cardinal_number) AS maximum_cardinality,
732 CAST(pos.n AS sql_identifier) AS dtd_identifier
734 FROM pg_namespace n, pg_proc p, pg_type t, pg_namespace nt, pg_user u,
735 _pg_keypositions() AS pos(n)
737 WHERE n.oid = p.pronamespace AND p.pronargs >= pos.n
738 AND p.proargtypes[pos.n-1] = t.oid AND t.typnamespace = nt.oid
739 AND p.proowner = u.usesysid
740 AND (u.usename = current_user OR has_function_privilege(p.oid, 'EXECUTE'));
742 GRANT SELECT ON parameters TO PUBLIC;
747 * REFERENTIAL_CONSTRAINTS view
750 CREATE FUNCTION _pg_keyissubset(smallint[], smallint[]) RETURNS boolean
753 RETURNS NULL ON NULL INPUT
754 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))';
756 CREATE FUNCTION _pg_keysequal(smallint[], smallint[]) RETURNS boolean
759 RETURNS NULL ON NULL INPUT
760 AS 'select information_schema._pg_keyissubset($1, $2) and information_schema._pg_keyissubset($2, $1)';
762 CREATE VIEW referential_constraints AS
763 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
764 CAST(ncon.nspname AS sql_identifier) AS constraint_schema,
765 CAST(con.conname AS sql_identifier) AS constraint_name,
767 CASE WHEN npkc.nspname IS NULL THEN NULL
768 ELSE current_database() END
769 AS sql_identifier) AS unique_constraint_catalog,
770 CAST(npkc.nspname AS sql_identifier) AS unique_constraint_schema,
771 CAST(pkc.conname AS sql_identifier) AS unique_constraint_name,
774 CASE con.confmatchtype WHEN 'f' THEN 'FULL'
775 WHEN 'p' THEN 'PARTIAL'
776 WHEN 'u' THEN 'NONE' END
777 AS character_data) AS match_option,
780 CASE con.confupdtype WHEN 'c' THEN 'CASCADE'
781 WHEN 'n' THEN 'SET NULL'
782 WHEN 'd' THEN 'SET DEFAULT'
783 WHEN 'r' THEN 'RESTRICT'
784 WHEN 'a' THEN 'NO ACTION' END
785 AS character_data) AS update_rule,
788 CASE con.confdeltype WHEN 'c' THEN 'CASCADE'
789 WHEN 'n' THEN 'SET NULL'
790 WHEN 'd' THEN 'SET DEFAULT'
791 WHEN 'r' THEN 'RESTRICT'
792 WHEN 'a' THEN 'NO ACTION' END
793 AS character_data) AS delete_rule
795 FROM (pg_namespace ncon INNER JOIN pg_constraint con ON ncon.oid = con.connamespace
796 INNER JOIN pg_class c ON con.conrelid = c.oid
797 INNER JOIN pg_user u ON c.relowner = u.usesysid)
799 (pg_constraint pkc INNER JOIN pg_namespace npkc ON pkc.connamespace = npkc.oid)
800 ON con.confrelid = pkc.conrelid AND _pg_keysequal(con.confkey, pkc.conkey)
802 WHERE c.relkind = 'r'
803 AND con.contype = 'f'
804 AND (pkc.contype IN ('p', 'u') OR pkc.contype IS NULL)
805 AND u.usename = current_user;
807 GRANT SELECT ON referential_constraints TO PUBLIC;
812 * ROLE_COLUMN_GRANTS view
815 CREATE VIEW role_column_grants AS
816 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
817 CAST(g_grantee.groname AS sql_identifier) AS grantee,
818 CAST(current_database() AS sql_identifier) AS table_catalog,
819 CAST(nc.nspname AS sql_identifier) AS table_schema,
820 CAST(c.relname AS sql_identifier) AS table_name,
821 CAST(a.attname AS sql_identifier) AS column_name,
822 CAST(pr.type AS character_data) AS privilege_type,
824 CASE WHEN aclcontains(c.relacl,
825 makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, pr.type, true))
826 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
833 (SELECT 'SELECT' UNION ALL
834 SELECT 'INSERT' UNION ALL
835 SELECT 'UPDATE' UNION ALL
836 SELECT 'REFERENCES') AS pr (type)
838 WHERE a.attrelid = c.oid
839 AND c.relnamespace = nc.oid
841 AND NOT a.attisdropped
842 AND c.relkind IN ('r', 'v')
843 AND aclcontains(c.relacl,
844 makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, pr.type, false))
845 AND g_grantee.groname IN (SELECT role_name FROM enabled_roles);
847 GRANT SELECT ON role_column_grants TO PUBLIC;
852 * ROLE_ROUTINE_GRANTS view
855 CREATE VIEW role_routine_grants AS
856 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
857 CAST(g_grantee.groname AS sql_identifier) AS grantee,
858 CAST(current_database() AS sql_identifier) AS specific_catalog,
859 CAST(n.nspname AS sql_identifier) AS specific_schema,
860 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
861 CAST(current_database() AS sql_identifier) AS routine_catalog,
862 CAST(n.nspname AS sql_identifier) AS routine_schema,
863 CAST(p.proname AS sql_identifier) AS routine_name,
864 CAST('EXECUTE' AS character_data) AS privilege_type,
866 CASE WHEN aclcontains(p.proacl,
867 makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, 'EXECUTE', true))
868 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
875 WHERE p.pronamespace = n.oid
876 AND aclcontains(p.proacl,
877 makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, 'EXECUTE', false))
878 AND g_grantee.groname IN (SELECT role_name FROM enabled_roles);
880 GRANT SELECT ON role_routine_grants TO PUBLIC;
885 * ROLE_TABLE_GRANTS view
888 CREATE VIEW role_table_grants AS
889 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
890 CAST(g_grantee.groname AS sql_identifier) AS grantee,
891 CAST(current_database() AS sql_identifier) AS table_catalog,
892 CAST(nc.nspname AS sql_identifier) AS table_schema,
893 CAST(c.relname AS sql_identifier) AS table_name,
894 CAST(pr.type AS character_data) AS privilege_type,
896 CASE WHEN aclcontains(c.relacl,
897 makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, pr.type, true))
898 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable,
899 CAST('NO' AS character_data) AS with_hierarchy
905 (SELECT 'SELECT' UNION ALL
906 SELECT 'DELETE' UNION ALL
907 SELECT 'INSERT' UNION ALL
908 SELECT 'UPDATE' UNION ALL
909 SELECT 'REFERENCES' UNION ALL
910 SELECT 'RULE' UNION ALL
911 SELECT 'TRIGGER') AS pr (type)
913 WHERE c.relnamespace = nc.oid
914 AND c.relkind IN ('r', 'v')
915 AND aclcontains(c.relacl,
916 makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, pr.type, false))
917 AND g_grantee.groname IN (SELECT role_name FROM enabled_roles);
919 GRANT SELECT ON role_table_grants TO PUBLIC;
924 * ROLE_USAGE_GRANTS view
927 -- See USAGE_PRIVILEGES.
929 CREATE VIEW role_usage_grants AS
930 SELECT CAST(null AS sql_identifier) AS grantor,
931 CAST(null AS sql_identifier) AS grantee,
932 CAST(current_database() AS sql_identifier) AS object_catalog,
933 CAST(null AS sql_identifier) AS object_schema,
934 CAST(null AS sql_identifier) AS object_name,
935 CAST(null AS character_data) AS object_type,
936 CAST('USAGE' AS character_data) AS privilege_type,
937 CAST(null AS character_data) AS is_grantable
941 GRANT SELECT ON role_usage_grants TO PUBLIC;
946 * ROUTINE_PRIVILEGES view
949 CREATE VIEW routine_privileges AS
950 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
951 CAST(grantee.name AS sql_identifier) AS grantee,
952 CAST(current_database() AS sql_identifier) AS specific_catalog,
953 CAST(n.nspname AS sql_identifier) AS specific_schema,
954 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
955 CAST(current_database() AS sql_identifier) AS routine_catalog,
956 CAST(n.nspname AS sql_identifier) AS routine_schema,
957 CAST(p.proname AS sql_identifier) AS routine_name,
958 CAST('EXECUTE' AS character_data) AS privilege_type,
960 CASE WHEN aclcontains(p.proacl,
961 makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, 'EXECUTE', true))
962 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
968 SELECT usesysid, 0, usename FROM pg_user
970 SELECT 0, grosysid, groname FROM pg_group
972 SELECT 0, 0, 'PUBLIC'
973 ) AS grantee (usesysid, grosysid, name)
975 WHERE p.pronamespace = n.oid
976 AND aclcontains(p.proacl,
977 makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, 'EXECUTE', false))
978 AND (u_grantor.usename = current_user
979 OR grantee.name = current_user
980 OR grantee.name = 'PUBLIC');
982 GRANT SELECT ON routine_privileges TO PUBLIC;
990 CREATE VIEW routines AS
991 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
992 CAST(n.nspname AS sql_identifier) AS specific_schema,
993 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
994 CAST(current_database() AS sql_identifier) AS routine_catalog,
995 CAST(n.nspname AS sql_identifier) AS routine_schema,
996 CAST(p.proname AS sql_identifier) AS routine_name,
997 CAST('FUNCTION' AS character_data) AS routine_type,
998 CAST(null AS sql_identifier) AS module_catalog,
999 CAST(null AS sql_identifier) AS module_schema,
1000 CAST(null AS sql_identifier) AS module_name,
1001 CAST(null AS sql_identifier) AS udt_catalog,
1002 CAST(null AS sql_identifier) AS udt_schema,
1003 CAST(null AS sql_identifier) AS udt_name,
1006 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
1007 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
1008 ELSE 'USER-DEFINED' END AS character_data)
1010 CAST(null AS cardinal_number) AS character_maximum_length,
1011 CAST(null AS cardinal_number) AS character_octet_length,
1012 CAST(null AS sql_identifier) AS character_set_catalog,
1013 CAST(null AS sql_identifier) AS character_set_schema,
1014 CAST(null AS sql_identifier) AS character_set_name,
1015 CAST(null AS sql_identifier) AS collation_catalog,
1016 CAST(null AS sql_identifier) AS collation_schema,
1017 CAST(null AS sql_identifier) AS collation_name,
1018 CAST(null AS cardinal_number) AS numeric_precision,
1019 CAST(null AS cardinal_number) AS numeric_precision_radix,
1020 CAST(null AS cardinal_number) AS numeric_scale,
1021 CAST(null AS cardinal_number) AS datetime_precision,
1022 CAST(null AS character_data) AS interval_type,
1023 CAST(null AS character_data) AS interval_precision,
1024 CAST(current_database() AS sql_identifier) AS type_udt_catalog,
1025 CAST(nt.nspname AS sql_identifier) AS type_udt_schema,
1026 CAST(t.typname AS sql_identifier) AS type_udt_name,
1027 CAST(null AS sql_identifier) AS scope_catalog,
1028 CAST(null AS sql_identifier) AS scope_schema,
1029 CAST(null AS sql_identifier) AS scope_name,
1030 CAST(null AS cardinal_number) AS maximum_cardinality,
1031 CAST(0 AS sql_identifier) AS dtd_identifier,
1033 CAST(CASE WHEN l.lanname = 'sql' THEN 'SQL' ELSE 'EXTERNAL' END AS character_data)
1036 CASE WHEN u.usename = current_user THEN p.prosrc ELSE null END
1037 AS character_data) AS routine_definition,
1039 CASE WHEN l.lanname = 'c' THEN p.prosrc ELSE null END
1040 AS character_data) AS external_name,
1041 CAST(upper(l.lanname) AS character_data) AS external_language,
1043 CAST('GENERAL' AS character_data) AS parameter_style,
1044 CAST(CASE WHEN p.provolatile = 'i' THEN 'YES' ELSE 'NO' END AS character_data) AS is_deterministic,
1045 CAST('MODIFIES' AS character_data) AS sql_data_access,
1046 CAST(CASE WHEN p.proisstrict THEN 'YES' ELSE 'NO' END AS character_data) AS is_null_call,
1047 CAST(null AS character_data) AS sql_path,
1048 CAST('YES' AS character_data) AS schema_level_routine,
1049 CAST(0 AS cardinal_number) AS max_dynamic_result_sets,
1050 CAST(null AS character_data) AS is_user_defined_cast,
1051 CAST(null AS character_data) AS is_implicitly_invocable,
1052 CAST(CASE WHEN p.prosecdef THEN 'DEFINER' ELSE 'INVOKER' END AS character_data) AS security_type,
1053 CAST(null AS sql_identifier) AS to_sql_specific_catalog,
1054 CAST(null AS sql_identifier) AS to_sql_specific_schema,
1055 CAST(null AS sql_identifier) AS to_sql_specific_name,
1056 CAST('NO' AS character_data) AS as_locator
1058 FROM pg_namespace n, pg_proc p, pg_language l, pg_user u,
1059 pg_type t, pg_namespace nt
1061 WHERE n.oid = p.pronamespace AND p.prolang = l.oid AND p.proowner = u.usesysid
1062 AND p.prorettype = t.oid AND t.typnamespace = nt.oid
1063 AND (u.usename = current_user OR has_function_privilege(p.oid, 'EXECUTE'));
1065 GRANT SELECT ON routines TO PUBLIC;
1073 CREATE VIEW schemata AS
1074 SELECT CAST(current_database() AS sql_identifier) AS catalog_name,
1075 CAST(n.nspname AS sql_identifier) AS schema_name,
1076 CAST(u.usename AS sql_identifier) AS schema_owner,
1077 CAST(null AS sql_identifier) AS default_character_set_catalog,
1078 CAST(null AS sql_identifier) AS default_character_set_schema,
1079 CAST(null AS sql_identifier) AS default_character_set_name,
1080 CAST(null AS character_data) AS sql_path
1081 FROM pg_namespace n, pg_user u
1082 WHERE n.nspowner = u.usesysid AND u.usename = current_user;
1084 GRANT SELECT ON schemata TO PUBLIC;
1089 * SQL_FEATURES table
1092 CREATE TABLE sql_features (
1093 feature_id character_data,
1094 feature_name character_data,
1095 sub_feature_id character_data,
1096 sub_feature_name character_data,
1097 is_supported character_data,
1098 is_verified_by character_data,
1099 comments character_data
1102 -- Will be filled with external data by initdb.
1104 GRANT SELECT ON sql_features TO PUBLIC;
1109 * SQL_IMPLEMENTATION_INFO table
1112 -- Note: Implementation information items are defined in ISO 9075-3:1999,
1115 CREATE TABLE sql_implementation_info (
1116 implementation_info_id character_data,
1117 implementation_info_name character_data,
1118 integer_value cardinal_number,
1119 character_value character_data,
1120 comments character_data
1123 INSERT INTO sql_implementation_info VALUES ('10003', 'CATALOG NAME', NULL, 'Y', NULL);
1124 INSERT INTO sql_implementation_info VALUES ('10004', 'COLLATING SEQUENCE', NULL, '', 'not supported');
1125 INSERT INTO sql_implementation_info VALUES ('23', 'CURSOR COMMIT BEHAVIOR', 1, NULL, 'close cursors and retain prepared statements');
1126 INSERT INTO sql_implementation_info VALUES ('2', 'DATA SOURCE NAME', NULL, '', NULL);
1127 INSERT INTO sql_implementation_info VALUES ('17', 'DBMS NAME', NULL, (select trim(trailing ' ' from substring(version() from '^[^0-9]*'))), NULL);
1128 INSERT INTO sql_implementation_info VALUES ('18', 'DBMS VERSION', NULL, '???', NULL); -- filled by initdb
1129 INSERT INTO sql_implementation_info VALUES ('26', 'DEFAULT TRANSACTION ISOLATION', 2, NULL, 'READ COMMITTED; user-settable');
1130 INSERT INTO sql_implementation_info VALUES ('28', 'IDENTIFIER CASE', 3, NULL, 'stored in mixed case - case sensitive');
1131 INSERT INTO sql_implementation_info VALUES ('85', 'NULL COLLATION', 0, NULL, 'nulls higher than non-nulls');
1132 INSERT INTO sql_implementation_info VALUES ('13', 'SERVER NAME', NULL, '', NULL);
1133 INSERT INTO sql_implementation_info VALUES ('94', 'SPECIAL CHARACTERS', NULL, '', 'all non-ASCII characters allowed');
1134 INSERT INTO sql_implementation_info VALUES ('46', 'TRANSACTION CAPABLE', 2, NULL, 'both DML and DDL');
1136 GRANT SELECT ON sql_implementation_info TO PUBLIC;
1141 * SQL_LANGUAGES table
1144 CREATE TABLE sql_languages (
1145 sql_language_source character_data,
1146 sql_language_year character_data,
1147 sql_language_conformance character_data,
1148 sql_language_integrity character_data,
1149 sql_language_implementation character_data,
1150 sql_language_binding_style character_data,
1151 sql_language_programming_language character_data
1154 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'DIRECT', NULL);
1155 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
1157 GRANT SELECT ON sql_languages TO PUBLIC;
1162 * SQL_PACKAGES table
1165 CREATE TABLE sql_packages (
1166 feature_id character_data,
1167 feature_name character_data,
1168 is_supported character_data,
1169 is_verified_by character_data,
1170 comments character_data
1173 INSERT INTO sql_packages VALUES ('PKG000', 'Core', 'NO', NULL, '');
1174 INSERT INTO sql_packages VALUES ('PKG001', 'Enhanced datetime facilities', 'YES', NULL, '');
1175 INSERT INTO sql_packages VALUES ('PKG002', 'Enhanced integrity management', 'NO', NULL, '');
1176 INSERT INTO sql_packages VALUES ('PKG003', 'OLAP facilities', 'NO', NULL, '');
1177 INSERT INTO sql_packages VALUES ('PKG004', 'PSM', 'NO', NULL, 'PL/pgSQL is similar.');
1178 INSERT INTO sql_packages VALUES ('PKG005', 'CLI', 'NO', NULL, 'ODBC is similar.');
1179 INSERT INTO sql_packages VALUES ('PKG006', 'Basic object support', 'NO', NULL, '');
1180 INSERT INTO sql_packages VALUES ('PKG007', 'Enhanced object support', 'NO', NULL, '');
1181 INSERT INTO sql_packages VALUES ('PKG008', 'Active database', 'NO', NULL, '');
1182 INSERT INTO sql_packages VALUES ('PKG009', 'SQL/MM support', 'NO', NULL, '');
1184 GRANT SELECT ON sql_packages TO PUBLIC;
1192 -- Note: Sizing items are defined in ISO 9075-3:1999, clause 7.2.
1194 CREATE TABLE sql_sizing (
1195 sizing_id cardinal_number,
1196 sizing_name character_data,
1197 supported_value cardinal_number,
1198 comments character_data
1201 INSERT INTO sql_sizing VALUES (34, 'MAXIMUM CATALOG NAME LENGTH', 63, NULL);
1202 INSERT INTO sql_sizing VALUES (30, 'MAXIMUM COLUMN NAME LENGTH', 63, NULL);
1203 INSERT INTO sql_sizing VALUES (97, 'MAXIMUM COLUMNS IN GROUP BY', 0, NULL);
1204 INSERT INTO sql_sizing VALUES (99, 'MAXIMUM COLUMNS IN ORDER BY', 0, NULL);
1205 INSERT INTO sql_sizing VALUES (100, 'MAXIMUM COLUMNS IN SELECT', 1664, NULL); -- match MaxTupleAttributeNumber
1206 INSERT INTO sql_sizing VALUES (101, 'MAXIMUM COLUMNS IN TABLE', 1600, NULL); -- match MaxHeapAttributeNumber
1207 INSERT INTO sql_sizing VALUES (1, 'MAXIMUM CONCURRENT ACTIVITIES', 0, NULL);
1208 INSERT INTO sql_sizing VALUES (31, 'MAXIMUM CURSOR NAME LENGTH', 63, NULL);
1209 INSERT INTO sql_sizing VALUES (0, 'MAXIMUM DRIVER CONNECTIONS', NULL, NULL);
1210 INSERT INTO sql_sizing VALUES (10005, 'MAXIMUM IDENTIFIER LENGTH', 63, NULL);
1211 INSERT INTO sql_sizing VALUES (32, 'MAXIMUM SCHEMA NAME LENGTH', 63, NULL);
1212 INSERT INTO sql_sizing VALUES (20000, 'MAXIMUM STATEMENT OCTETS', 0, NULL);
1213 INSERT INTO sql_sizing VALUES (20001, 'MAXIMUM STATEMENT OCTETS DATA', 0, NULL);
1214 INSERT INTO sql_sizing VALUES (20002, 'MAXIMUM STATEMENT OCTETS SCHEMA', 0, NULL);
1215 INSERT INTO sql_sizing VALUES (35, 'MAXIMUM TABLE NAME LENGTH', 63, NULL);
1216 INSERT INTO sql_sizing VALUES (106, 'MAXIMUM TABLES IN SELECT', 0, NULL);
1217 INSERT INTO sql_sizing VALUES (107, 'MAXIMUM USER NAME LENGTH', 63, NULL);
1218 INSERT INTO sql_sizing VALUES (25000, 'MAXIMUM CURRENT DEFAULT TRANSFORM GROUP LENGTH', NULL, NULL);
1219 INSERT INTO sql_sizing VALUES (25001, 'MAXIMUM CURRENT TRANSFORM GROUP LENGTH', NULL, NULL);
1220 INSERT INTO sql_sizing VALUES (25002, 'MAXIMUM CURRENT PATH LENGTH', 0, NULL);
1221 INSERT INTO sql_sizing VALUES (25003, 'MAXIMUM CURRENT ROLE LENGTH', NULL, NULL);
1222 INSERT INTO sql_sizing VALUES (25004, 'MAXIMUM SESSION USER LENGTH', 63, NULL);
1223 INSERT INTO sql_sizing VALUES (25005, 'MAXIMUM SYSTEM USER LENGTH', 63, NULL);
1226 SET supported_value = (SELECT typlen-1 FROM pg_catalog.pg_type WHERE typname = 'name'),
1227 comments = 'Might be less, depending on character set.'
1228 WHERE supported_value = 63;
1230 GRANT SELECT ON sql_sizing TO PUBLIC;
1235 * SQL_SIZING_PROFILES table
1238 -- The data in this table are defined by various profiles of SQL.
1239 -- Since we don't have any information about such profiles, we provide
1242 CREATE TABLE sql_sizing_profiles (
1243 sizing_id cardinal_number,
1244 sizing_name character_data,
1245 profile_id character_data,
1246 required_value cardinal_number,
1247 comments character_data
1250 GRANT SELECT ON sql_sizing_profiles TO PUBLIC;
1255 * TABLE_CONSTRAINTS view
1258 CREATE VIEW table_constraints AS
1259 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1260 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
1261 CAST(c.conname AS sql_identifier) AS constraint_name,
1262 CAST(current_database() AS sql_identifier) AS table_catalog,
1263 CAST(nr.nspname AS sql_identifier) AS table_schema,
1264 CAST(r.relname AS sql_identifier) AS table_name,
1266 CASE c.contype WHEN 'c' THEN 'CHECK'
1267 WHEN 'f' THEN 'FOREIGN KEY'
1268 WHEN 'p' THEN 'PRIMARY KEY'
1269 WHEN 'u' THEN 'UNIQUE' END
1270 AS character_data) AS constraint_type,
1271 CAST(CASE WHEN c.condeferrable THEN 'YES' ELSE 'NO' END AS character_data)
1273 CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS character_data)
1274 AS initially_deferred
1276 FROM pg_namespace nc,
1282 WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace
1283 AND c.conrelid = r.oid AND r.relowner = u.usesysid
1285 AND u.usename = current_user;
1287 -- FIMXE: Not-null constraints are missing here.
1289 GRANT SELECT ON table_constraints TO PUBLIC;
1294 * TABLE_PRIVILEGES view
1297 CREATE VIEW table_privileges AS
1298 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
1299 CAST(grantee.name AS sql_identifier) AS grantee,
1300 CAST(current_database() AS sql_identifier) AS table_catalog,
1301 CAST(nc.nspname AS sql_identifier) AS table_schema,
1302 CAST(c.relname AS sql_identifier) AS table_name,
1303 CAST(pr.type AS character_data) AS privilege_type,
1305 CASE WHEN aclcontains(c.relacl,
1306 makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr.type, true))
1307 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable,
1308 CAST('NO' AS character_data) AS with_hierarchy
1314 SELECT usesysid, 0, usename FROM pg_user
1316 SELECT 0, grosysid, groname FROM pg_group
1318 SELECT 0, 0, 'PUBLIC'
1319 ) AS grantee (usesysid, grosysid, name),
1320 (SELECT 'SELECT' UNION ALL
1321 SELECT 'DELETE' UNION ALL
1322 SELECT 'INSERT' UNION ALL
1323 SELECT 'UPDATE' UNION ALL
1324 SELECT 'REFERENCES' UNION ALL
1325 SELECT 'RULE' UNION ALL
1326 SELECT 'TRIGGER') AS pr (type)
1328 WHERE c.relnamespace = nc.oid
1329 AND c.relkind IN ('r', 'v')
1330 AND aclcontains(c.relacl,
1331 makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr.type, false))
1332 AND (u_grantor.usename = current_user
1333 OR grantee.name = current_user
1334 OR grantee.name = 'PUBLIC');
1336 GRANT SELECT ON table_privileges TO PUBLIC;
1344 CREATE VIEW tables AS
1345 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
1346 CAST(nc.nspname AS sql_identifier) AS table_schema,
1347 CAST(c.relname AS sql_identifier) AS table_name,
1350 CASE WHEN nc.nspname LIKE 'pg!_temp!_%' ESCAPE '!' THEN 'LOCAL TEMPORARY'
1351 WHEN c.relkind = 'r' THEN 'BASE TABLE'
1352 WHEN c.relkind = 'v' THEN 'VIEW'
1354 AS character_data) AS table_type,
1356 CAST(null AS sql_identifier) AS self_referencing_column_name,
1357 CAST(null AS character_data) AS reference_generation,
1359 CAST(null AS sql_identifier) AS user_defined_type_catalog,
1360 CAST(null AS sql_identifier) AS user_defined_type_schema,
1361 CAST(null AS sql_identifier) AS user_defined_name
1363 FROM pg_namespace nc, pg_class c, pg_user u
1365 WHERE c.relnamespace = nc.oid AND u.usesysid = c.relowner
1366 AND c.relkind IN ('r', 'v')
1367 AND (u.usename = current_user
1368 OR has_table_privilege(c.oid, 'SELECT')
1369 OR has_table_privilege(c.oid, 'INSERT')
1370 OR has_table_privilege(c.oid, 'UPDATE')
1371 OR has_table_privilege(c.oid, 'DELETE')
1372 OR has_table_privilege(c.oid, 'RULE')
1373 OR has_table_privilege(c.oid, 'RERERENCES')
1374 OR has_table_privilege(c.oid, 'TRIGGER') );
1376 GRANT SELECT ON tables TO PUBLIC;
1381 * TRIGGERED_UPDATE_COLUMNS view
1384 -- PostgreSQL doesn't allow the specification of individual triggered
1385 -- update columns, so this view is empty.
1387 CREATE VIEW triggered_update_columns AS
1388 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1389 CAST(null AS sql_identifier) AS trigger_schema,
1390 CAST(null AS sql_identifier) AS trigger_name,
1391 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1392 CAST(null AS sql_identifier) AS event_object_schema,
1393 CAST(null AS sql_identifier) AS event_object_table,
1394 CAST(null AS sql_identifier) AS event_object_column
1397 GRANT SELECT ON triggered_update_columns TO PUBLIC;
1405 CREATE VIEW triggers AS
1406 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1407 CAST(n.nspname AS sql_identifier) AS trigger_schema,
1408 CAST(t.tgname AS sql_identifier) AS trigger_name,
1409 CAST(em.text AS character_data) AS event_manipulation,
1410 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1411 CAST(n.nspname AS sql_identifier) AS event_object_schema,
1412 CAST(c.relname AS sql_identifier) AS event_object_table,
1413 CAST(null AS cardinal_number) AS action_order,
1414 CAST(null AS character_data) AS action_condition,
1416 substring(pg_get_triggerdef(t.oid) from
1417 position('EXECUTE PROCEDURE' in substring(pg_get_triggerdef(t.oid) from 48)) + 47)
1418 AS character_data) AS action_statement,
1420 CASE WHEN t.tgtype & 1 = 1 THEN 'ROW' ELSE 'STATEMENT' END
1421 AS character_data) AS action_orientation,
1423 CASE WHEN t.tgtype & 2 = 2 THEN 'BEFORE' ELSE 'AFTER' END
1424 AS character_data) AS condition_timing,
1425 CAST(null AS sql_identifier) AS condition_reference_old_table,
1426 CAST(null AS sql_identifier) AS condition_reference_new_table
1428 FROM pg_namespace n, pg_class c, pg_trigger t, pg_user u,
1429 (SELECT 4, 'INSERT' UNION ALL
1430 SELECT 8, 'DELETE' UNION ALL
1431 SELECT 16, 'UPDATE') AS em (num, text)
1433 WHERE n.oid = c.relnamespace
1434 AND c.oid = t.tgrelid
1435 AND c.relowner = u.usesysid
1436 AND t.tgtype & em.num <> 0
1437 AND NOT t.tgisconstraint
1438 AND u.usename = current_user;
1440 GRANT SELECT ON triggers TO PUBLIC;
1445 * USAGE_PRIVILEGES view
1448 -- Of the things currently implemented in PostgreSQL, usage privileges
1449 -- apply only to domains. Since domains have no real privileges, we
1450 -- represent all domains with implicit usage privilege here.
1452 CREATE VIEW usage_privileges AS
1453 SELECT CAST(u.usename AS sql_identifier) AS grantor,
1454 CAST('PUBLIC' AS sql_identifier) AS grantee,
1455 CAST(current_database() AS sql_identifier) AS object_catalog,
1456 CAST(n.nspname AS sql_identifier) AS object_schema,
1457 CAST(t.typname AS sql_identifier) AS object_name,
1458 CAST('DOMAIN' AS character_data) AS object_type,
1459 CAST('USAGE' AS character_data) AS privilege_type,
1460 CAST('NO' AS character_data) AS is_grantable
1466 WHERE u.usesysid = t.typowner
1467 AND t.typnamespace = n.oid
1468 AND t.typtype = 'd';
1470 GRANT SELECT ON usage_privileges TO PUBLIC;
1478 CREATE VIEW view_column_usage AS
1480 CAST(current_database() AS sql_identifier) AS view_catalog,
1481 CAST(nv.nspname AS sql_identifier) AS view_schema,
1482 CAST(v.relname AS sql_identifier) AS view_name,
1483 CAST(current_database() AS sql_identifier) AS table_catalog,
1484 CAST(nt.nspname AS sql_identifier) AS table_schema,
1485 CAST(t.relname AS sql_identifier) AS table_name,
1486 CAST(a.attname AS sql_identifier) AS column_name
1488 FROM pg_user, pg_namespace nv, pg_class v, pg_depend dv,
1489 pg_depend dt, pg_class t, pg_namespace nt,
1490 pg_attribute a, pg_user u
1492 WHERE nv.oid = v.relnamespace
1494 AND v.oid = dv.refobjid
1495 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
1496 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
1497 AND dv.deptype = 'i'
1498 AND dv.objid = dt.objid
1499 AND dv.refobjid <> dt.refobjid
1500 AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
1501 AND dt.refclassid = 'pg_catalog.pg_class'::regclass
1502 AND dt.refobjid = t.oid
1503 AND t.relnamespace = nt.oid
1504 AND t.relkind IN ('r', 'v')
1505 AND t.oid = a.attrelid
1506 AND dt.refobjsubid = a.attnum
1507 AND t.relowner = u.usesysid AND u.usename = current_user;
1509 GRANT SELECT ON view_column_usage TO PUBLIC;
1517 CREATE VIEW view_table_usage AS
1519 CAST(current_database() AS sql_identifier) AS view_catalog,
1520 CAST(nv.nspname AS sql_identifier) AS view_schema,
1521 CAST(v.relname AS sql_identifier) AS view_name,
1522 CAST(current_database() AS sql_identifier) AS table_catalog,
1523 CAST(nt.nspname AS sql_identifier) AS table_schema,
1524 CAST(t.relname AS sql_identifier) AS table_name
1526 FROM pg_user, pg_namespace nv, pg_class v, pg_depend dv,
1527 pg_depend dt, pg_class t, pg_namespace nt,
1530 WHERE nv.oid = v.relnamespace
1532 AND v.oid = dv.refobjid
1533 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
1534 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
1535 AND dv.deptype = 'i'
1536 AND dv.objid = dt.objid
1537 AND dv.refobjid <> dt.refobjid
1538 AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
1539 AND dt.refclassid = 'pg_catalog.pg_class'::regclass
1540 AND dt.refobjid = t.oid
1541 AND t.relnamespace = nt.oid
1542 AND t.relkind IN ('r', 'v')
1543 AND t.relowner = u.usesysid AND u.usename = current_user;
1545 GRANT SELECT ON view_table_usage TO PUBLIC;
1553 CREATE VIEW views AS
1554 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
1555 CAST(nc.nspname AS sql_identifier) AS table_schema,
1556 CAST(c.relname AS sql_identifier) AS table_name,
1559 CASE WHEN u.usename = current_user THEN pg_get_viewdef(c.oid)
1561 AS character_data) AS view_definition,
1563 CAST('NONE' AS character_data) AS check_option,
1564 CAST(null AS character_data) AS is_updatable, -- FIXME
1565 CAST(null AS character_data) AS is_insertable_into -- FIXME
1567 FROM pg_namespace nc, pg_class c, pg_user u
1569 WHERE c.relnamespace = nc.oid AND u.usesysid = c.relowner
1571 AND (u.usename = current_user
1572 OR has_table_privilege(c.oid, 'SELECT')
1573 OR has_table_privilege(c.oid, 'INSERT')
1574 OR has_table_privilege(c.oid, 'UPDATE')
1575 OR has_table_privilege(c.oid, 'DELETE')
1576 OR has_table_privilege(c.oid, 'RULE')
1577 OR has_table_privilege(c.oid, 'RERERENCES')
1578 OR has_table_privilege(c.oid, 'TRIGGER') );
1580 GRANT SELECT ON views TO PUBLIC;
1583 -- The following views have dependencies that force them to appear out of order.
1587 * DATA_TYPE_PRIVILEGES view
1590 CREATE VIEW data_type_privileges AS
1591 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
1592 CAST(x.objschema AS sql_identifier) AS object_schema,
1593 CAST(x.objname AS sql_identifier) AS object_name,
1594 CAST(x.objtype AS character_data) AS object_type,
1595 CAST(x.objdtdid AS sql_identifier) AS dtd_identifier
1599 SELECT table_schema, table_name, 'TABLE'::text, dtd_identifier FROM columns
1601 SELECT domain_schema, domain_name, 'DOMAIN'::text, dtd_identifier FROM domains
1603 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM parameters
1605 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM routines
1606 ) AS x (objschema, objname, objtype, objdtdid);
1608 GRANT SELECT ON data_type_privileges TO PUBLIC;
1613 * ELEMENT_TYPES view
1616 CREATE VIEW element_types AS
1617 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
1618 CAST(n.nspname AS sql_identifier) AS object_schema,
1619 CAST(x.objname AS sql_identifier) AS object_name,
1620 CAST(x.objtype AS character_data) AS object_type,
1621 CAST(x.objdtdid AS sql_identifier) AS array_type_identifier,
1623 CASE WHEN nbt.nspname = 'pg_catalog' THEN format_type(bt.oid, null)
1624 ELSE 'USER-DEFINED' END AS character_data) AS data_type,
1626 CAST(null AS cardinal_number) AS character_maximum_length,
1627 CAST(null AS cardinal_number) AS character_octet_length,
1628 CAST(null AS sql_identifier) AS character_set_catalog,
1629 CAST(null AS sql_identifier) AS character_set_schema,
1630 CAST(null AS sql_identifier) AS character_set_name,
1631 CAST(null AS sql_identifier) AS collation_catalog,
1632 CAST(null AS sql_identifier) AS collation_schema,
1633 CAST(null AS sql_identifier) AS collation_name,
1634 CAST(null AS cardinal_number) AS numeric_precision,
1635 CAST(null AS cardinal_number) AS numeric_precision_radix,
1636 CAST(null AS cardinal_number) AS numeric_scale,
1637 CAST(null AS cardinal_number) AS datetime_precision,
1638 CAST(null AS character_data) AS interval_type,
1639 CAST(null AS character_data) AS interval_precision,
1641 CAST(null AS character_data) AS domain_default, -- XXX maybe a bug in the standard
1643 CAST(current_database() AS sql_identifier) AS udt_catalog,
1644 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
1645 CAST(bt.typname AS sql_identifier) AS udt_name,
1647 CAST(null AS sql_identifier) AS scope_catalog,
1648 CAST(null AS sql_identifier) AS scope_schema,
1649 CAST(null AS sql_identifier) AS scope_name,
1651 CAST(null AS cardinal_number) AS maximum_cardinality,
1652 CAST('a' || x.objdtdid AS sql_identifier) AS dtd_identifier
1654 FROM pg_namespace n, pg_type at, pg_namespace nbt, pg_type bt,
1657 SELECT c.relnamespace, CAST(c.relname AS sql_identifier),
1658 'TABLE'::text, a.attnum, a.atttypid
1659 FROM pg_class c, pg_attribute a
1660 WHERE c.oid = a.attrelid
1661 AND c.relkind IN ('r', 'v')
1662 AND attnum > 0 AND NOT attisdropped
1667 SELECT t.typnamespace, CAST(t.typname AS sql_identifier),
1668 'DOMAIN'::text, 1, t.typbasetype
1670 WHERE t.typtype = 'd'
1675 SELECT p.pronamespace, CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier),
1676 'ROUTINE'::text, pos.n, p.proargtypes[pos.n-1]
1677 FROM pg_proc p, _pg_keypositions() AS pos(n)
1678 WHERE p.pronargs >= pos.n
1683 SELECT p.pronamespace, CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier),
1684 'ROUTINE'::text, 0, p.prorettype
1687 ) AS x (objschema, objname, objtype, objdtdid, objtypeid)
1689 WHERE n.oid = x.objschema
1690 AND at.oid = x.objtypeid
1691 AND (at.typelem <> 0 AND at.typlen = -1)
1692 AND at.typelem = bt.oid
1693 AND nbt.oid = bt.typnamespace
1695 AND (n.nspname, x.objname, x.objtype, x.objdtdid) IN
1696 ( SELECT object_schema, object_name, object_type, dtd_identifier
1697 FROM data_type_privileges );
1699 GRANT SELECT ON element_types TO PUBLIC;