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

Re: Multifunction Indexes

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 (view raw or flat)
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.



In response to

Responses

sfpug by date

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

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