From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | David Wheeler <david(at)kineticode(dot)com> |
Cc: | <elein(at)varlena(dot)com>, <sfpug(at)postgresql(dot)org> |
Subject: | Re: Multifunction Indexes |
Date: | 2003-03-15 02:40:06 |
Message-ID: | 20030314183832.H88199-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | sfpug |
On Fri, 14 Mar 2003, David Wheeler wrote:
> On Friday, March 14, 2003, at 05:20 PM, elein wrote:
>
> > I've got functions on the brain. Sorry. You are correct.
> >
> > So...turn it into a functional index. You can
> > pass more than one column into a functional index.
> >
> > create [unique] index fidx on workflow ( foo( name, site_id) );
> >
> > where foo is
> > create function foo(text,text) returns text as '
> > select lower($1)||$2;
> > ' language 'SQL' IMMUTABLE;
>
> That's exactly what I'm doing.
>
> > Then when you query and want to use the functional index
> > you must call it as where xxx = foo(name,site_id)
>
> Oh, right. Huh. I had just wanted to ensure that two columns combined
> were unique, but I don't actually want to use the function like that to
> do queries. I have separate indexes on each column for that. In that
> light, I think what makes the most sense is to actually use a function
> like Stephan and I have been discussing in a constraint, rather than an
> index, since at this point I'm really just using it to constrain what
> can be put into the table.
I think you still need to do it as a unique index. I don't think the
UNIQUE() constraint syntax will take it (which is just a unique index) and
doing your own unique is painful at best.
From | Date | Subject | |
---|---|---|---|
Next Message | David Wheeler | 2003-03-15 04:29:01 | Re: Multifunction Indexes |
Previous Message | Stephan Szabo | 2003-03-15 02:35:12 | Re: Multifunction Indexes |