Re: Multifunction Indexes

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.