Thankyou all for your feedback. I actually only want to check for whole numbers, so the ~ '^[0-9]+$' _expression_ is good.
The issue really is that our CMS system sometimes holds the value of primary keys within a "content" varchar column(don't ask!), which is a nightmare to search across. I tried applying an index across the "content" varchar column and it failed.
error: btree item size 2744 exceeds maximum 2713.
I assume I had to change some server settings to extend the maximum, however in the end this column holds content, and even applying an index would be incredible slow to search across hundred of thousands of "content" records looking for a primary key.
So I came up with the following. A Insert/update trigger would call a procedure to check to see if the content is numeric(a whole number), if so would update an indexed integer column called (content_numeric). Which would be the base column to search appon.
Here is the function anyway:
CREATE OR REPLACE FUNCTION update_content_node()
RETURNS trigger AS
'
begin
/* New function body */
IF NEW.content ~ \'^[0-9]+$\' THEN
NEW.content_numeric := NEW.content;
ELSE
NEW.content_numeric := null;
END IF;
RETURN NEW;
end;
'
LANGUAGE 'plpgsql' IMMUTABLE;
Does anyone have any better suggestions???
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. |
