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

Re: Multifunction Indexes

From: elein <elein(at)sbcglobal(dot)net>
To: David Wheeler <david(at)kineticode(dot)com>, elein(at)varlena(dot)com
Cc: sfpug(at)postgresql(dot)org
Subject: Re: Multifunction Indexes
Date: 2003-03-15 01:20:05
Message-ID: 200303150124.h2F1OIlK367312@pimout2-ext.prodigy.net (view raw or flat)
Thread:
Lists: sfpug

I've got functions on the brain.

So...turn it into a functional index.  You can
pass more than one column into a functional index.


On Friday 14 March 2003 16:39, David Wheeler wrote:
> On Friday, March 14, 2003, at 04:25  PM, elein wrote:
> > As a workaround, push the lower() function into the
> > workflow() function. Of course if the workflow doesn't
> > always want lower($1) then you'll have to overload or rename it.
>
> Uh, but workflow is a table, not a function.
>

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;

Then when you query and want to use the functional index
you must call it as where xxx = foo(name,site_id)

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

> > I'm don't know  the structures  like Stephen does.
> > But if a plain expression parser were used consistently
> > it should be able to enable expressions rather
> > than single functions.  The expression tree would need
> > to be held rather than the function function pointer.
> > And of course all of it should be immutable.
> >
> > Then again theory is nice, but practice is a whole 'nother
> > ball game.
>
> Yes...not sure I understand what you're saying here...perhaps that
> allowing multiple function indexes for function plus column indexes
> should be do-able if the parser understood the syntax properly?

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

>
> Regards,
>
> David

elein

-- 
----------------------------------------------------------------------------------------
elein(at)varlena(dot)com     Database Consulting     www.varlena.com
              I have always depended on the [QA] of strangers.

In response to

Responses

sfpug by date

Next:From: David WheelerDate: 2003-03-15 01:28:48
Subject: Re: Multifunction Indexes
Previous:From: Stephan SzaboDate: 2003-03-15 00:43:58
Subject: Re: Multifunction Indexes

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