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.27 2005/03/29 00:16:56 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 an oidvector or smallint[] into a set with integers 1..N */
34 CREATE TYPE _pg_expandoidvector_type AS (o oid, n int);
36 CREATE FUNCTION _pg_expandoidvector(oidvector)
37 RETURNS SETOF _pg_expandoidvector_type
38 LANGUAGE sql STRICT IMMUTABLE
40 from generate_series(0,array_upper($1,1),1) as g(s)';
42 CREATE TYPE _pg_expandsmallint_type AS (i smallint, n int);
44 CREATE FUNCTION _pg_expandsmallint(smallint[])
45 RETURNS SETOF _pg_expandsmallint_type
46 LANGUAGE sql STRICT IMMUTABLE
48 from generate_series(1,array_upper($1,1),1) as g(s)';
50 CREATE FUNCTION _pg_keyissubset(smallint[], smallint[]) RETURNS boolean
53 RETURNS NULL ON NULL INPUT
54 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))';
56 CREATE FUNCTION _pg_keysequal(smallint[], smallint[]) RETURNS boolean
59 RETURNS NULL ON NULL INPUT
60 AS 'select information_schema._pg_keyissubset($1, $2) and information_schema._pg_keyissubset($2, $1)';
62 CREATE FUNCTION _pg_truetypid(pg_attribute, pg_type) RETURNS oid
65 RETURNS NULL ON NULL INPUT
67 $$SELECT CASE WHEN $2.typtype = 'd' THEN $2.typbasetype ELSE $1.atttypid END$$;
69 CREATE FUNCTION _pg_truetypmod(pg_attribute, pg_type) RETURNS int4
72 RETURNS NULL ON NULL INPUT
74 $$SELECT CASE WHEN $2.typtype = 'd' THEN $2.typtypmod ELSE $1.atttypmod END$$;
76 -- these functions encapsulate knowledge about the encoding of typmod:
78 CREATE FUNCTION _pg_char_max_length(typid oid, typmod int4) RETURNS integer
81 RETURNS NULL ON NULL INPUT
84 CASE WHEN $2 = -1 /* default typmod */
86 WHEN $1 IN (1042, 1043) /* char, varchar */
88 WHEN $1 IN (1560, 1562) /* bit, varbit */
93 CREATE FUNCTION _pg_char_octet_length(typid oid, typmod int4) RETURNS integer
96 RETURNS NULL ON NULL INPUT
99 CASE WHEN $1 IN (25, 1042, 1043) /* text, char, varchar */
100 THEN CAST(2^30 AS integer)
104 CREATE FUNCTION _pg_numeric_precision(typid oid, typmod int4) RETURNS integer
107 RETURNS NULL ON NULL INPUT
111 WHEN 21 /*int2*/ THEN 16
112 WHEN 23 /*int4*/ THEN 32
113 WHEN 20 /*int8*/ THEN 64
114 WHEN 1700 /*numeric*/ THEN
117 ELSE (($2 - 4) >> 16) & 65535
119 WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/
120 WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/
124 CREATE FUNCTION _pg_numeric_precision_radix(typid oid, typmod int4) RETURNS integer
127 RETURNS NULL ON NULL INPUT
130 CASE WHEN $1 IN (21, 23, 20, 700, 701) THEN 2
131 WHEN $1 IN (1700) THEN 10
135 CREATE FUNCTION _pg_numeric_scale(typid oid, typmod int4) RETURNS integer
138 RETURNS NULL ON NULL INPUT
141 CASE WHEN $1 IN (21, 23, 20) THEN 0
142 WHEN $1 IN (1700) THEN
145 ELSE ($2 - 4) & 65535
150 CREATE FUNCTION _pg_datetime_precision(typid oid, typmod int4) RETURNS integer
153 RETURNS NULL ON NULL INPUT
156 CASE WHEN $2 = -1 /* default typmod */
158 WHEN $1 IN (1083, 1114, 1184, 1266) /* time, timestamp, same + tz */
160 WHEN $1 IN (1186) /* interval */
166 -- 20.3 INFORMATION_SCHEMA_CATALOG_NAME view appears later.
171 * CARDINAL_NUMBER domain
174 CREATE DOMAIN cardinal_number AS integer
175 CONSTRAINT cardinal_number_domain_check CHECK (value >= 0);
180 * CHARACTER_DATA domain
183 CREATE DOMAIN character_data AS character varying;
188 * SQL_IDENTIFIER domain
191 CREATE DOMAIN sql_identifier AS character varying;
196 * INFORMATION_SCHEMA_CATALOG_NAME view
199 CREATE VIEW information_schema_catalog_name AS
200 SELECT CAST(current_database() AS sql_identifier) AS catalog_name;
202 GRANT SELECT ON information_schema_catalog_name TO PUBLIC;
210 CREATE DOMAIN time_stamp AS timestamp(2)
211 DEFAULT current_timestamp(2);
216 * APPLICABLE_ROLES view
219 CREATE VIEW applicable_roles AS
220 SELECT CAST(current_user AS sql_identifier) AS grantee,
221 CAST(g.groname AS sql_identifier) AS role_name,
222 CAST('NO' AS character_data) AS is_grantable
224 FROM pg_group g, pg_user u
226 WHERE u.usesysid = ANY (g.grolist)
227 AND u.usename = current_user;
229 GRANT SELECT ON applicable_roles TO PUBLIC;
234 * CHECK_CONSTRAINTS view
237 CREATE VIEW check_constraints AS
238 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
239 CAST(rs.nspname AS sql_identifier) AS constraint_schema,
240 CAST(con.conname AS sql_identifier) AS constraint_name,
241 CAST(substring(pg_get_constraintdef(con.oid) from 7) AS character_data)
243 FROM pg_namespace rs,
245 LEFT OUTER JOIN pg_class c ON (c.oid = con.conrelid)
246 LEFT OUTER JOIN pg_type t ON (t.oid = con.contypid),
248 WHERE rs.oid = con.connamespace
249 AND u.usesysid = coalesce(c.relowner, t.typowner)
250 AND u.usename = current_user
251 AND con.contype = 'c';
253 GRANT SELECT ON check_constraints TO PUBLIC;
258 * COLUMN_DOMAIN_USAGE view
261 CREATE VIEW column_domain_usage AS
262 SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
263 CAST(nt.nspname AS sql_identifier) AS domain_schema,
264 CAST(t.typname AS sql_identifier) AS domain_name,
265 CAST(current_database() AS sql_identifier) AS table_catalog,
266 CAST(nc.nspname AS sql_identifier) AS table_schema,
267 CAST(c.relname AS sql_identifier) AS table_name,
268 CAST(a.attname AS sql_identifier) AS column_name
270 FROM pg_type t, pg_namespace nt, pg_class c, pg_namespace nc,
271 pg_attribute a, pg_user u
273 WHERE t.typnamespace = nt.oid
274 AND c.relnamespace = nc.oid
275 AND a.attrelid = c.oid
276 AND a.atttypid = t.oid
277 AND t.typowner = u.usesysid
279 AND c.relkind IN ('r', 'v')
281 AND NOT a.attisdropped
282 AND u.usename = current_user;
284 GRANT SELECT ON column_domain_usage TO PUBLIC;
292 CREATE VIEW column_privileges AS
293 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
294 CAST(grantee.name AS sql_identifier) AS grantee,
295 CAST(current_database() AS sql_identifier) AS table_catalog,
296 CAST(nc.nspname AS sql_identifier) AS table_schema,
297 CAST(c.relname AS sql_identifier) AS table_name,
298 CAST(a.attname AS sql_identifier) AS column_name,
299 CAST(pr.type AS character_data) AS privilege_type,
301 CASE WHEN aclcontains(c.relacl,
302 makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr.type, true))
303 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
310 SELECT usesysid, 0, usename FROM pg_user
312 SELECT 0, grosysid, groname FROM pg_group
314 SELECT 0, 0, 'PUBLIC'
315 ) AS grantee (usesysid, grosysid, name),
316 (SELECT 'SELECT' UNION ALL
317 SELECT 'INSERT' UNION ALL
318 SELECT 'UPDATE' UNION ALL
319 SELECT 'REFERENCES') AS pr (type)
321 WHERE a.attrelid = c.oid
322 AND c.relnamespace = nc.oid
324 AND NOT a.attisdropped
325 AND c.relkind IN ('r', 'v')
326 AND aclcontains(c.relacl,
327 makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr.type, false))
328 AND (u_grantor.usename = current_user
329 OR grantee.name = current_user
330 OR grantee.name = 'PUBLIC');
332 GRANT SELECT ON column_privileges TO PUBLIC;
337 * COLUMN_UDT_USAGE view
340 CREATE VIEW column_udt_usage AS
341 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
342 CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,
343 CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,
344 CAST(current_database() AS sql_identifier) AS table_catalog,
345 CAST(nc.nspname AS sql_identifier) AS table_schema,
346 CAST(c.relname AS sql_identifier) AS table_name,
347 CAST(a.attname AS sql_identifier) AS column_name
349 FROM pg_attribute a, pg_class c, pg_namespace nc, pg_user u,
350 (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
351 LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
352 ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
354 WHERE a.attrelid = c.oid
355 AND a.atttypid = t.oid
356 AND u.usesysid = coalesce(bt.typowner, t.typowner)
357 AND nc.oid = c.relnamespace
358 AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
359 AND u.usename = current_user;
361 GRANT SELECT ON column_udt_usage TO PUBLIC;
369 CREATE VIEW columns AS
370 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
371 CAST(nc.nspname AS sql_identifier) AS table_schema,
372 CAST(c.relname AS sql_identifier) AS table_name,
373 CAST(a.attname AS sql_identifier) AS column_name,
374 CAST(a.attnum AS cardinal_number) AS ordinal_position,
376 CASE WHEN u.usename = current_user THEN ad.adsrc ELSE null END
379 CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
384 CASE WHEN t.typtype = 'd' THEN
385 CASE WHEN bt.typelem <> 0 AND bt.typlen = -1 THEN 'ARRAY'
386 WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
387 ELSE 'USER-DEFINED' END
389 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
390 WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, null)
391 ELSE 'USER-DEFINED' END
397 _pg_char_max_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
399 AS character_maximum_length,
402 _pg_char_octet_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
404 AS character_octet_length,
407 _pg_numeric_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
409 AS numeric_precision,
412 _pg_numeric_precision_radix(_pg_truetypid(a, t), _pg_truetypmod(a, t))
414 AS numeric_precision_radix,
417 _pg_numeric_scale(_pg_truetypid(a, t), _pg_truetypmod(a, t))
422 _pg_datetime_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
424 AS datetime_precision,
426 CAST(null AS character_data) AS interval_type, -- XXX
427 CAST(null AS character_data) AS interval_precision, -- XXX
429 CAST(null AS sql_identifier) AS character_set_catalog,
430 CAST(null AS sql_identifier) AS character_set_schema,
431 CAST(null AS sql_identifier) AS character_set_name,
433 CAST(null AS sql_identifier) AS collation_catalog,
434 CAST(null AS sql_identifier) AS collation_schema,
435 CAST(null AS sql_identifier) AS collation_name,
437 CAST(CASE WHEN t.typtype = 'd' THEN current_database() ELSE null END
438 AS sql_identifier) AS domain_catalog,
439 CAST(CASE WHEN t.typtype = 'd' THEN nt.nspname ELSE null END
440 AS sql_identifier) AS domain_schema,
441 CAST(CASE WHEN t.typtype = 'd' THEN t.typname ELSE null END
442 AS sql_identifier) AS domain_name,
444 CAST(current_database() AS sql_identifier) AS udt_catalog,
445 CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,
446 CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,
448 CAST(null AS sql_identifier) AS scope_catalog,
449 CAST(null AS sql_identifier) AS scope_schema,
450 CAST(null AS sql_identifier) AS scope_name,
452 CAST(null AS cardinal_number) AS maximum_cardinality,
453 CAST(a.attnum AS sql_identifier) AS dtd_identifier,
454 CAST('NO' AS character_data) AS is_self_referencing
456 FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum),
457 pg_class c, pg_namespace nc, pg_user u,
458 (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
459 LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
460 ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
462 WHERE a.attrelid = c.oid
463 AND a.atttypid = t.oid
464 AND u.usesysid = c.relowner
465 AND nc.oid = c.relnamespace
467 AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
469 AND (u.usename = current_user
470 OR has_table_privilege(c.oid, 'SELECT')
471 OR has_table_privilege(c.oid, 'INSERT')
472 OR has_table_privilege(c.oid, 'UPDATE')
473 OR has_table_privilege(c.oid, 'REFERENCES') );
475 GRANT SELECT ON columns TO PUBLIC;
480 * CONSTRAINT_COLUMN_USAGE view
483 CREATE VIEW constraint_column_usage AS
484 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
485 CAST(tblschema AS sql_identifier) AS table_schema,
486 CAST(tblname AS sql_identifier) AS table_name,
487 CAST(colname AS sql_identifier) AS column_name,
488 CAST(current_database() AS sql_identifier) AS constraint_catalog,
489 CAST(cstrschema AS sql_identifier) AS constraint_schema,
490 CAST(cstrname AS sql_identifier) AS constraint_name
493 /* check constraints */
494 SELECT DISTINCT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
495 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_depend d, pg_namespace nc, pg_constraint c
496 WHERE nr.oid = r.relnamespace
497 AND r.oid = a.attrelid
498 AND d.refclassid = 'pg_catalog.pg_class'::regclass
499 AND d.refobjid = r.oid
500 AND d.refobjsubid = a.attnum
501 AND d.classid = 'pg_catalog.pg_constraint'::regclass
503 AND c.connamespace = nc.oid
506 AND NOT a.attisdropped
510 /* unique/primary key/foreign key constraints */
511 SELECT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
512 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc,
514 WHERE nr.oid = r.relnamespace
515 AND r.oid = a.attrelid
516 AND nc.oid = c.connamespace
517 AND (CASE WHEN c.contype = 'f' THEN r.oid = c.confrelid AND a.attnum = ANY (c.confkey)
518 ELSE r.oid = c.conrelid AND a.attnum = ANY (c.conkey) END)
519 AND NOT a.attisdropped
520 AND c.contype IN ('p', 'u', 'f')
523 ) AS x (tblschema, tblname, tblowner, colname, cstrschema, cstrname),
526 WHERE x.tblowner = u.usesysid AND u.usename = current_user;
528 GRANT SELECT ON constraint_column_usage TO PUBLIC;
533 * CONSTRAINT_TABLE_USAGE view
536 CREATE VIEW constraint_table_usage AS
537 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
538 CAST(nr.nspname AS sql_identifier) AS table_schema,
539 CAST(r.relname AS sql_identifier) AS table_name,
540 CAST(current_database() AS sql_identifier) AS constraint_catalog,
541 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
542 CAST(c.conname AS sql_identifier) AS constraint_name
544 FROM pg_constraint c, pg_namespace nc,
545 pg_class r, pg_namespace nr,
548 WHERE c.connamespace = nc.oid AND r.relnamespace = nr.oid
549 AND ( (c.contype = 'f' AND c.confrelid = r.oid)
550 OR (c.contype IN ('p', 'u') AND c.conrelid = r.oid) )
552 AND r.relowner = u.usesysid AND u.usename = current_user;
554 GRANT SELECT ON constraint_table_usage TO PUBLIC;
557 -- 20.21 DATA_TYPE_PRIVILEGES view appears later.
562 * DOMAIN_CONSTRAINTS view
565 CREATE VIEW domain_constraints AS
566 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
567 CAST(rs.nspname AS sql_identifier) AS constraint_schema,
568 CAST(con.conname AS sql_identifier) AS constraint_name,
569 CAST(current_database() AS sql_identifier) AS domain_catalog,
570 CAST(n.nspname AS sql_identifier) AS domain_schema,
571 CAST(t.typname AS sql_identifier) AS domain_name,
572 CAST(CASE WHEN condeferrable THEN 'YES' ELSE 'NO' END
573 AS character_data) AS is_deferrable,
574 CAST(CASE WHEN condeferred THEN 'YES' ELSE 'NO' END
575 AS character_data) AS initially_deferred
576 FROM pg_namespace rs, pg_namespace n, pg_constraint con, pg_type t, pg_user u
577 WHERE rs.oid = con.connamespace
578 AND n.oid = t.typnamespace
579 AND u.usesysid = t.typowner
580 AND u.usename = current_user
581 AND t.oid = con.contypid;
583 GRANT SELECT ON domain_constraints TO PUBLIC;
588 * DOMAIN_UDT_USAGE view
591 CREATE VIEW domain_udt_usage AS
592 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
593 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
594 CAST(bt.typname AS sql_identifier) AS udt_name,
595 CAST(current_database() AS sql_identifier) AS domain_catalog,
596 CAST(nt.nspname AS sql_identifier) AS domain_schema,
597 CAST(t.typname AS sql_identifier) AS domain_name
599 FROM pg_type t, pg_namespace nt,
600 pg_type bt, pg_namespace nbt,
603 WHERE t.typnamespace = nt.oid
604 AND t.typbasetype = bt.oid
605 AND bt.typnamespace = nbt.oid
607 AND bt.typowner = u.usesysid
608 AND u.usename = current_user;
610 GRANT SELECT ON domain_udt_usage TO PUBLIC;
618 CREATE VIEW domains AS
619 SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
620 CAST(nt.nspname AS sql_identifier) AS domain_schema,
621 CAST(t.typname AS sql_identifier) AS domain_name,
624 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
625 WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
626 ELSE 'USER-DEFINED' END
631 _pg_char_max_length(t.typbasetype, t.typtypmod)
633 AS character_maximum_length,
636 _pg_char_octet_length(t.typbasetype, t.typtypmod)
638 AS character_octet_length,
640 CAST(null AS sql_identifier) AS character_set_catalog,
641 CAST(null AS sql_identifier) AS character_set_schema,
642 CAST(null AS sql_identifier) AS character_set_name,
644 CAST(null AS sql_identifier) AS collation_catalog,
645 CAST(null AS sql_identifier) AS collation_schema,
646 CAST(null AS sql_identifier) AS collation_name,
649 _pg_numeric_precision(t.typbasetype, t.typtypmod)
651 AS numeric_precision,
654 _pg_numeric_precision_radix(t.typbasetype, t.typtypmod)
656 AS numeric_precision_radix,
659 _pg_numeric_scale(t.typbasetype, t.typtypmod)
664 _pg_datetime_precision(t.typbasetype, t.typtypmod)
666 AS datetime_precision,
668 CAST(null AS character_data) AS interval_type, -- XXX
669 CAST(null AS character_data) AS interval_precision, -- XXX
671 CAST(t.typdefault AS character_data) AS domain_default,
673 CAST(current_database() AS sql_identifier) AS udt_catalog,
674 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
675 CAST(bt.typname AS sql_identifier) AS udt_name,
677 CAST(null AS sql_identifier) AS scope_catalog,
678 CAST(null AS sql_identifier) AS scope_schema,
679 CAST(null AS sql_identifier) AS scope_name,
681 CAST(null AS cardinal_number) AS maximum_cardinality,
682 CAST(1 AS sql_identifier) AS dtd_identifier
684 FROM pg_type t, pg_namespace nt,
685 pg_type bt, pg_namespace nbt
687 WHERE t.typnamespace = nt.oid
688 AND t.typbasetype = bt.oid
689 AND bt.typnamespace = nbt.oid
692 GRANT SELECT ON domains TO PUBLIC;
695 -- 20.27 ELEMENT_TYPES view appears later.
703 CREATE VIEW enabled_roles AS
704 SELECT CAST(g.groname AS sql_identifier) AS role_name
705 FROM pg_group g, pg_user u
706 WHERE u.usesysid = ANY (g.grolist)
707 AND u.usename = current_user;
709 GRANT SELECT ON enabled_roles TO PUBLIC;
714 * KEY_COLUMN_USAGE view
717 CREATE VIEW key_column_usage AS
718 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
719 CAST(nc_nspname AS sql_identifier) AS constraint_schema,
720 CAST(conname AS sql_identifier) AS constraint_name,
721 CAST(current_database() AS sql_identifier) AS table_catalog,
722 CAST(nr_nspname AS sql_identifier) AS table_schema,
723 CAST(relname AS sql_identifier) AS table_name,
724 CAST(a.attname AS sql_identifier) AS column_name,
725 CAST((ss.x).n AS cardinal_number) AS ordinal_position
728 (SELECT r.oid, nc.nspname AS nc_nspname, c.conname,
729 nr.nspname AS nr_nspname, r.relname,
730 _pg_expandsmallint(c.conkey) AS x
731 FROM pg_namespace nr, pg_class r, pg_namespace nc,
732 pg_constraint c, pg_user u
733 WHERE nr.oid = r.relnamespace
734 AND r.oid = c.conrelid
735 AND nc.oid = c.connamespace
736 AND c.contype IN ('p', 'u', 'f')
738 AND r.relowner = u.usesysid
739 AND u.usename = current_user) AS ss
740 WHERE ss.oid = a.attrelid
741 AND a.attnum = (ss.x).i
742 AND NOT a.attisdropped;
744 GRANT SELECT ON key_column_usage TO PUBLIC;
752 CREATE VIEW parameters AS
753 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
754 CAST(n_nspname AS sql_identifier) AS specific_schema,
755 CAST(proname || '_' || CAST(p_oid AS text) AS sql_identifier) AS specific_name,
756 CAST((ss.x).n AS cardinal_number) AS ordinal_position,
757 CAST('IN' AS character_data) AS parameter_mode,
758 CAST('NO' AS character_data) AS is_result,
759 CAST('NO' AS character_data) AS as_locator,
760 CAST(NULLIF(proargnames[(ss.x).n], '') AS sql_identifier) AS parameter_name,
762 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
763 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
764 ELSE 'USER-DEFINED' END AS character_data)
766 CAST(null AS cardinal_number) AS character_maximum_length,
767 CAST(null AS cardinal_number) AS character_octet_length,
768 CAST(null AS sql_identifier) AS character_set_catalog,
769 CAST(null AS sql_identifier) AS character_set_schema,
770 CAST(null AS sql_identifier) AS character_set_name,
771 CAST(null AS sql_identifier) AS collation_catalog,
772 CAST(null AS sql_identifier) AS collation_schema,
773 CAST(null AS sql_identifier) AS collation_name,
774 CAST(null AS cardinal_number) AS numeric_precision,
775 CAST(null AS cardinal_number) AS numeric_precision_radix,
776 CAST(null AS cardinal_number) AS numeric_scale,
777 CAST(null AS cardinal_number) AS datetime_precision,
778 CAST(null AS character_data) AS interval_type,
779 CAST(null AS character_data) AS interval_precision,
780 CAST(current_database() AS sql_identifier) AS udt_catalog,
781 CAST(nt.nspname AS sql_identifier) AS udt_schema,
782 CAST(t.typname AS sql_identifier) AS udt_name,
783 CAST(null AS sql_identifier) AS scope_catalog,
784 CAST(null AS sql_identifier) AS scope_schema,
785 CAST(null AS sql_identifier) AS scope_name,
786 CAST(null AS cardinal_number) AS maximum_cardinality,
787 CAST((ss.x).n AS sql_identifier) AS dtd_identifier
789 FROM pg_type t, pg_namespace nt,
790 (SELECT n.nspname AS n_nspname, p.proname, p.oid AS p_oid,
791 p.proargnames, _pg_expandoidvector(p.proargtypes) AS x
792 FROM pg_namespace n, pg_proc p, pg_user u
793 WHERE n.oid = p.pronamespace
794 AND p.proowner = u.usesysid
795 AND (u.usename = current_user OR
796 has_function_privilege(p.oid, 'EXECUTE'))) AS ss
797 WHERE t.oid = (ss.x).o AND t.typnamespace = nt.oid;
799 GRANT SELECT ON parameters TO PUBLIC;
804 * REFERENTIAL_CONSTRAINTS view
807 CREATE VIEW referential_constraints AS
808 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
809 CAST(ncon.nspname AS sql_identifier) AS constraint_schema,
810 CAST(con.conname AS sql_identifier) AS constraint_name,
812 CASE WHEN npkc.nspname IS NULL THEN NULL
813 ELSE current_database() END
814 AS sql_identifier) AS unique_constraint_catalog,
815 CAST(npkc.nspname AS sql_identifier) AS unique_constraint_schema,
816 CAST(pkc.conname AS sql_identifier) AS unique_constraint_name,
819 CASE con.confmatchtype WHEN 'f' THEN 'FULL'
820 WHEN 'p' THEN 'PARTIAL'
821 WHEN 'u' THEN 'NONE' END
822 AS character_data) AS match_option,
825 CASE con.confupdtype WHEN 'c' THEN 'CASCADE'
826 WHEN 'n' THEN 'SET NULL'
827 WHEN 'd' THEN 'SET DEFAULT'
828 WHEN 'r' THEN 'RESTRICT'
829 WHEN 'a' THEN 'NO ACTION' END
830 AS character_data) AS update_rule,
833 CASE con.confdeltype WHEN 'c' THEN 'CASCADE'
834 WHEN 'n' THEN 'SET NULL'
835 WHEN 'd' THEN 'SET DEFAULT'
836 WHEN 'r' THEN 'RESTRICT'
837 WHEN 'a' THEN 'NO ACTION' END
838 AS character_data) AS delete_rule
840 FROM (pg_namespace ncon INNER JOIN pg_constraint con ON ncon.oid = con.connamespace
841 INNER JOIN pg_class c ON con.conrelid = c.oid
842 INNER JOIN pg_user u ON c.relowner = u.usesysid)
844 (pg_constraint pkc INNER JOIN pg_namespace npkc ON pkc.connamespace = npkc.oid)
845 ON con.confrelid = pkc.conrelid AND _pg_keysequal(con.confkey, pkc.conkey)
847 WHERE c.relkind = 'r'
848 AND con.contype = 'f'
849 AND (pkc.contype IN ('p', 'u') OR pkc.contype IS NULL)
850 AND u.usename = current_user;
852 GRANT SELECT ON referential_constraints TO PUBLIC;
857 * ROLE_COLUMN_GRANTS view
860 CREATE VIEW role_column_grants AS
861 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
862 CAST(g_grantee.groname AS sql_identifier) AS grantee,
863 CAST(current_database() AS sql_identifier) AS table_catalog,
864 CAST(nc.nspname AS sql_identifier) AS table_schema,
865 CAST(c.relname AS sql_identifier) AS table_name,
866 CAST(a.attname AS sql_identifier) AS column_name,
867 CAST(pr.type AS character_data) AS privilege_type,
869 CASE WHEN aclcontains(c.relacl,
870 makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, pr.type, true))
871 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
878 (SELECT 'SELECT' UNION ALL
879 SELECT 'INSERT' UNION ALL
880 SELECT 'UPDATE' UNION ALL
881 SELECT 'REFERENCES') AS pr (type)
883 WHERE a.attrelid = c.oid
884 AND c.relnamespace = nc.oid
886 AND NOT a.attisdropped
887 AND c.relkind IN ('r', 'v')
888 AND aclcontains(c.relacl,
889 makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, pr.type, false))
890 AND g_grantee.groname IN (SELECT role_name FROM enabled_roles);
892 GRANT SELECT ON role_column_grants TO PUBLIC;
897 * ROLE_ROUTINE_GRANTS view
900 CREATE VIEW role_routine_grants AS
901 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
902 CAST(g_grantee.groname AS sql_identifier) AS grantee,
903 CAST(current_database() AS sql_identifier) AS specific_catalog,
904 CAST(n.nspname AS sql_identifier) AS specific_schema,
905 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
906 CAST(current_database() AS sql_identifier) AS routine_catalog,
907 CAST(n.nspname AS sql_identifier) AS routine_schema,
908 CAST(p.proname AS sql_identifier) AS routine_name,
909 CAST('EXECUTE' AS character_data) AS privilege_type,
911 CASE WHEN aclcontains(p.proacl,
912 makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, 'EXECUTE', true))
913 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
920 WHERE p.pronamespace = n.oid
921 AND aclcontains(p.proacl,
922 makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, 'EXECUTE', false))
923 AND g_grantee.groname IN (SELECT role_name FROM enabled_roles);
925 GRANT SELECT ON role_routine_grants TO PUBLIC;
930 * ROLE_TABLE_GRANTS view
933 CREATE VIEW role_table_grants AS
934 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
935 CAST(g_grantee.groname AS sql_identifier) AS grantee,
936 CAST(current_database() AS sql_identifier) AS table_catalog,
937 CAST(nc.nspname AS sql_identifier) AS table_schema,
938 CAST(c.relname AS sql_identifier) AS table_name,
939 CAST(pr.type AS character_data) AS privilege_type,
941 CASE WHEN aclcontains(c.relacl,
942 makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, pr.type, true))
943 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable,
944 CAST('NO' AS character_data) AS with_hierarchy
950 (SELECT 'SELECT' UNION ALL
951 SELECT 'DELETE' UNION ALL
952 SELECT 'INSERT' UNION ALL
953 SELECT 'UPDATE' UNION ALL
954 SELECT 'REFERENCES' UNION ALL
955 SELECT 'RULE' UNION ALL
956 SELECT 'TRIGGER') AS pr (type)
958 WHERE c.relnamespace = nc.oid
959 AND c.relkind IN ('r', 'v')
960 AND aclcontains(c.relacl,
961 makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, pr.type, false))
962 AND g_grantee.groname IN (SELECT role_name FROM enabled_roles);
964 GRANT SELECT ON role_table_grants TO PUBLIC;
969 * ROLE_USAGE_GRANTS view
972 -- See USAGE_PRIVILEGES.
974 CREATE VIEW role_usage_grants AS
975 SELECT CAST(null AS sql_identifier) AS grantor,
976 CAST(null AS sql_identifier) AS grantee,
977 CAST(current_database() AS sql_identifier) AS object_catalog,
978 CAST(null AS sql_identifier) AS object_schema,
979 CAST(null AS sql_identifier) AS object_name,
980 CAST(null AS character_data) AS object_type,
981 CAST('USAGE' AS character_data) AS privilege_type,
982 CAST(null AS character_data) AS is_grantable
986 GRANT SELECT ON role_usage_grants TO PUBLIC;
991 * ROUTINE_PRIVILEGES view
994 CREATE VIEW routine_privileges AS
995 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
996 CAST(grantee.name AS sql_identifier) AS grantee,
997 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('EXECUTE' AS character_data) AS privilege_type,
1005 CASE WHEN aclcontains(p.proacl,
1006 makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, 'EXECUTE', true))
1007 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
1013 SELECT usesysid, 0, usename FROM pg_user
1015 SELECT 0, grosysid, groname FROM pg_group
1017 SELECT 0, 0, 'PUBLIC'
1018 ) AS grantee (usesysid, grosysid, name)
1020 WHERE p.pronamespace = n.oid
1021 AND aclcontains(p.proacl,
1022 makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, 'EXECUTE', false))
1023 AND (u_grantor.usename = current_user
1024 OR grantee.name = current_user
1025 OR grantee.name = 'PUBLIC');
1027 GRANT SELECT ON routine_privileges TO PUBLIC;
1035 CREATE VIEW routines AS
1036 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
1037 CAST(n.nspname AS sql_identifier) AS specific_schema,
1038 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1039 CAST(current_database() AS sql_identifier) AS routine_catalog,
1040 CAST(n.nspname AS sql_identifier) AS routine_schema,
1041 CAST(p.proname AS sql_identifier) AS routine_name,
1042 CAST('FUNCTION' AS character_data) AS routine_type,
1043 CAST(null AS sql_identifier) AS module_catalog,
1044 CAST(null AS sql_identifier) AS module_schema,
1045 CAST(null AS sql_identifier) AS module_name,
1046 CAST(null AS sql_identifier) AS udt_catalog,
1047 CAST(null AS sql_identifier) AS udt_schema,
1048 CAST(null AS sql_identifier) AS udt_name,
1051 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
1052 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
1053 ELSE 'USER-DEFINED' END AS character_data)
1055 CAST(null AS cardinal_number) AS character_maximum_length,
1056 CAST(null AS cardinal_number) AS character_octet_length,
1057 CAST(null AS sql_identifier) AS character_set_catalog,
1058 CAST(null AS sql_identifier) AS character_set_schema,
1059 CAST(null AS sql_identifier) AS character_set_name,
1060 CAST(null AS sql_identifier) AS collation_catalog,
1061 CAST(null AS sql_identifier) AS collation_schema,
1062 CAST(null AS sql_identifier) AS collation_name,
1063 CAST(null AS cardinal_number) AS numeric_precision,
1064 CAST(null AS cardinal_number) AS numeric_precision_radix,
1065 CAST(null AS cardinal_number) AS numeric_scale,
1066 CAST(null AS cardinal_number) AS datetime_precision,
1067 CAST(null AS character_data) AS interval_type,
1068 CAST(null AS character_data) AS interval_precision,
1069 CAST(current_database() AS sql_identifier) AS type_udt_catalog,
1070 CAST(nt.nspname AS sql_identifier) AS type_udt_schema,
1071 CAST(t.typname AS sql_identifier) AS type_udt_name,
1072 CAST(null AS sql_identifier) AS scope_catalog,
1073 CAST(null AS sql_identifier) AS scope_schema,
1074 CAST(null AS sql_identifier) AS scope_name,
1075 CAST(null AS cardinal_number) AS maximum_cardinality,
1076 CAST(0 AS sql_identifier) AS dtd_identifier,
1078 CAST(CASE WHEN l.lanname = 'sql' THEN 'SQL' ELSE 'EXTERNAL' END AS character_data)
1081 CASE WHEN u.usename = current_user THEN p.prosrc ELSE null END
1082 AS character_data) AS routine_definition,
1084 CASE WHEN l.lanname = 'c' THEN p.prosrc ELSE null END
1085 AS character_data) AS external_name,
1086 CAST(upper(l.lanname) AS character_data) AS external_language,
1088 CAST('GENERAL' AS character_data) AS parameter_style,
1089 CAST(CASE WHEN p.provolatile = 'i' THEN 'YES' ELSE 'NO' END AS character_data) AS is_deterministic,
1090 CAST('MODIFIES' AS character_data) AS sql_data_access,
1091 CAST(CASE WHEN p.proisstrict THEN 'YES' ELSE 'NO' END AS character_data) AS is_null_call,
1092 CAST(null AS character_data) AS sql_path,
1093 CAST('YES' AS character_data) AS schema_level_routine,
1094 CAST(0 AS cardinal_number) AS max_dynamic_result_sets,
1095 CAST(null AS character_data) AS is_user_defined_cast,
1096 CAST(null AS character_data) AS is_implicitly_invocable,
1097 CAST(CASE WHEN p.prosecdef THEN 'DEFINER' ELSE 'INVOKER' END AS character_data) AS security_type,
1098 CAST(null AS sql_identifier) AS to_sql_specific_catalog,
1099 CAST(null AS sql_identifier) AS to_sql_specific_schema,
1100 CAST(null AS sql_identifier) AS to_sql_specific_name,
1101 CAST('NO' AS character_data) AS as_locator
1103 FROM pg_namespace n, pg_proc p, pg_language l, pg_user u,
1104 pg_type t, pg_namespace nt
1106 WHERE n.oid = p.pronamespace AND p.prolang = l.oid AND p.proowner = u.usesysid
1107 AND p.prorettype = t.oid AND t.typnamespace = nt.oid
1108 AND (u.usename = current_user OR has_function_privilege(p.oid, 'EXECUTE'));
1110 GRANT SELECT ON routines TO PUBLIC;
1118 CREATE VIEW schemata AS
1119 SELECT CAST(current_database() AS sql_identifier) AS catalog_name,
1120 CAST(n.nspname AS sql_identifier) AS schema_name,
1121 CAST(u.usename AS sql_identifier) AS schema_owner,
1122 CAST(null AS sql_identifier) AS default_character_set_catalog,
1123 CAST(null AS sql_identifier) AS default_character_set_schema,
1124 CAST(null AS sql_identifier) AS default_character_set_name,
1125 CAST(null AS character_data) AS sql_path
1126 FROM pg_namespace n, pg_user u
1127 WHERE n.nspowner = u.usesysid AND u.usename = current_user;
1129 GRANT SELECT ON schemata TO PUBLIC;
1134 * SQL_FEATURES table
1137 CREATE TABLE sql_features (
1138 feature_id character_data,
1139 feature_name character_data,
1140 sub_feature_id character_data,
1141 sub_feature_name character_data,
1142 is_supported character_data,
1143 is_verified_by character_data,
1144 comments character_data
1147 -- Will be filled with external data by initdb.
1149 GRANT SELECT ON sql_features TO PUBLIC;
1154 * SQL_IMPLEMENTATION_INFO table
1157 -- Note: Implementation information items are defined in ISO 9075-3:1999,
1160 CREATE TABLE sql_implementation_info (
1161 implementation_info_id character_data,
1162 implementation_info_name character_data,
1163 integer_value cardinal_number,
1164 character_value character_data,
1165 comments character_data
1168 INSERT INTO sql_implementation_info VALUES ('10003', 'CATALOG NAME', NULL, 'Y', NULL);
1169 INSERT INTO sql_implementation_info VALUES ('10004', 'COLLATING SEQUENCE', NULL, '', 'not supported');
1170 INSERT INTO sql_implementation_info VALUES ('23', 'CURSOR COMMIT BEHAVIOR', 1, NULL, 'close cursors and retain prepared statements');
1171 INSERT INTO sql_implementation_info VALUES ('2', 'DATA SOURCE NAME', NULL, '', NULL);
1172 INSERT INTO sql_implementation_info VALUES ('17', 'DBMS NAME', NULL, (select trim(trailing ' ' from substring(version() from '^[^0-9]*'))), NULL);
1173 INSERT INTO sql_implementation_info VALUES ('18', 'DBMS VERSION', NULL, '???', NULL); -- filled by initdb
1174 INSERT INTO sql_implementation_info VALUES ('26', 'DEFAULT TRANSACTION ISOLATION', 2, NULL, 'READ COMMITTED; user-settable');
1175 INSERT INTO sql_implementation_info VALUES ('28', 'IDENTIFIER CASE', 3, NULL, 'stored in mixed case - case sensitive');
1176 INSERT INTO sql_implementation_info VALUES ('85', 'NULL COLLATION', 0, NULL, 'nulls higher than non-nulls');
1177 INSERT INTO sql_implementation_info VALUES ('13', 'SERVER NAME', NULL, '', NULL);
1178 INSERT INTO sql_implementation_info VALUES ('94', 'SPECIAL CHARACTERS', NULL, '', 'all non-ASCII characters allowed');
1179 INSERT INTO sql_implementation_info VALUES ('46', 'TRANSACTION CAPABLE', 2, NULL, 'both DML and DDL');
1181 GRANT SELECT ON sql_implementation_info TO PUBLIC;
1186 * SQL_LANGUAGES table
1189 CREATE TABLE sql_languages (
1190 sql_language_source character_data,
1191 sql_language_year character_data,
1192 sql_language_conformance character_data,
1193 sql_language_integrity character_data,
1194 sql_language_implementation character_data,
1195 sql_language_binding_style character_data,
1196 sql_language_programming_language character_data
1199 INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'DIRECT', NULL);
1200 INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
1202 GRANT SELECT ON sql_languages TO PUBLIC;
1207 * SQL_PACKAGES table
1210 CREATE TABLE sql_packages (
1211 feature_id character_data,
1212 feature_name character_data,
1213 is_supported character_data,
1214 is_verified_by character_data,
1215 comments character_data
1218 INSERT INTO sql_packages VALUES ('PKG000', 'Core', 'NO', NULL, '');
1219 INSERT INTO sql_packages VALUES ('PKG001', 'Enhanced datetime facilities', 'YES', NULL, '');
1220 INSERT INTO sql_packages VALUES ('PKG002', 'Enhanced integrity management', 'NO', NULL, '');
1221 INSERT INTO sql_packages VALUES ('PKG003', 'OLAP facilities', 'NO', NULL, '');
1222 INSERT INTO sql_packages VALUES ('PKG004', 'PSM', 'NO', NULL, 'PL/pgSQL is similar.');
1223 INSERT INTO sql_packages VALUES ('PKG005', 'CLI', 'NO', NULL, 'ODBC is similar.');
1224 INSERT INTO sql_packages VALUES ('PKG006', 'Basic object support', 'NO', NULL, '');
1225 INSERT INTO sql_packages VALUES ('PKG007', 'Enhanced object support', 'NO', NULL, '');
1226 INSERT INTO sql_packages VALUES ('PKG008', 'Active database', 'NO', NULL, '');
1227 INSERT INTO sql_packages VALUES ('PKG010', 'OLAP', 'NO', NULL, 'NO');
1229 GRANT SELECT ON sql_packages TO PUBLIC;
1237 -- Note: Sizing items are defined in ISO 9075-3:1999, clause 7.2.
1239 CREATE TABLE sql_sizing (
1240 sizing_id cardinal_number,
1241 sizing_name character_data,
1242 supported_value cardinal_number,
1243 comments character_data
1246 INSERT INTO sql_sizing VALUES (34, 'MAXIMUM CATALOG NAME LENGTH', 63, NULL);
1247 INSERT INTO sql_sizing VALUES (30, 'MAXIMUM COLUMN NAME LENGTH', 63, NULL);
1248 INSERT INTO sql_sizing VALUES (97, 'MAXIMUM COLUMNS IN GROUP BY', 0, NULL);
1249 INSERT INTO sql_sizing VALUES (99, 'MAXIMUM COLUMNS IN ORDER BY', 0, NULL);
1250 INSERT INTO sql_sizing VALUES (100, 'MAXIMUM COLUMNS IN SELECT', 1664, NULL); -- match MaxTupleAttributeNumber
1251 INSERT INTO sql_sizing VALUES (101, 'MAXIMUM COLUMNS IN TABLE', 1600, NULL); -- match MaxHeapAttributeNumber
1252 INSERT INTO sql_sizing VALUES (1, 'MAXIMUM CONCURRENT ACTIVITIES', 0, NULL);
1253 INSERT INTO sql_sizing VALUES (31, 'MAXIMUM CURSOR NAME LENGTH', 63, NULL);
1254 INSERT INTO sql_sizing VALUES (0, 'MAXIMUM DRIVER CONNECTIONS', NULL, NULL);
1255 INSERT INTO sql_sizing VALUES (10005, 'MAXIMUM IDENTIFIER LENGTH', 63, NULL);
1256 INSERT INTO sql_sizing VALUES (32, 'MAXIMUM SCHEMA NAME LENGTH', 63, NULL);
1257 INSERT INTO sql_sizing VALUES (20000, 'MAXIMUM STATEMENT OCTETS', 0, NULL);
1258 INSERT INTO sql_sizing VALUES (20001, 'MAXIMUM STATEMENT OCTETS DATA', 0, NULL);
1259 INSERT INTO sql_sizing VALUES (20002, 'MAXIMUM STATEMENT OCTETS SCHEMA', 0, NULL);
1260 INSERT INTO sql_sizing VALUES (35, 'MAXIMUM TABLE NAME LENGTH', 63, NULL);
1261 INSERT INTO sql_sizing VALUES (106, 'MAXIMUM TABLES IN SELECT', 0, NULL);
1262 INSERT INTO sql_sizing VALUES (107, 'MAXIMUM USER NAME LENGTH', 63, NULL);
1263 INSERT INTO sql_sizing VALUES (25000, 'MAXIMUM CURRENT DEFAULT TRANSFORM GROUP LENGTH', NULL, NULL);
1264 INSERT INTO sql_sizing VALUES (25001, 'MAXIMUM CURRENT TRANSFORM GROUP LENGTH', NULL, NULL);
1265 INSERT INTO sql_sizing VALUES (25002, 'MAXIMUM CURRENT PATH LENGTH', 0, NULL);
1266 INSERT INTO sql_sizing VALUES (25003, 'MAXIMUM CURRENT ROLE LENGTH', NULL, NULL);
1267 INSERT INTO sql_sizing VALUES (25004, 'MAXIMUM SESSION USER LENGTH', 63, NULL);
1268 INSERT INTO sql_sizing VALUES (25005, 'MAXIMUM SYSTEM USER LENGTH', 63, NULL);
1271 SET supported_value = (SELECT typlen-1 FROM pg_catalog.pg_type WHERE typname = 'name'),
1272 comments = 'Might be less, depending on character set.'
1273 WHERE supported_value = 63;
1275 GRANT SELECT ON sql_sizing TO PUBLIC;
1280 * SQL_SIZING_PROFILES table
1283 -- The data in this table are defined by various profiles of SQL.
1284 -- Since we don't have any information about such profiles, we provide
1287 CREATE TABLE sql_sizing_profiles (
1288 sizing_id cardinal_number,
1289 sizing_name character_data,
1290 profile_id character_data,
1291 required_value cardinal_number,
1292 comments character_data
1295 GRANT SELECT ON sql_sizing_profiles TO PUBLIC;
1300 * TABLE_CONSTRAINTS view
1303 CREATE VIEW table_constraints AS
1304 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1305 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
1306 CAST(c.conname AS sql_identifier) AS constraint_name,
1307 CAST(current_database() AS sql_identifier) AS table_catalog,
1308 CAST(nr.nspname AS sql_identifier) AS table_schema,
1309 CAST(r.relname AS sql_identifier) AS table_name,
1311 CASE c.contype WHEN 'c' THEN 'CHECK'
1312 WHEN 'f' THEN 'FOREIGN KEY'
1313 WHEN 'p' THEN 'PRIMARY KEY'
1314 WHEN 'u' THEN 'UNIQUE' END
1315 AS character_data) AS constraint_type,
1316 CAST(CASE WHEN c.condeferrable THEN 'YES' ELSE 'NO' END AS character_data)
1318 CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS character_data)
1319 AS initially_deferred
1321 FROM pg_namespace nc,
1327 WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace
1328 AND c.conrelid = r.oid AND r.relowner = u.usesysid
1330 AND u.usename = current_user;
1332 -- FIMXE: Not-null constraints are missing here.
1334 GRANT SELECT ON table_constraints TO PUBLIC;
1339 * TABLE_PRIVILEGES view
1342 CREATE VIEW table_privileges AS
1343 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
1344 CAST(grantee.name AS sql_identifier) AS grantee,
1345 CAST(current_database() AS sql_identifier) AS table_catalog,
1346 CAST(nc.nspname AS sql_identifier) AS table_schema,
1347 CAST(c.relname AS sql_identifier) AS table_name,
1348 CAST(pr.type AS character_data) AS privilege_type,
1350 CASE WHEN aclcontains(c.relacl,
1351 makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr.type, true))
1352 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable,
1353 CAST('NO' AS character_data) AS with_hierarchy
1359 SELECT usesysid, 0, usename FROM pg_user
1361 SELECT 0, grosysid, groname FROM pg_group
1363 SELECT 0, 0, 'PUBLIC'
1364 ) AS grantee (usesysid, grosysid, name),
1365 (SELECT 'SELECT' UNION ALL
1366 SELECT 'DELETE' UNION ALL
1367 SELECT 'INSERT' UNION ALL
1368 SELECT 'UPDATE' UNION ALL
1369 SELECT 'REFERENCES' UNION ALL
1370 SELECT 'RULE' UNION ALL
1371 SELECT 'TRIGGER') AS pr (type)
1373 WHERE c.relnamespace = nc.oid
1374 AND c.relkind IN ('r', 'v')
1375 AND aclcontains(c.relacl,
1376 makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr.type, false))
1377 AND (u_grantor.usename = current_user
1378 OR grantee.name = current_user
1379 OR grantee.name = 'PUBLIC');
1381 GRANT SELECT ON table_privileges TO PUBLIC;
1389 CREATE VIEW tables AS
1390 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
1391 CAST(nc.nspname AS sql_identifier) AS table_schema,
1392 CAST(c.relname AS sql_identifier) AS table_name,
1395 CASE WHEN nc.nspname LIKE 'pg!_temp!_%' ESCAPE '!' THEN 'LOCAL TEMPORARY'
1396 WHEN c.relkind = 'r' THEN 'BASE TABLE'
1397 WHEN c.relkind = 'v' THEN 'VIEW'
1399 AS character_data) AS table_type,
1401 CAST(null AS sql_identifier) AS self_referencing_column_name,
1402 CAST(null AS character_data) AS reference_generation,
1404 CAST(null AS sql_identifier) AS user_defined_type_catalog,
1405 CAST(null AS sql_identifier) AS user_defined_type_schema,
1406 CAST(null AS sql_identifier) AS user_defined_name
1408 FROM pg_namespace nc, pg_class c, pg_user u
1410 WHERE c.relnamespace = nc.oid AND u.usesysid = c.relowner
1411 AND c.relkind IN ('r', 'v')
1412 AND (u.usename = current_user
1413 OR has_table_privilege(c.oid, 'SELECT')
1414 OR has_table_privilege(c.oid, 'INSERT')
1415 OR has_table_privilege(c.oid, 'UPDATE')
1416 OR has_table_privilege(c.oid, 'DELETE')
1417 OR has_table_privilege(c.oid, 'RULE')
1418 OR has_table_privilege(c.oid, 'REFERENCES')
1419 OR has_table_privilege(c.oid, 'TRIGGER') );
1421 GRANT SELECT ON tables TO PUBLIC;
1426 * TRIGGERED_UPDATE_COLUMNS view
1429 -- PostgreSQL doesn't allow the specification of individual triggered
1430 -- update columns, so this view is empty.
1432 CREATE VIEW triggered_update_columns AS
1433 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1434 CAST(null AS sql_identifier) AS trigger_schema,
1435 CAST(null AS sql_identifier) AS trigger_name,
1436 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1437 CAST(null AS sql_identifier) AS event_object_schema,
1438 CAST(null AS sql_identifier) AS event_object_table,
1439 CAST(null AS sql_identifier) AS event_object_column
1442 GRANT SELECT ON triggered_update_columns TO PUBLIC;
1450 CREATE VIEW triggers AS
1451 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1452 CAST(n.nspname AS sql_identifier) AS trigger_schema,
1453 CAST(t.tgname AS sql_identifier) AS trigger_name,
1454 CAST(em.text AS character_data) AS event_manipulation,
1455 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1456 CAST(n.nspname AS sql_identifier) AS event_object_schema,
1457 CAST(c.relname AS sql_identifier) AS event_object_table,
1458 CAST(null AS cardinal_number) AS action_order,
1459 CAST(null AS character_data) AS action_condition,
1461 substring(pg_get_triggerdef(t.oid) from
1462 position('EXECUTE PROCEDURE' in substring(pg_get_triggerdef(t.oid) from 48)) + 47)
1463 AS character_data) AS action_statement,
1465 CASE WHEN t.tgtype & 1 = 1 THEN 'ROW' ELSE 'STATEMENT' END
1466 AS character_data) AS action_orientation,
1468 CASE WHEN t.tgtype & 2 = 2 THEN 'BEFORE' ELSE 'AFTER' END
1469 AS character_data) AS condition_timing,
1470 CAST(null AS sql_identifier) AS condition_reference_old_table,
1471 CAST(null AS sql_identifier) AS condition_reference_new_table
1473 FROM pg_namespace n, pg_class c, pg_trigger t, pg_user u,
1474 (SELECT 4, 'INSERT' UNION ALL
1475 SELECT 8, 'DELETE' UNION ALL
1476 SELECT 16, 'UPDATE') AS em (num, text)
1478 WHERE n.oid = c.relnamespace
1479 AND c.oid = t.tgrelid
1480 AND c.relowner = u.usesysid
1481 AND t.tgtype & em.num <> 0
1482 AND NOT t.tgisconstraint
1483 AND u.usename = current_user;
1485 GRANT SELECT ON triggers TO PUBLIC;
1490 * USAGE_PRIVILEGES view
1493 -- Of the things currently implemented in PostgreSQL, usage privileges
1494 -- apply only to domains. Since domains have no real privileges, we
1495 -- represent all domains with implicit usage privilege here.
1497 CREATE VIEW usage_privileges AS
1498 SELECT CAST(u.usename AS sql_identifier) AS grantor,
1499 CAST('PUBLIC' AS sql_identifier) AS grantee,
1500 CAST(current_database() AS sql_identifier) AS object_catalog,
1501 CAST(n.nspname AS sql_identifier) AS object_schema,
1502 CAST(t.typname AS sql_identifier) AS object_name,
1503 CAST('DOMAIN' AS character_data) AS object_type,
1504 CAST('USAGE' AS character_data) AS privilege_type,
1505 CAST('NO' AS character_data) AS is_grantable
1511 WHERE u.usesysid = t.typowner
1512 AND t.typnamespace = n.oid
1513 AND t.typtype = 'd';
1515 GRANT SELECT ON usage_privileges TO PUBLIC;
1523 CREATE VIEW view_column_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,
1531 CAST(a.attname AS sql_identifier) AS column_name
1533 FROM pg_user, pg_namespace nv, pg_class v, pg_depend dv,
1534 pg_depend dt, pg_class t, pg_namespace nt,
1535 pg_attribute a, pg_user u
1537 WHERE nv.oid = v.relnamespace
1539 AND v.oid = dv.refobjid
1540 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
1541 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
1542 AND dv.deptype = 'i'
1543 AND dv.objid = dt.objid
1544 AND dv.refobjid <> dt.refobjid
1545 AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
1546 AND dt.refclassid = 'pg_catalog.pg_class'::regclass
1547 AND dt.refobjid = t.oid
1548 AND t.relnamespace = nt.oid
1549 AND t.relkind IN ('r', 'v')
1550 AND t.oid = a.attrelid
1551 AND dt.refobjsubid = a.attnum
1552 AND t.relowner = u.usesysid AND u.usename = current_user;
1554 GRANT SELECT ON view_column_usage TO PUBLIC;
1562 CREATE VIEW view_table_usage AS
1564 CAST(current_database() AS sql_identifier) AS view_catalog,
1565 CAST(nv.nspname AS sql_identifier) AS view_schema,
1566 CAST(v.relname AS sql_identifier) AS view_name,
1567 CAST(current_database() AS sql_identifier) AS table_catalog,
1568 CAST(nt.nspname AS sql_identifier) AS table_schema,
1569 CAST(t.relname AS sql_identifier) AS table_name
1571 FROM pg_user, pg_namespace nv, pg_class v, pg_depend dv,
1572 pg_depend dt, pg_class t, pg_namespace nt,
1575 WHERE nv.oid = v.relnamespace
1577 AND v.oid = dv.refobjid
1578 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
1579 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
1580 AND dv.deptype = 'i'
1581 AND dv.objid = dt.objid
1582 AND dv.refobjid <> dt.refobjid
1583 AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
1584 AND dt.refclassid = 'pg_catalog.pg_class'::regclass
1585 AND dt.refobjid = t.oid
1586 AND t.relnamespace = nt.oid
1587 AND t.relkind IN ('r', 'v')
1588 AND t.relowner = u.usesysid AND u.usename = current_user;
1590 GRANT SELECT ON view_table_usage TO PUBLIC;
1598 CREATE VIEW views AS
1599 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
1600 CAST(nc.nspname AS sql_identifier) AS table_schema,
1601 CAST(c.relname AS sql_identifier) AS table_name,
1604 CASE WHEN u.usename = current_user THEN pg_get_viewdef(c.oid)
1606 AS character_data) AS view_definition,
1608 CAST('NONE' AS character_data) AS check_option,
1609 CAST(null AS character_data) AS is_updatable, -- FIXME
1610 CAST(null AS character_data) AS is_insertable_into -- FIXME
1612 FROM pg_namespace nc, pg_class c, pg_user u
1614 WHERE c.relnamespace = nc.oid AND u.usesysid = c.relowner
1616 AND (u.usename = current_user
1617 OR has_table_privilege(c.oid, 'SELECT')
1618 OR has_table_privilege(c.oid, 'INSERT')
1619 OR has_table_privilege(c.oid, 'UPDATE')
1620 OR has_table_privilege(c.oid, 'DELETE')
1621 OR has_table_privilege(c.oid, 'RULE')
1622 OR has_table_privilege(c.oid, 'REFERENCES')
1623 OR has_table_privilege(c.oid, 'TRIGGER') );
1625 GRANT SELECT ON views TO PUBLIC;
1628 -- The following views have dependencies that force them to appear out of order.
1632 * DATA_TYPE_PRIVILEGES view
1635 CREATE VIEW data_type_privileges AS
1636 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
1637 CAST(x.objschema AS sql_identifier) AS object_schema,
1638 CAST(x.objname AS sql_identifier) AS object_name,
1639 CAST(x.objtype AS character_data) AS object_type,
1640 CAST(x.objdtdid AS sql_identifier) AS dtd_identifier
1644 SELECT table_schema, table_name, 'TABLE'::text, dtd_identifier FROM columns
1646 SELECT domain_schema, domain_name, 'DOMAIN'::text, dtd_identifier FROM domains
1648 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM parameters
1650 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM routines
1651 ) AS x (objschema, objname, objtype, objdtdid);
1653 GRANT SELECT ON data_type_privileges TO PUBLIC;
1658 * ELEMENT_TYPES view
1661 CREATE VIEW element_types AS
1662 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
1663 CAST(n.nspname AS sql_identifier) AS object_schema,
1664 CAST(x.objname AS sql_identifier) AS object_name,
1665 CAST(x.objtype AS character_data) AS object_type,
1666 CAST(x.objdtdid AS sql_identifier) AS array_type_identifier,
1668 CASE WHEN nbt.nspname = 'pg_catalog' THEN format_type(bt.oid, null)
1669 ELSE 'USER-DEFINED' END AS character_data) AS data_type,
1671 CAST(null AS cardinal_number) AS character_maximum_length,
1672 CAST(null AS cardinal_number) AS character_octet_length,
1673 CAST(null AS sql_identifier) AS character_set_catalog,
1674 CAST(null AS sql_identifier) AS character_set_schema,
1675 CAST(null AS sql_identifier) AS character_set_name,
1676 CAST(null AS sql_identifier) AS collation_catalog,
1677 CAST(null AS sql_identifier) AS collation_schema,
1678 CAST(null AS sql_identifier) AS collation_name,
1679 CAST(null AS cardinal_number) AS numeric_precision,
1680 CAST(null AS cardinal_number) AS numeric_precision_radix,
1681 CAST(null AS cardinal_number) AS numeric_scale,
1682 CAST(null AS cardinal_number) AS datetime_precision,
1683 CAST(null AS character_data) AS interval_type,
1684 CAST(null AS character_data) AS interval_precision,
1686 CAST(null AS character_data) AS domain_default, -- XXX maybe a bug in the standard
1688 CAST(current_database() AS sql_identifier) AS udt_catalog,
1689 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
1690 CAST(bt.typname AS sql_identifier) AS udt_name,
1692 CAST(null AS sql_identifier) AS scope_catalog,
1693 CAST(null AS sql_identifier) AS scope_schema,
1694 CAST(null AS sql_identifier) AS scope_name,
1696 CAST(null AS cardinal_number) AS maximum_cardinality,
1697 CAST('a' || x.objdtdid AS sql_identifier) AS dtd_identifier
1699 FROM pg_namespace n, pg_type at, pg_namespace nbt, pg_type bt,
1702 SELECT c.relnamespace, CAST(c.relname AS sql_identifier),
1703 'TABLE'::text, a.attnum, a.atttypid
1704 FROM pg_class c, pg_attribute a
1705 WHERE c.oid = a.attrelid
1706 AND c.relkind IN ('r', 'v')
1707 AND attnum > 0 AND NOT attisdropped
1712 SELECT t.typnamespace, CAST(t.typname AS sql_identifier),
1713 'DOMAIN'::text, 1, t.typbasetype
1715 WHERE t.typtype = 'd'
1720 SELECT pronamespace, CAST(proname || '_' || CAST(oid AS text) AS sql_identifier),
1721 'ROUTINE'::text, (ss.x).n, (ss.x).o
1722 FROM (SELECT p.pronamespace, p.proname, p.oid,
1723 _pg_expandoidvector(p.proargtypes) AS x
1724 FROM pg_proc p) AS ss
1729 SELECT p.pronamespace, CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier),
1730 'ROUTINE'::text, 0, p.prorettype
1733 ) AS x (objschema, objname, objtype, objdtdid, objtypeid)
1735 WHERE n.oid = x.objschema
1736 AND at.oid = x.objtypeid
1737 AND (at.typelem <> 0 AND at.typlen = -1)
1738 AND at.typelem = bt.oid
1739 AND nbt.oid = bt.typnamespace
1741 AND (n.nspname, x.objname, x.objtype, x.objdtdid) IN
1742 ( SELECT object_schema, object_name, object_type, dtd_identifier
1743 FROM data_type_privileges );
1745 GRANT SELECT ON element_types TO PUBLIC;