I have a legacy system that has been developed with a mysql backend. I'm in the process of converting it over to postgres. As postgres is case sensitive it appears as I will have to make numerous code changes from "like" to "ilike" and from e.g. "cola = cola" to "lower(cola) = lower(cola)" and also changing all the indexes to lowercase as well. This is not as simple as it appears as the sql is dynamically generated in the code base, so I can see this as a very lengthy transition.
I have considered perhaps it might be easier to take a step back from the code and look at changing postgres.
There may be a way to change the database in postgres to be case insensitive. i.e. change some of the operations such as "text=text" to be case insensitive. Does this sound Insane? I have looking further into this and have found the function that does the text matching:
CREATE OR REPLACE FUNCTION texteq(text, text)
RETURNS bool AS
'texteq'
LANGUAGE 'internal' IMMUTABLE STRICT;
GRANT EXECUTE ON FUNCTION texteq(text, text) TO public;
COMMENT ON FUNCTION texteq(text, text) IS 'equal';
a. I was wondering if it was at all possible to change the case of the two text inputs to lower case. This will correct all text=text matches.
b. Then, substitute the like with ilike function, so they are the same.
C. By doing this I should not have to change any code at all, I guess I will still have to update the indexes to lower case, however that is a quick task.
Is this at all possible? And I dicing with danger here? Please enlighten me?
Theo
______________________________________________________________________
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright. If you
have received this email in error, please advise the sender and delete
it. If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone. You must not copy or
communicate to others content that is confidential or subject to
copyright, unless you have the consent of the content owner.
|
