Daniel Migowski <[EMAIL PROTECTED]> writes:I miss the possibility to code something like a userdifined varchar(n).
You're out of luck on that. The data types that can have precision parameters attached to them are hard-wired into the parser.
Maybe you don't need to invent a whole new data type but the existing varchar can serve just fine?
The attached script for version 7.3.4 (does not work with 7.4) demonstrates how to add case insensitive operators *=, *> and so on including an operator class for btree to the existing varchar.
All one has to do is to use *= instead of = in queries. Indexes, even unique, based on case insensitive comparision are possible too and well supported. The only thing I think wouldn't work are IN and NOT IN constructs.
Jan
-- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== [EMAIL PROTECTED] #
#!/bin/sh
DBNAME=ci_testdb
export DBNAME
dropdb ${DBNAME}
createdb ${DBNAME}
psql ${DBNAME} <<_EOF_
--
-- Case insensitive comparision functions
--
create function varchar_cieq(varchar, varchar) returns bool
as '
begin
return varchareq(lower(\$1), lower(\$2));
end;
' language plpgsql;
create function varchar_cine(varchar, varchar) returns bool
as '
begin
return varcharne(lower(\$1), lower(\$2));
end;
' language plpgsql;
create function varchar_cilt(varchar, varchar) returns bool
as '
begin
return varcharlt(lower(\$1), lower(\$2));
end;
' language plpgsql;
create function varchar_cile(varchar, varchar) returns bool
as '
begin
return varcharle(lower(\$1), lower(\$2));
end;
' language plpgsql;
create function varchar_cigt(varchar, varchar) returns bool
as '
begin
return varchargt(lower(\$1), lower(\$2));
end;
' language plpgsql;
create function varchar_cige(varchar, varchar) returns bool
as '
begin
return varcharge(lower(\$1), lower(\$2));
end;
' language plpgsql;
create function varchar_cicmp(varchar, varchar) returns int4
as '
begin
return varcharcmp(lower(\$1), lower(\$2));
end;
' language plpgsql;
--
-- Case insensitive operators
--
create operator *< (
procedure = varchar_cilt,
leftarg = varchar, rightarg = varchar,
commutator = *>, negator = *>=,
restrict = scalarltsel, join = scalarltjoinsel
);
create operator *= (
procedure = varchar_cieq,
leftarg = varchar, rightarg = varchar,
commutator = *=, negator = *<>,
restrict = eqsel, join = eqjoinsel,
sort1 = *<, sort2 = *<,
hashes
);
create operator *<> (
procedure = varchar_cine,
leftarg = varchar, rightarg = varchar,
commutator = *<>, negator = *=,
restrict = neqsel, join = neqjoinsel
);
create operator *!= (
procedure = varchar_cine,
leftarg = varchar, rightarg = varchar,
commutator = *<>, negator = *=,
restrict = neqsel, join = neqjoinsel
);
create operator *> (
procedure = varchar_cigt,
leftarg = varchar, rightarg = varchar,
commutator = *<, negator = *<=,
restrict = scalargtsel, join = scalargtjoinsel
);
create operator *<= (
procedure = varchar_cile,
leftarg = varchar, rightarg = varchar,
commutator = *>=, negator = *>,
restrict = scalarltsel, join = scalarltjoinsel
);
create operator *>= (
procedure = varchar_cige,
leftarg = varchar, rightarg = varchar,
commutator = *<=, negator = *<,
restrict = scalargtsel, join = scalargtjoinsel
);
--
-- And the operator class for case insensitive indexes
--
create operator class varchar_ciops
for type varchar using btree as
operator 1 *< (varchar, varchar),
operator 2 *<= (varchar, varchar),
operator 3 *= (varchar, varchar),
operator 4 *>= (varchar, varchar),
operator 5 *> (varchar, varchar),
function 1 varchar_cicmp(varchar, varchar);
create table citest_t1 (
id varchar(10),
data text
);
create unique index citest_t1_idx on citest_t1 (id varchar_ciops);
insert into citest_t1 values ('hello', 'world');
insert into citest_t1 values ('goodbye', 'world');
insert into citest_t1 values ('Hello', 'World');
set enable_seqscan to off;
set enable_indexscan to on;
explain select * from citest_t1 where id *= 'hello';
explain select * from citest_t1 where id = 'hello';
select * from citest_t1 where id *= 'HELLO';
select * from citest_t1 where id *!= 'HELLO';
select * from citest_t1 where id = 'HELLO';
_EOF_
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
