Re: Isnumeric function?

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.