From: Thomas Wegner <tomaten@t-online.de>
To: pgsql-sql@postgresql.org
Subject: Re: Make a column case insensitive
Date: Sun, 8 Aug 2004 22:39:29 +0200
Message-ID: <cf6324$1mi3$1@news.hub.org> (raw)
References: <s113b1e8.083@groupwise.ccw.gov.uk>
<1091993235.799.15.camel@jester>
I create every time i need this, a copy of this field filled out by a
trigger
on insert and update that holds the upper (or lower for you) value of
the orginal field like this:
create table "users" (
"email" varchar(255),
...
"u_email" varchar(255)
...
);
CREATE OR REPLACE FUNCTION "public"."user_function" () RETURNS trigger AS'
BEGIN
NEW."u_email" = UPPER(NEW."email");
RETURN NEW;
END;
'LANGUAGE 'plpgsql';
CREATE TRIGGER "user_insert" BEFORE INSERT
ON "public"."user" FOR EACH ROW
EXECUTE PROCEDURE "public"."user_function"();
CREATE TRIGGER "user_update" BEFORE UPDATE
ON "public"."user" FOR EACH ROW
EXECUTE PROCEDURE "public"."user_function"();
and do a simple
select from blabla where u_email=:email
Param email = Upper(searchvalue).
This speed up any case insensitive searches.
---------------------------------------------
Thomas Wegner
Cabrio Meter - The Weather Plugin for Trillian
http://trillian.wegner24.de/cabriometer
"Rod Taylor" <pg@rbt.ca> schrieb im Newsbeitrag
news:1091993235.799.15.camel@jester...
> On Fri, 2004-08-06 at 11:29, Gordon Ross wrote:
> > Is it possible to make a column case insensitive, without having to
pepper your SELECTs with lots of lower() function calls (and forgetting to
do it at times !)
>
> You could make yourself a set returning function to do this job -- but
> that is probably just as difficult to remember when selecting data out
> of the table.
>
> create function abc(text) returns set of tab as 'select * from tab where
> col = lower($1);' language sql;
>
> select * from abc('sEaRcH_VaLuE');
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tomaten@t-online.de
Subject: Re: Make a column case insensitive
In-Reply-To: <cf6324$1mi3$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox