2 * SQL Information Schema
3 * as defined in ISO 9075-2:1999 chapter 20
5 * Copyright 2002, PostgreSQL Global Development Group
7 * $Id: information_schema.sql,v 1.6 2003/05/25 09:36:09 petere Exp $
13 * INFORMATION_SCHEMA schema
16 CREATE SCHEMA information_schema;
17 GRANT USAGE ON SCHEMA information_schema TO PUBLIC;
18 SET search_path TO information_schema, public;
21 -- Note: 20.3 follows later. Some genius screwed up the order in the standard.
26 * CARDINAL_NUMBER domain
29 CREATE DOMAIN cardinal_number AS integer
30 CONSTRAINT cardinal_number_domain_check CHECK (value >= 0);
35 * CHARACTER_DATA domain
38 CREATE DOMAIN character_data AS character varying;
43 * SQL_IDENTIFIER domain
46 CREATE DOMAIN sql_identifier AS character varying;
51 * INFORMATION_SCHEMA_CATALOG_NAME view
54 CREATE VIEW information_schema_catalog_name AS
55 SELECT CAST(current_database() AS sql_identifier) AS catalog_name;
57 GRANT SELECT ON information_schema_catalog_name TO PUBLIC;
65 CREATE DOMAIN time_stamp AS timestamp(2)
66 DEFAULT current_timestamp(2);
71 * CHECK_CONSTRAINTS view
74 CREATE VIEW check_constraints AS
75 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
76 CAST(rs.nspname AS sql_identifier) AS constraint_schema,
77 CAST(con.conname AS sql_identifier) AS constraint_name,
78 CAST(con.consrc AS character_data) AS check_clause
81 LEFT OUTER JOIN pg_class c on (c.oid = con.conrelid)
82 LEFT OUTER JOIN pg_type t on (t.oid = con.contypid),
84 WHERE rs.oid = con.connamespace
85 AND u.usesysid = coalesce(c.relowner, t.typowner)
86 AND u.usename = current_user
87 AND con.contype = 'c';
89 GRANT SELECT ON check_constraints TO PUBLIC;
94 * COLUMN_DOMAIN_USAGE view
97 CREATE VIEW column_domain_usage AS
98 SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
99 CAST(nt.nspname AS sql_identifier) AS domain_schema,
100 CAST(t.typname AS sql_identifier) AS domain_name,
101 CAST(current_database() AS sql_identifier) AS table_catalog,
102 CAST(nc.nspname AS sql_identifier) AS table_schema,
103 CAST(c.relname AS sql_identifier) AS table_name,
104 CAST(a.attname AS sql_identifier) AS column_name
106 FROM pg_type t, pg_namespace nt, pg_class c, pg_namespace nc,
107 pg_attribute a, pg_user u
109 WHERE t.typnamespace = nt.oid AND t.typtype = 'd'
110 AND c.relnamespace = nc.oid AND a.attrelid = c.oid
111 AND a.atttypid = t.oid AND t.typowner = u.usesysid
112 AND u.usename = current_user;
114 GRANT SELECT ON column_domain_usage TO PUBLIC;
122 -- PostgreSQL does not have column privileges, so this view is empty.
123 -- (Table privileges do not also count as column privileges.)
125 CREATE VIEW column_privileges AS
126 SELECT CAST(null AS sql_identifier) AS grantor,
127 CAST(null AS sql_identifier) AS grantee,
128 CAST(null AS sql_identifier) AS table_catalog,
129 CAST(null AS sql_identifier) AS table_schema,
130 CAST(null AS sql_identifier) AS table_name,
131 CAST(null AS sql_identifier) AS column_name,
132 CAST(null AS character_data) AS privilege_type,
133 CAST(null AS character_data) AS is_grantable
136 GRANT SELECT ON column_privileges TO PUBLIC;
144 CREATE VIEW columns AS
145 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
146 CAST(nc.nspname AS sql_identifier) AS table_schema,
147 CAST(c.relname AS sql_identifier) AS table_name,
148 CAST(a.attname AS sql_identifier) AS column_name,
149 CAST(a.attnum AS cardinal_number) AS ordinal_position,
151 CASE WHEN u.usename = current_user THEN a.adsrc ELSE null END
154 CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
159 CASE WHEN t.typtype = 'd' THEN
160 CASE WHEN nbt.nspname = 'pg_catalog'
161 THEN format_type(t.typbasetype, null)
162 ELSE 'USER-DEFINED' END
164 CASE WHEN nt.nspname = 'pg_catalog'
165 THEN format_type(a.atttypid, null)
166 ELSE 'USER-DEFINED' END
172 CASE WHEN t.typtype = 'd' THEN
173 CASE WHEN t.typbasetype IN (25, 1042, 1043, 1560, 1562) AND t.typtypmod <> -1
177 CASE WHEN a.atttypid IN (25, 1042, 1043, 1560, 1562) AND a.atttypmod <> -1
182 AS character_maximum_length,
185 CASE WHEN t.typtype = 'd' THEN
186 CASE WHEN t.typbasetype IN (25, 1042, 1043) THEN 2^30 ELSE null END
188 CASE WHEN a.atttypid IN (25, 1042, 1043) THEN 2^30 ELSE null END
191 AS character_octet_length,
194 CASE (CASE WHEN t.typtype = 'd' THEN t.typbasetype ELSE a.atttypid END)
195 WHEN 21 /*int2*/ THEN 16
196 WHEN 23 /*int4*/ THEN 32
197 WHEN 20 /*int8*/ THEN 64
198 WHEN 1700 /*numeric*/ THEN ((CASE WHEN t.typtype = 'd' THEN t.typtypmod ELSE a.atttypmod END - 4) >> 16) & 65535
199 WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/
200 WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/
203 AS numeric_precision,
206 CASE WHEN t.typtype = 'd' THEN
207 CASE WHEN t.typbasetype IN (21, 23, 20, 700, 701) THEN 2
208 WHEN t.typbasetype IN (1700) THEN 10
211 CASE WHEN a.atttypid IN (21, 23, 20, 700, 701) THEN 2
212 WHEN a.atttypid IN (1700) THEN 10
216 AS numeric_precision_radix,
219 CASE WHEN t.typtype = 'd' THEN
220 CASE WHEN t.typbasetype IN (21, 23, 20) THEN 0
221 WHEN t.typbasetype IN (1700) THEN (t.typtypmod - 4) & 65535
224 CASE WHEN a.atttypid IN (21, 23, 20) THEN 0
225 WHEN a.atttypid IN (1700) THEN (a.atttypmod - 4) & 65535
232 CASE WHEN t.typtype = 'd' THEN
233 CASE WHEN t.typbasetype IN (1083, 1114, 1184, 1266)
234 THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod ELSE null END)
235 WHEN t.typbasetype IN (1186)
236 THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod & 65535 ELSE null END)
239 CASE WHEN a.atttypid IN (1083, 1114, 1184, 1266)
240 THEN (CASE WHEN a.atttypmod <> -1 THEN a.atttypmod ELSE null END)
241 WHEN a.atttypid IN (1186)
242 THEN (CASE WHEN a.atttypmod <> -1 THEN a.atttypmod & 65535 ELSE null END)
246 AS datetime_precision,
248 CAST(null AS character_data) AS interval_type, -- XXX
249 CAST(null AS character_data) AS interval_precision, -- XXX
251 CAST(null AS sql_identifier) AS character_set_catalog,
252 CAST(null AS sql_identifier) AS character_set_schema,
253 CAST(null AS sql_identifier) AS character_set_name,
255 CAST(null AS sql_identifier) AS collation_catalog,
256 CAST(null AS sql_identifier) AS collation_schema,
257 CAST(null AS sql_identifier) AS collation_name,
259 CAST(CASE WHEN t.typtype = 'd' THEN current_database() ELSE null END
260 AS sql_identifier) AS domain_catalog,
261 CAST(CASE WHEN t.typtype = 'd' THEN nt.nspname ELSE null END
262 AS sql_identifier) AS domain_schema,
263 CAST(CASE WHEN t.typtype = 'd' THEN t.typname ELSE null END
264 AS sql_identifier) AS domain_name,
266 CAST(current_database() AS sql_identifier) AS udt_catalog,
267 CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,
268 CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,
270 CAST(null AS sql_identifier) AS scope_catalog,
271 CAST(null AS sql_identifier) AS scope_schema,
272 CAST(null AS sql_identifier) AS scope_name,
274 CAST(null AS cardinal_number) AS maximum_cardinality,
275 CAST(CAST(t.oid AS varchar) AS sql_identifier) AS dtd_identifier,
276 CAST('NO' AS character_data) AS is_self_referencing
278 FROM (pg_attribute LEFT JOIN pg_attrdef ON attrelid = adrelid AND attnum = adnum) AS a,
279 pg_class c, pg_namespace nc, pg_user u,
280 (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
281 LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
282 ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
284 WHERE a.attrelid = c.oid
285 AND a.atttypid = t.oid
286 AND u.usesysid = c.relowner
287 AND nc.oid = c.relnamespace
289 AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
291 AND (u.usename = current_user
292 OR has_table_privilege(c.oid, 'SELECT')
293 OR has_table_privilege(c.oid, 'INSERT')
294 OR has_table_privilege(c.oid, 'UPDATE')
295 OR has_table_privilege(c.oid, 'DELETE')
296 OR has_table_privilege(c.oid, 'RULE')
297 OR has_table_privilege(c.oid, 'RERERENCES')
298 OR has_table_privilege(c.oid, 'TRIGGER') );
300 GRANT SELECT ON columns TO PUBLIC;
305 * CONSTRAINT_COLUMN_USAGE view
308 -- FIXME: This only works for check constraints so far; for the others
309 -- we need a built-in way to convert arrays to virtual tables.
311 CREATE VIEW constraint_column_usage AS
312 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
313 CAST(tblschema AS sql_identifier) AS table_schema,
314 CAST(tblname AS sql_identifier) AS table_name,
315 CAST(colname AS sql_identifier) AS column_name,
316 CAST(current_database() AS sql_identifier) AS constraint_catalog,
317 CAST(cstrschema AS sql_identifier) AS constraint_schema,
318 CAST(cstrname AS sql_identifier) AS constraint_name
321 SELECT DISTINCT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
322 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_depend d, pg_namespace nc, pg_constraint c
323 WHERE nr.oid = r.relnamespace
324 AND r.oid = a.attrelid
325 AND d.refclassid = (SELECT oid FROM pg_class WHERE relname = 'pg_class')
326 AND d.refobjid = r.oid
327 AND d.refobjsubid = a.attnum
328 AND d.classid = (SELECT oid FROM pg_class WHERE relname = 'pg_constraint')
330 AND c.connamespace = nc.oid
332 ) AS x (tblschema, tblname, tblowner, colname, cstrschema, cstrname),
335 WHERE x.tblowner = u.usesysid AND u.usename = current_user;
337 GRANT SELECT ON constraint_column_usage TO PUBLIC;
342 * CONSTRAINT_TABLE_USAGE view
345 CREATE VIEW constraint_table_usage AS
346 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
347 CAST(nr.nspname AS sql_identifier) AS table_schema,
348 CAST(r.relname AS sql_identifier) AS table_name,
349 CAST(current_database() AS sql_identifier) AS constraint_catalog,
350 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
351 CAST(c.conname AS sql_identifier) AS constraint_name
353 FROM pg_constraint c, pg_namespace nc,
354 pg_class r, pg_namespace nr,
357 WHERE c.connamespace = nc.oid AND r.relnamespace = nr.oid
358 AND ( (c.contype = 'f' AND c.confrelid = r.oid)
359 OR (c.contype IN ('p', 'u') AND c.conrelid = r.oid) )
360 AND r.relowner = u.usesysid AND u.usename = current_user;
362 GRANT SELECT ON constraint_table_usage TO PUBLIC;
367 * DOMAIN_CONSTRAINTS view
370 CREATE VIEW domain_constraints AS
371 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
372 CAST(rs.nspname AS sql_identifier) AS constraint_schema,
373 CAST(con.conname AS sql_identifier) AS constraint_name,
374 CAST(current_database() AS sql_identifier) AS domain_catalog,
375 CAST(n.nspname AS sql_identifier) AS domain_schema,
376 CAST(t.typname AS sql_identifier) AS domain_name,
377 CAST(CASE WHEN condeferrable THEN 'YES' ELSE 'NO' END
378 AS character_data) AS is_deferrable,
379 CAST(CASE WHEN condeferred THEN 'YES' ELSE 'NO' END
380 AS character_data) AS initially_deferred
381 FROM pg_namespace rs, pg_namespace n, pg_constraint con, pg_type t, pg_user u
382 WHERE rs.oid = con.connamespace
383 AND n.oid = t.typnamespace
384 AND u.usesysid = t.typowner
385 AND u.usename = current_user
386 AND t.oid = con.contypid;
388 GRANT SELECT ON domain_constraints TO PUBLIC;
396 CREATE VIEW domains AS
397 SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
398 CAST(nt.nspname AS sql_identifier) AS domain_schema,
399 CAST(t.typname AS sql_identifier) AS domain_name,
402 CASE WHEN nbt.nspname = 'pg_catalog'
403 THEN format_type(t.typbasetype, null)
404 ELSE 'USER-DEFINED' END
409 CASE WHEN t.typbasetype IN (25, 1042, 1043, 1560, 1562) AND t.typtypmod <> -1
413 AS character_maximum_length,
416 CASE WHEN t.typbasetype IN (25, 1042, 1043) THEN 2^30 ELSE null END
418 AS character_octet_length,
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,
430 WHEN 21 /*int2*/ THEN 16
431 WHEN 23 /*int4*/ THEN 32
432 WHEN 20 /*int8*/ THEN 64
433 WHEN 1700 /*numeric*/ THEN ((t.typtypmod - 4) >> 16) & 65535
434 WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/
435 WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/
438 AS numeric_precision,
441 CASE WHEN t.typbasetype IN (21, 23, 20, 700, 701) THEN 2
442 WHEN t.typbasetype IN (1700) THEN 10
445 AS numeric_precision_radix,
448 CASE WHEN t.typbasetype IN (21, 23, 20) THEN 0
449 WHEN t.typbasetype IN (1700) THEN (t.typtypmod - 4) & 65535
455 CASE WHEN t.typbasetype IN (1083, 1114, 1184, 1266)
456 THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod ELSE null END)
457 WHEN t.typbasetype IN (1186)
458 THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod & 65535 ELSE null END)
461 AS datetime_precision,
463 CAST(null AS character_data) AS interval_type, -- XXX
464 CAST(null AS character_data) AS interval_precision, -- XXX
466 CAST(t.typdefault AS character_data) AS domain_default,
468 CAST(current_database() AS sql_identifier) AS udt_catalog,
469 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
470 CAST(bt.typname AS sql_identifier) AS udt_name,
472 CAST(null AS sql_identifier) AS scope_catalog,
473 CAST(null AS sql_identifier) AS scope_schema,
474 CAST(null AS sql_identifier) AS scope_name,
476 CAST(null AS cardinal_number) AS maximum_cardinality,
477 CAST(CAST(t.oid AS varchar) AS sql_identifier) AS dtd_identifier
479 FROM pg_type t, pg_namespace nt,
480 pg_type bt, pg_namespace nbt
482 WHERE t.typnamespace = nt.oid
483 AND t.typbasetype = bt.oid
484 AND bt.typnamespace = nbt.oid
487 GRANT SELECT ON domains TO PUBLIC;
492 * REFERENTIAL_CONSTRAINTS view
495 CREATE VIEW referential_constraints AS
496 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
497 CAST(ncon.nspname AS sql_identifier) AS constraint_schema,
498 CAST(con.conname AS sql_identifier) AS constraint_name,
499 CAST(current_database() AS sql_identifier) AS unique_constraint_catalog,
500 CAST(npkc.nspname AS sql_identifier) AS unique_constraint_schema,
501 CAST(pkc.conname AS sql_identifier) AS unique_constraint_name,
504 CASE con.confmatchtype WHEN 'f' THEN 'FULL'
505 WHEN 'p' THEN 'PARTIAL'
506 WHEN 'u' THEN 'NONE' END
507 AS character_data) AS match_option,
510 CASE con.confupdtype WHEN 'c' THEN 'CASCADE'
511 WHEN 'n' THEN 'SET NULL'
512 WHEN 'd' THEN 'SET DEFAULT'
513 WHEN 'r' THEN 'RESTRICT'
514 WHEN 'a' THEN 'NOACTION' END
515 AS character_data) AS update_rule,
518 CASE con.confdeltype WHEN 'c' THEN 'CASCADE'
519 WHEN 'n' THEN 'SET NULL'
520 WHEN 'd' THEN 'SET DEFAULT'
521 WHEN 'r' THEN 'RESTRICT'
522 WHEN 'a' THEN 'NOACTION' END
523 AS character_data) AS delete_rule
525 FROM pg_namespace ncon,
532 WHERE ncon.oid = con.connamespace
533 AND con.conrelid = c.oid
534 AND con.confkey = pkc.conkey
535 AND pkc.connamespace = npkc.oid
536 AND c.relowner = u.usesysid
537 AND u.usename = current_user;
539 GRANT SELECT ON referential_constraints TO PUBLIC;
547 CREATE VIEW schemata AS
548 SELECT CAST(current_database() AS sql_identifier) AS catalog_name,
549 CAST(n.nspname AS sql_identifier) AS schema_name,
550 CAST(u.usename AS sql_identifier) AS schema_owner,
551 CAST(null AS sql_identifier) AS default_character_set_catalog,
552 CAST(null AS sql_identifier) AS default_character_set_schema,
553 CAST(null AS sql_identifier) AS default_character_set_name,
554 CAST(null AS character_data) AS sql_path
555 FROM pg_namespace n, pg_user u
556 WHERE n.nspowner = u.usesysid AND u.usename = current_user;
558 GRANT SELECT ON schemata TO PUBLIC;
566 CREATE TABLE sql_features (
567 feature_id character_data,
568 feature_name character_data,
569 sub_feature_id character_data,
570 sub_feature_name character_data,
571 is_supported character_data,
572 is_verified_by character_data,
573 comments character_data
576 -- Will be filled with external data by initdb.
578 GRANT SELECT ON sql_features TO PUBLIC;
583 * SQL_IMPLEMENTATION_INFO table
586 -- Note: Implementation information items are defined in ISO 9075-3:1999,
589 CREATE TABLE sql_implementation_info (
590 implementation_info_id character_data,
591 implementation_info_name character_data,
592 integer_value cardinal_number,
593 character_value character_data,
594 comments character_data
597 INSERT INTO sql_implementation_info VALUES ('10003', 'CATALOG NAME', NULL, 'Y', NULL);
598 INSERT INTO sql_implementation_info VALUES ('10004', 'COLLATING SEQUENCE', NULL, '', 'not supported');
599 INSERT INTO sql_implementation_info VALUES ('23', 'CURSOR COMMIT BEHAVIOR', 1, NULL, 'close cursors and retain prepared statements');
600 INSERT INTO sql_implementation_info VALUES ('2', 'DATA SOURCE NAME', NULL, '', NULL);
601 INSERT INTO sql_implementation_info VALUES ('17', 'DBMS NAME', NULL, (select trim(trailing ' ' from substring(version() from '^[^0-9]*'))), NULL);
602 INSERT INTO sql_implementation_info VALUES ('18', 'DBMS VERSION', NULL, '???', NULL); -- filled by initdb
603 INSERT INTO sql_implementation_info VALUES ('26', 'DEFAULT TRANSACTION ISOLATION', 2, NULL, 'READ COMMITED; user-settable');
604 INSERT INTO sql_implementation_info VALUES ('28', 'IDENTIFIER CASE', 3, NULL, 'stored in mixed case - case sensitive');
605 INSERT INTO sql_implementation_info VALUES ('85', 'NULL COLLATION', 0, NULL, 'nulls higher than non-nulls');
606 INSERT INTO sql_implementation_info VALUES ('13', 'SERVER NAME', NULL, '', NULL);
607 INSERT INTO sql_implementation_info VALUES ('94', 'SPECIAL CHARACTERS', NULL, '', 'all non-ASCII characters allowed');
608 INSERT INTO sql_implementation_info VALUES ('46', 'TRANSACTION CAPABLE', 2, NULL, 'both DML and DDL');
610 GRANT SELECT ON sql_implementation_info TO PUBLIC;
615 * SQL_LANGUAGES table
618 CREATE TABLE sql_languages (
619 sql_language_source character_data,
620 sql_language_year character_data,
621 sql_language_conformance character_data,
622 sql_language_integrity character_data,
623 sql_language_implementation character_data,
624 sql_language_binding_style character_data,
625 sql_language_programming_language character_data
628 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'DIRECT', NULL);
629 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
631 GRANT SELECT ON sql_languages TO PUBLIC;
639 CREATE TABLE sql_packages (
640 feature_id character_data,
641 feature_name character_data,
642 is_supported character_data,
643 is_verified_by character_data,
644 comments character_data
647 INSERT INTO sql_packages VALUES ('PKG000', 'Core', 'NO', NULL, '');
648 INSERT INTO sql_packages VALUES ('PKG001', 'Enhanced datetime facilities', 'YES', NULL, '');
649 INSERT INTO sql_packages VALUES ('PKG002', 'Enhanced integrity management', 'NO', NULL, '');
650 INSERT INTO sql_packages VALUES ('PKG003', 'OLAP facilities', 'NO', NULL, '');
651 INSERT INTO sql_packages VALUES ('PKG004', 'PSM', 'NO', NULL, 'PL/pgSQL is similar.');
652 INSERT INTO sql_packages VALUES ('PKG005', 'CLI', 'NO', NULL, 'ODBC is similar.');
653 INSERT INTO sql_packages VALUES ('PKG006', 'Basic object support', 'NO', NULL, '');
654 INSERT INTO sql_packages VALUES ('PKG007', 'Enhanced object support', 'NO', NULL, '');
655 INSERT INTO sql_packages VALUES ('PKG008', 'Active database', 'NO', NULL, '');
656 INSERT INTO sql_packages VALUES ('PKG009', 'SQL/MM support', 'NO', NULL, '');
658 GRANT SELECT ON sql_packages TO PUBLIC;
666 -- Note: Sizing items are defined in ISO 9075-3:1999, clause 7.2.
668 CREATE TABLE sql_sizing (
669 sizing_id cardinal_number,
670 sizing_name character_data,
671 supported_value cardinal_number,
672 comments character_data
675 INSERT INTO sql_sizing VALUES (34, 'MAXIMUM CATALOG NAME LENGTH', 63, NULL);
676 INSERT INTO sql_sizing VALUES (30, 'MAXIMUM COLUMN NAME LENGTH', 63, NULL);
677 INSERT INTO sql_sizing VALUES (97, 'MAXIMUM COLUMNS IN GROUP BY', 0, NULL);
678 INSERT INTO sql_sizing VALUES (99, 'MAXIMUM COLUMNS IN ORDER BY', 0, NULL);
679 INSERT INTO sql_sizing VALUES (100, 'MAXIMUM COLUMNS IN SELECT', 0, NULL);
680 INSERT INTO sql_sizing VALUES (101, 'MAXIMUM COLUMNS IN TABLE', 1600, NULL); -- match MaxHeapAttributeNumber
681 INSERT INTO sql_sizing VALUES (1, 'MAXIMUM CONCURRENT ACTIVITIES', 0, NULL);
682 INSERT INTO sql_sizing VALUES (31, 'MAXIMUM CURSOR NAME LENGTH', 63, NULL);
683 INSERT INTO sql_sizing VALUES (0, 'MAXIMUM DRIVER CONNECTIONS', NULL, NULL);
684 INSERT INTO sql_sizing VALUES (10005, 'MAXIMUM IDENTIFIER LENGTH', 63, NULL);
685 INSERT INTO sql_sizing VALUES (32, 'MAXIMUM SCHEMA NAME LENGTH', 63, NULL);
686 INSERT INTO sql_sizing VALUES (20000, 'MAXIMUM STATEMENT OCTETS', 0, NULL);
687 INSERT INTO sql_sizing VALUES (20001, 'MAXIMUM STATEMENT OCTETS DATA', 0, NULL);
688 INSERT INTO sql_sizing VALUES (20002, 'MAXIMUM STATEMENT OCTETS SCHEMA', 0, NULL);
689 INSERT INTO sql_sizing VALUES (35, 'MAXIMUM TABLE NAME LENGTH', 63, NULL);
690 INSERT INTO sql_sizing VALUES (106, 'MAXIMUM TABLES IN SELECT', 0, NULL);
691 INSERT INTO sql_sizing VALUES (107, 'MAXIMUM USER NAME LENGTH', 63, NULL);
692 INSERT INTO sql_sizing VALUES (25000, 'MAXIMUM CURRENT DEFAULT TRANSFORM GROUP LENGTH', NULL, NULL);
693 INSERT INTO sql_sizing VALUES (25001, 'MAXIMUM CURRENT TRANSFORM GROUP LENGTH', NULL, NULL);
694 INSERT INTO sql_sizing VALUES (25002, 'MAXIMUM CURRENT PATH LENGTH', 0, NULL);
695 INSERT INTO sql_sizing VALUES (25003, 'MAXIMUM CURRENT ROLE LENGTH', NULL, NULL);
696 INSERT INTO sql_sizing VALUES (25004, 'MAXIMUM SESSION USER LENGTH', 63, NULL);
697 INSERT INTO sql_sizing VALUES (25005, 'MAXIMUM SYSTEM USER LENGTH', 63, NULL);
700 SET supported_value = (SELECT typlen-1 FROM pg_catalog.pg_type WHERE typname = 'name'),
701 comments = 'Might be less, depending on character set.'
702 WHERE supported_value = 63;
704 GRANT SELECT ON sql_sizing TO PUBLIC;
709 * SQL_SIZING_PROFILES table
712 -- The data in this table are defined by various profiles of SQL.
713 -- Since we don't have any information about such profiles, we provide
716 CREATE TABLE sql_sizing_profiles (
717 sizing_id cardinal_number,
718 sizing_name character_data,
719 profile_id character_data,
720 required_value cardinal_number,
721 comments character_data
724 GRANT SELECT ON sql_sizing_profiles TO PUBLIC;
729 * TABLE_CONSTRAINTS view
732 CREATE VIEW table_constraints AS
733 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
734 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
735 CAST(c.conname AS sql_identifier) AS constraint_name,
736 CAST(current_database() AS sql_identifier) AS table_catalog,
737 CAST(nr.nspname AS sql_identifier) AS table_schema,
738 CAST(r.relname AS sql_identifier) AS table_name,
740 CASE c.contype WHEN 'c' THEN 'CHECK'
741 WHEN 'f' THEN 'FOREIGN KEY'
742 WHEN 'p' THEN 'PRIMARY KEY'
743 WHEN 'u' THEN 'UNIQUE' END
744 AS character_data) AS constraint_type,
745 CAST(CASE WHEN c.condeferrable THEN 'YES' ELSE 'NO' END AS character_data)
747 CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS character_data)
748 AS initially_deferred
750 FROM pg_namespace nc,
756 WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace
757 AND c.conrelid = r.oid AND r.relowner = u.usesysid
758 AND u.usename = current_user;
760 -- FIMXE: Not-null constraints are missing here.
762 GRANT SELECT ON table_constraints TO PUBLIC;
767 * TABLE_PRIVILEGES view
770 CREATE VIEW table_privileges AS
771 SELECT CAST(u_owner.usename AS sql_identifier) AS grantor,
772 CAST(u_grantee.usename AS sql_identifier) AS grantee,
773 CAST(current_database() AS sql_identifier) AS table_catalog,
774 CAST(nc.nspname AS sql_identifier) AS table_schema,
775 CAST(c.relname AS sql_identifier) AS table_name,
776 CAST(pr.type AS character_data) AS privilege_type,
777 CAST('NO' AS character_data) AS is_grantable,
778 CAST('NO' AS character_data) AS with_hierarchy
780 FROM pg_user u_owner,
784 (SELECT 'SELECT' UNION SELECT 'DELETE' UNION SELECT 'INSERT' UNION SELECT 'UPDATE'
785 UNION SELECT 'REFERENCES' UNION SELECT 'TRIGGER') AS pr (type)
787 WHERE u_owner.usesysid = c.relowner
788 AND c.relnamespace = nc.oid
789 AND has_table_privilege(u_grantee.usename, c.oid, pr.type)
791 AND (u_owner.usename = current_user OR u_grantee.usename = current_user);
793 GRANT SELECT ON table_privileges TO PUBLIC;
801 CREATE VIEW tables AS
802 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
803 CAST(nc.nspname AS sql_identifier) AS table_schema,
804 CAST(c.relname AS sql_identifier) AS table_name,
807 CASE WHEN nc.nspname LIKE 'pg!_temp!_%' ESCAPE '!' THEN 'LOCAL TEMPORARY'
808 WHEN c.relkind = 'r' THEN 'BASE TABLE'
809 WHEN c.relkind = 'v' THEN 'VIEW'
811 AS character_data) AS table_type,
813 CAST(null AS sql_identifier) AS self_referencing_column_name,
814 CAST(null AS character_data) AS reference_generation,
816 CAST(null AS sql_identifier) AS user_defined_type_catalog,
817 CAST(null AS sql_identifier) AS user_defined_type_schema,
818 CAST(null AS sql_identifier) AS user_defined_name
820 FROM pg_namespace nc, pg_class c, pg_user u
822 WHERE c.relnamespace = nc.oid AND u.usesysid = c.relowner
823 AND c.relkind IN ('r', 'v')
824 AND (u.usename = current_user
825 OR has_table_privilege(c.oid, 'SELECT')
826 OR has_table_privilege(c.oid, 'INSERT')
827 OR has_table_privilege(c.oid, 'UPDATE')
828 OR has_table_privilege(c.oid, 'DELETE')
829 OR has_table_privilege(c.oid, 'RULE')
830 OR has_table_privilege(c.oid, 'RERERENCES')
831 OR has_table_privilege(c.oid, 'TRIGGER') );
833 GRANT SELECT ON tables TO PUBLIC;
838 * USAGE_PRIVILEGES view
841 -- Of the things currently implemented in PostgreSQL, usage privileges
842 -- apply only to domains. Since domains have no real privileges, we
843 -- represent all domains with implicit usage privilege here.
845 CREATE VIEW usage_privileges AS
846 SELECT CAST(u.usename AS sql_identifier) AS grantor,
847 CAST('PUBLIC' AS sql_identifier) AS grantee,
848 CAST(current_database() AS sql_identifier) AS object_catalog,
849 CAST(n.nspname AS sql_identifier) AS object_schema,
850 CAST(t.typname AS sql_identifier) AS object_name,
851 CAST('DOMAIN' AS character_data) AS object_type,
852 CAST('USAGE' AS character_data) AS privilege_type,
853 CAST('NO' AS character_data) AS is_grantable
859 WHERE u.usesysid = t.typowner
860 AND t.typnamespace = n.oid
863 GRANT SELECT ON usage_privileges TO PUBLIC;
872 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
873 CAST(nc.nspname AS sql_identifier) AS table_schema,
874 CAST(c.relname AS sql_identifier) AS table_name,
877 CASE WHEN u.usename = current_user THEN pg_get_viewdef(c.oid)
879 AS character_data) AS view_definition,
881 CAST('NONE' AS character_data) AS check_option,
882 CAST(null AS character_data) AS is_updatable, -- FIXME
883 CAST(null AS character_data) AS is_insertable_into -- FIXME
885 FROM pg_namespace nc, pg_class c, pg_user u
887 WHERE c.relnamespace = nc.oid AND u.usesysid = c.relowner
889 AND (u.usename = current_user
890 OR has_table_privilege(c.oid, 'SELECT')
891 OR has_table_privilege(c.oid, 'INSERT')
892 OR has_table_privilege(c.oid, 'UPDATE')
893 OR has_table_privilege(c.oid, 'DELETE')
894 OR has_table_privilege(c.oid, 'RULE')
895 OR has_table_privilege(c.oid, 'RERERENCES')
896 OR has_table_privilege(c.oid, 'TRIGGER') );
898 GRANT SELECT ON views TO PUBLIC;