Index on substring?

Hi,

CREATE FUNCTION LANGUAGE C (and maybe others) allow to load shared
libraries. However the path can be specified arbitrarily by the user. Is
that a way for a user X to gain the UID rights of the user running the
postmaster ?

Yes, that is why only superusers have access to 'create function
language c'

-alex

On Wed, 11 Oct 2000, Marc SCHAEFER wrote:

Jeff Eckermann <jeckermann@verio.net> writes:

extracts=# create index c_namesum_i on customers (substr(bill_company,1,5));
ERROR: parser: parse error at or near "1"

The functional-index syntax only allows a function name applied to
simple column names.

You can work around this by defining a function that handles any
additional computation needed, eg,

create index c_namesum_i on customers (mysubstr15(bill_company));

where mysubstr15(foo) returns substr(foo,1,5). In current releases
the intermediate function has to be in C or a PL language. 7.1 will
allow a SQL-language function too (although frankly I'd recommend
against using a SQL function for indexing, on performance grounds).

There's been some talk of generalizing the functional-index support
into arbitrary-expression-index support, but it doesn't seem to be
real high on anyone's priority list.

regards, tom lane

Tom,
Thanks very much for your full and clear answer.
It's hard to imagine a general use for this facility, anyway.
For me this is a one-off exercise, albeit a big one.
Regards

On Thu, 12 Oct 2000, Tom Lane wrote:

Jeff Eckermann <jeckermann@verio.net> writes:

extracts=# create index c_namesum_i on customers (substr(bill_company,1,5));
ERROR: parser: parse error at or near "1"

The functional-index syntax only allows a function name applied to
simple column names.

You can work around this by defining a function that handles any
additional computation needed, eg,

I can't help but think this is a table design issue. Maybe not fully
normalized or needs to be de-normalized some. If the index is part of a
continuing need I'd suggest adding a column made up of the substring and
indexing on it instead. If the design isn't too far along review the
bill_company attribute (column) and see it it should be two columns.
It's always been easier for me to tie pieces together (views) than to
break them out of chunks.

Rod
--
Roderick A. Anderson
raanders@altoplanos.net Altoplanos Information Systems, Inc.
Voice: 208.765.6149 212 S. 11th Street, Suite 5
FAX: 208.664.5299 Coeur d'Alene, ID 83814

Thanks for the input.
In this case, we are not talking about persistent tables: they were
specially created for a particular purpose, and will be trashed again
afterwards.
To explain a little: I am tying together disparate customer databases that
have no common identifiers, other than the customer names and addresses.
These are subject to the usual variations in rendering, so direct name
comparisons fail far too often, although the two compared records refer to
the same customer. I have found that comparison on the first few characters
of the name gives good (enough) results, thus my interest in the substring.

Jeff Eckermann schrieb:

Tom,
Thanks very much for your full and clear answer.
It's hard to imagine a general use for this facility, anyway.
For me this is a one-off exercise, albeit a big one.
Regards

There're commercial OO persistance frameworks out there, which create
there own OID's (actually they consists out of three numbers) all these
numbers are converted to base 36 and concatenated to a string with size
15.

One part of this string is a class number of the instance you
have just loaded. Therefore if you look for instances of a
special class you may query a substring of this OID ....

Just as an practical usage ....

Marten