82
82
-- As of 9.1, type citext should be marked collatable. There is no ALTER TYPE
83
83
-- command for this, so we have to do it by poking the pg_type entry directly.
84
-- We have to poke any derived copies in pg_attribute or pg_index as well,
85
-- as well as those for arrays/domains based directly or indirectly on citext.
84
86
-- Notes: 100 is the OID of the "pg_catalog.default" collation --- it seems
85
87
-- easier and more reliable to hard-wire that here than to pull it out of
86
88
-- pg_collation. Also, we don't need to make pg_depend entries since the
87
89
-- default collation is pinned.
92
WITH RECURSIVE typeoids(typoid) AS
93
( SELECT 'citext'::pg_catalog.regtype UNION
94
SELECT oid FROM pg_catalog.pg_type, typeoids
95
WHERE typelem = typoid OR typbasetype = typoid )
90
96
UPDATE pg_catalog.pg_type SET typcollation = 100
91
WHERE oid = 'citext'::pg_catalog.regtype;
98
WHERE oid = typeoids.typoid;
100
WITH RECURSIVE typeoids(typoid) AS
101
( SELECT 'citext'::pg_catalog.regtype UNION
102
SELECT oid FROM pg_catalog.pg_type, typeoids
103
WHERE typelem = typoid OR typbasetype = typoid )
93
104
UPDATE pg_catalog.pg_attribute SET attcollation = 100
94
WHERE atttypid = 'citext'::pg_catalog.regtype;
106
WHERE atttypid = typeoids.typoid;
96
108
UPDATE pg_catalog.pg_index SET indcollation[0] = 100
97
WHERE indclass[0] IN (SELECT oid FROM pg_catalog.pg_opclass
98
WHERE opcintype = 'citext'::pg_catalog.regtype);
109
WHERE indclass[0] IN (
110
WITH RECURSIVE typeoids(typoid) AS
111
( SELECT 'citext'::pg_catalog.regtype UNION
112
SELECT oid FROM pg_catalog.pg_type, typeoids
113
WHERE typelem = typoid OR typbasetype = typoid )
114
SELECT oid FROM pg_catalog.pg_opclass, typeoids
115
WHERE opcintype = typeoids.typoid
100
118
UPDATE pg_catalog.pg_index SET indcollation[1] = 100
101
WHERE indclass[1] IN (SELECT oid FROM pg_catalog.pg_opclass
102
WHERE opcintype = 'citext'::pg_catalog.regtype);
119
WHERE indclass[1] IN (
120
WITH RECURSIVE typeoids(typoid) AS
121
( SELECT 'citext'::pg_catalog.regtype UNION
122
SELECT oid FROM pg_catalog.pg_type, typeoids
123
WHERE typelem = typoid OR typbasetype = typoid )
124
SELECT oid FROM pg_catalog.pg_opclass, typeoids
125
WHERE opcintype = typeoids.typoid
104
128
UPDATE pg_catalog.pg_index SET indcollation[2] = 100
105
WHERE indclass[2] IN (SELECT oid FROM pg_catalog.pg_opclass
106
WHERE opcintype = 'citext'::pg_catalog.regtype);
129
WHERE indclass[2] IN (
130
WITH RECURSIVE typeoids(typoid) AS
131
( SELECT 'citext'::pg_catalog.regtype UNION
132
SELECT oid FROM pg_catalog.pg_type, typeoids
133
WHERE typelem = typoid OR typbasetype = typoid )
134
SELECT oid FROM pg_catalog.pg_opclass, typeoids
135
WHERE opcintype = typeoids.typoid
108
138
UPDATE pg_catalog.pg_index SET indcollation[3] = 100
109
WHERE indclass[3] IN (SELECT oid FROM pg_catalog.pg_opclass
110
WHERE opcintype = 'citext'::pg_catalog.regtype);
139
WHERE indclass[3] IN (
140
WITH RECURSIVE typeoids(typoid) AS
141
( SELECT 'citext'::pg_catalog.regtype UNION
142
SELECT oid FROM pg_catalog.pg_type, typeoids
143
WHERE typelem = typoid OR typbasetype = typoid )
144
SELECT oid FROM pg_catalog.pg_opclass, typeoids
145
WHERE opcintype = typeoids.typoid
112
148
UPDATE pg_catalog.pg_index SET indcollation[4] = 100
113
WHERE indclass[4] IN (SELECT oid FROM pg_catalog.pg_opclass
114
WHERE opcintype = 'citext'::pg_catalog.regtype);
149
WHERE indclass[4] IN (
150
WITH RECURSIVE typeoids(typoid) AS
151
( SELECT 'citext'::pg_catalog.regtype UNION
152
SELECT oid FROM pg_catalog.pg_type, typeoids
153
WHERE typelem = typoid OR typbasetype = typoid )
154
SELECT oid FROM pg_catalog.pg_opclass, typeoids
155
WHERE opcintype = typeoids.typoid
116
158
UPDATE pg_catalog.pg_index SET indcollation[5] = 100
117
WHERE indclass[5] IN (SELECT oid FROM pg_catalog.pg_opclass
118
WHERE opcintype = 'citext'::pg_catalog.regtype);
159
WHERE indclass[5] IN (
160
WITH RECURSIVE typeoids(typoid) AS
161
( SELECT 'citext'::pg_catalog.regtype UNION
162
SELECT oid FROM pg_catalog.pg_type, typeoids
163
WHERE typelem = typoid OR typbasetype = typoid )
164
SELECT oid FROM pg_catalog.pg_opclass, typeoids
165
WHERE opcintype = typeoids.typoid
120
168
UPDATE pg_catalog.pg_index SET indcollation[6] = 100
121
WHERE indclass[6] IN (SELECT oid FROM pg_catalog.pg_opclass
122
WHERE opcintype = 'citext'::pg_catalog.regtype);
169
WHERE indclass[6] IN (
170
WITH RECURSIVE typeoids(typoid) AS
171
( SELECT 'citext'::pg_catalog.regtype UNION
172
SELECT oid FROM pg_catalog.pg_type, typeoids
173
WHERE typelem = typoid OR typbasetype = typoid )
174
SELECT oid FROM pg_catalog.pg_opclass, typeoids
175
WHERE opcintype = typeoids.typoid
124
178
UPDATE pg_catalog.pg_index SET indcollation[7] = 100
125
WHERE indclass[7] IN (SELECT oid FROM pg_catalog.pg_opclass
126
WHERE opcintype = 'citext'::pg_catalog.regtype);
179
WHERE indclass[7] IN (
180
WITH RECURSIVE typeoids(typoid) AS
181
( SELECT 'citext'::pg_catalog.regtype UNION
182
SELECT oid FROM pg_catalog.pg_type, typeoids
183
WHERE typelem = typoid OR typbasetype = typoid )
184
SELECT oid FROM pg_catalog.pg_opclass, typeoids
185
WHERE opcintype = typeoids.typoid
128
188
-- somewhat arbitrarily, we assume no citext indexes have more than 8 columns