Re: Multifunction Indexes

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.

> The downside is that functional indexes won't allow you to
> use partial indexes.

I haven't been using partial indexes, anyway.

> Yes, I'm saying it should work on any expression, but I don't know
> enough to do anything but theorize :-)

That's what I thought -- and I agree! :-)

Thanks for the help!

David

--
David Wheeler AIM: dwTheory
david(at)kineticode(dot)com ICQ: 15726394
Yahoo!: dew7e
Jabber: Theory(at)jabber(dot)org
Kineticode. Setting knowledge in motion.[sm]