Re: Multifunction Indexes

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

--
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]

On Fri, 14 Mar 2003, David Wheeler 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));

Not that I've heard in the short term. The data structures are
insufficient, so it might take some work.

> 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));

You need to be careful that you can't get duplicates from different value
pairs. In the above, I think $1 values ending in numerics could cause you
problems. Maybe using something like to_char($2, '09999999999') would
work better?

On Friday, March 14, 2003, at 03:56 PM, Stephan Szabo wrote:

>> CREATE UNIQUE INDEX on workflow(LOWER(name), site_id);
>>
>> And this:
>>
>> CREATE UNIQUE INDEX on workflow(LOWER(this), TEXT(that));
>
> Not that I've heard in the short term. The data structures are
> insufficient, so it might take some work.

Pity. I think that's a fairly important feature that many might find
significant.

>> 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));
>
> You need to be careful that you can't get duplicates from different
> value
> pairs. In the above, I think $1 values ending in numerics could cause
> you
> problems. Maybe using something like to_char($2, '09999999999') would
> work better?

Oh, yeah, good spot! So you're suggesting this:

CREATE FUNCTION lower(TEXT, NUMERIC(10, 0))
RETURNS TEXT AS 'SELECT LOWER($1) || to_char($2, ''09999999999'')'
LANGUAGE 'sql'
WITH (iscachable);

Would that be more efficient/precise than, say, this?:

CREATE FUNCTION lower(TEXT, NUMERIC(10, 0))
RETURNS TEXT AS 'SELECT LOWER($1) || '|' || TEXT($2)'
LANGUAGE 'sql'
WITH (iscachable);

Thanks,

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]

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.

On Fri, 14 Mar 2003, 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.
>
> 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.

Yeah, right now IIRC the index structure basically effectively has:
Array of columns
Function oid for functional indexes

That's also why you can't currently have constants in them, etc...

It needs work, but it's also probably likely to break/touch alot of stuff
to change, so that's why it's probably not been done.

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'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?

Regards,

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]

On Fri, 14 Mar 2003, David Wheeler wrote:

> >> 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));
>
> > You need to be careful that you can't get duplicates from different
> > value
> > pairs. In the above, I think $1 values ending in numerics could cause
> > you
> > problems. Maybe using something like to_char($2, '09999999999') would
> > work better?
>
> Oh, yeah, good spot! So you're suggesting this:
>
> CREATE FUNCTION lower(TEXT, NUMERIC(10, 0))
> RETURNS TEXT AS 'SELECT LOWER($1) || to_char($2, ''09999999999'')'
> LANGUAGE 'sql'
> WITH (iscachable);
>
> Would that be more efficient/precise than, say, this?:
>
> CREATE FUNCTION lower(TEXT, NUMERIC(10, 0))
> RETURNS TEXT AS 'SELECT LOWER($1) || '|' || TEXT($2)'
> LANGUAGE 'sql'
> WITH (iscachable);

Definately not more efficient but probably more correct.

sszabo=# create table test(a text, b numeric(10,0));
CREATE TABLE
sszabo=# insert into test values ('A', 123);
INSERT 17065 1
sszabo=# insert into test values ('A1', 23);
INSERT 17066 1
sszabo=# select lower(a) || TEXT(b) from test;
?column?
----------
a123
a123
(2 rows)

sszabo=# select lower(a) || to_char(b, '09999999999') from test;
?column?
----------------
a 00000000123
a1 00000000023
(2 rows)

(I guess you might really want FM0... but...)

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.

On Friday, March 14, 2003, at 04:43 PM, Stephan Szabo wrote:

> sszabo=# select lower(a) || to_char(b, '09999999999') from test;
> ?column?
> ----------------
> a 00000000123
> a1 00000000023
> (2 rows)
>
> (I guess you might really want FM0... but...)

FMO?

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]

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]

On Fri, 14 Mar 2003, David Wheeler wrote:

> On Friday, March 14, 2003, at 04:43 PM, Stephan Szabo wrote:
>
> > sszabo=# select lower(a) || to_char(b, '09999999999') from test;
> > ?column?
> > ----------------
> > a 00000000123
> > a1 00000000023
> > (2 rows)
> >
> > (I guess you might really want FM0... but...)
>
> FMO?

IIRC FM makes it drop that leading space, so FM09999999999 (but I was lazy
and didn't add the 9s

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.

On Friday, March 14, 2003, at 06:35 PM, Stephan Szabo wrote:

> IIRC FM makes it drop that leading space, so FM09999999999 (but I was
> lazy
> and didn't add the 9s

Ah, got it, a to_char() format. Thanks.

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]

On Friday, March 14, 2003, at 06:40 PM, Stephan Szabo wrote:

> 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.

Yep, I just tried and decided it was a big PITA -- the index is much
easier. So this is what I've gone with:

CREATE FUNCTION lower_text_num(TEXT, NUMERIC(10, 0))
RETURNS TEXT AS 'SELECT LOWER($1) || to_char($2, ''|FM9999999999'')'
LANGUAGE 'sql'IMMUTABLE;

CREATE UNIQUE INDEX udx_alert_type__name__usr__id
ON alert_type(lower_text_num(name, usr__id));

Thanks for the help, it's much appreciated.

Regards,

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]