44 *
55 * Copyright 2003, PostgreSQL Global Development Group
66 *
7- * $Id: information_schema.sql,v 1.13 2003/10/16 23:46:17 petere Exp $
7+ * $Id: information_schema.sql,v 1.14 2003/10/18 12:53:35 petere Exp $
88*/
99
1010/*
@@ -102,7 +102,8 @@ CREATE VIEW check_constraints AS
102102SELECT CAST(current_database()AS sql_identifier)AS constraint_catalog,
103103 CAST(rs .nspname AS sql_identifier)AS constraint_schema,
104104 CAST(con .conname AS sql_identifier)AS constraint_name,
105- CAST(con .consrc AS character_data)AS check_clause
105+ CAST(substring (pg_get_constraintdef(con .oid )from 7 )AS character_data)
106+ AS check_clause
106107FROM pg_namespace rs,
107108 pg_constraint con
108109LEFT OUTER JOIN pg_class cON (c .oid = con .conrelid )
@@ -172,12 +173,12 @@ CREATE VIEW column_privileges AS
172173 pg_user u_grantor,
173174 (
174175SELECT usesysid,0 , usenameFROM pg_user
175- UNION
176+ UNION ALL
176177SELECT 0 , grosysid, gronameFROM pg_group
177- UNION
178+ UNION ALL
178179SELECT 0 ,0 ,' PUBLIC'
179180 )AS grantee (usesysid, grosysid, name),
180- (SELECT ' SELECT' UNION SELECT ' INSERT' UNION SELECT ' UPDATE' UNION SELECT ' REFERENCES' AS pr (type)
181+ (SELECT ' SELECT' UNIONALL SELECT ' INSERT' UNIONALL SELECT ' UPDATE' UNION ALL SELECT ' REFERENCES' AS pr (type)
181182
182183WHERE a .attrelid = c .oid
183184AND c .relnamespace = nc .oid
@@ -403,29 +404,29 @@ CREATE VIEW constraint_column_usage AS
403404FROM pg_namespace nr, pg_class r, pg_attribute a, pg_depend d, pg_namespace nc, pg_constraint c
404405WHERE nr .oid = r .relnamespace
405406AND r .oid = a .attrelid
406- AND d .refclassid = ( SELECT oid FROM pg_class WHERE relname = ' pg_class' )
407+ AND d .refclassid = ' pg_catalog. pg_class' ::regclass
407408AND d .refobjid = r .oid
408409AND d .refobjsubid = a .attnum
409- AND d .classid = ( SELECT oid FROM pg_class WHERE relname = ' pg_constraint' )
410+ AND d .classid = ' pg_catalog. pg_constraint' ::regclass
410411AND d .objid = c .oid
411412AND c .connamespace = nc .oid
412413AND c .contype = ' c'
413414AND r .relkind = ' r'
414415AND a .attnum > 0
415416AND NOTa .attisdropped
416417
417- UNION
418+ UNION ALL
418419
419420/* unique/primary key/foreign key constraints*/
420421SELECT nr .nspname ,r .relname ,r .relowner ,a .attname ,nc .nspname ,c .conname
421422FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc, pg_constraint c,
422- (select 1 union select 2 union select 3 union select 4 union select 5 union
423- select 6 union select 7 union select 8 union select 9 union select 10 union
424- select 11 union select 12 union select 13 union select 14 union select 15 union
425- select 16 union select 17 union select 18 union select 19 union select 20 union
426- select 21 union select 22 union select 23 union select 24 union select 25 union
427- select 26 union select 27 union select 28 union select 29 union select 30 union
428- select 31 union select 32 )AS pos(n)
423+ (select 1 unionall select 2 unionall select 3 unionall select 4 unionall select 5 union all
424+ select 6 unionall select 7 unionall select 8 unionall select 9 unionall select 10 union all
425+ select 11 unionall select 12 unionall select 13 unionall select 14 unionall select 15 union all
426+ select 16 unionall select 17 unionall select 18 unionall select 19 unionall select 20 union all
427+ select 21 unionall select 22 unionall select 23 unionall select 24 unionall select 25 union all
428+ select 26 unionall select 27 unionall select 28 unionall select 29 unionall select 30 union all
429+ select 31 unionall select 32 )AS pos(n)
429430WHERE nr .oid = r .relnamespace
430431AND r .oid = a .attrelid
431432AND r .oid = c .conrelid
@@ -660,13 +661,13 @@ CREATE VIEW key_column_usage AS
660661
661662FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc, pg_constraint c,
662663 pg_user u,
663- (select 1 union select 2 union select 3 union select 4 union select 5 union
664- select 6 union select 7 union select 8 union select 9 union select 10 union
665- select 11 union select 12 union select 13 union select 14 union select 15 union
666- select 16 union select 17 union select 18 union select 19 union select 20 union
667- select 21 union select 22 union select 23 union select 24 union select 25 union
668- select 26 union select 27 union select 28 union select 29 union select 30 union
669- select 31 union select 32 )AS pos(n)
664+ (select 1 unionall select 2 unionall select 3 unionall select 4 unionall select 5 union all
665+ select 6 unionall select 7 unionall select 8 unionall select 9 unionall select 10 union all
666+ select 11 unionall select 12 unionall select 13 unionall select 14 unionall select 15 union all
667+ select 16 unionall select 17 unionall select 18 unionall select 19 unionall select 20 union all
668+ select 21 unionall select 22 unionall select 23 unionall select 24 unionall select 25 union all
669+ select 26 unionall select 27 unionall select 28 unionall select 29 unionall select 30 union all
670+ select 31 unionall select 32 )AS pos(n)
670671
671672WHERE nr .oid = r .relnamespace
672673AND r .oid = a .attrelid
@@ -726,13 +727,13 @@ CREATE VIEW parameters AS
726727 CAST(n+ 1 AS sql_identifier)AS dtd_identifier
727728
728729FROM pg_namespace n, pg_proc p, pg_type t, pg_namespace nt, pg_user u,
729- (select 0 union select 1 union select 2 union select 3 union select 4 union
730- select 5 union select 6 union select 7 union select 8 union select 9 union
731- select 10 union select 11 union select 12 union select 13 union select 14 union
732- select 15 union select 16 union select 17 union select 18 union select 19 union
733- select 20 union select 21 union select 22 union select 23 union select 24 union
734- select 25 union select 26 union select 27 union select 28 union select 29 union
735- select 30 union select 31 )AS pos(n)
730+ (select 0 unionall select 1 unionall select 2 unionall select 3 unionall select 4 union all
731+ select 5 unionall select 6 unionall select 7 unionall select 8 unionall select 9 union all
732+ select 10 unionall select 11 unionall select 12 unionall select 13 unionall select 14 union all
733+ select 15 unionall select 16 unionall select 17 unionall select 18 unionall select 19 union all
734+ select 20 unionall select 21 unionall select 22 unionall select 23 unionall select 24 union all
735+ select 25 unionall select 26 unionall select 27 unionall select 28 unionall select 29 union all
736+ select 30 unionall select 31 )AS pos(n)
736737
737738WHERE n .oid = p .pronamespace AND p .pronargs > pos .n
738739AND p .proargtypes [n]= t .oid AND t .typnamespace = nt .oid
@@ -751,13 +752,13 @@ CREATE FUNCTION _pg_keyissubset(smallint[], smallint[]) RETURNS boolean
751752 LANGUAGE sql
752753 IMMUTABLE
753754 RETURNSNULL ON NULL INPUT
754- AS ' select $1[1] is null or ($1[1] = any ($2) and coalesce(_pg_keyissubset($1[2:array_upper($1,1)], $2), true))'
755+ AS ' select $1[1] is null or ($1[1] = any ($2) and coalesce(information_schema. _pg_keyissubset($1[2:array_upper($1,1)], $2), true))'
755756
756757CREATE FUNCTION _pg_keysequal (smallint [],smallint []) RETURNSboolean
757758 LANGUAGE sql
758759 IMMUTABLE
759760 RETURNSNULL ON NULL INPUT
760- AS ' select _pg_keyissubset($1, $2) and _pg_keyissubset($2, $1)'
761+ AS ' selectinformation_schema. _pg_keyissubset($1, $2) andinformation_schema. _pg_keyissubset($2, $1)'
761762
762763CREATE VIEW referential_constraints AS
763764SELECT CAST(current_database()AS sql_identifier)AS constraint_catalog,
@@ -830,7 +831,7 @@ CREATE VIEW role_column_grants AS
830831 pg_namespace nc,
831832 pg_user u_grantor,
832833 pg_group g_grantee,
833- (SELECT ' SELECT' UNION SELECT ' INSERT' UNION SELECT ' UPDATE' UNION SELECT ' REFERENCES' AS pr (type)
834+ (SELECT ' SELECT' UNIONALL SELECT ' INSERT' UNIONALL SELECT ' UPDATE' UNION ALL SELECT ' REFERENCES' AS pr (type)
834835
835836WHERE a .attrelid = c .oid
836837AND c .relnamespace = nc .oid
@@ -899,8 +900,8 @@ CREATE VIEW role_table_grants AS
899900 pg_namespace nc,
900901 pg_user u_grantor,
901902 pg_group g_grantee,
902- (SELECT ' SELECT' UNION SELECT ' DELETE' UNION SELECT ' INSERT' UNION SELECT ' UPDATE'
903- UNION SELECT ' REFERENCES' UNION SELECT ' RULE' UNION SELECT ' TRIGGER' AS pr (type)
903+ (SELECT ' SELECT' UNIONALL SELECT ' DELETE' UNIONALL SELECT ' INSERT' UNION ALL SELECT ' UPDATE'
904+ UNIONALL SELECT ' REFERENCES' UNIONALL SELECT ' RULE' UNION ALL SELECT ' TRIGGER' AS pr (type)
904905
905906WHERE c .relnamespace = nc .oid
906907AND c .relkind IN (' r' ' v'
@@ -958,9 +959,9 @@ CREATE VIEW routine_privileges AS
958959 pg_user u_grantor,
959960 (
960961SELECT usesysid,0 , usenameFROM pg_user
961- UNION
962+ UNION ALL
962963SELECT 0 , grosysid, gronameFROM pg_group
963- UNION
964+ UNION ALL
964965SELECT 0 ,0 ,' PUBLIC'
965966 )AS grantee (usesysid, grosysid, name)
966967
@@ -1118,7 +1119,7 @@ INSERT INTO sql_implementation_info VALUES ('23', 'CURSOR COMMIT BEHAVIOR', 1
11181119INSERT INTO sql_implementation_infoVALUES (' 2' ' DATA SOURCE NAME' NULL ,' ' NULL );
11191120INSERT INTO sql_implementation_infoVALUES (' 17' ' DBMS NAME' NULL , (select trim (trailing' ' from substring (version()from ' ^[^0-9]*' NULL );
11201121INSERT INTO sql_implementation_infoVALUES (' 18' ' DBMS VERSION' NULL ,' ???' NULL );-- filled by initdb
1121- INSERT INTO sql_implementation_infoVALUES (' 26' ' DEFAULT TRANSACTION ISOLATION' 2 ,NULL ,' READCOMMITED ; user-settable'
1122+ INSERT INTO sql_implementation_infoVALUES (' 26' ' DEFAULT TRANSACTION ISOLATION' 2 ,NULL ,' READCOMMITTED ; user-settable'
11221123INSERT INTO sql_implementation_infoVALUES (' 28' ' IDENTIFIER CASE' 3 ,NULL ,' stored in mixed case - case sensitive'
11231124INSERT INTO sql_implementation_infoVALUES (' 85' ' NULL COLLATION' 0 ,NULL ,' nulls higher than non-nulls'
11241125INSERT INTO sql_implementation_infoVALUES (' 13' ' SERVER NAME' NULL ,' ' NULL );
@@ -1194,7 +1195,7 @@ INSERT INTO sql_sizing VALUES (34, 'MAXIMUM CATALOG NAME LENGTH', 63, NULL);
11941195INSERT INTO sql_sizingVALUES (30 ,' MAXIMUM COLUMN NAME LENGTH' 63 ,NULL );
11951196INSERT INTO sql_sizingVALUES (97 ,' MAXIMUM COLUMNS IN GROUP BY' 0 ,NULL );
11961197INSERT INTO sql_sizingVALUES (99 ,' MAXIMUM COLUMNS IN ORDER BY' 0 ,NULL );
1197- INSERT INTO sql_sizingVALUES (100 ,' MAXIMUM COLUMNS IN SELECT' 0 ,NULL );
1198+ INSERT INTO sql_sizingVALUES (100 ,' MAXIMUM COLUMNS IN SELECT' 1664 ,NULL );-- match MaxTupleAttributeNumber
11981199INSERT INTO sql_sizingVALUES (101 ,' MAXIMUM COLUMNS IN TABLE' 1600 ,NULL );-- match MaxHeapAttributeNumber
11991200INSERT INTO sql_sizingVALUES (1 ,' MAXIMUM CONCURRENT ACTIVITIES' 0 ,NULL );
12001201INSERT INTO sql_sizingVALUES (31 ,' MAXIMUM CURSOR NAME LENGTH' 63 ,NULL );
@@ -1304,13 +1305,13 @@ CREATE VIEW table_privileges AS
13041305 pg_user u_grantor,
13051306 (
13061307SELECT usesysid,0 , usenameFROM pg_user
1307- UNION
1308+ UNION ALL
13081309SELECT 0 , grosysid, gronameFROM pg_group
1309- UNION
1310+ UNION ALL
13101311SELECT 0 ,0 ,' PUBLIC'
13111312 )AS grantee (usesysid, grosysid, name),
1312- (SELECT ' SELECT' UNION SELECT ' DELETE' UNION SELECT ' INSERT' UNION SELECT ' UPDATE'
1313- UNION SELECT ' REFERENCES' UNION SELECT ' RULE' UNION SELECT ' TRIGGER' AS pr (type)
1313+ (SELECT ' SELECT' UNIONALL SELECT ' DELETE' UNIONALL SELECT ' INSERT' UNION ALL SELECT ' UPDATE'
1314+ UNIONALL SELECT ' REFERENCES' UNIONALL SELECT ' RULE' UNION ALL SELECT ' TRIGGER' AS pr (type)
13141315
13151316WHERE c .relnamespace = nc .oid
13161317AND c .relkind IN (' r' ' v'
@@ -1413,7 +1414,7 @@ CREATE VIEW triggers AS
14131414 CAST(null AS sql_identifier)AS condition_reference_new_table
14141415
14151416FROM pg_namespace n, pg_class c, pg_trigger t, pg_user u,
1416- (SELECT 4 ,' INSERT' UNION SELECT 8 ,' DELETE' UNION SELECT 16 ,' UPDATE' AS em (num,text )
1417+ (SELECT 4 ,' INSERT' UNIONALL SELECT 8 ,' DELETE' UNION ALL SELECT 16 ,' UPDATE' AS em (num,text )
14171418
14181419WHERE n .oid = c .relnamespace
14191420AND c .oid = t .tgrelid
@@ -1477,13 +1478,13 @@ CREATE VIEW view_column_usage AS
14771478WHERE nv .oid = v .relnamespace
14781479AND v .relkind = ' v'
14791480AND v .oid = dv .refobjid
1480- AND dv .refclassid = ( SELECT oid FROM pg_class WHERE relname = ' pg_class' )
1481- AND dv .classid = ( SELECT oid FROM pg_class WHERE relname = ' pg_rewrite' )
1481+ AND dv .refclassid = ' pg_catalog. pg_class' ::regclass
1482+ AND dv .classid = ' pg_catalog. pg_rewrite' ::regclass
14821483AND dv .deptype = ' i'
14831484AND dv .objid = dt .objid
14841485AND dv .refobjid <> dt .refobjid
1485- AND dt .classid = ( SELECT oid FROM pg_class WHERE relname = ' pg_rewrite' )
1486- AND dt .refclassid = ( SELECT oid FROM pg_class WHERE relname = ' pg_class' )
1486+ AND dt .classid = ' pg_catalog. pg_rewrite' ::regclass
1487+ AND dt .refclassid = ' pg_catalog. pg_class' ::regclass
14871488AND dt .refobjid = t .oid
14881489AND t .relnamespace = nt .oid
14891490AND t .relkind IN (' r' ' v'
@@ -1515,13 +1516,13 @@ CREATE VIEW view_table_usage AS
15151516WHERE nv .oid = v .relnamespace
15161517AND v .relkind = ' v'
15171518AND v .oid = dv .refobjid
1518- AND dv .refclassid = ( SELECT oid FROM pg_class WHERE relname = ' pg_class' )
1519- AND dv .classid = ( SELECT oid FROM pg_class WHERE relname = ' pg_rewrite' )
1519+ AND dv .refclassid = ' pg_catalog. pg_class' ::regclass
1520+ AND dv .classid = ' pg_catalog. pg_rewrite' ::regclass
15201521AND dv .deptype = ' i'
15211522AND dv .objid = dt .objid
15221523AND dv .refobjid <> dt .refobjid
1523- AND dt .classid = ( SELECT oid FROM pg_class WHERE relname = ' pg_rewrite' )
1524- AND dt .refclassid = ( SELECT oid FROM pg_class WHERE relname = ' pg_class' )
1524+ AND dt .classid = ' pg_catalog. pg_rewrite' ::regclass
1525+ AND dt .refclassid = ' pg_catalog. pg_class' ::regclass
15251526AND dt .refobjid = t .oid
15261527AND t .relnamespace = nt .oid
15271528AND t .relkind IN (' r' ' v'
@@ -1582,11 +1583,11 @@ CREATE VIEW data_type_privileges AS
15821583FROM
15831584 (
15841585SELECT table_schema, table_name,' TABLE' text , dtd_identifierFROM columns
1585- UNION
1586+ UNION ALL
15861587SELECT domain_schema, domain_name,' DOMAIN' text , dtd_identifierFROM domains
1587- UNION
1588+ UNION ALL
15881589SELECT specific_schema, specific_name,' ROUTINE' text , dtd_identifierFROM parameters
1589- UNION
1590+ UNION ALL
15901591SELECT specific_schema, specific_name,' ROUTINE' text , dtd_identifierFROM routines
15911592 )AS x (objschema, objname, objtype, objdtdid);
15921593
@@ -1645,28 +1646,28 @@ CREATE VIEW element_types AS
16451646AND c .relkind IN (' r' ' v'
16461647AND attnum> 0 AND NOT attisdropped
16471648
1648- UNION
1649+ UNION ALL
16491650
16501651/* domains*/
16511652SELECT t .typnamespace ,t .typname ,' DOMAIN' text ,1 ,t .typbasetype
16521653FROM pg_type t
16531654WHERE t .typtype = ' d'
16541655
1655- UNION
1656+ UNION ALL
16561657
16571658/* parameters*/
16581659SELECT p .pronamespace ,p .proname ,' ROUTINE' text ,pos .n + 1 ,p .proargtypes [n]
16591660FROM pg_proc p,
1660- (select 0 union select 1 union select 2 union select 3 union select 4 union
1661- select 5 union select 6 union select 7 union select 8 union select 9 union
1662- select 10 union select 11 union select 12 union select 13 union select 14 union
1663- select 15 union select 16 union select 17 union select 18 union select 19 union
1664- select 20 union select 21 union select 22 union select 23 union select 24 union
1665- select 25 union select 26 union select 27 union select 28 union select 29 union
1666- select 30 union select 31 )AS pos(n)
1661+ (select 0 unionall select 1 unionall select 2 unionall select 3 unionall select 4 union all
1662+ select 5 unionall select 6 unionall select 7 unionall select 8 unionall select 9 union all
1663+ select 10 unionall select 11 unionall select 12 unionall select 13 unionall select 14 union all
1664+ select 15 unionall select 16 unionall select 17 unionall select 18 unionall select 19 union all
1665+ select 20 unionall select 21 unionall select 22 unionall select 23 unionall select 24 union all
1666+ select 25 unionall select 26 unionall select 27 unionall select 28 unionall select 29 union all
1667+ select 30 unionall select 31 )AS pos(n)
16671668WHERE p .pronargs > pos .n
16681669
1669- UNION
1670+ UNION ALL
16701671
16711672/* result types*/
16721673SELECT p .pronamespace ,p .proname ,' ROUTINE' text ,0 ,p .prorettype
@@ -1680,7 +1681,7 @@ CREATE VIEW element_types AS
16801681AND at .typelem = bt .oid
16811682AND nbt .oid = bt .typnamespace
16821683
1683- AND (n .nspname ,x .objname ,x .objtype ,x .objtypeid )IN
1684+ AND (n .nspname ,x .objname ,x .objtype ,x .objdtdid )IN
16841685 (SELECT object_schema, object_name, object_type, dtd_identifier
16851686FROM data_type_privileges );
16861687