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.12 2003/06/29 15:14:41 petere Exp $
11 * Note: Generally, the definitions in this file should be ordered
12 * according to the clause numbers in the SQL standard, which is also the
13 * alphabetical order. In some cases it is convenient or necessary to
14 * define one information schema view by using another one; in that case,
15 * put the referencing view at the very end and leave a note where it
16 * should have been put.
22 * INFORMATION_SCHEMA schema
25 CREATE SCHEMA information_schema;
26 GRANT USAGE ON SCHEMA information_schema TO PUBLIC;
27 SET search_path TO information_schema, public;
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(con.consrc AS character_data) AS check_clause
106 FROM pg_namespace rs,
108 LEFT OUTER JOIN pg_class c ON (c.oid = con.conrelid)
109 LEFT OUTER JOIN pg_type t ON (t.oid = con.contypid),
111 WHERE rs.oid = con.connamespace
112 AND u.usesysid = coalesce(c.relowner, t.typowner)
113 AND u.usename = current_user
114 AND con.contype = 'c'
117 GRANT SELECT ON check_constraints TO PUBLIC;
122 * COLUMN_DOMAIN_USAGE view
125 CREATE VIEW column_domain_usage AS
126 SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
127 CAST(nt.nspname AS sql_identifier) AS domain_schema,
128 CAST(t.typname AS sql_identifier) AS domain_name,
129 CAST(current_database() AS sql_identifier) AS table_catalog,
130 CAST(nc.nspname AS sql_identifier) AS table_schema,
131 CAST(c.relname AS sql_identifier) AS table_name,
132 CAST(a.attname AS sql_identifier) AS column_name
134 FROM pg_type t, pg_namespace nt, pg_class c, pg_namespace nc,
135 pg_attribute a, pg_user u
137 WHERE t.typnamespace = nt.oid
138 AND c.relnamespace = nc.oid
139 AND a.attrelid = c.oid
140 AND a.atttypid = t.oid
141 AND t.typowner = u.usesysid
143 AND c.relkind IN ('r', 'v')
145 AND NOT a.attisdropped
146 AND u.usename = current_user;
148 GRANT SELECT ON column_domain_usage TO PUBLIC;
156 CREATE VIEW column_privileges AS
157 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
158 CAST(grantee.name AS sql_identifier) AS grantee,
159 CAST(current_database() AS sql_identifier) AS table_catalog,
160 CAST(nc.nspname AS sql_identifier) AS table_schema,
161 CAST(c.relname AS sql_identifier) AS table_name,
162 CAST(a.attname AS sql_identifier) AS column_name,
163 CAST(pr.type AS character_data) AS privilege_type,
165 CASE WHEN aclcontains(c.relacl,
166 makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr.type, true))
167 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
174 SELECT usesysid, 0, usename FROM pg_user
176 SELECT 0, grosysid, groname FROM pg_group
178 SELECT 0, 0, 'PUBLIC'
179 ) AS grantee (usesysid, grosysid, name),
180 (SELECT 'SELECT' UNION SELECT 'INSERT' UNION SELECT 'UPDATE' UNION SELECT 'REFERENCES') AS pr (type)
182 WHERE a.attrelid = c.oid
183 AND c.relnamespace = nc.oid
185 AND NOT a.attisdropped
186 AND c.relkind IN ('r', 'v')
187 AND aclcontains(c.relacl,
188 makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr.type, false))
189 AND (u_grantor.usename = current_user
190 OR grantee.name = current_user
191 OR grantee.name = 'PUBLIC');
193 GRANT SELECT ON column_privileges TO PUBLIC;
198 * COLUMN_UDT_USAGE view
201 CREATE VIEW column_udt_usage AS
202 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
203 CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,
204 CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,
205 CAST(current_database() AS sql_identifier) AS table_catalog,
206 CAST(nc.nspname AS sql_identifier) AS table_schema,
207 CAST(c.relname AS sql_identifier) AS table_name,
208 CAST(a.attname AS sql_identifier) AS column_name
210 FROM pg_attribute a, pg_class c, pg_namespace nc, pg_user u,
211 (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
212 LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
213 ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
215 WHERE a.attrelid = c.oid
216 AND a.atttypid = t.oid
217 AND u.usesysid = coalesce(bt.typowner, t.typowner)
218 AND nc.oid = c.relnamespace
219 AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
220 AND u.usename = current_user;
222 GRANT SELECT ON column_udt_usage TO PUBLIC;
230 CREATE VIEW columns AS
231 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
232 CAST(nc.nspname AS sql_identifier) AS table_schema,
233 CAST(c.relname AS sql_identifier) AS table_name,
234 CAST(a.attname AS sql_identifier) AS column_name,
235 CAST(a.attnum AS cardinal_number) AS ordinal_position,
237 CASE WHEN u.usename = current_user THEN a.adsrc ELSE null END
240 CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
245 CASE WHEN t.typtype = 'd' THEN
246 CASE WHEN bt.typelem <> 0 AND bt.typlen = -1 THEN 'ARRAY'
247 WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
248 ELSE 'USER-DEFINED' END
250 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
251 WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, null)
252 ELSE 'USER-DEFINED' END
258 CASE WHEN t.typtype = 'd' THEN
259 CASE WHEN t.typbasetype IN (25, 1042, 1043, 1560, 1562) AND t.typtypmod <> -1
263 CASE WHEN a.atttypid IN (25, 1042, 1043, 1560, 1562) AND a.atttypmod <> -1
268 AS character_maximum_length,
271 CASE WHEN t.typtype = 'd' THEN
272 CASE WHEN t.typbasetype IN (25, 1042, 1043) THEN 2^30 ELSE null END
274 CASE WHEN a.atttypid IN (25, 1042, 1043) THEN 2^30 ELSE null END
277 AS character_octet_length,
280 CASE (CASE WHEN t.typtype = 'd' THEN t.typbasetype ELSE a.atttypid END)
281 WHEN 21 /*int2*/ THEN 16
282 WHEN 23 /*int4*/ THEN 32
283 WHEN 20 /*int8*/ THEN 64
284 WHEN 1700 /*numeric*/ THEN ((CASE WHEN t.typtype = 'd' THEN t.typtypmod ELSE a.atttypmod END - 4) >> 16) & 65535
285 WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/
286 WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/
289 AS numeric_precision,
292 CASE WHEN t.typtype = 'd' THEN
293 CASE WHEN t.typbasetype IN (21, 23, 20, 700, 701) THEN 2
294 WHEN t.typbasetype IN (1700) THEN 10
297 CASE WHEN a.atttypid IN (21, 23, 20, 700, 701) THEN 2
298 WHEN a.atttypid IN (1700) THEN 10
302 AS numeric_precision_radix,
305 CASE WHEN t.typtype = 'd' THEN
306 CASE WHEN t.typbasetype IN (21, 23, 20) THEN 0
307 WHEN t.typbasetype IN (1700) THEN (t.typtypmod - 4) & 65535
310 CASE WHEN a.atttypid IN (21, 23, 20) THEN 0
311 WHEN a.atttypid IN (1700) THEN (a.atttypmod - 4) & 65535
318 CASE WHEN t.typtype = 'd' THEN
319 CASE WHEN t.typbasetype IN (1083, 1114, 1184, 1266)
320 THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod ELSE null END)
321 WHEN t.typbasetype IN (1186)
322 THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod & 65535 ELSE null END)
325 CASE WHEN a.atttypid IN (1083, 1114, 1184, 1266)
326 THEN (CASE WHEN a.atttypmod <> -1 THEN a.atttypmod ELSE null END)
327 WHEN a.atttypid IN (1186)
328 THEN (CASE WHEN a.atttypmod <> -1 THEN a.atttypmod & 65535 ELSE null END)
332 AS datetime_precision,
334 CAST(null AS character_data) AS interval_type, -- XXX
335 CAST(null AS character_data) AS interval_precision, -- XXX
337 CAST(null AS sql_identifier) AS character_set_catalog,
338 CAST(null AS sql_identifier) AS character_set_schema,
339 CAST(null AS sql_identifier) AS character_set_name,
341 CAST(null AS sql_identifier) AS collation_catalog,
342 CAST(null AS sql_identifier) AS collation_schema,
343 CAST(null AS sql_identifier) AS collation_name,
345 CAST(CASE WHEN t.typtype = 'd' THEN current_database() ELSE null END
346 AS sql_identifier) AS domain_catalog,
347 CAST(CASE WHEN t.typtype = 'd' THEN nt.nspname ELSE null END
348 AS sql_identifier) AS domain_schema,
349 CAST(CASE WHEN t.typtype = 'd' THEN t.typname ELSE null END
350 AS sql_identifier) AS domain_name,
352 CAST(current_database() AS sql_identifier) AS udt_catalog,
353 CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,
354 CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,
356 CAST(null AS sql_identifier) AS scope_catalog,
357 CAST(null AS sql_identifier) AS scope_schema,
358 CAST(null AS sql_identifier) AS scope_name,
360 CAST(null AS cardinal_number) AS maximum_cardinality,
361 CAST(a.attnum AS sql_identifier) AS dtd_identifier,
362 CAST('NO' AS character_data) AS is_self_referencing
364 FROM (pg_attribute LEFT JOIN pg_attrdef ON attrelid = adrelid AND attnum = adnum) AS a,
365 pg_class c, pg_namespace nc, pg_user u,
366 (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
367 LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
368 ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
370 WHERE a.attrelid = c.oid
371 AND a.atttypid = t.oid
372 AND u.usesysid = c.relowner
373 AND nc.oid = c.relnamespace
375 AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
377 AND (u.usename = current_user
378 OR has_table_privilege(c.oid, 'SELECT')
379 OR has_table_privilege(c.oid, 'INSERT')
380 OR has_table_privilege(c.oid, 'UPDATE')
381 OR has_table_privilege(c.oid, 'RERERENCES') );
383 GRANT SELECT ON columns TO PUBLIC;
388 * CONSTRAINT_COLUMN_USAGE view
391 CREATE VIEW constraint_column_usage AS
392 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
393 CAST(tblschema AS sql_identifier) AS table_schema,
394 CAST(tblname AS sql_identifier) AS table_name,
395 CAST(colname AS sql_identifier) AS column_name,
396 CAST(current_database() AS sql_identifier) AS constraint_catalog,
397 CAST(cstrschema AS sql_identifier) AS constraint_schema,
398 CAST(cstrname AS sql_identifier) AS constraint_name
401 /* check constraints */
402 SELECT DISTINCT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
403 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_depend d, pg_namespace nc, pg_constraint c
404 WHERE nr.oid = r.relnamespace
405 AND r.oid = a.attrelid
406 AND d.refclassid = (SELECT oid FROM pg_class WHERE relname = 'pg_class')
407 AND d.refobjid = r.oid
408 AND d.refobjsubid = a.attnum
409 AND d.classid = (SELECT oid FROM pg_class WHERE relname = 'pg_constraint')
411 AND c.connamespace = nc.oid
415 AND NOT a.attisdropped
419 /* unique/primary key/foreign key constraints */
420 SELECT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
421 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc, pg_constraint c,
422 (select 1 union select 2 union select 3 union select 4 union select 5 union
423 select 6 union select 7 union select 8 union select 9 union select 10 union
424 select 11 union select 12 union select 13 union select 14 union select 15 union
425 select 16 union select 17 union select 18 union select 19 union select 20 union
426 select 21 union select 22 union select 23 union select 24 union select 25 union
427 select 26 union select 27 union select 28 union select 29 union select 30 union
428 select 31 union select 32) AS pos(n)
429 WHERE nr.oid = r.relnamespace
430 AND r.oid = a.attrelid
431 AND r.oid = c.conrelid
432 AND nc.oid = c.connamespace
433 AND (CASE WHEN c.contype = 'f' THEN c.confkey[pos.n] = a.attnum
434 ELSE c.conkey[pos.n] = a.attnum END)
436 AND NOT a.attisdropped
437 AND c.contype IN ('p', 'u', 'f')
440 ) AS x (tblschema, tblname, tblowner, colname, cstrschema, cstrname),
443 WHERE x.tblowner = u.usesysid AND u.usename = current_user;
445 GRANT SELECT ON constraint_column_usage TO PUBLIC;
450 * CONSTRAINT_TABLE_USAGE view
453 CREATE VIEW constraint_table_usage AS
454 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
455 CAST(nr.nspname AS sql_identifier) AS table_schema,
456 CAST(r.relname AS sql_identifier) AS table_name,
457 CAST(current_database() AS sql_identifier) AS constraint_catalog,
458 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
459 CAST(c.conname AS sql_identifier) AS constraint_name
461 FROM pg_constraint c, pg_namespace nc,
462 pg_class r, pg_namespace nr,
465 WHERE c.connamespace = nc.oid AND r.relnamespace = nr.oid
466 AND ( (c.contype = 'f' AND c.confrelid = r.oid)
467 OR (c.contype IN ('p', 'u') AND c.conrelid = r.oid) )
469 AND r.relowner = u.usesysid AND u.usename = current_user;
471 GRANT SELECT ON constraint_table_usage TO PUBLIC;
474 -- 20.21 DATA_TYPE_PRIVILEGES view appears later.
479 * DOMAIN_CONSTRAINTS view
482 CREATE VIEW domain_constraints AS
483 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
484 CAST(rs.nspname AS sql_identifier) AS constraint_schema,
485 CAST(con.conname AS sql_identifier) AS constraint_name,
486 CAST(current_database() AS sql_identifier) AS domain_catalog,
487 CAST(n.nspname AS sql_identifier) AS domain_schema,
488 CAST(t.typname AS sql_identifier) AS domain_name,
489 CAST(CASE WHEN condeferrable THEN 'YES' ELSE 'NO' END
490 AS character_data) AS is_deferrable,
491 CAST(CASE WHEN condeferred THEN 'YES' ELSE 'NO' END
492 AS character_data) AS initially_deferred
493 FROM pg_namespace rs, pg_namespace n, pg_constraint con, pg_type t, pg_user u
494 WHERE rs.oid = con.connamespace
495 AND n.oid = t.typnamespace
496 AND u.usesysid = t.typowner
497 AND u.usename = current_user
498 AND t.oid = con.contypid;
500 GRANT SELECT ON domain_constraints TO PUBLIC;
505 * DOMAIN_UDT_USAGE view
508 CREATE VIEW domain_udt_usage AS
509 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
510 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
511 CAST(bt.typname AS sql_identifier) AS udt_name,
512 CAST(current_database() AS sql_identifier) AS domain_catalog,
513 CAST(nt.nspname AS sql_identifier) AS domain_schema,
514 CAST(t.typname AS sql_identifier) AS domain_name
516 FROM pg_type t, pg_namespace nt,
517 pg_type bt, pg_namespace nbt,
520 WHERE t.typnamespace = nt.oid
521 AND t.typbasetype = bt.oid
522 AND bt.typnamespace = nbt.oid
524 AND bt.typowner = u.usesysid
525 AND u.usename = current_user;
527 GRANT SELECT ON domain_udt_usage TO PUBLIC;
535 CREATE VIEW domains AS
536 SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
537 CAST(nt.nspname AS sql_identifier) AS domain_schema,
538 CAST(t.typname AS sql_identifier) AS domain_name,
541 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
542 WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
543 ELSE 'USER-DEFINED' END
548 CASE WHEN t.typbasetype IN (25, 1042, 1043, 1560, 1562) AND t.typtypmod <> -1
552 AS character_maximum_length,
555 CASE WHEN t.typbasetype IN (25, 1042, 1043) THEN 2^30 ELSE null END
557 AS character_octet_length,
559 CAST(null AS sql_identifier) AS character_set_catalog,
560 CAST(null AS sql_identifier) AS character_set_schema,
561 CAST(null AS sql_identifier) AS character_set_name,
563 CAST(null AS sql_identifier) AS collation_catalog,
564 CAST(null AS sql_identifier) AS collation_schema,
565 CAST(null AS sql_identifier) AS collation_name,
569 WHEN 21 /*int2*/ THEN 16
570 WHEN 23 /*int4*/ THEN 32
571 WHEN 20 /*int8*/ THEN 64
572 WHEN 1700 /*numeric*/ THEN ((t.typtypmod - 4) >> 16) & 65535
573 WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/
574 WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/
577 AS numeric_precision,
580 CASE WHEN t.typbasetype IN (21, 23, 20, 700, 701) THEN 2
581 WHEN t.typbasetype IN (1700) THEN 10
584 AS numeric_precision_radix,
587 CASE WHEN t.typbasetype IN (21, 23, 20) THEN 0
588 WHEN t.typbasetype IN (1700) THEN (t.typtypmod - 4) & 65535
594 CASE WHEN t.typbasetype IN (1083, 1114, 1184, 1266)
595 THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod ELSE null END)
596 WHEN t.typbasetype IN (1186)
597 THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod & 65535 ELSE null END)
600 AS datetime_precision,
602 CAST(null AS character_data) AS interval_type, -- XXX
603 CAST(null AS character_data) AS interval_precision, -- XXX
605 CAST(t.typdefault AS character_data) AS domain_default,
607 CAST(current_database() AS sql_identifier) AS udt_catalog,
608 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
609 CAST(bt.typname AS sql_identifier) AS udt_name,
611 CAST(null AS sql_identifier) AS scope_catalog,
612 CAST(null AS sql_identifier) AS scope_schema,
613 CAST(null AS sql_identifier) AS scope_name,
615 CAST(null AS cardinal_number) AS maximum_cardinality,
616 CAST(1 AS sql_identifier) AS dtd_identifier
618 FROM pg_type t, pg_namespace nt,
619 pg_type bt, pg_namespace nbt
621 WHERE t.typnamespace = nt.oid
622 AND t.typbasetype = bt.oid
623 AND bt.typnamespace = nbt.oid
626 GRANT SELECT ON domains TO PUBLIC;
629 -- 20.27 ELEMENT_TYPES view appears later.
637 CREATE VIEW enabled_roles AS
638 SELECT CAST(g.groname AS sql_identifier) AS role_name
639 FROM pg_group g, pg_user u
640 WHERE u.usesysid = ANY (g.grolist)
641 AND u.usename = current_user;
643 GRANT SELECT ON enabled_roles TO PUBLIC;
648 * KEY_COLUMN_USAGE view
651 CREATE VIEW key_column_usage AS
652 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
653 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
654 CAST(c.conname AS sql_identifier) AS constraint_name,
655 CAST(current_database() AS sql_identifier) AS table_catalog,
656 CAST(nr.nspname AS sql_identifier) AS table_schema,
657 CAST(r.relname AS sql_identifier) AS table_name,
658 CAST(a.attname AS sql_identifier) AS column_name,
659 CAST(pos.n AS cardinal_number) AS ordinal_position
661 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc, pg_constraint c,
663 (select 1 union select 2 union select 3 union select 4 union select 5 union
664 select 6 union select 7 union select 8 union select 9 union select 10 union
665 select 11 union select 12 union select 13 union select 14 union select 15 union
666 select 16 union select 17 union select 18 union select 19 union select 20 union
667 select 21 union select 22 union select 23 union select 24 union select 25 union
668 select 26 union select 27 union select 28 union select 29 union select 30 union
669 select 31 union select 32) AS pos(n)
671 WHERE nr.oid = r.relnamespace
672 AND r.oid = a.attrelid
673 AND r.oid = c.conrelid
674 AND nc.oid = c.connamespace
675 AND c.conkey[pos.n] = a.attnum
677 AND NOT a.attisdropped
678 AND c.contype IN ('p', 'u', 'f')
680 AND r.relowner = u.usesysid
681 AND u.usename = current_user;
683 GRANT SELECT ON key_column_usage TO PUBLIC;
691 CREATE VIEW parameters AS
692 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
693 CAST(n.nspname AS sql_identifier) AS specific_schema,
694 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
695 CAST(n + 1 AS cardinal_number) AS ordinal_position,
696 CAST('IN' AS character_data) AS parameter_mode,
697 CAST('NO' AS character_data) AS is_result,
698 CAST('NO' AS character_data) AS as_locator,
699 CAST(null AS sql_identifier) AS parameter_name,
701 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
702 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
703 ELSE 'USER-DEFINED' END AS character_data)
705 CAST(null AS cardinal_number) AS character_maximum_length,
706 CAST(null AS cardinal_number) AS character_octet_length,
707 CAST(null AS sql_identifier) AS character_set_catalog,
708 CAST(null AS sql_identifier) AS character_set_schema,
709 CAST(null AS sql_identifier) AS character_set_name,
710 CAST(null AS sql_identifier) AS collation_catalog,
711 CAST(null AS sql_identifier) AS collation_schema,
712 CAST(null AS sql_identifier) AS collation_name,
713 CAST(null AS cardinal_number) AS numeric_precision,
714 CAST(null AS cardinal_number) AS numeric_precision_radix,
715 CAST(null AS cardinal_number) AS numeric_scale,
716 CAST(null AS cardinal_number) AS datetime_precision,
717 CAST(null AS character_data) AS interval_type,
718 CAST(null AS character_data) AS interval_precision,
719 CAST(current_database() AS sql_identifier) AS udt_catalog,
720 CAST(nt.nspname AS sql_identifier) AS udt_schema,
721 CAST(t.typname AS sql_identifier) AS udt_name,
722 CAST(null AS sql_identifier) AS scope_catalog,
723 CAST(null AS sql_identifier) AS scope_schema,
724 CAST(null AS sql_identifier) AS scope_name,
725 CAST(null AS cardinal_number) AS maximum_cardinality,
726 CAST(n + 1 AS sql_identifier) AS dtd_identifier
728 FROM pg_namespace n, pg_proc p, pg_type t, pg_namespace nt, pg_user u,
729 (select 0 union select 1 union select 2 union select 3 union select 4 union
730 select 5 union select 6 union select 7 union select 8 union select 9 union
731 select 10 union select 11 union select 12 union select 13 union select 14 union
732 select 15 union select 16 union select 17 union select 18 union select 19 union
733 select 20 union select 21 union select 22 union select 23 union select 24 union
734 select 25 union select 26 union select 27 union select 28 union select 29 union
735 select 30 union select 31) AS pos(n)
737 WHERE n.oid = p.pronamespace AND p.pronargs > pos.n
738 AND p.proargtypes[n] = 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 VIEW referential_constraints AS
751 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
752 CAST(ncon.nspname AS sql_identifier) AS constraint_schema,
753 CAST(con.conname AS sql_identifier) AS constraint_name,
754 CAST(current_database() AS sql_identifier) AS unique_constraint_catalog,
755 CAST(npkc.nspname AS sql_identifier) AS unique_constraint_schema,
756 CAST(pkc.conname AS sql_identifier) AS unique_constraint_name,
759 CASE con.confmatchtype WHEN 'f' THEN 'FULL'
760 WHEN 'p' THEN 'PARTIAL'
761 WHEN 'u' THEN 'NONE' END
762 AS character_data) AS match_option,
765 CASE con.confupdtype WHEN 'c' THEN 'CASCADE'
766 WHEN 'n' THEN 'SET NULL'
767 WHEN 'd' THEN 'SET DEFAULT'
768 WHEN 'r' THEN 'RESTRICT'
769 WHEN 'a' THEN 'NOACTION' END
770 AS character_data) AS update_rule,
773 CASE con.confdeltype WHEN 'c' THEN 'CASCADE'
774 WHEN 'n' THEN 'SET NULL'
775 WHEN 'd' THEN 'SET DEFAULT'
776 WHEN 'r' THEN 'RESTRICT'
777 WHEN 'a' THEN 'NOACTION' END
778 AS character_data) AS delete_rule
780 FROM pg_namespace ncon,
787 WHERE ncon.oid = con.connamespace
788 AND con.conrelid = c.oid
789 AND con.confkey = pkc.conkey
790 AND pkc.connamespace = npkc.oid
791 AND c.relowner = u.usesysid
793 AND u.usename = current_user;
795 GRANT SELECT ON referential_constraints TO PUBLIC;
800 * ROLE_COLUMN_GRANTS view
803 CREATE VIEW role_column_grants AS
804 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
805 CAST(g_grantee.groname AS sql_identifier) AS grantee,
806 CAST(current_database() AS sql_identifier) AS table_catalog,
807 CAST(nc.nspname AS sql_identifier) AS table_schema,
808 CAST(c.relname AS sql_identifier) AS table_name,
809 CAST(a.attname AS sql_identifier) AS column_name,
810 CAST(pr.type AS character_data) AS privilege_type,
812 CASE WHEN aclcontains(c.relacl,
813 makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, pr.type, true))
814 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
821 (SELECT 'SELECT' UNION SELECT 'INSERT' UNION SELECT 'UPDATE' UNION SELECT 'REFERENCES') AS pr (type)
823 WHERE a.attrelid = c.oid
824 AND c.relnamespace = nc.oid
826 AND NOT a.attisdropped
827 AND c.relkind IN ('r', 'v')
828 AND aclcontains(c.relacl,
829 makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, pr.type, false))
830 AND g_grantee.groname IN (SELECT role_name FROM enabled_roles);
832 GRANT SELECT ON role_column_grants TO PUBLIC;
837 * ROLE_ROUTINE_GRANTS view
840 CREATE VIEW role_routine_grants AS
841 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
842 CAST(g_grantee.groname AS sql_identifier) AS grantee,
843 CAST(current_database() AS sql_identifier) AS specific_catalog,
844 CAST(n.nspname AS sql_identifier) AS specific_schema,
845 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
846 CAST(current_database() AS sql_identifier) AS routine_catalog,
847 CAST(n.nspname AS sql_identifier) AS routine_schema,
848 CAST(p.proname AS sql_identifier) AS routine_name,
849 CAST('EXECUTE' AS character_data) AS privilege_type,
851 CASE WHEN aclcontains(p.proacl,
852 makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, 'EXECUTE', true))
853 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
860 WHERE p.pronamespace = n.oid
861 AND aclcontains(p.proacl,
862 makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, 'EXECUTE', false))
863 AND g_grantee.groname IN (SELECT role_name FROM enabled_roles);
865 GRANT SELECT ON role_routine_grants TO PUBLIC;
870 * ROLE_TABLE_GRANTS view
873 CREATE VIEW role_table_grants AS
874 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
875 CAST(g_grantee.groname AS sql_identifier) AS grantee,
876 CAST(current_database() AS sql_identifier) AS table_catalog,
877 CAST(nc.nspname AS sql_identifier) AS table_schema,
878 CAST(c.relname AS sql_identifier) AS table_name,
879 CAST(pr.type AS character_data) AS privilege_type,
881 CASE WHEN aclcontains(c.relacl,
882 makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, pr.type, true))
883 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable,
884 CAST('NO' AS character_data) AS with_hierarchy
890 (SELECT 'SELECT' UNION SELECT 'DELETE' UNION SELECT 'INSERT' UNION SELECT 'UPDATE'
891 UNION SELECT 'REFERENCES' UNION SELECT 'RULE' UNION SELECT 'TRIGGER') AS pr (type)
893 WHERE c.relnamespace = nc.oid
894 AND c.relkind IN ('r', 'v')
895 AND aclcontains(c.relacl,
896 makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, pr.type, false))
897 AND g_grantee.groname IN (SELECT role_name FROM enabled_roles);
899 GRANT SELECT ON role_table_grants TO PUBLIC;
904 * ROLE_USAGE_GRANTS view
907 -- See USAGE_PRIVILEGES.
909 CREATE VIEW role_usage_grants AS
910 SELECT CAST(null AS sql_identifier) AS grantor,
911 CAST(null AS sql_identifier) AS grantee,
912 CAST(current_database() AS sql_identifier) AS object_catalog,
913 CAST(null AS sql_identifier) AS object_schema,
914 CAST(null AS sql_identifier) AS object_name,
915 CAST(null AS character_data) AS object_type,
916 CAST('USAGE' AS character_data) AS privilege_type,
917 CAST(null AS character_data) AS is_grantable
921 GRANT SELECT ON role_usage_grants TO PUBLIC;
926 * ROUTINE_PRIVILEGES view
929 CREATE VIEW routine_privileges AS
930 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
931 CAST(grantee.name AS sql_identifier) AS grantee,
932 CAST(current_database() AS sql_identifier) AS specific_catalog,
933 CAST(n.nspname AS sql_identifier) AS specific_schema,
934 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
935 CAST(current_database() AS sql_identifier) AS routine_catalog,
936 CAST(n.nspname AS sql_identifier) AS routine_schema,
937 CAST(p.proname AS sql_identifier) AS routine_name,
938 CAST('EXECUTE' AS character_data) AS privilege_type,
940 CASE WHEN aclcontains(p.proacl,
941 makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, 'EXECUTE', true))
942 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
948 SELECT usesysid, 0, usename FROM pg_user
950 SELECT 0, grosysid, groname FROM pg_group
952 SELECT 0, 0, 'PUBLIC'
953 ) AS grantee (usesysid, grosysid, name)
955 WHERE p.pronamespace = n.oid
956 AND aclcontains(p.proacl,
957 makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, 'EXECUTE', false))
958 AND (u_grantor.usename = current_user
959 OR grantee.name = current_user
960 OR grantee.name = 'PUBLIC');
962 GRANT SELECT ON routine_privileges TO PUBLIC;
970 CREATE VIEW routines AS
971 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
972 CAST(n.nspname AS sql_identifier) AS specific_schema,
973 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
974 CAST(current_database() AS sql_identifier) AS routine_catalog,
975 CAST(n.nspname AS sql_identifier) AS routine_schema,
976 CAST(p.proname AS sql_identifier) AS routine_name,
977 CAST('FUNCTION' AS character_data) AS routine_type,
978 CAST(null AS sql_identifier) AS module_catalog,
979 CAST(null AS sql_identifier) AS module_schema,
980 CAST(null AS sql_identifier) AS module_name,
981 CAST(null AS sql_identifier) AS udt_catalog,
982 CAST(null AS sql_identifier) AS udt_schema,
983 CAST(null AS sql_identifier) AS udt_name,
986 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
987 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
988 ELSE 'USER-DEFINED' END AS character_data)
990 CAST(null AS cardinal_number) AS character_maximum_length,
991 CAST(null AS cardinal_number) AS character_octet_length,
992 CAST(null AS sql_identifier) AS character_set_catalog,
993 CAST(null AS sql_identifier) AS character_set_schema,
994 CAST(null AS sql_identifier) AS character_set_name,
995 CAST(null AS sql_identifier) AS collation_catalog,
996 CAST(null AS sql_identifier) AS collation_schema,
997 CAST(null AS sql_identifier) AS collation_name,
998 CAST(null AS cardinal_number) AS numeric_precision,
999 CAST(null AS cardinal_number) AS numeric_precision_radix,
1000 CAST(null AS cardinal_number) AS numeric_scale,
1001 CAST(null AS cardinal_number) AS datetime_precision,
1002 CAST(null AS character_data) AS interval_type,
1003 CAST(null AS character_data) AS interval_precision,
1004 CAST(current_database() AS sql_identifier) AS type_udt_catalog,
1005 CAST(nt.nspname AS sql_identifier) AS type_udt_schema,
1006 CAST(t.typname AS sql_identifier) AS type_udt_name,
1007 CAST(null AS sql_identifier) AS scope_catalog,
1008 CAST(null AS sql_identifier) AS scope_schema,
1009 CAST(null AS sql_identifier) AS scope_name,
1010 CAST(null AS cardinal_number) AS maximum_cardinality,
1011 CAST(0 AS sql_identifier) AS dtd_identifier,
1013 CAST(CASE WHEN l.lanname = 'sql' THEN 'SQL' ELSE 'EXTERNAL' END AS character_data)
1016 CASE WHEN u.usename = current_user THEN p.prosrc ELSE null END
1017 AS character_data) AS routine_definition,
1019 CASE WHEN l.lanname = 'c' THEN p.prosrc ELSE null END
1020 AS character_data) AS external_name,
1021 CAST(upper(l.lanname) AS character_data) AS external_language,
1023 CAST('GENERAL' AS character_data) AS parameter_style,
1024 CAST(CASE WHEN p.provolatile = 'i' THEN 'YES' ELSE 'NO' END AS character_data) AS is_deterministic,
1025 CAST('MODIFIES' AS character_data) AS sql_data_access,
1026 CAST(CASE WHEN p.proisstrict THEN 'YES' ELSE 'NO' END AS character_data) AS is_null_call,
1027 CAST(null AS character_data) AS sql_path,
1028 CAST('YES' AS character_data) AS schema_level_routine,
1029 CAST(0 AS cardinal_number) AS max_dynamic_result_sets,
1030 CAST(null AS character_data) AS is_user_defined_cast,
1031 CAST(null AS character_data) AS is_implicitly_invocable,
1032 CAST(CASE WHEN p.prosecdef THEN 'DEFINER' ELSE 'INVOKER' END AS character_data) AS security_type,
1033 CAST(null AS sql_identifier) AS to_sql_specific_catalog,
1034 CAST(null AS sql_identifier) AS to_sql_specific_schema,
1035 CAST(null AS sql_identifier) AS to_sql_specific_name,
1036 CAST('NO' AS character_data) AS as_locator
1038 FROM pg_namespace n, pg_proc p, pg_language l, pg_user u,
1039 pg_type t, pg_namespace nt
1041 WHERE n.oid = p.pronamespace AND p.prolang = l.oid AND p.proowner = u.usesysid
1042 AND p.prorettype = t.oid AND t.typnamespace = nt.oid
1043 AND (u.usename = current_user OR has_function_privilege(p.oid, 'EXECUTE'));
1045 GRANT SELECT ON routines TO PUBLIC;
1053 CREATE VIEW schemata AS
1054 SELECT CAST(current_database() AS sql_identifier) AS catalog_name,
1055 CAST(n.nspname AS sql_identifier) AS schema_name,
1056 CAST(u.usename AS sql_identifier) AS schema_owner,
1057 CAST(null AS sql_identifier) AS default_character_set_catalog,
1058 CAST(null AS sql_identifier) AS default_character_set_schema,
1059 CAST(null AS sql_identifier) AS default_character_set_name,
1060 CAST(null AS character_data) AS sql_path
1061 FROM pg_namespace n, pg_user u
1062 WHERE n.nspowner = u.usesysid AND u.usename = current_user;
1064 GRANT SELECT ON schemata TO PUBLIC;
1069 * SQL_FEATURES table
1072 CREATE TABLE sql_features (
1073 feature_id character_data,
1074 feature_name character_data,
1075 sub_feature_id character_data,
1076 sub_feature_name character_data,
1077 is_supported character_data,
1078 is_verified_by character_data,
1079 comments character_data
1082 -- Will be filled with external data by initdb.
1084 GRANT SELECT ON sql_features TO PUBLIC;
1089 * SQL_IMPLEMENTATION_INFO table
1092 -- Note: Implementation information items are defined in ISO 9075-3:1999,
1095 CREATE TABLE sql_implementation_info (
1096 implementation_info_id character_data,
1097 implementation_info_name character_data,
1098 integer_value cardinal_number,
1099 character_value character_data,
1100 comments character_data
1103 INSERT INTO sql_implementation_info VALUES ('10003', 'CATALOG NAME', NULL, 'Y', NULL);
1104 INSERT INTO sql_implementation_info VALUES ('10004', 'COLLATING SEQUENCE', NULL, '', 'not supported');
1105 INSERT INTO sql_implementation_info VALUES ('23', 'CURSOR COMMIT BEHAVIOR', 1, NULL, 'close cursors and retain prepared statements');
1106 INSERT INTO sql_implementation_info VALUES ('2', 'DATA SOURCE NAME', NULL, '', NULL);
1107 INSERT INTO sql_implementation_info VALUES ('17', 'DBMS NAME', NULL, (select trim(trailing ' ' from substring(version() from '^[^0-9]*'))), NULL);
1108 INSERT INTO sql_implementation_info VALUES ('18', 'DBMS VERSION', NULL, '???', NULL); -- filled by initdb
1109 INSERT INTO sql_implementation_info VALUES ('26', 'DEFAULT TRANSACTION ISOLATION', 2, NULL, 'READ COMMITED; user-settable');
1110 INSERT INTO sql_implementation_info VALUES ('28', 'IDENTIFIER CASE', 3, NULL, 'stored in mixed case - case sensitive');
1111 INSERT INTO sql_implementation_info VALUES ('85', 'NULL COLLATION', 0, NULL, 'nulls higher than non-nulls');
1112 INSERT INTO sql_implementation_info VALUES ('13', 'SERVER NAME', NULL, '', NULL);
1113 INSERT INTO sql_implementation_info VALUES ('94', 'SPECIAL CHARACTERS', NULL, '', 'all non-ASCII characters allowed');
1114 INSERT INTO sql_implementation_info VALUES ('46', 'TRANSACTION CAPABLE', 2, NULL, 'both DML and DDL');
1116 GRANT SELECT ON sql_implementation_info TO PUBLIC;
1121 * SQL_LANGUAGES table
1124 CREATE TABLE sql_languages (
1125 sql_language_source character_data,
1126 sql_language_year character_data,
1127 sql_language_conformance character_data,
1128 sql_language_integrity character_data,
1129 sql_language_implementation character_data,
1130 sql_language_binding_style character_data,
1131 sql_language_programming_language character_data
1134 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'DIRECT', NULL);
1135 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
1137 GRANT SELECT ON sql_languages TO PUBLIC;
1142 * SQL_PACKAGES table
1145 CREATE TABLE sql_packages (
1146 feature_id character_data,
1147 feature_name character_data,
1148 is_supported character_data,
1149 is_verified_by character_data,
1150 comments character_data
1153 INSERT INTO sql_packages VALUES ('PKG000', 'Core', 'NO', NULL, '');
1154 INSERT INTO sql_packages VALUES ('PKG001', 'Enhanced datetime facilities', 'YES', NULL, '');
1155 INSERT INTO sql_packages VALUES ('PKG002', 'Enhanced integrity management', 'NO', NULL, '');
1156 INSERT INTO sql_packages VALUES ('PKG003', 'OLAP facilities', 'NO', NULL, '');
1157 INSERT INTO sql_packages VALUES ('PKG004', 'PSM', 'NO', NULL, 'PL/pgSQL is similar.');
1158 INSERT INTO sql_packages VALUES ('PKG005', 'CLI', 'NO', NULL, 'ODBC is similar.');
1159 INSERT INTO sql_packages VALUES ('PKG006', 'Basic object support', 'NO', NULL, '');
1160 INSERT INTO sql_packages VALUES ('PKG007', 'Enhanced object support', 'NO', NULL, '');
1161 INSERT INTO sql_packages VALUES ('PKG008', 'Active database', 'NO', NULL, '');
1162 INSERT INTO sql_packages VALUES ('PKG009', 'SQL/MM support', 'NO', NULL, '');
1164 GRANT SELECT ON sql_packages TO PUBLIC;
1172 -- Note: Sizing items are defined in ISO 9075-3:1999, clause 7.2.
1174 CREATE TABLE sql_sizing (
1175 sizing_id cardinal_number,
1176 sizing_name character_data,
1177 supported_value cardinal_number,
1178 comments character_data
1181 INSERT INTO sql_sizing VALUES (34, 'MAXIMUM CATALOG NAME LENGTH', 63, NULL);
1182 INSERT INTO sql_sizing VALUES (30, 'MAXIMUM COLUMN NAME LENGTH', 63, NULL);
1183 INSERT INTO sql_sizing VALUES (97, 'MAXIMUM COLUMNS IN GROUP BY', 0, NULL);
1184 INSERT INTO sql_sizing VALUES (99, 'MAXIMUM COLUMNS IN ORDER BY', 0, NULL);
1185 INSERT INTO sql_sizing VALUES (100, 'MAXIMUM COLUMNS IN SELECT', 0, NULL);
1186 INSERT INTO sql_sizing VALUES (101, 'MAXIMUM COLUMNS IN TABLE', 1600, NULL); -- match MaxHeapAttributeNumber
1187 INSERT INTO sql_sizing VALUES (1, 'MAXIMUM CONCURRENT ACTIVITIES', 0, NULL);
1188 INSERT INTO sql_sizing VALUES (31, 'MAXIMUM CURSOR NAME LENGTH', 63, NULL);
1189 INSERT INTO sql_sizing VALUES (0, 'MAXIMUM DRIVER CONNECTIONS', NULL, NULL);
1190 INSERT INTO sql_sizing VALUES (10005, 'MAXIMUM IDENTIFIER LENGTH', 63, NULL);
1191 INSERT INTO sql_sizing VALUES (32, 'MAXIMUM SCHEMA NAME LENGTH', 63, NULL);
1192 INSERT INTO sql_sizing VALUES (20000, 'MAXIMUM STATEMENT OCTETS', 0, NULL);
1193 INSERT INTO sql_sizing VALUES (20001, 'MAXIMUM STATEMENT OCTETS DATA', 0, NULL);
1194 INSERT INTO sql_sizing VALUES (20002, 'MAXIMUM STATEMENT OCTETS SCHEMA', 0, NULL);
1195 INSERT INTO sql_sizing VALUES (35, 'MAXIMUM TABLE NAME LENGTH', 63, NULL);
1196 INSERT INTO sql_sizing VALUES (106, 'MAXIMUM TABLES IN SELECT', 0, NULL);
1197 INSERT INTO sql_sizing VALUES (107, 'MAXIMUM USER NAME LENGTH', 63, NULL);
1198 INSERT INTO sql_sizing VALUES (25000, 'MAXIMUM CURRENT DEFAULT TRANSFORM GROUP LENGTH', NULL, NULL);
1199 INSERT INTO sql_sizing VALUES (25001, 'MAXIMUM CURRENT TRANSFORM GROUP LENGTH', NULL, NULL);
1200 INSERT INTO sql_sizing VALUES (25002, 'MAXIMUM CURRENT PATH LENGTH', 0, NULL);
1201 INSERT INTO sql_sizing VALUES (25003, 'MAXIMUM CURRENT ROLE LENGTH', NULL, NULL);
1202 INSERT INTO sql_sizing VALUES (25004, 'MAXIMUM SESSION USER LENGTH', 63, NULL);
1203 INSERT INTO sql_sizing VALUES (25005, 'MAXIMUM SYSTEM USER LENGTH', 63, NULL);
1206 SET supported_value = (SELECT typlen-1 FROM pg_catalog.pg_type WHERE typname = 'name'),
1207 comments = 'Might be less, depending on character set.'
1208 WHERE supported_value = 63;
1210 GRANT SELECT ON sql_sizing TO PUBLIC;
1215 * SQL_SIZING_PROFILES table
1218 -- The data in this table are defined by various profiles of SQL.
1219 -- Since we don't have any information about such profiles, we provide
1222 CREATE TABLE sql_sizing_profiles (
1223 sizing_id cardinal_number,
1224 sizing_name character_data,
1225 profile_id character_data,
1226 required_value cardinal_number,
1227 comments character_data
1230 GRANT SELECT ON sql_sizing_profiles TO PUBLIC;
1235 * TABLE_CONSTRAINTS view
1238 CREATE VIEW table_constraints AS
1239 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1240 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
1241 CAST(c.conname AS sql_identifier) AS constraint_name,
1242 CAST(current_database() AS sql_identifier) AS table_catalog,
1243 CAST(nr.nspname AS sql_identifier) AS table_schema,
1244 CAST(r.relname AS sql_identifier) AS table_name,
1246 CASE c.contype WHEN 'c' THEN 'CHECK'
1247 WHEN 'f' THEN 'FOREIGN KEY'
1248 WHEN 'p' THEN 'PRIMARY KEY'
1249 WHEN 'u' THEN 'UNIQUE' END
1250 AS character_data) AS constraint_type,
1251 CAST(CASE WHEN c.condeferrable THEN 'YES' ELSE 'NO' END AS character_data)
1253 CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS character_data)
1254 AS initially_deferred
1256 FROM pg_namespace nc,
1262 WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace
1263 AND c.conrelid = r.oid AND r.relowner = u.usesysid
1265 AND u.usename = current_user;
1267 -- FIMXE: Not-null constraints are missing here.
1269 GRANT SELECT ON table_constraints TO PUBLIC;
1274 * TABLE_PRIVILEGES view
1277 CREATE VIEW table_privileges AS
1278 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
1279 CAST(grantee.name AS sql_identifier) AS grantee,
1280 CAST(current_database() AS sql_identifier) AS table_catalog,
1281 CAST(nc.nspname AS sql_identifier) AS table_schema,
1282 CAST(c.relname AS sql_identifier) AS table_name,
1283 CAST(pr.type AS character_data) AS privilege_type,
1285 CASE WHEN aclcontains(c.relacl,
1286 makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr.type, true))
1287 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable,
1288 CAST('NO' AS character_data) AS with_hierarchy
1294 SELECT usesysid, 0, usename FROM pg_user
1296 SELECT 0, grosysid, groname FROM pg_group
1298 SELECT 0, 0, 'PUBLIC'
1299 ) AS grantee (usesysid, grosysid, name),
1300 (SELECT 'SELECT' UNION SELECT 'DELETE' UNION SELECT 'INSERT' UNION SELECT 'UPDATE'
1301 UNION SELECT 'REFERENCES' UNION SELECT 'RULE' UNION SELECT 'TRIGGER') AS pr (type)
1303 WHERE c.relnamespace = nc.oid
1304 AND c.relkind IN ('r', 'v')
1305 AND aclcontains(c.relacl,
1306 makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr.type, false))
1307 AND (u_grantor.usename = current_user
1308 OR grantee.name = current_user
1309 OR grantee.name = 'PUBLIC');
1311 GRANT SELECT ON table_privileges TO PUBLIC;
1319 CREATE VIEW tables AS
1320 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
1321 CAST(nc.nspname AS sql_identifier) AS table_schema,
1322 CAST(c.relname AS sql_identifier) AS table_name,
1325 CASE WHEN nc.nspname LIKE 'pg!_temp!_%' ESCAPE '!' THEN 'LOCAL TEMPORARY'
1326 WHEN c.relkind = 'r' THEN 'BASE TABLE'
1327 WHEN c.relkind = 'v' THEN 'VIEW'
1329 AS character_data) AS table_type,
1331 CAST(null AS sql_identifier) AS self_referencing_column_name,
1332 CAST(null AS character_data) AS reference_generation,
1334 CAST(null AS sql_identifier) AS user_defined_type_catalog,
1335 CAST(null AS sql_identifier) AS user_defined_type_schema,
1336 CAST(null AS sql_identifier) AS user_defined_name
1338 FROM pg_namespace nc, pg_class c, pg_user u
1340 WHERE c.relnamespace = nc.oid AND u.usesysid = c.relowner
1341 AND c.relkind IN ('r', 'v')
1342 AND (u.usename = current_user
1343 OR has_table_privilege(c.oid, 'SELECT')
1344 OR has_table_privilege(c.oid, 'INSERT')
1345 OR has_table_privilege(c.oid, 'UPDATE')
1346 OR has_table_privilege(c.oid, 'DELETE')
1347 OR has_table_privilege(c.oid, 'RULE')
1348 OR has_table_privilege(c.oid, 'RERERENCES')
1349 OR has_table_privilege(c.oid, 'TRIGGER') );
1351 GRANT SELECT ON tables TO PUBLIC;
1356 * TRIGGERED_UPDATE_COLUMNS view
1359 -- PostgreSQL doesn't allow the specification of individual triggered
1360 -- update columns, so this view is empty.
1362 CREATE VIEW triggered_update_columns AS
1363 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1364 CAST(null AS sql_identifier) AS trigger_schema,
1365 CAST(null AS sql_identifier) AS trigger_name,
1366 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1367 CAST(null AS sql_identifier) AS event_object_schema,
1368 CAST(null AS sql_identifier) AS event_object_table,
1369 CAST(null AS sql_identifier) AS event_object_column
1372 GRANT SELECT ON triggered_update_columns TO PUBLIC;
1380 CREATE VIEW triggers AS
1381 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1382 CAST(n.nspname AS sql_identifier) AS trigger_schema,
1383 CAST(t.tgname AS sql_identifier) AS trigger_name,
1384 CAST(em.text AS character_data) AS event_manipulation,
1385 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1386 CAST(n.nspname AS sql_identifier) AS event_object_schema,
1387 CAST(c.relname AS sql_identifier) AS event_object_table,
1388 CAST(null AS cardinal_number) AS action_order,
1389 CAST(null AS character_data) AS action_condition,
1391 substring(pg_get_triggerdef(t.oid) from
1392 position('EXECUTE PROCEDURE' in substring(pg_get_triggerdef(t.oid) from 48)) + 47)
1393 AS character_data) AS action_statement,
1395 CASE WHEN t.tgtype & 1 = 1 THEN 'ROW' ELSE 'STATEMENT' END
1396 AS character_data) AS action_orientation,
1398 CASE WHEN t.tgtype & 2 = 2 THEN 'BEFORE' ELSE 'AFTER' END
1399 AS character_data) AS condition_timing,
1400 CAST(null AS sql_identifier) AS condition_reference_old_table,
1401 CAST(null AS sql_identifier) AS condition_reference_new_table
1403 FROM pg_namespace n, pg_class c, pg_trigger t, pg_user u,
1404 (SELECT 4, 'INSERT' UNION SELECT 8, 'DELETE' UNION SELECT 16, 'UPDATE') AS em (num, text)
1406 WHERE n.oid = c.relnamespace
1407 AND c.oid = t.tgrelid
1408 AND c.relowner = u.usesysid
1409 AND t.tgtype & em.num <> 0
1410 AND NOT t.tgisconstraint
1411 AND u.usename = current_user;
1413 GRANT SELECT ON triggers TO PUBLIC;
1418 * USAGE_PRIVILEGES view
1421 -- Of the things currently implemented in PostgreSQL, usage privileges
1422 -- apply only to domains. Since domains have no real privileges, we
1423 -- represent all domains with implicit usage privilege here.
1425 CREATE VIEW usage_privileges AS
1426 SELECT CAST(u.usename AS sql_identifier) AS grantor,
1427 CAST('PUBLIC' AS sql_identifier) AS grantee,
1428 CAST(current_database() AS sql_identifier) AS object_catalog,
1429 CAST(n.nspname AS sql_identifier) AS object_schema,
1430 CAST(t.typname AS sql_identifier) AS object_name,
1431 CAST('DOMAIN' AS character_data) AS object_type,
1432 CAST('USAGE' AS character_data) AS privilege_type,
1433 CAST('NO' AS character_data) AS is_grantable
1439 WHERE u.usesysid = t.typowner
1440 AND t.typnamespace = n.oid
1441 AND t.typtype = 'd';
1443 GRANT SELECT ON usage_privileges TO PUBLIC;
1451 CREATE VIEW view_column_usage AS
1453 CAST(current_database() AS sql_identifier) AS view_catalog,
1454 CAST(nv.nspname AS sql_identifier) AS view_schema,
1455 CAST(v.relname AS sql_identifier) AS view_name,
1456 CAST(current_database() AS sql_identifier) AS table_catalog,
1457 CAST(nt.nspname AS sql_identifier) AS table_schema,
1458 CAST(t.relname AS sql_identifier) AS table_name,
1459 CAST(a.attname AS sql_identifier) AS column_name
1461 FROM pg_user, pg_namespace nv, pg_class v, pg_depend dv,
1462 pg_depend dt, pg_class t, pg_namespace nt,
1463 pg_attribute a, pg_user u
1465 WHERE nv.oid = v.relnamespace
1467 AND v.oid = dv.refobjid
1468 AND dv.refclassid = (SELECT oid FROM pg_class WHERE relname = 'pg_class')
1469 AND dv.classid = (SELECT oid FROM pg_class WHERE relname = 'pg_rewrite')
1470 AND dv.deptype = 'i'
1471 AND dv.objid = dt.objid
1472 AND dv.refobjid <> dt.refobjid
1473 AND dt.classid = (SELECT oid FROM pg_class WHERE relname = 'pg_rewrite')
1474 AND dt.refclassid = (SELECT oid FROM pg_class WHERE relname = 'pg_class')
1475 AND dt.refobjid = t.oid
1476 AND t.relnamespace = nt.oid
1477 AND t.relkind IN ('r', 'v')
1478 AND t.oid = a.attrelid
1479 AND dt.refobjsubid = a.attnum
1480 AND t.relowner = u.usesysid AND u.usename = current_user;
1482 GRANT SELECT ON view_column_usage TO PUBLIC;
1490 CREATE VIEW view_table_usage AS
1492 CAST(current_database() AS sql_identifier) AS view_catalog,
1493 CAST(nv.nspname AS sql_identifier) AS view_schema,
1494 CAST(v.relname AS sql_identifier) AS view_name,
1495 CAST(current_database() AS sql_identifier) AS table_catalog,
1496 CAST(nt.nspname AS sql_identifier) AS table_schema,
1497 CAST(t.relname AS sql_identifier) AS table_name
1499 FROM pg_user, pg_namespace nv, pg_class v, pg_depend dv,
1500 pg_depend dt, pg_class t, pg_namespace nt,
1503 WHERE nv.oid = v.relnamespace
1505 AND v.oid = dv.refobjid
1506 AND dv.refclassid = (SELECT oid FROM pg_class WHERE relname = 'pg_class')
1507 AND dv.classid = (SELECT oid FROM pg_class WHERE relname = 'pg_rewrite')
1508 AND dv.deptype = 'i'
1509 AND dv.objid = dt.objid
1510 AND dv.refobjid <> dt.refobjid
1511 AND dt.classid = (SELECT oid FROM pg_class WHERE relname = 'pg_rewrite')
1512 AND dt.refclassid = (SELECT oid FROM pg_class WHERE relname = 'pg_class')
1513 AND dt.refobjid = t.oid
1514 AND t.relnamespace = nt.oid
1515 AND t.relkind IN ('r', 'v')
1516 AND t.relowner = u.usesysid AND u.usename = current_user;
1518 GRANT SELECT ON view_table_usage TO PUBLIC;
1526 CREATE VIEW views AS
1527 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
1528 CAST(nc.nspname AS sql_identifier) AS table_schema,
1529 CAST(c.relname AS sql_identifier) AS table_name,
1532 CASE WHEN u.usename = current_user THEN pg_get_viewdef(c.oid)
1534 AS character_data) AS view_definition,
1536 CAST('NONE' AS character_data) AS check_option,
1537 CAST(null AS character_data) AS is_updatable, -- FIXME
1538 CAST(null AS character_data) AS is_insertable_into -- FIXME
1540 FROM pg_namespace nc, pg_class c, pg_user u
1542 WHERE c.relnamespace = nc.oid AND u.usesysid = c.relowner
1544 AND (u.usename = current_user
1545 OR has_table_privilege(c.oid, 'SELECT')
1546 OR has_table_privilege(c.oid, 'INSERT')
1547 OR has_table_privilege(c.oid, 'UPDATE')
1548 OR has_table_privilege(c.oid, 'DELETE')
1549 OR has_table_privilege(c.oid, 'RULE')
1550 OR has_table_privilege(c.oid, 'RERERENCES')
1551 OR has_table_privilege(c.oid, 'TRIGGER') );
1553 GRANT SELECT ON views TO PUBLIC;
1556 -- The following views have dependencies that force them to appear out of order.
1560 * DATA_TYPE_PRIVILEGES view
1563 CREATE VIEW data_type_privileges AS
1564 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
1565 CAST(x.objschema AS sql_identifier) AS object_schema,
1566 CAST(x.objname AS sql_identifier) AS object_name,
1567 CAST(x.objtype AS character_data) AS object_type,
1568 CAST(x.objdtdid AS sql_identifier) AS dtd_identifier
1572 SELECT table_schema, table_name, 'TABLE'::text, dtd_identifier FROM columns
1574 SELECT domain_schema, domain_name, 'DOMAIN'::text, dtd_identifier FROM domains
1576 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM parameters
1578 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM routines
1579 ) AS x (objschema, objname, objtype, objdtdid);
1581 GRANT SELECT ON data_type_privileges TO PUBLIC;
1586 * ELEMENT_TYPES view
1589 CREATE VIEW element_types AS
1590 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
1591 CAST(n.nspname AS sql_identifier) AS object_schema,
1592 CAST(x.objname AS sql_identifier) AS object_name,
1593 CAST(x.objtype AS character_data) AS object_type,
1594 CAST(x.objdtdid AS sql_identifier) AS array_type_identifier,
1596 CASE WHEN nbt.nspname = 'pg_catalog' THEN format_type(bt.oid, null)
1597 ELSE 'USER-DEFINED' END AS character_data) AS data_type,
1599 CAST(null AS cardinal_number) AS character_maximum_length,
1600 CAST(null AS cardinal_number) AS character_octet_length,
1601 CAST(null AS sql_identifier) AS character_set_catalog,
1602 CAST(null AS sql_identifier) AS character_set_schema,
1603 CAST(null AS sql_identifier) AS character_set_name,
1604 CAST(null AS sql_identifier) AS collation_catalog,
1605 CAST(null AS sql_identifier) AS collation_schema,
1606 CAST(null AS sql_identifier) AS collation_name,
1607 CAST(null AS cardinal_number) AS numeric_precision,
1608 CAST(null AS cardinal_number) AS numeric_precision_radix,
1609 CAST(null AS cardinal_number) AS numeric_scale,
1610 CAST(null AS cardinal_number) AS datetime_precision,
1611 CAST(null AS character_data) AS interval_type,
1612 CAST(null AS character_data) AS interval_precision,
1614 CAST(null AS character_data) AS domain_default, -- XXX maybe a bug in the standard
1616 CAST(current_database() AS sql_identifier) AS udt_catalog,
1617 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
1618 CAST(bt.typname AS sql_identifier) AS udt_name,
1620 CAST(null AS sql_identifier) AS scope_catalog,
1621 CAST(null AS sql_identifier) AS scope_schema,
1622 CAST(null AS sql_identifier) AS scope_name,
1624 CAST(null AS cardinal_number) AS maximum_cardinality,
1625 CAST('a' || x.objdtdid AS sql_identifier) AS dtd_identifier
1627 FROM pg_namespace n, pg_type at, pg_namespace nbt, pg_type bt,
1630 SELECT c.relnamespace, c.relname, 'TABLE'::text, a.attnum, a.atttypid
1631 FROM pg_class c, pg_attribute a
1632 WHERE c.oid = a.attrelid
1633 AND c.relkind IN ('r', 'v')
1634 AND attnum > 0 AND NOT attisdropped
1639 SELECT t.typnamespace, t.typname, 'DOMAIN'::text, 1, t.typbasetype
1641 WHERE t.typtype = 'd'
1646 SELECT p.pronamespace, p.proname, 'ROUTINE'::text, pos.n + 1, p.proargtypes[n]
1648 (select 0 union select 1 union select 2 union select 3 union select 4 union
1649 select 5 union select 6 union select 7 union select 8 union select 9 union
1650 select 10 union select 11 union select 12 union select 13 union select 14 union
1651 select 15 union select 16 union select 17 union select 18 union select 19 union
1652 select 20 union select 21 union select 22 union select 23 union select 24 union
1653 select 25 union select 26 union select 27 union select 28 union select 29 union
1654 select 30 union select 31) AS pos(n)
1655 WHERE p.pronargs > pos.n
1660 SELECT p.pronamespace, p.proname, 'ROUTINE'::text, 0, p.prorettype
1663 ) AS x (objschema, objname, objtype, objdtdid, objtypeid)
1665 WHERE n.oid = x.objschema
1666 AND at.oid = x.objtypeid
1667 AND (at.typelem <> 0 AND at.typlen = -1)
1668 AND at.typelem = bt.oid
1669 AND nbt.oid = bt.typnamespace
1671 AND (n.nspname, x.objname, x.objtype, x.objtypeid) IN
1672 ( SELECT object_schema, object_name, object_type, dtd_identifier
1673 FROM data_type_privileges );
1675 GRANT SELECT ON element_types TO PUBLIC;