Lists: | pgsql-hackers |
---|
From: | Daniel Migowski <nur-gueltig-bis-2003-12-01(at)Mig-O(dot)de> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | CREATE TYPE for case insensitive text and varchar |
Date: | 2003-10-30 08:42:47 |
Message-ID: | bnqiuf$2u9v$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Hallo.
i just coded a new datatype named "cistring", that can be
used in every function that accepts text or varchar, and has
the great ability to be ordered case insensitive in ORDER BY-
Clauses and Indices. The problem is, that its coded like the
text-type, so it is variable length completly.
I miss the possibility to code something like a userdifined
varchar(n). I don't know how to create an input function,
that accepts the size of the type, or a function, how to declare
such types at all. I think, somethink like a userdefined
numeric(n,m) isn't possible, too, or am I wrong?
Thank you for all replies,
Daniel Migowski
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Daniel Migowski <nur-gueltig-bis-2003-12-01(at)Mig-O(dot)de> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: CREATE TYPE for case insensitive text and varchar |
Date: | 2003-10-30 22:34:14 |
Message-ID: | 3539.1067553254@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Daniel Migowski <nur-gueltig-bis-2003-12-01(at)Mig-O(dot)de> 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.
regards, tom lane
From: | Jan Wieck <JanWieck(at)Yahoo(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Daniel Migowski <nur-gueltig-bis-2003-12-01(at)Mig-O(dot)de>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: CREATE TYPE for case insensitive text and varchar |
Date: | 2003-10-31 03:38:19 |
Message-ID: | 3FA1D92B.5040009@Yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Tom Lane wrote:
> Daniel Migowski <nur-gueltig-bis-2003-12-01(at)Mig-O(dot)de> 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. #
#================================================== JanWieck(at)Yahoo(dot)com #
Attachment | Content-Type | Size |
---|---|---|
case_insensitive_varchar.sh | text/plain | 3.2 KB |