Thread: Index on substring?
I thought this was possible, but searching the archives & docs I can't find any reference to it... Am I doing something wrong? jeffe@kiyoko=> psql -V psql (PostgreSQL) 7.0.0 jeffe@kiyoko=> uname -a FreeBSD kiyoko.la.verio.net 4.0-STABLE FreeBSD 4.0-STABLE #0: Thu Apr 27 10:44:07 CDT 2000 root@:/usr/src/sys/compile/KIYOKO i386 extracts=# create index c_namesum_i on customers (substr (bill_company,1,5)); ERROR: parser: parse error at or near "1" extracts=# select substr (bill_company, 1, 5) from customers limit 10; substr -------- RoadW Beliz Radio Trill R2000 Data Inter AEC M G2 Gr MindB (10 rows) extracts=# create index c_namesum_i on customers (substring(bill_company from 1 for 5)); ERROR: parser: parse error at or near "substring" extracts=# select substring (bill_company from 1 for 5) from customers limit 10; substr -------- RoadW Beliz Radio Trill R2000 Data Inter AEC M G2 Gr MindB (10 rows) extracts=#
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: > 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 ? > >
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 > -----Original Message----- > From: Tom Lane [SMTP:tgl@sss.pgh.pa.us] > Sent: Thursday, October 12, 2000 12:49 AM > To: Jeff Eckermann > Cc: 'pgsql-general@postgresql.org' > Subject: Re: [GENERAL] Index on substring? > > 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
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. > -----Original Message----- > From: Roderick A. Anderson [SMTP:raanders@altoplanos.net] > Sent: Thursday, October 12, 2000 9:53 AM > To: 'pgsql-general@postgresql.org' > Subject: Re: [GENERAL] Index on substring? > > 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
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