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>
Cc: sfpug(at)postgresql(dot)org
Subject: Re: Multifunction Indexes
Date: 2003-03-15 00:25:52
Message-ID: 200303150030.h2F0U5OU124380@pimout3-ext.prodigy.net (view raw or flat)
Thread:
Lists: sfpug
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.

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.

elein

On Friday 14 March 2003 15:46, you wrote:
> Hi all,
>
> Anyone know if there are plans to add multifunction or function +
> column indexes in the near future? I'd like to be able to do this:
>
>    CREATE UNIQUE INDEX on workflow(LOWER(name), site_id);
>
> And this:
>
>    CREATE UNIQUE INDEX on workflow(LOWER(this), TEXT(that));
>
> And a related question. Until the above is implemented, I have VARCHAR
> and NUMERIC(10, 0) columns I want to create a UNIQUE index on, with the
> VARCHAR column LOWERed. To do this, I have to create a single function
> that takes one of each of these types. Is this an appropriate function?
>
>    CREATE   FUNCTION lower(TEXT, NUMERIC(10, 0))
>    RETURNS  TEXT AS 'SELECT LOWER($1) || TEXT($2)' LANGUAGE 'sql'
>    WITH     (iscachable);
>
>    CREATE UNIQUE INDEX on workflow(lower(name, site_id));
>
> TIA,
>
> David

-- 
----------------------------------------------------------------------------------------
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: Stephan SzaboDate: 2003-03-15 00:38:54
Subject: Re: Multifunction Indexes
Previous:From: David WheelerDate: 2003-03-15 00:04:39
Subject: Re: Multifunction Indexes

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