2 * SQL Information Schema
3 * as defined in ISO 9075-2:1999 chapter 20
5 * Copyright 2003, PostgreSQL Global Development Group
7 * $PostgreSQL: pgsql/src/backend/catalog/information_schema.sql,v 1.24 2004/06/22 22:30:32 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 /* This returns the integers from 1 to INDEX_MAX_KEYS/FUNC_MAX_ARGS */
34 CREATE FUNCTION _pg_keypositions() RETURNS SETOF integer
38 from generate_series(1,current_setting(''max_index_keys'')::int,1)
41 CREATE FUNCTION _pg_keyissubset(smallint[], smallint[]) RETURNS boolean
44 RETURNS NULL ON NULL INPUT
45 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))';
47 CREATE FUNCTION _pg_keysequal(smallint[], smallint[]) RETURNS boolean
50 RETURNS NULL ON NULL INPUT
51 AS 'select information_schema._pg_keyissubset($1, $2) and information_schema._pg_keyissubset($2, $1)';
53 CREATE FUNCTION _pg_truetypid(pg_attribute, pg_type) RETURNS oid
56 RETURNS NULL ON NULL INPUT
58 $$SELECT CASE WHEN $2.typtype = 'd' THEN $2.typbasetype ELSE $1.atttypid END$$;
60 CREATE FUNCTION _pg_truetypmod(pg_attribute, pg_type) RETURNS int4
63 RETURNS NULL ON NULL INPUT
65 $$SELECT CASE WHEN $2.typtype = 'd' THEN $2.typtypmod ELSE $1.atttypmod END$$;
67 -- these functions encapsulate knowledge about the encoding of typmod:
69 CREATE FUNCTION _pg_char_max_length(typid oid, typmod int4) RETURNS integer
72 RETURNS NULL ON NULL INPUT
75 CASE WHEN $2 = -1 /* default typmod */
77 WHEN $1 IN (1042, 1043) /* char, varchar */
79 WHEN $1 IN (1560, 1562) /* bit, varbit */
84 CREATE FUNCTION _pg_char_octet_length(typid oid, typmod int4) RETURNS integer
87 RETURNS NULL ON NULL INPUT
90 CASE WHEN $1 IN (25, 1042, 1043) /* text, char, varchar */
91 THEN CAST(2^30 AS integer)
95 CREATE FUNCTION _pg_numeric_precision(typid oid, typmod int4) RETURNS integer
98 RETURNS NULL ON NULL INPUT
102 WHEN 21 /*int2*/ THEN 16
103 WHEN 23 /*int4*/ THEN 32
104 WHEN 20 /*int8*/ THEN 64
105 WHEN 1700 /*numeric*/ THEN
108 ELSE (($2 - 4) >> 16) & 65535
110 WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/
111 WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/
115 CREATE FUNCTION _pg_numeric_precision_radix(typid oid, typmod int4) RETURNS integer
118 RETURNS NULL ON NULL INPUT
121 CASE WHEN $1 IN (21, 23, 20, 700, 701) THEN 2
122 WHEN $1 IN (1700) THEN 10
126 CREATE FUNCTION _pg_numeric_scale(typid oid, typmod int4) RETURNS integer
129 RETURNS NULL ON NULL INPUT
132 CASE WHEN $1 IN (21, 23, 20) THEN 0
133 WHEN $1 IN (1700) THEN
136 ELSE ($2 - 4) & 65535
141 CREATE FUNCTION _pg_datetime_precision(typid oid, typmod int4) RETURNS integer
144 RETURNS NULL ON NULL INPUT
147 CASE WHEN $2 = -1 /* default typmod */
149 WHEN $1 IN (1083, 1114, 1184, 1266) /* time, timestamp, same + tz */
151 WHEN $1 IN (1186) /* interval */
157 -- 20.3 INFORMATION_SCHEMA_CATALOG_NAME view appears later.
162 * CARDINAL_NUMBER domain
165 CREATE DOMAIN cardinal_number AS integer
166 CONSTRAINT cardinal_number_domain_check CHECK (value >= 0);
171 * CHARACTER_DATA domain
174 CREATE DOMAIN character_data AS character varying;
179 * SQL_IDENTIFIER domain
182 CREATE DOMAIN sql_identifier AS character varying;
187 * INFORMATION_SCHEMA_CATALOG_NAME view
190 CREATE VIEW information_schema_catalog_name AS
191 SELECT CAST(current_database() AS sql_identifier) AS catalog_name;
193 GRANT SELECT ON information_schema_catalog_name TO PUBLIC;
201 CREATE DOMAIN time_stamp AS timestamp(2)
202 DEFAULT current_timestamp(2);
207 * APPLICABLE_ROLES view
210 CREATE VIEW applicable_roles AS
211 SELECT CAST(current_user AS sql_identifier) AS grantee,
212 CAST(g.groname AS sql_identifier) AS role_name,
213 CAST('NO' AS character_data) AS is_grantable
215 FROM pg_group g, pg_user u
217 WHERE u.usesysid = ANY (g.grolist)
218 AND u.usename = current_user;
220 GRANT SELECT ON applicable_roles TO PUBLIC;
225 * CHECK_CONSTRAINTS view
228 CREATE VIEW check_constraints AS
229 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
230 CAST(rs.nspname AS sql_identifier) AS constraint_schema,
231 CAST(con.conname AS sql_identifier) AS constraint_name,
232 CAST(substring(pg_get_constraintdef(con.oid) from 7) AS character_data)
234 FROM pg_namespace rs,
236 LEFT OUTER JOIN pg_class c ON (c.oid = con.conrelid)
237 LEFT OUTER JOIN pg_type t ON (t.oid = con.contypid),
239 WHERE rs.oid = con.connamespace
240 AND u.usesysid = coalesce(c.relowner, t.typowner)
241 AND u.usename = current_user
242 AND con.contype = 'c';
244 GRANT SELECT ON check_constraints TO PUBLIC;
249 * COLUMN_DOMAIN_USAGE view
252 CREATE VIEW column_domain_usage AS
253 SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
254 CAST(nt.nspname AS sql_identifier) AS domain_schema,
255 CAST(t.typname AS sql_identifier) AS domain_name,
256 CAST(current_database() AS sql_identifier) AS table_catalog,
257 CAST(nc.nspname AS sql_identifier) AS table_schema,
258 CAST(c.relname AS sql_identifier) AS table_name,
259 CAST(a.attname AS sql_identifier) AS column_name
261 FROM pg_type t, pg_namespace nt, pg_class c, pg_namespace nc,
262 pg_attribute a, pg_user u
264 WHERE t.typnamespace = nt.oid
265 AND c.relnamespace = nc.oid
266 AND a.attrelid = c.oid
267 AND a.atttypid = t.oid
268 AND t.typowner = u.usesysid
270 AND c.relkind IN ('r', 'v')
272 AND NOT a.attisdropped
273 AND u.usename = current_user;
275 GRANT SELECT ON column_domain_usage TO PUBLIC;
283 CREATE VIEW column_privileges AS
284 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
285 CAST(grantee.name AS sql_identifier) AS grantee,
286 CAST(current_database() AS sql_identifier) AS table_catalog,
287 CAST(nc.nspname AS sql_identifier) AS table_schema,
288 CAST(c.relname AS sql_identifier) AS table_name,
289 CAST(a.attname AS sql_identifier) AS column_name,
290 CAST(pr.type AS character_data) AS privilege_type,
292 CASE WHEN aclcontains(c.relacl,
293 makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr.type, true))
294 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
301 SELECT usesysid, 0, usename FROM pg_user
303 SELECT 0, grosysid, groname FROM pg_group
305 SELECT 0, 0, 'PUBLIC'
306 ) AS grantee (usesysid, grosysid, name),
307 (SELECT 'SELECT' UNION ALL
308 SELECT 'INSERT' UNION ALL
309 SELECT 'UPDATE' UNION ALL
310 SELECT 'REFERENCES') AS pr (type)
312 WHERE a.attrelid = c.oid
313 AND c.relnamespace = nc.oid
315 AND NOT a.attisdropped
316 AND c.relkind IN ('r', 'v')
317 AND aclcontains(c.relacl,
318 makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr.type, false))
319 AND (u_grantor.usename = current_user
320 OR grantee.name = current_user
321 OR grantee.name = 'PUBLIC');
323 GRANT SELECT ON column_privileges TO PUBLIC;
328 * COLUMN_UDT_USAGE view
331 CREATE VIEW column_udt_usage AS
332 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
333 CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,
334 CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,
335 CAST(current_database() AS sql_identifier) AS table_catalog,
336 CAST(nc.nspname AS sql_identifier) AS table_schema,
337 CAST(c.relname AS sql_identifier) AS table_name,
338 CAST(a.attname AS sql_identifier) AS column_name
340 FROM pg_attribute a, pg_class c, pg_namespace nc, pg_user u,
341 (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
342 LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
343 ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
345 WHERE a.attrelid = c.oid
346 AND a.atttypid = t.oid
347 AND u.usesysid = coalesce(bt.typowner, t.typowner)
348 AND nc.oid = c.relnamespace
349 AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
350 AND u.usename = current_user;
352 GRANT SELECT ON column_udt_usage TO PUBLIC;
360 CREATE VIEW columns AS
361 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
362 CAST(nc.nspname AS sql_identifier) AS table_schema,
363 CAST(c.relname AS sql_identifier) AS table_name,
364 CAST(a.attname AS sql_identifier) AS column_name,
365 CAST(a.attnum AS cardinal_number) AS ordinal_position,
367 CASE WHEN u.usename = current_user THEN ad.adsrc ELSE null END
370 CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
375 CASE WHEN t.typtype = 'd' THEN
376 CASE WHEN bt.typelem <> 0 AND bt.typlen = -1 THEN 'ARRAY'
377 WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
378 ELSE 'USER-DEFINED' END
380 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
381 WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, null)
382 ELSE 'USER-DEFINED' END
388 _pg_char_max_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
390 AS character_maximum_length,
393 _pg_char_octet_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
395 AS character_octet_length,
398 _pg_numeric_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
400 AS numeric_precision,
403 _pg_numeric_precision_radix(_pg_truetypid(a, t), _pg_truetypmod(a, t))
405 AS numeric_precision_radix,
408 _pg_numeric_scale(_pg_truetypid(a, t), _pg_truetypmod(a, t))
413 _pg_datetime_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
415 AS datetime_precision,
417 CAST(null AS character_data) AS interval_type, -- XXX
418 CAST(null AS character_data) AS interval_precision, -- XXX
420 CAST(null AS sql_identifier) AS character_set_catalog,
421 CAST(null AS sql_identifier) AS character_set_schema,
422 CAST(null AS sql_identifier) AS character_set_name,
424 CAST(null AS sql_identifier) AS collation_catalog,
425 CAST(null AS sql_identifier) AS collation_schema,
426 CAST(null AS sql_identifier) AS collation_name,
428 CAST(CASE WHEN t.typtype = 'd' THEN current_database() ELSE null END
429 AS sql_identifier) AS domain_catalog,
430 CAST(CASE WHEN t.typtype = 'd' THEN nt.nspname ELSE null END
431 AS sql_identifier) AS domain_schema,
432 CAST(CASE WHEN t.typtype = 'd' THEN t.typname ELSE null END
433 AS sql_identifier) AS domain_name,
435 CAST(current_database() AS sql_identifier) AS udt_catalog,
436 CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,
437 CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,
439 CAST(null AS sql_identifier) AS scope_catalog,
440 CAST(null AS sql_identifier) AS scope_schema,
441 CAST(null AS sql_identifier) AS scope_name,
443 CAST(null AS cardinal_number) AS maximum_cardinality,
444 CAST(a.attnum AS sql_identifier) AS dtd_identifier,
445 CAST('NO' AS character_data) AS is_self_referencing
447 FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum),
448 pg_class c, pg_namespace nc, pg_user u,
449 (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
450 LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
451 ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
453 WHERE a.attrelid = c.oid
454 AND a.atttypid = t.oid
455 AND u.usesysid = c.relowner
456 AND nc.oid = c.relnamespace
458 AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
460 AND (u.usename = current_user
461 OR has_table_privilege(c.oid, 'SELECT')
462 OR has_table_privilege(c.oid, 'INSERT')
463 OR has_table_privilege(c.oid, 'UPDATE')
464 OR has_table_privilege(c.oid, 'REFERENCES') );
466 GRANT SELECT ON columns TO PUBLIC;
471 * CONSTRAINT_COLUMN_USAGE view
474 CREATE VIEW constraint_column_usage AS
475 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
476 CAST(tblschema AS sql_identifier) AS table_schema,
477 CAST(tblname AS sql_identifier) AS table_name,
478 CAST(colname AS sql_identifier) AS column_name,
479 CAST(current_database() AS sql_identifier) AS constraint_catalog,
480 CAST(cstrschema AS sql_identifier) AS constraint_schema,
481 CAST(cstrname AS sql_identifier) AS constraint_name
484 /* check constraints */
485 SELECT DISTINCT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
486 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_depend d, pg_namespace nc, pg_constraint c
487 WHERE nr.oid = r.relnamespace
488 AND r.oid = a.attrelid
489 AND d.refclassid = 'pg_catalog.pg_class'::regclass
490 AND d.refobjid = r.oid
491 AND d.refobjsubid = a.attnum
492 AND d.classid = 'pg_catalog.pg_constraint'::regclass
494 AND c.connamespace = nc.oid
497 AND NOT a.attisdropped
501 /* unique/primary key/foreign key constraints */
502 SELECT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
503 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc,
504 pg_constraint c, _pg_keypositions() AS pos(n)
505 WHERE nr.oid = r.relnamespace
506 AND r.oid = a.attrelid
507 AND nc.oid = c.connamespace
508 AND (CASE WHEN c.contype = 'f' THEN r.oid = c.confrelid AND c.confkey[pos.n] = a.attnum
509 ELSE r.oid = c.conrelid AND c.conkey[pos.n] = a.attnum END)
510 AND NOT a.attisdropped
511 AND c.contype IN ('p', 'u', 'f')
514 ) AS x (tblschema, tblname, tblowner, colname, cstrschema, cstrname),
517 WHERE x.tblowner = u.usesysid AND u.usename = current_user;
519 GRANT SELECT ON constraint_column_usage TO PUBLIC;
524 * CONSTRAINT_TABLE_USAGE view
527 CREATE VIEW constraint_table_usage AS
528 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
529 CAST(nr.nspname AS sql_identifier) AS table_schema,
530 CAST(r.relname AS sql_identifier) AS table_name,
531 CAST(current_database() AS sql_identifier) AS constraint_catalog,
532 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
533 CAST(c.conname AS sql_identifier) AS constraint_name
535 FROM pg_constraint c, pg_namespace nc,
536 pg_class r, pg_namespace nr,
539 WHERE c.connamespace = nc.oid AND r.relnamespace = nr.oid
540 AND ( (c.contype = 'f' AND c.confrelid = r.oid)
541 OR (c.contype IN ('p', 'u') AND c.conrelid = r.oid) )
543 AND r.relowner = u.usesysid AND u.usename = current_user;
545 GRANT SELECT ON constraint_table_usage TO PUBLIC;
548 -- 20.21 DATA_TYPE_PRIVILEGES view appears later.
553 * DOMAIN_CONSTRAINTS view
556 CREATE VIEW domain_constraints AS
557 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
558 CAST(rs.nspname AS sql_identifier) AS constraint_schema,
559 CAST(con.conname AS sql_identifier) AS constraint_name,
560 CAST(current_database() AS sql_identifier) AS domain_catalog,
561 CAST(n.nspname AS sql_identifier) AS domain_schema,
562 CAST(t.typname AS sql_identifier) AS domain_name,
563 CAST(CASE WHEN condeferrable THEN 'YES' ELSE 'NO' END
564 AS character_data) AS is_deferrable,
565 CAST(CASE WHEN condeferred THEN 'YES' ELSE 'NO' END
566 AS character_data) AS initially_deferred
567 FROM pg_namespace rs, pg_namespace n, pg_constraint con, pg_type t, pg_user u
568 WHERE rs.oid = con.connamespace
569 AND n.oid = t.typnamespace
570 AND u.usesysid = t.typowner
571 AND u.usename = current_user
572 AND t.oid = con.contypid;
574 GRANT SELECT ON domain_constraints TO PUBLIC;
579 * DOMAIN_UDT_USAGE view
582 CREATE VIEW domain_udt_usage AS
583 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
584 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
585 CAST(bt.typname AS sql_identifier) AS udt_name,
586 CAST(current_database() AS sql_identifier) AS domain_catalog,
587 CAST(nt.nspname AS sql_identifier) AS domain_schema,
588 CAST(t.typname AS sql_identifier) AS domain_name
590 FROM pg_type t, pg_namespace nt,
591 pg_type bt, pg_namespace nbt,
594 WHERE t.typnamespace = nt.oid
595 AND t.typbasetype = bt.oid
596 AND bt.typnamespace = nbt.oid
598 AND bt.typowner = u.usesysid
599 AND u.usename = current_user;
601 GRANT SELECT ON domain_udt_usage TO PUBLIC;
609 CREATE VIEW domains AS
610 SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
611 CAST(nt.nspname AS sql_identifier) AS domain_schema,
612 CAST(t.typname AS sql_identifier) AS domain_name,
615 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
616 WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
617 ELSE 'USER-DEFINED' END
622 _pg_char_max_length(t.typbasetype, t.typtypmod)
624 AS character_maximum_length,
627 _pg_char_octet_length(t.typbasetype, t.typtypmod)
629 AS character_octet_length,
631 CAST(null AS sql_identifier) AS character_set_catalog,
632 CAST(null AS sql_identifier) AS character_set_schema,
633 CAST(null AS sql_identifier) AS character_set_name,
635 CAST(null AS sql_identifier) AS collation_catalog,
636 CAST(null AS sql_identifier) AS collation_schema,
637 CAST(null AS sql_identifier) AS collation_name,
640 _pg_numeric_precision(t.typbasetype, t.typtypmod)
642 AS numeric_precision,
645 _pg_numeric_precision_radix(t.typbasetype, t.typtypmod)
647 AS numeric_precision_radix,
650 _pg_numeric_scale(t.typbasetype, t.typtypmod)
655 _pg_datetime_precision(t.typbasetype, t.typtypmod)
657 AS datetime_precision,
659 CAST(null AS character_data) AS interval_type, -- XXX
660 CAST(null AS character_data) AS interval_precision, -- XXX
662 CAST(t.typdefault AS character_data) AS domain_default,
664 CAST(current_database() AS sql_identifier) AS udt_catalog,
665 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
666 CAST(bt.typname AS sql_identifier) AS udt_name,
668 CAST(null AS sql_identifier) AS scope_catalog,
669 CAST(null AS sql_identifier) AS scope_schema,
670 CAST(null AS sql_identifier) AS scope_name,
672 CAST(null AS cardinal_number) AS maximum_cardinality,
673 CAST(1 AS sql_identifier) AS dtd_identifier
675 FROM pg_type t, pg_namespace nt,
676 pg_type bt, pg_namespace nbt
678 WHERE t.typnamespace = nt.oid
679 AND t.typbasetype = bt.oid
680 AND bt.typnamespace = nbt.oid
683 GRANT SELECT ON domains TO PUBLIC;
686 -- 20.27 ELEMENT_TYPES view appears later.
694 CREATE VIEW enabled_roles AS
695 SELECT CAST(g.groname AS sql_identifier) AS role_name
696 FROM pg_group g, pg_user u
697 WHERE u.usesysid = ANY (g.grolist)
698 AND u.usename = current_user;
700 GRANT SELECT ON enabled_roles TO PUBLIC;
705 * KEY_COLUMN_USAGE view
708 CREATE VIEW key_column_usage AS
709 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
710 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
711 CAST(c.conname AS sql_identifier) AS constraint_name,
712 CAST(current_database() AS sql_identifier) AS table_catalog,
713 CAST(nr.nspname AS sql_identifier) AS table_schema,
714 CAST(r.relname AS sql_identifier) AS table_name,
715 CAST(a.attname AS sql_identifier) AS column_name,
716 CAST(pos.n AS cardinal_number) AS ordinal_position
718 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc,
719 pg_constraint c, pg_user u, _pg_keypositions() AS pos(n)
720 WHERE nr.oid = r.relnamespace
721 AND r.oid = a.attrelid
722 AND r.oid = c.conrelid
723 AND nc.oid = c.connamespace
724 AND c.conkey[pos.n] = a.attnum
725 AND NOT a.attisdropped
726 AND c.contype IN ('p', 'u', 'f')
728 AND r.relowner = u.usesysid
729 AND u.usename = current_user;
731 GRANT SELECT ON key_column_usage TO PUBLIC;
739 CREATE VIEW parameters AS
740 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
741 CAST(n.nspname AS sql_identifier) AS specific_schema,
742 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
743 CAST(pos.n AS cardinal_number) AS ordinal_position,
744 CAST('IN' AS character_data) AS parameter_mode,
745 CAST('NO' AS character_data) AS is_result,
746 CAST('NO' AS character_data) AS as_locator,
747 CAST(NULLIF(p.proargnames[pos.n], '') AS sql_identifier) AS parameter_name,
749 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
750 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
751 ELSE 'USER-DEFINED' END AS character_data)
753 CAST(null AS cardinal_number) AS character_maximum_length,
754 CAST(null AS cardinal_number) AS character_octet_length,
755 CAST(null AS sql_identifier) AS character_set_catalog,
756 CAST(null AS sql_identifier) AS character_set_schema,
757 CAST(null AS sql_identifier) AS character_set_name,
758 CAST(null AS sql_identifier) AS collation_catalog,
759 CAST(null AS sql_identifier) AS collation_schema,
760 CAST(null AS sql_identifier) AS collation_name,
761 CAST(null AS cardinal_number) AS numeric_precision,
762 CAST(null AS cardinal_number) AS numeric_precision_radix,
763 CAST(null AS cardinal_number) AS numeric_scale,
764 CAST(null AS cardinal_number) AS datetime_precision,
765 CAST(null AS character_data) AS interval_type,
766 CAST(null AS character_data) AS interval_precision,
767 CAST(current_database() AS sql_identifier) AS udt_catalog,
768 CAST(nt.nspname AS sql_identifier) AS udt_schema,
769 CAST(t.typname AS sql_identifier) AS udt_name,
770 CAST(null AS sql_identifier) AS scope_catalog,
771 CAST(null AS sql_identifier) AS scope_schema,
772 CAST(null AS sql_identifier) AS scope_name,
773 CAST(null AS cardinal_number) AS maximum_cardinality,
774 CAST(pos.n AS sql_identifier) AS dtd_identifier
776 FROM pg_namespace n, pg_proc p, pg_type t, pg_namespace nt, pg_user u,
777 _pg_keypositions() AS pos(n)
779 WHERE n.oid = p.pronamespace AND p.pronargs >= pos.n
780 AND p.proargtypes[pos.n-1] = t.oid AND t.typnamespace = nt.oid
781 AND p.proowner = u.usesysid
782 AND (u.usename = current_user OR has_function_privilege(p.oid, 'EXECUTE'));
784 GRANT SELECT ON parameters TO PUBLIC;
789 * REFERENTIAL_CONSTRAINTS view
792 CREATE VIEW referential_constraints AS
793 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
794 CAST(ncon.nspname AS sql_identifier) AS constraint_schema,
795 CAST(con.conname AS sql_identifier) AS constraint_name,
797 CASE WHEN npkc.nspname IS NULL THEN NULL
798 ELSE current_database() END
799 AS sql_identifier) AS unique_constraint_catalog,
800 CAST(npkc.nspname AS sql_identifier) AS unique_constraint_schema,
801 CAST(pkc.conname AS sql_identifier) AS unique_constraint_name,
804 CASE con.confmatchtype WHEN 'f' THEN 'FULL'
805 WHEN 'p' THEN 'PARTIAL'
806 WHEN 'u' THEN 'NONE' END
807 AS character_data) AS match_option,
810 CASE con.confupdtype WHEN 'c' THEN 'CASCADE'
811 WHEN 'n' THEN 'SET NULL'
812 WHEN 'd' THEN 'SET DEFAULT'
813 WHEN 'r' THEN 'RESTRICT'
814 WHEN 'a' THEN 'NO ACTION' END
815 AS character_data) AS update_rule,
818 CASE con.confdeltype WHEN 'c' THEN 'CASCADE'
819 WHEN 'n' THEN 'SET NULL'
820 WHEN 'd' THEN 'SET DEFAULT'
821 WHEN 'r' THEN 'RESTRICT'
822 WHEN 'a' THEN 'NO ACTION' END
823 AS character_data) AS delete_rule
825 FROM (pg_namespace ncon INNER JOIN pg_constraint con ON ncon.oid = con.connamespace
826 INNER JOIN pg_class c ON con.conrelid = c.oid
827 INNER JOIN pg_user u ON c.relowner = u.usesysid)
829 (pg_constraint pkc INNER JOIN pg_namespace npkc ON pkc.connamespace = npkc.oid)
830 ON con.confrelid = pkc.conrelid AND _pg_keysequal(con.confkey, pkc.conkey)
832 WHERE c.relkind = 'r'
833 AND con.contype = 'f'
834 AND (pkc.contype IN ('p', 'u') OR pkc.contype IS NULL)
835 AND u.usename = current_user;
837 GRANT SELECT ON referential_constraints TO PUBLIC;
842 * ROLE_COLUMN_GRANTS view
845 CREATE VIEW role_column_grants AS
846 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
847 CAST(g_grantee.groname AS sql_identifier) AS grantee,
848 CAST(current_database() AS sql_identifier) AS table_catalog,
849 CAST(nc.nspname AS sql_identifier) AS table_schema,
850 CAST(c.relname AS sql_identifier) AS table_name,
851 CAST(a.attname AS sql_identifier) AS column_name,
852 CAST(pr.type AS character_data) AS privilege_type,
854 CASE WHEN aclcontains(c.relacl,
855 makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, pr.type, true))
856 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
863 (SELECT 'SELECT' UNION ALL
864 SELECT 'INSERT' UNION ALL
865 SELECT 'UPDATE' UNION ALL
866 SELECT 'REFERENCES') AS pr (type)
868 WHERE a.attrelid = c.oid
869 AND c.relnamespace = nc.oid
871 AND NOT a.attisdropped
872 AND c.relkind IN ('r', 'v')
873 AND aclcontains(c.relacl,
874 makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, pr.type, false))
875 AND g_grantee.groname IN (SELECT role_name FROM enabled_roles);
877 GRANT SELECT ON role_column_grants TO PUBLIC;
882 * ROLE_ROUTINE_GRANTS view
885 CREATE VIEW role_routine_grants AS
886 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
887 CAST(g_grantee.groname AS sql_identifier) AS grantee,
888 CAST(current_database() AS sql_identifier) AS specific_catalog,
889 CAST(n.nspname AS sql_identifier) AS specific_schema,
890 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
891 CAST(current_database() AS sql_identifier) AS routine_catalog,
892 CAST(n.nspname AS sql_identifier) AS routine_schema,
893 CAST(p.proname AS sql_identifier) AS routine_name,
894 CAST('EXECUTE' AS character_data) AS privilege_type,
896 CASE WHEN aclcontains(p.proacl,
897 makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, 'EXECUTE', true))
898 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
905 WHERE p.pronamespace = n.oid
906 AND aclcontains(p.proacl,
907 makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, 'EXECUTE', false))
908 AND g_grantee.groname IN (SELECT role_name FROM enabled_roles);
910 GRANT SELECT ON role_routine_grants TO PUBLIC;
915 * ROLE_TABLE_GRANTS view
918 CREATE VIEW role_table_grants AS
919 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
920 CAST(g_grantee.groname AS sql_identifier) AS grantee,
921 CAST(current_database() AS sql_identifier) AS table_catalog,
922 CAST(nc.nspname AS sql_identifier) AS table_schema,
923 CAST(c.relname AS sql_identifier) AS table_name,
924 CAST(pr.type AS character_data) AS privilege_type,
926 CASE WHEN aclcontains(c.relacl,
927 makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, pr.type, true))
928 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable,
929 CAST('NO' AS character_data) AS with_hierarchy
935 (SELECT 'SELECT' UNION ALL
936 SELECT 'DELETE' UNION ALL
937 SELECT 'INSERT' UNION ALL
938 SELECT 'UPDATE' UNION ALL
939 SELECT 'REFERENCES' UNION ALL
940 SELECT 'RULE' UNION ALL
941 SELECT 'TRIGGER') AS pr (type)
943 WHERE c.relnamespace = nc.oid
944 AND c.relkind IN ('r', 'v')
945 AND aclcontains(c.relacl,
946 makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, pr.type, false))
947 AND g_grantee.groname IN (SELECT role_name FROM enabled_roles);
949 GRANT SELECT ON role_table_grants TO PUBLIC;
954 * ROLE_USAGE_GRANTS view
957 -- See USAGE_PRIVILEGES.
959 CREATE VIEW role_usage_grants AS
960 SELECT CAST(null AS sql_identifier) AS grantor,
961 CAST(null AS sql_identifier) AS grantee,
962 CAST(current_database() AS sql_identifier) AS object_catalog,
963 CAST(null AS sql_identifier) AS object_schema,
964 CAST(null AS sql_identifier) AS object_name,
965 CAST(null AS character_data) AS object_type,
966 CAST('USAGE' AS character_data) AS privilege_type,
967 CAST(null AS character_data) AS is_grantable
971 GRANT SELECT ON role_usage_grants TO PUBLIC;
976 * ROUTINE_PRIVILEGES view
979 CREATE VIEW routine_privileges AS
980 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
981 CAST(grantee.name AS sql_identifier) AS grantee,
982 CAST(current_database() AS sql_identifier) AS specific_catalog,
983 CAST(n.nspname AS sql_identifier) AS specific_schema,
984 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
985 CAST(current_database() AS sql_identifier) AS routine_catalog,
986 CAST(n.nspname AS sql_identifier) AS routine_schema,
987 CAST(p.proname AS sql_identifier) AS routine_name,
988 CAST('EXECUTE' AS character_data) AS privilege_type,
990 CASE WHEN aclcontains(p.proacl,
991 makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, 'EXECUTE', true))
992 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
998 SELECT usesysid, 0, usename FROM pg_user
1000 SELECT 0, grosysid, groname FROM pg_group
1002 SELECT 0, 0, 'PUBLIC'
1003 ) AS grantee (usesysid, grosysid, name)
1005 WHERE p.pronamespace = n.oid
1006 AND aclcontains(p.proacl,
1007 makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, 'EXECUTE', false))
1008 AND (u_grantor.usename = current_user
1009 OR grantee.name = current_user
1010 OR grantee.name = 'PUBLIC');
1012 GRANT SELECT ON routine_privileges TO PUBLIC;
1020 CREATE VIEW routines AS
1021 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
1022 CAST(n.nspname AS sql_identifier) AS specific_schema,
1023 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1024 CAST(current_database() AS sql_identifier) AS routine_catalog,
1025 CAST(n.nspname AS sql_identifier) AS routine_schema,
1026 CAST(p.proname AS sql_identifier) AS routine_name,
1027 CAST('FUNCTION' AS character_data) AS routine_type,
1028 CAST(null AS sql_identifier) AS module_catalog,
1029 CAST(null AS sql_identifier) AS module_schema,
1030 CAST(null AS sql_identifier) AS module_name,
1031 CAST(null AS sql_identifier) AS udt_catalog,
1032 CAST(null AS sql_identifier) AS udt_schema,
1033 CAST(null AS sql_identifier) AS udt_name,
1036 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
1037 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
1038 ELSE 'USER-DEFINED' END AS character_data)
1040 CAST(null AS cardinal_number) AS character_maximum_length,
1041 CAST(null AS cardinal_number) AS character_octet_length,
1042 CAST(null AS sql_identifier) AS character_set_catalog,
1043 CAST(null AS sql_identifier) AS character_set_schema,
1044 CAST(null AS sql_identifier) AS character_set_name,
1045 CAST(null AS sql_identifier) AS collation_catalog,
1046 CAST(null AS sql_identifier) AS collation_schema,
1047 CAST(null AS sql_identifier) AS collation_name,
1048 CAST(null AS cardinal_number) AS numeric_precision,
1049 CAST(null AS cardinal_number) AS numeric_precision_radix,
1050 CAST(null AS cardinal_number) AS numeric_scale,
1051 CAST(null AS cardinal_number) AS datetime_precision,
1052 CAST(null AS character_data) AS interval_type,
1053 CAST(null AS character_data) AS interval_precision,
1054 CAST(current_database() AS sql_identifier) AS type_udt_catalog,
1055 CAST(nt.nspname AS sql_identifier) AS type_udt_schema,
1056 CAST(t.typname AS sql_identifier) AS type_udt_name,
1057 CAST(null AS sql_identifier) AS scope_catalog,
1058 CAST(null AS sql_identifier) AS scope_schema,
1059 CAST(null AS sql_identifier) AS scope_name,
1060 CAST(null AS cardinal_number) AS maximum_cardinality,
1061 CAST(0 AS sql_identifier) AS dtd_identifier,
1063 CAST(CASE WHEN l.lanname = 'sql' THEN 'SQL' ELSE 'EXTERNAL' END AS character_data)
1066 CASE WHEN u.usename = current_user THEN p.prosrc ELSE null END
1067 AS character_data) AS routine_definition,
1069 CASE WHEN l.lanname = 'c' THEN p.prosrc ELSE null END
1070 AS character_data) AS external_name,
1071 CAST(upper(l.lanname) AS character_data) AS external_language,
1073 CAST('GENERAL' AS character_data) AS parameter_style,
1074 CAST(CASE WHEN p.provolatile = 'i' THEN 'YES' ELSE 'NO' END AS character_data) AS is_deterministic,
1075 CAST('MODIFIES' AS character_data) AS sql_data_access,
1076 CAST(CASE WHEN p.proisstrict THEN 'YES' ELSE 'NO' END AS character_data) AS is_null_call,
1077 CAST(null AS character_data) AS sql_path,
1078 CAST('YES' AS character_data) AS schema_level_routine,
1079 CAST(0 AS cardinal_number) AS max_dynamic_result_sets,
1080 CAST(null AS character_data) AS is_user_defined_cast,
1081 CAST(null AS character_data) AS is_implicitly_invocable,
1082 CAST(CASE WHEN p.prosecdef THEN 'DEFINER' ELSE 'INVOKER' END AS character_data) AS security_type,
1083 CAST(null AS sql_identifier) AS to_sql_specific_catalog,
1084 CAST(null AS sql_identifier) AS to_sql_specific_schema,
1085 CAST(null AS sql_identifier) AS to_sql_specific_name,
1086 CAST('NO' AS character_data) AS as_locator
1088 FROM pg_namespace n, pg_proc p, pg_language l, pg_user u,
1089 pg_type t, pg_namespace nt
1091 WHERE n.oid = p.pronamespace AND p.prolang = l.oid AND p.proowner = u.usesysid
1092 AND p.prorettype = t.oid AND t.typnamespace = nt.oid
1093 AND (u.usename = current_user OR has_function_privilege(p.oid, 'EXECUTE'));
1095 GRANT SELECT ON routines TO PUBLIC;
1103 CREATE VIEW schemata AS
1104 SELECT CAST(current_database() AS sql_identifier) AS catalog_name,
1105 CAST(n.nspname AS sql_identifier) AS schema_name,
1106 CAST(u.usename AS sql_identifier) AS schema_owner,
1107 CAST(null AS sql_identifier) AS default_character_set_catalog,
1108 CAST(null AS sql_identifier) AS default_character_set_schema,
1109 CAST(null AS sql_identifier) AS default_character_set_name,
1110 CAST(null AS character_data) AS sql_path
1111 FROM pg_namespace n, pg_user u
1112 WHERE n.nspowner = u.usesysid AND u.usename = current_user;
1114 GRANT SELECT ON schemata TO PUBLIC;
1119 * SQL_FEATURES table
1122 CREATE TABLE sql_features (
1123 feature_id character_data,
1124 feature_name character_data,
1125 sub_feature_id character_data,
1126 sub_feature_name character_data,
1127 is_supported character_data,
1128 is_verified_by character_data,
1129 comments character_data
1132 -- Will be filled with external data by initdb.
1134 GRANT SELECT ON sql_features TO PUBLIC;
1139 * SQL_IMPLEMENTATION_INFO table
1142 -- Note: Implementation information items are defined in ISO 9075-3:1999,
1145 CREATE TABLE sql_implementation_info (
1146 implementation_info_id character_data,
1147 implementation_info_name character_data,
1148 integer_value cardinal_number,
1149 character_value character_data,
1150 comments character_data
1153 INSERT INTO sql_implementation_info VALUES ('10003', 'CATALOG NAME', NULL, 'Y', NULL);
1154 INSERT INTO sql_implementation_info VALUES ('10004', 'COLLATING SEQUENCE', NULL, '', 'not supported');
1155 INSERT INTO sql_implementation_info VALUES ('23', 'CURSOR COMMIT BEHAVIOR', 1, NULL, 'close cursors and retain prepared statements');
1156 INSERT INTO sql_implementation_info VALUES ('2', 'DATA SOURCE NAME', NULL, '', NULL);
1157 INSERT INTO sql_implementation_info VALUES ('17', 'DBMS NAME', NULL, (select trim(trailing ' ' from substring(version() from '^[^0-9]*'))), NULL);
1158 INSERT INTO sql_implementation_info VALUES ('18', 'DBMS VERSION', NULL, '???', NULL); -- filled by initdb
1159 INSERT INTO sql_implementation_info VALUES ('26', 'DEFAULT TRANSACTION ISOLATION', 2, NULL, 'READ COMMITTED; user-settable');
1160 INSERT INTO sql_implementation_info VALUES ('28', 'IDENTIFIER CASE', 3, NULL, 'stored in mixed case - case sensitive');
1161 INSERT INTO sql_implementation_info VALUES ('85', 'NULL COLLATION', 0, NULL, 'nulls higher than non-nulls');
1162 INSERT INTO sql_implementation_info VALUES ('13', 'SERVER NAME', NULL, '', NULL);
1163 INSERT INTO sql_implementation_info VALUES ('94', 'SPECIAL CHARACTERS', NULL, '', 'all non-ASCII characters allowed');
1164 INSERT INTO sql_implementation_info VALUES ('46', 'TRANSACTION CAPABLE', 2, NULL, 'both DML and DDL');
1166 GRANT SELECT ON sql_implementation_info TO PUBLIC;
1171 * SQL_LANGUAGES table
1174 CREATE TABLE sql_languages (
1175 sql_language_source character_data,
1176 sql_language_year character_data,
1177 sql_language_conformance character_data,
1178 sql_language_integrity character_data,
1179 sql_language_implementation character_data,
1180 sql_language_binding_style character_data,
1181 sql_language_programming_language character_data
1184 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'DIRECT', NULL);
1185 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
1187 GRANT SELECT ON sql_languages TO PUBLIC;
1192 * SQL_PACKAGES table
1195 CREATE TABLE sql_packages (
1196 feature_id character_data,
1197 feature_name character_data,
1198 is_supported character_data,
1199 is_verified_by character_data,
1200 comments character_data
1203 INSERT INTO sql_packages VALUES ('PKG000', 'Core', 'NO', NULL, '');
1204 INSERT INTO sql_packages VALUES ('PKG001', 'Enhanced datetime facilities', 'YES', NULL, '');
1205 INSERT INTO sql_packages VALUES ('PKG002', 'Enhanced integrity management', 'NO', NULL, '');
1206 INSERT INTO sql_packages VALUES ('PKG003', 'OLAP facilities', 'NO', NULL, '');
1207 INSERT INTO sql_packages VALUES ('PKG004', 'PSM', 'NO', NULL, 'PL/pgSQL is similar.');
1208 INSERT INTO sql_packages VALUES ('PKG005', 'CLI', 'NO', NULL, 'ODBC is similar.');
1209 INSERT INTO sql_packages VALUES ('PKG006', 'Basic object support', 'NO', NULL, '');
1210 INSERT INTO sql_packages VALUES ('PKG007', 'Enhanced object support', 'NO', NULL, '');
1211 INSERT INTO sql_packages VALUES ('PKG008', 'Active database', 'NO', NULL, '');
1212 INSERT INTO sql_packages VALUES ('PKG009', 'SQL/MM support', 'NO', NULL, '');
1214 GRANT SELECT ON sql_packages TO PUBLIC;
1222 -- Note: Sizing items are defined in ISO 9075-3:1999, clause 7.2.
1224 CREATE TABLE sql_sizing (
1225 sizing_id cardinal_number,
1226 sizing_name character_data,
1227 supported_value cardinal_number,
1228 comments character_data
1231 INSERT INTO sql_sizing VALUES (34, 'MAXIMUM CATALOG NAME LENGTH', 63, NULL);
1232 INSERT INTO sql_sizing VALUES (30, 'MAXIMUM COLUMN NAME LENGTH', 63, NULL);
1233 INSERT INTO sql_sizing VALUES (97, 'MAXIMUM COLUMNS IN GROUP BY', 0, NULL);
1234 INSERT INTO sql_sizing VALUES (99, 'MAXIMUM COLUMNS IN ORDER BY', 0, NULL);
1235 INSERT INTO sql_sizing VALUES (100, 'MAXIMUM COLUMNS IN SELECT', 1664, NULL); -- match MaxTupleAttributeNumber
1236 INSERT INTO sql_sizing VALUES (101, 'MAXIMUM COLUMNS IN TABLE', 1600, NULL); -- match MaxHeapAttributeNumber
1237 INSERT INTO sql_sizing VALUES (1, 'MAXIMUM CONCURRENT ACTIVITIES', 0, NULL);
1238 INSERT INTO sql_sizing VALUES (31, 'MAXIMUM CURSOR NAME LENGTH', 63, NULL);
1239 INSERT INTO sql_sizing VALUES (0, 'MAXIMUM DRIVER CONNECTIONS', NULL, NULL);
1240 INSERT INTO sql_sizing VALUES (10005, 'MAXIMUM IDENTIFIER LENGTH', 63, NULL);
1241 INSERT INTO sql_sizing VALUES (32, 'MAXIMUM SCHEMA NAME LENGTH', 63, NULL);
1242 INSERT INTO sql_sizing VALUES (20000, 'MAXIMUM STATEMENT OCTETS', 0, NULL);
1243 INSERT INTO sql_sizing VALUES (20001, 'MAXIMUM STATEMENT OCTETS DATA', 0, NULL);
1244 INSERT INTO sql_sizing VALUES (20002, 'MAXIMUM STATEMENT OCTETS SCHEMA', 0, NULL);
1245 INSERT INTO sql_sizing VALUES (35, 'MAXIMUM TABLE NAME LENGTH', 63, NULL);
1246 INSERT INTO sql_sizing VALUES (106, 'MAXIMUM TABLES IN SELECT', 0, NULL);
1247 INSERT INTO sql_sizing VALUES (107, 'MAXIMUM USER NAME LENGTH', 63, NULL);
1248 INSERT INTO sql_sizing VALUES (25000, 'MAXIMUM CURRENT DEFAULT TRANSFORM GROUP LENGTH', NULL, NULL);
1249 INSERT INTO sql_sizing VALUES (25001, 'MAXIMUM CURRENT TRANSFORM GROUP LENGTH', NULL, NULL);
1250 INSERT INTO sql_sizing VALUES (25002, 'MAXIMUM CURRENT PATH LENGTH', 0, NULL);
1251 INSERT INTO sql_sizing VALUES (25003, 'MAXIMUM CURRENT ROLE LENGTH', NULL, NULL);
1252 INSERT INTO sql_sizing VALUES (25004, 'MAXIMUM SESSION USER LENGTH', 63, NULL);
1253 INSERT INTO sql_sizing VALUES (25005, 'MAXIMUM SYSTEM USER LENGTH', 63, NULL);
1256 SET supported_value = (SELECT typlen-1 FROM pg_catalog.pg_type WHERE typname = 'name'),
1257 comments = 'Might be less, depending on character set.'
1258 WHERE supported_value = 63;
1260 GRANT SELECT ON sql_sizing TO PUBLIC;
1265 * SQL_SIZING_PROFILES table
1268 -- The data in this table are defined by various profiles of SQL.
1269 -- Since we don't have any information about such profiles, we provide
1272 CREATE TABLE sql_sizing_profiles (
1273 sizing_id cardinal_number,
1274 sizing_name character_data,
1275 profile_id character_data,
1276 required_value cardinal_number,
1277 comments character_data
1280 GRANT SELECT ON sql_sizing_profiles TO PUBLIC;
1285 * TABLE_CONSTRAINTS view
1288 CREATE VIEW table_constraints AS
1289 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1290 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
1291 CAST(c.conname AS sql_identifier) AS constraint_name,
1292 CAST(current_database() AS sql_identifier) AS table_catalog,
1293 CAST(nr.nspname AS sql_identifier) AS table_schema,
1294 CAST(r.relname AS sql_identifier) AS table_name,
1296 CASE c.contype WHEN 'c' THEN 'CHECK'
1297 WHEN 'f' THEN 'FOREIGN KEY'
1298 WHEN 'p' THEN 'PRIMARY KEY'
1299 WHEN 'u' THEN 'UNIQUE' END
1300 AS character_data) AS constraint_type,
1301 CAST(CASE WHEN c.condeferrable THEN 'YES' ELSE 'NO' END AS character_data)
1303 CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS character_data)
1304 AS initially_deferred
1306 FROM pg_namespace nc,
1312 WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace
1313 AND c.conrelid = r.oid AND r.relowner = u.usesysid
1315 AND u.usename = current_user;
1317 -- FIMXE: Not-null constraints are missing here.
1319 GRANT SELECT ON table_constraints TO PUBLIC;
1324 * TABLE_PRIVILEGES view
1327 CREATE VIEW table_privileges AS
1328 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
1329 CAST(grantee.name AS sql_identifier) AS grantee,
1330 CAST(current_database() AS sql_identifier) AS table_catalog,
1331 CAST(nc.nspname AS sql_identifier) AS table_schema,
1332 CAST(c.relname AS sql_identifier) AS table_name,
1333 CAST(pr.type AS character_data) AS privilege_type,
1335 CASE WHEN aclcontains(c.relacl,
1336 makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr.type, true))
1337 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable,
1338 CAST('NO' AS character_data) AS with_hierarchy
1344 SELECT usesysid, 0, usename FROM pg_user
1346 SELECT 0, grosysid, groname FROM pg_group
1348 SELECT 0, 0, 'PUBLIC'
1349 ) AS grantee (usesysid, grosysid, name),
1350 (SELECT 'SELECT' UNION ALL
1351 SELECT 'DELETE' UNION ALL
1352 SELECT 'INSERT' UNION ALL
1353 SELECT 'UPDATE' UNION ALL
1354 SELECT 'REFERENCES' UNION ALL
1355 SELECT 'RULE' UNION ALL
1356 SELECT 'TRIGGER') AS pr (type)
1358 WHERE c.relnamespace = nc.oid
1359 AND c.relkind IN ('r', 'v')
1360 AND aclcontains(c.relacl,
1361 makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr.type, false))
1362 AND (u_grantor.usename = current_user
1363 OR grantee.name = current_user
1364 OR grantee.name = 'PUBLIC');
1366 GRANT SELECT ON table_privileges TO PUBLIC;
1374 CREATE VIEW tables AS
1375 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
1376 CAST(nc.nspname AS sql_identifier) AS table_schema,
1377 CAST(c.relname AS sql_identifier) AS table_name,
1380 CASE WHEN nc.nspname LIKE 'pg!_temp!_%' ESCAPE '!' THEN 'LOCAL TEMPORARY'
1381 WHEN c.relkind = 'r' THEN 'BASE TABLE'
1382 WHEN c.relkind = 'v' THEN 'VIEW'
1384 AS character_data) AS table_type,
1386 CAST(null AS sql_identifier) AS self_referencing_column_name,
1387 CAST(null AS character_data) AS reference_generation,
1389 CAST(null AS sql_identifier) AS user_defined_type_catalog,
1390 CAST(null AS sql_identifier) AS user_defined_type_schema,
1391 CAST(null AS sql_identifier) AS user_defined_name
1393 FROM pg_namespace nc, pg_class c, pg_user u
1395 WHERE c.relnamespace = nc.oid AND u.usesysid = c.relowner
1396 AND c.relkind IN ('r', 'v')
1397 AND (u.usename = current_user
1398 OR has_table_privilege(c.oid, 'SELECT')
1399 OR has_table_privilege(c.oid, 'INSERT')
1400 OR has_table_privilege(c.oid, 'UPDATE')
1401 OR has_table_privilege(c.oid, 'DELETE')
1402 OR has_table_privilege(c.oid, 'RULE')
1403 OR has_table_privilege(c.oid, 'REFERENCES')
1404 OR has_table_privilege(c.oid, 'TRIGGER') );
1406 GRANT SELECT ON tables TO PUBLIC;
1411 * TRIGGERED_UPDATE_COLUMNS view
1414 -- PostgreSQL doesn't allow the specification of individual triggered
1415 -- update columns, so this view is empty.
1417 CREATE VIEW triggered_update_columns AS
1418 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1419 CAST(null AS sql_identifier) AS trigger_schema,
1420 CAST(null AS sql_identifier) AS trigger_name,
1421 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1422 CAST(null AS sql_identifier) AS event_object_schema,
1423 CAST(null AS sql_identifier) AS event_object_table,
1424 CAST(null AS sql_identifier) AS event_object_column
1427 GRANT SELECT ON triggered_update_columns TO PUBLIC;
1435 CREATE VIEW triggers AS
1436 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1437 CAST(n.nspname AS sql_identifier) AS trigger_schema,
1438 CAST(t.tgname AS sql_identifier) AS trigger_name,
1439 CAST(em.text AS character_data) AS event_manipulation,
1440 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1441 CAST(n.nspname AS sql_identifier) AS event_object_schema,
1442 CAST(c.relname AS sql_identifier) AS event_object_table,
1443 CAST(null AS cardinal_number) AS action_order,
1444 CAST(null AS character_data) AS action_condition,
1446 substring(pg_get_triggerdef(t.oid) from
1447 position('EXECUTE PROCEDURE' in substring(pg_get_triggerdef(t.oid) from 48)) + 47)
1448 AS character_data) AS action_statement,
1450 CASE WHEN t.tgtype & 1 = 1 THEN 'ROW' ELSE 'STATEMENT' END
1451 AS character_data) AS action_orientation,
1453 CASE WHEN t.tgtype & 2 = 2 THEN 'BEFORE' ELSE 'AFTER' END
1454 AS character_data) AS condition_timing,
1455 CAST(null AS sql_identifier) AS condition_reference_old_table,
1456 CAST(null AS sql_identifier) AS condition_reference_new_table
1458 FROM pg_namespace n, pg_class c, pg_trigger t, pg_user u,
1459 (SELECT 4, 'INSERT' UNION ALL
1460 SELECT 8, 'DELETE' UNION ALL
1461 SELECT 16, 'UPDATE') AS em (num, text)
1463 WHERE n.oid = c.relnamespace
1464 AND c.oid = t.tgrelid
1465 AND c.relowner = u.usesysid
1466 AND t.tgtype & em.num <> 0
1467 AND NOT t.tgisconstraint
1468 AND u.usename = current_user;
1470 GRANT SELECT ON triggers TO PUBLIC;
1475 * USAGE_PRIVILEGES view
1478 -- Of the things currently implemented in PostgreSQL, usage privileges
1479 -- apply only to domains. Since domains have no real privileges, we
1480 -- represent all domains with implicit usage privilege here.
1482 CREATE VIEW usage_privileges AS
1483 SELECT CAST(u.usename AS sql_identifier) AS grantor,
1484 CAST('PUBLIC' AS sql_identifier) AS grantee,
1485 CAST(current_database() AS sql_identifier) AS object_catalog,
1486 CAST(n.nspname AS sql_identifier) AS object_schema,
1487 CAST(t.typname AS sql_identifier) AS object_name,
1488 CAST('DOMAIN' AS character_data) AS object_type,
1489 CAST('USAGE' AS character_data) AS privilege_type,
1490 CAST('NO' AS character_data) AS is_grantable
1496 WHERE u.usesysid = t.typowner
1497 AND t.typnamespace = n.oid
1498 AND t.typtype = 'd';
1500 GRANT SELECT ON usage_privileges TO PUBLIC;
1508 CREATE VIEW view_column_usage AS
1510 CAST(current_database() AS sql_identifier) AS view_catalog,
1511 CAST(nv.nspname AS sql_identifier) AS view_schema,
1512 CAST(v.relname AS sql_identifier) AS view_name,
1513 CAST(current_database() AS sql_identifier) AS table_catalog,
1514 CAST(nt.nspname AS sql_identifier) AS table_schema,
1515 CAST(t.relname AS sql_identifier) AS table_name,
1516 CAST(a.attname AS sql_identifier) AS column_name
1518 FROM pg_user, pg_namespace nv, pg_class v, pg_depend dv,
1519 pg_depend dt, pg_class t, pg_namespace nt,
1520 pg_attribute a, pg_user u
1522 WHERE nv.oid = v.relnamespace
1524 AND v.oid = dv.refobjid
1525 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
1526 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
1527 AND dv.deptype = 'i'
1528 AND dv.objid = dt.objid
1529 AND dv.refobjid <> dt.refobjid
1530 AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
1531 AND dt.refclassid = 'pg_catalog.pg_class'::regclass
1532 AND dt.refobjid = t.oid
1533 AND t.relnamespace = nt.oid
1534 AND t.relkind IN ('r', 'v')
1535 AND t.oid = a.attrelid
1536 AND dt.refobjsubid = a.attnum
1537 AND t.relowner = u.usesysid AND u.usename = current_user;
1539 GRANT SELECT ON view_column_usage TO PUBLIC;
1547 CREATE VIEW view_table_usage AS
1549 CAST(current_database() AS sql_identifier) AS view_catalog,
1550 CAST(nv.nspname AS sql_identifier) AS view_schema,
1551 CAST(v.relname AS sql_identifier) AS view_name,
1552 CAST(current_database() AS sql_identifier) AS table_catalog,
1553 CAST(nt.nspname AS sql_identifier) AS table_schema,
1554 CAST(t.relname AS sql_identifier) AS table_name
1556 FROM pg_user, pg_namespace nv, pg_class v, pg_depend dv,
1557 pg_depend dt, pg_class t, pg_namespace nt,
1560 WHERE nv.oid = v.relnamespace
1562 AND v.oid = dv.refobjid
1563 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
1564 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
1565 AND dv.deptype = 'i'
1566 AND dv.objid = dt.objid
1567 AND dv.refobjid <> dt.refobjid
1568 AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
1569 AND dt.refclassid = 'pg_catalog.pg_class'::regclass
1570 AND dt.refobjid = t.oid
1571 AND t.relnamespace = nt.oid
1572 AND t.relkind IN ('r', 'v')
1573 AND t.relowner = u.usesysid AND u.usename = current_user;
1575 GRANT SELECT ON view_table_usage TO PUBLIC;
1583 CREATE VIEW views AS
1584 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
1585 CAST(nc.nspname AS sql_identifier) AS table_schema,
1586 CAST(c.relname AS sql_identifier) AS table_name,
1589 CASE WHEN u.usename = current_user THEN pg_get_viewdef(c.oid)
1591 AS character_data) AS view_definition,
1593 CAST('NONE' AS character_data) AS check_option,
1594 CAST(null AS character_data) AS is_updatable, -- FIXME
1595 CAST(null AS character_data) AS is_insertable_into -- FIXME
1597 FROM pg_namespace nc, pg_class c, pg_user u
1599 WHERE c.relnamespace = nc.oid AND u.usesysid = c.relowner
1601 AND (u.usename = current_user
1602 OR has_table_privilege(c.oid, 'SELECT')
1603 OR has_table_privilege(c.oid, 'INSERT')
1604 OR has_table_privilege(c.oid, 'UPDATE')
1605 OR has_table_privilege(c.oid, 'DELETE')
1606 OR has_table_privilege(c.oid, 'RULE')
1607 OR has_table_privilege(c.oid, 'REFERENCES')
1608 OR has_table_privilege(c.oid, 'TRIGGER') );
1610 GRANT SELECT ON views TO PUBLIC;
1613 -- The following views have dependencies that force them to appear out of order.
1617 * DATA_TYPE_PRIVILEGES view
1620 CREATE VIEW data_type_privileges AS
1621 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
1622 CAST(x.objschema AS sql_identifier) AS object_schema,
1623 CAST(x.objname AS sql_identifier) AS object_name,
1624 CAST(x.objtype AS character_data) AS object_type,
1625 CAST(x.objdtdid AS sql_identifier) AS dtd_identifier
1629 SELECT table_schema, table_name, 'TABLE'::text, dtd_identifier FROM columns
1631 SELECT domain_schema, domain_name, 'DOMAIN'::text, dtd_identifier FROM domains
1633 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM parameters
1635 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM routines
1636 ) AS x (objschema, objname, objtype, objdtdid);
1638 GRANT SELECT ON data_type_privileges TO PUBLIC;
1643 * ELEMENT_TYPES view
1646 CREATE VIEW element_types AS
1647 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
1648 CAST(n.nspname AS sql_identifier) AS object_schema,
1649 CAST(x.objname AS sql_identifier) AS object_name,
1650 CAST(x.objtype AS character_data) AS object_type,
1651 CAST(x.objdtdid AS sql_identifier) AS array_type_identifier,
1653 CASE WHEN nbt.nspname = 'pg_catalog' THEN format_type(bt.oid, null)
1654 ELSE 'USER-DEFINED' END AS character_data) AS data_type,
1656 CAST(null AS cardinal_number) AS character_maximum_length,
1657 CAST(null AS cardinal_number) AS character_octet_length,
1658 CAST(null AS sql_identifier) AS character_set_catalog,
1659 CAST(null AS sql_identifier) AS character_set_schema,
1660 CAST(null AS sql_identifier) AS character_set_name,
1661 CAST(null AS sql_identifier) AS collation_catalog,
1662 CAST(null AS sql_identifier) AS collation_schema,
1663 CAST(null AS sql_identifier) AS collation_name,
1664 CAST(null AS cardinal_number) AS numeric_precision,
1665 CAST(null AS cardinal_number) AS numeric_precision_radix,
1666 CAST(null AS cardinal_number) AS numeric_scale,
1667 CAST(null AS cardinal_number) AS datetime_precision,
1668 CAST(null AS character_data) AS interval_type,
1669 CAST(null AS character_data) AS interval_precision,
1671 CAST(null AS character_data) AS domain_default, -- XXX maybe a bug in the standard
1673 CAST(current_database() AS sql_identifier) AS udt_catalog,
1674 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
1675 CAST(bt.typname AS sql_identifier) AS udt_name,
1677 CAST(null AS sql_identifier) AS scope_catalog,
1678 CAST(null AS sql_identifier) AS scope_schema,
1679 CAST(null AS sql_identifier) AS scope_name,
1681 CAST(null AS cardinal_number) AS maximum_cardinality,
1682 CAST('a' || x.objdtdid AS sql_identifier) AS dtd_identifier
1684 FROM pg_namespace n, pg_type at, pg_namespace nbt, pg_type bt,
1687 SELECT c.relnamespace, CAST(c.relname AS sql_identifier),
1688 'TABLE'::text, a.attnum, a.atttypid
1689 FROM pg_class c, pg_attribute a
1690 WHERE c.oid = a.attrelid
1691 AND c.relkind IN ('r', 'v')
1692 AND attnum > 0 AND NOT attisdropped
1697 SELECT t.typnamespace, CAST(t.typname AS sql_identifier),
1698 'DOMAIN'::text, 1, t.typbasetype
1700 WHERE t.typtype = 'd'
1705 SELECT p.pronamespace, CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier),
1706 'ROUTINE'::text, pos.n, p.proargtypes[pos.n-1]
1707 FROM pg_proc p, _pg_keypositions() AS pos(n)
1708 WHERE p.pronargs >= pos.n
1713 SELECT p.pronamespace, CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier),
1714 'ROUTINE'::text, 0, p.prorettype
1717 ) AS x (objschema, objname, objtype, objdtdid, objtypeid)
1719 WHERE n.oid = x.objschema
1720 AND at.oid = x.objtypeid
1721 AND (at.typelem <> 0 AND at.typlen = -1)
1722 AND at.typelem = bt.oid
1723 AND nbt.oid = bt.typnamespace
1725 AND (n.nspname, x.objname, x.objtype, x.objdtdid) IN
1726 ( SELECT object_schema, object_name, object_type, dtd_identifier
1727 FROM data_type_privileges );
1729 GRANT SELECT ON element_types TO PUBLIC;