Hi!
I´m currently trying to read the information of an functional index from the systables.
The only way I find out, was to use the indexkeyarray_out(sysindices.indexkeys) - Function.
But I found no official documentation for this (only errors after upgrade).
Only this page here:
getting functional index info from sysindices.indexkeys - InformixMy test-example:
CREATE TABLE Persons (
LastName varchar(255,30),
FirstName varchar(128,20),
Address varchar(255),
City char(50),
info lvarchar(500),
id serial
);
create function if not exists my_lower( s varchar(255) ) returning varchar(255) with (not variant )
return lower(s); end function;
create function if not exists my_lower( s char(796) ) returning char(796) with (not variant )
return lower(s); end function;
create function if not exists my_lower( s lvarchar(796) ) returning lvarchar(796) with (not variant )
return lower(s); end function;
create function if not exists multi_lower( s varchar(255), x varchar(255) ) returning varchar(255) with (not variant )
return lower(s); end function;
And then i created the following indexes as test (Result of "
SELECT indexkeyarray_out(sysindices.indexkeys) FROM sysindices
" as comment):
create index i_persons_idx1 on persons(LastName); -- "1 [1]"
create index i_persons_idx1desc on persons(LastName DESC); -- "-1 [1]"
create index i_persons_fu_idx1 on persons(my_lower(LastName));-- "<574>(1) [1]"
create index i_persons_idxAll1 on persons(city, my_lower(address), id DESC); -- "4 [1], <574>(3) [1], -6 [1]"
create index i_persons_idxAll2 on persons(my_lower(address) DESC, id DESC); -- "<574>(-3) [1], -6 [1]"
create index i_persons_idxMultiAll1 on persons(multi_lower(address, FirstName) DESC, id DESC); -- "<576>(-3, 2) [1], -6 [1]"
Shouldn´t I use this function? Or is there a better solution for reading out this information (over JDBC)?
------------------------------
Kind Regards
Stefan
------------------------------
#Informix