---------------------------------------------------------------------------
-- Case-insensitive text type for PostgreSQL
-- Copyright (C) Aug 2005   Martijn van Oosterhout <kleptog@svana.org>
-- Free for use to anybody who wants it.
--
-- This is my first type/operator class/etc so it might not work perfectly.
--
-- Original URL: http://svana.org/kleptog/pgsql/type_itext.sql
--
-- Notes: 
--   - Tucks all functions and stuff into a schema 'schema_itext'
--   - The type, the operators and operator class appear in public
--
--   - It creates a cast for itext to text, but not implicit so you'll have
--     to cast manually each time. If this bothers you for some function,
--     create an alias to it for itext.
--
--   - WARNING: Due to the DROP .. CASCADE, running this script on a
--     database that uses it will DROP any columns using this type...
--
-- Thanks to Darko Prenosil for posting the example that got me started.
-- http://archives.postgresql.org/pgsql-interfaces/2001-12/msg00030.php
---------------------------------------------------------------------------

-- Cleanup
DROP TYPE itext CASCADE;
DROP SCHEMA schema_itext CASCADE;
delete from pg_amproc where amopclaid=(select oid from pg_opclass where opcname = 'itext_ops');
delete from pg_amop where amopclaid=(select oid from pg_opclass where opcname = 'itext_ops');
delete from pg_opclass where opcname = 'itext_ops';

-- Create schema and create everything except the type in there
create schema schema_itext;

set search_path=schema_itext, public;

-- Basic type functions
CREATE FUNCTION itextin(cstring) RETURNS public.itext
    AS 'textin'
    LANGUAGE internal IMMUTABLE STRICT;



CREATE FUNCTION itextsend(public.itext) RETURNS bytea
    AS 'textsend'
    LANGUAGE internal IMMUTABLE STRICT;



CREATE FUNCTION itextrecv(internal) RETURNS public.itext
    AS 'textrecv'
    LANGUAGE internal IMMUTABLE STRICT;



CREATE FUNCTION itextout(public.itext) RETURNS cstring
    AS 'textout'
    LANGUAGE internal IMMUTABLE STRICT;



CREATE TYPE public.itext (
    INTERNALLENGTH = variable,
    INPUT = itextin,
    OUTPUT = itextout,
    RECEIVE = itextrecv,
    SEND = itextsend,
    ALIGNMENT = int4,
    STORAGE = plain
);

comment on type public.itext is 'Case-insensitive text type by Martijn van Oosterhout <kleptog@svana.org>. No warranty!!!';

-- Create cast, maybe AS ASSIGNMENT or AS IMPLICIT???
CREATE CAST (itext AS text) WITHOUT FUNCTION;

-- Comparison functions
CREATE FUNCTION itext_lt(itext, itext) RETURNS boolean
    AS 'select lower($1::text) < lower($2::text)'
    LANGUAGE sql IMMUTABLE STRICT;



CREATE FUNCTION itext_le(itext, itext) RETURNS boolean
    AS 'select lower($1::text) <= lower($2::text)'
    LANGUAGE sql IMMUTABLE STRICT;



CREATE FUNCTION itext_eq(itext, itext) RETURNS boolean
    AS 'select lower($1::text) = lower($2::text)'
    LANGUAGE sql IMMUTABLE STRICT;



CREATE FUNCTION itext_gt(itext, itext) RETURNS boolean
    AS 'select lower($1::text) > lower($2::text)'
    LANGUAGE sql IMMUTABLE STRICT;



CREATE FUNCTION itext_ge(itext, itext) RETURNS boolean
    AS 'select lower($1::text) >= lower($2::text)'
    LANGUAGE sql IMMUTABLE STRICT;


-- Comparison operators
CREATE OPERATOR public.> (
    PROCEDURE = itext_gt,
    LEFTARG = itext,
    RIGHTARG = itext
);



CREATE OPERATOR public.>= (
    PROCEDURE = itext_ge,
    LEFTARG = itext,
    RIGHTARG = itext
);



CREATE OPERATOR public.= (
    PROCEDURE = itext_eq,
    LEFTARG = itext,
    RIGHTARG = itext
);



CREATE OPERATOR public.<= (
    PROCEDURE = itext_le,
    LEFTARG = itext,
    RIGHTARG = itext
);



CREATE OPERATOR public.< (
    PROCEDURE = itext_lt,
    LEFTARG = itext,
    RIGHTARG = itext
);


-- BTree comparison function
CREATE FUNCTION btitextcmp(itext, itext) RETURNS integer
    AS 'select bttextcmp(lower($1::text),lower($2::text))'
    LANGUAGE sql IMMUTABLE STRICT;

-- Create operator class.
CREATE OPERATOR CLASS public.itext_ops
    DEFAULT FOR TYPE itext USING btree AS
    OPERATOR 1 <(itext,itext) ,
    OPERATOR 2 <=(itext,itext) ,
    OPERATOR 3 =(itext,itext) ,
    OPERATOR 4 >=(itext,itext) ,
    OPERATOR 5 >(itext,itext) ,
    FUNCTION 1 schema_itext.btitextcmp(itext,itext);

-- Done
