2 * SQL Information Schema
3 * as defined in ISO 9075-2:1999 chapter 20
5 * Copyright (c) 2003-2005, PostgreSQL Global Development Group
7 * $PostgreSQL: pgsql/src/backend/catalog/information_schema.sql,v 1.28 2005/05/31 03:36:24 tgl Exp $
11 * Note: Generally, the definitions in this file should be ordered
12 * according to the clause numbers in the SQL standard, which is also the
13 * alphabetical order. In some cases it is convenient or necessary to
14 * define one information schema view by using another one; in that case,
15 * put the referencing view at the very end and leave a note where it
16 * should have been put.
22 * INFORMATION_SCHEMA schema
25 CREATE SCHEMA information_schema;
26 GRANT USAGE ON SCHEMA information_schema TO PUBLIC;
27 SET search_path TO information_schema, public;
30 * A few supporting functions first ...
33 /* Expand any 1-D array into a set with integers 1..N */
34 CREATE FUNCTION _pg_expandarray(IN anyarray, OUT x anyelement, OUT n int)
36 LANGUAGE sql STRICT IMMUTABLE
37 AS 'select $1[s], s - pg_catalog.array_lower($1,1) + 1
38 from pg_catalog.generate_series(pg_catalog.array_lower($1,1),
39 pg_catalog.array_upper($1,1),
42 CREATE FUNCTION _pg_keyissubset(smallint[], smallint[]) RETURNS boolean
45 RETURNS NULL ON NULL INPUT
46 AS 'select $1[1] is null or ($1[1] = any ($2) and coalesce(information_schema._pg_keyissubset($1[2:pg_catalog.array_upper($1,1)], $2), true))';
48 CREATE FUNCTION _pg_keysequal(smallint[], smallint[]) RETURNS boolean
51 RETURNS NULL ON NULL INPUT
52 AS 'select information_schema._pg_keyissubset($1, $2) and information_schema._pg_keyissubset($2, $1)';
54 CREATE FUNCTION _pg_truetypid(pg_attribute, pg_type) RETURNS oid
57 RETURNS NULL ON NULL INPUT
59 $$SELECT CASE WHEN $2.typtype = 'd' THEN $2.typbasetype ELSE $1.atttypid END$$;
61 CREATE FUNCTION _pg_truetypmod(pg_attribute, pg_type) RETURNS int4
64 RETURNS NULL ON NULL INPUT
66 $$SELECT CASE WHEN $2.typtype = 'd' THEN $2.typtypmod ELSE $1.atttypmod END$$;
68 -- these functions encapsulate knowledge about the encoding of typmod:
70 CREATE FUNCTION _pg_char_max_length(typid oid, typmod int4) RETURNS integer
73 RETURNS NULL ON NULL INPUT
76 CASE WHEN $2 = -1 /* default typmod */
78 WHEN $1 IN (1042, 1043) /* char, varchar */
80 WHEN $1 IN (1560, 1562) /* bit, varbit */
85 CREATE FUNCTION _pg_char_octet_length(typid oid, typmod int4) RETURNS integer
88 RETURNS NULL ON NULL INPUT
91 CASE WHEN $1 IN (25, 1042, 1043) /* text, char, varchar */
92 THEN CAST(2^30 AS integer)
96 CREATE FUNCTION _pg_numeric_precision(typid oid, typmod int4) RETURNS integer
99 RETURNS NULL ON NULL INPUT
103 WHEN 21 /*int2*/ THEN 16
104 WHEN 23 /*int4*/ THEN 32
105 WHEN 20 /*int8*/ THEN 64
106 WHEN 1700 /*numeric*/ THEN
109 ELSE (($2 - 4) >> 16) & 65535
111 WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/
112 WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/
116 CREATE FUNCTION _pg_numeric_precision_radix(typid oid, typmod int4) RETURNS integer
119 RETURNS NULL ON NULL INPUT
122 CASE WHEN $1 IN (21, 23, 20, 700, 701) THEN 2
123 WHEN $1 IN (1700) THEN 10
127 CREATE FUNCTION _pg_numeric_scale(typid oid, typmod int4) RETURNS integer
130 RETURNS NULL ON NULL INPUT
133 CASE WHEN $1 IN (21, 23, 20) THEN 0
134 WHEN $1 IN (1700) THEN
137 ELSE ($2 - 4) & 65535
142 CREATE FUNCTION _pg_datetime_precision(typid oid, typmod int4) RETURNS integer
145 RETURNS NULL ON NULL INPUT
148 CASE WHEN $2 = -1 /* default typmod */
150 WHEN $1 IN (1083, 1114, 1184, 1266) /* time, timestamp, same + tz */
152 WHEN $1 IN (1186) /* interval */
158 -- 20.3 INFORMATION_SCHEMA_CATALOG_NAME view appears later.
163 * CARDINAL_NUMBER domain
166 CREATE DOMAIN cardinal_number AS integer
167 CONSTRAINT cardinal_number_domain_check CHECK (value >= 0);
172 * CHARACTER_DATA domain
175 CREATE DOMAIN character_data AS character varying;
180 * SQL_IDENTIFIER domain
183 CREATE DOMAIN sql_identifier AS character varying;
188 * INFORMATION_SCHEMA_CATALOG_NAME view
191 CREATE VIEW information_schema_catalog_name AS
192 SELECT CAST(current_database() AS sql_identifier) AS catalog_name;
194 GRANT SELECT ON information_schema_catalog_name TO PUBLIC;
202 CREATE DOMAIN time_stamp AS timestamp(2)
203 DEFAULT current_timestamp(2);
208 * APPLICABLE_ROLES view
211 CREATE VIEW applicable_roles AS
212 SELECT CAST(current_user AS sql_identifier) AS grantee,
213 CAST(g.groname AS sql_identifier) AS role_name,
214 CAST('NO' AS character_data) AS is_grantable
216 FROM pg_group g, pg_user u
218 WHERE u.usesysid = ANY (g.grolist)
219 AND u.usename = current_user;
221 GRANT SELECT ON applicable_roles TO PUBLIC;
226 * CHECK_CONSTRAINTS view
229 CREATE VIEW check_constraints AS
230 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
231 CAST(rs.nspname AS sql_identifier) AS constraint_schema,
232 CAST(con.conname AS sql_identifier) AS constraint_name,
233 CAST(substring(pg_get_constraintdef(con.oid) from 7) AS character_data)
235 FROM pg_namespace rs,
237 LEFT OUTER JOIN pg_class c ON (c.oid = con.conrelid)
238 LEFT OUTER JOIN pg_type t ON (t.oid = con.contypid),
240 WHERE rs.oid = con.connamespace
241 AND u.usesysid = coalesce(c.relowner, t.typowner)
242 AND u.usename = current_user
243 AND con.contype = 'c';
245 GRANT SELECT ON check_constraints TO PUBLIC;
250 * COLUMN_DOMAIN_USAGE view
253 CREATE VIEW column_domain_usage AS
254 SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
255 CAST(nt.nspname AS sql_identifier) AS domain_schema,
256 CAST(t.typname AS sql_identifier) AS domain_name,
257 CAST(current_database() AS sql_identifier) AS table_catalog,
258 CAST(nc.nspname AS sql_identifier) AS table_schema,
259 CAST(c.relname AS sql_identifier) AS table_name,
260 CAST(a.attname AS sql_identifier) AS column_name
262 FROM pg_type t, pg_namespace nt, pg_class c, pg_namespace nc,
263 pg_attribute a, pg_user u
265 WHERE t.typnamespace = nt.oid
266 AND c.relnamespace = nc.oid
267 AND a.attrelid = c.oid
268 AND a.atttypid = t.oid
269 AND t.typowner = u.usesysid
271 AND c.relkind IN ('r', 'v')
273 AND NOT a.attisdropped
274 AND u.usename = current_user;
276 GRANT SELECT ON column_domain_usage TO PUBLIC;
284 CREATE VIEW column_privileges AS
285 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
286 CAST(grantee.name AS sql_identifier) AS grantee,
287 CAST(current_database() AS sql_identifier) AS table_catalog,
288 CAST(nc.nspname AS sql_identifier) AS table_schema,
289 CAST(c.relname AS sql_identifier) AS table_name,
290 CAST(a.attname AS sql_identifier) AS column_name,
291 CAST(pr.type AS character_data) AS privilege_type,
293 CASE WHEN aclcontains(c.relacl,
294 makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr.type, true))
295 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
302 SELECT usesysid, 0, usename FROM pg_user
304 SELECT 0, grosysid, groname FROM pg_group
306 SELECT 0, 0, 'PUBLIC'
307 ) AS grantee (usesysid, grosysid, name),
308 (SELECT 'SELECT' UNION ALL
309 SELECT 'INSERT' UNION ALL
310 SELECT 'UPDATE' UNION ALL
311 SELECT 'REFERENCES') AS pr (type)
313 WHERE a.attrelid = c.oid
314 AND c.relnamespace = nc.oid
316 AND NOT a.attisdropped
317 AND c.relkind IN ('r', 'v')
318 AND aclcontains(c.relacl,
319 makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr.type, false))
320 AND (u_grantor.usename = current_user
321 OR grantee.name = current_user
322 OR grantee.name = 'PUBLIC');
324 GRANT SELECT ON column_privileges TO PUBLIC;
329 * COLUMN_UDT_USAGE view
332 CREATE VIEW column_udt_usage AS
333 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
334 CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,
335 CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,
336 CAST(current_database() AS sql_identifier) AS table_catalog,
337 CAST(nc.nspname AS sql_identifier) AS table_schema,
338 CAST(c.relname AS sql_identifier) AS table_name,
339 CAST(a.attname AS sql_identifier) AS column_name
341 FROM pg_attribute a, pg_class c, pg_namespace nc, pg_user u,
342 (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
343 LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
344 ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
346 WHERE a.attrelid = c.oid
347 AND a.atttypid = t.oid
348 AND u.usesysid = coalesce(bt.typowner, t.typowner)
349 AND nc.oid = c.relnamespace
350 AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
351 AND u.usename = current_user;
353 GRANT SELECT ON column_udt_usage TO PUBLIC;
361 CREATE VIEW columns AS
362 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
363 CAST(nc.nspname AS sql_identifier) AS table_schema,
364 CAST(c.relname AS sql_identifier) AS table_name,
365 CAST(a.attname AS sql_identifier) AS column_name,
366 CAST(a.attnum AS cardinal_number) AS ordinal_position,
368 CASE WHEN u.usename = current_user THEN ad.adsrc ELSE null END
371 CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
376 CASE WHEN t.typtype = 'd' THEN
377 CASE WHEN bt.typelem <> 0 AND bt.typlen = -1 THEN 'ARRAY'
378 WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
379 ELSE 'USER-DEFINED' END
381 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
382 WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, null)
383 ELSE 'USER-DEFINED' END
389 _pg_char_max_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
391 AS character_maximum_length,
394 _pg_char_octet_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
396 AS character_octet_length,
399 _pg_numeric_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
401 AS numeric_precision,
404 _pg_numeric_precision_radix(_pg_truetypid(a, t), _pg_truetypmod(a, t))
406 AS numeric_precision_radix,
409 _pg_numeric_scale(_pg_truetypid(a, t), _pg_truetypmod(a, t))
414 _pg_datetime_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
416 AS datetime_precision,
418 CAST(null AS character_data) AS interval_type, -- XXX
419 CAST(null AS character_data) AS interval_precision, -- XXX
421 CAST(null AS sql_identifier) AS character_set_catalog,
422 CAST(null AS sql_identifier) AS character_set_schema,
423 CAST(null AS sql_identifier) AS character_set_name,
425 CAST(null AS sql_identifier) AS collation_catalog,
426 CAST(null AS sql_identifier) AS collation_schema,
427 CAST(null AS sql_identifier) AS collation_name,
429 CAST(CASE WHEN t.typtype = 'd' THEN current_database() ELSE null END
430 AS sql_identifier) AS domain_catalog,
431 CAST(CASE WHEN t.typtype = 'd' THEN nt.nspname ELSE null END
432 AS sql_identifier) AS domain_schema,
433 CAST(CASE WHEN t.typtype = 'd' THEN t.typname ELSE null END
434 AS sql_identifier) AS domain_name,
436 CAST(current_database() AS sql_identifier) AS udt_catalog,
437 CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,
438 CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,
440 CAST(null AS sql_identifier) AS scope_catalog,
441 CAST(null AS sql_identifier) AS scope_schema,
442 CAST(null AS sql_identifier) AS scope_name,
444 CAST(null AS cardinal_number) AS maximum_cardinality,
445 CAST(a.attnum AS sql_identifier) AS dtd_identifier,
446 CAST('NO' AS character_data) AS is_self_referencing
448 FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum),
449 pg_class c, pg_namespace nc, pg_user u,
450 (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
451 LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
452 ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
454 WHERE a.attrelid = c.oid
455 AND a.atttypid = t.oid
456 AND u.usesysid = c.relowner
457 AND nc.oid = c.relnamespace
459 AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
461 AND (u.usename = current_user
462 OR has_table_privilege(c.oid, 'SELECT')
463 OR has_table_privilege(c.oid, 'INSERT')
464 OR has_table_privilege(c.oid, 'UPDATE')
465 OR has_table_privilege(c.oid, 'REFERENCES') );
467 GRANT SELECT ON columns TO PUBLIC;
472 * CONSTRAINT_COLUMN_USAGE view
475 CREATE VIEW constraint_column_usage AS
476 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
477 CAST(tblschema AS sql_identifier) AS table_schema,
478 CAST(tblname AS sql_identifier) AS table_name,
479 CAST(colname AS sql_identifier) AS column_name,
480 CAST(current_database() AS sql_identifier) AS constraint_catalog,
481 CAST(cstrschema AS sql_identifier) AS constraint_schema,
482 CAST(cstrname AS sql_identifier) AS constraint_name
485 /* check constraints */
486 SELECT DISTINCT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
487 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_depend d, pg_namespace nc, pg_constraint c
488 WHERE nr.oid = r.relnamespace
489 AND r.oid = a.attrelid
490 AND d.refclassid = 'pg_catalog.pg_class'::regclass
491 AND d.refobjid = r.oid
492 AND d.refobjsubid = a.attnum
493 AND d.classid = 'pg_catalog.pg_constraint'::regclass
495 AND c.connamespace = nc.oid
498 AND NOT a.attisdropped
502 /* unique/primary key/foreign key constraints */
503 SELECT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
504 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc,
506 WHERE nr.oid = r.relnamespace
507 AND r.oid = a.attrelid
508 AND nc.oid = c.connamespace
509 AND (CASE WHEN c.contype = 'f' THEN r.oid = c.confrelid AND a.attnum = ANY (c.confkey)
510 ELSE r.oid = c.conrelid AND a.attnum = ANY (c.conkey) END)
511 AND NOT a.attisdropped
512 AND c.contype IN ('p', 'u', 'f')
515 ) AS x (tblschema, tblname, tblowner, colname, cstrschema, cstrname),
518 WHERE x.tblowner = u.usesysid AND u.usename = current_user;
520 GRANT SELECT ON constraint_column_usage TO PUBLIC;
525 * CONSTRAINT_TABLE_USAGE view
528 CREATE VIEW constraint_table_usage AS
529 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
530 CAST(nr.nspname AS sql_identifier) AS table_schema,
531 CAST(r.relname AS sql_identifier) AS table_name,
532 CAST(current_database() AS sql_identifier) AS constraint_catalog,
533 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
534 CAST(c.conname AS sql_identifier) AS constraint_name
536 FROM pg_constraint c, pg_namespace nc,
537 pg_class r, pg_namespace nr,
540 WHERE c.connamespace = nc.oid AND r.relnamespace = nr.oid
541 AND ( (c.contype = 'f' AND c.confrelid = r.oid)
542 OR (c.contype IN ('p', 'u') AND c.conrelid = r.oid) )
544 AND r.relowner = u.usesysid AND u.usename = current_user;
546 GRANT SELECT ON constraint_table_usage TO PUBLIC;
549 -- 20.21 DATA_TYPE_PRIVILEGES view appears later.
554 * DOMAIN_CONSTRAINTS view
557 CREATE VIEW domain_constraints AS
558 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
559 CAST(rs.nspname AS sql_identifier) AS constraint_schema,
560 CAST(con.conname AS sql_identifier) AS constraint_name,
561 CAST(current_database() AS sql_identifier) AS domain_catalog,
562 CAST(n.nspname AS sql_identifier) AS domain_schema,
563 CAST(t.typname AS sql_identifier) AS domain_name,
564 CAST(CASE WHEN condeferrable THEN 'YES' ELSE 'NO' END
565 AS character_data) AS is_deferrable,
566 CAST(CASE WHEN condeferred THEN 'YES' ELSE 'NO' END
567 AS character_data) AS initially_deferred
568 FROM pg_namespace rs, pg_namespace n, pg_constraint con, pg_type t, pg_user u
569 WHERE rs.oid = con.connamespace
570 AND n.oid = t.typnamespace
571 AND u.usesysid = t.typowner
572 AND u.usename = current_user
573 AND t.oid = con.contypid;
575 GRANT SELECT ON domain_constraints TO PUBLIC;
580 * DOMAIN_UDT_USAGE view
583 CREATE VIEW domain_udt_usage AS
584 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
585 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
586 CAST(bt.typname AS sql_identifier) AS udt_name,
587 CAST(current_database() AS sql_identifier) AS domain_catalog,
588 CAST(nt.nspname AS sql_identifier) AS domain_schema,
589 CAST(t.typname AS sql_identifier) AS domain_name
591 FROM pg_type t, pg_namespace nt,
592 pg_type bt, pg_namespace nbt,
595 WHERE t.typnamespace = nt.oid
596 AND t.typbasetype = bt.oid
597 AND bt.typnamespace = nbt.oid
599 AND bt.typowner = u.usesysid
600 AND u.usename = current_user;
602 GRANT SELECT ON domain_udt_usage TO PUBLIC;
610 CREATE VIEW domains AS
611 SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
612 CAST(nt.nspname AS sql_identifier) AS domain_schema,
613 CAST(t.typname AS sql_identifier) AS domain_name,
616 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
617 WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
618 ELSE 'USER-DEFINED' END
623 _pg_char_max_length(t.typbasetype, t.typtypmod)
625 AS character_maximum_length,
628 _pg_char_octet_length(t.typbasetype, t.typtypmod)
630 AS character_octet_length,
632 CAST(null AS sql_identifier) AS character_set_catalog,
633 CAST(null AS sql_identifier) AS character_set_schema,
634 CAST(null AS sql_identifier) AS character_set_name,
636 CAST(null AS sql_identifier) AS collation_catalog,
637 CAST(null AS sql_identifier) AS collation_schema,
638 CAST(null AS sql_identifier) AS collation_name,
641 _pg_numeric_precision(t.typbasetype, t.typtypmod)
643 AS numeric_precision,
646 _pg_numeric_precision_radix(t.typbasetype, t.typtypmod)
648 AS numeric_precision_radix,
651 _pg_numeric_scale(t.typbasetype, t.typtypmod)
656 _pg_datetime_precision(t.typbasetype, t.typtypmod)
658 AS datetime_precision,
660 CAST(null AS character_data) AS interval_type, -- XXX
661 CAST(null AS character_data) AS interval_precision, -- XXX
663 CAST(t.typdefault AS character_data) AS domain_default,
665 CAST(current_database() AS sql_identifier) AS udt_catalog,
666 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
667 CAST(bt.typname AS sql_identifier) AS udt_name,
669 CAST(null AS sql_identifier) AS scope_catalog,
670 CAST(null AS sql_identifier) AS scope_schema,
671 CAST(null AS sql_identifier) AS scope_name,
673 CAST(null AS cardinal_number) AS maximum_cardinality,
674 CAST(1 AS sql_identifier) AS dtd_identifier
676 FROM pg_type t, pg_namespace nt,
677 pg_type bt, pg_namespace nbt
679 WHERE t.typnamespace = nt.oid
680 AND t.typbasetype = bt.oid
681 AND bt.typnamespace = nbt.oid
684 GRANT SELECT ON domains TO PUBLIC;
687 -- 20.27 ELEMENT_TYPES view appears later.
695 CREATE VIEW enabled_roles AS
696 SELECT CAST(g.groname AS sql_identifier) AS role_name
697 FROM pg_group g, pg_user u
698 WHERE u.usesysid = ANY (g.grolist)
699 AND u.usename = current_user;
701 GRANT SELECT ON enabled_roles TO PUBLIC;
706 * KEY_COLUMN_USAGE view
709 CREATE VIEW key_column_usage AS
710 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
711 CAST(nc_nspname AS sql_identifier) AS constraint_schema,
712 CAST(conname AS sql_identifier) AS constraint_name,
713 CAST(current_database() AS sql_identifier) AS table_catalog,
714 CAST(nr_nspname AS sql_identifier) AS table_schema,
715 CAST(relname AS sql_identifier) AS table_name,
716 CAST(a.attname AS sql_identifier) AS column_name,
717 CAST((ss.x).n AS cardinal_number) AS ordinal_position
720 (SELECT r.oid, nc.nspname AS nc_nspname, c.conname,
721 nr.nspname AS nr_nspname, r.relname,
722 _pg_expandarray(c.conkey) AS x
723 FROM pg_namespace nr, pg_class r, pg_namespace nc,
724 pg_constraint c, pg_user u
725 WHERE nr.oid = r.relnamespace
726 AND r.oid = c.conrelid
727 AND nc.oid = c.connamespace
728 AND c.contype IN ('p', 'u', 'f')
730 AND r.relowner = u.usesysid
731 AND u.usename = current_user) AS ss
732 WHERE ss.oid = a.attrelid
733 AND a.attnum = (ss.x).x
734 AND NOT a.attisdropped;
736 GRANT SELECT ON key_column_usage TO PUBLIC;
744 CREATE VIEW parameters AS
745 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
746 CAST(n_nspname AS sql_identifier) AS specific_schema,
747 CAST(proname || '_' || CAST(p_oid AS text) AS sql_identifier) AS specific_name,
748 CAST((ss.x).n AS cardinal_number) AS ordinal_position,
750 CASE WHEN proargmodes IS NULL THEN 'IN'
751 WHEN proargmodes[(ss.x).n] = 'i' THEN 'IN'
752 WHEN proargmodes[(ss.x).n] = 'o' THEN 'OUT'
753 WHEN proargmodes[(ss.x).n] = 'b' THEN 'INOUT'
754 END AS character_data) AS parameter_mode,
755 CAST('NO' AS character_data) AS is_result,
756 CAST('NO' AS character_data) AS as_locator,
757 CAST(NULLIF(proargnames[(ss.x).n], '') AS sql_identifier) AS parameter_name,
759 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
760 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
761 ELSE 'USER-DEFINED' END AS character_data)
763 CAST(null AS cardinal_number) AS character_maximum_length,
764 CAST(null AS cardinal_number) AS character_octet_length,
765 CAST(null AS sql_identifier) AS character_set_catalog,
766 CAST(null AS sql_identifier) AS character_set_schema,
767 CAST(null AS sql_identifier) AS character_set_name,
768 CAST(null AS sql_identifier) AS collation_catalog,
769 CAST(null AS sql_identifier) AS collation_schema,
770 CAST(null AS sql_identifier) AS collation_name,
771 CAST(null AS cardinal_number) AS numeric_precision,
772 CAST(null AS cardinal_number) AS numeric_precision_radix,
773 CAST(null AS cardinal_number) AS numeric_scale,
774 CAST(null AS cardinal_number) AS datetime_precision,
775 CAST(null AS character_data) AS interval_type,
776 CAST(null AS character_data) AS interval_precision,
777 CAST(current_database() AS sql_identifier) AS udt_catalog,
778 CAST(nt.nspname AS sql_identifier) AS udt_schema,
779 CAST(t.typname AS sql_identifier) AS udt_name,
780 CAST(null AS sql_identifier) AS scope_catalog,
781 CAST(null AS sql_identifier) AS scope_schema,
782 CAST(null AS sql_identifier) AS scope_name,
783 CAST(null AS cardinal_number) AS maximum_cardinality,
784 CAST((ss.x).n AS sql_identifier) AS dtd_identifier
786 FROM pg_type t, pg_namespace nt,
787 (SELECT n.nspname AS n_nspname, p.proname, p.oid AS p_oid,
788 p.proargnames, p.proargmodes,
789 _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
790 FROM pg_namespace n, pg_proc p, pg_user u
791 WHERE n.oid = p.pronamespace
792 AND p.proowner = u.usesysid
793 AND (u.usename = current_user OR
794 has_function_privilege(p.oid, 'EXECUTE'))) AS ss
795 WHERE t.oid = (ss.x).x AND t.typnamespace = nt.oid;
797 GRANT SELECT ON parameters TO PUBLIC;
802 * REFERENTIAL_CONSTRAINTS view
805 CREATE VIEW referential_constraints AS
806 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
807 CAST(ncon.nspname AS sql_identifier) AS constraint_schema,
808 CAST(con.conname AS sql_identifier) AS constraint_name,
810 CASE WHEN npkc.nspname IS NULL THEN NULL
811 ELSE current_database() END
812 AS sql_identifier) AS unique_constraint_catalog,
813 CAST(npkc.nspname AS sql_identifier) AS unique_constraint_schema,
814 CAST(pkc.conname AS sql_identifier) AS unique_constraint_name,
817 CASE con.confmatchtype WHEN 'f' THEN 'FULL'
818 WHEN 'p' THEN 'PARTIAL'
819 WHEN 'u' THEN 'NONE' END
820 AS character_data) AS match_option,
823 CASE con.confupdtype WHEN 'c' THEN 'CASCADE'
824 WHEN 'n' THEN 'SET NULL'
825 WHEN 'd' THEN 'SET DEFAULT'
826 WHEN 'r' THEN 'RESTRICT'
827 WHEN 'a' THEN 'NO ACTION' END
828 AS character_data) AS update_rule,
831 CASE con.confdeltype WHEN 'c' THEN 'CASCADE'
832 WHEN 'n' THEN 'SET NULL'
833 WHEN 'd' THEN 'SET DEFAULT'
834 WHEN 'r' THEN 'RESTRICT'
835 WHEN 'a' THEN 'NO ACTION' END
836 AS character_data) AS delete_rule
838 FROM (pg_namespace ncon INNER JOIN pg_constraint con ON ncon.oid = con.connamespace
839 INNER JOIN pg_class c ON con.conrelid = c.oid
840 INNER JOIN pg_user u ON c.relowner = u.usesysid)
842 (pg_constraint pkc INNER JOIN pg_namespace npkc ON pkc.connamespace = npkc.oid)
843 ON con.confrelid = pkc.conrelid AND _pg_keysequal(con.confkey, pkc.conkey)
845 WHERE c.relkind = 'r'
846 AND con.contype = 'f'
847 AND (pkc.contype IN ('p', 'u') OR pkc.contype IS NULL)
848 AND u.usename = current_user;
850 GRANT SELECT ON referential_constraints TO PUBLIC;
855 * ROLE_COLUMN_GRANTS view
858 CREATE VIEW role_column_grants AS
859 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
860 CAST(g_grantee.groname AS sql_identifier) AS grantee,
861 CAST(current_database() AS sql_identifier) AS table_catalog,
862 CAST(nc.nspname AS sql_identifier) AS table_schema,
863 CAST(c.relname AS sql_identifier) AS table_name,
864 CAST(a.attname AS sql_identifier) AS column_name,
865 CAST(pr.type AS character_data) AS privilege_type,
867 CASE WHEN aclcontains(c.relacl,
868 makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, pr.type, true))
869 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
876 (SELECT 'SELECT' UNION ALL
877 SELECT 'INSERT' UNION ALL
878 SELECT 'UPDATE' UNION ALL
879 SELECT 'REFERENCES') AS pr (type)
881 WHERE a.attrelid = c.oid
882 AND c.relnamespace = nc.oid
884 AND NOT a.attisdropped
885 AND c.relkind IN ('r', 'v')
886 AND aclcontains(c.relacl,
887 makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, pr.type, false))
888 AND g_grantee.groname IN (SELECT role_name FROM enabled_roles);
890 GRANT SELECT ON role_column_grants TO PUBLIC;
895 * ROLE_ROUTINE_GRANTS view
898 CREATE VIEW role_routine_grants AS
899 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
900 CAST(g_grantee.groname AS sql_identifier) AS grantee,
901 CAST(current_database() AS sql_identifier) AS specific_catalog,
902 CAST(n.nspname AS sql_identifier) AS specific_schema,
903 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
904 CAST(current_database() AS sql_identifier) AS routine_catalog,
905 CAST(n.nspname AS sql_identifier) AS routine_schema,
906 CAST(p.proname AS sql_identifier) AS routine_name,
907 CAST('EXECUTE' AS character_data) AS privilege_type,
909 CASE WHEN aclcontains(p.proacl,
910 makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, 'EXECUTE', true))
911 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
918 WHERE p.pronamespace = n.oid
919 AND aclcontains(p.proacl,
920 makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, 'EXECUTE', false))
921 AND g_grantee.groname IN (SELECT role_name FROM enabled_roles);
923 GRANT SELECT ON role_routine_grants TO PUBLIC;
928 * ROLE_TABLE_GRANTS view
931 CREATE VIEW role_table_grants AS
932 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
933 CAST(g_grantee.groname AS sql_identifier) AS grantee,
934 CAST(current_database() AS sql_identifier) AS table_catalog,
935 CAST(nc.nspname AS sql_identifier) AS table_schema,
936 CAST(c.relname AS sql_identifier) AS table_name,
937 CAST(pr.type AS character_data) AS privilege_type,
939 CASE WHEN aclcontains(c.relacl,
940 makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, pr.type, true))
941 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable,
942 CAST('NO' AS character_data) AS with_hierarchy
948 (SELECT 'SELECT' UNION ALL
949 SELECT 'DELETE' UNION ALL
950 SELECT 'INSERT' UNION ALL
951 SELECT 'UPDATE' UNION ALL
952 SELECT 'REFERENCES' UNION ALL
953 SELECT 'RULE' UNION ALL
954 SELECT 'TRIGGER') AS pr (type)
956 WHERE c.relnamespace = nc.oid
957 AND c.relkind IN ('r', 'v')
958 AND aclcontains(c.relacl,
959 makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, pr.type, false))
960 AND g_grantee.groname IN (SELECT role_name FROM enabled_roles);
962 GRANT SELECT ON role_table_grants TO PUBLIC;
967 * ROLE_USAGE_GRANTS view
970 -- See USAGE_PRIVILEGES.
972 CREATE VIEW role_usage_grants AS
973 SELECT CAST(null AS sql_identifier) AS grantor,
974 CAST(null AS sql_identifier) AS grantee,
975 CAST(current_database() AS sql_identifier) AS object_catalog,
976 CAST(null AS sql_identifier) AS object_schema,
977 CAST(null AS sql_identifier) AS object_name,
978 CAST(null AS character_data) AS object_type,
979 CAST('USAGE' AS character_data) AS privilege_type,
980 CAST(null AS character_data) AS is_grantable
984 GRANT SELECT ON role_usage_grants TO PUBLIC;
989 * ROUTINE_PRIVILEGES view
992 CREATE VIEW routine_privileges AS
993 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
994 CAST(grantee.name AS sql_identifier) AS grantee,
995 CAST(current_database() AS sql_identifier) AS specific_catalog,
996 CAST(n.nspname AS sql_identifier) AS specific_schema,
997 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
998 CAST(current_database() AS sql_identifier) AS routine_catalog,
999 CAST(n.nspname AS sql_identifier) AS routine_schema,
1000 CAST(p.proname AS sql_identifier) AS routine_name,
1001 CAST('EXECUTE' AS character_data) AS privilege_type,
1003 CASE WHEN aclcontains(p.proacl,
1004 makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, 'EXECUTE', true))
1005 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
1011 SELECT usesysid, 0, usename FROM pg_user
1013 SELECT 0, grosysid, groname FROM pg_group
1015 SELECT 0, 0, 'PUBLIC'
1016 ) AS grantee (usesysid, grosysid, name)
1018 WHERE p.pronamespace = n.oid
1019 AND aclcontains(p.proacl,
1020 makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, 'EXECUTE', false))
1021 AND (u_grantor.usename = current_user
1022 OR grantee.name = current_user
1023 OR grantee.name = 'PUBLIC');
1025 GRANT SELECT ON routine_privileges TO PUBLIC;
1033 CREATE VIEW routines AS
1034 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
1035 CAST(n.nspname AS sql_identifier) AS specific_schema,
1036 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1037 CAST(current_database() AS sql_identifier) AS routine_catalog,
1038 CAST(n.nspname AS sql_identifier) AS routine_schema,
1039 CAST(p.proname AS sql_identifier) AS routine_name,
1040 CAST('FUNCTION' AS character_data) AS routine_type,
1041 CAST(null AS sql_identifier) AS module_catalog,
1042 CAST(null AS sql_identifier) AS module_schema,
1043 CAST(null AS sql_identifier) AS module_name,
1044 CAST(null AS sql_identifier) AS udt_catalog,
1045 CAST(null AS sql_identifier) AS udt_schema,
1046 CAST(null AS sql_identifier) AS udt_name,
1049 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
1050 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
1051 ELSE 'USER-DEFINED' END AS character_data)
1053 CAST(null AS cardinal_number) AS character_maximum_length,
1054 CAST(null AS cardinal_number) AS character_octet_length,
1055 CAST(null AS sql_identifier) AS character_set_catalog,
1056 CAST(null AS sql_identifier) AS character_set_schema,
1057 CAST(null AS sql_identifier) AS character_set_name,
1058 CAST(null AS sql_identifier) AS collation_catalog,
1059 CAST(null AS sql_identifier) AS collation_schema,
1060 CAST(null AS sql_identifier) AS collation_name,
1061 CAST(null AS cardinal_number) AS numeric_precision,
1062 CAST(null AS cardinal_number) AS numeric_precision_radix,
1063 CAST(null AS cardinal_number) AS numeric_scale,
1064 CAST(null AS cardinal_number) AS datetime_precision,
1065 CAST(null AS character_data) AS interval_type,
1066 CAST(null AS character_data) AS interval_precision,
1067 CAST(current_database() AS sql_identifier) AS type_udt_catalog,
1068 CAST(nt.nspname AS sql_identifier) AS type_udt_schema,
1069 CAST(t.typname AS sql_identifier) AS type_udt_name,
1070 CAST(null AS sql_identifier) AS scope_catalog,
1071 CAST(null AS sql_identifier) AS scope_schema,
1072 CAST(null AS sql_identifier) AS scope_name,
1073 CAST(null AS cardinal_number) AS maximum_cardinality,
1074 CAST(0 AS sql_identifier) AS dtd_identifier,
1076 CAST(CASE WHEN l.lanname = 'sql' THEN 'SQL' ELSE 'EXTERNAL' END AS character_data)
1079 CASE WHEN u.usename = current_user THEN p.prosrc ELSE null END
1080 AS character_data) AS routine_definition,
1082 CASE WHEN l.lanname = 'c' THEN p.prosrc ELSE null END
1083 AS character_data) AS external_name,
1084 CAST(upper(l.lanname) AS character_data) AS external_language,
1086 CAST('GENERAL' AS character_data) AS parameter_style,
1087 CAST(CASE WHEN p.provolatile = 'i' THEN 'YES' ELSE 'NO' END AS character_data) AS is_deterministic,
1088 CAST('MODIFIES' AS character_data) AS sql_data_access,
1089 CAST(CASE WHEN p.proisstrict THEN 'YES' ELSE 'NO' END AS character_data) AS is_null_call,
1090 CAST(null AS character_data) AS sql_path,
1091 CAST('YES' AS character_data) AS schema_level_routine,
1092 CAST(0 AS cardinal_number) AS max_dynamic_result_sets,
1093 CAST(null AS character_data) AS is_user_defined_cast,
1094 CAST(null AS character_data) AS is_implicitly_invocable,
1095 CAST(CASE WHEN p.prosecdef THEN 'DEFINER' ELSE 'INVOKER' END AS character_data) AS security_type,
1096 CAST(null AS sql_identifier) AS to_sql_specific_catalog,
1097 CAST(null AS sql_identifier) AS to_sql_specific_schema,
1098 CAST(null AS sql_identifier) AS to_sql_specific_name,
1099 CAST('NO' AS character_data) AS as_locator
1101 FROM pg_namespace n, pg_proc p, pg_language l, pg_user u,
1102 pg_type t, pg_namespace nt
1104 WHERE n.oid = p.pronamespace AND p.prolang = l.oid AND p.proowner = u.usesysid
1105 AND p.prorettype = t.oid AND t.typnamespace = nt.oid
1106 AND (u.usename = current_user OR has_function_privilege(p.oid, 'EXECUTE'));
1108 GRANT SELECT ON routines TO PUBLIC;
1116 CREATE VIEW schemata AS
1117 SELECT CAST(current_database() AS sql_identifier) AS catalog_name,
1118 CAST(n.nspname AS sql_identifier) AS schema_name,
1119 CAST(u.usename AS sql_identifier) AS schema_owner,
1120 CAST(null AS sql_identifier) AS default_character_set_catalog,
1121 CAST(null AS sql_identifier) AS default_character_set_schema,
1122 CAST(null AS sql_identifier) AS default_character_set_name,
1123 CAST(null AS character_data) AS sql_path
1124 FROM pg_namespace n, pg_user u
1125 WHERE n.nspowner = u.usesysid AND u.usename = current_user;
1127 GRANT SELECT ON schemata TO PUBLIC;
1132 * SQL_FEATURES table
1135 CREATE TABLE sql_features (
1136 feature_id character_data,
1137 feature_name character_data,
1138 sub_feature_id character_data,
1139 sub_feature_name character_data,
1140 is_supported character_data,
1141 is_verified_by character_data,
1142 comments character_data
1145 -- Will be filled with external data by initdb.
1147 GRANT SELECT ON sql_features TO PUBLIC;
1152 * SQL_IMPLEMENTATION_INFO table
1155 -- Note: Implementation information items are defined in ISO 9075-3:1999,
1158 CREATE TABLE sql_implementation_info (
1159 implementation_info_id character_data,
1160 implementation_info_name character_data,
1161 integer_value cardinal_number,
1162 character_value character_data,
1163 comments character_data
1166 INSERT INTO sql_implementation_info VALUES ('10003', 'CATALOG NAME', NULL, 'Y', NULL);
1167 INSERT INTO sql_implementation_info VALUES ('10004', 'COLLATING SEQUENCE', NULL, '', 'not supported');
1168 INSERT INTO sql_implementation_info VALUES ('23', 'CURSOR COMMIT BEHAVIOR', 1, NULL, 'close cursors and retain prepared statements');
1169 INSERT INTO sql_implementation_info VALUES ('2', 'DATA SOURCE NAME', NULL, '', NULL);
1170 INSERT INTO sql_implementation_info VALUES ('17', 'DBMS NAME', NULL, (select trim(trailing ' ' from substring(version() from '^[^0-9]*'))), NULL);
1171 INSERT INTO sql_implementation_info VALUES ('18', 'DBMS VERSION', NULL, '???', NULL); -- filled by initdb
1172 INSERT INTO sql_implementation_info VALUES ('26', 'DEFAULT TRANSACTION ISOLATION', 2, NULL, 'READ COMMITTED; user-settable');
1173 INSERT INTO sql_implementation_info VALUES ('28', 'IDENTIFIER CASE', 3, NULL, 'stored in mixed case - case sensitive');
1174 INSERT INTO sql_implementation_info VALUES ('85', 'NULL COLLATION', 0, NULL, 'nulls higher than non-nulls');
1175 INSERT INTO sql_implementation_info VALUES ('13', 'SERVER NAME', NULL, '', NULL);
1176 INSERT INTO sql_implementation_info VALUES ('94', 'SPECIAL CHARACTERS', NULL, '', 'all non-ASCII characters allowed');
1177 INSERT INTO sql_implementation_info VALUES ('46', 'TRANSACTION CAPABLE', 2, NULL, 'both DML and DDL');
1179 GRANT SELECT ON sql_implementation_info TO PUBLIC;
1184 * SQL_LANGUAGES table
1187 CREATE TABLE sql_languages (
1188 sql_language_source character_data,
1189 sql_language_year character_data,
1190 sql_language_conformance character_data,
1191 sql_language_integrity character_data,
1192 sql_language_implementation character_data,
1193 sql_language_binding_style character_data,
1194 sql_language_programming_language character_data
1197 INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'DIRECT', NULL);
1198 INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
1200 GRANT SELECT ON sql_languages TO PUBLIC;
1205 * SQL_PACKAGES table
1208 CREATE TABLE sql_packages (
1209 feature_id character_data,
1210 feature_name character_data,
1211 is_supported character_data,
1212 is_verified_by character_data,
1213 comments character_data
1216 INSERT INTO sql_packages VALUES ('PKG000', 'Core', 'NO', NULL, '');
1217 INSERT INTO sql_packages VALUES ('PKG001', 'Enhanced datetime facilities', 'YES', NULL, '');
1218 INSERT INTO sql_packages VALUES ('PKG002', 'Enhanced integrity management', 'NO', NULL, '');
1219 INSERT INTO sql_packages VALUES ('PKG003', 'OLAP facilities', 'NO', NULL, '');
1220 INSERT INTO sql_packages VALUES ('PKG004', 'PSM', 'NO', NULL, 'PL/pgSQL is similar.');
1221 INSERT INTO sql_packages VALUES ('PKG005', 'CLI', 'NO', NULL, 'ODBC is similar.');
1222 INSERT INTO sql_packages VALUES ('PKG006', 'Basic object support', 'NO', NULL, '');
1223 INSERT INTO sql_packages VALUES ('PKG007', 'Enhanced object support', 'NO', NULL, '');
1224 INSERT INTO sql_packages VALUES ('PKG008', 'Active database', 'NO', NULL, '');
1225 INSERT INTO sql_packages VALUES ('PKG010', 'OLAP', 'NO', NULL, 'NO');
1227 GRANT SELECT ON sql_packages TO PUBLIC;
1235 -- Note: Sizing items are defined in ISO 9075-3:1999, clause 7.2.
1237 CREATE TABLE sql_sizing (
1238 sizing_id cardinal_number,
1239 sizing_name character_data,
1240 supported_value cardinal_number,
1241 comments character_data
1244 INSERT INTO sql_sizing VALUES (34, 'MAXIMUM CATALOG NAME LENGTH', 63, NULL);
1245 INSERT INTO sql_sizing VALUES (30, 'MAXIMUM COLUMN NAME LENGTH', 63, NULL);
1246 INSERT INTO sql_sizing VALUES (97, 'MAXIMUM COLUMNS IN GROUP BY', 0, NULL);
1247 INSERT INTO sql_sizing VALUES (99, 'MAXIMUM COLUMNS IN ORDER BY', 0, NULL);
1248 INSERT INTO sql_sizing VALUES (100, 'MAXIMUM COLUMNS IN SELECT', 1664, NULL); -- match MaxTupleAttributeNumber
1249 INSERT INTO sql_sizing VALUES (101, 'MAXIMUM COLUMNS IN TABLE', 1600, NULL); -- match MaxHeapAttributeNumber
1250 INSERT INTO sql_sizing VALUES (1, 'MAXIMUM CONCURRENT ACTIVITIES', 0, NULL);
1251 INSERT INTO sql_sizing VALUES (31, 'MAXIMUM CURSOR NAME LENGTH', 63, NULL);
1252 INSERT INTO sql_sizing VALUES (0, 'MAXIMUM DRIVER CONNECTIONS', NULL, NULL);
1253 INSERT INTO sql_sizing VALUES (10005, 'MAXIMUM IDENTIFIER LENGTH', 63, NULL);
1254 INSERT INTO sql_sizing VALUES (32, 'MAXIMUM SCHEMA NAME LENGTH', 63, NULL);
1255 INSERT INTO sql_sizing VALUES (20000, 'MAXIMUM STATEMENT OCTETS', 0, NULL);
1256 INSERT INTO sql_sizing VALUES (20001, 'MAXIMUM STATEMENT OCTETS DATA', 0, NULL);
1257 INSERT INTO sql_sizing VALUES (20002, 'MAXIMUM STATEMENT OCTETS SCHEMA', 0, NULL);
1258 INSERT INTO sql_sizing VALUES (35, 'MAXIMUM TABLE NAME LENGTH', 63, NULL);
1259 INSERT INTO sql_sizing VALUES (106, 'MAXIMUM TABLES IN SELECT', 0, NULL);
1260 INSERT INTO sql_sizing VALUES (107, 'MAXIMUM USER NAME LENGTH', 63, NULL);
1261 INSERT INTO sql_sizing VALUES (25000, 'MAXIMUM CURRENT DEFAULT TRANSFORM GROUP LENGTH', NULL, NULL);
1262 INSERT INTO sql_sizing VALUES (25001, 'MAXIMUM CURRENT TRANSFORM GROUP LENGTH', NULL, NULL);
1263 INSERT INTO sql_sizing VALUES (25002, 'MAXIMUM CURRENT PATH LENGTH', 0, NULL);
1264 INSERT INTO sql_sizing VALUES (25003, 'MAXIMUM CURRENT ROLE LENGTH', NULL, NULL);
1265 INSERT INTO sql_sizing VALUES (25004, 'MAXIMUM SESSION USER LENGTH', 63, NULL);
1266 INSERT INTO sql_sizing VALUES (25005, 'MAXIMUM SYSTEM USER LENGTH', 63, NULL);
1269 SET supported_value = (SELECT typlen-1 FROM pg_catalog.pg_type WHERE typname = 'name'),
1270 comments = 'Might be less, depending on character set.'
1271 WHERE supported_value = 63;
1273 GRANT SELECT ON sql_sizing TO PUBLIC;
1278 * SQL_SIZING_PROFILES table
1281 -- The data in this table are defined by various profiles of SQL.
1282 -- Since we don't have any information about such profiles, we provide
1285 CREATE TABLE sql_sizing_profiles (
1286 sizing_id cardinal_number,
1287 sizing_name character_data,
1288 profile_id character_data,
1289 required_value cardinal_number,
1290 comments character_data
1293 GRANT SELECT ON sql_sizing_profiles TO PUBLIC;
1298 * TABLE_CONSTRAINTS view
1301 CREATE VIEW table_constraints AS
1302 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1303 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
1304 CAST(c.conname AS sql_identifier) AS constraint_name,
1305 CAST(current_database() AS sql_identifier) AS table_catalog,
1306 CAST(nr.nspname AS sql_identifier) AS table_schema,
1307 CAST(r.relname AS sql_identifier) AS table_name,
1309 CASE c.contype WHEN 'c' THEN 'CHECK'
1310 WHEN 'f' THEN 'FOREIGN KEY'
1311 WHEN 'p' THEN 'PRIMARY KEY'
1312 WHEN 'u' THEN 'UNIQUE' END
1313 AS character_data) AS constraint_type,
1314 CAST(CASE WHEN c.condeferrable THEN 'YES' ELSE 'NO' END AS character_data)
1316 CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS character_data)
1317 AS initially_deferred
1319 FROM pg_namespace nc,
1325 WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace
1326 AND c.conrelid = r.oid AND r.relowner = u.usesysid
1328 AND u.usename = current_user;
1330 -- FIMXE: Not-null constraints are missing here.
1332 GRANT SELECT ON table_constraints TO PUBLIC;
1337 * TABLE_PRIVILEGES view
1340 CREATE VIEW table_privileges AS
1341 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
1342 CAST(grantee.name AS sql_identifier) AS grantee,
1343 CAST(current_database() AS sql_identifier) AS table_catalog,
1344 CAST(nc.nspname AS sql_identifier) AS table_schema,
1345 CAST(c.relname AS sql_identifier) AS table_name,
1346 CAST(pr.type AS character_data) AS privilege_type,
1348 CASE WHEN aclcontains(c.relacl,
1349 makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr.type, true))
1350 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable,
1351 CAST('NO' AS character_data) AS with_hierarchy
1357 SELECT usesysid, 0, usename FROM pg_user
1359 SELECT 0, grosysid, groname FROM pg_group
1361 SELECT 0, 0, 'PUBLIC'
1362 ) AS grantee (usesysid, grosysid, name),
1363 (SELECT 'SELECT' UNION ALL
1364 SELECT 'DELETE' UNION ALL
1365 SELECT 'INSERT' UNION ALL
1366 SELECT 'UPDATE' UNION ALL
1367 SELECT 'REFERENCES' UNION ALL
1368 SELECT 'RULE' UNION ALL
1369 SELECT 'TRIGGER') AS pr (type)
1371 WHERE c.relnamespace = nc.oid
1372 AND c.relkind IN ('r', 'v')
1373 AND aclcontains(c.relacl,
1374 makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr.type, false))
1375 AND (u_grantor.usename = current_user
1376 OR grantee.name = current_user
1377 OR grantee.name = 'PUBLIC');
1379 GRANT SELECT ON table_privileges TO PUBLIC;
1387 CREATE VIEW tables AS
1388 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
1389 CAST(nc.nspname AS sql_identifier) AS table_schema,
1390 CAST(c.relname AS sql_identifier) AS table_name,
1393 CASE WHEN nc.nspname LIKE 'pg!_temp!_%' ESCAPE '!' THEN 'LOCAL TEMPORARY'
1394 WHEN c.relkind = 'r' THEN 'BASE TABLE'
1395 WHEN c.relkind = 'v' THEN 'VIEW'
1397 AS character_data) AS table_type,
1399 CAST(null AS sql_identifier) AS self_referencing_column_name,
1400 CAST(null AS character_data) AS reference_generation,
1402 CAST(null AS sql_identifier) AS user_defined_type_catalog,
1403 CAST(null AS sql_identifier) AS user_defined_type_schema,
1404 CAST(null AS sql_identifier) AS user_defined_name
1406 FROM pg_namespace nc, pg_class c, pg_user u
1408 WHERE c.relnamespace = nc.oid AND u.usesysid = c.relowner
1409 AND c.relkind IN ('r', 'v')
1410 AND (u.usename = current_user
1411 OR has_table_privilege(c.oid, 'SELECT')
1412 OR has_table_privilege(c.oid, 'INSERT')
1413 OR has_table_privilege(c.oid, 'UPDATE')
1414 OR has_table_privilege(c.oid, 'DELETE')
1415 OR has_table_privilege(c.oid, 'RULE')
1416 OR has_table_privilege(c.oid, 'REFERENCES')
1417 OR has_table_privilege(c.oid, 'TRIGGER') );
1419 GRANT SELECT ON tables TO PUBLIC;
1424 * TRIGGERED_UPDATE_COLUMNS view
1427 -- PostgreSQL doesn't allow the specification of individual triggered
1428 -- update columns, so this view is empty.
1430 CREATE VIEW triggered_update_columns AS
1431 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1432 CAST(null AS sql_identifier) AS trigger_schema,
1433 CAST(null AS sql_identifier) AS trigger_name,
1434 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1435 CAST(null AS sql_identifier) AS event_object_schema,
1436 CAST(null AS sql_identifier) AS event_object_table,
1437 CAST(null AS sql_identifier) AS event_object_column
1440 GRANT SELECT ON triggered_update_columns TO PUBLIC;
1448 CREATE VIEW triggers AS
1449 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1450 CAST(n.nspname AS sql_identifier) AS trigger_schema,
1451 CAST(t.tgname AS sql_identifier) AS trigger_name,
1452 CAST(em.text AS character_data) AS event_manipulation,
1453 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1454 CAST(n.nspname AS sql_identifier) AS event_object_schema,
1455 CAST(c.relname AS sql_identifier) AS event_object_table,
1456 CAST(null AS cardinal_number) AS action_order,
1457 CAST(null AS character_data) AS action_condition,
1459 substring(pg_get_triggerdef(t.oid) from
1460 position('EXECUTE PROCEDURE' in substring(pg_get_triggerdef(t.oid) from 48)) + 47)
1461 AS character_data) AS action_statement,
1463 CASE WHEN t.tgtype & 1 = 1 THEN 'ROW' ELSE 'STATEMENT' END
1464 AS character_data) AS action_orientation,
1466 CASE WHEN t.tgtype & 2 = 2 THEN 'BEFORE' ELSE 'AFTER' END
1467 AS character_data) AS condition_timing,
1468 CAST(null AS sql_identifier) AS condition_reference_old_table,
1469 CAST(null AS sql_identifier) AS condition_reference_new_table
1471 FROM pg_namespace n, pg_class c, pg_trigger t, pg_user u,
1472 (SELECT 4, 'INSERT' UNION ALL
1473 SELECT 8, 'DELETE' UNION ALL
1474 SELECT 16, 'UPDATE') AS em (num, text)
1476 WHERE n.oid = c.relnamespace
1477 AND c.oid = t.tgrelid
1478 AND c.relowner = u.usesysid
1479 AND t.tgtype & em.num <> 0
1480 AND NOT t.tgisconstraint
1481 AND u.usename = current_user;
1483 GRANT SELECT ON triggers TO PUBLIC;
1488 * USAGE_PRIVILEGES view
1491 -- Of the things currently implemented in PostgreSQL, usage privileges
1492 -- apply only to domains. Since domains have no real privileges, we
1493 -- represent all domains with implicit usage privilege here.
1495 CREATE VIEW usage_privileges AS
1496 SELECT CAST(u.usename AS sql_identifier) AS grantor,
1497 CAST('PUBLIC' AS sql_identifier) AS grantee,
1498 CAST(current_database() AS sql_identifier) AS object_catalog,
1499 CAST(n.nspname AS sql_identifier) AS object_schema,
1500 CAST(t.typname AS sql_identifier) AS object_name,
1501 CAST('DOMAIN' AS character_data) AS object_type,
1502 CAST('USAGE' AS character_data) AS privilege_type,
1503 CAST('NO' AS character_data) AS is_grantable
1509 WHERE u.usesysid = t.typowner
1510 AND t.typnamespace = n.oid
1511 AND t.typtype = 'd';
1513 GRANT SELECT ON usage_privileges TO PUBLIC;
1521 CREATE VIEW view_column_usage AS
1523 CAST(current_database() AS sql_identifier) AS view_catalog,
1524 CAST(nv.nspname AS sql_identifier) AS view_schema,
1525 CAST(v.relname AS sql_identifier) AS view_name,
1526 CAST(current_database() AS sql_identifier) AS table_catalog,
1527 CAST(nt.nspname AS sql_identifier) AS table_schema,
1528 CAST(t.relname AS sql_identifier) AS table_name,
1529 CAST(a.attname AS sql_identifier) AS column_name
1531 FROM pg_user, pg_namespace nv, pg_class v, pg_depend dv,
1532 pg_depend dt, pg_class t, pg_namespace nt,
1533 pg_attribute a, pg_user u
1535 WHERE nv.oid = v.relnamespace
1537 AND v.oid = dv.refobjid
1538 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
1539 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
1540 AND dv.deptype = 'i'
1541 AND dv.objid = dt.objid
1542 AND dv.refobjid <> dt.refobjid
1543 AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
1544 AND dt.refclassid = 'pg_catalog.pg_class'::regclass
1545 AND dt.refobjid = t.oid
1546 AND t.relnamespace = nt.oid
1547 AND t.relkind IN ('r', 'v')
1548 AND t.oid = a.attrelid
1549 AND dt.refobjsubid = a.attnum
1550 AND t.relowner = u.usesysid AND u.usename = current_user;
1552 GRANT SELECT ON view_column_usage TO PUBLIC;
1560 CREATE VIEW view_table_usage AS
1562 CAST(current_database() AS sql_identifier) AS view_catalog,
1563 CAST(nv.nspname AS sql_identifier) AS view_schema,
1564 CAST(v.relname AS sql_identifier) AS view_name,
1565 CAST(current_database() AS sql_identifier) AS table_catalog,
1566 CAST(nt.nspname AS sql_identifier) AS table_schema,
1567 CAST(t.relname AS sql_identifier) AS table_name
1569 FROM pg_user, pg_namespace nv, pg_class v, pg_depend dv,
1570 pg_depend dt, pg_class t, pg_namespace nt,
1573 WHERE nv.oid = v.relnamespace
1575 AND v.oid = dv.refobjid
1576 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
1577 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
1578 AND dv.deptype = 'i'
1579 AND dv.objid = dt.objid
1580 AND dv.refobjid <> dt.refobjid
1581 AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
1582 AND dt.refclassid = 'pg_catalog.pg_class'::regclass
1583 AND dt.refobjid = t.oid
1584 AND t.relnamespace = nt.oid
1585 AND t.relkind IN ('r', 'v')
1586 AND t.relowner = u.usesysid AND u.usename = current_user;
1588 GRANT SELECT ON view_table_usage TO PUBLIC;
1596 CREATE VIEW views AS
1597 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
1598 CAST(nc.nspname AS sql_identifier) AS table_schema,
1599 CAST(c.relname AS sql_identifier) AS table_name,
1602 CASE WHEN u.usename = current_user THEN pg_get_viewdef(c.oid)
1604 AS character_data) AS view_definition,
1606 CAST('NONE' AS character_data) AS check_option,
1607 CAST(null AS character_data) AS is_updatable, -- FIXME
1608 CAST(null AS character_data) AS is_insertable_into -- FIXME
1610 FROM pg_namespace nc, pg_class c, pg_user u
1612 WHERE c.relnamespace = nc.oid AND u.usesysid = c.relowner
1614 AND (u.usename = current_user
1615 OR has_table_privilege(c.oid, 'SELECT')
1616 OR has_table_privilege(c.oid, 'INSERT')
1617 OR has_table_privilege(c.oid, 'UPDATE')
1618 OR has_table_privilege(c.oid, 'DELETE')
1619 OR has_table_privilege(c.oid, 'RULE')
1620 OR has_table_privilege(c.oid, 'REFERENCES')
1621 OR has_table_privilege(c.oid, 'TRIGGER') );
1623 GRANT SELECT ON views TO PUBLIC;
1626 -- The following views have dependencies that force them to appear out of order.
1630 * DATA_TYPE_PRIVILEGES view
1633 CREATE VIEW data_type_privileges AS
1634 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
1635 CAST(x.objschema AS sql_identifier) AS object_schema,
1636 CAST(x.objname AS sql_identifier) AS object_name,
1637 CAST(x.objtype AS character_data) AS object_type,
1638 CAST(x.objdtdid AS sql_identifier) AS dtd_identifier
1642 SELECT table_schema, table_name, 'TABLE'::text, dtd_identifier FROM columns
1644 SELECT domain_schema, domain_name, 'DOMAIN'::text, dtd_identifier FROM domains
1646 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM parameters
1648 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM routines
1649 ) AS x (objschema, objname, objtype, objdtdid);
1651 GRANT SELECT ON data_type_privileges TO PUBLIC;
1656 * ELEMENT_TYPES view
1659 CREATE VIEW element_types AS
1660 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
1661 CAST(n.nspname AS sql_identifier) AS object_schema,
1662 CAST(x.objname AS sql_identifier) AS object_name,
1663 CAST(x.objtype AS character_data) AS object_type,
1664 CAST(x.objdtdid AS sql_identifier) AS array_type_identifier,
1666 CASE WHEN nbt.nspname = 'pg_catalog' THEN format_type(bt.oid, null)
1667 ELSE 'USER-DEFINED' END AS character_data) AS data_type,
1669 CAST(null AS cardinal_number) AS character_maximum_length,
1670 CAST(null AS cardinal_number) AS character_octet_length,
1671 CAST(null AS sql_identifier) AS character_set_catalog,
1672 CAST(null AS sql_identifier) AS character_set_schema,
1673 CAST(null AS sql_identifier) AS character_set_name,
1674 CAST(null AS sql_identifier) AS collation_catalog,
1675 CAST(null AS sql_identifier) AS collation_schema,
1676 CAST(null AS sql_identifier) AS collation_name,
1677 CAST(null AS cardinal_number) AS numeric_precision,
1678 CAST(null AS cardinal_number) AS numeric_precision_radix,
1679 CAST(null AS cardinal_number) AS numeric_scale,
1680 CAST(null AS cardinal_number) AS datetime_precision,
1681 CAST(null AS character_data) AS interval_type,
1682 CAST(null AS character_data) AS interval_precision,
1684 CAST(null AS character_data) AS domain_default, -- XXX maybe a bug in the standard
1686 CAST(current_database() AS sql_identifier) AS udt_catalog,
1687 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
1688 CAST(bt.typname AS sql_identifier) AS udt_name,
1690 CAST(null AS sql_identifier) AS scope_catalog,
1691 CAST(null AS sql_identifier) AS scope_schema,
1692 CAST(null AS sql_identifier) AS scope_name,
1694 CAST(null AS cardinal_number) AS maximum_cardinality,
1695 CAST('a' || x.objdtdid AS sql_identifier) AS dtd_identifier
1697 FROM pg_namespace n, pg_type at, pg_namespace nbt, pg_type bt,
1700 SELECT c.relnamespace, CAST(c.relname AS sql_identifier),
1701 'TABLE'::text, a.attnum, a.atttypid
1702 FROM pg_class c, pg_attribute a
1703 WHERE c.oid = a.attrelid
1704 AND c.relkind IN ('r', 'v')
1705 AND attnum > 0 AND NOT attisdropped
1710 SELECT t.typnamespace, CAST(t.typname AS sql_identifier),
1711 'DOMAIN'::text, 1, t.typbasetype
1713 WHERE t.typtype = 'd'
1718 SELECT pronamespace, CAST(proname || '_' || CAST(oid AS text) AS sql_identifier),
1719 'ROUTINE'::text, (ss.x).n, (ss.x).x
1720 FROM (SELECT p.pronamespace, p.proname, p.oid,
1721 _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
1722 FROM pg_proc p) AS ss
1727 SELECT p.pronamespace, CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier),
1728 'ROUTINE'::text, 0, p.prorettype
1731 ) AS x (objschema, objname, objtype, objdtdid, objtypeid)
1733 WHERE n.oid = x.objschema
1734 AND at.oid = x.objtypeid
1735 AND (at.typelem <> 0 AND at.typlen = -1)
1736 AND at.typelem = bt.oid
1737 AND nbt.oid = bt.typnamespace
1739 AND (n.nspname, x.objname, x.objtype, x.objdtdid) IN
1740 ( SELECT object_schema, object_name, object_type, dtd_identifier
1741 FROM data_type_privileges );
1743 GRANT SELECT ON element_types TO PUBLIC;