Skip site navigation (1) Skip section navigation (2)

Re: Multifunction Indexes

From: David Wheeler <david(at)kineticode(dot)com>
To: elein(at)varlena(dot)com
Cc: sfpug(at)postgresql(dot)org
Subject: Re: Multifunction Indexes
Date: 2003-03-15 01:31:32
Message-ID: DA4DD79A-5685-11D7-8FDF-0003931A964A@kineticode.com (view raw or flat)
Thread:
Lists: sfpug
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]


In response to

Responses

sfpug by date

Next:From: Stephan SzaboDate: 2003-03-15 02:35:12
Subject: Re: Multifunction Indexes
Previous:From: David WheelerDate: 2003-03-15 01:28:48
Subject: Re: Multifunction Indexes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group