2 * SQL Information Schema
3 * as defined in ISO 9075-2:1999 chapter 20
5 * Copyright 2003, PostgreSQL Global Development Group
7 * $PostgreSQL: pgsql/src/backend/catalog/information_schema.sql,v 1.18 2003/12/07 10:21:58 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(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 (1042, 1043) AND t.typtypmod <> -1
264 THEN t.typtypmod - 4 /* char, varchar */
265 WHEN t.typbasetype IN (1560, 1562) AND t.typtypmod <> -1
266 THEN t.typtypmod /* bit, varbit */
269 CASE WHEN a.atttypid IN (1042, 1043) AND a.atttypmod <> -1
271 WHEN a.atttypid IN (1560, 1562) AND a.atttypmod <> -1
276 AS character_maximum_length,
279 CASE WHEN t.typtype = 'd' THEN
280 CASE WHEN t.typbasetype IN (25, 1042, 1043) THEN 2^30 ELSE null END
282 CASE WHEN a.atttypid IN (25, 1042, 1043) THEN 2^30 ELSE null END
285 AS character_octet_length,
288 CASE (CASE WHEN t.typtype = 'd' THEN t.typbasetype ELSE a.atttypid END)
289 WHEN 21 /*int2*/ THEN 16
290 WHEN 23 /*int4*/ THEN 32
291 WHEN 20 /*int8*/ THEN 64
292 WHEN 1700 /*numeric*/ THEN ((CASE WHEN t.typtype = 'd' THEN t.typtypmod ELSE a.atttypmod END - 4) >> 16) & 65535
293 WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/
294 WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/
297 AS numeric_precision,
300 CASE WHEN t.typtype = 'd' THEN
301 CASE WHEN t.typbasetype IN (21, 23, 20, 700, 701) THEN 2
302 WHEN t.typbasetype IN (1700) THEN 10
305 CASE WHEN a.atttypid IN (21, 23, 20, 700, 701) THEN 2
306 WHEN a.atttypid IN (1700) THEN 10
310 AS numeric_precision_radix,
313 CASE WHEN t.typtype = 'd' THEN
314 CASE WHEN t.typbasetype IN (21, 23, 20) THEN 0
315 WHEN t.typbasetype IN (1700) THEN (t.typtypmod - 4) & 65535
318 CASE WHEN a.atttypid IN (21, 23, 20) THEN 0
319 WHEN a.atttypid IN (1700) THEN (a.atttypmod - 4) & 65535
326 CASE WHEN t.typtype = 'd' THEN
327 CASE WHEN t.typbasetype IN (1083, 1114, 1184, 1266)
328 THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod ELSE null END)
329 WHEN t.typbasetype IN (1186)
330 THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod & 65535 ELSE null END)
333 CASE WHEN a.atttypid IN (1083, 1114, 1184, 1266)
334 THEN (CASE WHEN a.atttypmod <> -1 THEN a.atttypmod ELSE null END)
335 WHEN a.atttypid IN (1186)
336 THEN (CASE WHEN a.atttypmod <> -1 THEN a.atttypmod & 65535 ELSE null END)
340 AS datetime_precision,
342 CAST(null AS character_data) AS interval_type, -- XXX
343 CAST(null AS character_data) AS interval_precision, -- XXX
345 CAST(null AS sql_identifier) AS character_set_catalog,
346 CAST(null AS sql_identifier) AS character_set_schema,
347 CAST(null AS sql_identifier) AS character_set_name,
349 CAST(null AS sql_identifier) AS collation_catalog,
350 CAST(null AS sql_identifier) AS collation_schema,
351 CAST(null AS sql_identifier) AS collation_name,
353 CAST(CASE WHEN t.typtype = 'd' THEN current_database() ELSE null END
354 AS sql_identifier) AS domain_catalog,
355 CAST(CASE WHEN t.typtype = 'd' THEN nt.nspname ELSE null END
356 AS sql_identifier) AS domain_schema,
357 CAST(CASE WHEN t.typtype = 'd' THEN t.typname ELSE null END
358 AS sql_identifier) AS domain_name,
360 CAST(current_database() AS sql_identifier) AS udt_catalog,
361 CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,
362 CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,
364 CAST(null AS sql_identifier) AS scope_catalog,
365 CAST(null AS sql_identifier) AS scope_schema,
366 CAST(null AS sql_identifier) AS scope_name,
368 CAST(null AS cardinal_number) AS maximum_cardinality,
369 CAST(a.attnum AS sql_identifier) AS dtd_identifier,
370 CAST('NO' AS character_data) AS is_self_referencing
372 FROM (pg_attribute LEFT JOIN pg_attrdef ON attrelid = adrelid AND attnum = adnum) AS a,
373 pg_class c, pg_namespace nc, pg_user u,
374 (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
375 LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
376 ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
378 WHERE a.attrelid = c.oid
379 AND a.atttypid = t.oid
380 AND u.usesysid = c.relowner
381 AND nc.oid = c.relnamespace
383 AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
385 AND (u.usename = current_user
386 OR has_table_privilege(c.oid, 'SELECT')
387 OR has_table_privilege(c.oid, 'INSERT')
388 OR has_table_privilege(c.oid, 'UPDATE')
389 OR has_table_privilege(c.oid, 'REFERENCES') );
391 GRANT SELECT ON columns TO PUBLIC;
396 * CONSTRAINT_COLUMN_USAGE view
399 /* This returns the integers from 1 to INDEX_MAX_KEYS/FUNC_MAX_ARGS */
400 CREATE FUNCTION _pg_keypositions() RETURNS SETOF integer
403 AS 'select 1 union all select 2 union all select 3 union all
404 select 4 union all select 5 union all select 6 union all
405 select 7 union all select 8 union all select 9 union all
406 select 10 union all select 11 union all select 12 union all
407 select 13 union all select 14 union all select 15 union all
408 select 16 union all select 17 union all select 18 union all
409 select 19 union all select 20 union all select 21 union all
410 select 22 union all select 23 union all select 24 union all
411 select 25 union all select 26 union all select 27 union all
412 select 28 union all select 29 union all select 30 union all
413 select 31 union all select 32';
415 CREATE VIEW constraint_column_usage AS
416 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
417 CAST(tblschema AS sql_identifier) AS table_schema,
418 CAST(tblname AS sql_identifier) AS table_name,
419 CAST(colname AS sql_identifier) AS column_name,
420 CAST(current_database() AS sql_identifier) AS constraint_catalog,
421 CAST(cstrschema AS sql_identifier) AS constraint_schema,
422 CAST(cstrname AS sql_identifier) AS constraint_name
425 /* check constraints */
426 SELECT DISTINCT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
427 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_depend d, pg_namespace nc, pg_constraint c
428 WHERE nr.oid = r.relnamespace
429 AND r.oid = a.attrelid
430 AND d.refclassid = 'pg_catalog.pg_class'::regclass
431 AND d.refobjid = r.oid
432 AND d.refobjsubid = a.attnum
433 AND d.classid = 'pg_catalog.pg_constraint'::regclass
435 AND c.connamespace = nc.oid
439 AND NOT a.attisdropped
443 /* unique/primary key/foreign key constraints */
444 SELECT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
445 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc,
446 pg_constraint c, _pg_keypositions() AS pos(n)
447 WHERE nr.oid = r.relnamespace
448 AND r.oid = a.attrelid
449 AND r.oid = c.conrelid
450 AND nc.oid = c.connamespace
451 AND (CASE WHEN c.contype = 'f' THEN c.confkey[pos.n] = a.attnum
452 ELSE c.conkey[pos.n] = a.attnum END)
454 AND NOT a.attisdropped
455 AND c.contype IN ('p', 'u', 'f')
458 ) AS x (tblschema, tblname, tblowner, colname, cstrschema, cstrname),
461 WHERE x.tblowner = u.usesysid AND u.usename = current_user;
463 GRANT SELECT ON constraint_column_usage TO PUBLIC;
468 * CONSTRAINT_TABLE_USAGE view
471 CREATE VIEW constraint_table_usage AS
472 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
473 CAST(nr.nspname AS sql_identifier) AS table_schema,
474 CAST(r.relname AS sql_identifier) AS table_name,
475 CAST(current_database() AS sql_identifier) AS constraint_catalog,
476 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
477 CAST(c.conname AS sql_identifier) AS constraint_name
479 FROM pg_constraint c, pg_namespace nc,
480 pg_class r, pg_namespace nr,
483 WHERE c.connamespace = nc.oid AND r.relnamespace = nr.oid
484 AND ( (c.contype = 'f' AND c.confrelid = r.oid)
485 OR (c.contype IN ('p', 'u') AND c.conrelid = r.oid) )
487 AND r.relowner = u.usesysid AND u.usename = current_user;
489 GRANT SELECT ON constraint_table_usage TO PUBLIC;
492 -- 20.21 DATA_TYPE_PRIVILEGES view appears later.
497 * DOMAIN_CONSTRAINTS view
500 CREATE VIEW domain_constraints AS
501 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
502 CAST(rs.nspname AS sql_identifier) AS constraint_schema,
503 CAST(con.conname AS sql_identifier) AS constraint_name,
504 CAST(current_database() AS sql_identifier) AS domain_catalog,
505 CAST(n.nspname AS sql_identifier) AS domain_schema,
506 CAST(t.typname AS sql_identifier) AS domain_name,
507 CAST(CASE WHEN condeferrable THEN 'YES' ELSE 'NO' END
508 AS character_data) AS is_deferrable,
509 CAST(CASE WHEN condeferred THEN 'YES' ELSE 'NO' END
510 AS character_data) AS initially_deferred
511 FROM pg_namespace rs, pg_namespace n, pg_constraint con, pg_type t, pg_user u
512 WHERE rs.oid = con.connamespace
513 AND n.oid = t.typnamespace
514 AND u.usesysid = t.typowner
515 AND u.usename = current_user
516 AND t.oid = con.contypid;
518 GRANT SELECT ON domain_constraints TO PUBLIC;
523 * DOMAIN_UDT_USAGE view
526 CREATE VIEW domain_udt_usage AS
527 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
528 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
529 CAST(bt.typname AS sql_identifier) AS udt_name,
530 CAST(current_database() AS sql_identifier) AS domain_catalog,
531 CAST(nt.nspname AS sql_identifier) AS domain_schema,
532 CAST(t.typname AS sql_identifier) AS domain_name
534 FROM pg_type t, pg_namespace nt,
535 pg_type bt, pg_namespace nbt,
538 WHERE t.typnamespace = nt.oid
539 AND t.typbasetype = bt.oid
540 AND bt.typnamespace = nbt.oid
542 AND bt.typowner = u.usesysid
543 AND u.usename = current_user;
545 GRANT SELECT ON domain_udt_usage TO PUBLIC;
553 CREATE VIEW domains AS
554 SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
555 CAST(nt.nspname AS sql_identifier) AS domain_schema,
556 CAST(t.typname AS sql_identifier) AS domain_name,
559 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
560 WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
561 ELSE 'USER-DEFINED' END
566 CASE WHEN t.typbasetype IN (1042, 1043) AND t.typtypmod <> -1
567 THEN t.typtypmod - 4 /* char, varchar */
568 WHEN t.typbasetype IN (1560, 1562) AND t.typtypmod <> -1
569 THEN t.typtypmod /* bit, varbit */
572 AS character_maximum_length,
575 CASE WHEN t.typbasetype IN (25, 1042, 1043) THEN 2^30 ELSE null END
577 AS character_octet_length,
579 CAST(null AS sql_identifier) AS character_set_catalog,
580 CAST(null AS sql_identifier) AS character_set_schema,
581 CAST(null AS sql_identifier) AS character_set_name,
583 CAST(null AS sql_identifier) AS collation_catalog,
584 CAST(null AS sql_identifier) AS collation_schema,
585 CAST(null AS sql_identifier) AS collation_name,
589 WHEN 21 /*int2*/ THEN 16
590 WHEN 23 /*int4*/ THEN 32
591 WHEN 20 /*int8*/ THEN 64
592 WHEN 1700 /*numeric*/ THEN ((t.typtypmod - 4) >> 16) & 65535
593 WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/
594 WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/
597 AS numeric_precision,
600 CASE WHEN t.typbasetype IN (21, 23, 20, 700, 701) THEN 2
601 WHEN t.typbasetype IN (1700) THEN 10
604 AS numeric_precision_radix,
607 CASE WHEN t.typbasetype IN (21, 23, 20) THEN 0
608 WHEN t.typbasetype IN (1700) THEN (t.typtypmod - 4) & 65535
614 CASE WHEN t.typbasetype IN (1083, 1114, 1184, 1266)
615 THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod ELSE null END)
616 WHEN t.typbasetype IN (1186)
617 THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod & 65535 ELSE null END)
620 AS datetime_precision,
622 CAST(null AS character_data) AS interval_type, -- XXX
623 CAST(null AS character_data) AS interval_precision, -- XXX
625 CAST(t.typdefault AS character_data) AS domain_default,
627 CAST(current_database() AS sql_identifier) AS udt_catalog,
628 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
629 CAST(bt.typname AS sql_identifier) AS udt_name,
631 CAST(null AS sql_identifier) AS scope_catalog,
632 CAST(null AS sql_identifier) AS scope_schema,
633 CAST(null AS sql_identifier) AS scope_name,
635 CAST(null AS cardinal_number) AS maximum_cardinality,
636 CAST(1 AS sql_identifier) AS dtd_identifier
638 FROM pg_type t, pg_namespace nt,
639 pg_type bt, pg_namespace nbt
641 WHERE t.typnamespace = nt.oid
642 AND t.typbasetype = bt.oid
643 AND bt.typnamespace = nbt.oid
646 GRANT SELECT ON domains TO PUBLIC;
649 -- 20.27 ELEMENT_TYPES view appears later.
657 CREATE VIEW enabled_roles AS
658 SELECT CAST(g.groname AS sql_identifier) AS role_name
659 FROM pg_group g, pg_user u
660 WHERE u.usesysid = ANY (g.grolist)
661 AND u.usename = current_user;
663 GRANT SELECT ON enabled_roles TO PUBLIC;
668 * KEY_COLUMN_USAGE view
671 CREATE VIEW key_column_usage AS
672 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
673 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
674 CAST(c.conname AS sql_identifier) AS constraint_name,
675 CAST(current_database() AS sql_identifier) AS table_catalog,
676 CAST(nr.nspname AS sql_identifier) AS table_schema,
677 CAST(r.relname AS sql_identifier) AS table_name,
678 CAST(a.attname AS sql_identifier) AS column_name,
679 CAST(pos.n AS cardinal_number) AS ordinal_position
681 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc,
682 pg_constraint c, pg_user u, _pg_keypositions() AS pos(n)
683 WHERE nr.oid = r.relnamespace
684 AND r.oid = a.attrelid
685 AND r.oid = c.conrelid
686 AND nc.oid = c.connamespace
687 AND c.conkey[pos.n] = a.attnum
689 AND NOT a.attisdropped
690 AND c.contype IN ('p', 'u', 'f')
692 AND r.relowner = u.usesysid
693 AND u.usename = current_user;
695 GRANT SELECT ON key_column_usage TO PUBLIC;
703 CREATE VIEW parameters AS
704 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
705 CAST(n.nspname AS sql_identifier) AS specific_schema,
706 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
707 CAST(pos.n AS cardinal_number) AS ordinal_position,
708 CAST('IN' AS character_data) AS parameter_mode,
709 CAST('NO' AS character_data) AS is_result,
710 CAST('NO' AS character_data) AS as_locator,
711 CAST(null AS sql_identifier) AS parameter_name,
713 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
714 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
715 ELSE 'USER-DEFINED' END AS character_data)
717 CAST(null AS cardinal_number) AS character_maximum_length,
718 CAST(null AS cardinal_number) AS character_octet_length,
719 CAST(null AS sql_identifier) AS character_set_catalog,
720 CAST(null AS sql_identifier) AS character_set_schema,
721 CAST(null AS sql_identifier) AS character_set_name,
722 CAST(null AS sql_identifier) AS collation_catalog,
723 CAST(null AS sql_identifier) AS collation_schema,
724 CAST(null AS sql_identifier) AS collation_name,
725 CAST(null AS cardinal_number) AS numeric_precision,
726 CAST(null AS cardinal_number) AS numeric_precision_radix,
727 CAST(null AS cardinal_number) AS numeric_scale,
728 CAST(null AS cardinal_number) AS datetime_precision,
729 CAST(null AS character_data) AS interval_type,
730 CAST(null AS character_data) AS interval_precision,
731 CAST(current_database() AS sql_identifier) AS udt_catalog,
732 CAST(nt.nspname AS sql_identifier) AS udt_schema,
733 CAST(t.typname AS sql_identifier) AS udt_name,
734 CAST(null AS sql_identifier) AS scope_catalog,
735 CAST(null AS sql_identifier) AS scope_schema,
736 CAST(null AS sql_identifier) AS scope_name,
737 CAST(null AS cardinal_number) AS maximum_cardinality,
738 CAST(pos.n AS sql_identifier) AS dtd_identifier
740 FROM pg_namespace n, pg_proc p, pg_type t, pg_namespace nt, pg_user u,
741 _pg_keypositions() AS pos(n)
743 WHERE n.oid = p.pronamespace AND p.pronargs >= pos.n
744 AND p.proargtypes[pos.n-1] = t.oid AND t.typnamespace = nt.oid
745 AND p.proowner = u.usesysid
746 AND (u.usename = current_user OR has_function_privilege(p.oid, 'EXECUTE'));
748 GRANT SELECT ON parameters TO PUBLIC;
753 * REFERENTIAL_CONSTRAINTS view
756 CREATE FUNCTION _pg_keyissubset(smallint[], smallint[]) RETURNS boolean
759 RETURNS NULL ON NULL INPUT
760 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))';
762 CREATE FUNCTION _pg_keysequal(smallint[], smallint[]) RETURNS boolean
765 RETURNS NULL ON NULL INPUT
766 AS 'select information_schema._pg_keyissubset($1, $2) and information_schema._pg_keyissubset($2, $1)';
768 CREATE VIEW referential_constraints AS
769 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
770 CAST(ncon.nspname AS sql_identifier) AS constraint_schema,
771 CAST(con.conname AS sql_identifier) AS constraint_name,
773 CASE WHEN npkc.nspname IS NULL THEN NULL
774 ELSE current_database() END
775 AS sql_identifier) AS unique_constraint_catalog,
776 CAST(npkc.nspname AS sql_identifier) AS unique_constraint_schema,
777 CAST(pkc.conname AS sql_identifier) AS unique_constraint_name,
780 CASE con.confmatchtype WHEN 'f' THEN 'FULL'
781 WHEN 'p' THEN 'PARTIAL'
782 WHEN 'u' THEN 'NONE' END
783 AS character_data) AS match_option,
786 CASE con.confupdtype WHEN 'c' THEN 'CASCADE'
787 WHEN 'n' THEN 'SET NULL'
788 WHEN 'd' THEN 'SET DEFAULT'
789 WHEN 'r' THEN 'RESTRICT'
790 WHEN 'a' THEN 'NO ACTION' END
791 AS character_data) AS update_rule,
794 CASE con.confdeltype WHEN 'c' THEN 'CASCADE'
795 WHEN 'n' THEN 'SET NULL'
796 WHEN 'd' THEN 'SET DEFAULT'
797 WHEN 'r' THEN 'RESTRICT'
798 WHEN 'a' THEN 'NO ACTION' END
799 AS character_data) AS delete_rule
801 FROM (pg_namespace ncon INNER JOIN pg_constraint con ON ncon.oid = con.connamespace
802 INNER JOIN pg_class c ON con.conrelid = c.oid
803 INNER JOIN pg_user u ON c.relowner = u.usesysid)
805 (pg_constraint pkc INNER JOIN pg_namespace npkc ON pkc.connamespace = npkc.oid)
806 ON con.confrelid = pkc.conrelid AND _pg_keysequal(con.confkey, pkc.conkey)
808 WHERE c.relkind = 'r'
809 AND con.contype = 'f'
810 AND (pkc.contype IN ('p', 'u') OR pkc.contype IS NULL)
811 AND u.usename = current_user;
813 GRANT SELECT ON referential_constraints TO PUBLIC;
818 * ROLE_COLUMN_GRANTS view
821 CREATE VIEW role_column_grants AS
822 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
823 CAST(g_grantee.groname AS sql_identifier) AS grantee,
824 CAST(current_database() AS sql_identifier) AS table_catalog,
825 CAST(nc.nspname AS sql_identifier) AS table_schema,
826 CAST(c.relname AS sql_identifier) AS table_name,
827 CAST(a.attname AS sql_identifier) AS column_name,
828 CAST(pr.type AS character_data) AS privilege_type,
830 CASE WHEN aclcontains(c.relacl,
831 makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, pr.type, true))
832 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
839 (SELECT 'SELECT' UNION ALL
840 SELECT 'INSERT' UNION ALL
841 SELECT 'UPDATE' UNION ALL
842 SELECT 'REFERENCES') AS pr (type)
844 WHERE a.attrelid = c.oid
845 AND c.relnamespace = nc.oid
847 AND NOT a.attisdropped
848 AND c.relkind IN ('r', 'v')
849 AND aclcontains(c.relacl,
850 makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, pr.type, false))
851 AND g_grantee.groname IN (SELECT role_name FROM enabled_roles);
853 GRANT SELECT ON role_column_grants TO PUBLIC;
858 * ROLE_ROUTINE_GRANTS view
861 CREATE VIEW role_routine_grants AS
862 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
863 CAST(g_grantee.groname AS sql_identifier) AS grantee,
864 CAST(current_database() AS sql_identifier) AS specific_catalog,
865 CAST(n.nspname AS sql_identifier) AS specific_schema,
866 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
867 CAST(current_database() AS sql_identifier) AS routine_catalog,
868 CAST(n.nspname AS sql_identifier) AS routine_schema,
869 CAST(p.proname AS sql_identifier) AS routine_name,
870 CAST('EXECUTE' AS character_data) AS privilege_type,
872 CASE WHEN aclcontains(p.proacl,
873 makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, 'EXECUTE', true))
874 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
881 WHERE p.pronamespace = n.oid
882 AND aclcontains(p.proacl,
883 makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, 'EXECUTE', false))
884 AND g_grantee.groname IN (SELECT role_name FROM enabled_roles);
886 GRANT SELECT ON role_routine_grants TO PUBLIC;
891 * ROLE_TABLE_GRANTS view
894 CREATE VIEW role_table_grants AS
895 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
896 CAST(g_grantee.groname AS sql_identifier) AS grantee,
897 CAST(current_database() AS sql_identifier) AS table_catalog,
898 CAST(nc.nspname AS sql_identifier) AS table_schema,
899 CAST(c.relname AS sql_identifier) AS table_name,
900 CAST(pr.type AS character_data) AS privilege_type,
902 CASE WHEN aclcontains(c.relacl,
903 makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, pr.type, true))
904 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable,
905 CAST('NO' AS character_data) AS with_hierarchy
911 (SELECT 'SELECT' UNION ALL
912 SELECT 'DELETE' UNION ALL
913 SELECT 'INSERT' UNION ALL
914 SELECT 'UPDATE' UNION ALL
915 SELECT 'REFERENCES' UNION ALL
916 SELECT 'RULE' UNION ALL
917 SELECT 'TRIGGER') AS pr (type)
919 WHERE c.relnamespace = nc.oid
920 AND c.relkind IN ('r', 'v')
921 AND aclcontains(c.relacl,
922 makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, pr.type, false))
923 AND g_grantee.groname IN (SELECT role_name FROM enabled_roles);
925 GRANT SELECT ON role_table_grants TO PUBLIC;
930 * ROLE_USAGE_GRANTS view
933 -- See USAGE_PRIVILEGES.
935 CREATE VIEW role_usage_grants AS
936 SELECT CAST(null AS sql_identifier) AS grantor,
937 CAST(null AS sql_identifier) AS grantee,
938 CAST(current_database() AS sql_identifier) AS object_catalog,
939 CAST(null AS sql_identifier) AS object_schema,
940 CAST(null AS sql_identifier) AS object_name,
941 CAST(null AS character_data) AS object_type,
942 CAST('USAGE' AS character_data) AS privilege_type,
943 CAST(null AS character_data) AS is_grantable
947 GRANT SELECT ON role_usage_grants TO PUBLIC;
952 * ROUTINE_PRIVILEGES view
955 CREATE VIEW routine_privileges AS
956 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
957 CAST(grantee.name AS sql_identifier) AS grantee,
958 CAST(current_database() AS sql_identifier) AS specific_catalog,
959 CAST(n.nspname AS sql_identifier) AS specific_schema,
960 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
961 CAST(current_database() AS sql_identifier) AS routine_catalog,
962 CAST(n.nspname AS sql_identifier) AS routine_schema,
963 CAST(p.proname AS sql_identifier) AS routine_name,
964 CAST('EXECUTE' AS character_data) AS privilege_type,
966 CASE WHEN aclcontains(p.proacl,
967 makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, 'EXECUTE', true))
968 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
974 SELECT usesysid, 0, usename FROM pg_user
976 SELECT 0, grosysid, groname FROM pg_group
978 SELECT 0, 0, 'PUBLIC'
979 ) AS grantee (usesysid, grosysid, name)
981 WHERE p.pronamespace = n.oid
982 AND aclcontains(p.proacl,
983 makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, 'EXECUTE', false))
984 AND (u_grantor.usename = current_user
985 OR grantee.name = current_user
986 OR grantee.name = 'PUBLIC');
988 GRANT SELECT ON routine_privileges TO PUBLIC;
996 CREATE VIEW routines AS
997 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
998 CAST(n.nspname AS sql_identifier) AS specific_schema,
999 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1000 CAST(current_database() AS sql_identifier) AS routine_catalog,
1001 CAST(n.nspname AS sql_identifier) AS routine_schema,
1002 CAST(p.proname AS sql_identifier) AS routine_name,
1003 CAST('FUNCTION' AS character_data) AS routine_type,
1004 CAST(null AS sql_identifier) AS module_catalog,
1005 CAST(null AS sql_identifier) AS module_schema,
1006 CAST(null AS sql_identifier) AS module_name,
1007 CAST(null AS sql_identifier) AS udt_catalog,
1008 CAST(null AS sql_identifier) AS udt_schema,
1009 CAST(null AS sql_identifier) AS udt_name,
1012 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
1013 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
1014 ELSE 'USER-DEFINED' END AS character_data)
1016 CAST(null AS cardinal_number) AS character_maximum_length,
1017 CAST(null AS cardinal_number) AS character_octet_length,
1018 CAST(null AS sql_identifier) AS character_set_catalog,
1019 CAST(null AS sql_identifier) AS character_set_schema,
1020 CAST(null AS sql_identifier) AS character_set_name,
1021 CAST(null AS sql_identifier) AS collation_catalog,
1022 CAST(null AS sql_identifier) AS collation_schema,
1023 CAST(null AS sql_identifier) AS collation_name,
1024 CAST(null AS cardinal_number) AS numeric_precision,
1025 CAST(null AS cardinal_number) AS numeric_precision_radix,
1026 CAST(null AS cardinal_number) AS numeric_scale,
1027 CAST(null AS cardinal_number) AS datetime_precision,
1028 CAST(null AS character_data) AS interval_type,
1029 CAST(null AS character_data) AS interval_precision,
1030 CAST(current_database() AS sql_identifier) AS type_udt_catalog,
1031 CAST(nt.nspname AS sql_identifier) AS type_udt_schema,
1032 CAST(t.typname AS sql_identifier) AS type_udt_name,
1033 CAST(null AS sql_identifier) AS scope_catalog,
1034 CAST(null AS sql_identifier) AS scope_schema,
1035 CAST(null AS sql_identifier) AS scope_name,
1036 CAST(null AS cardinal_number) AS maximum_cardinality,
1037 CAST(0 AS sql_identifier) AS dtd_identifier,
1039 CAST(CASE WHEN l.lanname = 'sql' THEN 'SQL' ELSE 'EXTERNAL' END AS character_data)
1042 CASE WHEN u.usename = current_user THEN p.prosrc ELSE null END
1043 AS character_data) AS routine_definition,
1045 CASE WHEN l.lanname = 'c' THEN p.prosrc ELSE null END
1046 AS character_data) AS external_name,
1047 CAST(upper(l.lanname) AS character_data) AS external_language,
1049 CAST('GENERAL' AS character_data) AS parameter_style,
1050 CAST(CASE WHEN p.provolatile = 'i' THEN 'YES' ELSE 'NO' END AS character_data) AS is_deterministic,
1051 CAST('MODIFIES' AS character_data) AS sql_data_access,
1052 CAST(CASE WHEN p.proisstrict THEN 'YES' ELSE 'NO' END AS character_data) AS is_null_call,
1053 CAST(null AS character_data) AS sql_path,
1054 CAST('YES' AS character_data) AS schema_level_routine,
1055 CAST(0 AS cardinal_number) AS max_dynamic_result_sets,
1056 CAST(null AS character_data) AS is_user_defined_cast,
1057 CAST(null AS character_data) AS is_implicitly_invocable,
1058 CAST(CASE WHEN p.prosecdef THEN 'DEFINER' ELSE 'INVOKER' END AS character_data) AS security_type,
1059 CAST(null AS sql_identifier) AS to_sql_specific_catalog,
1060 CAST(null AS sql_identifier) AS to_sql_specific_schema,
1061 CAST(null AS sql_identifier) AS to_sql_specific_name,
1062 CAST('NO' AS character_data) AS as_locator
1064 FROM pg_namespace n, pg_proc p, pg_language l, pg_user u,
1065 pg_type t, pg_namespace nt
1067 WHERE n.oid = p.pronamespace AND p.prolang = l.oid AND p.proowner = u.usesysid
1068 AND p.prorettype = t.oid AND t.typnamespace = nt.oid
1069 AND (u.usename = current_user OR has_function_privilege(p.oid, 'EXECUTE'));
1071 GRANT SELECT ON routines TO PUBLIC;
1079 CREATE VIEW schemata AS
1080 SELECT CAST(current_database() AS sql_identifier) AS catalog_name,
1081 CAST(n.nspname AS sql_identifier) AS schema_name,
1082 CAST(u.usename AS sql_identifier) AS schema_owner,
1083 CAST(null AS sql_identifier) AS default_character_set_catalog,
1084 CAST(null AS sql_identifier) AS default_character_set_schema,
1085 CAST(null AS sql_identifier) AS default_character_set_name,
1086 CAST(null AS character_data) AS sql_path
1087 FROM pg_namespace n, pg_user u
1088 WHERE n.nspowner = u.usesysid AND u.usename = current_user;
1090 GRANT SELECT ON schemata TO PUBLIC;
1095 * SQL_FEATURES table
1098 CREATE TABLE sql_features (
1099 feature_id character_data,
1100 feature_name character_data,
1101 sub_feature_id character_data,
1102 sub_feature_name character_data,
1103 is_supported character_data,
1104 is_verified_by character_data,
1105 comments character_data
1108 -- Will be filled with external data by initdb.
1110 GRANT SELECT ON sql_features TO PUBLIC;
1115 * SQL_IMPLEMENTATION_INFO table
1118 -- Note: Implementation information items are defined in ISO 9075-3:1999,
1121 CREATE TABLE sql_implementation_info (
1122 implementation_info_id character_data,
1123 implementation_info_name character_data,
1124 integer_value cardinal_number,
1125 character_value character_data,
1126 comments character_data
1129 INSERT INTO sql_implementation_info VALUES ('10003', 'CATALOG NAME', NULL, 'Y', NULL);
1130 INSERT INTO sql_implementation_info VALUES ('10004', 'COLLATING SEQUENCE', NULL, '', 'not supported');
1131 INSERT INTO sql_implementation_info VALUES ('23', 'CURSOR COMMIT BEHAVIOR', 1, NULL, 'close cursors and retain prepared statements');
1132 INSERT INTO sql_implementation_info VALUES ('2', 'DATA SOURCE NAME', NULL, '', NULL);
1133 INSERT INTO sql_implementation_info VALUES ('17', 'DBMS NAME', NULL, (select trim(trailing ' ' from substring(version() from '^[^0-9]*'))), NULL);
1134 INSERT INTO sql_implementation_info VALUES ('18', 'DBMS VERSION', NULL, '???', NULL); -- filled by initdb
1135 INSERT INTO sql_implementation_info VALUES ('26', 'DEFAULT TRANSACTION ISOLATION', 2, NULL, 'READ COMMITTED; user-settable');
1136 INSERT INTO sql_implementation_info VALUES ('28', 'IDENTIFIER CASE', 3, NULL, 'stored in mixed case - case sensitive');
1137 INSERT INTO sql_implementation_info VALUES ('85', 'NULL COLLATION', 0, NULL, 'nulls higher than non-nulls');
1138 INSERT INTO sql_implementation_info VALUES ('13', 'SERVER NAME', NULL, '', NULL);
1139 INSERT INTO sql_implementation_info VALUES ('94', 'SPECIAL CHARACTERS', NULL, '', 'all non-ASCII characters allowed');
1140 INSERT INTO sql_implementation_info VALUES ('46', 'TRANSACTION CAPABLE', 2, NULL, 'both DML and DDL');
1142 GRANT SELECT ON sql_implementation_info TO PUBLIC;
1147 * SQL_LANGUAGES table
1150 CREATE TABLE sql_languages (
1151 sql_language_source character_data,
1152 sql_language_year character_data,
1153 sql_language_conformance character_data,
1154 sql_language_integrity character_data,
1155 sql_language_implementation character_data,
1156 sql_language_binding_style character_data,
1157 sql_language_programming_language character_data
1160 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'DIRECT', NULL);
1161 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
1163 GRANT SELECT ON sql_languages TO PUBLIC;
1168 * SQL_PACKAGES table
1171 CREATE TABLE sql_packages (
1172 feature_id character_data,
1173 feature_name character_data,
1174 is_supported character_data,
1175 is_verified_by character_data,
1176 comments character_data
1179 INSERT INTO sql_packages VALUES ('PKG000', 'Core', 'NO', NULL, '');
1180 INSERT INTO sql_packages VALUES ('PKG001', 'Enhanced datetime facilities', 'YES', NULL, '');
1181 INSERT INTO sql_packages VALUES ('PKG002', 'Enhanced integrity management', 'NO', NULL, '');
1182 INSERT INTO sql_packages VALUES ('PKG003', 'OLAP facilities', 'NO', NULL, '');
1183 INSERT INTO sql_packages VALUES ('PKG004', 'PSM', 'NO', NULL, 'PL/pgSQL is similar.');
1184 INSERT INTO sql_packages VALUES ('PKG005', 'CLI', 'NO', NULL, 'ODBC is similar.');
1185 INSERT INTO sql_packages VALUES ('PKG006', 'Basic object support', 'NO', NULL, '');
1186 INSERT INTO sql_packages VALUES ('PKG007', 'Enhanced object support', 'NO', NULL, '');
1187 INSERT INTO sql_packages VALUES ('PKG008', 'Active database', 'NO', NULL, '');
1188 INSERT INTO sql_packages VALUES ('PKG009', 'SQL/MM support', 'NO', NULL, '');
1190 GRANT SELECT ON sql_packages TO PUBLIC;
1198 -- Note: Sizing items are defined in ISO 9075-3:1999, clause 7.2.
1200 CREATE TABLE sql_sizing (
1201 sizing_id cardinal_number,
1202 sizing_name character_data,
1203 supported_value cardinal_number,
1204 comments character_data
1207 INSERT INTO sql_sizing VALUES (34, 'MAXIMUM CATALOG NAME LENGTH', 63, NULL);
1208 INSERT INTO sql_sizing VALUES (30, 'MAXIMUM COLUMN NAME LENGTH', 63, NULL);
1209 INSERT INTO sql_sizing VALUES (97, 'MAXIMUM COLUMNS IN GROUP BY', 0, NULL);
1210 INSERT INTO sql_sizing VALUES (99, 'MAXIMUM COLUMNS IN ORDER BY', 0, NULL);
1211 INSERT INTO sql_sizing VALUES (100, 'MAXIMUM COLUMNS IN SELECT', 1664, NULL); -- match MaxTupleAttributeNumber
1212 INSERT INTO sql_sizing VALUES (101, 'MAXIMUM COLUMNS IN TABLE', 1600, NULL); -- match MaxHeapAttributeNumber
1213 INSERT INTO sql_sizing VALUES (1, 'MAXIMUM CONCURRENT ACTIVITIES', 0, NULL);
1214 INSERT INTO sql_sizing VALUES (31, 'MAXIMUM CURSOR NAME LENGTH', 63, NULL);
1215 INSERT INTO sql_sizing VALUES (0, 'MAXIMUM DRIVER CONNECTIONS', NULL, NULL);
1216 INSERT INTO sql_sizing VALUES (10005, 'MAXIMUM IDENTIFIER LENGTH', 63, NULL);
1217 INSERT INTO sql_sizing VALUES (32, 'MAXIMUM SCHEMA NAME LENGTH', 63, NULL);
1218 INSERT INTO sql_sizing VALUES (20000, 'MAXIMUM STATEMENT OCTETS', 0, NULL);
1219 INSERT INTO sql_sizing VALUES (20001, 'MAXIMUM STATEMENT OCTETS DATA', 0, NULL);
1220 INSERT INTO sql_sizing VALUES (20002, 'MAXIMUM STATEMENT OCTETS SCHEMA', 0, NULL);
1221 INSERT INTO sql_sizing VALUES (35, 'MAXIMUM TABLE NAME LENGTH', 63, NULL);
1222 INSERT INTO sql_sizing VALUES (106, 'MAXIMUM TABLES IN SELECT', 0, NULL);
1223 INSERT INTO sql_sizing VALUES (107, 'MAXIMUM USER NAME LENGTH', 63, NULL);
1224 INSERT INTO sql_sizing VALUES (25000, 'MAXIMUM CURRENT DEFAULT TRANSFORM GROUP LENGTH', NULL, NULL);
1225 INSERT INTO sql_sizing VALUES (25001, 'MAXIMUM CURRENT TRANSFORM GROUP LENGTH', NULL, NULL);
1226 INSERT INTO sql_sizing VALUES (25002, 'MAXIMUM CURRENT PATH LENGTH', 0, NULL);
1227 INSERT INTO sql_sizing VALUES (25003, 'MAXIMUM CURRENT ROLE LENGTH', NULL, NULL);
1228 INSERT INTO sql_sizing VALUES (25004, 'MAXIMUM SESSION USER LENGTH', 63, NULL);
1229 INSERT INTO sql_sizing VALUES (25005, 'MAXIMUM SYSTEM USER LENGTH', 63, NULL);
1232 SET supported_value = (SELECT typlen-1 FROM pg_catalog.pg_type WHERE typname = 'name'),
1233 comments = 'Might be less, depending on character set.'
1234 WHERE supported_value = 63;
1236 GRANT SELECT ON sql_sizing TO PUBLIC;
1241 * SQL_SIZING_PROFILES table
1244 -- The data in this table are defined by various profiles of SQL.
1245 -- Since we don't have any information about such profiles, we provide
1248 CREATE TABLE sql_sizing_profiles (
1249 sizing_id cardinal_number,
1250 sizing_name character_data,
1251 profile_id character_data,
1252 required_value cardinal_number,
1253 comments character_data
1256 GRANT SELECT ON sql_sizing_profiles TO PUBLIC;
1261 * TABLE_CONSTRAINTS view
1264 CREATE VIEW table_constraints AS
1265 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1266 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
1267 CAST(c.conname AS sql_identifier) AS constraint_name,
1268 CAST(current_database() AS sql_identifier) AS table_catalog,
1269 CAST(nr.nspname AS sql_identifier) AS table_schema,
1270 CAST(r.relname AS sql_identifier) AS table_name,
1272 CASE c.contype WHEN 'c' THEN 'CHECK'
1273 WHEN 'f' THEN 'FOREIGN KEY'
1274 WHEN 'p' THEN 'PRIMARY KEY'
1275 WHEN 'u' THEN 'UNIQUE' END
1276 AS character_data) AS constraint_type,
1277 CAST(CASE WHEN c.condeferrable THEN 'YES' ELSE 'NO' END AS character_data)
1279 CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS character_data)
1280 AS initially_deferred
1282 FROM pg_namespace nc,
1288 WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace
1289 AND c.conrelid = r.oid AND r.relowner = u.usesysid
1291 AND u.usename = current_user;
1293 -- FIMXE: Not-null constraints are missing here.
1295 GRANT SELECT ON table_constraints TO PUBLIC;
1300 * TABLE_PRIVILEGES view
1303 CREATE VIEW table_privileges AS
1304 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
1305 CAST(grantee.name AS sql_identifier) AS grantee,
1306 CAST(current_database() AS sql_identifier) AS table_catalog,
1307 CAST(nc.nspname AS sql_identifier) AS table_schema,
1308 CAST(c.relname AS sql_identifier) AS table_name,
1309 CAST(pr.type AS character_data) AS privilege_type,
1311 CASE WHEN aclcontains(c.relacl,
1312 makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr.type, true))
1313 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable,
1314 CAST('NO' AS character_data) AS with_hierarchy
1320 SELECT usesysid, 0, usename FROM pg_user
1322 SELECT 0, grosysid, groname FROM pg_group
1324 SELECT 0, 0, 'PUBLIC'
1325 ) AS grantee (usesysid, grosysid, name),
1326 (SELECT 'SELECT' UNION ALL
1327 SELECT 'DELETE' UNION ALL
1328 SELECT 'INSERT' UNION ALL
1329 SELECT 'UPDATE' UNION ALL
1330 SELECT 'REFERENCES' UNION ALL
1331 SELECT 'RULE' UNION ALL
1332 SELECT 'TRIGGER') AS pr (type)
1334 WHERE c.relnamespace = nc.oid
1335 AND c.relkind IN ('r', 'v')
1336 AND aclcontains(c.relacl,
1337 makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr.type, false))
1338 AND (u_grantor.usename = current_user
1339 OR grantee.name = current_user
1340 OR grantee.name = 'PUBLIC');
1342 GRANT SELECT ON table_privileges TO PUBLIC;
1350 CREATE VIEW tables AS
1351 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
1352 CAST(nc.nspname AS sql_identifier) AS table_schema,
1353 CAST(c.relname AS sql_identifier) AS table_name,
1356 CASE WHEN nc.nspname LIKE 'pg!_temp!_%' ESCAPE '!' THEN 'LOCAL TEMPORARY'
1357 WHEN c.relkind = 'r' THEN 'BASE TABLE'
1358 WHEN c.relkind = 'v' THEN 'VIEW'
1360 AS character_data) AS table_type,
1362 CAST(null AS sql_identifier) AS self_referencing_column_name,
1363 CAST(null AS character_data) AS reference_generation,
1365 CAST(null AS sql_identifier) AS user_defined_type_catalog,
1366 CAST(null AS sql_identifier) AS user_defined_type_schema,
1367 CAST(null AS sql_identifier) AS user_defined_name
1369 FROM pg_namespace nc, pg_class c, pg_user u
1371 WHERE c.relnamespace = nc.oid AND u.usesysid = c.relowner
1372 AND c.relkind IN ('r', 'v')
1373 AND (u.usename = current_user
1374 OR has_table_privilege(c.oid, 'SELECT')
1375 OR has_table_privilege(c.oid, 'INSERT')
1376 OR has_table_privilege(c.oid, 'UPDATE')
1377 OR has_table_privilege(c.oid, 'DELETE')
1378 OR has_table_privilege(c.oid, 'RULE')
1379 OR has_table_privilege(c.oid, 'REFERENCES')
1380 OR has_table_privilege(c.oid, 'TRIGGER') );
1382 GRANT SELECT ON tables TO PUBLIC;
1387 * TRIGGERED_UPDATE_COLUMNS view
1390 -- PostgreSQL doesn't allow the specification of individual triggered
1391 -- update columns, so this view is empty.
1393 CREATE VIEW triggered_update_columns AS
1394 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1395 CAST(null AS sql_identifier) AS trigger_schema,
1396 CAST(null AS sql_identifier) AS trigger_name,
1397 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1398 CAST(null AS sql_identifier) AS event_object_schema,
1399 CAST(null AS sql_identifier) AS event_object_table,
1400 CAST(null AS sql_identifier) AS event_object_column
1403 GRANT SELECT ON triggered_update_columns TO PUBLIC;
1411 CREATE VIEW triggers AS
1412 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1413 CAST(n.nspname AS sql_identifier) AS trigger_schema,
1414 CAST(t.tgname AS sql_identifier) AS trigger_name,
1415 CAST(em.text AS character_data) AS event_manipulation,
1416 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1417 CAST(n.nspname AS sql_identifier) AS event_object_schema,
1418 CAST(c.relname AS sql_identifier) AS event_object_table,
1419 CAST(null AS cardinal_number) AS action_order,
1420 CAST(null AS character_data) AS action_condition,
1422 substring(pg_get_triggerdef(t.oid) from
1423 position('EXECUTE PROCEDURE' in substring(pg_get_triggerdef(t.oid) from 48)) + 47)
1424 AS character_data) AS action_statement,
1426 CASE WHEN t.tgtype & 1 = 1 THEN 'ROW' ELSE 'STATEMENT' END
1427 AS character_data) AS action_orientation,
1429 CASE WHEN t.tgtype & 2 = 2 THEN 'BEFORE' ELSE 'AFTER' END
1430 AS character_data) AS condition_timing,
1431 CAST(null AS sql_identifier) AS condition_reference_old_table,
1432 CAST(null AS sql_identifier) AS condition_reference_new_table
1434 FROM pg_namespace n, pg_class c, pg_trigger t, pg_user u,
1435 (SELECT 4, 'INSERT' UNION ALL
1436 SELECT 8, 'DELETE' UNION ALL
1437 SELECT 16, 'UPDATE') AS em (num, text)
1439 WHERE n.oid = c.relnamespace
1440 AND c.oid = t.tgrelid
1441 AND c.relowner = u.usesysid
1442 AND t.tgtype & em.num <> 0
1443 AND NOT t.tgisconstraint
1444 AND u.usename = current_user;
1446 GRANT SELECT ON triggers TO PUBLIC;
1451 * USAGE_PRIVILEGES view
1454 -- Of the things currently implemented in PostgreSQL, usage privileges
1455 -- apply only to domains. Since domains have no real privileges, we
1456 -- represent all domains with implicit usage privilege here.
1458 CREATE VIEW usage_privileges AS
1459 SELECT CAST(u.usename AS sql_identifier) AS grantor,
1460 CAST('PUBLIC' AS sql_identifier) AS grantee,
1461 CAST(current_database() AS sql_identifier) AS object_catalog,
1462 CAST(n.nspname AS sql_identifier) AS object_schema,
1463 CAST(t.typname AS sql_identifier) AS object_name,
1464 CAST('DOMAIN' AS character_data) AS object_type,
1465 CAST('USAGE' AS character_data) AS privilege_type,
1466 CAST('NO' AS character_data) AS is_grantable
1472 WHERE u.usesysid = t.typowner
1473 AND t.typnamespace = n.oid
1474 AND t.typtype = 'd';
1476 GRANT SELECT ON usage_privileges TO PUBLIC;
1484 CREATE VIEW view_column_usage AS
1486 CAST(current_database() AS sql_identifier) AS view_catalog,
1487 CAST(nv.nspname AS sql_identifier) AS view_schema,
1488 CAST(v.relname AS sql_identifier) AS view_name,
1489 CAST(current_database() AS sql_identifier) AS table_catalog,
1490 CAST(nt.nspname AS sql_identifier) AS table_schema,
1491 CAST(t.relname AS sql_identifier) AS table_name,
1492 CAST(a.attname AS sql_identifier) AS column_name
1494 FROM pg_user, pg_namespace nv, pg_class v, pg_depend dv,
1495 pg_depend dt, pg_class t, pg_namespace nt,
1496 pg_attribute a, pg_user u
1498 WHERE nv.oid = v.relnamespace
1500 AND v.oid = dv.refobjid
1501 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
1502 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
1503 AND dv.deptype = 'i'
1504 AND dv.objid = dt.objid
1505 AND dv.refobjid <> dt.refobjid
1506 AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
1507 AND dt.refclassid = 'pg_catalog.pg_class'::regclass
1508 AND dt.refobjid = t.oid
1509 AND t.relnamespace = nt.oid
1510 AND t.relkind IN ('r', 'v')
1511 AND t.oid = a.attrelid
1512 AND dt.refobjsubid = a.attnum
1513 AND t.relowner = u.usesysid AND u.usename = current_user;
1515 GRANT SELECT ON view_column_usage TO PUBLIC;
1523 CREATE VIEW view_table_usage AS
1525 CAST(current_database() AS sql_identifier) AS view_catalog,
1526 CAST(nv.nspname AS sql_identifier) AS view_schema,
1527 CAST(v.relname AS sql_identifier) AS view_name,
1528 CAST(current_database() AS sql_identifier) AS table_catalog,
1529 CAST(nt.nspname AS sql_identifier) AS table_schema,
1530 CAST(t.relname AS sql_identifier) AS table_name
1532 FROM pg_user, pg_namespace nv, pg_class v, pg_depend dv,
1533 pg_depend dt, pg_class t, pg_namespace nt,
1536 WHERE nv.oid = v.relnamespace
1538 AND v.oid = dv.refobjid
1539 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
1540 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
1541 AND dv.deptype = 'i'
1542 AND dv.objid = dt.objid
1543 AND dv.refobjid <> dt.refobjid
1544 AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
1545 AND dt.refclassid = 'pg_catalog.pg_class'::regclass
1546 AND dt.refobjid = t.oid
1547 AND t.relnamespace = nt.oid
1548 AND t.relkind IN ('r', 'v')
1549 AND t.relowner = u.usesysid AND u.usename = current_user;
1551 GRANT SELECT ON view_table_usage TO PUBLIC;
1559 CREATE VIEW views AS
1560 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
1561 CAST(nc.nspname AS sql_identifier) AS table_schema,
1562 CAST(c.relname AS sql_identifier) AS table_name,
1565 CASE WHEN u.usename = current_user THEN pg_get_viewdef(c.oid)
1567 AS character_data) AS view_definition,
1569 CAST('NONE' AS character_data) AS check_option,
1570 CAST(null AS character_data) AS is_updatable, -- FIXME
1571 CAST(null AS character_data) AS is_insertable_into -- FIXME
1573 FROM pg_namespace nc, pg_class c, pg_user u
1575 WHERE c.relnamespace = nc.oid AND u.usesysid = c.relowner
1577 AND (u.usename = current_user
1578 OR has_table_privilege(c.oid, 'SELECT')
1579 OR has_table_privilege(c.oid, 'INSERT')
1580 OR has_table_privilege(c.oid, 'UPDATE')
1581 OR has_table_privilege(c.oid, 'DELETE')
1582 OR has_table_privilege(c.oid, 'RULE')
1583 OR has_table_privilege(c.oid, 'REFERENCES')
1584 OR has_table_privilege(c.oid, 'TRIGGER') );
1586 GRANT SELECT ON views TO PUBLIC;
1589 -- The following views have dependencies that force them to appear out of order.
1593 * DATA_TYPE_PRIVILEGES view
1596 CREATE VIEW data_type_privileges AS
1597 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
1598 CAST(x.objschema AS sql_identifier) AS object_schema,
1599 CAST(x.objname AS sql_identifier) AS object_name,
1600 CAST(x.objtype AS character_data) AS object_type,
1601 CAST(x.objdtdid AS sql_identifier) AS dtd_identifier
1605 SELECT table_schema, table_name, 'TABLE'::text, dtd_identifier FROM columns
1607 SELECT domain_schema, domain_name, 'DOMAIN'::text, dtd_identifier FROM domains
1609 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM parameters
1611 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM routines
1612 ) AS x (objschema, objname, objtype, objdtdid);
1614 GRANT SELECT ON data_type_privileges TO PUBLIC;
1619 * ELEMENT_TYPES view
1622 CREATE VIEW element_types AS
1623 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
1624 CAST(n.nspname AS sql_identifier) AS object_schema,
1625 CAST(x.objname AS sql_identifier) AS object_name,
1626 CAST(x.objtype AS character_data) AS object_type,
1627 CAST(x.objdtdid AS sql_identifier) AS array_type_identifier,
1629 CASE WHEN nbt.nspname = 'pg_catalog' THEN format_type(bt.oid, null)
1630 ELSE 'USER-DEFINED' END AS character_data) AS data_type,
1632 CAST(null AS cardinal_number) AS character_maximum_length,
1633 CAST(null AS cardinal_number) AS character_octet_length,
1634 CAST(null AS sql_identifier) AS character_set_catalog,
1635 CAST(null AS sql_identifier) AS character_set_schema,
1636 CAST(null AS sql_identifier) AS character_set_name,
1637 CAST(null AS sql_identifier) AS collation_catalog,
1638 CAST(null AS sql_identifier) AS collation_schema,
1639 CAST(null AS sql_identifier) AS collation_name,
1640 CAST(null AS cardinal_number) AS numeric_precision,
1641 CAST(null AS cardinal_number) AS numeric_precision_radix,
1642 CAST(null AS cardinal_number) AS numeric_scale,
1643 CAST(null AS cardinal_number) AS datetime_precision,
1644 CAST(null AS character_data) AS interval_type,
1645 CAST(null AS character_data) AS interval_precision,
1647 CAST(null AS character_data) AS domain_default, -- XXX maybe a bug in the standard
1649 CAST(current_database() AS sql_identifier) AS udt_catalog,
1650 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
1651 CAST(bt.typname AS sql_identifier) AS udt_name,
1653 CAST(null AS sql_identifier) AS scope_catalog,
1654 CAST(null AS sql_identifier) AS scope_schema,
1655 CAST(null AS sql_identifier) AS scope_name,
1657 CAST(null AS cardinal_number) AS maximum_cardinality,
1658 CAST('a' || x.objdtdid AS sql_identifier) AS dtd_identifier
1660 FROM pg_namespace n, pg_type at, pg_namespace nbt, pg_type bt,
1663 SELECT c.relnamespace, CAST(c.relname AS sql_identifier),
1664 'TABLE'::text, a.attnum, a.atttypid
1665 FROM pg_class c, pg_attribute a
1666 WHERE c.oid = a.attrelid
1667 AND c.relkind IN ('r', 'v')
1668 AND attnum > 0 AND NOT attisdropped
1673 SELECT t.typnamespace, CAST(t.typname AS sql_identifier),
1674 'DOMAIN'::text, 1, t.typbasetype
1676 WHERE t.typtype = 'd'
1681 SELECT p.pronamespace, CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier),
1682 'ROUTINE'::text, pos.n, p.proargtypes[pos.n-1]
1683 FROM pg_proc p, _pg_keypositions() AS pos(n)
1684 WHERE p.pronargs >= pos.n
1689 SELECT p.pronamespace, CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier),
1690 'ROUTINE'::text, 0, p.prorettype
1693 ) AS x (objschema, objname, objtype, objdtdid, objtypeid)
1695 WHERE n.oid = x.objschema
1696 AND at.oid = x.objtypeid
1697 AND (at.typelem <> 0 AND at.typlen = -1)
1698 AND at.typelem = bt.oid
1699 AND nbt.oid = bt.typnamespace
1701 AND (n.nspname, x.objname, x.objtype, x.objdtdid) IN
1702 ( SELECT object_schema, object_name, object_type, dtd_identifier
1703 FROM data_type_privileges );
1705 GRANT SELECT ON element_types TO PUBLIC;