1/* contrib/citext/citext--unpackaged--1.0.sql */ 2 3-- complain if script is sourced in psql, rather than via CREATE EXTENSION 4\echo Use "CREATE EXTENSION citext FROM unpackaged" to load this file. \quit 5 6ALTER EXTENSION citext ADD type citext; 7ALTER EXTENSION citext ADD function citextin(cstring); 8ALTER EXTENSION citext ADD function citextout(citext); 9ALTER EXTENSION citext ADD function citextrecv(internal); 10ALTER EXTENSION citext ADD function citextsend(citext); 11ALTER EXTENSION citext ADD function citext(character); 12ALTER EXTENSION citext ADD function citext(boolean); 13ALTER EXTENSION citext ADD function citext(inet); 14ALTER EXTENSION citext ADD cast (citext as text); 15ALTER EXTENSION citext ADD cast (citext as character varying); 16ALTER EXTENSION citext ADD cast (citext as character); 17ALTER EXTENSION citext ADD cast (text as citext); 18ALTER EXTENSION citext ADD cast (character varying as citext); 19ALTER EXTENSION citext ADD cast (character as citext); 20ALTER EXTENSION citext ADD cast (boolean as citext); 21ALTER EXTENSION citext ADD cast (inet as citext); 22ALTER EXTENSION citext ADD function citext_eq(citext,citext); 23ALTER EXTENSION citext ADD function citext_ne(citext,citext); 24ALTER EXTENSION citext ADD function citext_lt(citext,citext); 25ALTER EXTENSION citext ADD function citext_le(citext,citext); 26ALTER EXTENSION citext ADD function citext_gt(citext,citext); 27ALTER EXTENSION citext ADD function citext_ge(citext,citext); 28ALTER EXTENSION citext ADD operator <>(citext,citext); 29ALTER EXTENSION citext ADD operator =(citext,citext); 30ALTER EXTENSION citext ADD operator >(citext,citext); 31ALTER EXTENSION citext ADD operator >=(citext,citext); 32ALTER EXTENSION citext ADD operator <(citext,citext); 33ALTER EXTENSION citext ADD operator <=(citext,citext); 34ALTER EXTENSION citext ADD function citext_cmp(citext,citext); 35ALTER EXTENSION citext ADD function citext_hash(citext); 36ALTER EXTENSION citext ADD operator family citext_ops using btree; 37ALTER EXTENSION citext ADD operator class citext_ops using btree; 38ALTER EXTENSION citext ADD operator family citext_ops using hash; 39ALTER EXTENSION citext ADD operator class citext_ops using hash; 40ALTER EXTENSION citext ADD function citext_smaller(citext,citext); 41ALTER EXTENSION citext ADD function citext_larger(citext,citext); 42ALTER EXTENSION citext ADD function min(citext); 43ALTER EXTENSION citext ADD function max(citext); 44ALTER EXTENSION citext ADD function texticlike(citext,citext); 45ALTER EXTENSION citext ADD function texticnlike(citext,citext); 46ALTER EXTENSION citext ADD function texticregexeq(citext,citext); 47ALTER EXTENSION citext ADD function texticregexne(citext,citext); 48ALTER EXTENSION citext ADD operator !~(citext,citext); 49ALTER EXTENSION citext ADD operator ~(citext,citext); 50ALTER EXTENSION citext ADD operator !~*(citext,citext); 51ALTER EXTENSION citext ADD operator ~*(citext,citext); 52ALTER EXTENSION citext ADD operator !~~(citext,citext); 53ALTER EXTENSION citext ADD operator ~~(citext,citext); 54ALTER EXTENSION citext ADD operator !~~*(citext,citext); 55ALTER EXTENSION citext ADD operator ~~*(citext,citext); 56ALTER EXTENSION citext ADD function texticlike(citext,text); 57ALTER EXTENSION citext ADD function texticnlike(citext,text); 58ALTER EXTENSION citext ADD function texticregexeq(citext,text); 59ALTER EXTENSION citext ADD function texticregexne(citext,text); 60ALTER EXTENSION citext ADD operator !~(citext,text); 61ALTER EXTENSION citext ADD operator ~(citext,text); 62ALTER EXTENSION citext ADD operator !~*(citext,text); 63ALTER EXTENSION citext ADD operator ~*(citext,text); 64ALTER EXTENSION citext ADD operator !~~(citext,text); 65ALTER EXTENSION citext ADD operator ~~(citext,text); 66ALTER EXTENSION citext ADD operator !~~*(citext,text); 67ALTER EXTENSION citext ADD operator ~~*(citext,text); 68ALTER EXTENSION citext ADD function regexp_matches(citext,citext); 69ALTER EXTENSION citext ADD function regexp_matches(citext,citext,text); 70ALTER EXTENSION citext ADD function regexp_replace(citext,citext,text); 71ALTER EXTENSION citext ADD function regexp_replace(citext,citext,text,text); 72ALTER EXTENSION citext ADD function regexp_split_to_array(citext,citext); 73ALTER EXTENSION citext ADD function regexp_split_to_array(citext,citext,text); 74ALTER EXTENSION citext ADD function regexp_split_to_table(citext,citext); 75ALTER EXTENSION citext ADD function regexp_split_to_table(citext,citext,text); 76ALTER EXTENSION citext ADD function strpos(citext,citext); 77ALTER EXTENSION citext ADD function replace(citext,citext,citext); 78ALTER EXTENSION citext ADD function split_part(citext,citext,integer); 79ALTER EXTENSION citext ADD function translate(citext,citext,text); 80 81-- 82-- As of 9.1, type citext should be marked collatable. There is no ALTER TYPE 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. 86-- Notes: 100 is the OID of the "pg_catalog.default" collation --- it seems 87-- easier and more reliable to hard-wire that here than to pull it out of 88-- pg_collation. Also, we don't need to make pg_depend entries since the 89-- default collation is pinned. 90-- 91 92DO LANGUAGE plpgsql 93$$ 94DECLARE 95 my_schema pg_catalog.text := pg_catalog.quote_ident(pg_catalog.current_schema()); 96 old_path pg_catalog.text := pg_catalog.current_setting('search_path'); 97BEGIN 98-- for safety, transiently set search_path to just pg_catalog+pg_temp 99PERFORM pg_catalog.set_config('search_path', 'pg_catalog, pg_temp', true); 100 101WITH RECURSIVE typeoids(typoid) AS 102 ( SELECT (my_schema || '.citext')::pg_catalog.regtype UNION 103 SELECT oid FROM pg_catalog.pg_type, typeoids 104 WHERE typelem = typoid OR typbasetype = typoid ) 105UPDATE pg_catalog.pg_type SET typcollation = 100 106FROM typeoids 107WHERE oid = typeoids.typoid; 108 109WITH RECURSIVE typeoids(typoid) AS 110 ( SELECT (my_schema || '.citext')::pg_catalog.regtype UNION 111 SELECT oid FROM pg_catalog.pg_type, typeoids 112 WHERE typelem = typoid OR typbasetype = typoid ) 113UPDATE pg_catalog.pg_attribute SET attcollation = 100 114FROM typeoids 115WHERE atttypid = typeoids.typoid; 116 117-- Updating the index indcollations is particularly tedious, but since we 118-- don't currently allow SQL assignment to individual elements of oidvectors, 119-- there's little choice. 120 121UPDATE pg_catalog.pg_index SET indcollation = 122 pg_catalog.regexp_replace(indcollation::pg_catalog.text, '^0', '100')::pg_catalog.oidvector 123WHERE indclass[0] IN ( 124 WITH RECURSIVE typeoids(typoid) AS 125 ( SELECT (my_schema || '.citext')::pg_catalog.regtype UNION 126 SELECT oid FROM pg_catalog.pg_type, typeoids 127 WHERE typelem = typoid OR typbasetype = typoid ) 128 SELECT oid FROM pg_catalog.pg_opclass, typeoids 129 WHERE opcintype = typeoids.typoid 130); 131 132UPDATE pg_catalog.pg_index SET indcollation = 133 pg_catalog.regexp_replace(indcollation::pg_catalog.text, E'^(\\d+) 0', E'\\1 100')::pg_catalog.oidvector 134WHERE indclass[1] IN ( 135 WITH RECURSIVE typeoids(typoid) AS 136 ( SELECT (my_schema || '.citext')::pg_catalog.regtype UNION 137 SELECT oid FROM pg_catalog.pg_type, typeoids 138 WHERE typelem = typoid OR typbasetype = typoid ) 139 SELECT oid FROM pg_catalog.pg_opclass, typeoids 140 WHERE opcintype = typeoids.typoid 141); 142 143UPDATE pg_catalog.pg_index SET indcollation = 144 pg_catalog.regexp_replace(indcollation::pg_catalog.text, E'^(\\d+ \\d+) 0', E'\\1 100')::pg_catalog.oidvector 145WHERE indclass[2] IN ( 146 WITH RECURSIVE typeoids(typoid) AS 147 ( SELECT (my_schema || '.citext')::pg_catalog.regtype UNION 148 SELECT oid FROM pg_catalog.pg_type, typeoids 149 WHERE typelem = typoid OR typbasetype = typoid ) 150 SELECT oid FROM pg_catalog.pg_opclass, typeoids 151 WHERE opcintype = typeoids.typoid 152); 153 154UPDATE pg_catalog.pg_index SET indcollation = 155 pg_catalog.regexp_replace(indcollation::pg_catalog.text, E'^(\\d+ \\d+ \\d+) 0', E'\\1 100')::pg_catalog.oidvector 156WHERE indclass[3] IN ( 157 WITH RECURSIVE typeoids(typoid) AS 158 ( SELECT (my_schema || '.citext')::pg_catalog.regtype UNION 159 SELECT oid FROM pg_catalog.pg_type, typeoids 160 WHERE typelem = typoid OR typbasetype = typoid ) 161 SELECT oid FROM pg_catalog.pg_opclass, typeoids 162 WHERE opcintype = typeoids.typoid 163); 164 165UPDATE pg_catalog.pg_index SET indcollation = 166 pg_catalog.regexp_replace(indcollation::pg_catalog.text, E'^(\\d+ \\d+ \\d+ \\d+) 0', E'\\1 100')::pg_catalog.oidvector 167WHERE indclass[4] IN ( 168 WITH RECURSIVE typeoids(typoid) AS 169 ( SELECT (my_schema || '.citext')::pg_catalog.regtype UNION 170 SELECT oid FROM pg_catalog.pg_type, typeoids 171 WHERE typelem = typoid OR typbasetype = typoid ) 172 SELECT oid FROM pg_catalog.pg_opclass, typeoids 173 WHERE opcintype = typeoids.typoid 174); 175 176UPDATE pg_catalog.pg_index SET indcollation = 177 pg_catalog.regexp_replace(indcollation::pg_catalog.text, E'^(\\d+ \\d+ \\d+ \\d+ \\d+) 0', E'\\1 100')::pg_catalog.oidvector 178WHERE indclass[5] IN ( 179 WITH RECURSIVE typeoids(typoid) AS 180 ( SELECT (my_schema || '.citext')::pg_catalog.regtype UNION 181 SELECT oid FROM pg_catalog.pg_type, typeoids 182 WHERE typelem = typoid OR typbasetype = typoid ) 183 SELECT oid FROM pg_catalog.pg_opclass, typeoids 184 WHERE opcintype = typeoids.typoid 185); 186 187UPDATE pg_catalog.pg_index SET indcollation = 188 pg_catalog.regexp_replace(indcollation::pg_catalog.text, E'^(\\d+ \\d+ \\d+ \\d+ \\d+ \\d+) 0', E'\\1 100')::pg_catalog.oidvector 189WHERE indclass[6] IN ( 190 WITH RECURSIVE typeoids(typoid) AS 191 ( SELECT (my_schema || '.citext')::pg_catalog.regtype UNION 192 SELECT oid FROM pg_catalog.pg_type, typeoids 193 WHERE typelem = typoid OR typbasetype = typoid ) 194 SELECT oid FROM pg_catalog.pg_opclass, typeoids 195 WHERE opcintype = typeoids.typoid 196); 197 198UPDATE pg_catalog.pg_index SET indcollation = 199 pg_catalog.regexp_replace(indcollation::pg_catalog.text, E'^(\\d+ \\d+ \\d+ \\d+ \\d+ \\d+ \\d+) 0', E'\\1 100')::pg_catalog.oidvector 200WHERE indclass[7] IN ( 201 WITH RECURSIVE typeoids(typoid) AS 202 ( SELECT (my_schema || '.citext')::pg_catalog.regtype UNION 203 SELECT oid FROM pg_catalog.pg_type, typeoids 204 WHERE typelem = typoid OR typbasetype = typoid ) 205 SELECT oid FROM pg_catalog.pg_opclass, typeoids 206 WHERE opcintype = typeoids.typoid 207); 208 209-- somewhat arbitrarily, we assume no citext indexes have more than 8 columns 210 211PERFORM pg_catalog.set_config('search_path', old_path, true); 212END 213$$; 214